Catch up on stories from the past week (and beyond) at the Slashdot story archive

 


Forgot your password?
Close
typodupeerror
×
Programming IT Technology

MySQL Problems Under Heavy Loads? 57

Posted by Cliff from the crashing-under-the-weight dept.
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."
This discussion has been archived. No new comments can be posted.

MySQL Problems Under Heavy Loads? More

MySQL Problems Under Heavy Loads?

Comments Filter:

  • a few suggestions (Score:1)

    by Anonymous Coward
    First off, do:
    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)

    by Anonymous Coward
    Could this be because MySQL does not have row level locking and a row near the one you are accessing is locked?

  • Re:Things to try (Score:1)

    by Anonymous Coward
    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
  • 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.

  • ...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.

  • 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.

  • If it doesn't have row level locking at all, how can adjacent rows become locked?
  • 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.

  • > I think they're just not considered exciting enough to make the main page.

    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 /Dread
  • Well the best solution seems to reduce DB dependance... this can be done by :
    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&param2=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 .html file. This can be done by a cron job too.

    This is called 'static from dynamic' and is used by many many big sites (like Yahoo!, although they probably don't use mod_rewrite)
  • Q: What is the nature of the query. Is it a simple SELECT with a few joins or is it a LOCK TABLE, INSERT/UPDATE, UNLOCK TABLES type of thing. I've seen the "Lost Connections" problem with the second type of query and the problem was caused by a number of processes timing out before they got the lock. mysql_pconnect made this worse until I realized what the problem was and surrounded the offending code with a SELECT GET_LOCK(...)/SELECT RELEASE_LOCK() mutex lock. MySQL seems to handle waiting for these types of lock a whole lot better. Having said that I would run "mysqladmin processlist" to see the processes that you have running and what state they are in. If you have a lot of things that are waiting for tables to be unlocked turn off pconnect. If this solves your problem, albeit by making things a little/lot slower use the mutex lock.

    Hope this helps

    Chris
  • 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
  • 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.
  • Upgrade NIC

    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 increase the limit to 32K by recompiling Postgres. You can use the lztext type, which compresses data before inserting/retrieving it.

    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.

  • Don't know if PHP and Perl/DBI have similar features.

    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).

  • ... not really. I haven't known any large learning curve. (I've run MySQL and Oracle) The only learning curve is in learning how to use Oracle *well*. Oracle comes with its own programming language (PL/SQL) and a bunch of really nifty tools like Pro*C, SQLJ, etc. that allow you to write pretty simple, yet complex database programs. Plus, you might also wish to become aquainted with the constraints, etc. that Oracle supports.
  • 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.
  • Moderators, wake up. The guy in article says clearly he has plenty of RAM (1GB) and enough CPU power. So how can be recommendation to use more RAM, at least half a Gig, and better CPU informative?

    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. :)

  • > If you want to get really fly you could store
    > that file in a RAM disk. :) 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!
  • D'Oh!

    Use preview.
    Must use preview.

  • Yes please, I would love to see your proof,

    bud.

    You're fantastic.

  • Hey, come on, that was not a troll, I was just trying to get some evidence for this unlikely claim.

  • I thought you were going to give me some evidence.

    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 EVIDENCE

    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.
  • switches switches switches

    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)

  • Switching DB's (Score:2)

    by Anonymous Coward
    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 <cache seconds='60'> </cache> tags, and let roxen handle the rest...
    --
  • I use type lztext rather than text - it compresses the data before it stores it in the database.
    Good:
    1. Allows larger than 8k in a row in most cases
    2. Less disk IO when reading data from disk

    Bad:
    1. Unknown upper limit for datasize - it depends on the compression ratio
    2. Much overhead when querying on that row.
    3. Slight overhead when storing data in that field

    --
  • /. 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.
  • And that would explain why even on the loopback interface he's still having problems?
  • 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)

    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).

  • 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.

  • Can you cache? (Score:4)

    by waldoj ( 8229 ) <waldo@@@jaquith...org> on Thursday November 09, 2000 @12:16PM (#634288) Homepage Journal
    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 (Score:4)

    by whydna ( 9312 ) <whydnaNO@SPAMhotmail.com> on Thursday November 09, 2000 @11:32AM (#634289)
    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 [abriasoft.com] who works with MySQL to provide support.
    • Try caching as much content as possible. Have you concidered squid [squid-cache.org]. Or, you can easily write your own (this is really easy in PHP... email me for info).
    -andy

  • Process limits? Connection limits? (Score:4)

    by jfrisby ( 21563 ) on Thursday November 09, 2000 @02:37PM (#634290) Homepage
    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

Slashdot Top Deals

"Experience has proved that some people indeed know everything." -- Russell Baker

Close