wwwoldi.ru

About Me

Dashy

Hello word I am dashy Guy

Разное

Cache size: Кэширование запросов в MYSQL

alexxlab
No Comments

query_cache_size параметр в Mysql

Эта настройка определяет количество памяти, которое Mysql выделит на кеш запросов. Кеш работает таким образом:

  • Mysql получает запрос и проверяет наличие его результата в кеше. Если данные есть, возвращает ответ.
  • Если данных нет, запрос выполняется и сохраняется в кеш.
  • Если таблица изменяется (любая из операций INSERT / UPDATE / DELETE) весь кеш таблицы чистится.

Это значит, что:

  • Эту настройку лучше использовать только тогда, когда имеет место большое количество чтений и небольшое количество записей (100 чтений на 1 запись).
  • Отключение этой настройки и использование индексов для получения высокой производительности более приемлемый вариант.

Если же вы решите использовать, сразу определяйте оба параметра (устанавливается в my.cnf): 

query_cache_limit = 1M
query_cache_size  = 8M


Не используйте значения более 100…200Мб, т.к. с увеличением этого значения, производительность Mysql деградирует.

Для проверки состояние включения этого параметра, используйте запрос: 

mysql> SHOW VARIABLES LIKE 'have_query_cache'

+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+


Для мониторинга: 

mysql> show status like '%Qcache%';


+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 165     |
| Qcache_free_memory      | 3893664 |
| Qcache_hits             | 4654886 |
| Qcache_inserts          | 352314  |
| Qcache_lowmem_prunes    | 301     |
| Qcache_not_cached       | 66691   |
| Qcache_queries_in_cache | 147     |
| Qcache_total_blocks     | 469     |
+-------------------------+---------+


Для очистки кеша: 

FLUSH QUERY CACHE

#mysql
#настройки

ID: 620

MySQL/MariaDB: тюнинг производительности #4: query_cache_size

Среди прочих данных tuning-primer сообщает что:

QUERY CACHE
Query cache is supported but not enabled
Perhaps you should set the query_cache_size

query_cache — это область памяти, выделяемая MySQL при старте, в которой хранятся запросы и их результаты типа SELECT. При получении нового запроса SELECT — MySQL сначала проверяет нет ли такого же запроса в этом кеше и, если есть, возвращает значение из памяти, иначе — выполняет обращение к базе (и диску). Обратите внимание, что select * FROM и SELECT * FROM для кеша MySQL являются двумя различными запросами.

Кеш запросов поддерживается в MySQL версий > 4.0:

MariaDB [(none)]> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+

По умолчанию query_cache отключён, что можно проверить так:

MariaDB [(none)]> SHOW VARIABLES LIKE 'query_cache_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_size | 0     |
+------------------+-------+

Qcache может иметь три состояния:

MariaDB [(none)]> SHOW VARIABLES LIKE 'query_cache_type';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | ON    |
+------------------+-------+
  • 0 (OFF) — Qcache отключён;
  • 1 (ON) — Qcache включен;
  • 2 (DEMAND) — только для запросов с явным указанием его кеширования (SELECT SQL_CACHE).

Что бы включить его — достаточно установить query_cache_size в любое положительное значение, отличное от нуля.

В настройке Qcache главную роль играют три параметра:

  • query_cache_size — размер памяти, выделяемый под кеш;
  • query_cache_type — см. выше;
  • query_cache_limit — максимальный размер возвращаемого результата запроса, который будет хранится в кеше.

Устанавливаем query_cache_size = 16 МБ, query_cache_type = 1 а query_cache_limit — оставляем по умолчанию, 1 МБ:

MariaDB [(none)]> SET GLOBAL query_cache_size=16*1024*1024;
MariaDB [(none)]> SET GLOBAL query_cache_type=1;
Query OK, 0 rows affected (0.00 sec)

Проверяем:

MariaDB [(none)]> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| have_query_cache             | YES      |
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 16777216 |
| query_cache_strip_comments   | OFF      |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+

Через какое-то время, хотя бы час, проверяем статус:

MariaDB [(none)]> SHOW STATUS LIKE 'qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 176      |
| Qcache_free_memory      | 13628632 |
| Qcache_hits             | 9935     |
| Qcache_inserts          | 4537     |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 2614     |
| Qcache_queries_in_cache | 392      |
| Qcache_total_blocks     | 1069     |
+-------------------------+----------+

В последней выборке нас интересуют:

  • Qcache_free_memory — доступное место в кеше;
  • Qcache_hits — количество запросов, отработанных из кэша;
  • Qcache_inserts — количество добавлений запросов в кэш;
  • Qcache_not_cached — количество запросов, не подлежащих кэшированию;
  • Qcache_lowmem_prunes — количество высвобождений памяти из-за наполненности кэша.

Теперь посчитаем % использования кеша:

Qcache_free_memory * 100 / query_cache_size — дадут нам % свободного места в query_cache_size:

>>> 13694488.0 * 100 / 16777216.0
81.625509262084961

81.6% свободно.

Или наоборот — % занятого места:

((query_cache_size-Qcache_free_memory)/query_cache_size)*100

>>> ((16777216.0 - 13694488.0) / 16777216.0) * 100
18.374490737915039

Т.е. занято 18.37%. Смысла в увеличении пока нет.

