Is there a tool to filter the MySQL slow query log based on database?

Question

I'm currently trying to tune the performance of a single database (actually, a set of similar databases), but the slow query log has a bunch of data for other databases that I'm not currently interested in. I've been able to find a few slow-log filtering scripts, but nothing that actually seems to filter based on the database name.

Looking through the log file (for 5.0.18), the database name seems to be appearing for each block of queries, so it should be a fairly simple matter of programming to do this filtering (which is what it looks like I'll do), but why has no-one (that I can find) already done it if it is this easy?

For example:

# Time: 090226 11:17:04 # User@Host: user1[user1] @ host [10.0.0.3] # Query_time: 12 Lock_time: 0 Rows_sent: 2 Rows_examined: 4042564 use [db1]; SELECT ... # Time: 090226 11:17:34 # User@Host: user1[user1] @ host [10.0.0.3] # Query_time: 12 Lock_time: 0 Rows_sent: 0 Rows_examined: 4042560 SELECT ... # Time: 090226 12:32:40 # User@Host: user2[user2] @ host [10.0.0.3] # Query_time: 8 Lock_time: 0 Rows_sent: 123390 Rows_examined: 812841 use [db2]; SELECT ... 

the first two queries are both against the same database, so the single use statement covers them both.

Answer 1

you can use this little awk script to do the job:

awk '{ if ($0 ~ /use \[.*\];/) { if ($2 ~ /db1/) { found = 1; } else { found = 0; }} if (found == 1) { print $0; }}' <mysqllogfile> 

just replace db1 with the database name you are searching for. when you take your example from above, this script will give you:

use [db1]; SELECT ... # Time: 090226 11:17:34 # User@Host: user1[user1] @ host [10.0.0.3] # Query_time: 12 Lock_time: 0 Rows_sent: 0 Rows_examined: 4042560 SELECT ... # Time: 090226 12:32:40 # User@Host: user2[user2] @ host [10.0.0.3] # Query_time: 8 Lock_time: 0 Rows_sent: 123390 Rows_examined: 812841 

i don't know what is your operation system, but awk/gawk is available for multiple os.

Answer 2

I eventually found mysqlsla and, after a bit of patching (which I submitted back to them, but will happily put here if anyone wants it), got it do my bidding.