Benchmarking MySQL Server

Before proceeding to production on a complex database server, it is always necessary to benchmark your server. There are different tools available for this purpose like that of Todd.

There is a tool in MySQL itself. Though primitive, it is interesting to try it out. The MySQL Benchmark Tool, sql-bench, is a perl program, which can be found in the folder sql-bench under the MySQL program folder.

Installation of Perl DBD/DBI for MySQL

On Windows, you should do the following to install the MySQL DBD module and DBI with ActiveState Perl.  Get ActiveState Perl 5.6 or later from

http://www.activestate.com/Products/ActivePerl/

and install it. Open a command window.  Type ppm at the command prompt. The Perl Package Manager starts as follows: 

 

Here you may add new packages. Add the package DBD-MySQL, the MySQL Driver for Perl Database Interface (DBI). Then add the package DBI also. 

Start the MySQL Benchmark Tool 

At sql-bench directory of MySQL, run the following command: 

Sql-bench:\> perl run-all-tests – -host {host name} - -user root – -password {password}

See below one of the sample screens it produces on Innodb stress test:

Testing server ‘MySQL 5.0.36sp1 enterprise gpl nt log/’ at 2007-0627 14:15:44 Innotest2: MySQL/InnoDB stress test in Perl for FOREIGN keys This is a randomized stress test for concurrent inserts, updates, deletes, commits and rollbacks with foreign keys with the ON DELETE … clause. The test will generatealso a lot of deadlocks, duplicate key errors, and other SQL errors.You should run innotest2, innotest2a, and innotest2b concurrently.The thing to watch is that the server does not crash or does not print to the .err log anything. Currently, due to a buglet in MySQL, warnings about MySQL lock reservations can appear in the .err log. The test will run very long, even several hours. You can kill the perl processes running this test at any time and do CHECK TABLE on tables innotest2a, b, c, d in the ‘test’ database.

 

 

 

 

 

 

 

 

 

 

 

 

 

A MySQL Backup Strategy

The two common tools of Backup in MySQL are the following: 

1.        mysqldump

2.        mysqlhotcopy

If we are using innodb database engine, it is possible to perform online backup with only mysqldump. It is possible for you to use mysqlhotcopy, in case you are using MyISAM database engine.

Assume that we have scheduled the full database backup daily at 3 am. As the database goes into production, we may need to revise this plan. We may still continue to do a full database backup daily. In addition, there could be incremental backups on an hourly basis.

C:\Program Files\MySQL\MySQL Server 5.0\bin> mysqldump –u root –p – - all-databases – - single-transaction > backup.sql

In order to make the incremental backups, we need to start MySQL server with the option --log-bin. If you have implemented Replication, it creates the binary log. It is stored in, for example, E:\mysqlBinlog, as shown below:

Name	Size	Type	Date Modified	Attributes
…	…	…	…	A
Mysql-bin.000080	6,733 KB	000080 File	6/25/2007 2:56PM	A
Mysql-bin.000081	1,027 KB	000081 File	6/25/2007 3:54 PM	A

The last file ‘mysql-bin.000081’ is being updated currently.  These binary logs form the incremental backups.  Hence while we conduct a full backup, ensure that the cache is flushed so that a new binary log is created. The full backup plus all the binary logs created after the full backup constitute the data at present.

C:\Program Files\MySQL\MySQL Server 5.0\bin> mysqldump –u root –p – -all-databases – -single-transaction – -flush-logs – -master-data=2 > e:\Backup\2007-06-25_10213136.sql 

It adds the following lines to the file 2007-06-25_10213136.sql, which will be useful at the time of Recovery:

– Position to start replication or point-in-time recovery from 

– CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.000082′, MASTER_LOG_POS=98; 

The listing in E:\mysqlBinlog currently will be as shown below: 

Name	Size	Type	Date Modified	Attributes
…	…	…	…	A
Mysql-bin.000080	6,733 KB	000080 File	6/25/2007 2:56PM	A
Mysql-bin.000081	1,949 KB	000081 File	6/25/2007 4:39 PM	A
Mysql-bin.000082	253 KB	000082 File	6/25/2007 4:54 PM	A

Essentially, 2007-06-25_10213136.sql and mysql-bin.000082 together form the most up to date database at any point of time. We may copy this binary file and the later ones to a safe location on every hour.The following is a backup script with name “backup.bat” created on the server in the folder C:\Program Files\MySQL\MySQL Server 5.0\data. It creates backup file once daily at 3 am and stores it in the folder E:\Backup. The script file contains the password of root user. So we need to control access to it.

echo off echo Starting Backup of Databases: for /f “tokens=2-4 delims=/ ” %%a in (‘date /t’) do (set dt=%%c-%%a-%%b) for /f “tokens=1-4 delims=:.” %%a in (‘echo %time%’) do (set tm=%%a%%b%%c%%d) set backupfile=%dt%_%tm%.sql echo Backing up to file: %backupfile% “C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqldump” -u root -p”password” – -all-databases – -single-transaction – -flush-logs – -master-data=2 > e:\Backup\%backupfile% CD \Program Files\MySQL\MySQL Server 5.0\data echo on

 This script is scheduled to run at 3 am everyday, by Windows Scheduler.