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?
1 2 3
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: