We are hosting a large site (10-12,000 visitors per day) which is database driven on a Linux box (RedHat 6.something), 750 proc and 256 RAM.

mySQL is eating up the system resources like wildfire - which seems very strange and has baffled everyone I've spoken to. RAM is fine - almost 100 free, no use of a swap file.

The largest table is about 20,000 records - nothing to sneeze at, but not huge. A few pages query the database several times, up to 10 on a given page, to get all the information needed.

At a loss as to what to do - any suggestions are much appreciated!!

Cheers..
Drew

    What do you mean by system resources? RAM, CPU, Disk space? You make a /etc/my.cnf and tweak variables in there to how much ram you want mysql to take up, I believe there are around 4 different my.cnf example files that come with the mysql source. Look in the "support-files" directory. Or just do a "locate my-large.cnf" to find try finding the file on disk (if you have it).

    Linux can get weird about memory usage, it can take up enourmous ammount of ram for caching things, this is normal behaviour. If it's eating into your swap file, then that's when you're getting into trouble. linux 2.4.8 and below had weird vm (swap) issues, 2.2.19 is fine. If you think this may be a problem, try upgrading your kernel to 2.4.10.
    If you're not noticing decreased performance, then I wouldn't really work about how much memory it's taking up. You should have a my.cnf on a production machine anyway.

    You may also try optimizing your mysql tables.
    sql command: "optimize table <tablename>". or you can even change table types to a table type that has row level locking. instead of the default myisam tables that have to lock the whole table to perform an update or insert.

      Hi Steve, sorry this wasn't clear - it is processor that is being eaten up, not RAM. RAM is just fine, not going into the swap file. But mySQL is eating up 80% + of the processor's resources!!

      Cheers..
      Drew

        Doh, now that I re-read your first message you did say RAM was fine, sorry about that. My selective reading strikes again.

        Hmmm, so 750 mhz cpu eh? I'd try doing an "OPTIMIZE TABLE <tablename>" for each table and see what happens.

        You can also do a "SHOW PROCESSLIST" to see if there are any long running queries stuck eating up all the cpu time. I've found mysql can have trouble with certain queries joining multiple tables, especially LEFT JOIN's and queries with AND in the ON JOIN clause, they can get really REALLY slow.

        Take a look at the processlist though, if anything's stuck then you've found your problem and go from there. Find the offending query and rethink it, maybe adding an index here or there to speed it up, or maybe you really didn't need that expensive LEFT JOIN or GROUP BY after all. 🙂

          OK, so it was very very slow tonight, I went and did a SHOW PROCESSLIST, and nothing had been running for more than a split second. However, I was intrigued that there were only 6 listed when I did SHOW PROCESSLIST, even though there are likely hundreds or even a thousand people browsing the site right now.

          Does that tip anyone off to anything?

          Frustrated

          Drew

            And just as intriguing - 3 or 4 of those 6 are sleeping at any given time, which baffles me given the current traffic.. but it changes which ones are sleeping...

            Baffled more
            Drew

              That sounds like perfectly normal behavior to me, even though there are hundreds or thousands of people browing the site at a time, there are only a few actually in the state of "waiting for the server to render the page", let alone, actually connected to mysql and issuing a query. If you time a page to see how much time has passed after loading, it usually happens quite fast, maybe half a second for a very slow page. And when you're browsing the web you load what, maybe one page a minute? This, of course, all depends on what kind of content your are serving and how much text is on each page.

              Everything sounds like a perfectly normal site to me, maybe it's time you move mysql on to it's own server or get a beefier machine? Either that, or audit your code and optimize queries.

              How much traffic are you pushing?
              Try issuing the command:
              mysqladmin -uusername -p status

              It should show output like below, giving us a better insight into how busy your mysql server is. (these are from my workstation/personal server, very very low traffic as you can see)

              Uptime: 67823 Threads: 1 Questions: 963 Slow queries: 0 Opens: 11 Flush tables: 1 Open tables: 5 Queries per second avg: 0.014

                Thanks for your insights, Steve.. a few well placed INDEXES, and switching to myISAM tables (I had to in order to run OPTIMIZE) did the trick.

                It is running so fast now I can barely keep up. What an amazing difference.

                Cheers!
                Drew

                  Write a Reply...