Slow database-driven sites


#1

I’m not quite sure if it’s the speed of the box I’m on or the fact that the mysql server I’m on (gwen) is just slammed, but my database driven website (my main site as well as my gallery) load pretty damn slow for sitting on a quad xeon (xevious.dreamhost.com) and am leaning towards that the SQL server is just overwhelmed with queries. Maybe some of the rest of you can check, but this seems a bit high for users on one machine:
[xevious]$ wc -l /etc/passwd
883 /etc/passwd
It looks like 42 of those are system accounts for daemons etc, but that’s still over 800 users on one machine. I’m sure I’m not the only person using database-driven sites to notice the speed (or lack thereof) of their sites.

I’m not going to say I expect superior speed knowing that I signed up for a year of service for under 10 bucks with domain registration (along with god knows how many other people in December), but there has to be something I can do. Possibly switch to another box, or at least a different SQL server (or both?)?

Suggestions? How about hearing from someone at DH on this one?

Thanks.


#2

You will have better luck emailing them, the forum isnt an officially supported and DH employees are not required to check it.


#3

yeah I had the same thing with my site - it started going REALLY slow… like 5 mins to load a page, thenit died altogether. This was my SQL on server ‘hobbes’ although my other ones on other servers seemed to be pretty fast.


#4

The database servers definitely seem a weak link. My static sites (or the static portions) are acceptably snappy. I’ve got one db-driven site that has about 7 queries (and not particularly intensive ones, supposedly) and its page render times have varied from one golden week of .5-1s to a more usual range of 3-12 seconds. Man, that’s painful.

I have no credibility or special knowledge about these things other than what I observe in my own experience and wanderings … my sense is that the hosting of the past was ‘home pages’ - and static performance (and maybe bandwidth or number of domains) was where it was at. As database support became a host differentiator, and as web applications are increasingly reliant on databases, even ‘home pages’ are becoming database driven. At this point I would trade back some disk and bandwidth allocation to get more optimized database performance on shared plans. I don’t know about others, but I suspect many are like me - we just use canned scripts. Telling us to ‘reduce the number of queries’ and so on isn’t really actionable advice.

Sadly, the day ‘dedicated server’ becomes the only answer is also the day I get fired from my volunteer job and our club takes a five-year step backwards in what we can offer our members and constituents. So for now I just tell them, 'Yep, it’s slow - that’s the price of all this functionality."


#5

It seems to have cleared up for me now… Back to nice and fast even with 20+ querys on a page. Maybe there is actually something wrong with your scripts/setup/server?


#6

We do not intentionally overload hosting servers or MySQL servers, but it can happen at times. When it does happen, we take steps to resolve the issue. We are very regularly adding new MySQL servers and moving user database around to spread out the load.

In this case, I checked both gwen and xevious and both look ok right now. If you are seeing chronic problems that are not going away, let us know so we can take steps to remedy it. It is helpful to us if you can log into the server when you are seeing problems with your site and send us the output of ‘uptime’. We track information on the servers as well, but more data doesn’t hurt!

On today’s modern hardware, 800 users on one machine is not that many. Most websites get very little traffic so the resources are shared by a smaller set of the users.

  • Dallas
  • DreamHost Head Honcho/Founder

#7

It’s not so much the number of users on the machine…at times the load average is 10+ ( which is obviously why you want the output of ‘uptime’…well, besides exactly when it’s occuring). Loading anything thats stored locally doesn’t take long at all, it’s my site and my installation of gallery2 which at times take 10+ seconds to load up, which I think should be much less. I realize that with shared hosting resources are just that – shared…but 10 seconds to load up a site is up there considering the average person waits 7 seconds, if I remember correctly, before getting impatient and moving on to something else.

The next thing I’m going to try is moving my MySQL databases to another server completely off of Dreamhost’s network to see if this improves the latency issues. I’ll post the results here when I get around to doing that.

Allan


#8

A load of 10 are not normal and is a problem. The mysql database is on a separate server altogether. That server seemed ok when I looked at it. The load was only about 0.60.

We have seen other complaints about slow mysql performance on other servers and usually it can be traced to a small number of poorly optimized databases. Once those tables are optimized the performance goes back to normal. That doesn’t seem to be the case here, from my limited checking.

  • Dallas
  • DreamHost Head Honcho/Founder

#9

This is what I’m talking about…not quite a load avg of 10, but still high, in my opinion.

[xevious]$ w
Mon Aug 22 16:49:44 PDT 2005
16:49:38 up 20 days, 3:38, 3 users, load average: 3.48, 2.89, 2.43
USER TTY FROM LOGIN@ IDLE JCPU PCPU WHAT
allank pts/3 adsl-66-51-195-4 16:32 0.00s 0.01s 0.00s w
[xevious]$

I decided to add a PHP timer to the footer of my website to see exactly how long it took to load up my page. 18 seconds and change is nuts.

You can see the image at http://www.allank.com/misc/longload.jpg


#10

Well see the first problem is it appears you’re using a mac.

:wink:

(j/k… could be windowblinds… )
btf


#11

It’s Firefox with StyleXP.


#12

Still taking 16+ seconds to load…
15:53:07 up 21 days, 2:41, 4 users, load average: 3.30, 3.43, 2.97


#13

Here’s a nice one…this time I decided to run top

top - 11:54:36 up 21 days, 22:42, 2 users, load average: 9.78, 6.17, 4.58
Tasks: 2 total, 1 running, 1 sleeping, 0 stopped, 0 zombie
Cpu(s): 29.7% user, 6.4% system, 1.9% nice, 62.0% idle
Mem: 1549472k total, 1509032k used, 40440k free, 6676k buffers
Swap: 2096472k total, 573124k used, 1523348k free, 725668k cached


#14

yeah, their mysql servers are definately a weak link… I’ve been noticing my wordpress blog tkaes a good 5 seconds on average to pull in the content… it loads the template fine, but then has to wait for the database… hopefully this’ll change soon :slight_smile: until then, I might need to find different hosting for the blog…

I am happy that they implemented zend however :slight_smile: