The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MySQL tuning - HW or SW

Discussion in 'General Discussion' started by MongoHosting, May 20, 2004.

  1. MongoHosting

    MongoHosting Registered

    Joined:
    Jan 22, 2004
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    top shows a boatload (25) of this:

    9599 mysql 0 0.0 2.8 /usr/local/mysqld --basedir=/ --datadir=var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/hostingco.com.pid --skip-locking

    First off, is this normal? Cpu usage has been pushing the server load up and down all night, some major spikes. Mem is the real nag, been high b/c of these entries like above.

    Any clues? There is one very active vB forum on this server.

    Should I just up the mem and re assess or is there something I'm missing?

    I'm posting this here because this didn't start occurring until I went to 9.2 Stable release to fix the exim problem.
     
  2. kris1351

    kris1351 Well-Known Member

    Joined:
    Apr 18, 2003
    Messages:
    963
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Lewisville, Tx
  3. GOT

    GOT Get Proactive!

    Joined:
    Apr 8, 2003
    Messages:
    900
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Norfolk, VA
    cPanel Access Level:
    DataCenter Provider
    From shell, type

    mysqladmin status

    and post the results.
     
  4. MongoHosting

    MongoHosting Registered

    Joined:
    Jan 22, 2004
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    We haven't had any problems tonight....of course we've been trying to track this down:

    Uptime: 82350 Threads: 2 Questions: 948149 Slow queries: 69 Opens: 9056 Flush tables: 1 Open tables: 64 Queries per second avg: 11.514
     
  5. mickalo

    mickalo Well-Known Member

    Joined:
    Apr 16, 2002
    Messages:
    765
    Likes Received:
    2
    Trophy Points:
    18
    Location:
    N.W. Iowa
    Queries per second avg: 11.514... this is WAY too high... looks like you might have some queries that need optimizing or fine tune your global my.cnf file.

    Mickalo
     
  6. GOT

    GOT Get Proactive!

    Joined:
    Apr 8, 2003
    Messages:
    900
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Norfolk, VA
    cPanel Access Level:
    DataCenter Provider
    I would not necessarily agree with that assessment.

    I have a very lightly loaded server and it runs at 10.

    I have a moderately busy server and it runs at 30.

    I have a server that I manage for someone which (IMO) is overloaded and it runs 50-60.

    Now, that is not to say that your my.cnf doesn't need optimizing. It almost certainly does if you've not done it.

    Whole books have been written on how to optimize mysql. Its more of an art than a science. There are lots of threads and guides that will give you a decent my.cnf. It won't be perfect, but it'll be better than what you have right now.
     
  7. tanq

    tanq Member

    Joined:
    Nov 11, 2003
    Messages:
    17
    Likes Received:
    0
    Trophy Points:
    0
    Uptime: 565008 Threads: 4 Questions: 74766200 Slow queries: 45 Opens: 82214
    Flush tables: 1 Open tables: 512 Queries per second avg: 132.328


    Thats bad, right? :(
     
  8. mickalo

    mickalo Well-Known Member

    Joined:
    Apr 16, 2002
    Messages:
    765
    Likes Received:
    2
    Trophy Points:
    18
    Location:
    N.W. Iowa
    right you are, I negeleted to take in consideration the Questions factor.

    One of our boxes has about 200 DB's... I've just never seen the QPS average quiet that high.

    Mickalo
     
  9. GOT

    GOT Get Proactive!

    Joined:
    Apr 8, 2003
    Messages:
    900
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Norfolk, VA
    cPanel Access Level:
    DataCenter Provider
    over 100 is not necessarily bad, depending on how taxing they are, and what type of io subsystem you are running.

    Bottom line is what can your server handle. If all of them are being hit by the query cache, then you won't be in too bad of shape, but if only 90% are, you are probably stressing your hard drives, even if you have scsi.
     
  10. mickalo

    mickalo Well-Known Member

    Joined:
    Apr 16, 2002
    Messages:
    765
    Likes Received:
    2
    Trophy Points:
    18
    Location:
    N.W. Iowa
    one of the big improvements in ver 4.0+ was the query_cache which we use alot in our perl appilications. the recent release of 4.0.20 has fixed many bugs found in 4.0.18 regarding memory leakage. taking some time to learn how to tune MySQL is really worth the cost of a few good books... believe me :)
     
Loading...

Share This Page