Are you using the deadline scheduler? (Part 1)

There have been many posts about performance, benchmarking and the results. Many DBAs have talked in the past about the deadline scheduler, available in all modern Linux distributions.  The deadline scheduler is very effective for database systems, as it tries to prevent starvation of I/O requests.  To see what the net effect of using the deadline scheduler looks like, I am using a simple TPCC benchmark program, created by the Percona team, tpcc-mysql to measure the number of New-Order transactions per minute (TpmC).  All of the testing was performed using RedHat Enterprise Linux 5, using the standard ext3 file system and a combination of the default scheduler (cfq) and the deadline scheduler (dl):

CFQ:  845 TpmC

DL:  2145 TpmC

This is a huge difference in performance between the two schedulers. How do you know if you are using the deadline scheduler?  There are a few ways. The deadline scheduler can be enabled for all of your disks at boot time via the LILO or GRUB boot loaders. The scheduler can be changed by appending the string elevator=deadline to the boot line (file /boot/grub/grub.conf). If you issue the command cat /proc/cmdline this will display the kernel parameters that the system was booted with.

The second way of switching schedulers is to change it "on the fly." You can list all of the available schedulers, as well as determine which scheduler is current by using this command: cat /sys/block/drive/queue/scheduler where drive is the actual drive represented to your system (i.e. sda, hda).  The list of schedulers is displayed, and the current scheduler is noted within square brackets.  To change the scheduler, echo the name of the new scheduler to the meta-file from the previous command (as user root). Be careful when switching schedulers on the fly, as there is a possibility to hand the system.

In part 2, I'll go over my testing methodology and show how using the deadline scheduler with different types of RAID drive configurations and filesystem types affects the overall throughput of the system.

MySQL 5.5 may be GA but not the docs.....

Woes of ROW based logging implementation

I have been trying to find ways to implement ROW based logging at our company, as it provides better reliability and far less chances for a slave going "out-of-sync" with a master. One of the big issues that I faced was constant replication lag from one datacenter to another because of the massive amounts of data that can potentially be generated just from one single SQL statement.

With the traditional STATEMENT based replication, one SQL statement is written to the binary log - very little network overhead there transferring that across the wire to another datacenter. But if that single SQL statement changes 20,000 rows, well that's where agony begins, and business continuity takes a beating.

And to compound situations even further, more and more operations are suddenly becoming "unsafe for STATEMENT based logging", generating hundreds upon thousands of warning statements in error log files. With 5.1.50, LOAD DATA INFILE statements generate warnings now. This leads me to believe that at some point, these warnings will soon become unsupported operations. Blech! Does not give me the warm fuzzy feeling I used to have with MySQL. But I will still keep trying to find a real-world solution to this problem.

Extracting load files from the binary log

There are times when you may be rebuilding a DB server by replaying the binary logs using the mysqlbinlog utility. Extracting CRUD statements and DDL is relatively straightforward, but not for statements like LOAD DATA INFILE. The actual data file is embedded within the binary log, and not very visible to the naked eye. But there is an easy way to decipher the binary log and extract the file to load manually.

As an example, I have taken a simple text file of numbers and loaded it into a fictitious table abc using the LOAD DATA LOCAL INFILE command. To see where in the binary log that command would reside, the mysqlbinlog utility is used:

$ mysqlbinlog mysqld-bin.000003 | grep -i -B7 "load data"

# at 174
#100921 21:42:10 server id 1136902037  end_log_pos 218
#Begin_load_query: file_id: 1  block_len: 21
# at 218
#100921 21:42:10 server id 1136902037  end_log_pos 432  Execute_load_query      thread_id=5     exec_time=0     error_code=0
use test/*!*/;
SET TIMESTAMP=1285130530/*!*/;
LOAD DATA LOCAL INFILE '/tmp/SQL_LOAD_MB-1-2' INTO TABLE `abc` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (`a`)

$

You can see that from the command above that the command, including the load file is contained between positions 174 and 432 of the binary log. Now that the start/stop positions are known, it is possible to extract the data file to load manually into your database (or into another database):

mysqlbinlog --start-position=174 --stop-position=432 --local-load=/var/tmp mysqld-bin.000003

The --local-load option specifies what directory to store the actual load file. And you can take the file and use the LOAD DATA command above (changing the directory name as needed) and load that data back into your database or use it to seed another database.