Mixing
MariaDB server running 10 x faster than anything we can reproduce. Ideas why?
up vote
0
down vote
favorite
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.
sql linux mariadb
asked 2 days ago
Jamie G
85131332
|
show 8 more comments
up vote
0
down vote
favorite
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.
sql linux mariadb
asked 2 days ago
Jamie G
85131332
1
Move the meta.value = "123456" condition to the ON clause to get true left join result. As it is now you get regular inner join result.
– jarlh
2 days ago
And probably you should use single quotes for string literals, and no quotes for numeric literals (even in mysql?)
– joop
2 days ago
If I had to guess, I would guess that there is a collation issue that prevents the use of indexes -- that is, the default collation used for the queries differs from the collations that exist on the columns with indexes.
– Gordon Linoff
yesterday
@jarlh thanks. The original query that I simplified for the example had two joins, and in the WHERE clauses it had meta1 OR meta2. This might be something that makes it very inefficient, but I don't know why that would make a difference between one server and the other?
– Jamie G
yesterday
@joop thanks, I think it's right to use the quotes here because the field it's matching is a varchar. Aka whether I like it or not, it's stored in meta.value as "123456".
– Jamie G
yesterday
|
show 8 more comments
up vote
0
down vote
favorite
up vote
0
down vote
favorite
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.
sql linux mariadb
asked 2 days ago
Jamie G
85131332
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.
sql linux mariadb
sql linux mariadb
asked 2 days ago
Jamie G
85131332
asked 2 days ago
Jamie G
85131332
asked 2 days ago
Jamie G
85131332
asked 2 days ago
Jamie G
85131332
asked 2 days ago
Jamie G
85131332
85131332
1
Move the meta.value = "123456" condition to the ON clause to get true left join result. As it is now you get regular inner join result.
– jarlh
2 days ago
And probably you should use single quotes for string literals, and no quotes for numeric literals (even in mysql?)
– joop
2 days ago
If I had to guess, I would guess that there is a collation issue that prevents the use of indexes -- that is, the default collation used for the queries differs from the collations that exist on the columns with indexes.
– Gordon Linoff
yesterday
@jarlh thanks. The original query that I simplified for the example had two joins, and in the WHERE clauses it had meta1 OR meta2. This might be something that makes it very inefficient, but I don't know why that would make a difference between one server and the other?
– Jamie G
yesterday
@joop thanks, I think it's right to use the quotes here because the field it's matching is a varchar. Aka whether I like it or not, it's stored in meta.value as "123456".
– Jamie G
yesterday
|
show 8 more comments
1
Move the meta.value = "123456" condition to the ON clause to get true left join result. As it is now you get regular inner join result.
– jarlh
2 days ago
And probably you should use single quotes for string literals, and no quotes for numeric literals (even in mysql?)
– joop
2 days ago
If I had to guess, I would guess that there is a collation issue that prevents the use of indexes -- that is, the default collation used for the queries differs from the collations that exist on the columns with indexes.
– Gordon Linoff
yesterday
@jarlh thanks. The original query that I simplified for the example had two joins, and in the WHERE clauses it had meta1 OR meta2. This might be something that makes it very inefficient, but I don't know why that would make a difference between one server and the other?
– Jamie G
yesterday
@joop thanks, I think it's right to use the quotes here because the field it's matching is a varchar. Aka whether I like it or not, it's stored in meta.value as "123456".
– Jamie G
yesterday
1
1
Move the meta.value = "123456" condition to the ON clause to get true left join result. As it is now you get regular inner join result.
– jarlh
2 days ago
Move the meta.value = "123456" condition to the ON clause to get true left join result. As it is now you get regular inner join result.
– jarlh
2 days ago
And probably you should use single quotes for string literals, and no quotes for numeric literals (even in mysql?)
– joop
2 days ago
And probably you should use single quotes for string literals, and no quotes for numeric literals (even in mysql?)
– joop
2 days ago
If I had to guess, I would guess that there is a collation issue that prevents the use of indexes -- that is, the default collation used for the queries differs from the collations that exist on the columns with indexes.
– Gordon Linoff
yesterday
If I had to guess, I would guess that there is a collation issue that prevents the use of indexes -- that is, the default collation used for the queries differs from the collations that exist on the columns with indexes.
– Gordon Linoff
yesterday
@jarlh thanks. The original query that I simplified for the example had two joins, and in the WHERE clauses it had meta1 OR meta2. This might be something that makes it very inefficient, but I don't know why that would make a difference between one server and the other?
– Jamie G
yesterday
@jarlh thanks. The original query that I simplified for the example had two joins, and in the WHERE clauses it had meta1 OR meta2. This might be something that makes it very inefficient, but I don't know why that would make a difference between one server and the other?
– Jamie G
yesterday
@joop thanks, I think it's right to use the quotes here because the field it's matching is a varchar. Aka whether I like it or not, it's stored in meta.value as "123456".
– Jamie G
yesterday
@joop thanks, I think it's right to use the quotes here because the field it's matching is a varchar. Aka whether I like it or not, it's stored in meta.value as "123456".
– Jamie G
yesterday
|
show 8 more comments
1 Answer
1
active
oldest
votes
up vote
1
down vote
accepted
..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.
answered yesterday
Rick James
64.2k55593
Thanks Rick. The buffer pool made a huge difference. We managed to get the new server down to about 1.2 seconds where the old one is 0.7 seconds. So it's not as fast, but we don't have 18 gigs space for a DB so perhaps not too surprising! We've then re-indexed our meta table as suggested in that article and it's transformed. Down to 0.1 seconds on our server. Phew. Many, many thanks.
– Jamie G
16 hours ago
@JamieG - Glad to hear your results. As a Rule Of Thumb -- Setinnodb_buffer_pool_sizeto about 70% of available RAM. (Not knowing the size of the old server, I did not question the 18G. That would be OK for 24GB of RAM, but not for 16GB.)
– Rick James
16 hours ago
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
1
down vote
accepted
..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.
answered yesterday
Rick James
64.2k55593
Thanks Rick. The buffer pool made a huge difference. We managed to get the new server down to about 1.2 seconds where the old one is 0.7 seconds. So it's not as fast, but we don't have 18 gigs space for a DB so perhaps not too surprising! We've then re-indexed our meta table as suggested in that article and it's transformed. Down to 0.1 seconds on our server. Phew. Many, many thanks.
– Jamie G
16 hours ago
@JamieG - Glad to hear your results. As a Rule Of Thumb -- Setinnodb_buffer_pool_sizeto about 70% of available RAM. (Not knowing the size of the old server, I did not question the 18G. That would be OK for 24GB of RAM, but not for 16GB.)
– Rick James
16 hours ago
add a comment |
up vote
1
down vote
accepted
..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.
answered yesterday
Rick James
64.2k55593
Thanks Rick. The buffer pool made a huge difference. We managed to get the new server down to about 1.2 seconds where the old one is 0.7 seconds. So it's not as fast, but we don't have 18 gigs space for a DB so perhaps not too surprising! We've then re-indexed our meta table as suggested in that article and it's transformed. Down to 0.1 seconds on our server. Phew. Many, many thanks.
– Jamie G
16 hours ago
@JamieG - Glad to hear your results. As a Rule Of Thumb -- Setinnodb_buffer_pool_sizeto about 70% of available RAM. (Not knowing the size of the old server, I did not question the 18G. That would be OK for 24GB of RAM, but not for 16GB.)
– Rick James
16 hours ago
add a comment |
up vote
1
down vote
accepted
up vote
1
down vote
accepted
..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.
answered yesterday
Rick James
64.2k55593
..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.
answered yesterday
Rick James
64.2k55593
edited yesterday
answered yesterday
Rick James
64.2k55593
answered yesterday
Rick James
64.2k55593
answered yesterday
Rick James
64.2k55593
64.2k55593
Thanks Rick. The buffer pool made a huge difference. We managed to get the new server down to about 1.2 seconds where the old one is 0.7 seconds. So it's not as fast, but we don't have 18 gigs space for a DB so perhaps not too surprising! We've then re-indexed our meta table as suggested in that article and it's transformed. Down to 0.1 seconds on our server. Phew. Many, many thanks.
– Jamie G
16 hours ago
@JamieG - Glad to hear your results. As a Rule Of Thumb -- Setinnodb_buffer_pool_sizeto about 70% of available RAM. (Not knowing the size of the old server, I did not question the 18G. That would be OK for 24GB of RAM, but not for 16GB.)
– Rick James
16 hours ago
add a comment |
Thanks Rick. The buffer pool made a huge difference. We managed to get the new server down to about 1.2 seconds where the old one is 0.7 seconds. So it's not as fast, but we don't have 18 gigs space for a DB so perhaps not too surprising! We've then re-indexed our meta table as suggested in that article and it's transformed. Down to 0.1 seconds on our server. Phew. Many, many thanks.
– Jamie G
16 hours ago
@JamieG - Glad to hear your results. As a Rule Of Thumb -- Setinnodb_buffer_pool_sizeto about 70% of available RAM. (Not knowing the size of the old server, I did not question the 18G. That would be OK for 24GB of RAM, but not for 16GB.)
– Rick James
16 hours ago
Thanks Rick. The buffer pool made a huge difference. We managed to get the new server down to about 1.2 seconds where the old one is 0.7 seconds. So it's not as fast, but we don't have 18 gigs space for a DB so perhaps not too surprising! We've then re-indexed our meta table as suggested in that article and it's transformed. Down to 0.1 seconds on our server. Phew. Many, many thanks.
– Jamie G
16 hours ago
Thanks Rick. The buffer pool made a huge difference. We managed to get the new server down to about 1.2 seconds where the old one is 0.7 seconds. So it's not as fast, but we don't have 18 gigs space for a DB so perhaps not too surprising! We've then re-indexed our meta table as suggested in that article and it's transformed. Down to 0.1 seconds on our server. Phew. Many, many thanks.
– Jamie G
16 hours ago
@JamieG - Glad to hear your results. As a Rule Of Thumb -- Set
innodb_buffer_pool_size to about 70% of available RAM. (Not knowing the size of the old server, I did not question the 18G. That would be OK for 24GB of RAM, but not for 16GB.)– Rick James
16 hours ago
@JamieG - Glad to hear your results. As a Rule Of Thumb -- Set
innodb_buffer_pool_size to about 70% of available RAM. (Not knowing the size of the old server, I did not question the 18G. That would be OK for 24GB of RAM, but not for 16GB.)– Rick James
16 hours ago
add a comment |
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53372851%2fmariadb-server-running-10-x-faster-than-anything-we-can-reproduce-ideas-why%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Move the meta.value = "123456" condition to the ON clause to get true left join result. As it is now you get regular inner join result.
– jarlh
2 days ago
And probably you should use single quotes for string literals, and no quotes for numeric literals (even in mysql?)
– joop
2 days ago
If I had to guess, I would guess that there is a collation issue that prevents the use of indexes -- that is, the default collation used for the queries differs from the collations that exist on the columns with indexes.
– Gordon Linoff
yesterday
@jarlh thanks. The original query that I simplified for the example had two joins, and in the WHERE clauses it had meta1 OR meta2. This might be something that makes it very inefficient, but I don't know why that would make a difference between one server and the other?
– Jamie G
yesterday
@joop thanks, I think it's right to use the quotes here because the field it's matching is a varchar. Aka whether I like it or not, it's stored in meta.value as "123456".
– Jamie G
yesterday