up vote 1 down vote favorite
share [g+] share [fb]

I've switched web hosting recently. The new box is slower than the old one (it's VPS vs. dedicated). However, performance testing reports that a good portion of the slowdown is database-driven.

I have a reference operation. On the old host it runs for 2.2 sec on average, 0.2 sec database access time. On the new one, 18 sec on average, 14 sec database time.

Server is the same - it's local MySQL v. 5.0.91-community in both cases. Connecting over Unix sockets.

What could account for such discrepancy? What server settings should I look at?

link|improve this question
50% accept rate
What the... I answered this post yesterday! Did you delete it and create it again on SO? – pauska Oct 7 '10 at 10:21
I deleted it on SO and created here :) It belongs here, really. – Seva Alekseyev Oct 7 '10 at 18:27
feedback

migrated from stackoverflow.com Oct 7 '10 at 2:08

This question came from our site for professional and enthusiast programmers.

4 Answers

up vote 2 down vote

VPS boxes disksystem is usually dependent on other OS's disk usage and therefore sometimes very slow. This can be worsened by small memory amounts of VPS system.

Try tuning down database harddrive access or request independent harddrives.

Using query cache can be a double-edged sword.

link|improve this answer
+1 agreed, likely to be oversubscribed storage – Chopper3 Oct 7 '10 at 10:31
What could go wrong with query caching? Overall site performance is not memory-bound, as far as I can tell. It's the CPU mostly. – Seva Alekseyev Oct 7 '10 at 18:30
feedback
up vote 1 down vote

Too little RAM dedicated to the VPS.

link|improve this answer
Unlikely, it reports 800MB free out of 1GB. – Seva Alekseyev Oct 6 '10 at 21:13
1  
Agreed. How about running "mysqld --verbose --help" on both and checking the diffs? – igelkott Oct 6 '10 at 21:17
Done that. The only significant difference is query_cache_size. That could be relevant! – Seva Alekseyev Oct 6 '10 at 21:22
feedback
up vote 1 down vote accepted

Query caching was disabled on the new box. Brought query_cache_size in line with the old value, and execution time went down to around 3 sec.

link|improve this answer
feedback
up vote 0 down vote

Also will want to watch per-CPU utilization - you may only be allocated a hardware thread or a share of a single core, depending on the OS platform and HW architecture of the server your VPS is hosted on. Odds are you're a Solaris Zone (perhaps even a Sparse Zone sharing a common kernel) or a Linux Jail...

I/O data flows are also going to be shared, so if some of your queries require table scans, you'll be butting up against other users and will be waiting for some cycles while their operations complete.

Sounds like a classic virtualization "catch 22" - cheaper service & centralized resource management, but slower performance under peak loads.

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.