Что бы убедиться в этом — подсчитаем ещё такое значение как «Query Cache Hit Rate» по формуле:

((Qcache_hits/(Qcache_hits+Qcache_inserts+Qcache_not_cached))*100)

Получаем такое значение:

>>> (9935.0 / (9935.0 + 4537.0 + 2614.0)) * 100
58.147020952826878

Т.е. — MySQL обращался к кешу 17086 раз (Qcache_hits+Qcache_inserts+Qcache_not_cached), и 9935 запросов были выданы из кеша, следовательно Query Cache Hit Rate = 58%.

Это не самое хорошее значение, однако — пока оно выше 50% — query_cache_size можно не менять. Если значение менее 50% — можно увеличить размер кеша, а если же значение ниже 10-20% — то, возможно, Qcache лучше не использовать вообще.

Ещё два важных значения — это соотношение значений Qcache_hits/Qcache_inserts и Qcache_inserts/Qcache_lowmem_prunes.

Из примера выше — для Qcache_hits/Qcache_inserts получаем такое значение:

>>> 11386.0 / 5697.0
1.9985957521502544

Т.е. — в среднем каждый добавленный в кеш результат был выдан клиентам 2 раза. Чем ближе значение к соотношению 1:1 (или даже меньше, напрмиер — 0.5:1) — тем меньше эффективность работы кеша.

Второе значение Qcache_inserts/Qcache_lowmem_prunes — из примера выше получить не выйдет (т.к. Qcache_lowmem_prunes = 0), поэтому — возьмём значение с другой базы другого сервера:

mysql> SHOW STATUS LIKE 'qcache%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
...
| Qcache_inserts          | 2773956 |
| Qcache_lowmem_prunes    | 1417292 |

…

Результат:

>>> 2773956.0 / 1417292.0
1.957222647132701

Т.е. — фактически, каждый второй запрос и результат, добавленные в кеш, были удалены.

Можно посчитать процентное соотношение:

>>> 1417292.0 * 100 / 2773956.0
51.092807528309748

Итого — процент удалений кеша равен 51%, тогда как более-менее оптимальным считается значение 10-20% удалений.

Эти же данные можно получить в выводе утилиты mysqlreport:

$ mysqlreport --user root --password password | less
...
__ Query Cache _________________________________________________________
Memory usage    3.48M of   8.00M  %Used:  43.46
Block Fragmnt   4.13%
Hits            1.35M     0.8/s
Inserts         2.96M     1.8/s
Insrt:Prune    1.95:1     0.9/s
Hit:Insert     0.46:1
...

По теме:

http://www.techinfobest.com

http://haydenjames.io

http://goldapplesoftware.ca

Настройка работы MySQL Query Cache

Использование кэширования запросов, является одним из ключевых факторов влияющих на производительность работы MySQL. Функционал Query Cache открывает дополнительные возможности для оптимизации базы данных и позволяет снизить время обработки запросов в несколько раз. Наилучшую эффективность работы Query Cache показывает на веб-серверах, у которых таблицы не обновляются слишком часто и присутствует много идентичных запросов.

Для подходящего запроса типа SELECT, MySQL автоматически сохраняет текст запроса и данные выборки в кэше. Все идентичные запросы в дальнейшем, будут обрабатываться в обход БД с помощью функции MySQL Query Cache. Таким образом, кэшированные запросы не выполняется вовсе.

Для работы Query Cache в значении переменной query_cache_type должно быть установлено ON или DEMAND, а query_cache_size быть отличной от нуля.

В противном случае, необходимо добавить соответствующие настройки в секцию [mysqld] конфигурационного файла MySQL:

nano /etc/mysql/my.cnf

[mysqld]
query_cache_type        = ON
query_cache_limit       = 1M
query_cache_size        = 16M

За настройку работы функции Query Cache отвечают системные переменные начинающиеся с 'query_cache_'.

mysql> SHOW VARIABLES LIKE 'query_cache_%';

+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 16777216 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
5 rows in set (0.00 sec)

query_cache_limit — размер максимальной выборки, которая будет записана в кэш. В качестве значения необходимо указать максимальный размер самого тяжелого запроса, но не стоит чрезмерно завышать значение данного параметра.
query_cache_min_res_unit — минимальный размер выделяемого блока памяти для хранения результатов кэшированного запроса. Для записи данных в кэш MySQL разбивает выборку на отдельные блоки с минимальным размером query_cache_min_res_unit. Последний такой блок обрезается до размера данных, а оставшаяся память освобождается. Для записи данных в кэш, MySQL по мере необходимости выделяет блоки размером query_cache_min_res_unit. В качестве значения необходимо указать среднее значение размера выборки от всех запросов. Примерное значение query_cache_min_res_unit можно вычислить по формуле query_cache_min_res_unit = (query_cache_size – Qcache_free_memory) / Qcache_queries_in_cache. Слишком большое значение будет способствовать фрагментации кэша, слишком маленькое может стать причиной снижения производительности.
query_cache_size — размер памяти выделяемый для хранения кэша запросов. Значение равное 0 отключает работу MySQL Query Cache. Устанавливаем значение исходя из количества свободной оперативной памяти в системе. Для выбора оптимального значения, в идеале переменная Qcache_lowmem_prunes должна равняться нулю. В противном случае, рекомендуется чтобы в процессе работы MySQL это значение увеличивалось незначительно.
query_cache_type — параметр отвечающий за работу кэша. Может принимать значения: ON, DEMAND и OFF. Опция включает или отключает работу MySQL Query Cache, если значение query_cache_type установлено равным DEMAND, MySQL будет кэшировать только запросы с директивой SQL_CACHE.
query_cache_wlock_invalidate — определяет будут ли данные браться из кэша, если таблица, к которым они относятся заблокирована на чтение. Если значение параметра query_cache_wlock_invalidate принимает значение OFF, то будет доступно получение данных заблокированной таблицы из Query Cache.

