MariaDB线程优化

Question

新(pcie)服务器: Intel(R) Xeon(R) CPU E5-2637 v4 @ 3.50GHz，1TB NVMe磁盘，128 GB内存，已安装Debian 4.9.65-3+ Ver 9u1，Ver 15.1 Distrib 10.1.26-MariaDB

将二进制db文件从

旧服务器: Intel(R) Xeon(R) CPU E5-1630 v3 @ 3.70GHz，SSD磁盘，64 GB内存，FreeBSD 11.0-稳定，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

如您所见，新(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基准太合成，所以我加载employees db：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并发的情况下，查询缓存禁用：

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配置是相同的.

更新更多信息:最初我安装在新的DB服务器FREEBSD上，MariaDB性能很差，我认为这是与操作系统相关的问题，但在Linux上也有相同的症状。在基准测试期间，在填充缓存之后基本上没有IO，因此这不是与IO相关的问题。

谢谢你的建议。

Answer 1

比较这两个CPU显示：

• 老服务器拥有最快的基本freq (3.7vs3.5GHz)
• 老服务器有更高的最大涡轮速度(3.8比3.7)
• TPD高5W (140 W对135 W)
• 旧CPU有10 new的缓存和新的15 new。

基于此，我期望类似的表现，但不会下降一半。

这可能是电源管理问题或糟糕的CPU冷却显示，在负荷和CPU开始节流。

• 检查服务器BIOS电源管理设置并尝试高性能配置文件
• 使用powertop http://xmodulo.com/how-to-monitor-power-usage-in-linux.html (或其他类似工具)检查负载下的CPU状态和频率。

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