Оптимизация MySQL сервера. Часть 1. Тестирование производительности MySQL сервера

В этой статье (скорее даже шпаргалке) я буду конспектирования сводную информацию и методики того, как можно осуществлять тестирование производительности MySQL сервера. Основная цель написания этой шпаргалки – личное использование. Если вы найдете что-то полезно для себя – я буду очень рад и вы можете свободное использовать материал статьи в своих целях, но без каких-либо гарантий со стороны автора.

Эта публикация сугубо мой конспект прочитанной литературы. Информация может быть не очень структурирована и удобочитаема. Материал публикации предоставляется без каких-либо гарантий со стороны автора.

Вместо описания

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

Список первоисточников

  1. O’Reilly High Performance MySQL 3rd Edition.

Инструментарий

Здесь я приведу список используемых утилит.

sysbench

Установка sysbench на Ubuntu Server:

sudo apt install sysbench

Тестирование производительности дисковой подсистемы

Утилита sysbench

Сначала подготавливаем файл нужного вам размера:

sysbench --test=fileio --file-total-size=10G prepare
root@mysql01:/home/roman# sysbench --test=fileio --file-total-size=10G prepare
WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)

128 files, 81920Kb each, 10240Mb total
Creating files for the test...
Extra file open flags: (none)
Creating file test_file.0
Creating file test_file.1
Creating file test_file.2
<...>
Creating file test_file.126
Creating file test_file.127
10737418240 bytes written in 43.62 seconds (234.76 MiB/sec).

Запускаем тестирование. В примере ниже тест случайной записи:

sysbench fileio --file-total-size=10G --file-test-mode=rndrw time=300 --max-requests=0 run
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time


Extra file open flags: (none)
128 files, 80MiB each
10GiB total file size
Block size 16KiB
Number of IO requests: 0
Read/Write ratio for combined random IO test: 1.50
Periodic FSYNC enabled, calling fsync() each 100 requests.
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random r/w test
Initializing worker threads...

Threads started!

File operations:
    reads/s:                      1504.18
    writes/s:                     1002.79
    fsyncs/s:                     3209.23

Throughput:
    read, MiB/s:                  23.50
    written, MiB/s:               15.67

General statistics:
    total time:                          300.0120s
    total number of events:              1714815

Latency (ms):
         min:                                    0.00
         avg:                                    0.17
         max:                                  100.72
         95th percentile:                        0.59
         sum:                               298062.01

Threads fairness:
    events (avg/stddev):           1714815.0000/0.00
    execution time (avg/stddev):   298.0620/0.00

Наиболее важные параметры выделены на скриншоте выше – это количество операций чтения/записи в секунду и общая скорость операций чтения и записи на диск.

Возможные варианты режима тестирования (параметр –file-test-mode):

Режим (–file-test-mode)Описание
seqwrПоследовательная запись
seqrewrПоследовательная перезапись
seqrdПоследовательное чтение
rndrdСлучайное чтение
rndwrСлучайная запись
rndrwСлучайные чтение и перезапись

По завершение тестирования удаляем тестовый файл:

sysbench fileio --file-total-size=10G cleanup
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)
Removing test files...

Тестирование производительности CPU

Утилита sysbench

Вариант тестирования с помощью утилиты sysbench:

sysbench cpu --cpu-max-prime=20000 run
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)

Running the test with following options:
Number of threads: 1
Initializing random number generator from current time


Prime numbers limit: 20000

Initializing worker threads...

Threads started!

CPU speed:
    events per second:   359.68

General statistics:
    total time:                          10.0025s
    total number of events:              3599

Latency (ms):
         min:                                    2.45
         avg:                                    2.78
         max:                                    3.82
         95th percentile:                        2.97
         sum:                                 9997.25

Threads fairness:
    events (avg/stddev):           3599.0000/0.00
    execution time (avg/stddev):   9.9973/0.00

В этом тесте стоит обратить внимание на общее время выполнения.

Синтетические тесты для сервера баз данных

Утилита sysbench

