Tools from Google

Chip Turner is giving a presentation on some of the tools used by Google.

[root@srv31 ~]# mkdir /google
[root@srv31 ~]# cd /google/
[root@srv31 google]# svn checkout http://google-mysql-tools.googlecode.com/svn/t                                                                             runk/ google-mysql-tools
A  google-mysql-tools/compat_logging.py
A  google-mysql-tools/compact_innodb.py
A  google-mysql-tools/compat_flags.py
A  google-mysql-tools/thread_pool.py
A  google-mysql-tools/COPYING
A  google-mysql-tools/mypgrep.py
A  google-mysql-tools/config_helper.py
A  google-mysql-tools/dbspec_lib.py
A  google-mysql-tools/command_pool.py
Checked out revision 2.

Tools that people keep re-writing.

All the code is in Python and released under Apache license.

mypgrep (mytop + grep)

If you have more than 5 dbs then it gets kinda difficult to monitor.
Find queries coming from certain IPs etc.

compactinoodb: deals with fragmentation. 20% to 30% increase in speeds becuase the data gets sequential. Must be done offline. Do it on replica and fail-over to replica. If using one large tablespace file instead of per table tablespace then also you can gain performance.

How often do you fragment? Not too often. It depends on how often you use drop table or deletes.

Another way: Change table to MyISAM and then to InnoDB. But this cannot be done on separate servers. You can kill the dump and import. Killing the alter can be bad.

OPTIMIZE/ANALYZE doesn't free up space. It does defragment but doesn't

Google running 4.0 in a lot of places and "cannot use tablespace per table" in all cases.

ERIC: Check how busy disk is compared to QPS and then decide whether to OPTIMIZE.