Very slow SELECTS after reinstall
Hey folks,
I run a server that used MySQL 5.1.45 64-bit. All worked well, and everybody was happy.
However, I had to swap the HD of my server because it was failing. Restoring a bare metal backup failed, so I reinstalled Windows, and installed the latest PHP and MySQL versions. I restored my databases from a backup, and everything works again.
But... mysql is slow. Sloooooooowwwwwww.
It could of course be the fact that the hard drive is a bit slower than the previous one (I swapped a 7200rpm 250 GB Barracuda with a 5700rpm 1TB Caviar Green drive), but the weird thing is that WRITE's don't seem to suffer a lot.
My machine's details:
4 GB ram
Xeon X3440 @2.53 Ghz, 4 cores, HT-enabled (so 8 cores available)
Windows Server 2008 R2 sp1, all updates
MySQL 5.5.25a and PHP 5.3.13
I decided to compare my home pc, which I used as a dev machine for php/mysql scripting, with the server. Previously my own pc felt a bit sluggish compared to the server, now it's Roadrunner vs Slimey-the-one-legged-toad.
I made two tables in a clean database, both are identical. One uses InnoDB, one uses MyISAM.
They look like this:
id (int, auto inc, primary)
test1 (float)
test2 (varchar 255, index)
test3 (text)
datum (timestamp)
I made a script that fills the database with 1000 random values by means of this SQL query:
"INSERT VALUES ( NULL, 12, RAND(), CONCAT('x_', RAND(), CONCAT('text_', RAND(), NOW()) INTO bench"
I then read the values 20000 times with this query:
"SELECT * FROM bench ORDER BY test2"
I clean the result sets with every iteration, and TRUNCATE the table when I am at the end of the test run. I also have a timer running for each operation. The results are almost identical for both MyISAM and InnoDB tables. I ran them a few times and these are on average the numbers that I got:
Write 1000x: 300 ms
Read 20000x: 4000 ms
I built a similar setup (IIS, same PHP, same MySQL) on my home computer, and got these results:
Write 1000x: 1500 ms
Read 20000x: 800 ms
So, on my rather sluggish home computer writing goes 5 times as slow (as expected), but reading goes 5 times as fast. That's rather weird, isn't it?
Anyway, I hope somebody can give me some help me with this. It's driving me mad!
Thanks in advance,
Diederik (who isn't an IT-technomancer, so take it easy)