我的服务器运行良好，直到最近更新 Plesk，现在速度变慢了。我的服务器技术人员不得不重建 Apache 配置文件，从那时起网站就一直很慢。
该服务器是 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 tuner 的最新结果
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
答案1
当它运行缓慢时，我会运行“显示完整进程列表”，并启用慢速日志。
log-slow-queries = slow.log
long_query_time = 20
log-queries-not-using-indexes
这是为了了解究竟哪些查询正在杀死该框，因为它可能是没有索引的情况的连接。
另外，一些其他优化可能会减少你的 innodb 缓冲池，因为它大部分都没有使用
[OK] InnoDB 数据大小/缓冲池：111.0M/5.0G
增加密钥缓冲区大小：
[确定] 键缓冲区大小/总 MyISAM 索引：525.0M/504.2M
增加 tmp_table_size，这样临时表就不会写入磁盘
tmp_table_size (> 100M)
max_heap_table_size (> 100M)
减少缓冲区分配，因为缓冲区分配太高，可能会使你的机器在负载下挂起
[!!] 最大可能的内存使用量：1960.1G（已安装 RAM 的 12495%）
在 mysql 社区下载中，至少在源 tarball 中，有小/中/大/巨大的 my.cnf 示例，我可能会用“大”my.cnf（如果是专用的 mysql，则为“巨大”）替换一些现有的缓冲区值。记住一次进行一个更改，然后进行加载测试。