Для мониторинга MySQL Query Cache используется команда:

mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';

+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 158      |
| Qcache_free_memory      | 16420704 |
| Qcache_hits             | 143791   |
| Qcache_inserts          | 21851    |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 12506    |
| Qcache_queries_in_cache | 215      |
| Qcache_total_blocks     | 598      |
+-------------------------+----------+
8 rows in set (0.00 sec)

Qcache_free_blocks — количество свободных блоков в кэше. Чем больше незадействованных блоков, тем больше степень фрагментации кэша. Если результат большинства запросов имеет небольшой объем данных выборки, необходимо уменьшить значение параметра query_cache_min_res_unit.
Qcache_total_blocks — количество занятых блоков.
Qcache_free_memory — объем свободной памяти, отведенной под кэш.
Qcache_hits — количество запросов отработанных из кэша.
Qcache_inserts — количество запросов записанных в кэш.
Qcache_lowmem_prunes — количество запросов, которые были удалены из-за переполнения кэша.
Qcache_not_cached — количество запросов не подлежащих кэшированию.
Qcache_queries_in_cache — количество запросов находящихся в кэше.

Кратко механизм работы Query Cache выглядит следующим образом. Под кэширование запросов MySQL выделяет в памяти область размером query_cache_size. Для записи результатов запроса сервер создает в кэше свободный блок размером query_cache_min_res_unit. После заполнения блока, сервер создает новый пустой блок и так до тех пор, пока все данные выборки не будут записаны в кэш. После чего свободная область памяти последнего блока выделяется в новый свободный блок. В случае если размер выборки превышает установленное значение query_cache_limit, то запись прекращается, а занятое память освобождается.

Фрагментация кэша возникает при удалении выборки из кэша, когда для записи результатов новых запросов количества освободившихся блоков недостаточно. Для того что бы определить степень фрагментации, необходимо обратить внимание на значение переменной Qcache_free_blocks. В идеале значение должно быть равно единице, в случае фрагментации — Qcache_total_blocks / 2. Так же можно определить, что ваш кэш запросов сильно фрагментируется, если значение Qcache_lowmem_prunes постоянно возрастает при том, что значение Qcache_free_memory далеко от нуля.

Для дефрагментации кэша используется команда:

mysql> FLUSH QUERY CACHE;

Для оценки эффективности работы кэша используется формула Qcache_hits / (Qcache_hits + Com_select).

Оптимизация связки Nginx, Apache, PHP, MySql / Хабр

Неожиданно поступила задача разобраться почему определенный сайт не работает столь быстро сколь хочется. В основе его CakePHP, в связке с Apache и MySQL. В статье описание процесса поиска узких мест и приведение в порядок на столько, на сколько это возможно.

Название сайта светить не буду — думаю, программисты сами узнают. Скажу лишь, что это приложение для социальной сети нагрузкой 70-150 тысяч посетителей в обычное время. Все усложняется тем, что периодически производится рекламная рассылка, которая привлекает около 200-300 тысяч посетителей за пару часов.

Итак, под катом описание всей борьбы на протяжении 4 дней.


Данная статья нацелена на 2 группы людей. Первая — это конечно же мы, админы, которым приходится разгребать подобное безобразие. Иногда вытаскивая практически за уши умирающий сервер. Вторая часть аудитории, которые я надеюсь тоже обязательно прочтут весь материал — это программисты. Друзья, забегая вперед, скажу: не было бы такого безобразия, если бы вы правильно проектировали свои проекты, многих казусов можно было бы избежать. Особенно зная на какую аудиторию вы пишите свой проект.

В моем распоряжении оказался сервер EX10, находящийся на площадке hetzner. Для тех кто не в курсе — это 64 Гб ОЗУ и 6-ядерный процессор. Назовем это ядром системы. Есть еще 2 сервера, один со статикой, другой с бекэнд базой. Небольшое приложение, InnoDB база данных на 500Мб.

С постоянной нагрузкой, как позже выяснилось, 70-100 онлайн сессий, ситуация следующая: загрузка CPU по 100% на каждое ядро. В топе, понятное дело, MySQL и Apache дерутся за ресурсы системы.

Nginx

Первой попыткой было снизить нагрузку сервера за счет кеширования выдачи Apache с целью снять с него выдачу статики.

Установил в очень простой конфигурации: он должен был все все файлы по маске пробовать забирать из определенной папки, если в ней файла нет — забирать его с проксируемого сервера, складывать в эту папку и выдавать клиенту.

