I have been doing quite a lot of benchmarking recently.
I needed to find a safe way of measuring the time spend by the
database doing a long task, like catching up on a huge backlog of
accumulated replication updates. The problem with measuring this
event is that I can record when it starts, but I can't easily
detect when it finishes. My initial approach was to monitor the
database and count the tables rows to see when the task was done,
but I ended up affecting the task performance with my additional
queries. So I thought of another method.
Since I had control on what was sent from the master to the
slave, I used the following:
The initial time is calculated as the minimum creation time of
the databases that I know are created during the exercise. Let's
say that I had 5 databases named from db1 to db5:
set @START = (select min(create_time) from information_schema.tables where table_schema like "db%")
…
[Read more]