Performance

MariaDB 執行緒優化

  • February 14, 2018

新(pcie)伺服器:Intel(R) Xeon(R) CPU E5-2637 v4 @ 3.50GHz,1TB NVMe 磁碟,128 GB RAM,安裝 Debian 4.9.65-3+deb9u1,Ver 15.1 Distrib 10.1.26-MariaDB

從移動二進制數據庫文件

舊伺服器:Intel(R) Xeon(R) CPU E5-1630 v3 @ 3.70GHz,SSD 磁碟,64 GB RAM,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) 伺服器在執行 concurrency=1 時性能非常好,在 concurrency=8 時性能相同,而在 concurrency=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 配置在兩台伺服器上都是相同的……

更新更多資訊:最初我安裝在 NEW DB 伺服器 FREEBSD 上,MariaDB 性能很差,我認為這是與作業系統相關的問題,但在 Linux 上也有同樣的症狀。在 benchmark 期間,填充記憶體後基本上沒有 IO,所以這不是 IO 相關的問題。

感謝您的任何想法。

我要回答我自己的問題。我花了幾天時間解決了這個問題。問題是 NUMA。但是,讓我們更多地了解一下。

為了理解問題,我們需要 CPU 資訊:

# 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。伺服器在兩個節點上有兩個 CPU。閱讀有關NUMA的更多資訊,閱讀有關NUMA 和基於 mysql 的數據庫的更多資訊。

為了加快查詢速度,我們需要使用numactl執行 MariaDB 。我做了一些基準測試,最好的設置是

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

總體而言,查詢記憶體在多個執行緒上的擴展性不佳,因此一些 DBA 建議將其關閉。

基準測試結果(QC 是查詢記憶體),MyISAM 表,我的數據庫使用 mysqlslap 並發 64,簡化輸出,越低越好:

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 ”並且仍然打開查詢記憶體。我嘗試了帶有noibrs noibpb nopti標誌的核心,速度增益非常低,只有 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

引用自:https://serverfault.com/questions/895506