http {
proxy_cache_path  /var/tmp/nginx_cache/  levels=1:2   keys_zone=ok:100m inactive=1d max_size=1024m;
server {
        location ~*  \.(js|JPG|jpg|png|jpeg|gif|zip|tgz|gz|rar|doc|xls|exe|pdf|ppt|txt|wav|bmp|rtf)$ {
                expires 1y;
                open_file_cache_errors  off;
                error_page 404 = @fetch;
                root  /var/tmp/_fetch_ok;
                }

        location @fetch {
                proxy_store_access      user:rw  group:rw  all:r;
                proxy_store on;
                proxy_pass http://127.0.0.1:80;
                proxy_temp_path /var/tmp/_fetch_ok_temp;
                root  /var/tmp/_fetch_ok;
                }


       location / {
                proxy_cache ok;
                proxy_pass   http://127.0.0.1;
                proxy_cache_valid any 10m;
                proxy_buffer_size 8k;
               }
}
}

Конфиг приведен не полностью, а только нужными для данного примера блоками.

К сожалению, не очень это привело к каким-то результатам.

Плюсов было всего 3:

  • Apache перестал выдавать картинки, то есть стал чуть менее загружен

  • Apache перестал напрямую общаться с внешним миром, следовательно у него можно было выключить keep-alive и уменьшить количество детей.

  • Нашлось первое узкое место — абсолютно всеми запросами на сайт управляет PHP скрипт на который .htaccess переадресует любой запрос. Включая, вы не поверите, всю статику, и даже css.

MySQL

Тут самое сложное, поскольку на данный момент у меня нет большого опыта в SQL запросах и оптимизации.
Начал с чтения документации к MySQL.

Так как имеем на руках InnoDB — я взял для исходной позиции конфигурационный файл из стандартной поставки my-innodb-heavy-4G.cnf

Ниже опишу параметры конфигурации, на которые следует обращать внимание на высоконагруженных проектах.

back_log = 5000
max_connections = 1600

Первый параметр отвечает за количество соединений, которые могут находится в очереди до того момента, как сервер перестанет отвечать на новые запросы. Второй — сколько подключений может быть принято сервером.

У меня эти значения достаточно большие, так как конкурентных сессий в среднем выходит до 1300. Ставить больше чем нужно — не стоит, так как каждое соединение может потребовать некоторого количества ОЗУ. Об этом позже.

max_connect_errors = 50

Тут просто — количество ошибок, которые может сделать клиент до того как получит дисконнект. Пришлось увеличить, в виду того что проект в стадии разработки и шансов некорректных запросов много.

table_cache = 2048

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

Узнать надо ли его менять можно по переменной
SHOW GLOBAL STATUS LIKE 'Opened_tables';

Она не должна быть как можно меньше.

Тут хорошо написано: http://www.mysql.ru/docs/man/Table_cache.html

max_allowed_packet = 16M

Максимальный размер пакета. Если не пользуемся большими BLOB, изменять не имеет смысла.

binlog_cache_size = 1M

Размер кеша бинарного лога, для транзакции. В официальной документации рекомендуют увеличивать если у нас большие транзакции.
dev.mysql.com/doc/refman/5.5/en/replication-options-binary-log.html#sysvar_binlog_cache_size

max_heap_table_size = 64M
tmp_table_size = 64M

Насколько я понимаю, учитывается меньший из них. Параметр отвечает за максимальный размер временной таблицы, умещающейся в памяти. Если таблица его достигает она кладется на диск. Следовательно необходимо стараться что бы таблиц на диске создавалось как можно меньше. Посмотреть какое отношение временных таблиц к таблицам на диске на данный момент можно запросив
show status like '%tmp%tables';
www.mysqlperformanceblog.com/2007/01/19/tmp_table_size-and-max_heap_table_size

sort_buffer_size = 8M

Что бы не обмануть никого, не возьмусь переводить. Уточню лишь, что в документации советуют смотреть на этот параметр только если

show status like ‘%Sort_merge_passes%’; больше нуля
dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_sort_buffer_size

join_buffer_size = 2M

Насколько понимаю, максимальный размер буфера, рассчитанного на операции не использующие индекс. Пока не трогал.
dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_join_buffer_size

thread_cache_size = 4096

Максимальное количество треадов, которые остаются для повторного использования после выполнения запроса. Полезно держать достаточным для того что бы MySQL как можно меньше делал новых треадов и использовал старые. Понять эффективность данного параметра можно по отношению параметров Threads_created / Connections;
dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_thread_cache_size

query_cache_size = 256M
query_cache_limit = 8M

Я думаю тут лучше чем мой соратник, автор этого перевода habrahabr.ru/post/41166 никто не скажет.

Это наверное наиболее важный параметр, так что лучше перечитать.

thread_stack = 192K

Не возьмусь описывать назначение данного параметра, обращу лишь внимание на то что он тоже влияет на количество потребляемой ОЗУ, так как тоже выделяется на каждое соединение. Следовательно опять умножайте на max connections

long_query_time = 2
log_long_format
log-queries-not-using-indexes

У сервера MySQL есть очень удобный инструмент для оценки производительности БД. Это лог файл длинных запросов. По моему опыту это чаще всего неэффективные запросы или запросы неиспользующие индекс.

Советую с этим лог файлом идти к программерам.

key_buffer_size = 1G

Параметр отвечает за кеширование индексов в памяти, для оптимизации данного значения смотрите на Key_read_requests, Key_reads. Второй параметр отвечает за количество чтений с диска а не их буфера.
mysqltips.blogspot.com/2007/03/key-buffer.html

