How to find un-indexed queries in MySQL, without using the log
You probably know that it’s possible to set configuration variables to log queries that don’t use indexes to the slow query log in MySQL. This is a good way to find tables that might need indexes.
But what if the slow query log isn’t enabled and you are using (or consulting on) MySQL 5.0 or earlier, where it can’t be enabled on the fly unless you’re using a patched server such as Percona’s enhanced builds? You can still capture these queries.
The key is knowing what it really means for a query to “not use an index.” There are two conditions that trigger this — not using an index at all, or not using a “good” index. Both of these set a bit. If either bit is set, the query is captured by the filter and logged. Both of these bits also set a corresponding bit in the protocol, so the TCP response to the client actually says “here comes the result of your query, and by the way it didn’t use an index.” This is very useful information.
I’m sure you can see where this is going. Let’s use tcpdump to capture queries, consume the output with mk-query-digest, and filter out all but ones that don’t use an index or use no good index:
$ sudo tcpdump -i lo port 3306 -s 65535 -x -n -q -tttt \
| mk-query-digest --type tcpdump \
--filter '($event->{No_index_used} eq "Yes" || $event->{No_good_index_used} eq "Yes")'
If I run a few full table scans now, and then cancel mk-query-digest, I’ll get output like the following (abbreviated for clarity):
# pct total min max avg 95% stddev median # Count 100 8 # Exec time 100 5ms 511us 857us 604us 839us 106us 582us # 100% (8) No_index_used select * from t\G
You can see I ran the query 8 times and each time it reported back that it didn’t use an index. This is a dead-easy way to find queries that might not have an index available!
Want to print out tables from those queries? You can do that too. Just add --group-by tables --report-format profile to the command above, and instead of grouping queries together by the query text, it’ll group them by the tables they mention. Then the report will contain one item per table and you’ll just see a summary at the end, like so:
# Rank Query ID Response time Calls R/Call Item # ==== ================== ================ ======= ========== ==== # 1 0x 0.0037 100.0% 8 0.000467 test.t
Aha, looks like test.t is the problem table!
This is very cool. :)
I tried this on our production platform and got alot of warnings from mk_query_digest like:
# mk_query_digest:6758 23762 Illegal hexadecimal digit ‘h’ ignored at /usr/bin/mk-query-digest line 2649, chunk 85.
#
# mk_query_digest:6758 23762 Illegal hexadecimal digit ‘.’ ignored at /usr/bin/mk-query-digest line 2649, chunk 86.
At the end I also get some noise:
# Tables
# SHOW TABLE STATUS LIKE ‘astdb’\G
# SHOW CREATE TABLE `astdb`\G
insert ®îîâ|ëÞinto astdb (fami’ØêÍÛèúbly,astkey,value)RÊÍN,ú^é values (‘CHANSTï¥îÎÁ§ÍATE’,’072413′,’1çÇ$|q\G
# Rank Query ID Response time Calls R/Call Item
# ==== ================== ================ ======= ========== ====
# 1 0x2D1239501CFD190E 0.0003 100.0% 1 0.000265 INSERT astdb
MySQL version 5.0.18 if that is important.
Morten Isaksen
18 Aug 09 at 8:00 am
Morten, can you post a bug report and sample of your tcpdump output (if possible) to the Maatkit mailing list and/or the bug list on code.google.com/p/maatkit?
The Maatkit docs show an example of the required format of the tcpdump output, which sometimes needs different options to achieve, depending on the OS and release.
Xaprb
18 Aug 09 at 8:57 am
slight typo… there shouldn’t be a pipe (|) before –filter on the third line of the command.
Tom Krouper
18 Aug 09 at 2:57 pm
Ah, thanks — of course I was running it all on one line and just broke the lines for formatting. Blogging at 4:30AM…
Xaprb
19 Aug 09 at 8:08 am
[...] Delade How to find un-indexed queries in MySQL, using tcpdump [...]
Dagbok för 19 August 2009 | En sur karamell
19 Aug 09 at 4:04 pm
Interesting. It’d be cool if this data was accessible from the various language clients too (like Perl’s DBD::mysql) as an attribute. That way we could slip some monitoring code into our centralized query libraries and suddenly start gathering interesting stats!
Jeremy Zawodny
20 Aug 09 at 12:35 pm
It’s actually available in the PHP libraries, and could be in the DBD::mysql ones too. Not too long ago CaptainTofu added $sth->{mysql_warning_count} to DBD::mysql. I remember having to upgrade to get it. We wanted to start capturing that to catch problems with some configuration changes… they originally set mysql up to accept the usual invalid input etc and I changed it.
Xaprb
20 Aug 09 at 5:41 pm
[...] How to find un-indexed queries in MySQL, without using the log [...]
Destillat KW34-2009 | duetsch.info - GNU/Linux, Open Source, Softwareentwicklung, Selbstmanagement, Vim ...
21 Aug 09 at 3:55 am
Is there a similar way to find unindexed queries with mysql that works only on linux sockets ?
januzi
28 Aug 09 at 8:46 am
I haven’t looked into sniffing queries from Unix sockets, and I don’t know whether it’s even possible.
Xaprb
29 Aug 09 at 9:37 pm
[...] How to find un-indexed queries in MySQL, without using the log at Xaprb (tags: mysql database debug) [...]
links for 2009-08-31 « toonz
31 Aug 09 at 7:13 pm
[...] We have a big project in progress using MySQL. We located a useful tip at Xaprb.com article “How to Find Un-Indexed Queries in MySQL, without Using the Log.” The write up includes a useful script. A happy quack to the Percona wizard who shared this [...]
MySQL Tip… Finding Un Indexed Queries : Beyond Search
1 Sep 09 at 7:05 am
I have been trying to figure out, is there a limit on the size of the index that can be created on a table. Can there be a case where queries lost performance because of the index sizes actually were so heavy that it took a huge time to load. I made a test table with 500K rows and 22 columns with mostly float variables and the interesting thing I noticed that index creation took more and more time for index creation.
I don’t think this is the right place to put this query up, but I hope you could reply to it personally or even here. Thank you.
rahulheinaa
29 Jan 10 at 1:31 pm
Hi,
I have executed the commands exactly as said above and tcpdump is waiting for capture.
As the above forum, it specified as cancal mk-query-digest. Not sure how to do that, but did by pressing control-c
Able to see the below message post that.. can any one help me out please
# Caught SIGINT.
0 packets captured
0 packets received by filter
0 packets dropped by kernel
The command I tried is —
————
tcpdump -i lo port 3306 -s 65535 -x -n -q -tttt | mk-query-digest –type tcpdump –filter ‘($event->{No_index_used} eq “Yes” || $event->{No_good_index_used} eq “Yes”)’ –group-by tables –report-format profile
————
Thanks
Raaj
raja
12 May 11 at 12:34 am