MySQL Performance – Howto – part 2 (write cache dependance)

I already wrote about Sysbench and MySQL tunning scripts. Those articles can be found HERE and HERE.

Now lets do some tests and lets see the dependance from write cache enabled/disabled option on your disks. Keep in mind that RH based distros will probably disable write cache (I tried Ubuntu on this server and the same thing happen – write cache was disabled after installation).

The first step is to check the disk with

[root@s1 sysbench-0.4.12]# hdparm -i /dev/sda
 
/dev/sda:
 
 Model=GB0250EAFYK                             , FwRev=HPG1    , SerialNo=WCAT1E535427
 Config={ HardSect NotMFM HdSw>15uSec SpinMotCtl Fixed DTR>5Mbs FmtGapReq }
 RawCHS=16383/16/63, TrkSize=0, SectSize=0, ECCbytes=50
 BuffType=unknown, BuffSize=16384kB, MaxMultSect=16, MultSect=?16?
 CurCHS=16383/16/63, CurSects=16514064, LBA=yes, LBAsects=268435455
 IORDY=on/off, tPIO={min:120,w/IORDY:120}, tDMA={min:120,rec:120}
 PIO modes:  pio0 pio3 pio4
 DMA modes:  mdma0 mdma1 mdma2
 UDMA modes: udma0 udma1 udma2
 AdvancedPM=no WriteCache=disabled
 Drive conforms to: unknown:  ATA/ATAPI-1 ATA/ATAPI-2 ATA/ATAPI-3 ATA/ATAPI-4 ATA/ATAPI-5 ATA/ATAPI-6 ATA/ATAPI-7

Note WriteCache=disabled line

I suppose you know how to create a database, assign a user and set password

Read-write test for InnoDB

# sysbench --num-threads=16 --max-requests=10000 --test=oltp --oltp-table-size=500000 --mysql-socket=/var/lib/mysql/mysql.sock --oltp-test-mode=complex --mysql-user=test_database --mysql-password=test_database_password run

gave me the next results

 
No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 16
 
Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 10000
Threads started!
Done.
 
OLTP test statistics:
    queries performed:
        read:                            140000
        write:                           50000
        other:                           20000
        total:                           210000
    transactions:                        10000  (42.63 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 190000 (809.95 per sec.)
    other operations:                    20000  (85.26 per sec.)
 
Test execution summary:
    total time:                          234.5821s
    total number of events:              10000
    total time taken by event execution: 3751.1329
    per-request statistics:
         min:                                 17.33ms
         avg:                                375.11ms
         max:                               3850.27ms
         approx.  95 percentile:             766.79ms
 
Threads fairness:
    events (avg/stddev):           625.0000/1.73
    execution time (avg/stddev):   234.4458/0.08

The total time is more than 234 seconds which is terrible result.

Now, delete sbtest table and recreate it again (please see the sysbench howto).

Enable write cache with

# hdparm -W1 /dev/sda

and start test again

The result is

No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 16
 
Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 10000
Threads started!
Done.
 
OLTP test statistics:
    queries performed:
        read:                            140000
        write:                           50000
        other:                           20000
        total:                           210000
    transactions:                        10000  (621.08 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 190000 (11800.43 per sec.)
    other operations:                    20000  (1242.15 per sec.)
 
Test execution summary:
    total time:                          16.1011s
    total number of events:              10000
    total time taken by event execution: 255.8624
    per-request statistics:
         min:                                  2.49ms
         avg:                                 25.59ms
         max:                                689.84ms
         approx.  95 percentile:              41.84ms
 
Threads fairness:
    events (avg/stddev):           625.0000/4.68
    execution time (avg/stddev):   15.9914/0.03

Now we have a 16.1011 seconds for a complete test. I repeated the test several time with the same results.

We have a 14.62 times better result.

It is obvious that write cache is very important for MySQL but keep in mind that something can be lost in case of power failure and you should think about battery backups.

Tuning Kernel parameters

It is good idea to tune a few kernel parameters too. For this purpose you can install ktune (yum install ktune). (more info https://fedorahosted.org/ktune/)

With “service ktune start” you will set up scheduler on deadline instead of cfq (which can be up to 20% slower)

[root@s1 bekap]# service ktune start
Applying ktune sysctl settings:
/etc/sysctl.ktune:                                         [  OK  ]
Applying sysctl settings from /etc/sysctl.conf:            [  OK  ]
Applying deadline elevator: sda                            [  OK  ]

After Ktune I have a little faster time (around 15 seconds)

More info about disk elevators can be found here http://www.redhat.com/magazine/008jun05/features/schedulers/

Optimizing the EXT3 file system on CentOS

noatime
This mount option tells the system not to update inode access times. This is a good option for web servers, news servers or other uses with high access file systems.

Open /etc/fstab and add noatime like shown below

/dev/VolGroup00/LogVol00 /                       ext3    defaults,noatime        1 1

Leave a Reply

Your email address will not be published. Required fields are marked *