Slashdot Mirror
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."
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.
Could this be because MySQL does not have row level locking and a row near the one you are accessing is locked?
i would of liked to email you about the php caching from mysql but you didn't leave an email..
Is there a common thread besides MySQL? First thing i'd try is see if the majority of people on the mailing list having that problem are running linux/*BSD/some other os, might be an OS DB deal. If that doesn't provide any answers, I'd change DB's.
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
This sig left intentionally blank.
You don't have a problem with your hardware. It should be able to handle much greater loads that you're subjecting it to.
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.
Thanks---I run without most of the /boxes, and thus never noted their ghetto.
I refuse to believe corporations are people until Texas executes one. -- desert rain on http://www.dailykos.com/user/
...as a believer in MySQL at heart, but there is always Oracle. Might be worth it, though I have never used it, and there is supposedly a steep learning curve.
Funny and I thought Perl == Paid employment recently located
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.
You're absolutely right. That's actually how I tend to handle it, saving not only the data, but also the complete HTML file. I'm just stupid, and forgot to mention it. :)
-Waldo
Upgrading the NIC could make a huge difference.
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
listen bud,
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 much about your situation!
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.
http://slashdot.org/~tf23/journal
> I think they're just not considered exciting enough to make the main page.
/Dread
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
Well the best solution seems to reduce DB dependance... this can be done by :
.html file. This can be done by a cron job too.
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)
php allows persistant connections, just use pconenct rather than connect
i believe it has to be running as an apache module for that to work though
Need a Catering Connection
Yeah, but Slash has tons more hardware (IIRC, the database server is a quad xeon with 2 gigs of RAM). The point is that he has more than enough hardware power to serve the content - but MySQL seems to be falling over itself under that kind of load.
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.
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.
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).
How about updating some of the other componets. Newer kernel might help. Find out if any program on your computer is out of date. If your running the stable version of mysql perhaps try a newer beta version.
guvf vf zl fvt
It is common to post here without reading linked articles, but posting replies without reading original post is really something.
Really quick note, I've read about the file descriptor problem before, but have never run into it myself. If you read through some of the ducmentation in the kernel source, I belive (it was a while ago when I read this info, so just samck me if I'm wrong) it is a simple matter to increase the number of file descriptors that the kernel can handle. I kindof recall this being a variable set through a makefile, but that doesn't sound quite right... anway it is supposed to be a simple matter to inrease the number of file descriptors that your kernel can handle, however it will give you a performance hit in some other areas.
:)
I guess I lied when I said this would be quick.
You are only young once, but you can stay immature indefinitely.
> If you want to get really fly you could store :)
Although there's really no point, cos it'll be read out of disk cache on every hit, if you've got the right page out there!
> that file in a RAM disk.
Use preview.
Must use preview.
bud.
You're fantastic.
--
--
Still waiting.
--
Yes. Hello sir? The longer you don't reply to my requests for information, the more of an ideut you will look.
--
Never have I met such an ideut in all my life as a man who claims.. what was it again? Something about hubs and a database. Well, I seem to remember it was pretty damn ideutic anyway.
--
Yeah that funny "swap out your hardware" bloke is a fucking scream.
--
Please note that these claims are false. Only an ideut could seriously claim that something as high level as a persistent database connection (over TCP) doesn't like certain switch/NIC combinations.
--
i want my DOCUMENTED PROOF
--
Why am I beginning to suspect that no such proof exists, and that you were squealing out of your wrinkly anus the whole time?
--
Don't be scared, you can come out and tell me why it's the switch, I promise I won't laugh at you.
--
jessop jessop jessop
--
s w i t c h e s m y a r s e
--
What version of MySQL? What operating system/version? What glibc? I had a problem with 3.22.32, TurboLinux cluster server 4.0, glibc 2.0.7. There is a known bug in that particular combination. The solution for me is to get off of turbolinux (RedHat 6.2 with Kimberlite for me)
Depending on how your DB is structured and how your front-end interfaces with it, you could give Postgres a shot..
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.
Or, if you were using Roxen, you could just wrap your code in tags, and let roxen handle the rest...
--
Good:
Bad:
--
/. used to run on a Multia doing a few hundred k hits a day. Forget what the box after that was, I think a 450 or something. It's just when Andover bought /., they threw a LOT of hardware at the problem.
Uh. How about you despam it?
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.
the company i was working for had similar problems... Where php and mysql would get in this wierd state when using persistant connections. It would cause the DB to get all sorts of confused and end up eating it... The solution (please don't ask why this is the case, it seems absolutely illogical) was the switch between the systems.. We changed it to a hub, BAM, problem solved. After spending about 3-4 days digging through newsgroups, IRC, etc. We found a few people that were discussing this problem. It is unknown if it is a php problem or a mysql problem. -andy
check out the section on file descriptors:
http://www.kegel.com/c10k.html
In my experience, connection pooling makes a very large difference on busy MySQL servers. If you're using Java, there are a lot of easy-to-use pools, like PoolMan and Resin's pooling. Don't know if PHP and Perl/DBI have similar features.
I have no idea about the content of your site, but I see a lot of sites where everything is dynamicly generated, for no obvious reason.
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.
Rather than just dumping the results of the database query to disk, why not dump the complete, generated HTML into a static file? This doesn't even require modifying a lot of code - just write a simple daemon to send an http request ('lynx -dump' or whatever) to the correct page every X seconds/minutes, and save the results to a file. In the code, just divert requests to known static pages (either with a redirect, or just send the file on disk). You can get the web code to ignore the static file if the timestamp is too old.
/. gets like 1.5 million hits a day, and it seems to work pretty damn good all things considered. You could probably run some sort of tests to figure out where you're having bottlenecks. While it's certainly not impossible for MySQL to be giving you trouble, it really doesn't seem likely, unless you use an older version.
Although I personally have no hands-on experience with PostGres, it has often been declared in this here Ask Slashdot forum that PostGres is a somewhat slower but more robust and complete SQL server that doesn't buckle under such load as quickly as MySQL does. I suggest you try it on a test server, migrating from MySQL shouldn't be difficult.
-Billco, Fnarg.com
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
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).
-andyWhat 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
MrJoy.com -- Because coding is FUN!