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.

Optimizing a MySQL Database?

Discussion in 'General Discussion' started by noimad1, Mar 26, 2006.

  1. noimad1

    noimad1 Well-Known Member

    Joined:
    Mar 27, 2003
    Messages:
    627
    Likes Received:
    0
    Trophy Points:
    16
    I have a customer that is on a dedicated box, and we have a terrible time with server load and MySQL. He runs a message forum, and get's a fair amount every day, but nothing too ridiculous. Maybe 30-50 users on at a time consistantly throughout the day. No more then 100 at a time. So I'm thinking the load shouldn't be that bad.

    He only runs one website of the dedicated box, but it still brings it to a crawl at times...Server loads will jump up to 12-20 at times. It's not a huge box - a 1.8Ghz, 512mb ram, so I know that is kind of an issue as well.

    His forum database (invision boards) is currently around 540mb. I know this is a considerable size for a database, but I was wondering if anyone had any ideas on how to optimize this database to have it run smoother.

    I know this isn't a mysql forum either, but there is such a great deal of knowledge here, I was hoping someone might have had some experience with this.

    Thanks,
    Damion
     
  2. webignition

    webignition Well-Known Member

    Joined:
    Jan 22, 2005
    Messages:
    1,880
    Likes Received:
    0
    Trophy Points:
    36
    Have you tried optimising the tables through phpMyAdmin?
     
  3. Murtaza_t

    Murtaza_t Well-Known Member

    Joined:
    Jan 24, 2005
    Messages:
    476
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Earth
    cPanel Access Level:
    Website Owner
    Well you call 1.8Ghz, 512mb a huge server but I dont think thats really is a huge server. We had a client on one of our shared server (each server is 1.8Ghz Dual, 2GB RAM) having a huge invesion board forums with fair amount of trafic and the server load use to be always between 3-8. The problem with his board was all this Mysql connection did not die and went into sleeping mode. He was finally requested to move to a different board vBulletin, we helped him all over to move his database and the problem with server load was solved.

    So the problem is with the Invesion board, either upgrade to change to some other board, you can fire this command to check the number of processes when the load is high:
    Code:
     ]# mysqladmin processlist
    you can try setting the persistant connections to Off in your php.ini if that solves your prob.
     
  4. AndyReed

    AndyReed Well-Known Member
    PartnerNOC

    Joined:
    May 29, 2004
    Messages:
    2,222
    Likes Received:
    3
    Trophy Points:
    38
    Location:
    Minneapolis, MN
    While you need to optimize the server services/applications, the problem your client is experiencing is not MySQL. Although there are many variables involved in a high server load, VB might be the culprit.
     
  5. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    14,451
    Likes Received:
    195
    Trophy Points:
    63
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    Or VB might be the savior. As he's running an Invision. ;)

    Might want to consider converting over. Short of that I'd look at hacks on this forum that might be causing the problems as well.
     
  6. noimad1

    noimad1 Well-Known Member

    Joined:
    Mar 27, 2003
    Messages:
    627
    Likes Received:
    0
    Trophy Points:
    16

    Actually I said "It's not a huge box" in my origional posting, so I know that this is not a powerhouse machine. No biggie.

    I don't think switching forum software is going to be an option for this guy. He's purchased invisionboard, and has been using it for years, so he doesn't want to switch (unfortunately).

    I'll try the suggestions here so far to see if they help.....

    Also, no one really said anything about the size of the database? Do you think that a 540mb database with 30-100 constant connections could be a problem, or should that machine be able to handle that with no problems?

    I appreciate everyones help.

    Regards,
    Damion
     
    #6 noimad1, Mar 26, 2006
    Last edited: Mar 26, 2006
  7. Spiral

    Spiral BANNED

    Joined:
    Jun 24, 2005
    Messages:
    2,023
    Likes Received:
    7
    Trophy Points:
    0
    540 MB? That is way beyond massively enormous!

    I'd say time for a bit of forum pruning and DB cleanup!
     
  8. noimad1

    noimad1 Well-Known Member

    Joined:
    Mar 27, 2003
    Messages:
    627
    Likes Received:
    0
    Trophy Points:
    16

    That's what I told him. I mean he has something like 400,000 posts just from 2003. The problem is he wants all posts available to all users, which means he doesn't want to clear out any previous messages,

    My recommendation at this point is to have him create a sepearte forum just for archived messages, then delete all the old messages from the active forum.

    That way people can still search for old topics, but the newer active topics are still fast and efficient....
     
  9. gorilla

    gorilla Well-Known Member

    Joined:
    Feb 3, 2004
    Messages:
    699
    Likes Received:
    1
    Trophy Points:
    18
    Location:
    Sydney / Australia
    or upgrade the ram to 1 or 2 gig
     
  10. dave9000

    dave9000 Well-Known Member

    Joined:
    Apr 7, 2003
    Messages:
    891
    Likes Received:
    1
    Trophy Points:
    16
    Location:
    arkansas
    cPanel Access Level:
    Root Administrator
    I agree upgrade the ram to 2 gig and make sure the database tables are indexed.
     
  11. abubin

    abubin Well-Known Member

    Joined:
    Dec 7, 2004
    Messages:
    393
    Likes Received:
    1
    Trophy Points:
    18
    yes, upgrading RAM does helps a lot. I remember running with 512mb and then when I upgraded to 1gb, it made a lot of difference to my server.
     
Loading...

Share This Page