Caution: These tools are NOT for production use! Use with care.
The tools below I need sometimes for my consulting engagements...
Some other useful MySQL tools are:
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
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 allways 3 lines together into 1 line).
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.
Shows private and shareable memory of an LINUX process.
Allocates memory until crash. For 32/64-bit related memory problems.
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.
Prepares a script for altering all tables in a schema to an other storage engine (for example InnoDB to NDB).
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:
Very simple but nice little tool (test.sh) to test failover in HA setups. It simulates application behaviour. Point it to the VIP!
Tool to monitor vm of a process (vm_mon.sh). To find if memory is leaking or not.
Start as root with:
# nohup ./vm_mon.sh <pid> & |
Tool to read memory from other processes (read_process_memory.c) and to generate a *.CSV outout (read_process_memory.sh).
# read_process_memory.sh process keywoard interval times # read_process_memory.sh mysqld btr 5 100 # read_process_memory pid addr addr # read_process_memory 123 0x1234 0x1238
Tool to stress a bit your I/O system (to verify if drivers and caches are working optimal).