read_buffer_size = 1M
boombick.org/blog/posts/3 — После прочтения этого текста, не рискну что либо добавлять, так как не буду уверен в своей правоте.

read_rnd_buffer_size = 24M

Параметр влияет на скорость операций сортировки. К сожалению не нашел как оценить его эффективность.
dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_read_rnd_buffer_size
www.mysqlperformanceblog.com/2007/07/24/what-exactly-is-read_rnd_buffer_size

myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_max_extra_sort_file_size = 10G

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

sync_binlog = 0

В нашем случае означает не синхронизировать бинарных лог на диск через системные функции. Если параметр больше нуля, то сервер будет синхронизировать данные каждые n запросов.
dev.mysql.com/doc/refman/5.5/en/replication-options-binary-log.html#sysvar_sync_binlog

innodb_buffer_pool_size = 4G

Увеличение этого параметра снижает количество дисковых операций. К сожалению тоже не нашел как его лучше замерить. Поскольку база небольшая решил его сильно не увеличивать. Где-то встречал совет, в случае большой БД увеличивать этот параметр до 70% ОЗУ.
dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size

innodb_log_buffer_size = 32M

Если верить описанию, снижает дисковые операции при тяжелых транзакциях.
dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_log_buffer_size

innodb_log_file_size = 1024M

Если верить документации, то увеличение лог файла уменьшает загруженность IO операций диска, но увеличивает время восстановления в случае сбоев.
dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_log_file_size

innodb_flush_log_at_trx_commit = 0

В значении 0, сброс буферов происходит раз в минутусекунду, а не после каждого инсерта.
dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit

innodb_thread_concurrency = 14

Рекомендуют ставить чуть больше чем количество ядер.

innodb_sync_spin_loops=10

Насколько я понял, влияет на количество попыток доступа к заблокированным данным. Увеличивая данное значение мы можем потерять процессорное время, а уменьшая — надежность записи в БД.
www.mysqlperformanceblog.com/2006/09/07/internals-of-innodb-mutexes
dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_sync_spin_loops

БД и оперативная память

Есть прекрасный скрипт на perl который дает базовое понимание того что надо поменять в БД. mysqltuner.pl/mysqltuner.pl
Очень часто данный скрипт ругается на максимальное потребление памяти сервером mysql.
Если посмотреть в исходник — вот как эта программа считает использование памяти:

per_thread_buffers = read_buffer_size +read_rnd_buffer_size + sort_buffer_size + thread_stack +join_buffer_size;

total_per_thread_buffers = per_thread_buffers * max_connections;

server_buffers = key_buffer_size +max_tmp_table_size;

server_buffers +=innodb_buffer_pool_size;

server_buffers +=innodb_additional_mem_pool_size;

server_buffers +=innodb_log_buffer_size;

server_buffers +=query_cache_size;

total_possible_used_memory =server_buffers + total_per_thread_buffers;

Мне было полезно понять где я не правильно указал значения.

Кстати, сразу кидаться занижать параметры, если скрипт ругается на большое потребление ОЗУ базой, не стоит. Так как многие отмечают, что это лишь теоретический показатель и БД может никогда не попытаться забрать себе столько памяти.

Оптимизация структуры БД.

Когда мы сделали все возможные настройки и все равно не получили хорошего результата — пора обратиться к slow-log файлу.
В стандартной комплектации к mysql есть приложение mysqldumpslow.
Запустив

mysqldumpslow -s c отсортированный по количеству вхождений список запросов к базе данных, которые были слишком долгими или не использовали индексы. Обычно добавление правильных индексов исправляет обе проблемы.

В большинстве случаев, когда вы видите в выводе этой программы большое количество долгих запросов(переменная count), копируете кусок этого запроса и ищете в тексте лог файла пример такого запроса.

Далее заходите в клиент БД и выполняете этот запрос, добавив вначале слово explain.

Про это так же можно прочитать подробнее вот тут:
habrahabr.ru/post/31072

Так можно увидеть использует ли запрос индекс или нет.

Если таблице не хватает индексов их можно смело добавлять, хотя перебарщивать тоже не стоит. Индексы нужны тем столбцам которые используются после where и order. Начинайте с уникальных индексов на каждый стоблец. Иначе индекс может не сработать.

Вот тут можно узнать более подробно как эти индексы работают:
dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

Скажу честно, после оптимизации примерно 5 ключевых запросов сервер смог обрабатывать 500-700 подключений вместо 50, а время выдачи php-страницы сократилось до 1с вместо 8с. При максимальной нагрузке время выдачи страницы составило 5с вместо 50с. (Имеются в виду замеры производительности с помощью Apache Benchmark c примерно 1000 потоков)

Еще немного об nginx.

После оптимизации заметил, что при больших нагрузках откидывает запросы больше определенного количества уже сам nginx, а не apache. При этом память и CPU не загружены.
Стал разбираться. Увидел в логах, что сервер nginx пытается открыть файлов больше, чем ему положено.
В ОС Suse, с которым мне пришлось столкнуться, за это ограничение отвечает файл
/etc/security/limits.conf
Дописал я туда вот такие строчки:

nginx		soft 	nofile		300000
nginx		hard	nofile		300000

Рестарта сервера не понадобилось.

Apache2

