DBA Blogs
Efficient way to fetch data using rownumber from a billion record table??
using sql loader for a table where you only have insert privileges via a non default role
Access Table Without Synonym and Without Schema Name
print an XMLTYPE Record Type variable
Expdb views_as_tables failes after view recreated, bug or feature?
Partner Webcast – Next-Gen Automated Systems Management for Oracle Exadata
We share our skills to maximize your revenue!
Update production database from ETL Process
DB Link between Oracle 12c (Linux) and Oracle 11.2 R2 (Windows)
Merge using collections
getting rows N through M of a result set
Permission issues when loading text file to external table
Best practices for keeping passwords hidden
Object View To Manipulate Data In Tables
Lob: Basic - Secure
Distinct count across multiple tables
lost update
Update a column after multiple criteria
How Oracle Database gets the data from multiple left joins
One query in MySQL Performance Schema
I am learning about MySQL performance tuning. I read the Performance Schema chapter of the MySQL 5.7 manual and I have a MySQL 5.7.20 database running under Linux. I have a test table and I am running a simple query that does a full scan of the table and returns the sum of a couple of columns. The goal was to look at some Performance Schema tables to see what they would tell me about this simple query. I bounce the MySQL database service before running my test script so that the query pulls data from disk and not memory. A zip of my test script and its output is here: zip
I looked at the tables that had this pattern events_*_history_long. These are the four tables:
- events_statements_history_long
- events_transactions_history_long
- events_stages_history_long
- events_waits_history_long
There seems to be a hierarchical relationship among these tables something like this:
- statements->transactions
- statements->stages->waits->waits
There may be other relationships that my simple test did not capture. If you look at the zip you will see all the levels of the hierarchy that I explored but, in this case, the bottom level seems the most interesting. Here is the query that explores the bottom of the statements->stages->waits->waits path:
select * from events_waits_history_long
where
NESTING_EVENT_ID in
(select EVENT_ID from events_waits_history_long
where
NESTING_EVENT_ID in
(select EVENT_ID from events_stages_history_long
where
NESTING_EVENT_ID in
(select EVENT_ID from events_statements_history_long
where SQL_TEXT like concat('%te','st%'))
and NESTING_EVENT_TYPE = 'STATEMENT')
and NESTING_EVENT_TYPE = 'STAGE')
and NESTING_EVENT_TYPE = 'WAIT'
order by EVENT_ID;
The output reminds me of a trace in Oracle. It shows the individual disk reads and waits for internal locks and mutexs.
+-----------+----------+--------------+-------------------------------------------+-----------------+---------------+---------------+------------+-------+---------------+-------------------------------+------------+-------------+-----------------------+------------------+--------------------+----------------+-----------------+-------+
| THREAD_ID | EVENT_ID | END_EVENT_ID | EVENT_NAME | SOURCE | TIMER_START | TIMER_END | TIMER_WAIT | SPINS | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | OBJECT_TYPE | OBJECT_INSTANCE_BEGIN | NESTING_EVENT_ID | NESTING_EVENT_TYPE | OPERATION | NUMBER_OF_BYTES | FLAGS |
+-----------+----------+--------------+-------------------------------------------+-----------------+---------------+---------------+------------+-------+---------------+-------------------------------+------------+-------------+-----------------------+------------------+--------------------+----------------+-----------------+-------+
| 28 | 11162 | 11162 | wait/io/file/innodb/innodb_data_file | fil0fil.cc:5778 | 5744476160960 | 5744479752316 | 3591356 | NULL | NULL | /var/lib/mysql/bobby/test.ibd | NULL | FILE | 139648405042752 | 203 | WAIT | read | 16384 | NULL |
| 28 | 11163 | 11163 | wait/synch/mutex/innodb/buf_pool_mutex | buf0lru.cc:1320 | 5744479934370 | 5744479974470 | 40100 | NULL | NULL | NULL | NULL | NULL | 66655496 | 203 | WAIT | lock | NULL | NULL |
| 28 | 11164 | 11164 | wait/synch/mutex/innodb/buf_pool_mutex | buf0buf.cc:5150 | 5744480619278 | 5744480646546 | 27268 | NULL | NULL | NULL | NULL | NULL | 66655496 | 203 | WAIT | lock | NULL | NULL |
| 28 | 11165 | 11165 | wait/synch/sxlock/innodb/hash_table_locks | buf0buf.cc:5153 | 5744480749202 | 5744480858274 | 109072 | NULL | NULL | NULL | NULL | NULL | 70197752 | 203 | WAIT | exclusive_lock | NULL | NULL |
| 28 | 11166 | 11166 | wait/synch/mutex/innodb/fil_system_mutex | fil0fil.cc:1032 | 5744481202332 | 5744481236016 | 33684 | NULL | NULL | NULL | NULL | NULL | 66654712 | 203 | WAIT | lock | NULL | NULL |
| 28 | 11167 | 11167 | wait/io/file/innodb/innodb_data_file | fil0fil.cc:5778 | 5744481464586 | 5744485206718 | 3742132 | NULL | NULL | /var/lib/mysql/bobby/test.ibd | NULL | FILE | 139648405042752 | 203 | WAIT | read | 16384 | NULL |
| 28 | 11168 | 11168 | wait/synch/mutex/innodb/buf_pool_mutex | buf0lru.cc:1320 | 5744485374336 | 5744485415238 | 40902 | NULL | NULL | NULL | NULL | NULL | 66655496 | 203 | WAIT | lock | NULL | NULL |
| 28 | 11169 | 11169 | wait/synch/mutex/innodb/buf_pool_mutex | buf0buf.cc:5150 | 5744485590876 | 5744485618144 | 27268 | NULL | NULL | NULL | NULL | NULL | 66655496 | 203 | WAIT | lock | NULL | NULL |
| 28 | 11170 | 11170 | wait/synch/sxlock/innodb/hash_table_locks | buf0buf.cc:5153 | 5744485730424 | 5744485815436 | 85012 | NULL | NULL | NULL | NULL | NULL | 70197624 | 203 | WAIT | exclusive_lock | NULL | NULL |
| 28 | 11171 | 11171 | wait/synch/mutex/innodb/fil_system_mutex | fil0fil.cc:1032 | 5744486328716 | 5744486357588 | 28872 | NULL | NULL | NULL | NULL | NULL | 66654712 | 203 | WAIT | lock | NULL | NULL |
| 28 | 11172 | 11172 | wait/io/file/innodb/innodb_data_file | fil0fil.cc:5778 | 5744486586960 | 5744490523176 | 3936216 | NULL | NULL | /var/lib/mysql/bobby/test.ibd | NULL | FILE | 139648405042752 | 203 | WAIT | read | 16384 | NULL |
|
... edited for length...
Most of the time seems to be taken up on reads from a file, which is what I expected. If you look at the wait/io/file/innodb/innodb_data_file waits they seem to be 16384 byte reads from the file associated with the table. I could use this information to build a query to show a profile of the time spent by the query based on EVENT_NAME. It would be kind of like a tkprof of an Oracle trace.
This post is just a simple first test. I am new to MySQL tuning so feel free to leave a comment or email me if you have suggestions or corrections related to this post.
Bobby
Please help understand expiration_secs in sys.dbms_lock.allocate_unique
Pages
