Question :
We recently upgraded our Dell T110 server to a Dell T710 with a 100GB SSD drive. We were hoping that we would see a tremendous improvement in mysql select performance, but unfortunately the new system is slower than the old.
I have tried tweaking the fstab and the mysql onfiguration, but nothing has seemed to help. It seems that for some reason the system is unable to use the full speed of the drive or there is something else slowing it down.
According to hdparm the new SSD drive has a similar read speed and surprisingly a slower cached read speed.
When doing a random access data read test using seeker the SSD drive is much faster.
Old System:
Dell T110, Single 2.4GHz Quad core processor
8GB of RAM
250 GB 7.2K SATA Drive
Mysql 5.1
ext2 filesystem
Timing buffered disk reads: 100 MB in 0.87 seconds = 114.99 MB/sec
Timing cached reads: 22636 MB in 1.99 seconds = 11361.70 MB/sec
Results: 81 seeks/second, 12.28 ms random access time
New System
Dell T710, Dual 2.13GHz Quad core processor
16 GB of RAM
100 GB SSD Drive
Percona 5.5
ext4 filesystem
Timing buffered disk reads: 366 MB in 3.01 seconds = 121.47 MB/sec
Timing cached reads: 10960 MB in 2.00 seconds = 5487.23 MB/sec
Results: 3742 seeks/second, 0.27 ms random access time
The new system has the following /etc/fstab entry:
/dev/mapper/vg_katahdin-lv_root / ext4 defaults,noatime,discard,data=ordered,errors=remount-ro 1 1
Answer :
Before tweaking the disk you should tweak memory usage for the DB – especially with mySQL.
From what I read I suspect that your DB is doing heavy write IO – which is faster on an ext2 and on a “real” disk.
Update 2011-11-23 (after migration to dba):
Perhaps you should analyze your DB with the free TOAD version.
You should add the following to the read slave’s /etc/my.cnf and restart mysql
[mysqld]
read-only
This will prevents users without the SUPER prvilege from performs any updates. Only users with REPLICATION SLAVE and/or SUPER privileges.
If you are running MySQL on Slave Server, you should convert all the tables to MyISAM.
Here is nice script to do so:
mysql -u... -p... -AN -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=MyISAM;') InnoDBConversionSQL FROM information_schema.tables WHERE engine='InnoDB' ORDER BY (data_length+index_length)" > ConvertInnoDBToMyISAM.sql
mysql -u... -p... -AN < ConvertInnoDBToMyISAM.sql
You must make sure of two things that affect performance:
-parition alignment.
-nature of SSD at intensive write operations: overwriting to non-empty blocks is not possible so the ERASE operation runs on SSD drives to empty unused blocks. It’s a very expensive operation which takes few milliseconds. To overcome this challenge, you must use of SSD feature called “reserve space”. Set it to about 40% of SSD size then check performance.