原文地址：https://www.percona.com/blog/deep-dive-into-mysqls-performance-schema/
April 2, 2024
This blog was originally published in January 2023 and was updated in April 2024.
Recently I was working with a customer wherein our focus was to carry out a performance audit of their multiple MySQL database nodes. We started looking into the stats of the performance schema. While working, the customer raised two interesting questions: how can he make complete use of the performance schema, and how can he find what he requires? I realized that it is important to understand the insights of the MySQL performance schema and how we can make effective use of it. This blog should make it easier to understand for everyone.
The performance schema is an engine in MySQL which can easily be checked whether enabled or not using SHOW ENGINES. It is entirely built upon various sets of instruments (also can be called event names) each serving different purposes.
Instruments are the main part of the performance schema. It is useful when I want to investigate a problem and its root causes. Some of the examples are listed below (but not limited to) :**
1. Which IO operation is causing MySQL to slow down?
2. Which file a process/thread is mostly waiting for?
3. At which execution stage is a query taking time, or how much time will an alter command will take?
4. Which process is consuming most of the memory or how to identify the cause of memory leakage?
Instruments are a combination of different sets of components like wait, io, sql, binlog, file, etc. If we combine these components, they become a meaningful tool to help us troubleshoot different issues. For example, wait/io/file/sql/binlog is one of the instruments providing information regarding the wait and I/O details on binary log files. Instruments are being read from left and then components will be added with delimiter “/”. The more components we add to the instrument, the more complex or more specific it becomes, i.e. the more lengthy the instrument is, the more complex it goes.
You can locate all instruments available in your MySQL version under table setup_instruments. It is worth noting that every version of MySQL has a different number of instruments.
For easy understanding, instruments can be divided into seven different parts as shown below. The MySQL version I am using here is 8.0.30. In earlier versions, we used to have only four, so expect to see different types of instruments in case you are using different/lower versions.
- Stage – Instrument starting with ‘stage’ provides the execution stage of any query like reading data, sending data, altering table, checking query cache for queries, etc. For example stage/sql/altering table.
- Wait – Instrument starting with ‘wait’ falls here. Like mutex waiting, file waiting, I/O waiting, and table waiting. Instrument for this can be wait/io/file/sql/map.
- Memory – Instrument starting from “memory” providing information regarding memory usage on a per-thread basis. For example memory/sql/MYSQL_BIN_LOG
- Statement – Instruments starting with “statement” provide information about the type of SQL, and stored procedures.
- Idle – provide information on socket connection and information related to a thread.
- Transaction – Provide information related to the transactions and have only one instrument.
- Error – This single instrument provides information related to the errors generated by user activities. There are no further components attached to this instrument.
The total number of instruments for these seven components is listed below**.** You can identify these instruments starting with these names only.
I do remember that a customer asked me since there are thousands of instruments available, how can he find out which one he requires. As I mentioned before that instruments are being read from left to right, we can find out which instrument we require and then find its respective performance.
For example – I need to observe the performance of redo logs (log files or WAL files) of my MySQL instance and need to check if threads/connections need to wait for the redo log files to be flushed before further writing and if so then how much.
Here you see that I have two instruments for redo log files. One is for the mutex stats on the redo log files and the second is for the IO wait stats on the redo log files.
Example two – You need to find out those operations or instruments for which you can calculate the time required i.e. how much time a bulk update will take. Below are all the instruments that help you to locate the same.
The above instruments are the ones for which progress can be tracked.
To take advantage of these instruments, they need to be enabled first to make the performance schema log-related data. In addition to logging the information of running threads, it is also possible to maintain the history of such threads (statement/stages or any particular operation). Let’s see, by default, how many instruments are enabled in the version I am using. I have not enabled any other instrument explicitly.
The below query lists the top 30 enabled instruments for which logging will take place in the tables.
Note – The top 15 records in the above rows are self-explanatory, but the last one for digest means to allow the digest text for SQL statements. By digest I mean, grouping similar queries and showing their performance. This is being done by hashing algorithms.
Let’s say, you want to analyze the stages of a query that is spending most of the time in, you need to enable the respective logging using the below query.
Now that we know what instruments are, how to enable them, and the amount of data we want to store in, it’s time to understand how to make use of these instruments. To make it easier to understand I have taken the output of a few instruments from my test cases as it won’t be possible to cover all as there are more than a thousand instruments.
Please note that to generate the fake load, I used sysbench (if you are not familiar with it, read about it here) to create read and write traffic using the below details :
As an example, think about a case when you want to find out where memory is getting utilized. To find out this, let’s execute the below query in the table related to the memory.
Instrument memory/innodb/buf_buf_pool is related to the buffer pool which is utilizing 3 GB and we can fetch this information from SUM_NUMBER_OF_BYTES_ALLOC. Another data that is also important for us to consider is CURRENT_COUNT_USED which tells us how many blocks of data have been currently allocated and once work is done, the value of this column will be modified. Looking at the stats of this record, consumption of 3GB is not a problem since MySQL uses a buffer pool quite frequently ( for example, while writing data, loading data, modifying data, etc.). But the problem rises, when you have memory leakage issues or the buffer pool is not getting used. In such cases, this instrument is quite useful to analyze.
Looking at the second instrument memory/sql/THD::main_mem_root which is utilizing 2G, is related to the **sql (**that’s how we should read it from the very left). THD::main_mem_root is one of the thread classes. Let us try to understand this instrument:
THD represent thread
main_mem_root is a class of mem_root. MEM_ROOT is a structure being used to allocate memory to threads while parsing the query, during execution plans, during execution of nested queries/sub-queries and other allocations while query execution. Now, in our case we want to check which thread/host is consuming memory so that we can further optimize the query. Before digging down further, let’s understand the 3rd instrument first which is an important instrument to look for.
memory/sql/filesort_buffer::sort_keys – As I mentioned earlier, instrument names should be read starting from left. In this case, it is related to memory allocated to sql. The next component in this instrument is filesort_buffer::sort_keys which is responsible for sorting the data (it can be a buffer in which data is stored and needs to be sorted. Various examples of this can be index creation or normal order by clause)
It’s time to dig down and analyze which connection is using this memory. To find out this, I have used table memory_summary_by_host_by_event_name and filtered out the record coming from my application server.
Event name memory/sql/THD::main_mem_root has consumed more than 1G memory ( sum ) by the host 11.11.120.141 which is my application host at the time of executing this query. Now since we know that this host is consuming memory, we can dig down further to find out the queries like nested or subquery and then try to optimize it.
Similarly, if we see the memory allocation by filesort_buffer::sort_keys is also more than 1G (total) at the time of execution. Such instruments signal us to refer to any queries using sorting i.e. order by clause.
Ensure your databases are performing their best — today and tomorrow — with proactive database optimization and query tuning. Book a database assessment
Let’s try to find out the culprit thread in one of the cases where most of the memory is being utilized by the file sort. The first query helps us in finding the host and event name (instrument):
Ahan, this is my application host, and let’s find out which user is executing and its respective thread id.
select * from memory_summary_by_account_by_event_name where HOST='10.11.54.152' order by SUM_NUMBER_OF_BYTES_ALLOC desc limit 1G;
*************************** 1. row ***************************
USER: sbuser
HOST: 10.11.54.152
EVENT_NAME: memory/sql/Filesort_buffer::sort_keys
COUNT_ALLOC: 5612993
COUNT_FREE: 5612993
SUM_NUMBER_OF_BYTES_ALLOC: 193239513120
SUM_NUMBER_OF_BYTES_FREE: 193239513120
LOW_COUNT_USED: 0
CURRENT_COUNT_USED: 0
HIGH_COUNT_USED: 20
LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 0
HIGH_NUMBER_OF_BYTES_USED: 819840
select * from memory_summary_by_thread_by_event_name where EVENT_NAME='memory/sql/Filesort_buffer::sort_keys' order by SUM_NUMBER_OF_BYTES_ALLOC desc limit 1G;
*************************** 1. row ***************************
THREAD_ID: 84
EVENT_NAME: memory/sql/Filesort_buffer::sort_keys
COUNT_ALLOC: 565645
COUNT_FREE: 565645
SUM_NUMBER_OF_BYTES_ALLOC: 19475083680
SUM_NUMBER_OF_BYTES_FREE: 19475083680
LOW_COUNT_USED: 0
CURRENT_COUNT_USED: 0
HIGH_COUNT_USED: 2
LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 0
HIGH_NUMBER_OF_BYTES_USED: 81984
Now, we have the complete details of the user and its thread id. Let’s see which sort of queries are being executed by this thread.
I have pasted one record only as per rows_scan (which refers to the table scan) here but you can find similar other queries in your case and then try to optimize it either by creating an index or some other suitable solution.
Example Two
Let’s try to find out the situation of table locking i.e. which lock i.e. read lock, write lock, etc., has been put on the user table and for what duration (displayed in pico seconds).
Lock a table with write lock :
Now, think of a situation wherein you are not aware of this session and you are trying to read this table and thus waiting for the meta data locks. In this situation, we need to take the help of instruments (to find out which session is locking this table) related to the lock i.e. wait/table/lock/sql/handler (table_handles is the table responsible for table lock instruments):
mysql> select * from table_handles where object_name='sbtest2' and OWNER_THREAD_ID is not null;
+-------------+---------------+-------------+-----------------------+-----------------+----------------+---------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | OBJECT_INSTANCE_BEGIN | OWNER_THREAD_ID | OWNER_EVENT_ID | INTERNAL_LOCK | EXTERNAL_LOCK |
+-------------+---------------+-------------+-----------------------+-----------------+----------------+---------------+----------------+
| TABLE | sysbench | sbtest2 | 140087472317648 | 141 | 77 | NULL | WRITE EXTERNAL |
+-------------+---------------+-------------+-----------------------+-----------------+----------------+---------------+----------------+
mysql> select * from metadata_locks;
+---------------+--------------------+------------------+-------------+-----------------------+----------------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
+---------------+--------------------+------------------+-------------+-----------------------+----------------------+---------------+-------------+-------------------+-----------------+----------------+
| GLOBAL | NULL | NULL | NULL | 140087472151024 | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | sql_base.cc:5534 | 141 | 77 |
| SCHEMA | sysbench | NULL | NULL | 140087472076832 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | sql_base.cc:5521 | 141 | 77 |
| TABLE | sysbench | sbtest2 | NULL | 140087471957616 | SHARED_NO_READ_WRITE | TRANSACTION | GRANTED | sql_parse.cc:6295 | 141 | 77 |
| BACKUP TABLES | NULL | NULL | NULL | 140087472077120 | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | lock.cc:1259 | 141 | 77 |
| TABLESPACE | NULL | sysbench/sbtest2 | NULL | 140087471954800 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | lock.cc:812 | 141 | 77 |
| TABLE | sysbench | sbtest2 | NULL | 140087673437920 | SHARED_READ | TRANSACTION | PENDING | sql_parse.cc:6295 | 142 | 77 |
| TABLE | performance_schema | metadata_locks | NULL | 140088117153152 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6295 | 143 | 970 |
| TABLE | sysbench | sbtest1 | NULL | 140087543861792 | SHARED_WRITE | TRANSACTION | GRANTED | sql_parse.cc:6295 | 132 | 156 |
+---------------+--------------------+------------------+-------------+-----------------------+----------------------+---------------+-------------+-------------------+-----------------+----------------+
From here we know that thread id 141 is holding the lock “SHARED_NO_READ_WRITE” on sbtest2 and thus we can take the corrective step i.e. either commit the session or kill it, once we realize its requirement. We need to find the respective processlist_id from the threads table to kill it.
Example Three
In some situations, we need to find out where our MySQL server is spending most of the time waiting so that we can take further steps :
We can configure almost all instruments and instead of querying, we can just make use of these graphs.
Obviously, knowing about performance schema helps us a lot but also enabling all of them incurs additional costs and impacts performance. Hence, in many cases, Percona Toolkit is helpful without impacting the DB performance. For example, pt-index-usage, pt-online schema change, pt-query-digest.
- History table loads after a while, not instantly. Only after completion of a thread activity.
- Enabling all instruments may impact the performance of your MySQL as we are enabling more writes to these in-memory tables. Also, it will impose additional money on your budget. Hence enable as per requirements only.
- PMM contains most of the instruments and is also possible to configure more as per your requirements.
- You don’t need to remember the name of all the tables. You can just use PMM or use joins to create the queries. This article hashes the entire concept into smaller chunks and thus didn’t use any joins so that readers can understand it.
- The best method of enabling multiple instruments is in the staging environment and then optimize your findings and then moving to the productions.
Performance schemas are a great help while troubleshooting the behavior of your MySQL server. You need to find out which instrument you need. Should you be still struggling with the performance, we offer expert support for MySQL databases as well as fully managed services for MySQL. Our experts can help you overcome issues that are negatively impacting performance like poorly optimized queries, slow response times, and much more. Please don’t hesitate to reach out to us and we will be more than happy to help you.
Our MySQL Performance Tuning guide covers the critical aspects of MySQL performance optimization. It will help you ensure your databases run smoother, faster, and more reliably. Get it today:
In MySQL, the performance_schema is a feature designed to help monitor and tune MySQL’s performance. It acts as a specialized database that collects and aggregates data about server execution at a low level, including wait events, file I/O, and table locks, among others. This rich set of data is accessible through a suite of tables and provides a comprehensive view of the internal operation of the MySQL server, enabling developers and database administrators to diagnose performance issues and optimize their databases more effectively.
To harness the MySQL Performance Schema’s full potential, it’s crucial to start with a clear understanding of your monitoring goals. Tailor the Performance Schema’s instrumentation to focus on specific areas of interest, such as wait events or I/O operations, to minimize overhead. Utilize tools like sys schema for simplified access to Performance Schema data. Most importantly, integrate Performance Schema data into your regular performance review process to proactively identify and mitigate potential issues before they escalate.
The Performance Schema is designed to have minimal impact on MySQL performance by using lightweight instrumentation and efficient data structures to collect performance data. However, the extent of its impact can vary based on the level of instrumentation enabled and the workload of the server. Enabling detailed monitoring across numerous events and tables can introduce overhead, particularly under high workloads.