OurSQL Episode 140: More Performance

This week we explain performance_schema a bit deeper. In Ear Candy, we talk about max_binlog_cache_size and At the Movies presents Max Mether of SkySQL talking about "High Availability Solutions for MySQL".

Events
DB Hangops - every other Wednesay at noon Pacific time
Upcoming MySQL events

Training
SkySQL Trainings
Tungsten University trainings
Oracle's MySQL Trainings
Percona Trainings

Performance Schema, part 2
Danil Zburivsky gave a great talk at Percona Live about
Performance Schema in MySQL: Debugging performance issue and getting deeper insight into server's behavior

pre-filtering and post-filtering

Turn off CSV table monitoring of timing:
UPDATE setup_instruments SET timed = 'NO' WHERE name like '%csv%';

Completely eliminate all CSV instruments:
UPDATE setup_instruments SET enabled = 'NO'  WHERE name LIKE '%csv%';

Disable all instruments:
UPDATE setup_instruments SET enabled = 'NO';

Enable only I/O monitoring:
UPDATE setup_instruments SET enabled = 'YES' WHERE name LIKE '%/io%';

Example from the MySQL Manual, which uses more complicated SQL (an IF statement)
UPDATE setup_instruments SET ENABLED = IF(NAME LIKE 'wait/io/file/%', 'NO', 'YES');

Disable history tables:
UPDATE setup_consumers SET enabled = 'NO' WHERE name LIKE '%history%';

events_wait_current table manual page

events_waits_history_size value - 10 by default.

events_waits_history_long_size value - 10,000 by default

event wait tables

event wait summary tables in the MySQL manual

Top 5 most frequent wait events:

SELECT * FROM events_waits_summary_global_by_event_name
WHERE count_star > 0 ORDER BY count_star DESC limit 5\G

Example from a Bugzilla server that the top most frequent wait event is a btr_search_latch. Recall that time is in picosends (1000000000000 (trillion) picoseconds = 1 second)

*************************** 1. row ***************************
    EVENT_NAME: wait/synch/rwlock/innodb/btr_search_latch
    COUNT_STAR: 30606189102
SUM_TIMER_WAIT: 1232255742140540
MIN_TIMER_WAIT: 18564
AVG_TIMER_WAIT: 40261
MAX_TIMER_WAIT: 221596277890

Mark Leith's article on mutex locks, and how btr_search_latch is " the synchronization point for the InnoDB Adaptive Hash Index"

Find events that have been waiting the most:
SELECT * FROM events_waits_summary_global_by_event_name ORDER BY sum_timer_wait DESC limit 5\G

File I/O summary tables in MySQL 5.5
File I/O summary tables in MySQL 5.6

Find the event name that waits the most:

MariaDB [performance_schema]> SELECT event_name FROM file_summary_by_event_name ORDER BY count_read DESC LIMIT 1;
+--------------------------------------+
| event_name                           |
+--------------------------------------+
| wait/io/file/innodb/innodb_data_file |
+--------------------------------------+

Use that string to query the file_summary_by_instance table to see all the files affected by that event:

MariaDB [performance_schema]> SELECT file_name FROM file_summary_by_instance
WHERE event_name = 'wait/io/file/innodb/innodb_data_file';
+------------------------------------------------+
| file_name                                      |
+------------------------------------------------+
| /var/lib/mysql/my_fks/mytable_inno.ibd         |
| /var/lib/mysql/my_fks/etl_download_control.ibd |
| /var/lib/mysql/ibdata1                         |
+------------------------------------------------+

Some raw data from bugzilla servers - first, the top 5 by COUNT_WRITE:

MariaDB [performance_schema]> SELECT * FROM file_summary_by_event_name
ORDER BY COUNT_WRITE DESC limit 5\G
*************************** 1. row ***************************
               EVENT_NAME: wait/io/file/innodb/innodb_log_file
               COUNT_READ: 6
              COUNT_WRITE: 15497630
