Update – “setup_timers” table has been depreciated and is removed in MySQL 8.0. This blog is covering the details for 5.7/8.0/9.4. There might be some changes that reader can expect in 8.0 and 9.4

Blog starts from this point –

This is a two article series on Instrument in MySQL. This is the first one and second one can be read from here

Instrument are the main part of performance schema . Well, it is useful in cases when i want to dive inside a problem . For an example, if i want to check what IO operation is causing my DB slow down or what file a process is waiting for or what stage is taking time, which connection is expensive etc.

But this is also true that enabling them , affects mysql performance. Hence you need to follow strategy as per your needs.You can enable all of them while in testing environment and can tune your queries or can enable few of them in production environment to monitor your DB performance.

Instruments are combination of different set of components like wait/io/file/sql/binlog.Instrument start from general and get more deeper. We can identify how specific are they by the length of the name of instruments.

I can check all instrument from performance_schema.setup_instruments table whether they are enabled or not. There are thousand of instrument which are responsible for representing different activities.

---

—-Update—

There are more than 600 such instruments for different purposes in MySQL 5.7

like for sql binlog, innodb_data_file,replication,page lock, mutex lock,select,delete,show privileges , load,copying to tmp table.

So every instrument is not enabled, but we can enable it and can check which is enabled by executing below query :

select * from setup_instrument where enabled='Yes'

We have one more table which is setup_consumer which tells you how much data you would like to save . By this i meant , history or current data.

---

If we check in setup_timers table , we find different component there which are prefix in all instrument , like below :

1. Idle ( microcycle)

2. Stage (cycle)

3. Statement (nano)

4. Wait (nano)

Idle will give internal information on socket connection and information related to a thread like how much packet has been transferred .

Stage tell the execution stage of any query like reading data, sending data, altering table, checking query cache for queries etc.  Instrument for this can be represented as stage/sql/altering table.

Statements are related to sql,abstract,com which i will give more information later.Instrument for this can be statement/sql/select.

Wait , as the name implies all the wait operation comes here. Like mutex waiting  , file waiting, I/O waiting ,table waiting.Instrument for this can be wait/io/file/sql/map.

Instrument should alway be read from left to right.

In the next session , i will cover how it helps in improving your database performance. Please be aligned.

Your comments are welcome.

About Ankit Kapoor

Ankit Kapoor is a database geek and enjoy playing with open source databases like MySQL and PG.
Connect with me on linkedin