Как оптимизировать MySQL под 6-8гб ОП?

Question

[Кирилл]

Помогите пожалуйста оптимизировать конфиг

/etc/mysql/mysql.conf.d/mysqld.cnf

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0

[mysqld]
#
# * 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

key_buffer_size = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8

myisam-recover-options = BACKUP

query_cache_type=2
query_cache_limit = 1M
query_cache_size = 32M

log_error = /var/log/mysql/error.log

expire_logs_days = 10
max_binlog_size = 100M

#Не используем старый хеш
old_passwords=0

#Защита сервера от кривых рук
max_join_size=1000000

#размер буфера, выделяемого под индексы
key_buffer_size = 4096M
#Полезно для запросов с сортировкой ORDER BY и группировкой GROUP BY
read_rnd_buffer_size=4M
sort_buffer_size=8M
#Память для запросов с джойнами
join_buffer_size=16M
#Память InnoDB для хранения индексов и данных
innodb_buffer_pool_size=4096M
#Размер буфера транзакций, которые не были еще закомичены
innodb_log_buffer_size=4M

#Увеличивает пропускную способность записи
innodb_flush_method=O_DSYNC
innodb_io_capacity=2000
innodb_io_capacity_max=6000
innodb_lru_scan_depth=2000
table_open_cache_instances=16
innodb_checksum_algorithm=crc32
#Увеличивает пропускную способность записи
innodb_flush_log_at_trx_commit=2
#Быстрый запуск MySQL сервера
innodb_buffer_pool_dump_at_shutdown=ON
innodb_buffer_pool_load_at_startup=ON
innodb_flush_neighbors=0

Comments

[sim3x]

Загрузите картинку на хабрасторадж

[Кирилл]

sim3x, загрузил

Answer 1

[Игорь Горгуль]

Используйте mysqltuner

Comments

[hx510b]

wget mysqltuner.pl -O mysqltuner.pl && perl mysqltuner.pl

Answer 2

[Станислав Бодро́в]

Если у тебя:
server's role - production
dedicated server
type of workload will this server - OLTP
type of storage - HDD RAID
CPUs does your system - 4
memory does your server - 6 GB
operating system - Linux
tables will you - 100
MySQL server version - 5.6
preferred storage engine - InnoDB
variant of MySQL - MariaDB
thread cache - 50
key buffer - 32 MB
connection limit - 250

конфиг примерно следующий
[mysql]
# CLIENT #
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]
# GENERAL #
user = mysql
default-storage-engine = InnoDB
socket = /var/lib/mysql/mysql.sock
pid-file = /var/lib/mysql/mysql.pid

# MyISAM #
key-buffer-size = 32M
myisam-recover-options = FORCE,BACKUP

# SAFETY #
max-allowed-packet = 16M
max-connect-errors = 1000000
skip-name-resolve

# DATA STORAGE #
datadir = /var/lib/mysql/

# BINARY LOGGING #
log-bin = /var/lib/mysql/mysql-bin
expire-logs-days = 14
sync-binlog = 1

# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 250
thread-cache-size = 25
open-files-limit = 65535
table-definition-cache = 1024
table-open-cache = 2048

# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 256M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 4G

# LOGGING #
log-error = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log

Comments

[hx510b]

это не очень верный ответ, т.к. неизвестно какие типы движков используются. если не используется MyISAM то нет смысла ему выделять память, и наоборот.
innodb-buffer-pool-size над делать под размер базы или чуть с запасом на вырост - цель чтобы mysql мог держать всю базу в памяти, если памяти больше чем размер базы.
если же размер базы значительно превышает размер ОЗУ то надо выделять памяти под завязку, за вычетом нужд системы и других процессов. Поэтому когда огульно выделяют половину ОЗУ - это странный подход.
250 коннектов тоже непонятно почему столько? каждый коннект жрет память под буфера, если реально максимум до десятка соединений то такой запас не нужен. А если выделить памяти больше, чем ее есть физически можно доиграться до OOM killer, уж лучше mysql прекратит принимать часть коннектов, чем его прибьют в разгар транзакции

[Станислав Бодро́в]

это не очень верный ответ, т.к. неизвестно какие типы движков используются. если не используется MyISAM то нет смысла ему выделять память, и наоборот.

Можешь это ребзям из Перконы рассказать. Это их рук дело. Они вроде как спецы, поэтому даже не протестую, когда работаю с их планировщиком конфигов.

innodb-buffer-pool-size над делать под размер базы или чуть с запасом на вырост - цель чтобы mysql мог держать всю базу в памяти, если памяти больше чем размер базы.

А операционка всё до последнего аллоцирует, ничего не зажмёт, не посчитает, что вот этот файл из /var/lib/mysql/..., к которому давно не было обращений, пусть полежит на диске?

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

есть приблизительные формулы, но у них один общий недостаток - они приблизительные.

250 коннектов тоже непонятно почему столько? каждый коннект жрет память под буфера, если реально максимум до десятка соединений то такой запас не нужен.

процессы php-fpm на скрине указывают, что это всё-таки вэб-проект. Есть мнение, что сервер проектировался минимум под 10 одновременных подключений в секунду, и совсем только не 10 уникальных адресов. Поэтому 250 может быть ещё маловато.

А если выделить памяти больше, чем ее есть физически можно доиграться до OOM killer

В случае, если обладатель сервера не самодур, знает и помнит про так называемые грязные и анонимные страницы памяти, а также про механизм свопирования, когда операционная система сбрасывает на диск давно не используемые страницы памяти, то он наверное создаст во время разбиения раздел подкачки или даже выделит под него отдельный быстрый диск. Да и ООМК не так уже плох, чем решение ОСи замораживать процессы при нехватке ресурсов.

mysql прекратит принимать часть коннектов

Здесь очень важным момент - коннекты изначально принимает и обрабатывает операционная система, её планировщики (сетевой, дисковый, ОЗУ) подготавливают, обрабатывают и закрывают взаимодействия с той или иной периферией, сеть и файловая система держат свои буфера в оперативной памяти и лишь потом приложение, которое принимает коннекты. Помимо прочего там могут быть и другие приложения, всякие вэб сервера и аппликухи, которые тоже памяти хотят. На скрине вот целых два php-fpm (7.2 и 7.1), не исключено, что и nginx там болтается, также BIND виден. Поэтому не известно, что раньше может прекратить принимать соединения.

А теперь внимание на скрин от топикстартера: виртуальная память потребляемая мускулом - 1,5 ГБ, резидентная 145 МБ и больше 30 процессов мускула - все в топе. Процесс - это заявка ОСи на выделение определённых ресурсов, но и процессорного времени. Что у него там с процессором? Сколько ядер и каких?
По опыту знаю, что мускул любит потреблять ресурсы процессора. Памяти ему выделаешь хоть завались, а он всё равно процессор грузит.
Последний и важный момент - сами запросы. Что там? SELECT * FROM very_big_table, куча вложенных подзапросов, или двойное соединение, индексы для слабаков? А то может быть наши советы совсем не нужны, если с запросами порядок навести.