4

I am having a strange issue with a slow query under certain conditions: I have made a few tests and was able to isolate the issue to one single table called products_description (all the tables are MyISAM). At first I noticed that when this table is new (ie. just imported), the query always performs fast (~0.3s). However, if I perform any of these operations on this specific table, at any time (even immediately after importing it): CHECK, OPTIMIZE, ANALYZE or REPAIR, the query suddenly slows down x10 times (takes ~4.5s) and keeps staying always slow.

Note that I forced no-caching when running the query to ensure the results are correct.

I am able to restore the performance only if I am then performing any of these operations on that table:

1) DROP the table and import it again.

or

2) ALTER any of the following of that table: Collation or CHECKSUM or DELAY_KEY_WRITE. It then runs fast with the altered value and when reverting back to the old value, the performance remains fast.

Even then, if I perform any of the CHECK, OPTIMIZE, ANALYZE or REPAIR operations on that table, the query speed drops until I do either 1) or 2)

One more thing I tested: Before performing any operation on the table, I backed up the table's files (products_description.frm, products_description.MYD, products_description.MYI), ran the query, it ran fast. Then I performed CHECK on the table, ran the query, speed was x10 times slower, I copied the backed up files and overwrote the 3 files, ran the query again, slow speed again.

I have compressed the database in a ~5mb zip file (~80mb unzipped). If anyone wants to test the database in your own environment, please let me know and I will send you a download link. I could reproduce this on several different servers, on both MariaDB 10.1+ and MySQL 5.6+.

This is the SQL query that I am running and you should test with:

SELECT DISTINCT pav.products_options_values_id, 
            pav.products_options_values_name, 
            pav.products_options_values_sort_order 
FROM   products_stock ps, 
       products_options_values pav, 
       (SELECT DISTINCT pa.products_id 
        FROM   products_attributes pa, 
               products_options_values pov, 
               (SELECT p.products_id, 
                       p.products_image, 
                       p.products_subimage1, 
                       pd.products_name, 
                       p.products_quantity, 
                       p.products_model, 
                       p.products_ordered, 
                       p.products_price, 
                       p.products_date_added, 
                       p.products_weight, 
                       p.products_length, 
                       p.products_width, 
                       p.products_height, 
                       p.products_tax_class_id, 
                       p.products_status, 
                       IF(s.status, s.specials_new_products_price, NULL) 
                       AS 
                       specials_new_products_price, 
                       IF(s.status, s.specials_new_products_price, 
                       p.products_price) AS 
                       final_price, 
                       IF(p.clearance_price < p.products_cost * 2.25, 
                       p.clearance_price, 
                       p.products_cost * 2.25) 
                       AS 
                       sorting_price 
                FROM   products p 
                       LEFT JOIN specials s 
                              ON p.products_id = s.products_id 
                       LEFT JOIN products_description pd 
                              ON p.products_id = pd.products_id 
                WHERE 
                 /*FASTIDS*/ 
                 p.products_status = '1' 
                 AND Date_sub('2016-04-19', INTERVAL 7000 day) <= 
                     p.products_date_added 
               ) m 
        WHERE  m.products_id = pa.products_id 
               AND pa.options_id = 1 
               AND pa.options_values_id = pov.products_options_values_id 
               AND pov.language_id = '1') q 
WHERE  q.products_id = ps.products_id 
       AND ps.products_stock_attributes = 
           Concat('1-', pav.products_options_values_id) 
       AND ps.products_stock_quantity > 0 
ORDER  BY pav.products_options_values_sort_order ASC 

Here are the EXPLAIN EXTENDED results. Seems like the optimizer is working differently on both tables, but that doesn't really explain why that happens, as the copied database is supposedly identical.

Slow database's EXPLAIN

Fast database's EXPLAIN

These are the screenshots of the profiling of the query on the slow and fast database:

Slow database profiling Fast database profiling

What can be the possible causes for this huge discrepancy and how can they be verified and fixed?

P.S. I posted this question on stackexchange and was advised to ask the experts here.

  • Please provide links between this question and the SE one. – Rick James Apr 23 '16 at 20:49
  • Has anyone ever seen the top item in a Profile be anything other than "Copying to tmp table" or "Sending data"? – Rick James Apr 27 '16 at 4:28
  • @RickJames - were you finally able to reproduce this? Everyone who tested it were able to. – Nikita 웃 Apr 27 '16 at 12:26
0

I suppose you have used Innodb storage engine. Its probably due to the log file size of database. MySQL MariaDb both store the data on log first and then pushed forward to its own files later while service stop event etc. With time log file size increases, and that affects DB load, selecting and all.

If its new DB then log files are small, less fragmentation of data. So speedy.

  • Thanks. It's myisam as I wrote and I can reproduce the issue even if I copy the copied database. The title of this question is misleading, please read the entire question including all the edits thoroughly. – Nikita 웃 Apr 21 '16 at 8:30
  • oh, did u tried mysiampack? – Mrigesh Raj Shrestha Apr 21 '16 at 8:34
  • No, but I don't see how it's related to my question. Please kindly read it thoroughly. – Nikita 웃 Apr 21 '16 at 8:37
  • as far as I know,every storage engine on MySQL uses log files. those mysql-bin.000.. files are probably the ones. Innodb has its own ib data logs. – Mrigesh Raj Shrestha Apr 21 '16 at 8:39
  • I also had similar experiences in MySQL and elsewhere also; remember what its like after your reinstall windows or reset ur fone. Goodluck for the right answer ;) – Mrigesh Raj Shrestha Apr 21 '16 at 8:41
0

Seems like I forgot to answer my own question for a while. Apparently I have discovered quite a serious bug in the MySQL/MariaDB engine and/or optimizer.

This bug has been reported (both to Oracle and Maria) and reproduced (confirmed) by MariaDB staff (Elena Stepanova), who already assigned it to one of their lead developers (Sergei Petrunia) and Varun Gupta. I believe it will probably be fixed quite soon, as the report was made on 2016 and their patch seems to be in final stages ("In Review" at the time of writing this): https://jira.mariadb.org/browse/MDEV-9959

With Oracle tho, well, a different story entirely...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.