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 |