Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-9959

A serious MariaDB server performance bug

    Details

      Description

      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) and uploaded it to the private folder on MariaDB ftp.

      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.

      http://i.stack.imgur.com/NQ9YQ.png
      http://i.stack.imgur.com/pLyOt.png

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

      http://i.stack.imgur.com/JBlwP.png
      http://i.stack.imgur.com/E1qf0.png

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

          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
      

        Attachments

          Activity

            People

            • Assignee:
              psergey Sergei Petrunia
              Reporter:
              Yo Ami
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated: