CODEWITHSUNDEEP
mysqlsqllinuxmemory-managementmariadb
Jamie G
Jamie G

Reputation: 1743

MariaDB server running 10 x faster than anything we can reproduce. Ideas why?

we've got a site on cheap, shared hosting.

They've got a MariaDB server, which I presume is shared by many customers. We run some pretty chunky queries on this and get fast responses. We want to move the hosting elsewhere, but we simply can't get a database running anywhere near as fast.

We've tried on a new VPS server, with SSD, 12 cores and 16 gigs ram. Queries are taking 10 times longer than the shared hosting's db. (In fact, increasing number of cores made little difference). We tried MySQL and MariaDB + our hosts also setup lightspeed, memcache and Redis, but they didn't help.

For a specific query I've been testing with, the shared server takes approx 0.7 seconds to run, the new server takes about 8 seconds.

We've checked that the indexes are in place (the EXPLAIN query looks the same on both), and we're confident that the whole query isn't cached because minor changes to the query don't effect speed. The output is tiny, so it's the processing that's slower.

The slow queries are long and wide ranging, but effectively it's when it's inefficiently trying to match rows where an ID is saved as a varchar. eg.

SELECT post.ID
FROM post 
LEFT JOIN meta
ON post.ID = meta.postID
AND meta.key = "ABC"
WHERE post.type = "XYZ"
AND meta.value = "123456";

An EXPLAIN on a slow query reveals:

id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
1, SIMPLE, _booking_all_day, ref, "post_id,meta_key", meta_key, 576, const, 71474, Using where; Using temporary; Using filesort
....

I've pasted the SHOW VARIABLES for each server here: https://pastebin.com/iW6WL3zC - fast, shared server. https://pastebin.com/kJGKHyqs - new, slower server.

I have a decent amount of experience with MySQL, but I'm no db engineer! This is my first time with a MariaDB so I don't know if there are important optimisations that I'm unaware of.

One obvious answer is re-write the bad SQL code. We will do, but we will need to move the hosting before we have time to do that. This question is just on why the same query could be so different / any recommendations for trying to get the new server running as fast (or even faster)! Thanks.

Upvotes: 2

Views: 1822

Answers (1)

Rick James
Rick James

Reputation: 142198

..3zC Old, fast: MariaDB 10.1.27 buffer_pool=18G
..yqs New, slow: MariaDB 10.1.37 buffer_pool=128M

innodb_buffer_pool_size is very important to performance. Your 10x clue is what I often see as the difference between I/O-bound and in-cache.

How much RAM in each machine? The buffer_pool should be about 70% of available RAM.

Meanwhile, do they have the same version of WP?

A tips on improving WP's postmeta performance: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta The recommended index change may actually speed up that query in the slower machine enough to match the faster machine.

Upvotes: 1

Related Questions