Hi Brano, I'm afraid query cache doesn't scale well. Try turning it off.
Regards, Sergey On Wed, Feb 07, 2018 at 02:08:18PM +0700, Brano Gege wrote: > Hi All, > > I got problem with mariadb performance, > > NEW (pcie) server: Intel(R) Xeon(R) CPU E5-2637 v4 @ 3.50GHz, 1TB NVMe > disks, 128 GB RAM, installed Debian 4.9.65-3+deb9u1, Ver 15.1 Distrib > 10.1.26-MariaDB > > OLD server: Intel(R) Xeon(R) CPU E5-1630 v3 @ 3.70GHz, SSD disk, 64 GB RAM, > FreeBSD 11.0-STABLE, 10.1.21-MariaDB > > On servers is running just mysql, I copy my.ini file, config files are same. > > mysql slap results for example employees db: > > #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) > > as you can see with raising threads PCIE performance is lowering rapidly. > Any idea what to check? > > Thank you. > > I post this question couple of days with more information also here: > https://serverfault.com/questions/895506/mariadb-threads-optimalization > _______________________________________________ > Mailing list: https://launchpad.net/~maria-discuss > Post to : [email protected] > Unsubscribe : https://launchpad.net/~maria-discuss > More help : https://help.launchpad.net/ListHelp _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
