В этой статье (скорее даже шпаргалке) я буду конспектирования сводную информацию и методики того, как можно осуществлять тестирование производительности MySQL сервера. Основная цель написания этой шпаргалки – личное использование. Если вы найдете что-то полезно для себя – я буду очень рад и вы можете свободное использовать материал статьи в своих целях, но без каких-либо гарантий со стороны автора.
Эта публикация сугубо мой конспект прочитанной литературы. Информация может быть не очень структурирована и удобочитаема. Материал публикации предоставляется без каких-либо гарантий со стороны автора.
Вместо описания
В цикле статей про оптимизацию производительности MySQL я буду агрегировать всю информацию о тестировании производительности различных компонентов сервера MySQL и их оптимизацию, которую я почерпну из различных источников и литературы. Подробные пояснения я буду приводить только в том случае, если это необходимо.
Список первоисточников
- O’Reilly High Performance MySQL 3rd Edition.
Инструментарий
Здесь я приведу список используемых утилит.
sysbench
Установка sysbench на Ubuntu Server:
sudo apt install sysbenchТестирование производительности дисковой подсистемы
Утилита sysbench
Сначала подготавливаем файл нужного вам размера:
sysbench --test=fileio --file-total-size=10G prepareroot@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 runRunning 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 cleanupsysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)
Removing test files...
Тестирование производительности CPU
Утилита sysbench
Вариант тестирования с помощью утилиты sysbench:
sysbench cpu --cpu-max-prime=20000 runsysbench 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 preparesysbench 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 runRunning 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.