Mariadb настройка my cnf

Хочу рассказать о настройках, которые применял, но не все из них помогли, просто опишу их.

Введение

Как я успел разобраться, при каждом соединении с MySQL создается mysqld (демон), который и обрабатывает все запросы соединения. Вот в блоке [mysqld] описывается именно настройки таких демонов.

Итак, давайте рассмотрим настройки демона [mysqld].

Выставить кодировку по умолчанию можно так:

character-set-server = utf8
collation-server = utf8_unicode_ci

Защитить сервер от кривых рук программиста, способного join`ом на 10 миллионов записей похоронить даже 4-х процессорный сервер, можно так:

Буфер можно выставить 25% от общего объема оперативной памяти:

как я понял, это буфер обмена для всех демонов, т.е. реально будет: key_buffer_size / кол-во демонов = . M

Размер стека для каждого потока (демона):

стек – это место для хранения списка задач (открыть таблицу, выполнить запрос, закрыть и т.п.)

Кол-во потоков, которые сервер должен поместить в кэш для повторного использования:

т.е. если к примеру есть часто повтояющийся SELECT * FROM myTable, то он попадет в кэш, чтобы не выполняться каждый раз.

Полезная настройка: если размер временной таблицы превышает размер, установленный этой переменной, она сбрасывается на диск. При наличии достаточного количества памяти на сервере, рекомендуется повысить значение данной переменной, по ускорения запросов с конструкцией GROUP BY

Установить максимальный размер таблиц типа MEMORY ( HEAP ) можно так:

Размер буфера, выделяемого демону при выполнении операций сортировки. Для ускорения операций ORDER BY, GROUP BY рекомендуется увеличить данное значение

Размер буфера выделяемого для сортировки MyISAM индексов с помощью оператора REPAIR TABLE или при создании индексов операторами CREATE TABLE, ALTER TABLE:

Размер буфера, выделяемого демону для каждой сканируемой таблицы. При частом последовательном сканировании, рекомендуется увеличить значение данной переменной.

Размер буфера, выделяемого для чтения строк после сортировки, что-бы избежать повторного поиска на диске. Увеличение значения данной переменной может существенно увеличить эффективность конструкции ORDER BY. Имейте в виду, так как данный буфер выделяется для каждого демона, не следует устанавливать чересчур большое значение.

Размер буфера использующегося при операциях объединения таблиц ( если не используются индексы ). Буфер устанавливается один раз во время каждой операции объединения

Величина буфера, который используется для индексов, всех демонов. Если используется много DELETE или INSERT запросов к таблицам с большим кол – индексов, то увеличение значения повысит скорость выполнения таких запросов. Для достижения еще большей скорости нужно использовать LOCK TABLES. Советуют устанавливать не больше чем 1/3 озу и не больше объема всех б.д.

Читайте также:  Как в компасе повернуть лист горизонтально

Максимально количество соединений клиентов с сервером

Задает максимально количество неудачных попыток подключения с хоста. Значение по-умолчанию 10. При достижении данного значения, хост блокируется. Разблокировать хост можно с помощью: mysql> FLUSH HOSTS

Максимальное число одновременных подключений для одной учетной записи MySQL. Значение по-умолчанию 0, отсутствие каких-либо ограничений

table_cache старое название для переменной table_open_cache, в нем указывается к оличество открытых таблиц для всех демонов. Увеличение значения приведет к увеличению количества используемых дескрипторов файла. Советуют рассчитывать по формуле : количество одновременных соединений * количество открытых таблиц в соединении. Т .е. для каждого соединения используется свои ячейки из кэша. Для проверки можно запустить mysqltuner.pl

Количество одновременно запускаемых демонов, советуют формулу: количество ядер процессора умножаем на 2

Размер буфера для соединений, устанавливаемый сервером в промежутках между запросами

Максимальный объем одного SQL-запроса к серверу. Изначально буфер сообщений имеет размер net_buffer_length и при необходимости, автоматически увеличивается до значения данной переменной.

Переменная задает количество байт при операциях сортировки значений BLOB или TEXT. Использованы только первые max_sort_length, остальные игнорируются

Полезная настройка: Изначальная величина буфера запросов. Если большинство запросов большого размера (например, при вставке записей с BLOB), его необходимо увеличить!

Полезная настройка: запросы, результат которых превышает значение данной переменной, не будут размещаться в кэше запросов.

Полезная настройка: объем памяти, выделенной для кэширования результатов запросов. По-умолчанию данный кэш отключен, значение – 0

Полезная настройка: вид кэширования:

Значение Объяснение Примечание
OFF Не кэширует и не извлекает результаты (по-умолчанию).
1 ON Кэширует все результаты, кроме запросов SELECT SQL_NO_CACHE . .
2 DEMAND Кэширует только запросы SELECT SQL_CACHE . .

не забывайте про query_cache_startup_type.

Настройки innodb (извините, что без пояснений, просто оставлю их тут, чтобы не забыть):

innodb_data_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:2000M;ibdata2:10M :autoextend
innodb_log_group_home_dir = /var/lib/mysql
innodb_buffer_pool_size = 64M
innodb_additional_mem_poo l_size = 32M
innodb_file_io_threads = 8
innodb_lock_wait_timeout = 50
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_c ommit = 2
innodb_flush_method = O_DIRECT

transaction-isolation = READ-COMMITTED
default-character-set = utf8

Настройки блока для создания дампов

Старое название следующей настройки: character-set-server = utf8 выдает ошибку: /usr/bin/mysql_upgrade: unknown variable ‘character-set-server=utf 8

Как находить проблемные места

  1. смотрим превышения в /myadmin/server_status.php
  2. смотрим инфу по ним в мане
  3. исправляем конфиги
  4. улучшаем SQL-запросы

И не забываем про разгон: 1 – 2 – 3 – 4

Надеюсь, кому-нибудь помог разобраться, удачки в освоении MySQL.

my.cnf — это файл конфигурации MySQL. Чтобы найти файл, используйте команду:

Читайте также:  Как выглядит видеокарта в системном блоке

В Linux’e обычно он находится по такому пути:

Редактирование

Для редактирования необходимо открыть файл my.cnf в текстовом редакторе:

После внесения изменений в файл, необходимо делать перезапуск mysql:

Пример конфигурации

Это пример настроек Mysql для сервера с 1Гб оперативной памяти и двумя ядрами:

Если в вашем конфиге есть настройка thread_concurrency — уберите ее.

Стандартные конфигурации

Для удобства используйте одну из указанных конфигураций для вашего сервера:

  • Для 1Гб оперативной памяти
  • Для 2Гб оперативной памяти
  • Для 4Гб оперативной памяти
  • Для 8Гб оперативной памяти
  • Для 16Гб оперативной памяти
  • Для 32Гб оперативной памяти
  • Для 64Гб оперативной памяти
  • Для 128Гб оперативной памяти

Описание, рекомендации и значение параметра innodb_log_file_size

Описание, рекомендации и значение параметра slow_query_log

Описание, рекомендации и значение параметра innodb_buffer_pool_size

Описание, рекомендации и значение параметра thread_cache

Описание, рекомендации и значение параметра max_binlog_size

Описание, рекомендации и значение параметра thread_cache_size

Описание, рекомендации и значение параметра query_cache_size

Простой способ выбрать индексы для Mysql

DROP INDEX в Mysql

Просмотр профиля запросов в Mysql

Запросы для выборки самых больших таблиц

Создание индексов на рабочих MySQL таблицах без головной боли

И как правильно работать с длительными соединениями в MySQL

Как исправить ошибку доступа к базе 1045 Access denied for user

Запрос для определения версии Mysql: SELECT version()

Как создать индекс в Mysql

Обновление до новой версии Mysql

Включение и работа с логом медленных запросов в Mysql

Включение или восстановление репликации на работащих Mysql серверах

Что значит и как это починить

Описание, рекомендации и значение параметра log_slow_queries

Что делать, если во время репликации встречается: Error ‘Duplicate entry ‘115846’ for key ‘PRIMARY” on query. Default database: ‘db’. Query: ‘INSERT INTO some_table .

5 параметров, которые стоит оптимизировать в Mysql для wordpress

Оптимизация записи на диск в Mysql с помощью innodb_flush_method

Один из важных моментов в скорости работы сайта, это правильная оптимизация конфигурационного файла базы данных my.cnf, с которым многие сталкиваются в процессе оптимизации системных ресурсов. Зачастую многие приведенные в сети мануалы рассказывают о настройках которые были применены к слабым конфигурациям и это не дает нужного ответа.

Я обычно использую сервера на уровне ксеонов с 32 гигабайтами памяти, поэтому найти нужную конфигурацию настроек mysql достаточно трудно, отсюда и попытки настроить самому методом тыка и проб. Что из этого получилось сегодня я постараюсь рассказать.

В ТЕМУСТАТЬИ

Плагины и темы WordPress, экономика создания сайта на премиум уровне

Как в Mysql и MariaDB установить оптимальный параметр innodb_log_file_size

Исходные данные для настройки

Итак рассматриваем систему с установленным ISP manager на котором стоит Centos и MariaDB. Задача, оптимизировать работу Mysql и ускорить тем самым обработку запросов на сайтах. Для начала я приведу, пример своего my.cnf который находится по адресу etc/my.cnf, если у вас стоит Debian то смотреть надо в папке другой. Итак вот так выглядит настроенный файл, но иногда я все таки еще изменяю некоторые настройки, о которых расскажу ниже.

Читайте также:  1С работа со строками примеры

[mysqld]
#open_files_limit = 2000

local-infile=0
innodb_file_per_table = 1

p >
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
ignore-db-dir=lost+found
max_allowed_packet = 1024M
skip-external-locking
skip-name-resolve

myisam-recover = BACKUP
max_connections = 500
table-cache = 120000
table-open-cache = 120000

Тюнинг базы данных Mysql варианты

Итак что я меняю и что вижу при этом. Для начала выведу основные параметры которые считаю спорными в настройке.

key_buffer_size = 4Gsort_buffer_size = 1G

read_rnd_buffer_size = 2G
myisam-recover = BACKUP

Разбор параметров тюнинга Mysql

Разберёмся по порядку с каждым параметром настройки и вопросами которые есть при этом. Итак по пунктам.

key_buffer = 2Gkey_buffer_size = 4G
Так и не смог я понять, различаются ли эти два параметра или первый является устаревшим значением второго.

max_connections = 500 и thread-cache-size = 500
По замерам выходило, что не более 90 одновременных подключений, так и поставил 500 с запасом. Тут следует учесть что следующий параметр thread-cache-size должен быть одинаковым числом с максимальным соединением. Поэтому там также стоит 500.

table-cache = 120000 и table-open-cache = 120000
Здесь я поставил по 120000, так как таблиц у меня достаточно много, если у вас не много сайтов, то этот параметр можно не повышать.

interactive-timeout = 360
Установил в 360, чтобы снимались запросы, которые находятся без активности 6 минут или 360 секунд.

query_cache_limit = 12Mquery_cache_size = 4Gjoin_buffer_size = 512M
Следующие три параметра настроил исходя из следующих наблюдений. Пробовал ставить query_cache_size от 2 до 6 гигабайт, в итоге оптимально показалось 4. Обработка запросов до 12 мегабайт мне вполне хватало, поэтому оставил 12. Но есть такое мнение, что большой query_cache_size на самом деле сильно грузит систему и желательно держать кеш в memcashed, на практике я не заметил особо, чтобы он забирал мощность, а вот при проверке кеша, обнаружил, что много запросов проходит через него.

sort_buffer_size = 1Gread_buffer_size = 1Gread_rnd_buffer_size = 2G
Буфера поставил побольше, так как несколько баз имеют большой размер, хотя есть риск переполнения памяти, тем не менее они настолько не забивали память.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Adblock detector