Как ускорить работу c mysql при большом количестве операций записи?

Рейтинг: 5Ответов: 2Опубликовано: 28.02.2023

Делаю сервис на php, в основном ничего сложного, загружаем в базу (mysql) записи, делаю 1 curl запрос на каждую запись и записываю результат в бд.

И все работало отлично, до тех пор покуда не потребовалось работать с базой объемом в 20 млн записей.

Сейчас все записи пишет в одну таблицу. При запуске скрипта я формирую массив из всех данных из таблицы (20 млн. строк) и после этого делю этот массив на 20 частей. Затем я запускаю 20 одинаковых php скриптов

shell_exec("php script.php $array_part")

и каждый скрипт делает запросы с помощью curl_multi_init() на нужный мне API (он платный, поэтому ограничений по кол-ву запросов и частоте нету), после полученный результат записываю в базу.

Так как curl_multi_init() делает сразу несколько запросов (поставили 200), быстро приходит много ответов (~200*20=4000), и на каждый ответ нужно сделать запись в базу и несколько обновлений уже существующих записей. И вот здесь базе данных очень тяжко работать, несмотря на ее оптимизацию, индексы и тд.

Сервер имеет 14гб ОЗУ, 8 процессоров, но все равно видны заметные фризы в момент работы php скриптов с большим объемом данных одновременно.

Как правильно работать с такими большими таблицами (когда нужно 1 раз прочитать и на каждую полученную запись делать ~2 update и 1 insert)?

Может быть создать несколько таблиц которые делать записи четные/нечетные? или как-то еще разделять? Но на запись же от этого серверу по идее легче не станет, так как все пишет в одну БД...

Разбивать базу данные на несколько серверов? по какому принципу делить данные...?

Буду очень благодарен за ответы!

P.S. очень важна скорость обработки данных, поэтому уменьшить число одновременно запускаемых php скриптов нет возможности.

php mysql
Источник: Stack Overflow на русском

Ответы

▲ 1

Оптимизация записи

Самое первое, на что следует обращать внимание при проблемах множественной записи - это значение переменной innodb_flush_log_at_trx_commit, поскольку она может замедлять запись в десятки раз.

И далее смотреть по обостоятельствам. В вашем случае, как я понимаю, безопасно поставить innodb_flush_log_at_trx_commit = 0. Это сразу повысит скорость в десятки раз.

Если хочется использовать другие значения, то можно собирать запросы в блоки, и записывать большими партиями - либо через INSERT с множеством VALUES, либо заключая пачки отдельных запросов в одну транзакцию.

Оптимизация работы с БД

Кроме того, просто закидать БД железом недостаточно. Им надо с умом воспользоваться. В частности настраивать буфера, и в первую очередь - innodb_buffer_pool_size. Если в нем стоит значение по умолчанию, то разницы будет ни с 4, ни с 14, ни со 140 гигами памяти. Под этот буфер надо отводить 80-90% памяти на сервере.

Плюс все эти общие слова - "индексы, шминдексы, оптимизация" - это разговор ни о чем. Надо конкретно смотреть, какие индексы, какая оптимизация, в каком месте тормозит. И приходить уже с конкретными вопросами.

Партиционирование

При больших объемах данных (начиная от миллионов строк) следует подумать о партиционировании. Это разбиение таблиц на части средствами самой БД, прозрачно для приложения.

Платная поддержка

В России это не принято, но на самом деле это очень умный вариант. Вместо того чтобы самим биться как рыба об лед, и вместо того чтобы спрашивать совета у непонятных людей в интернете, можно купить консультационные услуги одной из многочисленных компаний, которые развивают форки mysql - MariaDB, Percona, Mysql AB. Это, собственно, их заработок, благодаря которому данные СУБД и развиваются. То есть вы поможете одновременно и себе, и поддержите разработку открытого программного обеспечения.

▲ -2

Вам нужно перевести таблицы в формат хранения данных InnoDB InnoDB более надежна при больших объемах данных. InnoDB в теории немного быстрее.

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