При оптимизации Mysql со стороны параметров сервера в первую очередь необходимо отталкиваться от того что доступ к данным, находящимся в памяти, осуществляется на порядок быстрее, чем к данным на жестком диске. Так же необходимо хранить данные находящиеся в оперативной памяти как можно дольше, тем самым мы снижаем кол-во обращений к жестким дискам сервера.
Кэш-память таблиц
Используется для хранения информации об открытых таблицах. Ее объем можно регулировать с помощью параметра table_cache в конфиг.файле Mysql сервера (начиная с MySQL версии 5.1, данный параметр называется table_open_cache).
Table_cache это максимальное кол-во одновременно открытых файлов (таблиц), которое может себе позволить Mysql сервер (так же надо помнить о максимальном кол-ве открытых файловых дескрипторов накладываемых со стороны ОС). Если мы выставили параметр max_connections в 150, то желательно и table_cache подтянуть до такого же значения в 150.
Эффективность кэш-памяти таблиц можно наблюдать через opened_tables в show status.
mysql> show status like 'opened_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 0 |
+---------------+-------+
1 row in set (0.00 sec)
Кэш-память таблиц
Используется для хранения информации об открытых таблицах. Ее объем можно регулировать с помощью параметра table_cache в конфиг.файле Mysql сервера (начиная с MySQL версии 5.1, данный параметр называется table_open_cache).
Table_cache это максимальное кол-во одновременно открытых файлов (таблиц), которое может себе позволить Mysql сервер (так же надо помнить о максимальном кол-ве открытых файловых дескрипторов накладываемых со стороны ОС). Если мы выставили параметр max_connections в 150, то желательно и table_cache подтянуть до такого же значения в 150.
Эффективность кэш-памяти таблиц можно наблюдать через opened_tables в show status.
mysql> show status like 'opened_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 0 |
+---------------+-------+
1 row in set (0.00 sec)
или так
tsyrenov:~# mysqladmin status -p
Enter password:
Uptime: 480602 Threads: 5 Questions: 107079 Slow queries: 0 Opens: 118 Flush tables: 1 Open tables: 4 Queries per second avg: 0.223
Если значение opened_tables небольшое в процессе работы Mysql сервера, то как правило это хороший признак, если наоборот, то требуется и дальше подбирать параметр table_cache (как правило просто в сторону увеличения). Вообщем увеличение размера кэш-памяти таблиц позволяет уменьшить кол-во операций открытия/закрытия таблиц, что положительно сказывается на производительности.
Неиспользуемые таблицы закрываются и удаляются из кэша в том случае, когда вся кэш-память переполнена и сервер пытается открыть новую таблицу, отсутствующую в этом кэше. Удаляется таблица не использованная дольше всех остальных таблиц.
Вручную очистить кэш можно с помощью команд mysqladmin refresh или flush_tables;
Ключевой буфер
Используется для хранения индексов. В работе MyISAM таблиц используется для выборок и сортировок, осуществляемых с применением индекса и для операций создания и модификации индекса. Регулируется с помощью параметра key_buffer_size (по умолчанию 8Мб, максимальное значение 4Гб). Чем больше значение, тем лучше, так как сервер сможет одновременно хранить больше индексных блоков в оперативной памяти, тем самым разгружая жесткие диски сервера, так как в процессе работы сервера, вероятность обнаружения ключевых значений в ОЗУ становится больше.
Если работа сервера упирается в производительность Mysql сервера и при этом у нас есть приличное кол-во свободной оперативной памяти, то мы первым делом увеличиваем key_buffer_size.
Если работа сервера упирается в производительность Mysql сервера и при этом у нас есть приличное кол-во свободной оперативной памяти, то мы первым делом увеличиваем key_buffer_size.
Для InnoDB таблиц данное значение регулируется с помощью innodb_buffer_pool_size.
В связи с тем что кэш-память ключей ограничена и разделена между всему MyISAM таблицами, возникает проблема такого плана как удаление значений из кэш-памяти (когда в ней отсутствуют нужные данные или она переполнена (по умолчанию первыми удаляются наименее используемые значения), а серверу требуется место в ней под новые значения). Иногда это недопустимо для таблиц которые активно используются, разрешить данную проблему можно с помощью создания индексных кэш-областей и указания загрузки индексов целевой таблицы в определенную область кэш-памяти.
Это позволяет выделить определенную область кэш-памяти только для работы с определенной таблицей. После загрузки ключей в таблицу мы практически исключаем необходимость в обращении к жесткому диску сервера, кроме этого, индексы никогда не будут удалены из кэш-памяти.
В данном примере мы создадим кэш-память индекса ofUserFlag_sTime_idx, таблицы ofUserFlag, базы данных Polygon. Размер индекса будет в 10Мб.
mysql> set global ofUserFlag_sTime_idx.key_buffer_size = 10240*10240;
Query OK, 0 rows affected (0.01 sec)
(для автоматического создания данного кэша при запуске MySQL сервера, надо добавить в my.cnf ofUserFlag_sTime_idx.key_buffer_size = 10М)
(для автоматического создания данного кэша при запуске MySQL сервера, надо добавить в my.cnf ofUserFlag_sTime_idx.key_buffer_size = 10М)
Привязываем кэш-память индекса целевой таблице ofUserFlag
mysql> cache index ofUserFlag in ofUserFlag_sTime_idx;
+--------------------+--------------------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------------+--------------------+----------+----------+
| polygon.ofUserFlag | assign_to_keycache | status | OK |
+--------------------+--------------------+----------+----------+
1 row in set (0.00 sec)
Загружаем индексы таблицы в ее кэш-память индекса (не обязательная процедура, но это лучше, чем ждать их выборки по мере необходимости)
mysql> load index into cache ofUserFlag;
+--------------------+--------------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------------+--------------+----------+----------+
| polygon.ofUserFlag | preload_keys | status | OK |
+--------------------+--------------+----------+----------+
1 row in set (0.00 sec)
Кэш-память запросов
Кэш-память запросов (параметры query_cache_type, query_cache_size, query_cache_limit) используется для повторной обработки select запросов, предназначена для ускорения данных запросов.
После первой обработки оператора select сервер запоминает текст запроса и возвращаемый им результат. В следующий раз сервер анализирует запрос не выполняя его повторно, вместо этого сервер выбирает результат такого запроса непосредственно из кэш-памяти запросов и возвращает его клиенту.
Запросы считаются разными, если они отличаются регистром или приходят от клиентов, которые используют разные кодировки, протоколы или относятся к другим таблицам.
При обновлении таблицы любые закэшированные запросы в которых сделана ссылка на эту таблицу, автоматически удаляются. Таким образом избегается возвращение устаревших данных.
Query_cache_type - определяет режим работы кэш-памяти запросов, ниже указаны ее возможные значения
0 - не кэшировать результаты выполнения запросов
1 - кэшировать все запросы, за исключением таблицы, которые начинаются с select sql_no_cache
2 - кэшировать по запросу только те запросы, которые начинаются с select sql_cache
Query_cache_size - определяет размер кэш-памяти запросов
Query_cache_limit - определяет максимальный кэширующий размер результата запросов, если размер превышен, то данный результат запроса кэшироваться не будет
Кэширование запросов в общем положительная функция, но если целевая таблица модифицируется очень часто, то есть смысл отключить кэширование запросов для нее с помощью sql_no_cache в query_cache_type. Так как при частом изменении данных в этой таблице, все закэшированные запросы для нее быстро теряют актуальность.
Комментариев нет:
Отправить комментарий