my.cnf settings for a DV4 8GB

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
psurrena
Forum Contributor
Posts: 355
Joined: Thu Nov 10, 2005 12:31 pm
Location: Broolyn, NY

my.cnf settings for a DV4 8GB

Post by psurrena »

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: Select all

 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: Select all

-------- 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]
Post Reply