Is /etc/my.cnf is OK?

Portal Home > Knowledgebase > Articles Database > Is /etc/my.cnf is OK?

Posted by bdwebservices, 10-15-2011, 02:10 PM
Hello, We got problem with sever load. We hosted 750 sites in this server. Most of them Wordpress and Joomla sites. Our server specification is Intel Core i7 950 Quad-Core (8 x 3.06GHz, Bloomfield) with 24GB DDR3 RAM Our Current my.cnf is: +------------------------------------------------+ [mysqld] log-slow-queries=/var/lib/mysql/slow.log max_connections = 250 max_user_connections = 50 query_cache_limit = 2M query_cache_size = 48M join_buffer_size = 3M thread_cache_size = 8 table_cache = 384 innodb_buffer_pool_size = 384M key_buffer_size = 92M tmp_table_size = 60M max_heap_table_size = 60M +------------------------------------------------+ and root@i79501 [/var/lib/mysql]# /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl >> MySQLTuner 1.1.2 - Major Hayden >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.1.56-log [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 1G (Tables: 15398) [--] Data in InnoDB tables: 155M (Tables: 2171) [--] Data in MEMORY tables: 0B (Tables: 32) [!!] Total fragmented tables: 3343 -------- Performance Metrics ------------------------------------------------- [--] Up for: 1h 36m 47s (365K q [62.892 qps], 14K conn, TX: 1B, RX: 68M) [--] Reads / Writes: 69% / 31% [--] Total buffers: 586.0M global + 5.6M per thread (250 max threads) [OK] Maximum possible memory usage: 1.9G (8% of installed RAM) [OK] Slow queries: 0% (59/365K) [OK] Highest usage of available connections: 8% (21/250) [OK] Key buffer size / total MyISAM indexes: 92.0M/413.4M [OK] Key buffer hit rate: 99.7% (16M cached / 50K reads) [OK] Query cache efficiency: 70.7% (195K cached / 276K selects) [!!] Query cache prunes per day: 169333 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 17K sorts) [!!] Joins performed without indexes: 460 [!!] Temporary tables created on disk: 26% (4K on disk / 17K total) [OK] Thread cache hit rate: 99% (67 created / 14K connections) [!!] Table cache hit rate: 0% (384 open / 58K opened) [OK] Open file limit used: 53% (671/1K) [OK] Table locks acquired immediately: 99% (137K immediate / 137K locks) [OK] InnoDB data size / buffer pool: 155.4M/384.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate Adjust your join queries to always utilize indexes When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries without LIMIT clauses Increase table_cache gradually to avoid file descriptor limits Variables to adjust: query_cache_size (> 48M) join_buffer_size (> 3.0M, or always use indexes with joins) tmp_table_size (> 60M) max_heap_table_size (> 60M) table_cache (> 384) +------------------------------------------------+ Any help, suggestion would be appreciate.
Posted by barbus, 10-16-2011, 05:47 AM
To use mysqtuner you should run your mysql server at least a day. About settings. I'd increase key buffer to fit all myisam indexes and follow mysqltuner advices. Rest is okay. You database is quite small, I don't see here any problems.

Add to Favourites Print this Article

Knowledgebase

Knowledgebase

Is /etc/my.cnf is OK?

Contact us