Насколько велика должна быть mysql innodb_buffer_pool_size?

У меня есть загруженная база данных с единственными таблицами InnoDB размером около 5 ГБ. База данных работает на сервере Debian с использованием SSD-дисков, и я установил max connections = 800, которые иногда насыщают и заставляют сервер останавливаться. Средний запрос в секунду составляет около 2.5K. Поэтому мне нужно оптимизировать использование памяти, чтобы освободить место для максимально возможных подключений.

Я видел предложения, что innodb_buffer_pool_size должен быть до% 80 от общей памяти. С другой стороны, я получаю это предупреждение от скрипта настройки-праймера:

Макс. объем памяти: 91,97 G
Конфигурированные максимальные поточные буферы: 72,02 G
Настроенные максимальные глобальные буферы: 19,86 G
Настроенный максимальный предел памяти: 91,88 G
Физическая память: 94,58 G

Вот мои текущие переменные innodb:

| innodb_adaptive_flushing | ON |
| innodb_adaptive_hash_index | ON |
| innodb_additional_mem_pool_size | 20971520 |
| innodb_autoextend_increment | 8 |
| innodb_autoinc_lock_mode | 1 |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_size | 20971520000 |
| innodb_change_buffering | все |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1: 10M: автоэкстенд |
| innodb_data_home_dir | |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_format | Антилопа |
| innodb_file_format_check | ON |
| innodb_file_format_max | Антилопа |
| innodb_file_per_table | ON |
| innodb_flush_log_at_trx_commit | 2 |
| innodb_flush_method | O_DIRECT |
| innodb_force_load_corrupted | ВЫКЛ |
| innodb_force_recovery | 0 |
| innodb_io_capacity | 200 |
|innodb_large_prefix | ВЫКЛ |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | ВЫКЛ |
| innodb_log_buffer_size | 4194304 |
| innodb_log_file_size | 524288000 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./|
| innodb_max_dirty_pages_pct | 75 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_old_blocks_pct | 37 |
| innodb_old_blocks_time | 0 |
| innodb_open_files | 300 |
| innodb_purge_batch_size | 20 |
| innodb_purge_threads | 0 |
| innodb_random_read_ahead | ВЫКЛ |
| innodb_read_ahead_threshold | 56 |
| innodb_read_io_threads | 4 |
| innodb_replication_delay | 0 |
| innodb_rollback_on_timeout | ВЫКЛ |
| innodb_rollback_segments | 128 |
| innodb_spin_wait_delay | 6 |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | ON |
| innodb_stats_sample_pages | 8 |
| innodb_strict_mode | ВЫКЛ |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 30 |
| innodb_table_locks | НА|
| innodb_thread_concurrency | 4 |
| innodb_thread_sleep_delay | 10000 |
| innodb_use_native_aio | ON |
| innodb_use_sys_malloc | ON |
| innodb_version | 1.1.8 |
| innodb_write_io_threads | 4 |

Замечание, которое может быть актуальным: я вижу, что когда я пытаюсь вставить большую базу данных (скажем, более 10 КБ) из Drupal (которая находится на отдельном веб-сервере) в базу данных, она длится вечно, и страница не возвращается правильно.

В связи с этим мне интересно, какой должен быть мой innodb_buffer_pool_size для оптимальной производительности. Я ценю ваши предложения по оптимальному настройке этого и других параметров для этого сценария.

141 голос | спросил alfish 21 +04002012-10-21T19:03:45+04:00312012bEurope/MoscowSun, 21 Oct 2012 19:03:45 +0400 2012, 19:03:45

4 ответа


211

innodb_buffer_pool_size огромен. У вас установлено значение 20971520000. Это 19.5135 ГБ. Если у вас всего 5 ГБ данных и индексов InnoDB, то у вас должно быть только около 8 ГБ. Даже это может быть слишком высоким.

Вот что вы должны делать. Сначала запустите этот запрос

SELECT CEILING (Total_InnoDB_Bytes * 1.6 /POWER (1024,3)) RIBPS FROM
(SELECT SUM (data_length + index_length) Всего_InnoDB_Bytes
FROM information_schema.tables WHERE engine = 'InnoDB') A;

Это даст вам RIBPS, рекомендуемый размер пула буферов InnoDB на основе всех данных и индексов InnoDB с дополнительными 60%.

