my.cnf和php.ini出现问题 – 使用mysql tuner

我的服务器运行良好，直到Plesk最近更新，现在一直很慢。 我的服务器技术不得不重buildApacheconfiguration文件，并且自从网站速度缓慢。

服务器是1and1的XL8专用盒子。 它有16GB的内存和一个8核心的CPU。 该服务器包含2个相当繁忙的vbulletin论坛和一些较小的网站。

这是我设置的当前my.cnf –

[mysqld] #bind-address=127.0.0.1 #skip-bdb local-infile=0 max_connections=90 open_files_limit=2050 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 query_cache_limit = 1M query_cache_size = 15M join_buffer_size = 512K read_buffer_size = 1M tmp_table_size = 3024M thread_cache_size = 8 table_cache = 450 innodb_buffer_pool_size = 540M key_buffer_size = 32M table_definition_cache = 4024 max_allowed_packet = 35M max_heap_table_size = 3024M [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid 

这里是来自mysql调谐器的最新结果

• mysql缓慢的查询不写入日志
• 诊断零星的MySQL错误
• 在已经进入只读模式的debian系统上运行mysqldump
• 安装mytop时出错：需要：libmysqlclient.so.16
• 无法启动生产MySQL服务器。 没有日志消息

 Last login: Tue Sep 24 19:28:02 2013 from ip70-181-17-116.ri.ri.cox.net [root@u16557714 ~]# ./mysqltuner.pl >> MySQLTuner 1.2.0 - Major Hayden <[email protected]> >> 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.69 [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 1G (Tables: 3316) [--] Data in InnoDB tables: 111M (Tables: 487) [--] Data in MEMORY tables: 372K (Tables: 329) [!!] Total fragmented tables: 612 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 4m 15s (10K q [42.020 qps], 506 conn, TX: 286M, RX: 2M) [--] Reads / Writes: 80% / 20% [--] Total buffers: 4.5G global + 3.1M per thread (90 max threads) [OK] Maximum possible memory usage: 4.8G (30% of installed RAM) [OK] Slow queries: 0% (1/10K) [OK] Highest usage of available connections: 13% (12/90) [OK] Key buffer size / total MyISAM indexes: 1.0G/504.2M [OK] Key buffer hit rate: 98.0% (131K cached / 2K reads) [OK] Query cache efficiency: 71.4% (5K cached / 8K selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 429 sorts) [!!] Joins performed without indexes: 6 [!!] Temporary tables created on disk: 34% (109 on disk / 320 total) [OK] Thread cache hit rate: 95% (25 created / 506 connections) [OK] Table cache hit rate: 97% (330 open / 337 opened) [OK] Open file limit used: 4% (509/12K) [OK] Table locks acquired immediately: 99% (4K immediate / 4K locks) [OK] InnoDB data size / buffer pool: 111.0M/540.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate Enable the slow query log to troubleshoot bad queries Adjust your join queries to always utilize indexes Temporary table size is already large - reduce result set size Reduce your SELECT DISTINCT queries without LIMIT clauses Variables to adjust: join_buffer_size (> 512.0K, or always use indexes with joins) [root@u16557714 ~]# /etc/init.d/mysqld restart Stopping mysqld: [ OK ] Starting mysqld: [ OK ] [root@u16557714 ~]# ./mysqltuner.pl >> MySQLTuner 1.2.0 - Major Hayden <[email protected]> >> 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.69 [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 1G (Tables: 3316) [--] Data in InnoDB tables: 111M (Tables: 487) [--] Data in MEMORY tables: 124K (Tables: 329) [!!] Total fragmented tables: 613 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 52s (683 q [13.135 qps], 39 conn, TX: 27M, RX: 229K) [--] Reads / Writes: 90% / 10% [--] Total buffers: 4.5G global + 3.1M per thread (90 max threads) [OK] Maximum possible memory usage: 4.8G (30% of installed RAM) [OK] Slow queries: 0% (0/683) [OK] Highest usage of available connections: 2% (2/90) [OK] Key buffer size / total MyISAM indexes: 1.0G/504.3M [!!] Key buffer hit rate: 92.2% (11K cached / 928 reads) [OK] Query cache efficiency: 38.5% (194 cached / 504 selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 54 sorts) [!!] Joins performed without indexes: 1 [OK] Temporary tables created on disk: 19% (8 on disk / 41 total) [OK] Thread cache hit rate: 94% (2 created / 39 connections) [OK] Table cache hit rate: 94% (113 open / 120 opened) [OK] Open file limit used: 1% (212/12K) [OK] Table locks acquired immediately: 100% (656 immediate / 656 locks) [OK] InnoDB data size / buffer pool: 111.0M/540.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate Enable the slow query log to troubleshoot bad queries Adjust your join queries to always utilize indexes Variables to adjust: join_buffer_size (> 512.0K, or always use indexes with joins) [root@u16557714 ~]# /etc/init.d/mysqld restart Stopping mysqld: [ OK ] Starting mysqld: [ OK ] [root@u16557714 ~]# ./mysqltuner.pl >> MySQLTuner 1.2.0 - Major Hayden <[email protected]> >> 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.69 [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 1G (Tables: 3316) [--] Data in InnoDB tables: 111M (Tables: 487) [--] Data in MEMORY tables: 124K (Tables: 329) [!!] Total fragmented tables: 612 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 25s (411 q [16.440 qps], 25 conn, TX: 14M, RX: 152K) [--] Reads / Writes: 90% / 10% [--] Total buffers: 4.0G global + 3.1M per thread (90 max threads) [OK] Maximum possible memory usage: 4.3G (27% of installed RAM) [OK] Slow queries: 0% (0/411) [OK] Highest usage of available connections: 3% (3/90) [!!] Key buffer size / total MyISAM indexes: 500.0M/504.2M [!!] Key buffer hit rate: 92.5% (8K cached / 610 reads) [OK] Query cache efficiency: 29.7% (91 cached / 306 selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 39 sorts) [!!] Joins performed without indexes: 1 [OK] Temporary tables created on disk: 15% (3 on disk / 19 total) [OK] Thread cache hit rate: 88% (3 created / 25 connections) [OK] Table cache hit rate: 92% (81 open / 88 opened) [OK] Open file limit used: 7% (148/2K) [OK] Table locks acquired immediately: 100% (381 immediate / 381 locks) [OK] InnoDB data size / buffer pool: 111.0M/540.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate Enable the slow query log to troubleshoot bad queries Adjust your join queries to always utilize indexes Variables to adjust: key_buffer_size (> 504.2M) join_buffer_size (> 512.0K, or always use indexes with joins) [root@u16557714 ~]# /etc/init.d/mysqld restart Stopping mysqld: [ OK ] Starting mysqld: [ OK ] [root@u16557714 ~]# ./mysqltuner.pl >> MySQLTuner 1.2.0 - Major Hayden <[email protected]> >> 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.69 [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 1G (Tables: 3316) [--] Data in InnoDB tables: 111M (Tables: 487) [--] Data in MEMORY tables: 1M (Tables: 329) [!!] Total fragmented tables: 613 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 13s (262 q [20.154 qps], 24 conn, TX: 15M, RX: 64K) [--] Reads / Writes: 83% / 17% [--] Total buffers: 3.5G global + 4.0M per thread (90 max threads) [OK] Maximum possible memory usage: 3.9G (24% of installed RAM) [OK] Slow queries: 0% (0/262) [OK] Highest usage of available connections: 2% (2/90) [!!] Key buffer size / total MyISAM indexes: 32.0M/504.2M [!!] Key buffer hit rate: 89.5% (2K cached / 240 reads) [OK] Query cache efficiency: 46.4% (78 cached / 168 selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 13 sorts) [OK] Temporary tables created on disk: 0% (0 on disk / 5 total) [OK] Thread cache hit rate: 91% (2 created / 24 connections) [OK] Table cache hit rate: 88% (54 open / 61 opened) [OK] Open file limit used: 5% (103/2K) [OK] Table locks acquired immediately: 100% (197 immediate / 197 locks) [OK] InnoDB data size / buffer pool: 111.0M/540.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate Enable the slow query log to troubleshoot bad queries Variables to adjust: key_buffer_size (> 504.2M) 

任何意见或援助将非常感激。

非常感谢！！

这是我最新的

  [mysqld] #bind-address=127.0.0.1 #skip-bdb local-infile=0 max_connections=90 open_files_limit=2050 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 query_cache_limit = 1M query_cache_size = 15M join_buffer_size = 512K read_buffer_size = 1M tmp_table_size = 100M thread_cache_size = 8 table_cache = 450 innodb_buffer_pool_size = 540M key_buffer_size = 32M table_definition_cache = 4024 max_allowed_packet = 35M max_heap_table_size = 100M [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid