Dmacleo Posted June 12, 2012 Share Posted June 12, 2012 so I am looking around at tuning sql, this is my.cnf on CPANEL server (centos 6.2 64bit) located in /etc[mysqld] # safe-show-database log-slow-queries max_connections=500 default-storage-engine=MyISAM innodb_file_per_table=1 lol seems a bit bare. I should be able to drop those max connections right? Link to comment Share on other sites More sharing options...
Elly Posted June 12, 2012 Share Posted June 12, 2012 run script -f mysql.log wget mysqltuner.pl chmod +x mysqltuner.pl ./mysqltuner.pl exit Open and paste the contents of mysql.log here :) Link to comment Share on other sites More sharing options...
Dmacleo Posted June 12, 2012 Author Share Posted June 12, 2012 >> MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net> >> 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.5.24-cll [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 451M (Tables: 1152) [--] Data in InnoDB tables: 224K (Tables: 14) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [!!] Total fragmented tables: 33 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 2h 15m 23s (47K q [5.888 qps], 1K conn, TX: 1B, RX: 332M) [--] Reads / Writes: 51% / 49% [--] Total buffers: 168.0M global + 2.8M per thread (500 max threads) [OK] Maximum possible memory usage: 1.5G (37% of installed RAM) [OK] Slow queries: 0% (4/47K) [OK] Highest usage of available connections: 1% (6/500) [OK] Key buffer size / total MyISAM indexes: 8.0M/263.2M [OK] Key buffer hit rate: 99.6% (2M cached / 8K reads) [!!] Query cache is disabled [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 4K sorts) [OK] Temporary tables created on disk: 19% (1K on disk / 5K total) [!!] Thread cache is disabled [!!] Table cache hit rate: 15% (400 open / 2K opened) [OK] Open file limit used: 31% (795/2K) [OK] Table locks acquired immediately: 99% (49K immediate / 49K locks) [!!] Connections aborted: 6% [OK] InnoDB data size / buffer pool: 224.0K/128.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate Set thread_cache_size to 4 as a starting value Increase table_cache gradually to avoid file descriptor limits Your applications are not closing MySQL connections properly Variables to adjust: query_cache_size (>= 8M) thread_cache_size (start at 4) table_cache (> 400) server was restarted recently, right now its really small forum.Your applications are not closing MySQL connections properly I wonder about that one and the innodb. need to open other domains on the vps and see whats going on there. Link to comment Share on other sites More sharing options...
AlexJ Posted June 13, 2012 Share Posted June 13, 2012 Add this in your my.cnfthread_cache_size = 56query_cache_size = 56Mtable_cache = 500 Link to comment Share on other sites More sharing options...
Dmacleo Posted June 13, 2012 Author Share Posted June 13, 2012 will give that a try, thanks. never had chance to look into that closing connections error. Link to comment Share on other sites More sharing options...
Dmacleo Posted June 13, 2012 Author Share Posted June 13, 2012 running this gives nothing listed in recomendations, need to run it a few days to see. [mysqld] # safe-show-database log-slow-queries max_connections=500 default-storage-engine=MyISAM innodb_file_per_table=1 thread_cache_size=56 query_cache_size=56M query_cache_type=1 table_cache=500 query_cache_limit=10M key_buffer_size=270M Link to comment Share on other sites More sharing options...
AlexJ Posted June 13, 2012 Share Posted June 13, 2012 U can also change this on next restart:max_connections = 100 instead of 500. and make this key_buffer_size to 512M Then run MySQL tuner after 2-3 days and post the result back here. Link to comment Share on other sites More sharing options...
Dmacleo Posted June 13, 2012 Author Share Posted June 13, 2012 I upped the key buffer size until it did not show warnings then added like 10 or so, will keep eye on that one and see. I forgot to drop the key connections down though, grr should do that right now I guess LOL Link to comment Share on other sites More sharing options...
Kyle F Posted June 13, 2012 Share Posted June 13, 2012 In cPanel WHM, don't forget to restart the MySQL service. This worked for me when I had to add those lines in my my.cnf file. :) Link to comment Share on other sites More sharing options...
Dmacleo Posted June 13, 2012 Author Share Posted June 13, 2012 oh yeah done that a few times today , lol got shoutbox sql error log every time too :P Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.