У нас довольно большая база данных MySQL (всего около 35 ГБ) и около 900 qps. Производительность пока не является большой проблемой, но проект постоянно растет, и я бы лучше подумал об оптимизации заранее.
Недавно я узнал о кластерных решениях InnoDB / NDB, где вы можете добавить как минимум 3 сервера для обеспечения отказоустойчивости базы данных, но мне было интересно, улучшит ли это общую производительность, поскольку в обработке трафика задействовано несколько серверов?
Проект в основном представляет собой публичную рекламную платформу, и в нем всего несколько центральных таблиц, которые получают наибольший трафик - пользователи, реклама и некоторые другие. Я не могу указать на одно слабое место, есть много разных вещей, которые можно оптимизировать с помощью инфраструктуры (например, есть внутренний обмен мгновенными сообщениями между пользователями - я планирую протестировать, как он будет работать с MongoDB, я думаю, что сообщения хорошо подходят для этой архитектуры базы данных)
Вот статистика запросов для сервера базы данных:
Есть компромиссы. Множественный записываемый серверам в кластере по-прежнему необходимо отправлять все записи на все другие машины. Читает выгода от кластеризации и / или подчиненных устройств. Пишет только небольшую выгоду от кластеризации. (Шардинг - реальное решение для масштабирования записи.)
Даже добавление двух или более ведомых устройств к одному ведущему помогает писать некоторые. Это потому, что читает теперь распределены по крайней мере между двумя подчиненными серверами, тем самым меньше конкурируя с операциями записи.
При быстром росте:
ALTER
большой стол, может потребоваться достаточно места для полной копии. И вы не хотите, чтобы не хватало места на диске.ALTERs
займет все больше и больше времени.Если вы в настоящее время используете InnoDB, вы обнаружите, что есть несколько структурных изменений, которые нужно перенести в NDB. Модель транзакции совершенно другая («конечная согласованность»). В зависимости от типа приложения это может быть проблема или даже "кирпичная стена".
Только в MySQL / MariaDB есть «InnoDB Cluster» (MySQL 8.0) и Galera Cluster (PXC, MariaDB). Возможно, они одинаково «хороши».
Для всего вышеперечисленного требуется как минимум 3 машины, желательно распределенные как минимум в 3 центрах обработки данных. (Да, центры обработки данных могут выйти из строя.)
Возможно, я смогу сказать больше, если вы предоставите более подробную информацию о своем приложении. Четный SHOW CREATE TABLE
для вашей самой большой таблицы может дать много информации.
Если хотите, следуйте инструкциям Вот ; Возможно, я смогу помочь вам еще немного масштабироваться, прежде чем менять топологию или оборудование.
Больше
Поскольку почти все запросы SELECTs
любая топология репликации или кластеризации может помочь вам в масштабировании. Любое количество ведомых устройств может обеспечить любое масштабирование. Эти подчиненные устройства могут зависать от одного мастера (или кластера InnoDB) или трех узлов кластера Galera. NDB работает по-другому, но его также можно произвольно масштабировать для чтения.
Так что простой ответ на ваш вопрос - «да». Что касается решения, пока не известно, какое решение подойдет вам лучше. Возможно, лучшее, что можно сказать, - это «Выберите решение и работайте с ним».
Обзор ПЕРЕМЕННЫХ и ГЛОБАЛЬНОГО СТАТУСА:
Наблюдения:
* Version: 10.3.15-MariaDB
* 16 GB of RAM
* Uptime = 64d 10:48:05
* You are not running on Windows.
* Running 64-bit version
* You appear to be running entirely (or mostly) InnoDB.
Более важные вопросы:
Является ли это автономной базой данных InnoDB, не кластеризованной, не подчиненной и т. Д.?
Узнайте, есть ли у вас HDD или SSD; затем просмотрите несколько элементов в разделе "Подробности" ниже.
Вы используете «Кэш запросов», но он не очень эффективен, возможно, замедляя работу системы в целом. Рекомендую либо выключить, либо использовать DEMAND
вместе с тщательным подбором SELECTs
иметь SQL_CACHE
в.
Нет ОБЯЗАТЕЛЬСТВ? Вы используете autocommit = ON и когда-нибудь использовали BEGIN? Опишите типичные запросы DML; у нас могут быть предложения по другому использованию транзакций для уменьшения ввода-вывода.
Рассмотрите возможность перехода с REPLACE
к INSERT ... ON DUPLICATE KEY UPDATE
.
Ваш вопрос был о том, какую систему использовать. Обратите внимание, что пропускная способность сети может быть для вас большой проблемой (Bytes_sent = 7666357 /sec
); следовательно, обращение к количеству и многословности запросов может быть полезно (и не зависит от системы).
Почему так много SHOW STATUS
звонки?
Множество полных сканирований таблицы для DELETE. Давайте обсудим их и возможные способы их улучшения, особенно если они находятся на больших столах. ( http://mysql.rjweb.org/doc.php/deletebig )
Детали и другие наблюдения:
( Table_open_cache_misses ) = 14,420,381 / 5568485 = 2.6 /sec
- Может потребоваться увеличить table_open_cache (сейчас 2048)
( innodb_lru_scan_depth * innodb_page_cleaners ) = 1,024 * 4 = 4,096
- Объем работы очистителей страниц каждую секунду. - «InnoDB: page_cleaner: предполагаемый цикл 1000 мс занял ...» можно исправить, уменьшив lru_scan_depth: рассмотрим 1000 / innodb_page_cleaners (теперь 4). Также проверьте наличие подкачки.
( innodb_page_cleaners / innodb_buffer_pool_instances ) = 4 / 6 = 0.667
- innodb_page_cleaners - Рекомендую установить innodb_page_cleaners (теперь 4) на innodb_buffer_pool_instances (теперь 6)
( innodb_lru_scan_depth ) = 1,024
- «InnoDB: page_cleaner: намеченный цикл 1000 мс занял ...» можно исправить, уменьшив lru_scan_depth
( innodb_doublewrite ) = innodb_doublewrite = OFF
- Дополнительный ввод-вывод, но повышенная безопасность при столкновении. - ВЫКЛ. Подходит для FusionIO, Galera, Slaves, ZFS.
( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 182,569,362,432 / (5568485 / 3600) / 2 / 2048M = 0.0275
- Коэффициент - (см. Минуты)
( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 5,568,485 / 60 * 2048M / 182569362432 = 1,091
- Минуты между ротациями журнала InnoDB. Начиная с 5.6.8, это можно изменять динамически; не забудьте также изменить my.cnf. - (Рекомендация 60 минут между ротациями несколько произвольна.) Отрегулируйте innodb_log_file_size (теперь 2147483648). (Нельзя изменить в AWS.)
( innodb_flush_method ) = innodb_flush_method = fsync
- Как InnoDB должен запрашивать у ОС запись блоков. Предложите O_DIRECT или O_ALL_DIRECT (Percona), чтобы избежать двойной буферизации. (По крайней мере, для Unix.) См. Предостережение по поводу O_ALL_DIRECT у chrischandler.
( Innodb_row_lock_waits ) = 917,931 / 5568485 = 0.16 /sec
- Как часто происходит задержка получения блокировки строки. - Может быть вызвано сложными запросами, которые можно оптимизировать.
( innodb_flush_neighbors ) = 1
- Небольшая оптимизация при записи блоков на диск. - Используйте 0 для SSD-накопителей; 1 для HDD.
( innodb_io_capacity ) = 200
- Число операций ввода-вывода на диске в секунду. 100 для медленных дисков; 200 для прядильных приводов; 1000-2000 для SSD; умножить на коэффициент RAID.
( sync_binlog ) = 0
- Используйте 1 для дополнительной безопасности, при некоторых затратах на ввод-вывод = 1 может привести к большому количеству "завершения запроса"; = 0 может привести к "бинлогу в невозможной позиции" и потере транзакций в случае сбоя, но это быстрее.
( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF
- Регистрировать ли все взаимоблокировки. - Если вас беспокоят тупиковые ситуации, включите это. Внимание: если у вас много тупиковых ситуаций, это может привести к большой записи на диск.
( character_set_server ) = character_set_server = latin1
- Проблемы с кодировкой могут быть решены установкой character_set_server (теперь latin1) на utf8mb4. Это будущий дефолт.
( local_infile ) = local_infile = ON
- local_infile (теперь ON) = ON является потенциальной проблемой безопасности
( query_cache_size ) = 128M
- Размер QC - Слишком маленький = бесполезен. Слишком большой = слишком много накладных расходов. Рекомендую 0 или не более 50 млн.
( Qcache_hits / Qcache_inserts ) = 1,259,699,944 / 2684144053 = 0.469
- Соотношение попаданий и вставок - высокое - это хорошо - Рассмотрите возможность отключения кеша запросов.
( Qcache_hits / (Qcache_hits + Com_select) ) = 1,259,699,944 / (1259699944 + 3986160638) = 24.0%
- Коэффициент попадания - ВЫБОРЫ, которые использовали QC - Рассмотрите возможность отключения кеша запросов.
( Qcache_inserts - Qcache_queries_in_cache ) = (2684144053 - 46843) / 5568485 = 482 /sec
- Инвалидаций / сек.
( (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size ) = (128M - 59914960) / 46843 / 16384 = 0.0968
- query_alloc_block_size против формулы - Отрегулируйте query_alloc_block_size (теперь 16384)
( Select_scan ) = 6,048,081 / 5568485 = 1.1 /sec
- полное сканирование таблиц - добавление индексов / оптимизация запросов (если они не являются крошечными таблицами)
( Com_stmt_prepare - Com_stmt_close ) = 4,138,804,898 - 4129522738 = 9.28e+6
- Сколько подготовленных заявлений не закрыто. - ЗАКРЫТЬ подготовленные отчеты
( Com_replace ) = 28,182,079 / 5568485 = 5.1 /sec
- Рассмотрите возможность перехода на INSERT ... ON DUPLICATE KEY UPDATE.
( binlog_format ) = binlog_format = MIXED
- ЗАЯВЛЕНИЕ / СТРОКА / СМЕШАННЫЕ. - ROW предпочтительнее 5,7 (10,3)
( slow_query_log ) = slow_query_log = OFF
- Следует ли регистрировать медленные запросы. (5.1.12)
( long_query_time ) = 10
- Отсечка (секунды) для определения «медленного» запроса. - Предложить 2
( max_connect_errors ) = 999,999,999 = 1.0e+9
- Небольшая защита от хакеров. - Пожалуй, не больше 200.
( Connections ) = 206,910,348 / 5568485 = 37 /sec
- Подключения - Использовать пул?
Аномально маленький:
Com_show_tables = 0
Created_tmp_files = 0.12 /HR
Innodb_dblwr_pages_written = 0
Qcache_total_blocks * query_cache_min_res_unit / Qcache_queries_in_cache = 5,166
eq_range_index_dive_limit = 0
innodb_ft_min_token_size = 2
innodb_spin_wait_delay = 4
lock_wait_timeout = 86,400
query_cache_min_res_unit = 2,048
Аномально большой:
Access_denied_errors = 93,135
Acl_table_grants = 10
Bytes_sent = 7666357 /sec
Com_create_trigger = 0.0026 /HR
Com_create_user = 0.0013 /HR
Com_replace_select = 0.086 /HR
Com_reset = 1 /HR
Com_show_open_tables = 0.02 /HR
Com_show_status = 0.18 /sec
Com_stmt_close = 741 /sec
Com_stmt_execute = 743 /sec
Com_stmt_prepare = 743 /sec
Delete_scan = 43 /HR
Executed_triggers = 1.5 /sec
Feature_fulltext = 0.62 /sec
Handler_read_last = 0.83 /sec
Handler_read_next = 357845 /sec
Handler_read_prev = 27369 /sec
Innodb_buffer_pool_pages_misc * 16384 / innodb_buffer_pool_size = 16.2%
Innodb_row_lock_time_max = 61,943
Prepared_stmt_count = 3
Qcache_free_blocks = 24,238
Qcache_hits = 226 /sec
Qcache_inserts = 482 /sec
Qcache_total_blocks = 118,160
Select_range = 53 /sec
Sort_range = 47 /sec
Tc_log_page_size = 4,096
innodb_open_files = 10,000
max_relay_log_size = 1024MB
performance_schema_max_stage_classes = 160
Аномальные строки:
aria_recover_options = BACKUP,QUICK
ft_min_word_len = 2
innodb_fast_shutdown = 1
innodb_use_atomic_writes = ON
log_slow_admin_statements = ON
myisam_stats_method = NULLS_UNEQUAL
old_alter_table = DEFAULT
plugin_maturity = gamma