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

Автор: | 01/13/2015
 

mariadb_logoДля начала – возьмём параметры, которые анализирует tuning-primer:

LOW QUERIES

long_query_time – время в секундах, после которого запрос будет считаться как “медленный”, счётчик slow_queries увеличится на 1 и, если включен slow_query_log – то запрос будет записан в лог;

WORKER THREADS

threads_cached – переменная, количество потоков в кеше;
thread_cache_size – количество потоков для повторного использования новым клиентом, которое будет храниться в кеше;

MAX CONNECTIONS

max_connections – максимальное количество одновременно подключенных клиентов;
threads_connected – активные подключения;
max_used_connections – максимальное значение threads_connected со времени запуска сервера;

INNODB STATUS

innodb_buffer_pool_size – размер буфера для хранения InnoDB индексов и данных с диска;

MEMORY USAGE

Memory Ever Allocated, Max Per-thread Buffers, Max Global Buffers и Max Memory Limit – интересные данные, разберём их далее;

KEY BUFFER

key_buffer_size – размер памяти, выделяемый под кеш индексов таблиц (общий для всех потоков);

QUERY CACHE

query_cache_size – размер памяти, выделяемый под кеширование запросов;

SORT OPERATIONS

sort_buffer_size – размер памяти, выделяемый при операциях сортировки и группировки (ORDER  BY, GROUP BY);
read_rnd_buffer_size – размер памяти, выделяемой для чтения строк после сортировки, что-бы избежать повторного поиска на диске (ORDER BY);

JOINS

join_buffer_size – размер памяти, выделяемой для  операций объединения таблиц;

OPEN FILES LIMIT

open_files_limit – максимальное количество файлов, которое сможет открыть MySQL (задаётся в ulimit пользователя);

TABLE CACHE

table_open_cache – максимально допустимое количество одновременно открытых таблиц;
table_definition_cache – определяет размер кэша для структур таблиц (.frm файлов);

TEMP TABLES

max_heap_table_size – максимальный размер таблиц типа MEMORY, которые может создавать пользователь;
tmp_table_size – максимальный размер временной таблицы, которая будет создана в памяти (при превышении – запись на диск);

TABLE SCANS

read_buffer_size – размер памяти, выделяемой потокам для каждой сканируемой таблицы;

Так как пост получился слишком большим – он разбит на несколько отдельных, в каждом будут рассмотрены отдельные параметры, и в конце – несколько параметров, которые не учитываются tuning-primer но тоже полезны.

Помимо всех прочих ссылок – отдельно хочется выделить вот такой MySQL memory калькулятор, который позволят подсчитать размер памяти, которая будет занята сервером при текущих настройках. Мы ещё вернёмся к нему, когда дойдём до параметров MEMORY USAGE.

Продолжение:

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

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

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

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

Начнём с thread_cache_size.

Размер thread_cache_size

Задаёт количество потоков, которое будет храниться в кэше. Т.е. – после отключения клиента от сервера – поток будет перемещён в кеш, и при подключении нового клиента будет использван из кэша, а не создан новый.

Имеет значение оптимизировать, если сервер обслуживает много одновременных подключений:

However, if your server sees hundreds of connections per second you should normally set thread_cache_size high enough so that most new connections use cached threads.

Хорошая статья на тему – тут>>> и тут>>>.

Проверить текущее количество активных сессий можно так:

# mysqladmin -u root -p extended-status | grep -wi 'threads_connected|threads_running' | awk '{ print $2,$4}'
Enter password:
Threads_connected 3
Threads_running 1

Или так:

MariaDB [mysql]> show status where variable_name='Threads_connected' or variable_name='Threads_running';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 1     |
| Threads_running   | 1     |
+-------------------+-------+
2 rows in set (0.00 sec)

Проверяем текущее значение thread_cache_size:

MariaDB [mysql]> show variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 0     |
+-------------------+-------+
1 row in set (0.00 sec)

Значение по-умолчанию – 0, т.е. отключено.

Проверить необходимость в его включении можно так:

mysql> show global status where variable_name like 'Threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_connected | 2     |
| Threads_created   | 1017  |
| Threads_running   | 1     |
+-------------------+-------+
4 rows in set (0.00 sec)

Threads_running – активные потоки в данный момент, Threads_created – количество потоков, созданных с момента старта сервера.

Что бы определить оптимальное значение для thread_cache_size – необходимо выяснить количество активных потоков (Threads_connected), вписать значение в thread_cache_size и перезапустить сервер (или использовать set global).

Потом – ещё раз проверить значение Threads_created – если оно продолжает расти – снова увеличить thread_cache_size. И так до тех пор, пока значения Threads_created и Threads_cached не будут примерно равны (Threads_created может быть немного больше):

Ещё один из вариантов, с чего начать – посмотреть значение Max_used_connections, которое отобразит максимальное количество клиентов с момента старта сервера:

mysql> show status like 'max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 2    |
+----------------------+-------+
1 row in set (0.00 sec)

И становить значение thread_cache_size немного больше Max_used_connections.

thread_cache_size = 4

Перезапускаем сервер:

# service mysql restart
Shutting down MySQL. SUCCESS!
Starting MySQL.. SUCCESS!

Или так (без перезапуска):

MariaDB [mysql]> set global thread_cache_size = 4;
Query OK, 0 rows affected (0.00 sec)

Выполняем 10 одновременных подключений:

# mysqlslap -v -u yser -pPass --auto-generate-sql --concurrency=10 --number-of-queries=100
        ...
        Number of clients running queries: 10
        Average number of queries per client: 10

Проверяем статус:

MariaDB [mysql]> show global status where variable_name='Threads_cached';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Threads_cached | 4     |
+----------------+-------+
1 row in set (0.00 sec)

Коллекция ссылок

https://mariadb.com

http://web-scalability.com

http://blogfreebsd.com

http://vds-admin.ru

http://habrahabr.ru

http://www.percona.com

http://www.codingpedia.org

http://www.ewhathow.com

http://www.dbtuna.com

http://cosydale.com