Назад | Перейти на главную страницу

Оптимизация потоков MariaDB

НОВЫЙ (pcie) сервер: Intel (R) Xeon (R) CPU E5-2637 v4 @ 3,50 ГГц, диски NVMe 1 ТБ, ОЗУ 128 ГБ, установлен Debian 4.9.65-3 + deb9u1, версия 15.1 Distrib 10.1.26-MariaDB

перемещены двоичные файлы БД из

СТАРЫЙ сервер: Intel (R) Xeon (R) CPU E5-1630 v3 @ 3,70 ГГц, SSD-диск, 64 ГБ ОЗУ, FreeBSD 11.0-STABLE, 10.1.21-MariaDB

На серверах работает только mysql, копирую файл my.ini, файлы конфигурации такие же.

Запустите тест mysqlslap (всегда перезапускайте сервер перед выполнением каждого теста):

root@db1:/tmp # mysqlslap --user=root --query=/tmp/slap2.sql --create-schema=mydatabase --concurrency=1 --iterations=1
Benchmark
        Average number of seconds to run all queries: 59.573 seconds
        Minimum number of seconds to run all queries: 59.573 seconds
        Maximum number of seconds to run all queries: 59.573 seconds
        Number of clients running queries: 1
        Average number of queries per client: 100000


root@pcie:~# mysqlslap --user=root --query=/tmp/slap2.sql --create-schema=mydatabase --concurrency=1 --iterations=1
Benchmark
        Average number of seconds to run all queries: 31.151 seconds
        Minimum number of seconds to run all queries: 31.151 seconds
        Maximum number of seconds to run all queries: 31.151 seconds
        Number of clients running queries: 1
        Average number of queries per client: 100000
====================================================================================================================================
root@db1:/tmp # mysqlslap --user=root --query=/tmp/slap2.sql --create-schema=mydatabase --concurrency=100 --iterations=1
Benchmark
        Average number of seconds to run all queries: 568.082 seconds
        Minimum number of seconds to run all queries: 568.082 seconds
        Maximum number of seconds to run all queries: 568.082 seconds
        Number of clients running queries: 100
        Average number of queries per client: 100000

root@pcie:/etc/security/limits.d# mysqlslap --user=root --query=/tmp/slap2.sql --create-schema=mydatabase --concurrency=100 --iterations=1
Benchmark
        Average number of seconds to run all queries: 2059.712 seconds
        Minimum number of seconds to run all queries: 2059.712 seconds
        Maximum number of seconds to run all queries: 2059.712 seconds
        Number of clients running queries: 100
        Average number of queries per client: 100000



====================================================================================================================================
root@db1:/tmp # mysqlslap --user=root --query=/tmp/slap2.sql --create-schema=mydatabase --concurrency=8 --iterations=1
Benchmark
        Average number of seconds to run all queries: 134.003 seconds
        Minimum number of seconds to run all queries: 134.003 seconds
        Maximum number of seconds to run all queries: 134.003 seconds
        Number of clients running queries: 8
        Average number of queries per client: 100000

root@pcie:/etc/security/limits.d# mysqlslap --user=root --query=/tmp/slap2.sql --create-schema=mydatabase --concurrency=8 --iterations=1
Benchmark
        Average number of seconds to run all queries: 133.410 seconds
        Minimum number of seconds to run all queries: 133.410 seconds
        Maximum number of seconds to run all queries: 133.410 seconds
        Number of clients running queries: 8
        Average number of queries per client: 100000

Как видите, сервер NEW (pcie) работает очень хорошо при параллелизме = 1, производительность такая же, когда параллелизм = 8, и очень низкая производительность при параллелизме = 100.

Вот интересные результаты с использованием внутреннего теста:

root@pcie:~/slap/employees_db# mysqlslap --auto-generate-sql --concurrency=8 --iterations=500 --verbose
        Average number of seconds to run all queries: 0.002 seconds
DB1:    Average number of seconds to run all queries: 0.002 seconds

root@pcie:~/slap/employees_db# mysqlslap --auto-generate-sql --concurrency=16 --iterations=500
        Average number of seconds to run all queries: 0.007 seconds
DB1:    Average number of seconds to run all queries: 0.005 seconds

root@pcie:~/slap/employees_db# mysqlslap --auto-generate-sql --concurrency=32 --iterations=500
        Average number of seconds to run all queries: 0.015 seconds
DB1:    Average number of seconds to run all queries: 0.011 seconds

root@pcie:~/slap/employees_db# mysqlslap --auto-generate-sql --concurrency=64 --iterations=500
        Average number of seconds to run all queries: 0.033 seconds
DB1:    Average number of seconds to run all queries: 0.029 seconds

root@pcie:~/slap/employees_db# mysqlslap --auto-generate-sql --concurrency=128 --iterations=500
        Average number of seconds to run all queries: 0.074 seconds
DB1:    Average number of seconds to run all queries: 0.097 seconds

root@pcie:~/slap/employees_db# mysqlslap --auto-generate-sql --concurrency=256 --iterations=500
        Average number of seconds to run all queries: 0.197 seconds
