MySQL Problems Under Heavy Loads?

pgatt asks: "I am running a very high load Web site, averaging 500,000 hits per day. Most pages on the site require a MySQL connection for some simple query. I have a separate Web server and MySQL server, each consisting of dual Pentium III 750s with a gig of RAM. Under peak hours, MySQL reports a "Lost Connection To Server During Query" error, even if I try to connect to the database locally from the MySQL server. This usually seems to happen when the number simultaneous threads get to be around 700-800, but there doesn't seem to be an exact number that it hits when it encounters a problem. I have contacted MySQL support but have not had much luck. My system has plenty of resources left, only running at about 0.40 load average with plenty of RAM. I see a lot of people experiencing the same problem as me in the mysql mailing list but they haven't found a solution. I am looking for recommendations about how to either tweak MySQL to run better or other database solutions. I would like to stick with something open source, but if I need to pay for something to get stability then I will. Any suggestions will be greatly appreciated."

Comments

Can you cache?

[waldoj]

You may find it help to write a simply caching function for some of the more intensive pages. Think of Slashdot. Let's pretend that 50% of pageviews are the front page, and, of those, 50% are ACs or simply non-logged-in users. So that's 25% of the traffic seeing identical pages. (That is, it's been in no way customized.)

This is a prime example (I think!) of a very cacheable page. You could write a simple caching function -- dump the results of a series of MySQL queries to a file, and check the timestamp on that file to see if you want to re-query the DB or if you want to just re-use the contents of that file. Totally simple.

If you did this to avoid a single query (unless it was a bummer of a join), it would probably be inefficient. But if you used this in place of a dozen queries, I think you'd find that this made better use of your machine. If you want to get really fly you could store that file in a RAM disk. :)

-Waldo

Things to try

[whydna]

I worked for a company that took about 750,000 to a million hits/day with almost every page pulled from the DB.

Try the following:

• Add more RAM (at LEAST 512), preferably 1 Gig
• Upgrade CPUs
• Upgrade Motherboards
• Upgrade NIC
• Read the "Optimizing MySQL" chapter (11??) from the mysql website.
• Try contacting AbriaSoft who works with MySQL to provide support.
• Try caching as much content as possible. Have you concidered squid. Or, you can easily write your own (this is really easy in PHP... email me for info).

-andy

Process limits? Connection limits?

[jfrisby]

What user is MySQL running as? Make sure you're not hitting the per-user process limit. Also, make sure you're not near the MAX_CONNECTIONS setting.

My daytime employer handles millions of database driven page views every day with MySQL on Solaris... We have our connection limit set to around 4,500 right now...

-JF