Ниже я приведу вариант тестирования OLPT с помощью утилиты sysbench.

Сначала создадим тестовую базу данных:

mysql -e 'CREATE DATABASE tst_db'

Теперь подготовим табличку для тестирования с 1 миллионом записей:

sysbench --db-driver=mysql --mysql-user=root --mysql-password=Qwerty123   --mysql-socket=/var/run/mysqld/mysqld.sock --mysql-db=tst_db --range_size=100 --table_size=1000000 --tables=2 --threads=1 --events=0 --time=60 --rand-type=uniform /usr/share/sysbench/oltp_read_only.lua prepare
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)

Creating table 'sbtest1'...
Inserting 1000000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...
Creating table 'sbtest2'...
Inserting 1000000 records into 'sbtest2'
Creating a secondary index on 'sbtest2'...

Запускаем OLTP тест:

sysbench --db-driver=mysql --mysql-user=root --mysql-password=Qwerty123 --mysql-socket=/var/run/mysqld/mysqld.sock --mysql-db=tst_db --range_size=100 --table_size=1000000 --tables=2 --threads=8 --events=0 --time=60 --rand-type=uniform /usr/share/sysbench/oltp_read_only.lua run
Running the test with following options:
Number of threads: 8
Initializing random number generator from current time


Initializing worker threads...

Threads started!

SQL statistics:
    queries performed:
        read:                            2015636
        write:                           0
        other:                           287948
        total:                           2303584
    transactions:                        143974 (2399.33 per sec.)
    queries:                             2303584 (38389.21 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0040s
    total number of events:              143974

Latency (ms):
         min:                                    2.71
         avg:                                    3.33
         max:                                   13.23
         95th percentile:                        3.68
         sum:                               479718.33

Threads fairness:
    events (avg/stddev):           17996.7500/217.19
    execution time (avg/stddev):   59.9648/0.00

Наиболее важные параметры:

  • Количество транзакций (всего и за одну секунду).
  • Количество запросов (всего и за одну секунду).
  • Показатели задержки.
  • Распределение нагрузки по потокам (threads fairness), т.е. сколько событий в среднем обработал каждый из потоков и сколько было потрачено на это времени.

Также в директории /usr/share/sysbench/ также лежат многие другие типы тестов. Можете использовать их для вашего сценария.

ls -l /usr/share/sysbench/
root@mysql01:/home/roman# ls -l /usr/share/sysbench/
total 60
-rwxr-xr-x 1 root root  1448 Oct 23  2021 bulk_insert.lua
-rw-r--r-- 1 root root 14369 Oct 23  2021 oltp_common.lua
-rwxr-xr-x 1 root root  1286 Oct 23  2021 oltp_delete.lua
-rwxr-xr-x 1 root root  2411 Oct 23  2021 oltp_insert.lua
-rwxr-xr-x 1 root root  1261 Oct 23  2021 oltp_point_select.lua
-rwxr-xr-x 1 root root  1645 Oct 23  2021 oltp_read_only.lua
-rwxr-xr-x 1 root root  1820 Oct 23  2021 oltp_read_write.lua
-rwxr-xr-x 1 root root  1114 Oct 23  2021 oltp_update_index.lua
-rwxr-xr-x 1 root root  1123 Oct 23  2021 oltp_update_non_index.lua
-rwxr-xr-x 1 root root  1436 Oct 23  2021 oltp_write_only.lua
-rwxr-xr-x 1 root root  1915 Oct 23  2021 select_random_points.lua
-rwxr-xr-x 1 root root  2114 Oct 23  2021 select_random_ranges.lua

Заключение

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

Ссылки на другие статьи этого цикла

Часть 2. Профилирование нагрузки сервера MySQL.

Часть 3. Базовая конфигурация и оптимизация настроек MySQL.

Часть 4. Признаки проблем с производительностью MySQL.

Часть 5. Полезные запросы для MySQL.

Часть 6. Нюансы настройки репликации MySQL.

Часть 7. Резервное копирование MySQL.

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

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