DB1:    Average number of seconds to run all queries: 0.293 seconds

root@pcie:~/slap/employees_db# mysqlslap --auto-generate-sql --concurrency=512 --iterations=500
        Average number of seconds to run all queries: 0.587 seconds
DB1:    Average number of seconds to run all queries: 1.009 seconds

Внутренний тест mysqlsap слишком синтетический, поэтому я загружаю базу данных сотрудников: https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2

SQL:

#less /root/slap/select_query.sql
SELECT emp_no, first_name, last_name, gender FROM employees LIMIT 10;
SELECT emp_no, first_name, last_name, gender FROM employees ORDER BY last_name ASC LIMIT 10;
SELECT COUNT(emp_no) FROM employees WHERE last_name = 'Aamodt';
SELECT last_name, COUNT(emp_no) AS num_emp FROM employees GROUP BY last_name ORDER BY num_emp DESC LIMIT 10;
SELECT employees.* FROM  employees LEFT JOIN dept_emp ON ( dept_emp.emp_no =  employees.emp_no ) LEFT JOIN salaries ON ( salaries.emp_no =  salaries.emp_no ) WHERE employees.first_name LIKE '%Jo%' AND salaries.from_date > '1993-01-21' AND salaries.to_date < '1998-01-01' LIMIT 0, 100;

Полученные результаты:

root@pcie:~/slap# mysqlslap --pre-query="RESET QUERY CACHE;" --create-schema=employees --query="/root/slap/select_query.sql" --iterations=10 --concurrency=1
        Average number of seconds to run all queries: 0.459 seconds
DB1:    Average number of seconds to run all queries: 0.627 seconds

root@pcie:~/slap# mysqlslap --pre-query="RESET QUERY CACHE;" --create-schema=employees --query="/root/slap/select_query.sql" --iterations=10 --concurrency=2
Benchmark
        Average number of seconds to run all queries: 0.473 seconds
DB1:    Average number of seconds to run all queries: 0.626 seconds

root@pcie:~/slap# mysqlslap --pre-query="RESET QUERY CACHE;" --create-schema=employees --query="/root/slap/select_query.sql" --iterations=10 --concurrency=4
        Average number of seconds to run all queries: 0.486 seconds
DB1:    Average number of seconds to run all queries: 0.656 seconds

root@pcie:~/slap# mysqlslap --pre-query="RESET QUERY CACHE;" --create-schema=employees --query="/root/slap/select_query.sql" --iterations=10 --concurrency=8
        Average number of seconds to run all queries: 0.569 seconds
DB1:    Average number of seconds to run all queries: 1.136 seconds

root@pcie:~/slap# mysqlslap --pre-query="RESET QUERY CACHE;" --create-schema=employees --query="/root/slap/select_query.sql" --iterations=10 --concurrency=16
Benchmark
        Average number of seconds to run all queries: 0.948 seconds
DB1:    Average number of seconds to run all queries: 1.750 seconds

root@pcie:~/slap# mysqlslap --pre-query="RESET QUERY CACHE;" --create-schema=employees --query="/root/slap/select_query.sql" --iterations=10 --concurrency=32
        Average number of seconds to run all queries: 1.650 seconds
DB1:    Average number of seconds to run all queries: 2.455 seconds

root@pcie:~/slap# mysqlslap --pre-query="RESET QUERY CACHE;" --create-schema=employees --query="/root/slap/select_query.sql" --iterations=10 --concurrency=64
        Average number of seconds to run all queries: 3.306 seconds
DB1:    Average number of seconds to run all queries: 3.176 seconds

root@pcie:~/slap# mysqlslap --pre-query="RESET QUERY CACHE;" --create-schema=employees --query="/root/slap/select_query.sql" --iterations=10 --concurrency=128
        Average number of seconds to run all queries: 6.744 seconds
DB1:    Average number of seconds to run all queries: 5.737 seconds

root@pcie:~/slap# mysqlslap --pre-query="RESET QUERY CACHE;" --create-schema=employees --query="/root/slap/select_query.sql" --iterations=10 --concurrency=256
        Average number of seconds to run all queries: 13.474 seconds (verified 2nd run: 12.883 seconds)
DB1:    Average number of seconds to run all queries: 3.451 seconds (verified 2nd run:  4.935 seconds)

root@pcie:~/slap# mysqlslap --pre-query="RESET QUERY CACHE;" --create-schema=employees --query="/root/slap/select_query.sql" --iterations=10 --concurrency=512
        Average number of seconds to run all queries: 26.085 seconds (verified 2nd run: 26.307 seconds)
DB1:    Average number of seconds to run all queries: 15.862 seconds (verified 2nd run: 11.280 seconds)

с 512 параллелизмом, QUERY CACHE отключен: 

OLD db1 server:  Average number of seconds to run all queries: 72.710s
NEW PCIE server: Average number of seconds to run all queries: 29.774s

Кто-нибудь знает, что проверить, как оптимизировать настройку? Я использую таблицы MyISAM только в своей БД, конфигурация mariadb одинакова на обоих серверах ...

