Решено: ЗаОптимизировал MySQL (вусмерть)

[JwsD]

ВСем доброго времени. На одном сервере стояла жесткая проблема с MySQL. Вот оптимизировал его хорошо. ВСе отлично стало. Но после того, как сервер поработает 2 дня, и мускуль сожрет 1.5 гигов памяти из 4, больше не может, то начинаются тормоза, а точнее slow-queries лезть, активность к диску дикая появляется. Вы сами по графикам сейчас видите как она спала, когда ребутнул mysql. Ниже прилагаю графики:


Подозреваю что ему не хватает ядренного кеша ФС. Вот мой конфиг:

Код: Выделить всё

[client]
port        = 3306
socket        = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket        = /var/run/mysqld/mysqld.sock
nice        = 0
[mysqld]
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket        = /var/run/mysqld/mysqld.sock
port        = 3306
basedir        = /usr
datadir        = /var/lib/mysql
tmpdir        = /tmp
language    = /usr/share/mysql/english
skip-external-locking
key_buffer        = 830M
max_allowed_packet    = 8M
thread_stack        = 256K
thread_cache_size    = 32
myisam-recover        = BACKUP
max_connections         = 400
wait_timeout         = 100
table_cache             = 500K
###my_start####
query_cache_limit       = 20M
query_cache_size        = 256M
max_join_size     = 1000000
join_buffer_size = 32M
max_sort_length  = 20
key_buffer_size = 2500M
sort_buffer_size = 64M
sort_buffer     = 128M
record_buffer      = 64M
read_rnd_buffer_size = 128M
read_buffer      =  256M
myisam_sort_buffer_size = 512M
net_read_timeout    = 12
net_write_timeout    = 15
wait_timeout        = 30
tmp_table_size        = 256M
max_tmp_tables        = 4096
#####my_end###
log_slow_queries    = /var/log/mysql/mysql-slow.log
expire_logs_days    = 10
max_binlog_size         = 256M
skip-bdb
innodb_buffer_pool_size = 16M
[mysqldump]
quick
quote-names
max_allowed_packet    = 16M
[mysql]
[isamchk]
key_buffer        = 16M
!includedir /etc/mysql/conf.d/

Мне нужно не допустить съедания mysql'ом больше 1 гига памяти, какие параметры нужно уменьшать?

[arkhnchul]

key-buffer попробуйте...

[JwsD]

Поставил key_buffer = 512M, Или может еще меньше нужно?
Ждемс...

[arkhnchul]

экспериментально подбирать.

[distress]

Код: Выделить всё

key_buffer        = 830M
key_buffer_size = 2500M

не одно и то же ? и второе не перетрет первое? да и key_buffer_size наверно правильнее будет, т.к.:

Код: Выделить всё

mysql> show variables like "key_buffer%";
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| key_buffer_size | 499998720 |
+-----------------+-----------+
1 row in set (0.00 sec)

Код: Выделить всё

tmp_table_size        = 256M
max_tmp_tables        = 4096

256M x 4096= 1048576M -> как бы много памяти съест если будут активно плодиться большие временные таблицы. ага?

еще для корректного конфига временных таблиц (это для версии mysql 5.0... хз как там в 5.1+) надо указать max_heap_table_size равный tmp_table_size.

[Kreept]

Была похожая проблема, для уменьшение нагрузки на винт изменил тип таблиц в innodb, нагрузка значительно снизилась. Но нужно учитывать, что при крахе большая вероятность потери данных.

[neol]

Проще взять my-large.cnf ( В Debian лежит в /usr/share/doc/mysql-server-5.0/examples ) и танцевать от него, поглядывая на SHOW STATUS, чем этот конфиг допиливать. Вписать цифры побольше может любой дурак, только к оптимизации это относится слабо.

еще для корректного конфига временных таблиц (это для версии mysql 5.0... хз как там в 5.1+) надо указать max_heap_table_size равный tmp_table_size. т.е.

max_heap_table_size определяет максимальный размер таблицы в памяти (если временная таблица больше, она сбрасывается на диск). Не всегда стоит выкручивать этот параметр на максимум, тем более при таких диких значениях tmp_table_size.

Была похожая проблема, для уменьшение нагрузки на винт изменил тип таблиц в innodb, нагрузка значительно снизилась. Но нужно учитывать, что при крахе большая вероятность потери данных.

В такие моменты очень жаль, что нецензурная лексика на этом замечательном форуме находится под запретом. RTFM.
UPD Имеется ввиду, что MyISAM при прочих равных быстрее InnoDB, ибо значительно проще устроен. При криво настроенном сервере возможно обратное утверждение, но это не повод советовать переводить все базы на InnoDB.

[Kreept]

В такие моменты очень жаль, что нецензурная лексика на этом замечательном форуме находится под запретом. RTFM.
UPD Имеется ввиду, что MyISAM при прочих равных быстрее InnoDB, ибо значительно проще устроен. При криво настроенном сервере возможно обратное утверждение, но это не повод советовать переводить все базы на InnoDB.

Если возрастает количество запросов, которые при "нормальном режиме" выполняются быстро, то при использовании MyISAM происходит блокирование таблиц, что приводит к возрастанию нагрузки на сервер (особенно на диск). При использовании InnoDB таких проблем не возникает -- "Формат данных InnoDB обеспечивает надежное хранение данных за счет транзакционности и блокирование данных на уровне СТРОКИ." Innodb намного быстрее работает если постоянно происходит запись и чтение в таблицу.

[JwsD]

Всем спасибо, удалось настроить mysql до нормального состояния. Теперь работает как часы! Если кому нужно, вот мой конфиг:

cat /etc/mysql/my.cnf | grep -v "#" | grep -v "^$

[client] port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language = /usr/share/mysql/english skip-external-locking skip-networking key_buffer = 256M max_allowed_packet = 8M thread_cache_size = 8 myisam-recover = BACKUP max_connections = 400 wait_timeout = 100 table_cache = 256 thread_concurrency = 8 query_cache_limit = 20M query_cache_size = 256M max_join_size = 1000000 join_buffer_size = 32M max_sort_length = 20 sort_buffer_size = 8M read_buffer_size = 8M record_buffer = 32 read_rnd_buffer_size = 64M myisam_sort_buffer_size = 128M net_read_timeout = 12 net_write_timeout = 15 wait_timeout = 30 tmp_table_size = 8M max_tmp_tables = 8 log_slow_queries = /var/log/mysql/mysql-slow.log log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 512M skip-bdb innodb_buffer_pool_size = 16M [mysqldump] quick quote-names max_allowed_packet = 16M [mysql] [isamchk] key_buffer = 16M !includedir /etc/mysql/conf.d/

А из этих картинок можете наблюдать результаты)
Предупреждаю, у меня там включен bin-log так что, уважаемые, вырубайте если не нужен))