Home Queries that are fast on production database very slow on imported copy locally
Reply: 0

Queries that are fast on production database very slow on imported copy locally

user11938
1#
user11938 Published in September 20, 2018, 4:36 pm

If I export our production database to a .sql file and then import it on my local machine, the same exact queries that run very fast (90ms) on the production database run insanely slow (100 seconds or more) locally.

production mysql version: 5.7.18 local mysql version: 5.7.21

I checked and it looks like all the indexes are there and the same in both databases. The only difference is the Cardinality values are different on the indexes. Would that make a difference?

Here's an example of a query that is slow:

SELECT DISTINCT v0.`id` 
FROM `visibility__slugs` AS v0 
INNER JOIN `visibility__business_type_slug` AS v5 ON v5.`slug_id` = v0.`id` 
INNER JOIN `visibility__business_types` AS v1 ON (v5.`business_type_id` = v1.`id`) AND (v1.`type_name` != 'Other') 
INNER JOIN `visibility__business_type_page` AS v6 ON v6.`business_type_id` = v1.`id` 
INNER JOIN `visibility__pages` AS v2 ON v6.`page_id` = v2.`id` 
INNER JOIN `visibility__page_postal_code` AS v7 ON v7.`page_id` = v2.`id` 
INNER JOIN `visibility__postal_codes` AS v3 ON v7.`postal_code` = v3.`postal_code` 
WHERE (v2.`is_live`) 

Here's EXPLAIN

1 SIMPLE  v0  NULL  index PRIMARY,visibility__slugs_slug_unique visibility__slugs_slug_unique 767 NULL  1 100.00  Using index; Using temporary
1 SIMPLE  v3  NULL  index visibility__postal_codes_postal_code_unique,visibility__postal_codes_postal_code_index  visibility__postal_codes_postal_code_index  767 NULL  1 100.00  Using index; Distinct; Using join buffer (Block Nested Loop)
1 SIMPLE  v5  NULL  ref visibility__business_type_slug_slug_id_business_type_id_unique,visibility__business_type_slug_slug_id_index,visibility__business_type_slug_business_type_id_index visibility__business_type_slug_slug_id_index  4 zipbooks_development.v0.id  1 100.00  Using index; Distinct
1 SIMPLE  v1  NULL  eq_ref  PRIMARY PRIMARY 4 zipbooks_development.v5.business_type_id  1 90.00 Using where; Distinct
1 SIMPLE  v6  NULL  ref visibility__business_type_page_page_id_business_type_id_unique,visibility__business_type_page_page_id_index,visibility__business_type_page_business_type_id_index visibility__business_type_page_business_type_id_index 4 zipbooks_development.v5.business_type_id  15  100.00  Using index; Distinct
1 SIMPLE  v2  NULL  eq_ref  PRIMARY PRIMARY 4 zipbooks_development.v6.page_id 1 90.00 Using where; Distinct
1 SIMPLE  v7  NULL  eq_ref  visibility__page_postal_code_page_id_postal_code_unique,visibility__page_postal_code_page_id_index,visibility__page_postal_code_postal_code_index visibility__page_postal_code_page_id_postal_code_unique 771 zipbooks_development.v6.page_id,zipbooks_development.v3.postal_code 1 100.00  Using index; Distinct

Here's the slow query log:

Time                 Id Command    Argument# Time: 2018-02-14T18:12:40.968206Z
# User@Host: root[root] @ localhost [127.0.0.1]  Id:     2
# Query_time: 191.781505  Lock_time: 0.000142 Rows_sent: 0  Rows_examined: 183768270
SET timestamp=1518631960;
SELECT DISTINCT v0.`id` 
FROM `visibility__slugs` AS v0 
INNER JOIN `visibility__business_type_slug` AS v5 ON v5.`slug_id` = v0.`id` 
INNER JOIN `visibility__business_types` AS v1 ON (v5.`business_type_id` = v1.`id`) AND (v1.`type_name` != 'Other') 
INNER JOIN `visibility__business_type_page` AS v6 ON v6.`business_type_id` = v1.`id` 
INNER JOIN `visibility__pages` AS v2 ON v6.`page_id` = v2.`id` 
INNER JOIN `visibility__page_postal_code` AS v7 ON v7.`page_id` = v2.`id` 
INNER JOIN `visibility__postal_codes` AS v3 ON v7.`postal_code` = v3.`postal_code` 
WHERE (v2.`is_live`);

It's also interesting to note that sometimes it works. Sometimes I import the backup and the queries are as fast as on production. I haven't seen a pattern between when it works and when the queries are very slow after import, but thats noteworthy. Could it be related to our use of --single-transaction --quick to dump production?

Can anyone think of some things I can try? Configuration values? What might be wrong here?

share|improve this question
  • Have you tried using EXPLAIN on the query? It may be related to memory caching in mysql as well, take a read of dev.mysql.com/doc/refman/5.7/en/buffering-caching.html – Taylor Feb 14 at 18:18
  • I'll add explain – atomkirk Feb 14 at 18:18
  • I'm not sure its a memory caching issue because the problem persists between server restarts. – atomkirk Feb 14 at 18:28
  • If the prod server has a big cache, it can keep all this in mem, if the cache is small on local dev, it may need to swap to disk. Also, check the engine being used (InnoDB vs. MyISAM, etc...) – Taylor Feb 14 at 18:29
  • you must duplicate mysql setup too to be able to compare times. So at least my.cnf on local should be the same as remote one. – Luis Muñoz Feb 14 at 18:35

1 Answer 1

active oldest votes
up vote 1 down vote
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.336998 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO