MySQL Problems Under Heavy Loads? 57
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."
a few suggestions (Score:1)
SHOW VARIABLES;
and make sure you have max_connections set to a sufficient number.
The next step would be to make a more efficient use of your connections. In PHP use pconnect instead of connect. If you are using something like WebLogic middleware then take advantage of the connection pool.
good luck,
-T.
No row-level locking? (Score:1)
Re:Things to try (Score:1)
Who's fault is it really? (Score:1)
Disclaimer: I don't know DB's well, but I know troubleshooting. You didn't say os, and knowing only hardware, just putting out random (hopefully helpful) ideas.
bash: ispell: command not found
Tuning (Score:1)
Therefore you need to do some tuning. I generally work with Oracle, but these suggestion should apply equally to MySQL:
Make sure it can use as much real memory as possible (i.e., use memory, avoid swap space); split indexes and data over two (or more) disks; make sure you have connection pooling to avoid the overhead of making new connections.
If the machine has a load average of 0.4 and it's grinding to a halt, you probably have a disk bottle-neck. If the above suggestions do nothing, you'll have to consider rewriting your queries, denormalising the database, etc.
Finally, if you need to do a lot of updates, inserts or deletions you'll probably have to move away from MySQL (due to its lack of row-level locking). You can't go far wrong with Oracle, but PostgresSQL comes highly recommended by some.
Re:Good luck getting answers... (Score:1)
Thanks---I run without most of the /boxes, and thus never noted their ghetto.
Hate to say it.... (Score:1)
Re:Good luck getting answers... (Score:1)
It shows up in the Ask Slashdot side-box. There are a lot of stories which wind up there. I think they're just not considered exciting enough to make the main page.
Re:No row-level locking? (Score:1)
Re:Can you cache? (Score:1)
-Waldo
Re:Things to try (Score:1)
Mysql/PHP has a known problem with persistant connections that can be traced directly to nic/switch combinations. It is a rare problem but I have personally seen it happen.
And under a conjested situation like this MySQL doesn't kill the connections that aren't being actively used... and it just drowns in its own connections.
-andy
Re:WHY IT IS YOUR SWITCH (Score:1)
my other post wasn't "swapping out all other sorts of pieces of hardware at random"... it was UPGRADE those parts. Add more RAM, get a faster CPU, better NIC, etc.
And yes as odd as it is. I'll dig up some documented proof of this if you really want.
-andy
You don't mention... (Score:1)
What OS, what kernels, how did you build Mysql, what version of Mysql you're using, what's built into apache, are you using perl? php? etc.
If you're using perl head over to slashcode.com and look at the source to slashdot (and bender). They're code is designed for mod_perl, apache and mysql. I hear they can handle 1.5mil+ hits a day.
Re:Good luck getting answers... (Score:1)
Bah considering _I_ like this topic and considering Oracle is doing a _good_ job on linux lately.
A prominent db is important for the OS ppls put this on the front page!
Greetz
How to cache... (Score:1)
a) rethinking queries or DB architecture (reduce or eliminate lock, add more indexes, etc.)
b) make more caching
As for caching there's good solution I use with PHP/MySQL. It can save lots of work for the DB. Here's how it is done :
a) The page are generated as usual thru PHP (no big modification to apply)
b) The PHP is modified just at the beginning and the end to buffer the output of the script (use the ob_start(), ob_stop() and ob_* functions of PHP 4). Whenever the page is called the output is written to disk as well as sent to the user browser. The file is usually called "script_param1_param2.html" if the original script is "script.php3param1=xxx¶m2=xxx"
c) I use mod_rewrite (very usefull Apache module, a must have!) to, when a url of the form "script_param1_param2.html" is requested :
- check for the file with this name, and serve it if it exists
- serve the script.php3 with parsed arguments if not
this takes 3 lines of code to do... then just use the url form script_param1_param2.html everywhere on your site. This will always work because Apache won't issue a redirect to the browser, it will redirect itself to the script whenever needed and the browser will never see the difference
d) whenever content needs to be refreshed (new post, DB change, etc...) delete the
This is called 'static from dynamic' and is used by many many big sites (like Yahoo!, although they probably don't use mod_rewrite)
Re:a few suggestions (Score:1)
Hope this helps
Chris
Re:Connection pooling (Score:1)
i believe it has to be running as an apache module for that to work though
Re:/. uses mysql, doesnt it? (Score:1)
Re:Things to try (Score:1)
It doesn't seem like this will help. He said that he experiences the same connection problems when trying to access MySQL locally. It doesn't seem like a network congestion issue.
Also, the author seems under the impression that he has more than enough hardware to serve the content. It just seems like the software (MySQL, possibly the queries he's running) is causing problems.
Re:Text storage (Score:1)
You can also wait for the 7.1 release, which will support rows of unlimited length. The changes are in the current CVS sources - I use them for development and they're perfectly stable.
Re:Connection pooling (Score:1)
Perl does, if you're using mod_perl. It's called Apache::DBI (caveat: it allows persistent connections, not true 'pools'. In other words, each Apache child established one, and only one, connection to the DB. It usually works just as well as a true connection pool).
Re:Hate to say it.... (Score:1)
Upgrade other componets (Score:1)
Re:Things to try (Score:1)
It is common to post here without reading linked articles, but posting replies without reading original post is really something.
Re:The answer... (Score:1)
I guess I lied when I said this would be quick.
Re:Can you cache? (Score:1)
> that file in a RAM disk.
Re:Can you cache? (Score:1)
Use preview.
Must use preview.
Re:WHY IT IS YOUR SWITCH (Score:1)
bud.
You're fantastic.
Re:WHY IT IS YOUR SWITCH (Score:1)
Re:WHY IT IS YOUR SWITCH (Score:1)
Still waiting.
Re:WHY IT IS YOUR SWITCH (Score:1)
Re:WHY IT IS YOUR SWITCH (Score:1)
Re:WHY IT IS YOUR SWITCH (Score:1)
Re:Things to try (Score:1)
Re:WHY IT IS YOUR SWITCH (Score:1)
i want my DOCUMENTED PROOF
Re:WHY IT IS YOUR SWITCH (Score:1)
whyDNA is hiding (Score:1)
Re:WHY IT IS YOUR SWITCH (Score:1)
jessop jessop jessop
Re:WHY IT IS YOUR SWITCH (Score:1)
more questions first (Score:1)
Switching DB's (Score:2)
First problem (DB structure) is: are any records larger than 8K? A well-designed DB usually won't have (it would use foreign keys) but since MySQL doesn't support foreign keys, and does support arbitrarily large records, your DB may vary.
Postgres doesn't support records larger than 8K (OK, it does if you modify the source, but I'm told there is a performance hit for doing this) - this isn't unusual, as other "professional" grade RDB's have similar limits (MSSQL has a record limit of 4K)
Second problem, does your front end support/use persistant connections?
The major difference in performance between Postgres and MySQL boils down to the persistant/non-persistant connections. Postgres takes a much longer time to start up the connection than MySQL, so if your front-end doesn't support persistant connections, I wouldn't recommend switching. However if it does, a well-designed DB running on Postgres could solve your problem..
The biggest thing is the learning curve - Postgres supports things that MySQL doesn't, such as row-locking and foreign keys (even though these are coming) - my experience with Postgres is that it just slows down under extreme load, instead of stopping altogether - with a properly designed DB and good understanding of how to optimize your queries, it could provide an solution to your database; the problem is that you'll have to learn a little more SQL.
Re:Can you cache? (Score:2)
--
Re:Text storage (Score:2)
Good:
Bad:
--
Re:/. uses mysql, doesnt it? (Score:2)
Re:Things to try (Score:2)
Things to try (Score:3, Informative)
by whyDNA? (whydna@fuckspam.hotmail.com) on Thursday November 09, @03:32PM EST (#17)
(User #9312 Info) http://dcaff.com
His username is WhyDNA?. His name at hotmail is whydna. I think the fuckspam. part is easy enough to figure out.
WHY IT IS YOUR SWITCH (Score:2)
Re:The answer... (Score:2)
http://www.kegel.com/c10k.html
Connection pooling (Score:2)
too much stuff done on the fly? (Score:2)
Maybe you could save a few thousand hits on your database by making some of the pages static, or just regenerating them with a perl script once an hour or something.
Re:WHY IT IS YOUR SWITCH (Score:2)
Re:Switching DB's (Score:2)
As you said, you can up the limit to 32K with a recompile. I haven't heard anything about a performance hit though. Also, the 'lztext' data type compresses all data before storing it, meaning you can get far more than 8K in. Finally, you can use Large Objects to store data of unlimited size (see the Postgres Programmers Guide for more info). BTW, this limit is fixed in the current CVS sources, and the upcoming 7.1 release (beta soon).
Re:Can you cache? (Score:2)
/. uses mysql, doesnt it? (Score:2)
PostGres (Score:2)
Can you cache? (Score:4)
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 (Score:4)
Try the following:
Process limits? Connection limits? (Score:4)
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