Ускоряем 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.
Share post

Similar posts

Comments 39

    +4
    При выключении сервера по питанию база отправится в страну вечной охоты?
      –3
      Читаем внимательно статью. Для режима 0 и 6 добавляем скрипт, который… читаем выше.
        +6
        При отключении по питанию ваши rc скрипты отрабатывать не будут, все просто умрет.
          –3
          Для этого делается бэкап и пишется бинарь. Если питание внезапно исчезает (что случается в нормальных дата-центрах крайне редко). Поднимаем последний бэкап и накатываем бинарный лог. Самое большее, что в таком случае мы теряем это транзакцию за последнюю секунду, т.к. у нас стоит innodb_flush_log_at_trx_commit = 2.
            0
            За какой промежуток времени вы собираетесь хранить бинарные логи?
              –1
              Как минимум не меньше времени между бэкапами.
          +1
          Читал внимательно. Я про неконтролируемую потерю питания и отсутствие резервирования.
            –1
            Написал только что.
            «отсутствие резервирования» — об этом написано.
              0
              Тогда по хорошему можно в эту базу писать, а в более медленную реплицировать.
                0
                Согласен. Если есть две машины — то это очень хорошее решение.
        +6
        Мне кажется, Blackhole будет ещё быстрее.
        А бинарные логи можно складировать в /dev/null
          –4
          Забавно.
          «бинарные логи можно складировать в /dev/null » = отключить бинарные логи. Так будет еще быстрее :)
            +2
            Это шутка
              –4
              Это понятно. Я так, поддержать шутку )
          +3
          Не понял, а разве не тоже самое делает MEMORY storage engine MySql? Вы не сравнивали производительность MEMORY storage engine и вашего решения?
            +1
            Кстати, при использовании MEMORY storage engine есть возможность установить master-slave репликацию на нормальные таблицы, где все будет делаться уже на уровне самой БД и там уже можно будет играться со скоростью/надежностью.
              0
              memory внутри это myisam, что накладывает свои ограничения и проблемы в т.ч бесполезность binlog
                0
                конечно, но можно ведь репликацировать данные на обычные таблицы. В целом, если автор рассматривал оба варианта хотелось увидеть разницу в производительности и функциональности. Если не рассматривал, то почему?
                  0
                  туда куда будет происходить репликация — должно быть хранилище которое тоже может переварить такую скорость.

                  имхо изначально достаточно было поставить raid1 и два SSD.
                    0
                    Ну конечно можно. Есс-но, что SSD это круто. В данном случае RAID1 не обязателен. Задача — повысить скорость записи, а не надежность.
                    0
                    Не рассматривал MEMORY, т.к. это не транзакционная таблица, как было написано в пред.коменте.
                    А схему MEMORY -> реплика в InnoDB это неудачное решение. Первичное хранилище должно поддерживать транзакции.
                    Кроме того, MEMORY плохо работает с ф-ми агрегации (заметно хуже, чем MyISAM). Возможно это было исправлено. Но в MySQL 5.1 агрегаторы SORT и GROUP BY существенно снижали скорось выборки.
                      0
                      Чтобы не быть голословным проверил на MySQL 5.5

                      time mysql -u root test_001 -e 'select sql_no_cache count(*) from table_10m_2 group by level'
                      InnoDB 0m2.536s
                      Memory 0m0.106s

                      time mysql -u root test_001 -e 'select sql_no_cache * from table_10m_2 order by level desc limit 10'
                      InnoDB 0m0.013s
                      Memory 0m0.215s

                      time mysql -u root test_001 -e 'explain select sql_no_cache * from table_10m_2 order by level desc limit 10'
                      +----+-------------+-------------+-------+---------------+-------+---------+------+------+-------------+
                      | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
                      +----+-------------+-------------+-------+---------------+-------+---------+------+------+-------------+
                      | 1 | SIMPLE | table_10m_2 | index | NULL | level | 5 | NULL | 10 | Using index |
                      +----+-------------+-------------+-------+---------------+-------+---------+------+------+-------------+

                      time mysql -u root test_001 -e 'explain select sql_no_cache * from table_10m_2_m order by level desc limit 10'
                      +----+-------------+---------------+------+---------------+------+---------+------+--------+----------------+
                      | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
                      +----+-------------+---------------+------+---------------+------+---------+------+--------+----------------+
                      | 1 | SIMPLE | table_10m_2_m | ALL | NULL | NULL | NULL | NULL | 349228 | Using filesort |
                      +----+-------------+---------------+------+---------------+------+---------+------+--------+----------------+

                      time mysql -u root test_001 -e 'select sql_no_cache * from table_10m_2 order by level desc limit 9000000,100'
                      InnoDB 0m3.529s
                      Memory 0m0.293s

                      time mysql -u root test_001 -e 'select sql_no_cache count(*) as c from table_10m_2 group by level
                      having c >= 1000 order by level desc limit 10'
                      InnoDB 0m0.436s
                      Memory 0m0.102s

                      Все запросы работаю прекрасно, кроме сортировки. Memory почему-то не использует ключ (level). Директивы FORCE INDEX или USE INDEX картины не меняют.
                        0
                        Вы какой тип индекса указали, когда MEMORY-таблицу создавали: hash или btree?
                          –1
                          Никакой не указывал. По умолчанию.
                +6
                Такой громкий заголовок:
                Ускоряем MySQL insert/update в 5-10 раз
                И далее статья исключительно про то, как переместить файлы с данными БД с харда на ram-диск. Оно вроде как всё правда и нет обмана, но чувствуется какая-то… хм, разочарованность)
                В операционных системах UNIX существует раздел файловой системы, который физически находится в оперативной памяти, но позволяет работать с ним как с обычным дисковым накопителем
                Такое даже в msdos было.
                  –2
                  Поэтому и оговорка — «немного теории». Можно считать, что это напоминалка.
                    –2
                    > И далее статья исключительно про то, как переместить файлы с данными БД с харда на ram-диск. Оно вроде как всё правда и нет обмана, но чувствуется какая-то… хм, разочарованность)
                    Если вы ожидали, что вам покажут, как преодолеть законы физики, то, конечно, я вас разочаровал.
                    +4
                    Очень громкое название для такой статьи.
                    1. Если оптимально настроить БД она и так будет вся в оперативке. Или почти вся. Тот же innodb_buffer_pool_size не зря доступен для редактирования.
                    2.
                    Как таковых транзакций здесь у нас нет и на скорость это не влияет.
                    — в InnoDB каждая операция транзакция, если не указано иное, или я путаю?
                    Ну а так спасибо, интересное решение. Никогда постараюсь не применять его на практике, но все ж имеет место быть)
                      –3
                      > Если оптимально настроить БД она и так будет вся в оперативке. Или почти вся. Тот же innodb_buffer_pool_size не зря доступен для редактирования.
                      Не будет. Можете проверить сами. Даже если innodb_buffer_pool_size поставите больше размера БД. Операции записи будут регулярно работать с диском.

                      > в InnoDB каждая операция транзакция, если не указано иное, или я путаю
                      Да, здесь вы правы. В любом случае и там и там флаш транзакций был 1 раз в секунду.
                      +1
                      Вероятно, если хочется использовать такое решение, то следует озадачиться еще и такими вопросами:

                      Что будет, если шаред память уйдет в своп?
                      Что будет, если размер базы вырастет и станет больше размера шаред памяти?
                      Запись на диск кэшируется. Т.е., запись на диск — это тоже работа с памятью. За счет чего так выходит, что работа с шаред памятью оказалась быстрее? В принципе, этот вопрос должен был быть рассмотрен в такой статье т.к. подходит по тематике.

                      плюс озвученные выше.

                      Если база помещается в памяти, то возможно, стоит посмотреть не в сторону mysql, а в сторону каких-нибудь других баз. В частности, существуют неплохие решения, которые хранят и оперируют с данными on-memory, а для записи на диск делают fork и в отдельном процессе пишут — желательно попеременно в два файла. Тут надо отметить, что в современных архитектурах/нормальных_ОС fork не копирует данные, а использует copy-on-write идиому, поэтому реально копироваться будет мало данных. Я не большой спец по базам, и конкретную бд посоветовать не могу, но по идее такие решения есть — надо поискать.
                        –2
                        > Что будет, если шаред память уйдет в своп?
                        Просто начнет медленнее работать. Поэтому ставим мониторилку свободного места.

                        > Что будет, если размер базы вырастет и станет больше размера шаред памяти?
                        Здесь либо данные пойдут в своп, либо вылезет ошибка из-за нехватки места. Поэтому ставим мониторилку свободного места.

                        > Запись на диск кэшируется. Т.е., запись на диск — это тоже работа с памятью. За счет чего так выходит, что работа с шаред памятью оказалась быстрее?
                        Так то все действия в операционной системе — это работа с памятью. Про скорость — не понял фишки вопроса.

                        > Если база помещается в памяти, то возможно, стоит посмотреть не в сторону mysql, а в сторону каких-нибудь других баз.
                        Я могу посоветовать — mongoDB :) Отличная база. Реплицируется до бесконечности, не теряя скорости (работал на проекте, где был 1 млрд. запросов к базе в сутки). Но она не поддерживает транзакции.
                          0
                          она не поддерживает транзакции.

                          поддерживает вообще-то, да при этом приходится танцевать с бубном, но даже финансовые транзакции вполне на монге делают. Это намного лучше чем риск потерять транзакцию за последнею секунду (например, миллиардный перевод).
                            –2
                            Про это я читал. Это псевдо-транзакции без уровня изоляций. Такое можно реализовать и на обычном массиве в любом языке. Поэтому такое решение для финансовых компаний будет странным. Зачем рисковать деньгами, используя костыли, когда можно взять нормальную реплику на SSD. trx_auto_commit ставится = 1 и все, никаких потерь последней секунды.
                              0
                              Это псевдо-транзакции без уровня изоляций. Такое можно реализовать и на обычном массиве в любом языке.

                              Вообще-то, там обычные транзакции с блокированием записей и максимальным уровнем изоляции, монга гарантирует Atomicity, Consistency и блокирование параллельных транзакций.

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

                              Странно это слышать, после того как вы предложили свой костыль и велосипед для финансовых транзакций. Вообще-то, нормальный бизнес не будет экономить на спичках, а просто возьмет несколько серверов с SSD дисками и настроит разделение и репликацию данных.

                              можно взять нормальную реплику на SSD. trx_auto_commit ставится = 1 и все

                              А не проще просто писать сразу на SSD с кешированием данных средствами mysql?
                                0
                                > Вообще-то, там обычные транзакции с блокированием записей и максимальным уровнем изоляции, монга гарантирует Atomicity, Consistency и блокирование параллельных транзакций.
                                Не уверен, что это правда. Но это приятно, что такое потенциально у них появилось. В любом случае изучу тему.

                                Кстати, а что у них с dead-lock?

                                > Странно это слышать, после того как вы предложили свой костыль и велосипед для финансовых транзакций.
                                Я костыль для финансовых компаний не предлагал и не никогда не буду ибо это опасно. Это вы тему подняли.
                                Ставить SSD и нормальные транзакции — абсолютно логично, что мое решение тут вообще никак не нужно будет. Смысл ставить SSD и работать с шарой?
                            +1
                            Про фишку вопроса.

                            Традиционный способ: insert работает с памятью, память в другом потоке пишется на диск.
                            Ваш способ: insert работает с памятью, но пишет бинлог на другой диск.

                            И там и тут insert работает с памятью. В первом случае это кэш, во втором это шаред. Все операции с диском в обеих случаях выполняются в другом потоке/процессе и не должны мешать друг другу. Т.е., я просто не вижу откуда может взяться такая производительность. Может быть небольшая разница, за счет разной имеплементации этих механизмов памяти. Если разница получилась значительной — это скорей всего значит, что там где вышло медленно — там скорей всего вы просто что-то недонастроили.
                              –1
                              > Традиционный способ: insert работает с памятью, память в другом потоке пишется на диск.
                              Кэш должен куда-то сброситься. В моем случае вместо «пишется на диск» пишется в шару.
                                +1
                                кэш сбрасывается в другом процессе. Следовательно, сброс кэша в такой мере на быстродействие влиять не должен.
                                  –1
                                  Он может сбрасываться хоть в N+100 процессе. Это не имеет значения. Важно через какое время он сбрасывается и куда.

                        Only users with full accounts can post comments. Log in, please.