Пример

MySQL > ВЫБРАТЬ ПОТОЛОК (Total_InnoDB_Bytes * 1.6 /POWER (1024,3)) РЕЗИНЫ ИЗ
    - > (SELECT SUM (data_length + index_length) Всего_InnoDB_Bytes
    - > FROM information_schema.tables WHERE engine = 'InnoDB') A;
+ ------- +
| RIBPS |
+ ------- +
| 8 |
+ ------- +
1 строка в наборе (4.31 сек)

MySQL >

С этим выходом вы должны установить следующее в /etc/my.cnf

[туздЫ]
innodb_buffer_pool_size = 8G

Далее, служба mysql restart

После перезапуска запустите mysql в течение недели или двух. Затем запустите этот запрос:

SELECT (PagesData * PageSize) /POWER (1024,3) DataGB FROM
(SELECT variable_value PagesData
FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_data') A,
(SELECT variable_value PageSize
FROM information_schema.global_status
WHERE variable_name = 'Innodb_page_size') B;

Это даст вам, сколько фактических ГБ памяти используется InnoDB Данные в буфере пула InnoDB в данный момент.

Я уже писал об этом раньше: Что нужно установить innodb_buffer_pool и почему ...?

Вы можете просто запустить этот DataGB запрос прямо сейчас, а не реконфигурировать, перезапустить и ждать неделю.

Это значение DataGB более похоже на то, насколько велик пул InnoDB Buffer Pool + (процент указывается в innodb_change_buffer_max_size). Я уверен, что это будет намного меньше, чем 20000M, которые вы зарезервировали прямо сейчас. Экономия в ОЗУ может быть использована для настройки других вещей, таких как

CAVEAT # 1

Это очень важно отметить: время от времени InnoDB может потребовать дополнительные 10% от значения для innodb_buffer_pool_size . Вот что говорит Документация по MySQL .

  

Чем больше вы устанавливаете это значение, тем меньше дискового ввода-вывода требуется для доступа   данные в таблицах. На выделенном сервере базы данных вы можете установить это вверх   до 80% от объема физической памяти машины. Будьте готовы к сокращению назад   это значение, если возникают другие проблемы:

     

Конкурс на физическую память может вызвать пейджинг в операционной   система.

     

InnoDB резервирует дополнительную память для буферов и структур управления,   так что общее выделенное пространство примерно на 10% больше, чем   указанный размер.

     

Адресное пространство должно быть смежным, что может быть проблемой для Windows   системы с DLL, которые загружаются с определенными адресами.

     

Время инициализации пула буферов примерно пропорционально его   размер. На больших установках это время инициализации может быть   значительное. Например, на современном Linux-сервере x86_64,   инициализация пула буферов на 10 ГБ занимает приблизительно 6 секунд.   См. Раздел 8.9.1, «Буферный пул InnoDB» .

CAVEAT # 2

Я вижу следующие значения в my.cnf

| innodb_io_capacity | 200 |
| innodb_read_io_threads | 4 |
|innodb_thread_concurrency | 4 |
| innodb_write_io_threads | 4 |

Эти числа будут препятствовать доступу InnoDB к нескольким ядрам

Установите следующее:

[туздЫ]
innodb_io_capacity = 2000
innodb_read_io_threads = 64
innodb_thread_concurrency = 0
innodb_write_io_threads = 64

Я уже писал об этом раньше в DBA StackExchange

Я просто ответил на такой вопрос в ServerFault, используя более сжатую формулу :

SELECT CONCAT (ПОТОЛОК (RIBPS /POWER (1024, pw)), SUBSTR ('KMGT', pw + 1,1))
Recommended_InnoDB_Buffer_Pool_Size FROM
(
    SELECT RIBPS, FLOOR (LOG (RIBPS) /LOG (1024)) pw
    ИЗ
    (
        SELECT SUM (data_length + index_length) * 1.1 * рост RIBPS
        FROM information_schema.tables AAA,
        (Рост SELECT 1,25) BBB
        WHERE ENGINE = 'InnoDB'
    ) AA
) A;
ответил RolandoMySQLDBA 22 +04002012-10-22T04:30:39+04:00312012bEurope/MoscowMon, 22 Oct 2012 04:30:39 +0400 2012, 04:30:39
8

Что-то вроде этого? Использование SHOW VARIABLES и SHOW GLOBAL STATUS:

Выражение: innodb_buffer_pool_size /_ram
Значение: % ОЗУ, используемой для InnoDB buffer_pool
Рекомендуемый диапазон: 60 ~ 80%

Выражение: Innodb_buffer_pool_reads /Innodb_buffer_pool_read_requests
Значение: Запросы на чтение, которые должны были hit disk
Рекомендуемый диапазон: 0-2%
Что делать, если вне диапазона: Увеличьте innodb_buffer_pool_size, если у вас достаточно ОЗУ.

> Выражение: Innodb_pages_read /Innodb_buffer_pool_read_requests
Значение: Чтение запросов, которые должны были попасть на диск
Рекомендуемый диапазон: 0-2%
Что делать, если вне диапазона: Увеличьте innodb_buffer_pool_size, если у вас достаточно ОЗУ.

Выражение: Innodb_pages_written /Innodb_buffer_pool_write_requests
Значение: Написать запросы, которые должны были ударить диск
Рекомендуемый диапазон: 0-15%
Что делать, если вне диапазона: Проверить innodb_buffer_pool_size

Expre ssion: Innodb_buffer_pool_reads /Uptime
Значение: Считывает Рекомендуемый диапазон: 0-100 /sec.
Что делать, если вне диапазона: Увеличить innodb_buffer_pool_size?

Выражение: (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) /Uptime
Значение: InnoDB I /O
Рекомендуемый диапазон: 0-100 /sec.
Что делать, если вне диапазона: Увеличить innodb_buffer_pool_size?

Выражение: Innodb_buffer_pool_pages_flushed /Uptime
Значение: Записывает (flushes)
Рекомендуемый диапазон: 0-100 /sec.
Что делать, если вне диапазона: Увеличить innodb_buffer_pool_size?

Выражение: Innodb_buffer_pool_wait_free /Uptime
Значение: Счетчик, если в buffer_pool нет свободных страниц. То есть все страницы загрязнены.
Рекомендуемый диапазон: 0-1 /sec.
Что делать, если вне диапазона: Сначала убедитесь, что innodb_buffer_pool_size установить разумно; если все еще проблемы, уменьшите innodb_max_dirty_pages_pct

ответил Rick James 7 FebruaryEurope/MoscowbSat, 07 Feb 2015 04:02:07 +0300000000amSat, 07 Feb 2015 04:02:07 +030015 2015, 04:02:07
7

Ваше имя спрашивает о innodb_buffer_pool_size, но я подозреваю, что это не настоящая проблема. (Роландо прокомментировал, почему вы установили его достаточно большим, даже слишком большим.)

  

Я установил max connections = 800, который иногда насыщает и прерывает работу сервера.

Это неясно. 800 пользователей в режиме «Сон» практически не влияют на систему. 800 активных потоков были бы катастрофой. Сколько потоков работает?

Связаны ли потоки друг с другом? Смотрите SHOW ENGINE INNODB STATUS для некоторых подсказок о тупиках и т. Д.

Появляются ли какие-либо запросы в медленном журнале? Давайте их оптимизировать.

Какую версию вы используете? XtraDB (замена для InnoDB) улучшает работу с несколькими ядрами. 5.6.7 делает еще лучшую работу.

innodb_buffer_pool_instances - измените это на 8 (предположив 20G buffer_pool); он немного сократится при конкуренции Mutex.

Связаны ли вы с I /O или связаны ли вы с CPU? Решения радикально различаются в зависимости от вашего ответа.

SSD. Возможно, было бы лучше, если бы все файлы журнала были на дисках, отличных от SSD.

ответил Rick James 23 +04002012-10-23T03:19:55+04:00312012bEurope/MoscowTue, 23 Oct 2012 03:19:55 +0400 2012, 03:19:55
4

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

Временной интервал, который вы указываете для активных страниц, влияет на производительность, но есть оптимальная точка, в которой вы не получите более высокую производительность для большего размера буфера. Вы можете оценить /рассчитать /измерить это с помощью «showon status innodb».

ответил user77376 14 MarpmFri, 14 Mar 2014 14:23:47 +04002014-03-14T14:23:47+04:0002 2014, 14:23:47

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132