MySQL is generally an all-around kickass piece of software, and like any good open source application, there are a host of tools you can use to squeeze every last drop of goodness out of it. Nearly all of them, however, are geared towards the operational DBA, leaving the wayward developer out in the cold.
Lately I’ve been working on optimizing our stored procedure library which is primarily responsible for generating all sorts of fancy reports for the users. We use lots of nested procedure calls and finding potential targets for optimization is a tricky and time consuming job. Enter Ghetto Profile.
It’s still pretty basic but gets the job done. How’s it work?
ghetto_profile.rb --attach crappy_code.sql
mysql < crappy_code.sql
ghetto_profile.rb --stats -uroot -pmysql
The attach command simply wraps all stored procedure calls with debug statements which can later be analyzed using the stats command.
CALL debug.on('stored_proc1'); -- added by ghetto_profile
call stored_proc1(...params...);
CALL debug.off('stored_proc1'); -- added by ghetto_profile
And the output of the stats command is something like this:
total time: 3718.3220
stored_proc1
pct: 75.3873
total: 2803.1430 (46.72)
avg: 0.0895
count: 31327
min: 0.0860000000000001
max: 0.343
stored_proc2
pct: 22.9479
total: 853.2760 (14.22)
avg: 0.0273
count: 31240
min: 0.0
max: 0.371
stored_proc3
pct: 1.1224
total: 41.7340 (0.70)
avg: 0.0007
count: 59020
min: 0.0
max: 0.074
Wow, that stored_proc1 is taking up 75% of the time! Hacking ensues…
total time: 1151.3400
stored_proc1
pct: 0.4372
total: 5.0340 (0.08)
avg: 0.0001
count: 43892
min: 0.0
max: 0.007
Totally sweet! Ok, enough talk. Get some:
Ghetto Profile 0.7 (requires ruby, BSD licsensed) debug.tgz (helper package to setup the debug database and procs)