Сильно изменять конфигурацию я пока не стал. Единственное что сделал — так это выключил keep-alive. Что бы апач мог спокойно выдать ответ и заняться следующим запросом в тот момент, когда nginx все еще отдает клиенту страницу по медленному каналу.

eAccelerator

Не забываем о том что у данного оптимизатора тоже есть некоторое количество параметров которые можно поменять.

Вот что изменил я:
eaccelerator.shm_size = «2096»

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

eaccelerator.shm_only = «1» — использовать только ОЗУ и не использовать диск, в борьбе за io на софт рейде из 2 сата дисков решил сделать так.

Вот еще что будет полезно почитать:

habrahabr.ru/post/41166
habrahabr.ru/post/108418
dev.mysql.com/doc/refman/5.5/en/server-system-variables.html

Отдельное спасибо моему другу, который помог вычитать статью и исправить целую гору грамматических и стилистических ошибок.

Вместо послесловия

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

UPD. Резюме по комментам

Спасибо за столь большой интерес. Не ожидал.

Хорошая ссылка, рекомендую к прочтению: www.percona.com/files/presentations/percona-live/dc-2012/PLDC2012-optimizing-mysql-configuration.pdf

Спасибо Albertum

По поводу систем кеширования php:

да действительно eAccelerator не единственный вариант.

Есть так же APC, и да действительно его собираются встроить в PHP.

Что лучше судить не возьмусь, так как не делал достаточного количества тестов.

Альтернативы MySQL тоже присутствуют и их много.

Ключевые конечно:

mAriadb

percona

Сам я лично выбрал перкону и пока доволен.

По поводу того что это не конечный светлый результат и надо двигаться дальше — те кто внимательно читал увидит что это лишь латание дыр.

Что нужно настроить в mySQL сразу после установки? / Хабр

Вольный перевод довольно старой статьи с MySQL Performance Blog о том, что лучше сразу же настроить после установки базовой версии mySQL.

Удивительно, сколько народу устанавливает mySQL на свои сервера и оставляют его с настройками по умолчанию.

Несмотря на то, что в mySQL существует довольно много настроек, которые Вы можете изменить, есть набор действительно очень важных характеристик, которые обязательно нужно оптимизировать под собственный сервер. Обычно после такой небольшой настройки производительность сервера заметно увеличивается.

  • key_buffer_size — крайне важная настройка при использовании MyISAM-таблиц. Установите её равной около 30-40% от доступной оперативной памяти, если используете только MyISAM. Правильный размер зависит от размеров индексов, данных и нагрузки на сервер — помните, что MyISAM использует кэш операционной системы (ОС), чтобы хранить данные, поэтому нужно оставить достаточно места в ОЗУ под данные, и данные могут занимать значительно больше места, чем индексы. Однако обязательно проверьте, чтобы всё место, отводимое директивой key_buffer_size под кэш, постоянно использовалось — нередко можно видеть ситуации, когда под кэш индексов отведено 4 ГБ, хотя общий размер всех .MYI-файлов не превышает 1 ГБ. Делать так совершенно бесполезно, Вы только потратите ресурсы. Если у Вас практически нет MyISAM-таблиц, то key_buffer_size следует выставить около 16-32 МБ — они будут использоваться для хранения в памяти индексов временных таблиц, создаваемых на диске.

  • innodb_buffer_pool_size — не менее важная настройка, но уже для InnoDB, обязательно обратите на неё внимание, если собираетесь использовать в основном InnoDB-таблицы, т.к. они значительно более чувствительны к размеру буфера, чем MyISAM-таблицы. MyISAM-таблицы в принципе могут неплохо работать даже с большим количеством данных и при стандартном значении key_buffer_size, однако mySQL может сильно «тормозить» при неверном значении innodb_buffer_pool_size. InnoDB использует свой буфер для хранения и индексов, и данных, поэтому нет необходимости оставлять память под кэш ОС — устанавливайте innodb_buffer_pool_size в 70-80% доступной оперативной памяти (если, конечно, используются только InnoDB-таблицы). Относительно максимального размера данной опции — аналогично key_buffer_size — не стоит увлекаться, нужно найти оптимальный размер, найдите лучшее применение доступной памяти.

  • innodb_additional_mem_pool_size — данная опция практически никак не влияет на производительность mySQL, однако рекомендую оставлять для InnoDB около 20 МБ (или чуть больше) под различные внутренние нужды.

  • innodb_log_file_size — крайне важная настройка в условиях баз данных с частыми операциями записи в таблицы, в особенности при больших объёмах. Большие размеры увеличивают быстродействие, однако будьте осторожны — увеличится и время восстановления данных. Я обычно выставляю значение около 64-512 МБ в зависимости от размера сервера.

  • innodb_log_buffer_size — стандартное значение данной опции вполне подойдёт для большинства систем со средним количеством операций записи и небольшими транзакциями. Если же в Вашей системе бывают всплески активности, или Вы активно работаете с BLOB-данными, то рекомендую немного увеличить значение innodb_log_buffer_size. Однако не переусердствуйте — слишком большое значение будет пустой тратой памяти: буфер сбрасывается каждую секунду, поэтому Вам не понадобится больше места, чем требуется в течение этой секунды. Рекомендуемое значение — около 8-16 МБ, а для небольших баз — и того меньше.

  • innodb_flush_log_at_trx_commit — жалуетесь, что InnoDB работает в 100 раз медленнее MyISAM? Вероятно, Вы забыли про настройку innodb_flush_log_at_trx_commit. Значение по умолчанию «1» означает, что каждая UPDATE-транзакция (или аналогичная команда вне транзакции) должна сбрасывать буфер на диск, что достаточно ресурсоёмко. Большинство приложений, в особенности ранее использовавшие таблицы MyISAM, будут хорошо работать со значением «2» (т.е. «не сбрасывать буфер на диск, только в кэш ОС»). Лог, однако, всё равно будет сбрасываться на диск каждые 1-2 секунды, поэтому в случае аварии Вы потеряете максимум 1-2 секунды обновлений. Значение «0» повысит производительность, но Вы рискуете потерять данные даже при аварийной остановке mySQL-сервера, в то время как при установке значение innodb_flush_log_at_trx_commit в «2» Вы потеряете данные только при аварии всей операционной системы.

  • table_cache — открытие таблиц может быть весьма ресурсоёмко. К примеру, MyISAM-таблицы помечают заголовки .MYI файлов как «используемые в текущий момент». Обычно не рекомендуется открывать таблицы слишком часто, поэтому лучше, чтобы кэш был достаточных размеров, чтобы держать все Ваши таблицы открытыми. Для этого используется некоторое количество ресурсов ОС и оперативной памяти, однако это обычно не является существенной проблемой для современных серверов. Если у Вас несколько сотен таблиц, то стартовым значением для опции table_cache может быть«1024» (помните, что каждое соединение требует свой собственный дескриптор). Если у Вас ещё больше таблиц или очень много соединений — увеличьте значение параметра. Я видел mySQL сервера со значением table_cache равной 100 000.

  • thread_cache — создание/уничтожение потоков также является ресурсоёмкой операцией, которая происходит при каждой установке соединения и каждом разрыве соединения. Я обычно выставляю эту опцию равную 16. Если у Вашего приложения могут быть скачки количество конкурентных соединений и по переменной Threads_Created виден быстрый рост количества потоков, то стоит увеличить значение thread_cache. Цель — не допускать создания новых потоков в условиях нормального функционирования сервера.

  • query_cache_size — если Ваше приложение много и часто читает данные, и при этом у Вас нет кэша на уровне приложения, эта опция может очень помочь. Не ставьте здесь слишком большое значение, так как обслуживание большого кэша запросов будет само по себе затратным. Рекомендуемое значение — от 32 до 512 МБ. Не забудьте проверить, насколько хорошо используется кэш запросов — в некоторых условиях (при небольшом количестве хитов в кэше, т.е. когда практически не выбираются одинаковые данные) использование большого кэша может ухудшить производительность.

