Killed mySQL

I've got a busy website, but mySQL too many connections

[tucj7]

I've got quite a high traffic website, which gets a lot of traffic to individual pages.

The pages are dynamically generated and there are about 300000 of them.

Somehow, though, when the traffic gets really high, or several spiders come to the site, mySQL and apache spawn hundreds of processes which don't die.

Then mySQL complains of too many connections and Apache keeps running out of MaxOpenFiles.

I'm on a shared reseller account and so don't have root access.

Do you think there's something wrong/inefficient with my code, or is there something not set up properly on the server?

I'm using PHP and mySQL only.

Thanks in advance.

[AprilS]

Hello tucj7 - Welcome to Webmaster World.

I understand what you are going through as I experienced it hard this spring. I have used shared servers for years and they have always done well for me. Though I had root access on my shared servers, it didn't really matter when traffic was high.

The first time I noticed it happening I frantically went through my php and perl code to make sure my queries were optimized as much as they could be. Doing so seemed to help for a while, but then a few weeks later (after traffic kept increasing) - MySQL kept going nuts and was gobbling up all the system resources.

What I found out is that most shared servers have resource caps for each account - that way if you max out your resources, the other accounts on the server can still function.

I knew what I had to do...but dreaded it because of a bad experience with a dedicated company. But, I ended up making the switch to a dedicated machine - and can't believe I went this long without doing so.

I am still configuring the dedicated server (just about done) - so I still haven't switched my domains over yet - but one of the first things I did was get MySQL 4.0 installed and transfer my database to the new server and have my php & perl scripts use the database on the new server. I noticed a HUGE difference in performance! (HUGE) -

So, back to your question:

Do you think there's something wrong/inefficient with my code, or is there something not set up properly on the server?

Without seeing your code - it's really hard to say. It is very possible your query statements could use some tweaking. Also, are you closing your MySQL connections in your PHP?

Have you viewed your MySQL variables? [show variables;] Depending on your user rights, you can tweak quite a few of those variables - which may help. Also do [show status;] it will show you current stats for your MySQL server - threads, aborted connections, etc.

Are you using Linux, *BSD, Windows, etc? What version of MySQL are you using?

How much traffic are you getting (page views & unique users)?

[tucj7]

Thanks for the reply, it's very informative.

I'm running on Linux. PHP ver 4.3.4 and MySQL ver 4.0.20 Standard.

So far this month I've got 406450 page views and 80500 unique visitors. I think a lot of views are due to spiders because I have so many dynamically linked pages.

If I wasn't closing my DB connections would that affect the open Apache processes as well?

[AprilS]

It is usually a good idea to close your connections because there is only a limited number of socket connections that can be opened on any server - wether it's Linux or Windows. Since you are on a shared server, your limited number of socket connections can be used up quickly.

However, the php manual states:

Using mysql_close() isn't usually necessary, as non-persistent open links are automatically closed at the end of the script's execution.

But keep in mind it says "usually". It is possible for some connections to hang. Go ahead and close them - if you're using header and footers (templates) - it may be easiest to put your close statement in your footer file.

Also, are you doing a mysql_connect or mysql_pconnect (perminent connect)? pconnect's cannot be closed like standard mysql_connect.

I'm impressed your shared server has made it as far as it has. Your stats are a little higher than mine before I had to jump ship.

Your MaxOpenFiles issue (if I remember correctly) is something that can be overcome with more memory. I think you can change the value for MaxOpenFiles in one of the Apache config files - but I think it would be pointless unless you had the memory to handle it.

[superpower]

tucj7, what kind of site is it? (ie messageboard).

Why do you have to dynamically generate each page?

[tucj7]

It's a directory of links I've collected.

I've thought about creating static pages, but the nested directories just become too detailed for my liking.

[isitreal]

Think about maintaining the database generated version on your development server, outputting the site as static pages by a script, upload those. Someone here recommended that option to me, I didn't do it, don't need it yet, but it's an interesting idea.

[tucj7]

I might try that if I get some spare time (my processor's and mine).

In the meantime, I think I've definitely improved my MySQL and Apache use. I modified all my old ugly code and now just use a Pear DB handler and I make sure I disconnect after every connection.