Portal Home > Knowledgebase > Articles Database > MySQL performance regression :-(
MySQL performance regression :-(
| Posted by meshuge, 11-08-2007, 10:59 PM |
| I've decided to give VPS a shot. So...
I'm runnning the same query on a 1 & 1 VPS, where the MySQL server is local, and on a 1 & 1 shared server (where the MySQL server is remote).
The MySQL version on the VPS machine is 4.1, while on the shared server it's 4.0.
The exact same query takes 20x longer (!) on the VPS.
Any ideas?
|
| Posted by Apolo, 11-08-2007, 11:05 PM |
| One idea:
In the VPS you have limited RAM. You don't mention how much is it in your case, but say it is 128 or 256 MB of RAM.
Whereas in a shared hosting server the RAM is 1, 2, 4 or more GB. And that's a big difference.
|
| Posted by foobic, 11-08-2007, 11:22 PM |
| Yes, it's reasonable to expect that the much more powerful shared database server would be quicker than the vps provided other users aren't overloading it.
But there may be other reasons also... Did you run the same query more than once on each server? And if so, have you enabled the query cache on your vps?
|
| Posted by Apolo, 11-08-2007, 11:54 PM |
| Now you mention it, that would be another big reason: perhaps the VPS is not optimized at all (my.cnf).
|
| Posted by david510, 11-09-2007, 12:25 AM |
| Set query_cache_type = 1 to enable query_cache. Increase the table_cache and query_cache_size value a bit and have a check again.
|
| Posted by albatross.smart, 11-09-2007, 02:52 AM |
| With these all major suggestions I will like you to mention this to 1&1 support too, they may want to check on the root node for any processes or resource consumers there. Sometimes it can delay the processes as you may fail to get the alloted RAM for your Server. Again, the next best step will be my.cnf optimization. That will do.
|
| Posted by RBBOT, 11-09-2007, 06:59 AM |
| I'd recommend you disable the query cache completely while debuging issues like this, as it can make you think you have fixed the underlying issue when you haven't, then turn it back on afterwards.
|
| Posted by meshuge, 11-09-2007, 12:13 PM |
| Thanks for all the good ideas. I've tried tweaking the variables to match those on the Shared host (buffers, tmp tables, key caches etc), but to no avail.
But then I ran an experiment that convinced me that the MySQL version is the problem. I issued the same query using MySQL 5.0 on the shared host (1and1 allows you to select 4.0 or 5.0), and got the same horrible performance.
So here are the results:
MySQL 4.0: 3.0 seconds on Shared Host
MySQL 4.1: ~60 seconds on a VPS
MySQL 5.0: ~60 seconds on a Shared Host
So basically something changed in the server software when moving from MySQL 4. I also noticed that EXPLAIN SELECT for the exact same query returns completely different results for 4.0 and 4.1.
|
Add to Favourites 
Print this Article
Also Read
Shared Hosting
Shared Hosting
