There are few easy ticks to see what is stuck inside MySQL instance. All these techniques will not give you whole picture, but might help to find queries that block performance. Let’s start from what exactly doing your MySQL server right now.
Which queries are running now?
This will give you an idea what’s running right now so you can find long running queries which slowing down MySQL and/or causing replication lag:
mysql -e "SHOW PROCESSLIST" | grep -v -i "sleep"
It is more convenient than just run “SHOW PROCESSLIST” as it’s hiding all connected threads in “Sleep” state so you’ll get a clean output. Also you can get same output but updating each second:
watch -n1 'mysql -e "SHOW FULL PROCESSLIST" | grep -v -i "Sleep"'
What to look for? This is complex output but you can start with Time and State columns. When you see a query running for more than one second it’s time for query review. If you sure it’s ok for query to be slow (like for queries in complex reports) you can move it away from critical server to special “reporting” replica.
When you see states like “Copying to tmp table” or any kinds of “Waiting …” like “Waiting for query cache lock” even if it’s fast then your server performance is less than stellar and it’s time to dig in.
How to collect bad qeuries
How to easily collect bad queries? Non-intrusive way is to listen MySQL network communications and dump all the data using tcpdump. This is extremely useful when you need to get an idea about most time consuming queries without touching MySQL configuration and query logging at all. This will also require Percona toolkit to be installed, but it’s worth it. Here is quick example. Let tcpdump run for some time and then run pt-query-digest to aggregate tcpdump output into readable report.
tcpdump -s 65535 -X -nn -q -tttt -i any port 3306 > mysql.tcp.txt pt-query-digest --type tcpdump ./mysql.tcp.txt > digest.log
This will produce report with most resource consuming queries on the top. Optimizing them one by one will improve speed or your MySQL instances, application response time and so give you a chance to handle more traffic with less hardware.
Which engines are in use?
Where also to look? Performance optimization techniques quite complex but depends on the engine that you use to store data. InnoDB, MyISAM, TokuDB – they all require different approach in query tunning and troubleshooting. So next step is to see how your data is distributed across the engines. In that case the following query will help:
SELECT engine, count(*) TABLES, concat(round(sum(table_rows)/1000000,2),'M') rows, concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') Size FROM information_schema.TABLES WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema') GROUP BY engine ORDER BY Size DESC;
Find all tables across all databases uses specific engine
Now you would probably like to see all tables that uses say MyISAM engine to store data. Here you go – example for MyISAM with big tables first.
SELECT concat(table_schema,'.',table_name), engine, concat(round(table_rows/1000000,2),'M') Rows, concat(round((data_length+index_length)/(1024*1024*1024),2),'G') Size FROM information_schema.TABLES WHERE engine = 'MyISAM' AND table_schema NOT IN ('mysql', 'information_schema', 'performance_schema') ORDER BY Size DESC;
Keeping your queries in a good shape could save you money on hardware and make your application fast but it’s also cause servers to do less work which means less electricity consumed and less CO2 and heat released to the atmosphere. So it is good thing to do whatever you believe on global warming or not