MySQL Forums
Forum List  »  Performance

Very slow SELECTS after reinstall
Posted by: Diederik Slob
Date: August 16, 2012 05:37AM

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)

Options: ReplyQuote


Subject
Views
Written By
Posted
Very slow SELECTS after reinstall
2872
August 16, 2012 05:37AM
1078
August 18, 2012 10:02PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.