SUM_NUMBER_OF_BYTES_READ: 69632
SUM_NUMBER_OF_BYTES_WRITE: 56373471232
*************************** 2. row ***************************
               EVENT_NAME: wait/io/file/aria/MAI
               COUNT_READ: 1233229
              COUNT_WRITE: 15366900
SUM_NUMBER_OF_BYTES_READ: 491022284
SUM_NUMBER_OF_BYTES_WRITE: 577624076
*************************** 3. row ***************************
               EVENT_NAME: wait/io/file/sql/relaylog
               COUNT_READ: 9954613
              COUNT_WRITE: 14276452
SUM_NUMBER_OF_BYTES_READ: 14866413342
SUM_NUMBER_OF_BYTES_WRITE: 14866307811
*************************** 4. row ***************************
               EVENT_NAME: wait/io/file/sql/master_info
               COUNT_READ: 0
              COUNT_WRITE: 13888151
SUM_NUMBER_OF_BYTES_READ: 0
SUM_NUMBER_OF_BYTES_WRITE: 1526850134
*************************** 5. row ***************************
               EVENT_NAME: wait/io/file/innodb/innodb_data_file
               COUNT_READ: 38565044
              COUNT_WRITE: 6463126
SUM_NUMBER_OF_BYTES_READ: 631852498944
SUM_NUMBER_OF_BYTES_WRITE: 316807413760
5 rows in set (0.00 sec)

And then by COUNT_READ:

MariaDB [performance_schema]> SELECT * FROM file_summary_by_event_name
ORDER BY COUNT_READ DESC limit 5\G
*************************** 1. row ***************************
               EVENT_NAME: wait/io/file/myisam/dfile
               COUNT_READ: 1809256185
              COUNT_WRITE: 2965886
SUM_NUMBER_OF_BYTES_READ: 2527870679350
SUM_NUMBER_OF_BYTES_WRITE: 3547570604
*************************** 2. row ***************************
               EVENT_NAME: wait/io/file/aria/MAD
               COUNT_READ: 150850634
              COUNT_WRITE: 14512
SUM_NUMBER_OF_BYTES_READ: 11986672551
SUM_NUMBER_OF_BYTES_WRITE: 800649588
*************************** 3. row ***************************
               EVENT_NAME: wait/io/file/innodb/innodb_data_file
               COUNT_READ: 38567671
              COUNT_WRITE: 6463126
SUM_NUMBER_OF_BYTES_READ: 631895539712
SUM_NUMBER_OF_BYTES_WRITE: 316807413760
*************************** 4. row ***************************
               EVENT_NAME: wait/io/file/sql/binlog
               COUNT_READ: 36141966
              COUNT_WRITE: 4652779
SUM_NUMBER_OF_BYTES_READ: 44434141719
SUM_NUMBER_OF_BYTES_WRITE: 14833540004
*************************** 5. row ***************************
               EVENT_NAME: wait/io/file/sql/relaylog
               COUNT_READ: 9954630
              COUNT_WRITE: 14276715
SUM_NUMBER_OF_BYTES_READ: 14866560798
SUM_NUMBER_OF_BYTES_WRITE: 14866570788
5 rows in set (0.00 sec)

Query what threads have an open mutex lock:
SELECT * FROM mutex_instances WHERE locked_by_thread_id IS NOT NULL\G

For example, on a running Bugzilla server:

MariaDB [performance_schema]> SELECT * FROM mutex_instances
WHERE locked_by_thread_id IS NOT NULL\G
*************************** 1. row ***************************
                 NAME: wait/synch/mutex/sql/LOCK_thread_count
OBJECT_INSTANCE_BEGIN: 18286240
  LOCKED_BY_THREAD_ID: 665255
*************************** 2. row ***************************
                 NAME: wait/synch/mutex/sql/LOG::LOCK_log
OBJECT_INSTANCE_BEGIN: 18470568
  LOCKED_BY_THREAD_ID: 637026
