MySQL Indexing for vBulletin Forums

Discussion in 'MySQL' started by explorer, Jan 14, 2010.

0
  1. #1
    Consider a vBulletin forum with about 500,000 posts 10,000 members and about 100 online at any time - like one of mine. (It's on my own server, not shared hosting.)

    I'm having a problem with some queries running slowly and making the board unavailable for a few seconds at a time or longer.

    Should indexing of the MySQL tables be necessary for a forum of this size? What's your experience?
     
    explorer, Jan 14, 2010 IP
  2. Brandon Sheley

    Brandon Sheley Illustrious Member

    Messages:
    9,721
    Likes Received:
    612
    Best Answers:
    2
    Trophy Points:
    420
    #2
    what version of vb are you using
    what mods do you have installed
    what are some specs on your server
    a site of that size shouldn't run slow
     
    Brandon Sheley, Jan 14, 2010 IP
  3. explorer

    explorer Well-Known Member

    Messages:
    463
    Likes Received:
    40
    Best Answers:
    0
    Trophy Points:
    110
    #3
    Thanks Loco.M

    3.7.6 Patch Level 2

    Hardly anything. Just three plugins that shouldn't affect anything - Advanced Reputation Checks, Analytics in Archive and Archive Adsense.


    1 Xeon CPU Core
    1024MB Memory
     
    explorer, Jan 14, 2010 IP
  4. scylla

    scylla Notable Member

    Messages:
    1,025
    Likes Received:
    33
    Best Answers:
    1
    Trophy Points:
    225
    #4
    theres your problem right there, the 1gb of memory.
     
    scylla, Jan 14, 2010 IP
  5. Internal09

    Internal09 Member

    Messages:
    256
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    35
    #5
    Your PHP Memory might be too low...
     
    Internal09, Jan 14, 2010 IP
  6. mwasif

    mwasif Active Member

    Messages:
    816
    Likes Received:
    23
    Best Answers:
    1
    Trophy Points:
    70
    #6
    How did you know that, did you enable slow query log? If not then do so.

    How many pages you serve daily?

    There are number of ways to optimize high traffic sites/forums e.g. identify slow queries and optimize them (either by creating indexes or rewriting them), applying cache (memcached, APC) and serving static content from a different web server like lighttpd with a subdomain like images.yourdomain.com.

    In your case, initially you can go for database indexes, serving images from different web server and tune MySQL server configuration (my.cnf).

    It depends on the table size and the number of rows scanned by queries.
     
    mwasif, Jan 16, 2010 IP