[Maria|My]SQL: советы и шпаргалки

Настройка /etc/my.cnf

Установка профайлера и анализатора настроек:

sudo yum install -y mysqltuner

Запуск:

mysqltuner

После запуска и анализа структур и производительности БД следует обратить внимание на замечания, начинающиеся с символов [!!] (например, на число фрагментированных таблиц) и на раздел Recommendations.

Пример:

.....

-------- Recommendations ----------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Enable the slow query log to troubleshoot bad queries
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Set thread_cache_size to 4 as a starting value
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)

Здесь tmp_table_size, max_heap_table_size, thread_cache_size — параметры, которые [мо|ну]жно подкрутить в /etc/my.cnf

Перезапуск движка после изменения настроек:

sudo systemctl restart mariadb.service

!Note: Естественно, надо учитывать, что чем дольше был запущен сервер БД, тем точнее статистика, выдаваемая mysqltuner

Конвертация между разными движками БД: MyISAM ↔ Innodb

Цели

Поскольку, бытует мнение, что MyISAM быстрее на операциях чтения, а типичное веб-приложение (а тем более блог с единственным автором/редактором) — это 98% чтения, то разумно было бы перейти на этот движок.

-------- Performance Metrics -------
[--] Up for: 16d 20h 51m 37s (2M q [1.388 qps], 36K conn, TX: 9B, RX: 601M)
[--] Reads / Writes: 73% / 27%

— Как показывает статистика mysqltuner предположение о сооотношении не совсем верное, но надо учесть, что это не один хост блога, и потом — идёт интенсивное редактирование статей вкупе с малым числом посетителей.

Как?

SQL-запросом:

USE db_name;
ALTER TABLE table_name ENGINE=InnoDB;

через CLI:

mysql -u root -p -e "SELECT concat('ALTER TABLE ', table_name,' ENGINE=MYISAM;') FROM Information_schema.TABLES WHERE TABLE_SCHEMA = 'db_name' AND ENGINE = 'InnoDB' AND TABLE_TYPE = 'BASE TABLE'" | tail -n+2 >> alter.sql

где db_name имя нужной БД

Или через phpMyAdmin:

1. Выбрать БД, которую надо сконвертить
2. Выбрать вкладку SQL
3. Вставить в поле SQL-запрос из предыдущего способа, убедившись в правильности написания table_name
4. Нажать Go!

Накатка phpMyAdmin

Поскольку у меня стоит php 7.0 из репы webtatic, то устанавливаем вместе с phpMyAdmin соответствующий версии (и репе) php пакет mcrypt:

$ sudo yum install -y phpMyAdmin php70w-mcrypt

Если есть желание ходить на phpMyAdmin секьюрно (https), то стоит не забыть поставить в конфиге nginx следующий параметр:

fastcgi_param HTTPS on;

Полный текст файла:

$ cat /etc/nginx/phpmyadmin.conf

#### phpmyadmin instance ####
location /pma/ {
 alias /usr/share/phpMyAdmin/;
 index index.php;
}

location ~ /pma/(libraries|setup/frames|setup/libs) {
 deny all;
 return 404;
}

location ~ ^/pma/(.+\.php)$ {
 alias /usr/share/phpMyAdmin/$1;
 fastcgi_pass unix:/var/run/php-fpm/php-fpm.sock;
 fastcgi_index index.php;
 fastcgi_param SCRIPT_FILENAME $request_filename;
 fastcgi_param HTTPS on;
 include fastcgi_params; 
 }

Затем инклюдим этот phpmyadmin.conf в любой конфиг хоста с https-транспортом и ходим по ссылке вида https://ваш_хост/pma/

Troubles

Ну и не обошлось конечно без сюрпризов. Тщательным гуглением было выяснено, что php апечеориентированный и пермишены стояли неверно для /var/lib/php/* и отсутствовала подпапка session, исправляем это (за0дно и /var/lib/phpMyAdmin поправим — там тоже апач владельцем числится):

$ sudo mkdir /var/lib/php/session

$ sudo chown nginx: -R /var/lib/php
$ sudo chown nginx: -R /var/lib/phpMyAdmin