Chip Turner, been @ Google approximately 1.5 years.
http://code.google.com/p/google-mysql-tools
Right now you have to click “source” and then browse the subversion tree’s trunk.
These are tools written at Google that are frequently used, and perhaps useful for other folks.
So what’s there, how does Google use the code, and how can other folks use it?
mypgrep is to show and kill processes among many database servers. Perhaps from certain users, IP addresses, how often certain queries are being run, long-running queries, if it’s locked, connectionless queries — anything you’d use show processlist or show full processlist on.
Uses threads to talk to multiple MySQL databases in parallel to try to be fast, but it’s pretty standard Python code.
Connectionless queries are those that have been killed, but MySQL doesn’t check for the connection until it tries to send rows back — I know I’ve had this issue a lot, when I kill a query but it still runs, I’d like it to just die.
You can specify it to include or not include replication queries.
compact_innodb uses mysqldump and reload with checks to make sure that stuff doesn’t corrupt. This defragments InnoDB tables (and others). Done offline.
Dumps all the innodb tables, then drops the tables, stops mysql, deletes the innodb datafile and logs, and then restarts mysql and reload the dumps. They’ve found the defragmentation works well.
Can do only the dump or only the restore if you want.
It does your largest tables first, to maximize I/O bandwidth.
Why not ALTER TABLE? Takes about the same amount of time, so you can dump and restore to and from different machines. Seems to be more reliable, you can kill the process without worrying about data corruption.
Why not OPTIMIZE? If you use one innodb file per table and autoextend, you can reclaim the space used by the fragmentation. Of course, if you don’t use one innodb data file per table, and just have a hard limit, then it’s not going to reclaim space.
When is it time to defragment? One answer: Disk busy time vs. queries per second — when the disk starts to take a lot longer to seek with the same # of queries per second, it’s time to defragment.
compat_* are the compatibility libraries.
Interestingly, making the source open was easy — Google gave approval, so the work was mostly removing comments that were to-do lists for folks that no longer work there.
Other useful tools?
One way to speed up replication is to start some processes which will do all physical I/O that replication threads are about to do, so the stuff to be updated is already in the cache. If your slave has enough disk drives, and you can use iostat and see that the disks are 10% busy, it probably means that you’re I/O bound by the serialized nature of replication, and you can use this method to decrease the time. If your disks are 100% busy AND I/O bound, it won’t help. This will be on the page linked above sometime next week.
Basically, it reads the binary logs, trying not to get too far ahead, and issues the queries so the disk can cache the right data.
Comments are closed.