Ускоряем MySQL insert/update в 5-10 раз

Немного теории. В операционных системах UNIX существует раздел файловой системы, который физически находится в оперативной памяти, но позволяет работать с ним как с обычным дисковым накопителем. Скорость доступа к блоку жесткого диска приблизительно равна 1 мс. Скорость доступа к памяти — 0.001 мс. Попробуем применить это к БД MySQL, чтобы выжать максимум из операций insert/update.

Сперва проверим скорость случайной записи на жесткий диск:

time sysbench --test=fileio --file-total-size=100M --file-num=1000 prepare
    1000 files, 102Kb each, 99Mb total
    real    0m47.682s

Теперь то же самое для shared memory (/run/shm или /dev/shm):

time sysbench --test=fileio --file-total-size=100M --file-num=1000 prepare
    1000 files, 102Kb each, 99Mb total
     real    0m0.083s

Сравним результаты и увидим, что время создания 1000 файлов уменьшилось в 574 раза. Хорошо. Значит, следует ожидать прирост скорости записи в БД.

Выполняем следующее:

1) Проверяем размер и свободное место для /run/shm

df -h | grep -P "Filesystem|shm"
    Filesystem      Size  Used Avail Use% Mounted on
    none               16G  782M  15,2G  4.8% /run/shm

2) Проверяем сколько места занимает БД

sudo ls -lh /var/lib/mysql/ | grep ibdata1
-rw-rw---- 1 mysql mysql 4.5G июня  14 05:21 ibdata1

Значит, что базу мы можем перенести в /run/shm

3) Останавливаем MySQL:

sudo service mysql stop

4) Создаем директории и копируем данные:

sudo mkdir /run/shm/mysql-lib
sudo chown mysql:mysql /run/shm/mysql-lib
sudo cp -rp /var/lib/mysql/* /run/shm/mysql-lib/

5) Правим конфиг:

sudo nano /etc/mysql/my.cnf
[mysqld]
#datadir       = /var/lib/mysql
datadir         = /run/shm/mysql-lib

6) Правим AppArmor:

sudo nano /etc/apparmor.d/usr.sbin.mysqld
#/var/lib/mysql/ r,
#/var/lib/mysql/** rwk,
/run/shm/mysql-lib/ r,
/run/shm/mysql-lib/** rwk,

7) Запускаем MySQL

sudo service mysql start

* Если сервис не стартует — смотрим /var/log/mysql/error.log

Теперь самое интересное. Проверяем, что получилось.

Тест на жестком диске я провел заранее, поэтому сразу привожу результаты.

                            HDD         Shared mem

Insert 10 000 000 строк     202 сек     66 сек
(bulk = 1000)               

Update 100 000 раз          122 сек     16 сек
(id = rnd)

Update выполнялся по случайному интервалу [1 000 000 — 9 000 000] для первичного ключа (id). Крайние значения отброшены, чтобы движок «копался» внутри таблицы.

Существенный прирост скорости на INSERT и еще больший на UPDATE.
Меньше для вставки, т.к. MySQL производит пересчет индексов и организацию данных.

В настройках MySQL выставлено:
innodb_buffer_pool_size = 1024M
Если ставить меньше, то скорость UPDATE для HDD естественно падает.

innodb_flush_log_at_trx_commit = 2
Как таковых транзакций здесь у нас нет и на скорость это не влияет. Тем не менее, оставляем это значение равным 2.

При такой схеме критически важно писать бинарный лог и регулярно делать бэкап. Максимально снизить издержки записи мы можем только указав для бинарника отдельный жесткий диск. Последовательная скорость записи на винчестер намного выше случайной. Поэтому ставим в систему дополнительный жесткий диск, монтируем его, например, в /mnt/hddbin/, и указываем в my.ini путь для бинарного лога: log_bin = /mnt/hddbin/mysql-bin.log

Не забываем добавить скрипты для перезагрузки и останова системы. Смотрим папку /etc/rc*. Обычно это 0 (отключение системы) и 6 (перезагрузка). Мануал, как добавить скрипты легко найти в гугле. Скрипт перед перезагрузкой или выключением системы останавливает MySQL, затем копирует папку /run/shm/mysql-lib на жесткий диск. При включении системы скрипт восстанавливает данные с жесткого диска в папку /run/shm/mysql-lib и после запускает MySQL.

Так же добавляем простенький bash или perl скрипт для мониторинга свободной памяти в /run/shm. Можно подключить Zabbix.