Как Вы можете видеть, это — глобальные настройки. Эти переменные зависят от «железа» сервера и используемых движков mySQL, в то время как сессионные переменные обычно настраиваются специально под конкретные задачи. Если Вы в основном используете простые запросы, то нет никакой необходимости увеличивать значение sort_buffer_size, даже если у Вас есть лишние 64 ГБ оперативной памяти. Более того, большие значения кэшей могут только ухудшить производительность сервера. Сессионные переменные лучше оставить на потом, для тонкой настройки сервера.

PS: инсталляция mySQL идёт с несколькими предустановленными файлами my.cnf, рассчитанными под разную нагрузку. Если Вам некогда настраивать сервер вручную, то обычно лучше использовать их, чем стандартный конфигурационный файл, выбрав тот, что больше подойдёт под нагрузку Вашего сервера.

Настройка my.cnf для стабильной работы Mysql | Клуб разработки сайтов

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

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

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

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

[mysqld]
#open_files_limit = 2000

local-infile=0
innodb_file_per_table = 1

pid-file = /var/run/mysqld/mysqld.pid
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

key_buffer = 2G

key_buffer = 2G

key_cache_division_limit = 70

thread_stack = 192K

tmp_table_size = 2G

max_heap_table_size = 2G

key_buffer_size = 4G

sort_buffer_size    = 1G

read_buffer_size    = 1G

read_rnd_buffer_size = 2G

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

thread-cache-size = 500

thread-cache-size = 500

interactive-timeout = 360

query_cache_limit = 12M

query_cache_size    = 4G

join_buffer_size = 512M

#log_slow_queries = /var/log/mysql/mysql-slow.log

#log_slow_queries = /var/log/mysql/mysql-slow.log

#long_query_time = 75#log-queries-not-using-indexes

expire_logs_days = 10

expire_logs_days = 10

max_binlog_size         = 100M

innodb_buffer_pool_size = 4G

innodb_buffer_pool_instances = 4

innodb_flush_log_at_trx_commit = 2

innodb_flush_method = O_DIRECT

symbolic-links=0

bind-address = 127.0.0.1

[mysqld_safe]

log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

!includedir /etc/my.cnf.d

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

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

key_buffer = 2G

key_cache_division_limit = 70

thread_stack = 192K

tmp_table_size = 2G

max_heap_table_size = 2G

key_buffer_size = 4Gsort_buffer_size    = 1G

read_buffer_size    = 1G

read_rnd_buffer_size = 2G
myisam-recover         = BACKUP

max_connections        = 500

table-cache = 120000

table-open-cache = 120000

thread-cache-size = 500

interactive-timeout = 360

query_cache_limit = 12M

query_cache_size    = 4G