Обновите с дополнительной информацией: Первоначально я установил на НОВЫЙ сервер БД FREEBSD, работа MariaDB была плохой, я думал, что это проблема, связанная с ОС, но те же симптомы есть и в Linux. Во время тестирования после заполнения кеша операций ввода-вывода практически не происходит, так что это не проблема, связанная с вводом-выводом.

Спасибо за любые идеи.

Я отвечу на свой вопрос. Я потратил пару дней на решение этой проблемы. Проблема была в NUMA. Но давайте посмотрим на это подробнее.

Для понимания проблемы нам нужна информация о процессоре:

# lscpu
Architecture:          x86_64
CPU op-mode(s):        32-bit, 64-bit
Byte Order:            Little Endian
CPU(s):                16
On-line CPU(s) list:   0-15
Thread(s) per core:    2
Core(s) per socket:    4
Socket(s):             2
NUMA node(s):          2
Vendor ID:             GenuineIntel
CPU family:            6
Model:                 79
Model name:            Intel(R) Xeon(R) CPU E5-2637 v4 @ 3.50GHz
Stepping:              1
CPU MHz:               1262.725
CPU max MHz:           3500.0000
CPU min MHz:           1200.0000
BogoMIPS:              6999.47
Virtualization:        VT-x
L1d cache:             32K
L1i cache:             32K
L2 cache:              256K
L3 cache:              15360K
NUMA node0 CPU(s):     0-3,8-11
NUMA node1 CPU(s):     4-7,12-15
Flags:                 fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx pdpe1gb rdtscp lm constant_tsc arch_perfmon pebs bts rep_good nopl xtopology nonstop_tsc aperfmperf eagerfpu pni pclmulqdq dtes64 ds_cpl vmx smx est tm2 ssse3 sdbg fma cx16 xtpr pdcm pcid dca sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand lahf_lm abm 3dnowprefetch epb invpcid_single intel_pt tpr_shadow vnmi flexpriority ept vpid fsgsbase tsc_adjust bmi1 hle avx2 smep bmi2 erms invpcid rtm cqm rdseed adx smap xsaveopt cqm_llc cqm_occup_llc cqm_mbm_total cqm_mbm_local dtherm arat pln pts

Важно NUMA узлов: 2. Сервер получил ДВА процессора на ДВА узлах. Узнать больше о NUMA, подробнее о Базы данных на базе NUMA и mysql.

Чтобы ускорить запросы, нам нужно запустить MariaDB, используя numactl. Я сделал несколько тестов, и лучшая настройка была

numactl --cpunodebind=1 --membind=1  /usr/sbin/mysqld

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

Результаты тестов (QC - Query Cache), таблицы MyISAM, параллелизм 64 в моей базе данных с использованием mysqlslap, упрощенный вывод, чем ниже, тем лучше:

QC-ON  | numactl --cpunodebind=1 --membind=1   | 16.311s
QC-OFF | numactl --cpunodebind=1 --membind=1   | 17.575s 
QC-ON  | [standard execution]                  | 27.177s
QC-OFF | [standard execution]                  | 29.850s
QC-ON  | numactl --interleave all              | 28.664s
QC-OFF | numactl --interleave all              | 30.071s
QC-ON  | numactl -N=1 -m=1 noibrs noibpb nopti | 15.976s

По этим результатам лучше всего указать "numactl --cpunodebind = 1 --membind = 1"и по-прежнему включен Query Cache. Я пробовал ядро ​​с noibrs noibpb nopti flags, прирост скорости довольно низкий, всего 2%. Что меня удивило, так это плохие результаты с numactl --interleave all

Итак, если вы испытываете странные медленные результаты на новом более мощном сервере, убедитесь, что вы понимаете NUMA, это может сэкономить много времени на отладку.

Скрипт для запуска теста:

#!/bin/bash
COUNTER=1
while [ $COUNTER -le 512 ]; do
    mysqlslap --pre-query="RESET QUERY CACHE;" --create-schema=mydatabase --query="/tmp/slap3.sql" --iterations=10 --concurrency=$COUNTER --csv
    let COUNTER=COUNTER*2
done

Сравнение этих двух процессоров показывает:

  • у старых серверов самая быстрая базовая частота (3,7 против 3,5 ГГц)
  • старый сервер имеет более высокую максимальную скорость турбо (3,8 против 3,7)
  • TPD у старых моделей на 5 Вт выше (140 Вт против 135 Вт)
  • старый процессор имеет кэш 10 МБ, а новый 15 МБ

Исходя из этого, я ожидал такой же производительности, но не наполовину.

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

  • Проверьте настройки управления питанием в BIOS сервера и попробуйте профиль высокой производительности
  • Проверьте состояние и частоту процессора под нагрузкой с помощью powertop http://xmodulo.com/how-to-monitor-power-usage-in-linux.html (или аналогичный инструмент)

https://ark.intel.com/products/82764/Intel-Xeon-Processor-E5-1630-v3-10M-Cache-3_70-GHz https://ark.intel.com/products/92983/Intel-Xeon-Processor-E5-2637-v4-15M-Cache-3_50-GHz