Правильная настройка mysql, nginx, php-fpm

450

16 мая 2017, 03:06

Посещаемость сайта в сутки 3000 человек. На сайте есть моменты времени, когда с каждого пользователя(а их около 200 человек онлайном) поступает около 100-150 запросов в минуту, итог, сайт падает с 502 ошибкой nginx. На сервере грузятся все процессы, а занятая память не превышает 1100М.

Конфигурация сервера: Intel Xeon CPU E3-1230 v3 @ 3.30GHz 2400.000 Mhz, 32 GB RAM, 256 SSD, 1 GB канал.

Настрйоки nginx 1.10.1-1:

worker_processes 8;
error_log /var/log/nginx/error.log crit;
worker_rlimit_nofile 200000;
events {
 worker_connetctions 1024;
 multi_accept on;
 use epoll;
}
sendfile on;
keepalive_timeout 30;
keepalive_requests 1000;
proxy_connect_timeout 604800;
proxy_send_timeout 604800;
proxy_read_timeout 604800;

Включено gzip сжатие 5 уровня.

На php-fpm 5.6 увеличил memory_limit на 512M.

Настройка mysql 5.5.55:

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock
[mysqld_safe]
log-error=/var/log/mysql.log
socket      = /var/run/mysqld/mysqld.sock
nice        = 0
[mysqld]
collation-server = utf8_general_ci
character-set-server = utf8
local-infile=0
table_open_cache = 3072
table_definition_cache = 4096
max_heap_table_size = 64M
tmp_table_size = 64M
max_connections = 505
max_user_connections = 500
max_allowed_packet = 16M
thread_cache_size = 32
query_cache_size = 64M
#
# * Basic Settings
#
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
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address        = 127.0.0.1
[mysqldump]
quick
single-transaction
max_allowed_packet = 16M
[mysql]
no_auto_rehash
[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqld_safe]
open-files-limit = 8192
log-error = /var/log/mysql/error.log
expire_logs_days    = 10
max_binlog_size         = 100M
bind-address = 127.0.0.1
# MyISAM
default_table_type             = MyISAM
# 80% of ram that is dedicated for the database (this needs to be adjusted to your system)
innodb_buffer_pool_size        = 24G
# number of CPU cores dedicated to the MySQL MyISAM backend 
innodb_buffer_pool_instances = 16 
innodb_data_file_path          = ibdata1:128M:autoextend
innodb_file_per_table          = 1
innodb_log_file_size           = 512M
innodb_log_files_in_group      = 2
# MyISAM
myisam_recover                 = backup,force
[mysqldump]
quick
quote-names
max_allowed_packet  = 16M
[isamchk]
key_buffer      = 16M
!includedir /etc/mysql/conf.d/

Пытался настроить по: https://dba.stackexchange.com/questions/50941/the-best-mysql-settings-for-32gb-ram-on-a-dedicated-server

Статистика htop сервера во время нагрузки:

Answer 1

MySQL использует подозрительно мало памяти. Скорее всего, огромный запрос на innodb_buffer_size = 24G, да еще умноженный на 16 innodb_buffer_pool_instances, просто игнорируется MySQL. Вы запрашиваете ни много ни мало 384G памяти под innodb. Где их взять? У вас совокупный объем памяти 32 + 1 GB. Скорее всего, в логе mysql есть ошибки, проверьте.

Число innodb_buffer_pool_instances ставят обычно по числу процессоров, а общий объём не должен превышать 80% памяти. Да, эту дурацкую рекомендацию я читал не раз. В ней всего одна ошибка - у вас ВСЕ в системе должно влезать в 80% памяти - mysql, php-fpm, nginx.

Для mysql есть неплохой калькулятор (только они забыли про innodb_buffer_pool_instances).

Так что в вашем случае я бы поставил

innodb_buffer_size = 2.4G
innodb_buffer_pool_instances = 8

Дальше. В mysql есть совершенно непонятная мне проблема - по умолчанию запросы не кэшируются: query_cache_type = OFF.

Добавление примерно таких настроек у меня на сервере радикально изменило скорость доступа к базе.

query_cache_type = ON
query_cache_size = 128M
query_cache_limit = 2M

php вы зря увеличили memory limit - он столько не просит, судя по htop. Оставьте 128M по умолчанию в php7. А, да - так у вас php 5.6! Сносите его и ставьте php7. Семерка почти вдвое быстрее, а судя по htop, у вас php сидит на процессоре.

И постарайтесь кэшировать все, что возможно в вашем приложении. Более подробные советы по настройке сервера на высокую производительность можно посмотреть в моей статье "10,000 соединений в секунду на WordPress".

Answer 2

Судя по скриншотам, у вас падает сервер из-за нагрузки MySQL. Дело не в серверных настройках (здесь все относительно нормально), а в архитектуре вашего web-приложения. А учитывая, что у вас достаточно хорошая тачка "Intel Xeon CPU E3-1230 v3 @ 3.30GHz 2400.000 Mhz, 32 GB RAM, 256 SSD, 1 GB канал.", то можно однозначно сказать, что ее рушит неправильно спроектированное приложение.

Вот несколько советов:

Индексы

Важно! Думаю это и есть причина ваших падений.

Очень подозреваю, что учитывая ваше достаточно хорошее железо и небольшую нагрузку на сайт у вас не выставлены или неправильно выставлены в таблицах индексы. При правильной настройке индексов (под профиль нагрузки) MySQL отлично справляется гораздо более высокими нагрузками и на более слабом железе.

Оптимизация запросов

Посмотрите через профайлер (например, XDebug) какие запросы выполняются наиболее долго и проведите их оптимизацию.

Если INSERT/UPDATE-запросы в коде идут подряд, то отправляйте их на сервер пачкой, а не по одному. Также используйте транзакции.

Сократите общее количество запросов к базе. Где можно обойтись одним запросом, не делайте их несколько. Особенно обратите внимание на запросы, которые делаются в циклах (например, часто встречается в лентах новостей, личных сообщениях и других блоках, где есть агрегация данных). Это надо исключить. Запрашивайте данные одним запросом (например, в первом запросе определяете id, по которым надо сделать второй запрос с данными id, а далее выполняете сопоставление данных - подставляете недостающие для первого запроса данные полученными из второго).

Memcached

Установите для повторяющихся (SELECT) и редко меняющихся (UPDATE) запросов сервер Memcached. Выделите 3-4Gb на кэширование запросов.

ПОСЛЕДНИЕ ВОПРОСЫ

Все вопросы

ТОП-10

Все популярные вопросы

SQLiteLog: (1) near “Result”: syntax error

ЗдравствуйтеПишу курсор для заполнения listview

379

Java

Многопоточная сортировка слиянием. java

Задание: написать многопоточную сортировку слиянием, на вход подаётся массив и число потоков

710

Java

Android Studio настройка Spinner

Здравствуйте! У ComboBox в VS имеются свойства DisplayMember и ValueMember, но у Spinner-а я подобных не обнаружил:( Почитав переполнение стека, я нашел решение...

546

Java

Как унаследовать класс А от двух абстрактных классов B и C?

Класс А должен быть унаследован от двух абстрактных классов B и CКак это можно сделать?

407