You are here

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

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...

allocate.c

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

backslashG2table.pl

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).

general2bench.pl

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

See also the MySQL General Log Parser by Gavin Towey.

memuse.pl

Shows private and shareable memory of an LINUX process.

memwaster.c

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

MySQLprofiler

A tool which uses SHOW PROCESSLIST to generate a trace file (tracer). This trace file can be profiled by the script profiler. Can help sometimes to find the problem in your MySQL database.
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 %

pointer_size.c

Displays Pointer size of compiled programs.

alter_engine.pl

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

commit_demo.pl

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

Here are some results you can compare with:

# Time
[s]
trx
[ms/trx]
theorie
[ms/trx]
Comment Conditions
1 12.3 1.2 8.3 My Laptop with very good disk cache :-) ext3, 1 disk (7.2k rpm) for data and trx log, no binary logging, sync_binlog=0
2 27.2 2.7 2.5 The SAN itself should be able to make up to 2000 I/O per second. ext3, unknown amount of disks for data and trx log, no binary logging, sync_binlog=0 on VMware ESX server, 400 I/O per second where for our VM. Some peaks show 10 x longer trx time!!!
3 42.2 4.2 4.0 2.5% slower with DRBD!!! ext3, DRBD protocol C, 3 disk RAID 5 (10k rpm) for data, 2 disk RAID 1 (15k rpm) for trx log, 1 Gbit bonded network full duplex, no binary logging, sync_binlog=0
4 41.0 4.1 4.0 ext3, 3 disk RAID 5 (10k rpm) for data, 2 disk RAID 1 (15k rpm) for trx log, no binary logging, sync_binlog=0
5 157 15.7 4.0 Default xfs formating parameters where uses. We think that for this test something was wrong! xfs, DRBD protocol C, 3 disk RAID 5 (10k rpm) for data, 2 disk RAID 1 (15k rpm) for trx log, 100 Mbit bonded network full duplex, no binary logging, sync_binlog=0
6 146 14.6 4.0 Default xfs formating parameters where uses. We think that for this test something was wrong! xfs, DRBD protocol C, 3 disk RAID 5 (10k rpm) for data, 2 disk RAID 1 (15k rpm) for trx log, 1 Gbit bonded network full duplex, no binary logging, sync_binlog=0
7 108 10.8 4.0 Default xfs formating parameters where uses. We think that for this test something was wrong! xfs, DRBD protocol C in stand alone mode, 3 disk RAID 5 (10k rpm) for data, 2 disk RAID 1 (15k rpm) for trx log, 1 Gbit bonded network full duplex, no binary logging, sync_binlog=0
8 86.4 8.6 8.0 with binary logging!!! ext3, DRBD protocol C, 3 disk RAID 5 (10k rpm) for data, 2 disk RAID 1 (15k rpm) for trx log, 1 Gbit bonded network full duplex, with binary logging, sync_binlog=0
9 565 56.5 8.0 sync_binlog=1!!! ext3, DRBD protocol C, 3 disk RAID 5 (10k rpm) for data, 2 disk RAID 1 (15k rpm) for trx log, 1 Gbit bonded network full duplex, with binary logging, sync_binlog=1

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:

Transaction time over 8 h in a VM on a SAN

test.sh

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!

vm_mon.sh

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> &

read_process_memory

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

cluster_initial_test.pl

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

mem_map.pl

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)

csv_converter.pl

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

mem_tracker.sh

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

Memory consumption Memory consumption

filesystem_table.php

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: