show status like, show variables like. Оптимизация MySQL | MYSQL

На заметку себе по вопросам оптимизации mysql. Актуально для myisam, mysql 4.0 (думаю актуально и для 5 ветки).
Общий принцип – смотрим счетчики в запросах типа

mysql> show status like "%table%";

И значение переменных сервера:

mysql> show variables like "%table%";

Сравниваем, думаем, читаем что за что отвечает.

После того как сделали выводы – идем в /etc/my.cnf правим переменные и перезапускаем сервер. Снова смотрим на вывод show status и делаем выводы. :)

1. table_cache

Смотрим Open_tables и Opened_tables
Значение не должно расти, не должно быть большим.

mysql> show status like "Open_table%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 187   |
+---------------+-------+
1 row in set (0.00 sec)

Смотрим table_cache. Если Open_tables растет – увеличиваем кэш. При этом помним что останавливая сервер mysql придется сбрасывать кэш на диск дольше. Останавливаться mysql будет дольше.

mysql> show variables like "table_cache";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| table_cache   | 256   |
+---------------+-------+
1 row in set (0.00 sec)

2. thread_cache_size

Переменная – thread_cache_size. Это количество готовых к соединению потоков для ускорения обслуживания запроса на подключение к серверу mysql.

mysql> show variables like "thread_cache%";
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 8     |
+-------------------+-------+
1 row in set (0.00 sec)

Смотрим Threads_cached, Threads_connected, Threads_created
Если Threads_created растет – можно немного увеличить кэш.

mysql> show status like "%threads%";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Delayed_insert_threads | 0     |
| Slow_launch_threads    | 0     |
| Threads_cached         | 1     |
| Threads_connected      | 6     |
| Threads_created        | 7     |
| Threads_running        | 6     |
+------------------------+-------+
6 rows in set (0.00 sec)

3. Временные таблицы.

Создаются сервером в результате выборки столбцов типа – BLOB или TEXT. Или в результате сложных запросов с помещением результатов выборки во временную таблицу.

mysql> show status like "%tmp%";
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 23647 |
| Created_tmp_files       | 26132 |
| Created_tmp_tables      | 84166 |
+-------------------------+-------+
3 rows in set (0.00 sec)

Можно попробовать увеличить max_heap_table_size и tmp_table_size

mysql> show variables like "%heap%";
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| max_heap_table_size | 16777216 |
+---------------------+----------+
1 row in set (0.00 sec)
mysql> show variables like "tmp_table_size";
+----------------+----------+
| Variable_name  | Value    |
+----------------+----------+
| tmp_table_size | 33554432 |
+----------------+----------+
1 row in set (0.00 sec)

Не забываем при этом что параметры должны быть сбалансированы – увеличивая параметры можно потратить память впустую. Увеличиваем понемногу и снова смотрим насколько увеличиваются показатели в выводе show status like «%tmp%»; Чем медленнее растут значения переменных типа %tmp% – тем лучше.

4. key_buffer_size

Определяем какое нам необходимо значение переменной key_buffer_size

Говорят, что Mysql (myisam) умеет кэшировать только индексы. Кэширование данных таблиц (MYD файлы) она доверяет ОС. Следовательно если запрос не может обойтись чтением данных из индекса – получаем обращение к диску (если файлы данных не скэшированы операционной системой).

Смотрим значение переменной key_cache_block_size – размер блока кэша ключей (индексов).

mysql> show variables like "key_cache_block_size";
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| key_cache_block_size | 1024  |
+----------------------+-------+
1 row in set (0.00 sec)

И выясняем сколько блоков использовано:

mysql> show status like "Key_blocks_used";
+-----------------+--------+
| Variable_name   | Value  |
+-----------------+--------+
| Key_blocks_used | 214342 |
+-----------------+--------+
1 row in set (0.00 sec)

Произведение этих переменных сравниваем с размером кэша:

mysql> show variables like "key_buffer_size";
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| key_buffer_size | 268435456 |
+-----------------+-----------+
1 row in set (0.00 sec)

В нашем примере: 214342 х 1024 = 219486208 – это использованная память.
Вычитаем полученную занятую выделенными блоками память из значения всей памяти выделенной под кэш (key_buffer_size) получаем свободные 48949248 байта. Эта память используется «впустую», про запас. :) Если объем этой неиспользуемой памяти велик – мы можем уменьшить значение key_buffer_size
Оценку надо проводить на «прогретом» сервере под реальной нагрузкой.

5. Max_connections

Смотрим max_used_connections – количество соединений

mysql> show status like "max_used_connections";
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 7     |
+----------------------+-------+
1 row in set (0.00 sec)
mysql> show variables like "max_connections";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 400   |
+-----------------+-------+
1 row in set (0.00 sec)

Если в процессе работы сервер жалуется на то что ему не хватает значения определенной в переменной max_connections – то это значит что скорее всего серверу не хватает ресурсов. Лучше посмотреть на то свопит ли сервер, количество блокировок и т.д чем увеличить значение переменной…

6. Операции соединения

Операции соединения – очень ресурсоемки.
Select_full_join – полное соединение без индексов.
Select_full_range_join – соединение с поиском по диапазону.
Смотрим значения:

mysql> show status like "%join%";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Select_full_join       | 0     |
| Select_full_range_join | 0     |
+------------------------+-------+
2 rows in set (0.00 sec)

У нас все в порядке – таких запросов пока не было.

7. Сортировки. sort_buffer_size

Смотрим счетчик Sort_merge_passes

mysql> show status like "Sort_merge_passes";
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 13740 |
+-------------------+-------+
1 row in set (0.00 sec)

Если счетчик быстро растет – значит некоторые операции не попадают в размер кэша и sort_buffer_size стоит увеличить.

mysql> show variables like "sort_buffer_size";
+------------------+---------+
| Variable_name    | Value   |
+------------------+---------+
| sort_buffer_size | 2097144 |
+------------------+---------+
1 row in set (0.00 sec)

Увеличивать переменную стоит понемногу – иначе даже для запрособ которые требуют небольшого размера буфера будет выделен полный обьем памяти определнный в переменной sort_buffer_size.

  1. hello.
    а чем отличаются sort_buffer_size от myisam_sort_buffer_size ?