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

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

atomkirk
1#
atomkirk Published in 2018-02-14 18:16:01Z

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?

atomkirk
2#
atomkirk Reply to 2018-02-14 20:52:03Z

My indexes were corrupted. I found the offending table and ran

ALTER TABLE visibility__postal_codes ENGINE = InnoDB;

to rebuild the indexes and now its fixed.

You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO