新 (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）服务器在并发数=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 上也有同样的症状。在基准测试期间，填充缓存后基本上没有 IO，所以这不是与 IO 相关的问题。
感谢您的任何想法。
答案1
我要回答我自己的问题。我花了几天时间解决这个问题。问题是 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 的数据库。
为了加快查询速度，我们需要使用以下方式运行 MariaDBnumactl。我做了一些基准测试，最好的设置是
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 --交错全部
因此，如果您在新的更强大的服务器上遇到奇怪的缓慢结果，请确保您了解 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
答案2
比较这两个 CPU 可以发现：
- 旧服务器具有最快的基本频率（3.7 vs 3.5 GHz）
- 旧服务器的最大加速速度更高（3.8 比 3.7）
- 旧款的 TPD 高 5W（140W vs 135W）
- 旧 CPU 有 10MB 缓存，新 CPU 有 15MB
基于此，我预计性能会类似，但不会下降一半。
这可能是电源管理问题或 CPU 冷却不良，在负载下显示并且 CPU 开始节流。
- 检查服务器 BIOS 电源管理设置并尝试高性能配置文件
- 使用 powertop 检查负载下的 CPU 状态和频率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