Сперва проверим скорость случайной записи на жесткий диск:
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.
Комментарии (39)
maxru
15.06.2015 12:54+6Мне кажется, Blackhole будет ещё быстрее.
А бинарные логи можно складировать в /dev/null
Dmitry_Belyaev Автор
15.06.2015 13:03-4Забавно.
«бинарные логи можно складировать в /dev/null » = отключить бинарные логи. Так будет еще быстрее :)
vedenin1980
15.06.2015 14:47+3Не понял, а разве не тоже самое делает MEMORY storage engine MySql? Вы не сравнивали производительность MEMORY storage engine и вашего решения?
vedenin1980
15.06.2015 15:06+1Кстати, при использовании MEMORY storage engine есть возможность установить master-slave репликацию на нормальные таблицы, где все будет делаться уже на уровне самой БД и там уже можно будет играться со скоростью/надежностью.
StirolXXX
15.06.2015 15:07memory внутри это myisam, что накладывает свои ограничения и проблемы в т.ч бесполезность binlog
vedenin1980
15.06.2015 15:13конечно, но можно ведь репликацировать данные на обычные таблицы. В целом, если автор рассматривал оба варианта хотелось увидеть разницу в производительности и функциональности. Если не рассматривал, то почему?
StirolXXX
15.06.2015 15:20туда куда будет происходить репликация — должно быть хранилище которое тоже может переварить такую скорость.
имхо изначально достаточно было поставить raid1 и два SSD.Dmitry_Belyaev Автор
15.06.2015 15:28Ну конечно можно. Есс-но, что SSD это круто. В данном случае RAID1 не обязателен. Задача — повысить скорость записи, а не надежность.
Dmitry_Belyaev Автор
15.06.2015 15:27Не рассматривал MEMORY, т.к. это не транзакционная таблица, как было написано в пред.коменте.
А схему MEMORY -> реплика в InnoDB это неудачное решение. Первичное хранилище должно поддерживать транзакции.
Кроме того, MEMORY плохо работает с ф-ми агрегации (заметно хуже, чем MyISAM). Возможно это было исправлено. Но в MySQL 5.1 агрегаторы SORT и GROUP BY существенно снижали скорось выборки.Dmitry_Belyaev Автор
15.06.2015 16:18Чтобы не быть голословным проверил на 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 картины не меняют.
altexxx
16.06.2015 00:49Вы какой тип индекса указали, когда MEMORY-таблицу создавали: hash или btree?
barker
15.06.2015 17:21+6Такой громкий заголовок:
Ускоряем MySQL insert/update в 5-10 раз
И далее статья исключительно про то, как переместить файлы с данными БД с харда на ram-диск. Оно вроде как всё правда и нет обмана, но чувствуется какая-то… хм, разочарованность)В операционных системах UNIX существует раздел файловой системы, который физически находится в оперативной памяти, но позволяет работать с ним как с обычным дисковым накопителем
Такое даже в msdos было.Dmitry_Belyaev Автор
15.06.2015 18:16-2Поэтому и оговорка — «немного теории». Можно считать, что это напоминалка.
Dmitry_Belyaev Автор
16.06.2015 11:16-2> И далее статья исключительно про то, как переместить файлы с данными БД с харда на ram-диск. Оно вроде как всё правда и нет обмана, но чувствуется какая-то… хм, разочарованность)
Если вы ожидали, что вам покажут, как преодолеть законы физики, то, конечно, я вас разочаровал.
stavinsky
15.06.2015 17:21+4Очень громкое название для такой статьи.
1. Если оптимально настроить БД она и так будет вся в оперативке. Или почти вся. Тот же innodb_buffer_pool_size не зря доступен для редактирования.
2.Как таковых транзакций здесь у нас нет и на скорость это не влияет.
— в InnoDB каждая операция транзакция, если не указано иное, или я путаю?
Ну а так спасибо, интересное решение. Никогда постараюсь не применять его на практике, но все ж имеет место быть)Dmitry_Belyaev Автор
15.06.2015 18:23-3> Если оптимально настроить БД она и так будет вся в оперативке. Или почти вся. Тот же innodb_buffer_pool_size не зря доступен для редактирования.
Не будет. Можете проверить сами. Даже если innodb_buffer_pool_size поставите больше размера БД. Операции записи будут регулярно работать с диском.
> в InnoDB каждая операция транзакция, если не указано иное, или я путаю
Да, здесь вы правы. В любом случае и там и там флаш транзакций был 1 раз в секунду.
chabapok
16.06.2015 00:38+1Вероятно, если хочется использовать такое решение, то следует озадачиться еще и такими вопросами:
Что будет, если шаред память уйдет в своп?
Что будет, если размер базы вырастет и станет больше размера шаред памяти?
Запись на диск кэшируется. Т.е., запись на диск — это тоже работа с памятью. За счет чего так выходит, что работа с шаред памятью оказалась быстрее? В принципе, этот вопрос должен был быть рассмотрен в такой статье т.к. подходит по тематике.
плюс озвученные выше.
Если база помещается в памяти, то возможно, стоит посмотреть не в сторону mysql, а в сторону каких-нибудь других баз. В частности, существуют неплохие решения, которые хранят и оперируют с данными on-memory, а для записи на диск делают fork и в отдельном процессе пишут — желательно попеременно в два файла. Тут надо отметить, что в современных архитектурах/нормальных_ОС fork не копирует данные, а использует copy-on-write идиому, поэтому реально копироваться будет мало данных. Я не большой спец по базам, и конкретную бд посоветовать не могу, но по идее такие решения есть — надо поискать.Dmitry_Belyaev Автор
16.06.2015 10:18-2> Что будет, если шаред память уйдет в своп?
Просто начнет медленнее работать. Поэтому ставим мониторилку свободного места.
> Что будет, если размер базы вырастет и станет больше размера шаред памяти?
Здесь либо данные пойдут в своп, либо вылезет ошибка из-за нехватки места. Поэтому ставим мониторилку свободного места.
> Запись на диск кэшируется. Т.е., запись на диск — это тоже работа с памятью. За счет чего так выходит, что работа с шаред памятью оказалась быстрее?
Так то все действия в операционной системе — это работа с памятью. Про скорость — не понял фишки вопроса.
> Если база помещается в памяти, то возможно, стоит посмотреть не в сторону mysql, а в сторону каких-нибудь других баз.
Я могу посоветовать — mongoDB :) Отличная база. Реплицируется до бесконечности, не теряя скорости (работал на проекте, где был 1 млрд. запросов к базе в сутки). Но она не поддерживает транзакции.vedenin1980
16.06.2015 10:26она не поддерживает транзакции.
поддерживает вообще-то, да при этом приходится танцевать с бубном, но даже финансовые транзакции вполне на монге делают. Это намного лучше чем риск потерять транзакцию за последнею секунду (например, миллиардный перевод).Dmitry_Belyaev Автор
16.06.2015 10:35-2Про это я читал. Это псевдо-транзакции без уровня изоляций. Такое можно реализовать и на обычном массиве в любом языке. Поэтому такое решение для финансовых компаний будет странным. Зачем рисковать деньгами, используя костыли, когда можно взять нормальную реплику на SSD. trx_auto_commit ставится = 1 и все, никаких потерь последней секунды.
vedenin1980
16.06.2015 11:03Это псевдо-транзакции без уровня изоляций. Такое можно реализовать и на обычном массиве в любом языке.
Вообще-то, там обычные транзакции с блокированием записей и максимальным уровнем изоляции, монга гарантирует Atomicity, Consistency и блокирование параллельных транзакций.
Зачем рисковать деньгами, используя костыли, когда...
Странно это слышать, после того как вы предложили свой костыль и велосипед для финансовых транзакций. Вообще-то, нормальный бизнес не будет экономить на спичках, а просто возьмет несколько серверов с SSD дисками и настроит разделение и репликацию данных.
можно взять нормальную реплику на SSD. trx_auto_commit ставится = 1 и все
А не проще просто писать сразу на SSD с кешированием данных средствами mysql?Dmitry_Belyaev Автор
16.06.2015 11:11> Вообще-то, там обычные транзакции с блокированием записей и максимальным уровнем изоляции, монга гарантирует Atomicity, Consistency и блокирование параллельных транзакций.
Не уверен, что это правда. Но это приятно, что такое потенциально у них появилось. В любом случае изучу тему.
Кстати, а что у них с dead-lock?
> Странно это слышать, после того как вы предложили свой костыль и велосипед для финансовых транзакций.
Я костыль для финансовых компаний не предлагал и не никогда не буду ибо это опасно. Это вы тему подняли.
Ставить SSD и нормальные транзакции — абсолютно логично, что мое решение тут вообще никак не нужно будет. Смысл ставить SSD и работать с шарой?
chabapok
16.06.2015 11:44+1Про фишку вопроса.
Традиционный способ: insert работает с памятью, память в другом потоке пишется на диск.
Ваш способ: insert работает с памятью, но пишет бинлог на другой диск.
И там и тут insert работает с памятью. В первом случае это кэш, во втором это шаред. Все операции с диском в обеих случаях выполняются в другом потоке/процессе и не должны мешать друг другу. Т.е., я просто не вижу откуда может взяться такая производительность. Может быть небольшая разница, за счет разной имеплементации этих механизмов памяти. Если разница получилась значительной — это скорей всего значит, что там где вышло медленно — там скорей всего вы просто что-то недонастроили.Dmitry_Belyaev Автор
16.06.2015 12:51-1> Традиционный способ: insert работает с памятью, память в другом потоке пишется на диск.
Кэш должен куда-то сброситься. В моем случае вместо «пишется на диск» пишется в шару.chabapok
16.06.2015 13:05+1кэш сбрасывается в другом процессе. Следовательно, сброс кэша в такой мере на быстродействие влиять не должен.
Dmitry_Belyaev Автор
16.06.2015 13:06-1Он может сбрасываться хоть в N+100 процессе. Это не имеет значения. Важно через какое время он сбрасывается и куда.
evilbot
При выключении сервера по питанию база отправится в страну вечной охоты?
Dmitry_Belyaev Автор
Читаем внимательно статью. Для режима 0 и 6 добавляем скрипт, который… читаем выше.
Fesor
При отключении по питанию ваши rc скрипты отрабатывать не будут, все просто умрет.
Dmitry_Belyaev Автор
Для этого делается бэкап и пишется бинарь. Если питание внезапно исчезает (что случается в нормальных дата-центрах крайне редко). Поднимаем последний бэкап и накатываем бинарный лог. Самое большее, что в таком случае мы теряем это транзакцию за последнюю секунду, т.к. у нас стоит innodb_flush_log_at_trx_commit = 2.
maxru
За какой промежуток времени вы собираетесь хранить бинарные логи?
Dmitry_Belyaev Автор
Как минимум не меньше времени между бэкапами.
evilbot
Читал внимательно. Я про неконтролируемую потерю питания и отсутствие резервирования.
Dmitry_Belyaev Автор
Написал только что.
«отсутствие резервирования» — об этом написано.
evilbot
Тогда по хорошему можно в эту базу писать, а в более медленную реплицировать.
Dmitry_Belyaev Автор
Согласен. Если есть две машины — то это очень хорошее решение.