Недостаточно памяти для сортировки увеличьте размер буфера сортировки на сервере
Обновлено: 03.07.2024
PostgreSQL не читает данные напрямую с диска и не пишет их сразу на диск. Данные загружаются в общий буфер сервера, находящийся в разделяемой памяти, серверные процессы читают и пишут блоки в этом буфере, а затем уже изменения сбрасываются на диск. Если процессу нужен доступ к таблице, то он сначала ищет нужные блоки в общем буфере. Если блоки присутствуют, то он может продолжать работу, если нет — делается системный вызов для их загрузки.
Если объём буфера недостаточен для хранения часто используемых рабочих данных, то они будут постоянно писаться и читаться из кэша ОС или с диска, что крайне отрицательно скажется на производительности.
- Средний объём данных и 256-512 МБ доступной памяти: 16-32 МБ
- Большой объём данных и 1-4 ГБ доступной памяти: 64-256 МБ
temp_buffers
Буфер под временные объекты, в основном для временных таблиц.
Можно установить порядка 16 МБ
max_prepared_transactions
Количество одновременно подготавливаемых транзакций.
Для работы 1С этот параметр значения не имеет, PREPARE TRANSACTION там не используются. Да и в других приложениях его лучше не трогать.
Можно оставить по дефолту – 5
work_mem
Разумное значение параметра определяется следующим образом: количество доступной оперативной памяти (после того, как из общего объема вычли память, требуемую для других приложений, и shared_buffers) делится на максимальное число одновременных запросов умноженное на среднее число операций в запросе, которые требуют памяти.
Если объём памяти недостаточен для сортировки некоторого результата, то серверный процесс будет использовать временные файлы. Если же объём памяти слишком велик, то это может привести к своппингу.
Единица измерения параметра — 1 кБ. Значение по умолчанию — 1024. В качестве начального значения для параметра можете взять 2–4% доступной памяти.
Для веб-приложений обычно устанавливают низкие значения work_mem, так как запросов обычно много, но они простые, обычно хватает от 512 до 2048 КБ.
Приложения для поддержки принятия решений, аналитических систем с сотнями строк в каждом запросе и десятками миллионов столбцов в таблицах фактов часто требуют work_mem порядка 500 МБ.
Для баз данных, которые используются и так, и так, этот параметр можно устанавливать для каждого запроса индивидуально, используя настройки сессии.
Например, при памяти 1–4 ГБ рекомендуется устанавливать 32–128 MB. Для 1С это рекомендованное значение.
maintenance_work_mem
Память использующаяся для операций VACUUM, CREATE INDEX, ALTER TABLE и FOREGIN KEY.
Неплохо бы устанавливать его значение от 50 до 75% размера вашей самой большой таблицы или индекса или, если точно определить невозможно, от 32 до 256 МБ. Следует устанавливать большее значение, чем для work_mem. Слишком большие значения приведут к использованию свопа.
max_stack_depth
Специальный стек для сервера, в идеале он должен совпадать с размером стека, выставленном в ядре ОС. Установка большего значения, чем в ядре, может привести к ошибкам.
Рекомендуется устанавливать 2-4MB
max_fsm_relations и max_fsm_pages
Особенностями версионных движков БД (к которым относится и используемый в PostgreSQL) является следующее:
- Транзакции, изменяющие данные в таблице, не блокируют транзакции, читающие из неё данные, и наоборот (это хорошо);
- При изменении данных в таблице (командами UPDATE или DELETE) накапливается мусор1 (а это плохо).
В каждой СУБД сборка мусора реализована особым образом, в PostgreSQL для этой цели применяется команда VACUUM.
До версии 7.2 команда VACUUM полностью блокировала таблицу. Начиная с версии 7.2, команда VACUUM накладывает более слабую блокировку, позволяющую параллельно выполнять команды SELECT, INSERT, UPDATE и DELETE над обрабатываемой таблицей. Старый вариант команды называется теперь VACUUM FULL.
Новый вариант команды не пытается удалить все старые версии записей и, соответственно, уменьшить размер файла, содержащего таблицу, а лишь помечает занимаемое ими место как свободное. Для информации о свободном месте есть следующие настройки:
max_fsm_relations Максимальное количество таблиц, для которых будет отслеживаться свободное место в общей карте свободного пространства. Эти данные собираются VACUUM. Параметр max_fsm_relations должен быть не меньше общего количества таблиц во всех базах данной установки (лучше с запасом);
max_fsm_pages Данный параметр определяет размер реестра, в котором хранится информация о частично освобождённых страницах данных, готовых к заполнению новыми данными. Значение этого параметра нужно установить чуть больше, чем полное число страниц, которые могут быть затронуты операциями обновления или удаления между выполнением VACUUM. Чтобы определить это число, можно запустить VACUUM VERBOSE ANALYZE и выяснить общее число страниц, используемых базой данных. max_fsm_pages обычно требует немного памяти, так что на этом параметре лучше не экономить.
Если эти параметры установлены верно и информация обо всех изменениях помещается в FSM, то команды VACUUM будет достаточно для сборки мусора, если нет – понадобится VACUUM FULL, во время работы которой нормальное использование БД сильно затруднено.
ВНИМАНИЕ! Начиная с 8.4 версии fsm параметры были убраны, поскольку Free Space Map сохраняется на жесткий диск, а не в память.
max_files_per_process
Максимальное количество файлов, открываемых процессом и его подпроцессами в один момент времени.
vacuum_cost_delay
Если у вас большие таблицы, и производится много одновременных операций записи, вам может пригодиться функция, которая уменьшает затраты на I/O для VACUUM, растягивая его по времени. Чтобы включить эту функциональность, нужно поднять значение vacuum_cost_delay выше 0.
Используйте разумную задержку от 50 до 200 мс.
Для более тонкой настройки повышайте vacuum_cost_page_hit и понижайте vacuum_cost_page_limit. Это ослабит влияние VACUUM, увеличив время его выполнения.
В тестах с параллельными транзакциями Ян Вик (Jan Wieck) получил, что при значениях delay — 200, page_hit — 6 и limit —100 влияние VACUUM уменьшилось более чем на 80%, но его длительность увеличилась втрое;
Я пытался выполнить довольно большой INSERT. SELECT в MySQL с JDBC, и я получил следующее исключение:
Поскольку я на самом деле не возвращаю объект ResultSet, я подумал, что пространство кучи Java не должно быть проблемой. Тем не менее, я все равно пытался поднять его, и это не помогло. Затем я попытался выполнить инструкцию в MySQL Workbench, и я получил по существу то же самое:
У меня должно быть достаточно оперативной памяти для выполнения этих операций (достаточно, чтобы вместить всю таблицу, из которой я выбираю), но я предполагаю, что существуют различные настройки, которые необходимо настроить, чтобы использовать всю свою память. Я использую большой дополнительный экземпляр Amazon EC2 с двойной памятью и Windows Server 2008 AMI. Я попытался поиграться с файлом my.ini, чтобы использовать лучшие настройки, но, насколько я знаю, я мог бы ухудшить ситуацию. Вот дамп этого файла:
Так что это просто вопрос изменения вышеуказанных настроек, чтобы они лучше работали в моей среде? Если да, какие настройки я должен использовать? Я единственный, кто когда-либо использовал этот экземпляр; Я использую его для своего личного хобби проекта, который включает в себя статистический анализ больших наборов данных. Таким образом, я могу позволить ему использовать все доступные ресурсы для моих собственных запросов.
Если проблема не в изменении этих настроек, в чем проблема? Спасибо за любую помощь, которую вы можете предложить, как лучше настроить все.
Любой, кто использует кеш запросов 1g, понятия не имеет, что он делает. @winmutt Вы вполне можете быть правы, но ваш комментарий никому не поможет без дальнейших объяснений. Не могли бы вы помочь нам, объяснив свои чувства?Учитывая, что это установка Windows, @DTest все еще указывал правильное начальное направление.
Примените следующую формулу:
Большинство людей используют это:
Я предпочитаю это:
Эти переменные необходимо корректировать до тех пор, пока формула не даст 80% установленной оперативной памяти или меньше.
Я бы попробовал уменьшить размер буфера. Создание таких размеров, как у вас, вызовет проблемы. Сколько памяти у вас доступно для запуска этих значений:
Некоторые размеры буфера выделяются для каждого потока, например, myisam_sort_buffer_size 10G выделяет 10G для каждого потока.
Сначала я резко уменьшу эти значения, а затем выясню, какие значения вам действительно нужны для выделения такого большого объема ОЗУ (если оно есть).
Быстрый способ определить, сколько памяти MySQL считает, что может выделить:
Когда вы запустите этот скрипт, он скажет вам, какой процент установленной оперативной памяти MySQL считает безопасным для себя. Если ответ превышает 100%, вам определенно необходимо уменьшить размер буфера. Основными из них являются:
sort_buffer_size
read_buffer_size
read_rnd_buffer_size
join_buffer_size
max_connections
key_buffer_size ( на самом деле не эффективны в прошлом 4G)
@DTest уже установил для вас направление в своем ответе, поэтому +1 для его ответчика. Сценарий Perl сообщит вам, что произойдет, если вы не установите его или измените какое-либо значение. Вот пример:
Мой клиент имеет
read_buffer_size = 128K
read_rnd_buffer_size = 256K
sort_buffer_size = 2M
join_buffer_size = 128K
max_connections = 1050
Вот вывод из mysqltuner.pl:
-------- Общая статистика ---------------------------------------- ----------
[-] Проверка пропущенной версии для сценария MySQLTuner
[OK] В настоящее время работает поддерживаемая версия MySQL 5.0.51a-community-log
[!!] Переключиться на 64-разрядную ОС - MySQL в настоящее время не может использовать вся ваша оперативная память
-------- Статистика механизма хранения --------------------------------------- ----
[-] Статус: + Архив -BDB + Federated + InnoDB -ISAM -NDBCluster
[-] Данные в таблицах MyISAM: 319M (Таблицы: 108)
[-] Данные в таблицах InnoDB: 2M (Таблицы: 5)
[!!] Всего фрагментированных таблиц: 22
-------- Показатели эффективности ---------------------------------------- ---------
[-] Up для: 52d 23h 15m 57s (72M q [15,875 qps], 241K conn, TX: 2B, RX: 1B)
[-] Чтение / запись: 59% / 41%
[-] Всего буферов: 34,0M глобальных + 2,7M на поток (максимум 1050 потоков)
[!!] Выделение> 2 ГБ ОЗУ в 32-разрядных системах может вызвать нестабильность системы
[!!] Максимально возможное использование памяти: 2,8 ГБ (72% установленной оперативной памяти)
[OK] Медленные запросы: 0% (54 / 72M)
[OK] Максимальное использование доступных подключений: 6% (65/1050)
[OK] Размер буфера ключа / всего индексов MyISAM: 8,0M / 82.1M
[OK] Частота обращений к буферу ключей: 100.0% (4B кэшировано / 1M операций чтения)
[!!] Кэш запросов отключен
[OK] Сортировки, требующие временных таблиц: 0% (0 временных сортировок / 948K сортировок)
[OK] Временные таблицы, созданные на диске: 3% (11K на диске / 380K всего)
[!!] Кэш потоков отключен
[!!] Частота обращений в кэш таблиц: 0% (64 открытых / 32K открытых)
[OK] Открыть файл используемый предел: 2% (125 / 5K)
[OK] Блокировки таблицы получены немедленно: 99% (немедленные 30M / 30M блокировки)
[OK] Размер данных InnoDB / пул буферов: 2,7M / 8,0M
-------- Рекомендации ----------------------------------------- ------------
Общие рекомендации:
Запустите OPTIMIZE TABLE для дефрагментации таблиц для повышения производительности.
Включите медленный журнал запросов для устранения проблем с ошибочными запросами.
Установите thread_cache_size в качестве начального значения
4, постепенно увеличивая table_cache, чтобы избежать ограничений дескриптора файла.
Переменные настроить:
query_cache_size (> = 8M)
thread_cache_size (начиная с 4)
table_cache (> 64)
Пожалуйста, обратите внимание на показатели производительности
[-] Общее количество буферов: 34.0M глобально + 2.7M на поток (максимум 1050 потоков)
что MySQL может выделить до 72% установленной оперативной памяти в зависимости от настроек в /etc/my.cnf.
34M основан на сочетании innodb_buffer_pool_size и key_buffer_size
2.7M на поток основывалось на read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size.
Кратные 2.7M основаны на max_connections.
Поэтому вы должны настроить эти параметры до тех пор, пока в отчете метрик производительности не будет указано, что у вас установлено менее 100% (предпочтительно менее 80%) установленной оперативной памяти.
Настройки MySQL по-умолчанию зачастую не оптимальны, они предназначены для начальных конфигураций с малыми ресурсами, около 512 МБ. Именно поэтому всегда настройки необходимо оптимизировать под каждую конкретную инсталляцию. Процесс оптимальной настройки Mysql состоит из двух частей — первоначальная настройка и корректировка параметров во время работы. Настройки нужно вносить в файл my.cnf по мере их уточнения.
Любой тюнинг MySQL должна начинаться с определения преобладающего количества таблиц определенного типа — InnoDB или MyISAM. Существуют настройки общие, а существуют и специфичные для разных типов таблиц. Для начала разберемся, как MySQL работает с памятью.
Выделение памяти в MySQL
Работа с потоками
Работа с памятью играют значительную роль для скорости и эффективности обработки параллельных транзакций и исполнения больших SQL запросов. Каждый поток (тред) использует память для клиентских соединений, и это потоки используют общую базовую память. Есть переменные thread_stack (стек потоков), net_buffer_length (для буфера соединений и буфера результата), которые динамически увеличивают свое значение до значения max_allowed_packet , когда это требуется и не влияют на общую утилизацию памяти. Буфер результата урезается до значения net_buffer_length после каждого SQL выражения. Когда поток больше не требуется, память, выделенная на данный поток, освобождается и возвращается системе до момента, пока поток не вернется в кэш потоков. Каждое поток соединения также использует память для для вычисления дайджестов SQL выражений. Параметр max_digest_length в байтах указывает на количество памяти, которое сервер выделяет на сессию.
Почти весь синтаксический анализ и вычисления выполняются в пулах памяти, локальных для потока и повторно используемых. Для небольших элементов не требуется дополнительных затрат памяти, что позволяет избежать обычного медленного выделения и освобождения памяти. Память выделяется только для неожиданно больших строк.
Работа с таблицами
MySQL использует память и дескрипторы для кеширования таблиц. Структуры обработчиков для всех используемых таблиц сохраняются в кэше таблиц и управляются как «первым пришел — первым ушел» (FIFO). Системная переменная table_open_cache определяет начальный размер кэша таблицы. MySQL также требует памяти для кеша дескрипторов таблиц. Системная переменная table_definition_cache определяет количество дескрипторов таблиц, которые могут храниться в кэше дескрипторов таблиц. Если вы используете большое количество таблиц, вы можете создать большой кэш дескрипторов таблиц, чтобы ускорить открытие таблиц. Кэш дескрипторов таблицы занимает меньше места и не использует файловые дескрипторы, в отличие от кеша таблицы.
MySQL также выделяет память для временных таблиц, если она не становится слишком большой (определяется tmp_table_size и max_heap_table_size). Если внутренняя временная таблица в памяти становится слишком большой (определено в системных переменных tmp_table_size и max_heap_table_size), MySQL автоматически преобразует таблицу из формата в памяти в формат на диске. Если вы используете таблицы MEMORY и для переменной max_heap_table_size установлено очень большое значение, это приводит к потреблению большого объема памяти, поскольку системная переменная max_heap_table_size определяет, насколько велика таблица, и преобразование в дисковый формат отсутствует.
Все джойны (JOIN) выполняются за один проход, и большинство из них может быть выполнено даже без использования временной таблицы. Большинство временных таблиц представляют собой хеш-таблицы на основе памяти. Временные таблицы с большой длиной строки (рассчитанной как сумма длин всех столбцов) или содержащие столбцы BLOB хранятся на диске. Большинство запросов, выполняющих сортировку (SORT), выделяют буфер сортировки и от нуля до двух временных файлов в зависимости от размера набора результатов.
Пулы и буферы
Запросы JOIN, запросы к кэшу, сортировки, кэш таблиц, дескрипторы таблиц потребляют память постоянно, но они настраиваются с помощью системных переменных. В большинстве случаев, такие переменные привязаны к типу хранилища - InnoDB или MyISAM. Когда экземпляр mysqld появляется в хост-системе, MySQL выделяет буферы и кеши для повышения производительности операций с базой данных на основе значений, установленных в конкретной конфигурации.
Например, наиболее распространенными переменными, которые каждый администратор баз данных устанавливает в InnoDB, являются переменные innodb_buffer_pool_size и innodb_buffer_pool_instances, которые связаны с распределением памяти пула буферов, в котором хранятся кэшированные данные для таблиц InnoDB. Желательно, если у вас большой объем памяти и вы ожидаете обрабатывать большие транзакции, установив innodb_buffer_pool_instances для улучшения параллелизма путем разделения пула буферов на несколько экземпляров пула буферов.
В то время как для MyISAM вы должны иметь дело с key_buffer_size, чтобы обрабатывать объем памяти, который будет обрабатывать буфер ключей. MyISAM также выделяет буфер для каждого параллельного потока, который содержит структуру таблицы, структуры столбцов для каждого столбца и буфер размером 3 * N (где N — максимальная длина строки, не считая столбцов BLOB). MyISAM также поддерживает один дополнительный буфер строк для внутреннего использования.
Каждый запрос, который выполняет последовательное сканирование таблицы, выделяет буфер для чтения. Переменная read_buffer_size определяет размер такого буфера. При чтении строк в произвольной последовательности (например, после сортировки) может быть выделен буфер случайного чтения, чтобы избежать обращений к диску. Системная переменная read_rnd_buffer_size определяет размер буфера.
Мониторинг
MySQL также имеет Performance Schema, которая является функцией для мониторинга действий MySQL на низком уровне. Как только она включена (а он настройка по-умолчанию), то она постепенно динамически выделяет память, масштабируя использование памяти до фактической нагрузки сервера, вместо того, чтобы выделять требуемую память во время запуска сервера. После выделения памяти она не освобождается до перезапуска сервера. Именно поэтому, Performance Schema постоянно отъедает все больший кусок памяти и его не возвращает, что может сказаться на производительности сервера. Многие рекомендуют отключать Performance Schema полностью.
Буферы и настройки InnoDB
Пул буферов InnoDB — это область памяти, в которой хранятся кэшированные данные InnoDB для таблиц, индексов и других вспомогательных буферов. Для повышения эффективности операций чтения большого объема пул буферов разделен на страницы, которые потенциально могут содержать несколько строк. Для повышения эффективности управления кешем буферный пул реализован в виде связанного списка страниц; данные, которые редко используются, удаляются из кеша с использованием разновидности алгоритма LRU.
- InnoDB выделяет память для всего пула буферов при запуске сервера. Системная переменная innodb_buffer_pool_size определяет размер пула буферов. Обычно рекомендуемое значение innodb_buffer_pool_size составляет от 50 до 75% системной памяти (при условии, что она не используется другими сервисами). innodb_buffer_pool_size можно настраивать динамически, пока сервер работает без его перезагрузки.
- В системах с большим объемом памяти вы можете улучшить параллелизм, разделив пул буферов на несколько экземпляров пула буферов. Системная переменная innodb_buffer_pool_instances определяет количество экземпляров буферного пула. По умолчанию значение параметра равно 1, но более оправданным является значение вычисляемое как сумма innodb_buffer_pool_size в гигабайтах и ядер CPU, деленная пополам: (innodb_buffer_pool_size in Gb + CPUs)/2
- Слишком маленький пул буферов может вызвать чрезмерное перемешивание, поскольку страницы удаляются из пула буферов только для того, как они могут потребоваться снова через короткое время.
- Слишком большой пул буферов может вызвать своппинг из-за конкуренции за память. Если сервер уходит в SWAP, стоит уменьшать значения.
- innodb_file_per_table — если включить эту опцию, Innodb будет сохранять данные всех таблиц в отдельных файлах (вместо одного файла по умолчанию). С версии 5.6 этот параметр включен по умолчанию. Прироста в производительности не будет, однако есть ряд преимуществ:
- При удалении таблиц, диск будет освобождаться. По умолчанию общий файл данных может только расширяться, но не уменьшаться.
- Использование компрессионного формата таблиц потребует включить этот параметр.
Настройки MyISAM
- key_buffer_size (default 8M) — крайне важная настройка при использовании MyISAM-таблиц. Установите её равной около 20-40% от доступной оперативной памяти, если используете только MyISAM. Правильный размер зависит от размеров индексов, данных и нагрузки на сервер — помните, что MyISAM использует кэш операционной системы (ОС), чтобы хранить данные, поэтому нужно оставить достаточно места в ОЗУ под данные, и данные могут занимать значительно больше места, чем индексы. Однако обязательно проверьте, чтобы всё место, отводимое директивой key_buffer_size под кэш, постоянно использовалось — нередко можно видеть ситуации, когда под кэш индексов отведено 4 ГБ, хотя общий размер всех .MYI-файлов не превышает 1 ГБ. Делать так совершенно бесполезно, Вы только потратите ресурсы. Если у Вас практически нет MyISAM-таблиц, то key_buffer_size следует выставить около 16-32 МБ — они будут использоваться для хранения в памяти индексов временных таблиц, создаваемых на диске. Памяти под индексы выделяется достаточно если отношение значений Key_reads/Key_read_request оказывается < 0,01.
Узнать значения параметров можно выполнив в консоли сервера баз данных запрос
Затем установить значение переменной несколько больше значения в выводе:
+—————+——-+
| Variable_name | Value |
+—————+——-+
| Opened_tables | 1756 |
+—————+——-+SHOW STATUS LIKE «Max_used_connections%»;
Общие буферы MySQL
Большинство из этих настроек стоит трогать, если вы понимаете, как устроена ваша база, и какие к ней делается запросы.
- sort_buffer=128M – ускоряет запросы ORDER BY или GROUP BY. Устанавливается не очень большим (как правило — в зависимости от среды). Выделяемое количество ОЗУ будет определяться как значение sort_buffer, умноженное на Threads_running.
SHOW STATUS LIKE «Threads_running%»;
- record_buffer=720M — хорошим значением будет эквивалент sort_buffer умноженный на 4-6.
- read_buffer_size (default 128K) - последовательный буфер чтения потоков.
- join_buffer_size (default 128K) - используется под JOIN без индексов, лучшим решением (вместо использования параметра) является ораганизация базы с джойнами которые могут быть проиндексированы — если такой возможности нет джойны ускоряются параметром join_buffer_size.
- max_join_size=1000000 — максимальное количество записей, которые могут обрабатываться единовременно. Своего рода «защита от дурака» при использовании JOIN.
Общие настройки
max_connections=64 — устанавливаем параметр минимальным возможным при необходимости экономить ресурсы сервера, при возникновении в логе записей вида «Too many connections…» увеличиваем значение. Не следует изменять значение этого параметра на старте. 4000 клиентов является максимумом. Можно довести максимальное количество клиентов до 7000, но для стандартных сборок 4000 является пределом.
open_files_limit = 2048 Устанавливать значение стоит опираясь на существующее количество открытых файлов MySQL:
В конфигурационном файле задается большее значение.
connect_timeout (MySQL pre-5.1.23: default 5, MySQL 5.1.23+: default 10) - количество секунд по прошествии которых сервер баз данных будет выдавать ошибку, при активном веб-сервере значение можно уменьшать чтобы увеличить скорость работы, на медленной машине — можно увеличивать.
max_connect_errors (default 10) - максимальное количество единовременных соединений с сервером баз данных с хоста запрос блокируется если он прерывается запросами с того же хоста до момента окончания обработки запроса)
блокируются навсегда, очистить можно только из командной оболочки MySQL:В случае атаки на сервер нужно уменьшать (5) чтобы отсекать попытки соединения, при большой активности веб-сервера можно увеличивать
max_allowed_packet (default 1M) - максимальный для буфера соединений и буфера результата при исполнении SQL инструкций. Каждый тред имеет свой буфер. Хорошим значением для начала будет 16М.
tmp_table_size (system-specific default) - максимальный размер памяти выделяемой под хранение временных таблиц. 16М — довольно много.Примеры готовых конфигураций для разных объёмов памяти можно посмотреть здесь.
Чтобы посомореть значения переменных можно воспользоваться SQL запросом:
mysql> SHOW SERVER STATUS\G
или для конкретных переменных:
mysql> SHOW SERVER STATUS WHERE variable_name IN ( '<var1>' , 'var2' . );
Чтобы проверить мониториг InnoDB, используте:
mysql> SHOW ENGINE INNODB STATUS\G
Чтобы узнать, не свопается ли память, используйте команду и смотрите строку swap:
Аннотация: Когда мы используем mariadb, мы обнаруживаем, что иногда он не может быть запущен. Mariadb занимает много памяти во время использования. Здесь мы узнаем о mariadb и элементах конфигурации, связанных с памятью, и настраиваем mariadb.
Используйте следующую команду, чтобы узнать, сколько оперативной памяти использует конфигурация mysql.
можно использоватькалькулятор mysqlДля расчета использования памяти
Следующее - теория, вы можете сразу перейти к рекомендуемой конфигурации
key_buffer_size (для индекса MyISAM)
Укажите размер индексного буфера, который определяет скорость обработки индекса, особенно скорость чтения индекса. Чтобы минимизировать дисковый ввод-вывод, таблицы механизма хранения MyISAM используют ключевой кеш для кэширования индексов, а размер этого ключевого кеша устанавливается параметром key-buffer-size. Если таблица, используемая в прикладной системе, основана на механизме хранения MyISAM, значение этого параметра должно быть увеличено соответствующим образом, чтобы максимально кэшировать индекс и повысить скорость доступа.
Как установить
Другая ссылка выглядит следующим образом
Key_blocks_unused представляет количество неиспользуемых кластеров (блоков) кеша, Key_blocks_used представляет наибольшее количество блоков, когда-либо использовавшихся, например, этот сервер, все кеши используются, либо увеличьте key_buffer_size, либо индекс перехода заполнен . Идеальные настройки:
- Возможность динамической настройки в соответствии с этим стилем работы Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%
- Механизм хранения запросов
innodb_buffer_pool_size (для индекса innodb)
Этот параметр и MyISAM key_buffer_size Есть сходства, но есть и различия. Этот параметр в основном кэширует индекс, данные таблицы innodb и буфер при вставке данных. Оптимизируйте первые параметры для ускорения Innodb.
Принцип распределения памяти по параметрам: по умолчанию для этого параметра выделяется только 8 МБ, что можно назвать очень маленьким значением.
- Если это выделенный сервер БД и движок InnoDB является основным сценарием, обычно может быть установлено 50% физической памяти.Этот параметр не может быть изменен динамически, поэтому распределение требует большего внимания. Если выделение слишком велико, Swap будет занимать слишком много места, что сделает запрос Mysql чрезвычайно медленным.
- Если это невыделенный сервер БД, вы можете сначала попытаться установить для него 1/4 объема памяти, а затем отрегулировать, если есть проблема.
query_cache_size (кеш запросов)
Механизм кэширования просто означает кэширование текста SQL и результатов запроса.Если тот же SQL запущен, сервер будет напрямую извлекать результаты из кеша без необходимости синтаксического анализа и выполнения SQL. Если таблица будет изменена, все кэшированные запросы, использующие эту таблицу, станут недействительными, а соответствующие записи значения кэша запросов будут удалены. Изменение относится к любым изменениям данных или структуры в таблице, включая INSERT, UPDATE, DELETE, TRUNCATE, ALTER TABLE, DROP TABLE или DROP DATABASE и т. Д., А также к запросам, использующим таблицы MERGE, сопоставленные с измененными таблицами. Очевидно, что этот кеш запросов не подходит для часто обновляемых таблиц, а для некоторых таблиц, которые не часто меняют данные и имеют большое количество одинаковых запросов SQL, кеш запросов значительно снизит производительность.
- Примечание. Если запрашиваемая таблица часто обновляется и есть несколько идентичных запросов, лучше не использовать кеш запросов. Потому что это потребляет много производительности системы и не влияет
Вы хотите его открыть?
Настройте его на некоторое время
Посмотрите на результаты попаданий для дальнейшего суждения
Qcache_free_blocks: указывает, сколько оставшихся блоков в настоящее время находится в кэше запросов. Если значение показывает большое значение, это означает, что в кэше запросов слишком много фрагментов памяти и их можно отсортировать за определенный период времени.
Qcache_free_memory: размер памяти кеша запросов. С помощью этого параметра вы можете четко определить, достаточно ли памяти запросов в текущей системе, слишком ли она или недостаточно. Администратор базы данных может вносить корректировки в соответствии с реальной ситуацией.
Qcache_hits: указывает, сколько попаданий в кеш. В основном мы можем использовать это значение для проверки эффективности нашего кеша запросов. Чем больше число, тем лучше эффект кеширования.
Qcache_inserts: указывает количество промахов, а затем вставок, что означает, что новый запрос SQL не найден в кэше и должна быть выполнена обработка запроса.После выполнения обработки запроса результат вставляется в кеш запроса. В этом случае, чем больше раз, тем меньше применяется кеш запросов, и эффект не идеален. Конечно, кеш запросов пуст сразу после запуска системы, что нормально.
Qcache_lowmem_prunes: этот параметр записывает, сколько запросов было удалено из кеша запросов из-за нехватки памяти. С помощью этого значения пользователи могут соответствующим образом настроить размер кеша.
Qcache_not_cached: указывает количество запросов, которые не были кэшированы из-за установки query_cache_type.
Qcache_queries_in_cache: количество запросов, кэшированных в текущем кэше.
Qcache_total_blocks: количество блоков, кэшированных в данный момент.
- Мы можем видеть, что действующая сеть достигает 1 158, а некэшированная - 1 755 767. Это показывает, что в нашей системе слишком мало совпадений, а таблица сильно изменилась. Нет ничего, что позволяло бы этой функции задействовать параметры.
- query_cache_limit: максимальная емкость одного набора результатов запроса, разрешенная кешем. Значение по умолчанию - 1 МБ. Наборы результатов запроса, превышающие это значение параметра, не будут кэшироваться.
- query_cache_min_res_unit: устанавливает минимальный размер пространства каждой выделенной памяти в Query Cache, то есть минимальное пространство памяти, занимаемое каждым Query Cache.
- query_cache_size: установите размер памяти, используемый Query Cache. Значение по умолчанию - 0. Размер должен быть целым числом, кратным 1024. Если это не целое число, MySQL автоматически настроит и уменьшит минимальный объем, чтобы достичь кратного 1024.
- query_cache_type: переключатель, управляющий функцией Query Cache, который может быть установлен на 0 (OFF), 1 (ON) и 2 (DEMAND). Значения следующие: 0 (OFF): выключить функцию Query Cache. Он не будет использоваться ни при каких обстоятельствах. Query Cache 1 (ON): включите функцию Query Cache, но когда используется подсказка SQL_NO_CACHE, используемая в операторе SELECT, Query Cache 2 (DEMAND): Включите функцию Query Cache, но только если подсказка SQL_CACHE используется в операторе SELECT. Использовать только кеш запросов
- query_cache_wlock_invalidate: определяет, следует ли аннулировать кэш запросов, связанный с таблицей, при возникновении блокировки записи в таблице. Если установлено значение 1 (TRUE), весь кэш запросов, связанный с таблицей, будет аннулирован, пока установлена блокировка записи. Если установлено значение 0 (FALSE) все еще разрешено читать кэш запросов, связанный с таблицей, во время блокировки.
innodb_additional_mem_pool_size (размер внутреннего каталога InnoDB)
Кэш словарной информации InnoDB в основном используется для хранения словарной информации механизма хранения InnoDB и некоторой внутренней информации о структуре общих данных, которая является внутренним каталогом InnoDB, поэтому его размер также больше, чем количество таблиц механизма хранения InnoDB, используемых в системе. отношения.
Это значение не нужно выделять слишком много, обычно достаточно 16M, по умолчанию - 8M, если установленного размера памяти недостаточно, InnoDB автоматически подаст заявку на увеличение памяти и запишет информацию о предупреждениях в журнал ошибок MySQL.
innodb_log_buffer_size (буфер журнала)
Указывает количество байтов в буфере, используемом при записи InnoDB в файл журнала на диске.Значение по умолчанию - 16M. Большой буфер журнала позволяет большому количеству транзакций не записывать журналы на диск перед фиксацией, поэтому, если есть транзакции, которые обновляют, вставляют или удаляют много строк, увеличение размера буфера журнала может сэкономить дисковый ввод-вывод.
Обычно достаточно 64M
max_connections (максимальное количество одновременных подключений)
Параметр MySQL max_connections используется для установки максимального количества подключений (пользователей). Каждый пользователь, подключающийся к MySQL, считается подключением, а значение max_connections по умолчанию равно 100.
- Фактическое максимальное значение этого параметра (фактическое максимальное количество подключений) составляет 16384, то есть максимальное значение этого параметра не может превышать 16384, даже если оно превышает, 16384 имеет преимущественную силу;
- Увеличение значения параметра max_connections не займет слишком много системных ресурсов. Занятость системных ресурсов (ЦП, память) в основном зависит от плотности и эффективности запроса;
- Самая очевидная особенность настройки слишком маленького параметра - это ошибка «Слишком много соединений».
Вы можете видеть, что количество одновременных операций в настоящее время равно Threads_connected = 1, что далеко от 2048.
max_connections также зависит от ограничения операционной системы на максимальное количество файлов, разрешенных для открытия в одном процессе
Другими словами, если операционная система ограничивает один процесс максимум 100 файлами
Таким образом, установка max_connections на 200 бесполезна
Значение параметра MySQL open_files_limit - это значение максимального количества открытых файлов в одном процессе, записанное операционной системой при запуске MySQL.
Вы можете использовать переменные show, такие как 'open_files_limit'; для просмотра значения open_files_limit
Или непосредственно под Linux используйте команду ulimit -n, чтобы просмотреть максимальное количество файлов, открытых операционной системой для одного процесса (по умолчанию 1024)
Параметр уровня соединения предназначен для одновременного выделения установленной памяти, когда каждое соединение должно использовать этот буфер впервые.
Производительность сортировки
Для сортировки MySQL использует две переменные для управления sort_buffer_size и max_length_for_sort_data, в отличие от Oracle, использующего управление SGA. Недостатком этого метода является то, что им нужно управлять отдельно, что может привести к проблемам с производительностью сортировки.
- Если найден Sort_merge_passes Относительно большой, вы можете увеличить sort_buffer_size Чтобы ускорить операции ORDER BY или GROUP BY, его нельзя оптимизировать с помощью запроса или индекса. Наше значение равно 0, поэтому нет необходимости устанавливать его таким большим.
Читать кеш
read_buffer_size = 128K (по умолчанию 128K) указывает буфер для потока таблицы данных MYISAM, который требует полного сканирования таблицы
read_rnd_buffer_size = 4M: (по умолчанию 256K) Прежде всего, эту переменную может использовать любой механизм хранения.При чтении строк из отсортированной таблицы значений ключей она будет сначала извлечена из буфера, а не с диска.
Большой журнал транзакций
- Binlog_cache_disk_use указывает, сколько раз временные файлы использовались для кэширования двоичных журналов из-за недостатка памяти в нашем проекте binlog_cache_size
- Binlog_cache_use представляет количество кешей с binlog_cache_size
- Когда соответствующее значение Binlog_cache_disk_use относительно велико, мы можем рассмотреть возможность увеличения соответствующего значения binlog_cache_size соответствующим образом.
- Как показано выше, текущая сеть составляет 32 КБ, мы добавляем к 64 КБ.
Влияние оператора соединения на память
Если оператор соединения редко появляется в приложении, вам не нужно слишком заботиться о размере установки параметра join_buffer_size.
Если операторов соединения немного, я предлагаю вам соответствующим образом увеличить join_buffer_size примерно до 1 МБ или установить его на 2 МБ, если памяти достаточно.
Влияние потока на память
Thread_stack: когда создается каждый поток подключения, MySQL выделяет ему объем памяти. Когда MySQL создает новый поток подключения, ему необходимо выделить для него определенный размер пространства стека памяти, чтобы хранить запрос, запрошенный клиентом, и свою собственную информацию о состоянии и обработке.
Как указано выше: система получила 9 639 111 соединений от клиента, пока система не была запущена, и было создано 9 639 301. В настоящее время 1 поток соединений находится в состоянии соединения с клиентом. Всего в пуле кэша потоков кэшируется 0 потоков подключения (Threads_cached).
Частота попаданий в кеш потоков:
Как правило, после того, как система стабильно работает в течение определенного периода времени, частота попаданий в кэш потоков должна поддерживаться на уровне примерно 90%, прежде чем она будет считаться нормальной.
Временная таблица памяти
tmp_table_size управляет максимальным значением временной таблицы памяти. При превышении лимита она будет записана на жесткий диск. Позиция записи определяется переменной tmpdir
max_heap_table_size Размер таблицы памяти, которую может создать пользователь.Это значение используется для вычисления максимального значения строки таблицы памяти.
Если выполняется много операций «Упорядочить по» или «Группировать по», увеличьте эти два значения, значение по умолчанию - 16 млн.
- Как показано на рисунке выше, промежуточная таблица, записываемая на жесткий диск, составляет 0,3 раза, что указывает на то, что нашего значения по умолчанию достаточно.
- Обратите внимание, что здесь рекомендуется только движок innodb
- Конфигурация памяти фокусируется только на прокомментированных строках
Используйте следующую команду для изменения переменной
- Мы это видим innodb_buffer_pool_size с участием thread_stack Если сообщается об ошибке, они могут изменить только файл конфигурации, который во время выполнения доступен только для чтения. Скопируйте и используйте прямо ниже
Эта статья была опубликована сообществом Tencent Cloud + в различных каналах.
Читайте также: