Резюме: Все упоминали, что оптимизация производительности Mysql фокусируется на оптимизации sql и индексов для повышения производительности запросов.Большинство продуктов или веб-сайтов сталкиваются с более серьезными проблемами чтения данных с высокой степенью параллелизма. Однако как его оптимизировать в сценарии записи большого объема данных?
Сегодня я в основном познакомлю вас со схемой оптимизации в сценариях с большим количеством операций записи.
Как правило, производительность записи базы данных MYSQL в основном ограничивается конфигурацией самой базы данных, производительностью операционной системы и производительностью дискового ввода-вывода. К основным методам оптимизации относятся следующие:
1. Настройте параметры базы данных
(1) innodb_flush_log_at_trx_commit
Значение по умолчанию — 1, что является параметром настройки фиксации транзакции базы данных.Необязательные значения:
0: Буфер журнала записывается в файл журнала один раз в секунду и сбрасывает файл журнала с дисковыми операциями, но ничего не делает при фиксации транзакции.
1: Когда каждая транзакция фиксируется, буфер журнала записывается в файл журнала, и файл журнала обновляется дисковыми операциями.
2: при каждой фиксации буфер журнала записывается в файл, но файл журнала не очищается для операций с диском. Файл журнала сбрасывается каждую секунду.
Некоторые люди скажут, что если его изменить на значение, отличное от 1, будет ли это небезопасно? Сравнение безопасности выглядит следующим образом:
В руководстве по mysql для обеспечения долговечности и непротиворечивости транзакции рекомендуется устанавливать этот параметр равным 1. Заводская установка по умолчанию — 1, что является самой безопасной настройкой.
Безопаснее всего, когда и innodb_flush_log_at_trx_commit, и sync_binlog равны 1. В случае сбоя службы mysqld или сбоя хоста сервера двоичный журнал может потерять не более одного оператора или одной транзакции.
Но в этом случае это вызовет частые операции ввода-вывода, поэтому этот режим также является самым медленным способом.
- Когда для innodb_flush_log_at_trx_commit установлено значение 0, сбой процесса mysqld приведет к потере всех данных транзакции за предыдущую секунду.
- Когда для innodb_flush_log_at_trx_commit установлено значение 2, все данные транзакций за последнюю секунду могут быть потеряны только при сбое операционной системы или выключении системы.
Для той же таблицы выполняется пакетная вставка в соответствии с системным бизнес-процессом через код C # Сравнение производительности выглядит следующим образом:
- (a. При тех же условиях: innodb_flush_log_at_trx_commit=0 вставка строк данных объемом 50 Вт занимает 25,08 секунды;
- (b. При тех же условиях: innodb_flush_log_at_trx_commit=1 для вставки 50W строк данных требуется 17 минут и 21,91 секунды;
- (c. При тех же условиях: innodb_flush_log_at_trx_commit=2 для вставки строк данных объемом 50 Вт требуется 1 минута и 0,35 секунды.
Вывод: когда установлено значение 0, запись данных является самой быстрой, что может быстро улучшить производительность записи базы данных, но может привести к потере данных за последнюю 1 секунду.
(2) temp_table_size, куча_table_size
Эти два параметра в основном влияют на запись временной таблицы и таблицы памяти механизма базы данных в памяти.Если параметр слишком мал, будет даже сообщение об ошибке таблица заполнена.
Необходимо установить размер пространства, занимаемого данными, больше, чем объем записываемых данных в соответствии с реальной деловой ситуацией.
(3) max_allowed_packet=256M, net_buffer_length=16M, установить autocommit=0
Если вы установите эти три параметра во время резервного копирования и восстановления, вы можете увеличить скорость резервного копирования и восстановления!
(4) innodb_data_file_path=ibdata1:1G;ibdata2:64M:авторасширение
Очевидно, что авторасширение позади табличного пространства заключается в автоматическом расширении табличного пространства, которого недостаточно, чтобы по умолчанию было только 10 М. В сценарии записи больших объемов данных вы можете увеличить этот параметр;
По мере роста табличного пространства выделяйте как можно больше табличного пространства за раз, чтобы избежать частого расширения файла при записи большими пакетами.
(5) innodb_log_file_size, innodb_log_files_in_group, innodb_log_buffer_size
Задайте размер журнала транзакций, количество групп журнала и кэш журнала. Значение по умолчанию очень маленькое, значение по умолчанию для innodb_log_file_size составляет всего несколько десятков M, а значение по умолчанию для innodb_log_files_in_group равно 2.
Однако в innodb данные обычно сначала записываются в кеш, затем в журнал транзакций, а затем в файл данных. Слишком маленькое значение параметра в сценарии записи большого объема данных неизбежно приведет к частому срабатыванию контрольных точек базы данных для записи данных журнала в файл данных на диске. Частая очистка буферов и переключение журналов снизит производительность записи данных большими пакетами.
Конечно, его не следует ставить слишком большим. Когда база данных выходит из строя из-за большой конференции, при перезапуске базы данных она считывает грязные данные в журнале, которые не были записаны в файл данных, выполняет повтор и восстанавливает базу данных.Если она слишком велика, время восстановления будет больше. Когда время восстановления намного превышает ожидаемое и приемлемое для пользователя время восстановления, пользователь неизбежно будет жаловаться.
Для настроек в этом отношении вы можете обратиться к настройкам базы данных HUAWEI CLOUD по умолчанию.В среде HUAWEI CLOUD 2 ядра и 4G кажется, что конфигурация буфера по умолчанию: 16M, log_file_size: 1G ---- почти 25% от общего объема памяти в соответствии с официальной рекомендацией MySQL. , а группа журналов files_in_group установлена в 4 группы.
Аппаратная конфигурация 2 ядра и 4G настолько мала, что за счет рациональности настроек параметров смогла выдержать тысячи запросов на чтение и запись в секунду и 80 000 раз в минуту.
Если объем записываемых данных намного больше, чем читаемых, или удобно изменять параметры по желанию, вы можете импортировать большие пакеты данных и внести коррективы, чтобы настроить log_file_size на большее значение, которое может достигать 25%~ 100% innodb_buffer_pool_size .
(6) innodb_buffer_pool_size устанавливает доступный размер кэша MySQL Innodb. Теоретически максимально можно установить 80% от общей памяти сервера.
Установка большего значения, конечно, лучше для производительности записи, чем установка меньшего значения. Например, указанный выше параметр innodb_log_file_size устанавливается со ссылкой на размер innodb_buffer_pool_size.
(7) innodb_thread_concurrency=16
Как следует из названия, контролировать количество параллельных потоков, Теоретически, чем больше потоков, тем быстрее будет запись. Конечно, его нельзя ставить слишком большим, официальная рекомендация — примерно в два раза больше ядер процессора.
(8) write_buffer_size
Управляет размером кеша для одной записи в одном сеансе. Значение по умолчанию составляет около 4 КБ, что обычно не нужно настраивать. Однако в сценарии частой и большой пакетной записи вы можете попробовать настроить его на 2M, и вы обнаружите, что скорость записи в определенной степени улучшится.
(9) innodb_buffer_pool_instance
Значение по умолчанию равно 1, что в основном устанавливает количество пулов буферов памяти, проще говоря, контролирует количество одновременных операций чтения и записи innodb_buffer_pool.
В сценарии большой пакетной записи вы также можете увеличить этот параметр, что также принесет значительный прирост производительности.
(10) бин_лог
Двоичный журнал, который обычно записывает все добавления, удаления и изменения в базе данных. Однако при импорте большого объема данных, например при восстановлении базы данных, вы можете временно закрыть bin_log, отключить запись в двоичный журнал и разрешить запись данных только в файл данных, быстро завершить восстановление данных и затем откройте его снова.
2. Сокращение операций ввода-вывода на диск и повышение эффективности чтения и записи на диск.
В том числе следующие методы:
(1): Оптимизация архитектуры системы баз данных
а: репликация ведущий-ведомый;
Например, развертывание режима двойного главного-ведомого предназначено для резервного копирования друг друга и обеспечения безопасности данных.Различные бизнес-системы подключены к разным серверам баз данных в сочетании с функцией автоматического переключения ngnix или keepalive для достижения балансировки нагрузки и автоматического переключения в случае неудачи.
Благодаря этой оптимизации архитектуры одновременные операции чтения и записи децентрализованной бизнес-системы могут быть перенесены с одного сервера на несколько серверов, что также может повысить скорость записи одной базы данных.
b: сделать разделение чтения и записи
Подобно проблеме, рассматриваемой в 1, дисковые операции ввода-вывода одного сервера могут быть уменьшены, а операция резервного копирования на сервере может быть перенесена на резервный сервер, чтобы уменьшить нагрузку ввода-вывода основного сервера, тем самым улучшив производительность записи.
(2): Аппаратная оптимизация
a: В случае ограниченных ресурсов при установке и развертывании в операционной системе должно быть несколько дисков.Рассредоточьте приложения, файлы баз данных, файлы журналов и т. д. по разным дисковым хранилищам, уменьшите количество операций ввода-вывода каждого диска и улучшите отдельные Производительность записи на диск.
б: с использованием твердотельного накопителя SSD
Если ресурсов достаточно, можно использовать хранилище SSD.SSD обладает характеристиками высокоскоростной записи, а также может значительно улучшить все операции дискового ввода-вывода.
Конечно, есть и другие аппаратные или программные методы оптимизации, которые здесь не перечислены.
Эта статья опубликована в сообществе HUAWEI CLOUD «Оптимизация производительности массовых операций записи MYSQL», оригинальный автор: Fuchen.
Нажмите «Подписаться», чтобы впервые узнать о новых технологиях HUAWEI CLOUD~