*************************** 3. row ***************************
                 NAME: wait/synch/mutex/aria/SERVICE_THREAD_CONTROL::LOCK_control
OBJECT_INSTANCE_BEGIN: 26897856
  LOCKED_BY_THREAD_ID: 17
3 rows in set (0.01 sec)

Files with the biggest open_count values:

MariaDB [performance_schema]> SELECT * FROM file_instances
ORDER BY open_count DESC LIMIT 5;
+---------------------------------------+--------------------------------------+------------+
| FILE_NAME                             | EVENT_NAME                           | OPEN_COUNT |
+---------------------------------------+--------------------------------------+------------+
| /var/lib/mysql/bugs/bugs_fulltext.MYD | wait/io/file/myisam/dfile            |         11 |
| /var/lib/mysql/bugs/#sql2-6f07-2.ibd  | wait/io/file/innodb/innodb_data_file |          8 |
| /var/lib/mysql/bugs/bugs.ibd          | wait/io/file/innodb/innodb_data_file |          7 |
| /var/lib/mysql/bugs/bugs.frm          | wait/io/file/sql/FRM                 |          6 |
| /var/lib/mysql/bugzilla1-bin.000207   | wait/io/file/sql/binlog              |          4 |
+---------------------------------------+--------------------------------------+------------+
5 rows in set (0.00 sec)

Find out which threads are write locked:
SELECT * FROM rwlock_instances WHERE write_locked_by_thread_id IS NOT NULL;

(on a running Bugzilla server, nothing has this lock, so the example was empty set)

Find out which threads are read locked:

MariaDB [performance_schema]> select * from rwlock_instances order by read_locked_by_count desc limit 5\G
*************************** 1. row ***************************
                     NAME: wait/synch/rwlock/sql/LOCK_grant
    OBJECT_INSTANCE_BEGIN: 18287520
WRITE_LOCKED_BY_THREAD_ID: NULL
     READ_LOCKED_BY_COUNT: 4032
*************************** 2. row ***************************
                     NAME: wait/synch/rwlock/innodb/dict_table_stats
    OBJECT_INSTANCE_BEGIN: 18493264
WRITE_LOCKED_BY_THREAD_ID: NULL
     READ_LOCKED_BY_COUNT: 2603
*************************** 3. row ***************************
                     NAME: wait/synch/rwlock/innodb/dict_table_stats
    OBJECT_INSTANCE_BEGIN: 18490864
WRITE_LOCKED_BY_THREAD_ID: NULL
     READ_LOCKED_BY_COUNT: 1831
*************************** 4. row ***************************
                     NAME: wait/synch/rwlock/innodb/dict_table_stats
    OBJECT_INSTANCE_BEGIN: 18495904
WRITE_LOCKED_BY_THREAD_ID: NULL
     READ_LOCKED_BY_COUNT: 1417
*************************** 5. row ***************************
                     NAME: wait/synch/rwlock/innodb/dict_table_stats
    OBJECT_INSTANCE_BEGIN: 18496024
WRITE_LOCKED_BY_THREAD_ID: NULL
     READ_LOCKED_BY_COUNT: 852
5 rows in set (0.01 sec)

threads table in the performance_schema, which can be joined with INFORMATION_SCHEMA.PROCESSLIST to get more information about the processlist.

Ear Candy
In this week's ear candy we talk about what max_binlog_cache_size really is, and some interesting discrepancies in the documentation.

At the Movies
This week in At the Movies we highlight Max Mether of SkySQL presenting High Availability Solutions for MySQL, a talk he gave at both Scale in Los Angeles and Open Database Camp 2 months ago.

Feedback
Facebook group
Google+ page
e-mail: podcast at technocation.org
voicemail using phone/Skype: +1-617-674-2369
twitter: @oursqlcast
or Tweet about @oursqlcast

Trackback URL for this post:

http://technocation.org/trackback/1245