Skip to content

Instantly share code, notes, and snippets.

@yoku0825

yoku0825/gist:1a3db88d23ec20704359

Created March 12, 2015 09:55
Show Gist options
  • Save yoku0825/1a3db88d23ec20704359 to your computer and use it in GitHub Desktop.
Save yoku0825/1a3db88d23ec20704359 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
mysql> SELECT * FROM setup_instruments WHERE name LIKE '%query%cache%';
+------------------------------------------------------------+---------+-------+
| NAME | ENABLED | TIMED |
+------------------------------------------------------------+---------+-------+
| wait/synch/mutex/sql/Query_cache::structure_guard_mutex | NO | NO |
| wait/synch/rwlock/sql/Query_cache_query::lock | NO | NO |
| wait/synch/cond/sql/Query_cache::COND_cache_status_changed | NO | NO |
| stage/sql/checking query cache for query | NO | NO |
| stage/sql/invalidating query cache entries (table) | NO | NO |
| stage/sql/invalidating query cache entries (table list) | NO | NO |
| stage/sql/storing result in query cache | NO | NO |
| stage/sql/Waiting for query cache lock | NO | NO |
| memory/sql/Query_cache | NO | NO |
+------------------------------------------------------------+---------+-------+
9 rows in set (0.00 sec)
mysql> UPDATE setup_instruments SET enabled= 'YES', TIMED= 'YES' WHERE name LIKE '%query%cache%';
Query OK, 9 rows affected (0.02 sec)
Rows matched: 9 Changed: 9 Warnings: 0
mysql> SELECT * FROM setup_instruments WHERE name LIKE '%query%cache%';
+------------------------------------------------------------+---------+-------+
| NAME | ENABLED | TIMED |
+------------------------------------------------------------+---------+-------+
| wait/synch/mutex/sql/Query_cache::structure_guard_mutex | YES | YES |
| wait/synch/rwlock/sql/Query_cache_query::lock | YES | YES |
| wait/synch/cond/sql/Query_cache::COND_cache_status_changed | YES | YES |
| stage/sql/checking query cache for query | YES | YES |
| stage/sql/invalidating query cache entries (table) | YES | YES |
| stage/sql/invalidating query cache entries (table list) | YES | YES |
| stage/sql/storing result in query cache | YES | YES |
| stage/sql/Waiting for query cache lock | YES | YES |
| memory/sql/Query_cache | YES | YES |
+------------------------------------------------------------+---------+-------+
9 rows in set (0.00 sec)
mysql> SELECT @@query_cache_type;
+--------------------+
| @@query_cache_type |
+--------------------+
| ON |
+--------------------+
1 row in set (0.00 sec)
$ perl qcache.pl &
mysql> SELECT event_name, SUM(count_star) AS count_star, SUM(sum_timer_wait) AS sum_timer_wait, AVG(avg_timer_wait) AS avg_timer_wait, MAX(max_timer_wait) AS max_timer_wait FROM events_waits_summary_by_instance GROUP BY event_name ORDER BY avg_timer_wait DESC;
+-----------------------------------------------+------------+----------------+----------------+----------------+
| event_name | count_star | sum_timer_wait | avg_timer_wait | max_timer_wait |
+-----------------------------------------------+------------+----------------+----------------+----------------+
| wait/io/file/innodb/innodb_log_file | 20 | 31474853795 | 926782972.5000 | 14957273485 |
| wait/io/file/sql/ERRMSG | 5 | 579503540 | 115900400.0000 | 396543070 |
| wait/io/file/sql/FRM | 1417 | 98079448775 | 69216073.5321 | 20218959915 |
| wait/io/file/sql/binlog | 10389 | 21547813980 | 53271757.0000 | 944297585 |
| wait/io/file/sql/binlog_index | 5 | 222388705 | 44477510.0000 | 214555495 |
| wait/io/file/sql/slow_log | 229 | 9996943880 | 43654765.0000 | 6491175075 |
| wait/io/file/innodb/innodb_data_file | 4557 | 677927760895 | 21570604.2391 | 27752754645 |
| wait/io/file/sql/pid | 3 | 37722300 | 12574100.0000 | 28816865 |
| wait/io/file/mysys/cnf | 5 | 49880600 | 9976120.0000 | 27500550 |
| wait/io/file/sql/dbopt | 8 | 79057440 | 9881987.5000 | 41062175 |
| wait/io/file/mysys/charset | 3 | 21808710 | 7269570.0000 | 10842370 |
| wait/io/file/myisam/dfile | 23 | 62741910 | 4360606.2500 | 9234995 |
| wait/io/file/myisam/kfile | 33 | 112245980 | 3423708.7500 | 16324770 |
| wait/synch/rwlock/sql/Query_cache_query::lock | 655 | 132254815 | 198571.0222 | 972895 |
+-----------------------------------------------+------------+----------------+----------------+----------------+
14 rows in set (0.01 sec)
# kill and restart mysqld
mysql> SELECT @@query_cache_type;
+--------------------+
| @@query_cache_type |
+--------------------+
| DEMAND |
+--------------------+
1 row in set (0.00 sec)
$ perl qcache.pl &
mysql> SELECT event_name, SUM(count_star) AS count_star, SUM(sum_timer_wait) AS sum_timer_wait, AVG(avg_timer_wait) AS avg_timer_wait, MAX(max_timer_wait) AS max_timer_wait FROM events_waits_summary_by_instance GROUP BY event_name ORDER BY sum_timer_wait DESC;
+-----------------------------------------------+------------+----------------+----------------+----------------+
| event_name | count_star | sum_timer_wait | avg_timer_wait | max_timer_wait |
+-----------------------------------------------+------------+----------------+----------------+----------------+
| wait/io/file/innodb/innodb_data_file | 4557 | 576099763855 | 20682032.6087 | 21135731155 |
| wait/io/file/sql/binlog | 10399 | 19945383920 | 39200975.0000 | 900873050 |
| wait/io/file/innodb/innodb_log_file | 20 | 19594100295 | 577253407.5000 | 9017002610 |
| wait/io/file/sql/slow_log | 289 | 17984976625 | 62231400.0000 | 5188555295 |
| wait/io/file/sql/FRM | 1417 | 1753808980 | 1237538.3486 | 33507705 |
| wait/io/file/sql/ERRMSG | 5 | 394378985 | 78875720.0000 | 372033970 |
| wait/io/file/sql/binlog_index | 5 | 196443555 | 39288480.0000 | 189432705 |
| wait/synch/rwlock/sql/Query_cache_query::lock | 847 | 171442810 | 199817.6644 | 869330 |
| wait/io/file/myisam/kfile | 33 | 109026225 | 3287803.7500 | 23883860 |
| wait/io/file/mysys/cnf | 5 | 73896130 | 14778995.0000 | 52824310 |
| wait/io/file/myisam/dfile | 23 | 66950730 | 4238031.8750 | 10758825 |
| wait/io/file/sql/dbopt | 8 | 62477030 | 7809532.5000 | 30907415 |
| wait/io/file/sql/pid | 3 | 49144480 | 16381365.0000 | 38978940 |
| wait/io/file/mysys/charset | 3 | 30123940 | 10041185.0000 | 19996515 |
+-----------------------------------------------+------------+----------------+----------------+----------------+
14 rows in set (0.00 sec)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment