[Maria-discuss] MariaDB threads optimalization
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
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@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Am 07.02.2018 um 11:28 schrieb Sergey Vojtovich:
I'm afraid query cache doesn't scale well. Try turning it off
well, the question is why on a read-bound workload because there is no valid reason that it locks reads for other threads and no remove query-cache like MySQL 8.0 is no solution because when you have a few terrible optimized queries on your server which takes seconds to run query-cache will save your ass while you can (and we did) write known fast queries with "select SQL_NO_CACHE" to get the locking relaxed
Hi!
Le 7 févr. 2018 à 11:34, Reindl Harald <h.reindl@thelounge.net> a écrit :
Am 07.02.2018 um 11:28 schrieb Sergey Vojtovich:
I'm afraid query cache doesn't scale well. Try turning it off
well, the question is why on a read-bound workload because there is no valid reason that it locks reads for other threads
and no remove query-cache like MySQL 8.0 is no solution because when you have a few terrible optimized queries on your server which takes seconds to run query-cache will save your ass while you can (and we did) write known fast queries with "select SQL_NO_CACHE" to get the locking relaxed
You could also take a look at software like https://www.heimdalldata.com which tries to replace the query cache (and much more BTW) HTH, -- Jocelyn Fournier Founder M : +33 6 51 21 54 10 https://www.softizy.com Softizy - At your side to Optimize your PHP / MySQL applications
Am 07.02.2018 um 11:39 schrieb jocelyn fournier:
Le 7 févr. 2018 à 11:34, Reindl Harald <h.reindl@thelounge.net> a écrit :
Am 07.02.2018 um 11:28 schrieb Sergey Vojtovich:
I'm afraid query cache doesn't scale well. Try turning it off
well, the question is why on a read-bound workload because there is no valid reason that it locks reads for other threads
and no remove query-cache like MySQL 8.0 is no solution because when you have a few terrible optimized queries on your server which takes seconds to run query-cache will save your ass while you can (and we did) write known fast queries with "select SQL_NO_CACHE" to get the locking relaxed
You could also take a look at software like https://www.heimdalldata.com which tries to replace the query cache (and much more BTW)
it's not very helpful to throw more and more layers and software on problems trying to hide instead solve them each layer brings inevitable it's own problems to the mix and if it's only added latency for non-cacheable queries and complexer dependencies for long-term maintainance of oyur whole application stack __ P.S.: can you fix your mail-client so that it knows about how to handle mailing-lists properly instead reply-all? you break *my* reply-list button and make reply go directly to you and leave the list out besides that you off-list copy was fatser, the list-message silently got killed by the duplicate filter and my reply now starts a new thread
Le 7 févr. 2018 à 11:48, Reindl Harald <h.reindl@thelounge.net> a écrit : Am 07.02.2018 um 11:39 schrieb jocelyn fournier: Le 7 févr. 2018 à 11:34, Reindl Harald <h.reindl@thelounge.net> a écrit : Am 07.02.2018 um 11:28 schrieb Sergey Vojtovich: I'm afraid query cache doesn't scale well. Try turning it off well, the question is why on a read-bound workload because there is no valid reason that it locks reads for other threads and no remove query-cache like MySQL 8.0 is no solution because when you have a few terrible optimized queries on your server which takes seconds to run query-cache will save your ass while you can (and we did) write known fast queries with "select SQL_NO_CACHE" to get the locking relaxed You could also take a look at software like https://www.heimdalldata.com which tries to replace the query cache (and much more BTW) it's not very helpful to throw more and more layers and software on problems trying to hide instead solve them well if there's no query cache at all like in MySQL 8, it could be definitely useful :) And it add a few other useful features like read/write split (for those who have not implemented it directly in their application yet), automated failover, etc... And with its dashboard you know what it's doing (and as a bonus what's potentially wrong with your queries as well). each layer brings inevitable it's own problems to the mix and if it's only added latency for non-cacheable queries and complexer dependencies for long-term maintainance of oyur whole application stack I agree before adopting this kind of software, you have to check the cacheability of your application (which is a metric displayed by the heimdalldata dashboard BTW) because there's always a small overhead. But I like the concept of heimdalldata where the cache is actually hosted on each front server, instead of a potentially remote MySQL server, hence *reducing* the latency of your application by removing the network round-trip delay for cached queries. -- Jocelyn Fournier Founder M : +33 6 51 21 54 10 <+33%206%2051%2021%2054%2010> https://www.softizy.com Softizy - At your side to Optimize your PHP / MySQL applications
Am 07.02.2018 um 14:25 schrieb jocelyn fournier:
each layer brings inevitable it's own problems to the mix and if it's only added latency for non-cacheable queries and complexer dependencies for long-term maintainance of oyur whole application stack
I agree before adopting this kind of software, you have to check the cacheability of your application (which is a metric displayed by the heimdalldata dashboard BTW) because there's always a small overhead. But I like the concept of heimdalldata where the cache is actually hosted on each front server, instead of a potentially remote MySQL server, hence *reducing* the latency of your application by removing the network round-trip delay for cached queries well, our mysqld are conencted via unix-sockets, so no TCP and no failovers needed on that layer - HA is done by the virtualization cluster on a deeper layer and when the whole cluster fails game over anyways :-)
Reindl, QC was developed many years ago when scalability wasn't a concern. It is fixable, but unfortunately it was never a priority. Regards, Sergey On Wed, Feb 07, 2018 at 11:34:02AM +0100, Reindl Harald wrote:
Am 07.02.2018 um 11:28 schrieb Sergey Vojtovich:
I'm afraid query cache doesn't scale well. Try turning it off
well, the question is why on a read-bound workload because there is no valid reason that it locks reads for other threads
and no remove query-cache like MySQL 8.0 is no solution because when you have a few terrible optimized queries on your server which takes seconds to run query-cache will save your ass while you can (and we did) write known fast queries with "select SQL_NO_CACHE" to get the locking relaxed
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
participants (4)
-
Brano Gege -
jocelyn fournier -
Reindl Harald -
Sergey Vojtovich