0

We had a very strange encounter that all of our queries that uses an indexed column in the select wasn't returning any results. On the other hand, if we do select * or on any other non-indexed column then we get the expected results.

For example,

SELECT product_id FROM product where product_id = 1; 

doesn't return any results.

But if we do this:

SELECT * FROM product where product_id = 1;

We get the expected results.

The table engine used to be MyISAM, however, when we changed it to InnoDB we started having these issues. We had to change it back to MyISAM because a lot of websites were down because of the above issue.

The server specs:

  • Ubuntu 16.04 LTS
  • Mysql 5.6
  • 27GB RAM
  • 300 GB Free space while using 100 GB

My.cnf:

  1. innodb_log_file_size = 3G
  2. innodb_buffer_pool_instances = 21
  3. key_buffer_size = 500M
  4. innodb_buffer_pool_size = 22G
  5. sort_buffer_size = 512K
  6. read_rnd_buffer_size = 512K
  7. thread_cache_size = 9
  8. thread_cache_size = 9
  9. innodb_file_per_table = 1
  10. innodb_buffer_pool_dump_at_shutdown = 1
  11. innodb_buffer_pool_dump_at_shutdown = 1

We didn't have any errors on the application side and no error logs in Mysql. The server had 6 GB of Free memory so we weren't running out of memory. The above recommendations were made by mysqltunner.pl

Results from Mysqltunner:

[OK] Slow queries: 0%    
[OK] Thread cache hit rate: 93%        
[OK] Table cache hit rate: 95%     
[OK] Open file limit used: 0%       
[OK] InnoDB File per table is activated        
[OK] InnoDB buffer pool / data size: 20.0G/21.3G        
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 3G *     2/20G should be equal 25%        
[OK] InnoDB buffer pool instances: 21       
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 99.95%        
[OK] InnoDB Write Log efficiency:  93%    
[OK] InnoDB log waits: 0.00% (0 waits / 68 writes)        

Edit 1:

  1. Query used to convert MyISAM to InnoDb

    ALTER TABLE `product` ENGINE=InnoDB; 
    
  2. Table Schema:

     CREATE TABLE `product` (
      `product_id` int(11) NOT NULL,
      `product_type` int(11) NOT NULL DEFAULT '1',
      `product_quantity` float NOT NULL DEFAULT '0',
      `product_model` varchar(32) DEFAULT NULL,
      `product_sku` varchar(255) DEFAULT NULL,
      `product_label` tinyint(4) NOT NULL,
      `product_flavor` tinyint(4) NOT NULL,
      `product_new` tinyint(1) NOT NULL,
      `product_sale` tinyint(1) NOT NULL,
      `product_image` varchar(64) DEFAULT NULL,
      `product_price` decimal(15,4) NOT NULL DEFAULT '0.0000',
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    
    ALTER TABLE `product` ADD PRIMARY KEY (`products_id`),
    
    -- Added the FullText key when we changed it to InnoDB 
    ALTER TABLE `product` ADD FULLTEXT KEY `model_index` (`products_model`);
    
  • Please provide SHOW CREATE TABLE product; -- both as MyISAM and InnoDB. Also provide the steps you used to change engines. – Rick James May 8 at 0:37
  • @RickJames, Thanks, I updated my post. The only thing that was changed was the engine and we added FullText to the index. – Mido May 8 at 2:45
1

I wonder what would happen if you ran OPTIMIZE TABLE product; on your MySQL server. OPTIMIZE TABLE will “rebuild” the physical storage of your table, including the indexes.

https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html

  • I can't remember if we used this, however, we ran ANALYZE on the table and it returned OK. – Mido May 8 at 2:46
0

Suggestions to consider for your my.cnf [mysqld] section

innodb_lru_scan_depth=100  # from 1024 to conserve 90% of CPU cycles used for function
innodb_page_cleaners=21  # to match your number of bp instances of 21
innodb_purge_threads=21  # to match your number of bp instances of 21
innodb_flushing_avg_loops=5  # to minimize loop delay 

Disclaimer: I am the web content author of web site mentioned in my profile, Network profile and can provide additional suggestions and FREE Utility Scripts to improve performance.

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.