FromDual consulting tool collection

The following tools we use sometimes for our consulting engagements...

Caution: These tools are NOT for production use! Use with care!

Tools

• allocate.c
• backslashG2table.pl
• general2bench.pl
• memuse.pl
• memwaster.c
• MySQLprofiler
• pointer_size.c
• alter_engine.pl
• commit_demo.pl
• test.sh
• vm_mon.sh
• read_process_memory.c
• read_process_memory.sh
• cluster_initial_test.pl
• mem_map.pl
• csv_converter.pl
• mem_tracker.sh
• filesystem_table.php

Sample Databases

• MySQL world: world.tgz (92 kbyte, runs with version 4.1 (tested on 4.1.16))
• Oracle's scott/tiger for MySQL: scott_tiger.tgz (1.2 kbyte, runs with version 4.1 (tested on 4.1.16))
• Mondrian's FoodMart: FoodMart.tar.bz2 (2.9 Mbyte, runs with version 5.0 (tested on 5.0.18)) or the adapted FoodMart which seems also to work with 5.1.16 and even MySQL Cluster!
• FoodMart-2.tar.gz (13 Mbyte). Tested with MySQL 5.5, with much more data, newer date ranges and some other improvements...

Very evil memory reclaimer, tries to allocate specified amount of memory in smaller chunks, then releases everything (by exiting). By Domas Mituzas, MySQL AB, 2006

• allocate.c (1.3 kbyte)

Converts an output generated with \G (e.g. SHOW TABLE STATUS LIKE '%'\G) into a table like format. Can also be used to remove CR/LF characters (e.g. concatenate always 3 lines together into 1 line).

• backslashG2table-0.1.2.tgz (1.4 kbyte)
• backslashG2table-v0.1.1.tgz (1.4 kbyte)
• backslashG2table-v0.1.tgz (1020 byte)

Converts the output of the MySQL general lot into SQL statements which can be executed with the mysql client.

• general2bench-0.1.1.tgz (2.7 kbyte)
• general2bench-0.1.tgz (2.1 kbyte)

See also the MySQL General Log Parser by Gavin Towey.

Shows private and shareable memory of an LINUX process.

• memuse.tgz (526 byte)

Allocates memory until crash. For 32/64-bit related memory problems.

• memwaster.c (306 byte)

• mysql_tracer_profiler-0.1.2.tgz (24 kbyte)
• mysql_tracer_profiler-0.1.1.tgz (24 kbyte)
• mysql_tracer_profiler-0.1.tgz (24 kbyte)

General infos
-------------
Slots         :    19288
Time          :     1007.261 s
Interval      :        0.052 s
Filters       : none
Lines total   :    75426
Lines skipped :    34285
Lines matched :    41141

Commands
--------
Query                          30681       1602.228 s    74.6 %
Sleep                           9911        517.574 s    24.1 %
Connect                          522         27.260 s     1.3 %
Killed                            21          1.097 s     0.1 %
Quit                               6          0.313 s     0.0 %
-------------------------   --------   --------------   -------
Total                          41141       2148.471 s   100.0 %

State
-----
NULL                           19591       1023.084 s    47.6 %
Idling                          9900        516.999 s    24.1 %
Sending data                    9333        487.389 s    22.7 %
Copying to tmp table            1156         60.369 s     2.8 %
Reading from net                 467         24.388 s     1.1 %
Writing to net                   206         10.758 s     0.5 %
Sorting result                   185          9.661 s     0.4 %
statistics                        95          4.961 s     0.2 %
login                             45          2.350 s     0.1 %
preparing                         36          1.880 s     0.1 %
removing tmp table                25          1.306 s     0.1 %
init                              25          1.306 s     0.1 %
Creating tmp table                24          1.253 s     0.1 %
freeing items                     15          0.783 s     0.0 %
closing tables                    13          0.679 s     0.0 %
System lock                       13          0.679 s     0.0 %
Opening tables                     5          0.261 s     0.0 %
end                                4          0.209 s     0.0 %
logging slow query                 2          0.104 s     0.0 %
Locked                             1          0.052 s     0.0 %
-------------------------   --------   --------------   -------
Total                          41141       2148.471 s   100.0 %

Displays Pointer size of compiled programs.

• pointer_size.c (126 byte)

Prepares a script for altering all tables in a schema to an other storage engine (for example InnoDB to NDB).

• alter_engine.tgz (2380 byte), alter_engine-0.01-1.noarch.rpm (4992 byte), alter_engine.deb (3248 byte)

This script shows the impact of the frequency of a COMMIT during inserts and how data loads can get better performance.

• commit_demo.tgz (1.5 kbyte)

Here are some results you can compare with:

If you have some other measurements, please let me know so I can publish them...

That's the reason why I do not like SAN and why I do not recommend virtualization for highly loaded database systems:

Very simple but nice little tool: test.tgz (604 byte) to test failover in HA setups. It simulates application behavior. Point it to the VIP!

Tool to monitor virtual memory of a process: vm_mon.tgz (403 byte). To find if memory is leaking or not.

Start as root with:

# nohup ./vm_mon.sh <pid> &

Tool (1.1 kbyte) to read memory from other processes (read_process_memory.c) and to generate a *.CSV output (read_process_memory.sh).

# read_process_memory.sh process keyword interval times
# read_process_memory.sh mysqld btr 5 100

# read_process_memory pid addr addr
# read_process_memory 123 0x1234 0x1238

Tool (694 byte) to stress a bit your I/O system (to verify if drivers and caches are working optimal).

See the memory mapping (mem_map.pl (756 byte)) of a process:

# ./mem_map.pl 9214

One big chunk                    rw-p   228428 Kbyte
One big chunk                    rw-p    22088 Kbyte
One big chunk                    rw-p    15844 Kbyte
One big chunk                    rw-p     9396 Kbyte
Several different small chunks   rw-p     7328 Kbyte
Several different small chunks   ---p      124 Kbyte

/lib/...                         r-xp     1684 Kbyte
/lib/...                         rw-p       60 Kbyte
ndbd                             r-xp     2464 Kbyte
ndbd                             rw-p     1136 Kbyte
[heap]                           rw-p     3688 Kbyte
[stack]                          rw-p       88 Kbyte
[vdso]                           ---p        4 Kbyte

share   =     4148 Kbyte
private =   288184 Kbyte
total   =   292332 Kbyte (1.42 % shareable)

A Tool (336 byte) to convert normal CSV files into MySQL CSV files.

This tool is useful if you want to track the memory consumption of a single process: mem_tracker.sh

A Tool (6.2 kbyte) which helps you to find out, when a MySQL table was last read or written...

Some useful third party tools

Some other useful MySQL tools are:

• Slow Query Log in Milliseconds
• mysql_explain_log and mysql_explain_slow_log
• MyTop
• InnoTop
• MySQL Visual Explain