join_buffer_size = 512M

Разбор параметров тюнинга 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
Буфера поставил побольше, так как несколько баз имеют большой размер, хотя есть риск переполнения памяти, тем не менее они настолько не забивали память.

Postgres Pro Standard : Документация: 9.5: 18.7. Планирование запросов : Компания Postgres Professional

Задаёт приблизительную стоимость чтения одной произвольной страницы с диска. Значение по умолчанию равно 4.0. Это значение можно переопределить для таблиц и индексов в определённом табличном пространстве, установив одноимённый параметр табличного пространства (см. ALTER TABLESPACE).

При уменьшении этого значения по отношению к seq_page_cost система начинает предпочитать сканирование по индексу; при увеличении такое сканирование становится более дорогостоящим. Оба эти значения также можно увеличить или уменьшить одновременно, чтобы изменить стоимость операций ввода/вывода по отношению к стоимости процессорных операций, которая определяется следующими параметрами.

Произвольный доступ к механическому дисковому хранилищу обычно гораздо дороже последовательного доступа, более чем в четыре раза. Однако по умолчанию выбран небольшой коэффициент (4.0), в предположении, что большой объём данных при произвольном доступе, например, при чтении индекса, окажется в кеше. Таким образом, можно считать, что значение по умолчанию моделирует ситуацию, когда произвольный доступ в 40 раз медленнее последовательного, но 90% операций произвольного чтения удовлетворяются из кеша.

Если вы считаете, что для вашей рабочей нагрузки процент попаданий не достигает 90%, вы можете увеличить параметр random_page_cost, чтобы он больше соответствовал реальной стоимости произвольного чтения. И напротив, если ваши данные могут полностью поместиться в кеше, например, когда размер базы меньше общего объёма памяти сервера, может иметь смысл уменьшить random_page_cost. С хранилищем, у которого стоимость произвольного чтения не намного выше последовательного, как например, у твердотельных накопителей, так же лучше выбрать меньшее значение random_page_cost.

Подсказка

Хотя система позволяет сделать random_page_cost меньше, чем seq_page_cost, это лишено физического смысла. Однако сделать их равными имеет смысл, если база данных полностью кешируется в ОЗУ, так как в этом случае с обращением к страницам в произвольном порядке не связаны никакие дополнительные издержки. Кроме того, для сильно загруженной базы данных оба этих параметра следует понизить по отношению к стоимости процессорных операций, так как стоимость выборки страницы, уже находящейся в ОЗУ, оказывается намного меньше, чем обычно.

Определяет представление планировщика об эффективном размере дискового кеша, доступном для одного запроса. Это представление влияет на оценку стоимости использования индекса; чем выше это значение, тем больше вероятность, что будет применяться сканирование по индексу, чем ниже, тем более вероятно, что будет выбрано последовательное сканирование. При установке этого параметра следует учитывать и объём разделяемых буферов Postgres Pro, и процент дискового кеша ядра, который будут занимать файлы данных Postgres Pro, хотя некоторые данные могут оказаться и там, и там. Кроме того, следует принять во внимание ожидаемое число параллельных запросов к разным таблицам, так как общий размер будет разделяться между ними. Этот параметр не влияет на размер разделяемой памяти, выделяемой Postgres Pro, и не задаёт размер резервируемого в ядре дискового кеша; он используется только в качестве ориентировочной оценки. При этом система не учитывает, что данные могут оставаться в дисковом кеше от запроса к запросу. Значение этого параметра по умолчанию — 4 гигабайта (4GB).

Кэширование

— ограничение размера кеш-памяти

Переполнение стека

  1. Около

  2. Продукты

  3. Для команд

  1. Переполнение стека
    Общественные вопросы и ответы

  2. Переполнение стека для команд
    Где разработчики и технологи делятся частными знаниями с коллегами

  3. Вакансии
    Программирование и связанные с ним технические возможности карьерного роста

  4. Талант
    Нанимайте технических специалистов и создавайте свой бренд работодателя

  5. Реклама
    Обратитесь к разработчикам и технологам со всего мира

  6. О компании

.

Параметр размера кэша LruCache Android

Переполнение стека

  1. Около

  2. Продукты

  3. Для команд

  1. Переполнение стека
    Общественные вопросы и ответы

  2. Переполнение стека для команд
    Где разработчики и технологи делятся частными знаниями с коллегами

  3. Вакансии
    Программирование и связанные с ним технические возможности карьерного роста

  4. Талант
    Нанимайте технических специалистов и создавайте свой бренд работодателя

  5. Реклама
    Обратитесь к разработчикам и технологам со всего мира

  6. О компании

.

c — Выравнивание по строке кэша и знание размера строки кэша

Переполнение стека

  1. Около

  2. Продукты

  3. Для команд

  1. Переполнение стека
    Общественные вопросы и ответы

  2. Переполнение стека для команд
    Где разработчики и технологи делятся частными знаниями с коллегами

  3. Вакансии
    Программирование и связанные с ним технические возможности карьерного роста

  4. Талант
    Нанимайте технических специалистов и создавайте свой бренд работодателя

  5. Реклама
    Обратитесь к разработчикам и технологам со всего мира

  6. О компании

Загрузка…

  2. Авторизоваться
    зарегистрироваться

  3. текущее сообщество

.

No related posts.

Share :

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

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