1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Crazy MYSQL Database Load Averages

Discussion in 'MySQL' started by bookatechie, Nov 14, 2008.

  1. #1
    Hello,

    I got 2 VPS's, they do fine for Apache hosting but can not handle MYSQL. I imagine it is due to the heavy IO needed. MYSQL is at 50-75% CPU at all times and my load goes up to 30 at times.

    I was thinking of using perhaps one of the shared, cheap hosts just for the database. Is there a better solution? Can someone recommend me host?

    I'm by no means a MSQL expert but I read a few optimization manuals but can't get the load under control. We are only talking about 5,000 - 10,000 page views per hour.

    Please help!
     
    bookatechie, Nov 14, 2008 IP
  2. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #2
    With that volume you should probably be using a dedicated database server. How may transactions per page view are you seeing? Is this a standard php / apache setup? For one of your sites in your signature? Also, what sort of access to mysql parameters do you have? It may be possible to push the performance with caching, and more optimization.

    Depending on how the DB is being used more caching may not help all that much.

    In any situation, if you do use a separate database, you want it right next to your web server, and preferably without even a switch in-between the two (1000Mb full-duplex & a Crossover cable). If you try to host a dedicated DB on a separate network, you're run into a whole mess of other problems with the load you currently have.
     
    jestep, Nov 14, 2008 IP
  3. bookatechie

    bookatechie Peon

    Messages:
    225
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #3
    jestep, thanks for the quick response!
    This is a standard LAMP setup on a VPS so I got full control to everything. It is a LAMP setup with one VPS running the MYSQL and the other Apache. This is the ninja website in my sig, it got really popular once it went onto myspace.
    I think there are about 5-15 transactions per page.

    I was thinking VPS's are usually poor on disk performance so it might help to put as much of the DB into memory as possible but I'm no MYSQL expert. I do have 2GB of dedicated memory.

    I will try anything! 30+ load is just crazy.
     
    bookatechie, Nov 14, 2008 IP
  4. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #4
    With 10,000 page views per hour, and 15 transactions per page, you're looking at about 42 per second, which should be within reach of your setup.

    I'm not sure as to what you've done so far, but here's where I would start, access permitting.

    Install PHP APC or another caching system right away.
    If your application has any ability to do front-end caching, look into that as well. Front-end caching can reduce DB usage by huge amounts for the more static pages of the site.
    Start analyzing queries to find which ones are taking a long time to process. You want to reduce every query's time as much as possible, by optimizing column data types, and indexes. Use mysql's slow query log to automatically log slow queries.
    Then mess with my.cnf parameters and try to further speed up the query times.

    If you haven't already, definitely check out: http://www.mysqlperformanceblog.com/ for ideas on how to optimize your configuration. Without seeing your exact table structures and queries it's hard to give more specific advice, but I would start with these.
     
    jestep, Nov 14, 2008 IP
  5. dingloo

    dingloo Peon

    Messages:
    13
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #5
    Jestep, excellent suggestions.

    Besides this, try to identify areas where the information about relatively static data can be moved away from the db into a static file. This way you can try and minimize the access to database.
     
    dingloo, Nov 14, 2008 IP
  6. bookatechie

    bookatechie Peon

    Messages:
    225
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #6
    Great tips so far! ... I didn't think that the load was all that out outrageous either :)
    I have put APC on but had to take it off again due to session irregularities ... some people would no be able to log in anymore. It helped the Apache server but MYSQL didn't seem to care much.
    I don't really have much static content due to the nature of the ninja game.
    I had query logging on for a while but it didn't get anything above 3s under normal load times ... under heavier load it was starting to catch a few but the query was cut down to the fields I had to have. Not sure what else I can do.
    I also added indexing to all the ids, foreign keys, and fields that I search on ... not sure if it made any difference.
    I can post tables, my.conf files and what ever else you experts need to help :)

    I can't afford to get an other server just for the DB.
     
    bookatechie, Nov 14, 2008 IP
  7. bookatechie

    bookatechie Peon

    Messages:
    225
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #7
    OK, I talked to the IT for my VPS and they said I should let you know that it has 80 red/writes per second, I don't know what that means. Is that high for MySQL?
     
    bookatechie, Nov 17, 2008 IP