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.

my.cnf settings for a DV4 8GB

Discussion in 'Database Discussions' started by ok1ha, Oct 8, 2012.

  1. ok1ha

    ok1ha Registered

    Joined:
    Oct 8, 2012
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    I have a Wordpress site on a MediaTemple DV4 server with 8GB RAM. The DB has over 35K posts and at peak hours, the site has between 300 and 400 concurrent users. The homepage runs 6 queries to the wp_posts table.

    I have also included my mysqltuner results below.

    Could anyone offer any advice on the best settings for the my.cnf file? Thanks.

    Code:
     query-cache-type = 1
     query-cache-size = 256M
     query_cache_limit = 1M
     thread_cache_size = 256
     max_connections = 350
     tmp_table_size=256M
     max_heap_table_size=256M
     table_cache= 1024
     join_buffer_size = 5M
     key_buffer_size = 512M
     wait_timeout = 60
     local-infile=0
     datadir=/var/lib/mysql
     socket=/var/lib/mysql/mysql.sock
     user=mysql
     # Disabling symbolic-links is recommended to prevent assorted security risks
     symbolic-links=0

    Code:
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.1.54-log
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
    [--] Data in MyISAM tables: 963M (Tables: 504)
    [--] Data in InnoDB tables: 4M (Tables: 198)
    [!!] Total fragmented tables: 219
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 2d 18h 38m 35s (27M q [114.806 qps], 1M conn, TX: 504B, RX: 4B)
    [--] Reads / Writes: 97% / 3%
    [--] Total buffers: 1.4G global + 7.6M per thread (350 max threads)
    [OK] Maximum possible memory usage: 4.0G (42% of installed RAM)
    [OK] Slow queries: 0% (3K/27M)
    [!!] Highest connection usage: 86%  (301/350)
    [OK] Key buffer size / total MyISAM indexes: 128.0M/679.7M
    [OK] Key buffer hit rate: 100.0% (1B cached / 58K reads)
    [OK] Query cache efficiency: 67.8% (14M cached / 21M selects)
    [!!] Query cache prunes per day: 29047
    [OK] Sorts requiring temporary tables: 0% (1K temp sorts / 1M sorts)
    [!!] Temporary tables created on disk: 35% (700K on disk / 1M total)
    [OK] Thread cache hit rate: 99% (463 created / 1M connections)
    [OK] Table cache hit rate: 28% (1K open / 3K opened)
    [OK] Open file limit used: 56% (1K/2K)
    [OK] Table locks acquired immediately: 99% (7M immediate / 7M locks)
    [OK] InnoDB data size / buffer pool: 4.3M/32.0M
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Reduce or eliminate persistent connections to reduce connection usage
        Increasing the query_cache size over 128M may reduce performance
        Temporary table size is already large - reduce result set size
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
        max_connections (> 350)
        wait_timeout (< 60)
        interactive_timeout (< 28800)
        query_cache_size (> 1G) [see warning above]
     
  2. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    You'd want to remove these lines as they aren't needed in /etc/my.cnf file:

    Code:
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    Next, you are using MySQL 5.1, so table_open_cache is what is used rather than table_cache for the directive name.

    After that, you could review the recommendations by mysqltuner.pl and try to implement them. It's pretty clear on what it suggests.

    I would suggest enabling the MySQL slow query log, which you haven't done, and finding out which queries are slow, then work on indexing or improving those queries. You can also enable mysqlsla after you have slow queries logging to get a report of the queries. I discuss this stuff in my MySQL presentation at the cPanel conference 2 years ago:

    MySQL OptimizationcPanel Videos | cPanel Videos

    I'd highly suggest viewing the presentation, since many questions you might have should be answered in it. The purpose of the presentation is not for us to provide exactly what variables to use, but for the user to determine what ones to use with tools and logging details.
     
  3. ok1ha

    ok1ha Registered

    Joined:
    Oct 8, 2012
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Great, thanks so much!
     
  4. Astral God

    Astral God Well-Known Member

    Joined:
    Sep 27, 2010
    Messages:
    180
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    127.0.0.1
    cPanel Access Level:
    Root Administrator
    I would also suggest a

    # mysqlcheck -o -A

    to defragment and optimize your tables.
     
Loading...

Share This Page