Best mysql my.cnf setup for my site

Discussion in 'MySQL' started by hp_solomon, Nov 5, 2012.

0
  1. #1
    Hi,

    I need help on optimizing my.cnf of my site. my site is so slow, and seems response and connection is too long or slow.

    website is a phpfox website with database members of 80,000+.

    Logged-in Online users: 50+, + bots hundreds to thousands.

    here's my server setup.
    8GB DDR ECC RAM
    CPanel/whm
    Dual Xeon E5335 Quad Core Clovertown
    4 x 120GB SATA Raid 5
    Mysql version 5.0


    mysqltuner.pl results

    current my.cnf configurations:






    Please help me. Thanks in advance.
     
    hp_solomon, Nov 5, 2012 IP
  2. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #2
    Are you using innodb or myisam tables? Are you using any front-end caching mechanism?
     
    jestep, Nov 5, 2012 IP
  3. hp_solomon

    hp_solomon Member

    Messages:
    8
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    36
    #3
    hello,

    thank you for the reply.

    im using the built in phpfox caching of pages and data.. most are myisam tables but some are innodb. also changed some tables from myisam to innodb that usually locks when i checked mysql processlist. but most of them are myisam.

    so its a combination of both.
     
    hp_solomon, Nov 5, 2012 IP
  4. eventsbz1

    eventsbz1 Peon

    Messages:
    10
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #4
    YOu have over allocated mem settings as it shows :

    Maximum possible memory usage: 9.9G (125% of installed RAM)

    So you must reduce these two first

    max_connections 800
    innodb_buffer_pool_size 512 M
     
    eventsbz1, Dec 28, 2012 IP
  5. Rukbat

    Rukbat Well-Known Member

    Messages:
    2,908
    Likes Received:
    37
    Best Answers:
    51
    Trophy Points:
    125
    #5
    And defragmenting tables, adjusting join queries to always utilize indexes and reducing SELECT DISTINCT queries without LIMIT clauses have nothing to do with the cnf file.
     
    Rukbat, Dec 31, 2012 IP