maria-discuss team mailing list archive

[Sergey Vojtovich <svoj@xxxxxxxxxxx>]

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     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp