Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer

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.

share|improve this answer
    
There's a slight problem with this in that the # Time etc comments are in reference to the following query, so the current output from the awk script is giving some misleading info (should have 11:17:04, shouldn't have 12:32:40). I'd expect this to be fixable with some light jiggery-pokery, but I managed to find a suitable pre-existing solution. –  Cebjyre Feb 8 '10 at 8:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.