Мой сервер работал нормально до недавнего обновления в Plesk, а теперь он работает медленно. Моему технику по серверу пришлось перестроить файл конфигурации Apache, и с тех пор сайты работают медленно.
Сервер - это выделенная коробка XL8 от 1and1. у него 16 ГБ оперативной памяти и 8-ядерный процессор. Сервер содержит 2 довольно загруженных форума с электронными бюллетенями и несколько небольших сайтов.
Вот текущий 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
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 <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.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 <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.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 <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.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 <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.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
Я бы запустил «показать полный список процессов», когда он работает медленно, а также включил бы медленный журнал.
журнал-медленные-запросы = медленный.log
long_query_time = 20
Журнал запросов, не использующих индексы
это сделано для того, чтобы получить представление о том, какие именно запросы убивают блок, поскольку это может быть соединение без индексации.
Кроме того, некоторые другие оптимизации могут уменьшить ваш буферный пул innodb, поскольку он в основном не используется
[OK] Размер данных InnoDB / буферный пул: 111.0M / 5.0G
увеличение размера ключевого буфера:
[OK] Размер ключевого буфера / общее количество индексов MyISAM: 525.0M / 504.2M
увеличение tmp_table_size, чтобы таблицы tmp не записывались на диск
tmp_table_size (> 100 МБ)
max_heap_table_size (> 100 МБ)
уменьшить выделение буфера, так как это слишком много и может зависнуть под нагрузкой.
[!!] Максимально возможное использование памяти: 1960,1 ГБ (12495% установленной ОЗУ)
в загрузке сообщества mysql, по крайней мере, в архиве исходных текстов, есть примеры малых / средних / больших / огромных примеров my.cnf, и я, вероятно, заменил бы некоторые из ваших существующих значений буфера на "большие" my.cnf (или " огромный ", если это посвященный mysql). не забудьте внести изменения по одному, а затем выполнить нагрузочный тест.