I have a table with about 8.5m rows in it. The table is tokudb and it has the indexes described below. I'm experiencing dismal performance when trying to run update statements like the following:
update retail.lw_item_discovery
set price = 'X',
prev_price = 'Y',
last_updated = '2016-04-13',
last_price_change = '2016-04-13'
where market = 'XX'
and sku = '123456'
It takes upwards of 40 seconds to perform this update. There are other updates like this happening frequently, but the I/O subsystem of this machine is not being stressed in the least (raided SSDs) and there is plenty of RAM available as well.
EXPLAIN yields:
+----+-------------+-------------------+------------+-------+------------------------------------------------------------+---------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------------+------------+-------+------------------------------------------------------------+---------+---------+------+------+----------+------------------------------+
| 1 | UPDATE | lw_item_discovery | NULL | index | cl_unique_idx,cl_mkt_sku_upd_avail_idx,market_sku_item_idx | PRIMARY | 4 | NULL | 100 | 100.00 | Using where; Using temporary |
+----+-------------+-------------------+------------+-------+------------------------------------------------------------+---------+---------+------+------+----------+------------------------------+
1 row in set (0.00 sec)
Based on this - it's picking the PRIMARY index instead of one of the others which such as cl_unique_idx which have both columns in the where statement in the first two positions. So I'm stumped at why the planner is choosing the PRIMARY instead and causing the performance to be so poor. Below are a list of the indexes:
+-------------------+------------+--------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------------------+------------+--------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| lw_item_discovery | 0 | PRIMARY | 1 | itd_id | A | 82 | NULL | NULL | | BTREE | | |
| lw_item_discovery | 0 | cl_unique_idx | 1 | sku | A | 82 | NULL | NULL | | BTREE | | |
| lw_item_discovery | 0 | cl_unique_idx | 2 | market | A | 82 | NULL | NULL | | BTREE | | |
| lw_item_discovery | 0 | cl_unique_idx | 3 | upc | A | 82 | NULL | NULL | | BTREE | | |
| lw_item_discovery | 0 | cl_unique_idx | 4 | model_num | A | 82 | NULL | NULL | | BTREE | | |
| lw_item_discovery | 0 | cl_unique_idx | 5 | item_id | A | 82 | NULL | NULL | YES | BTREE | | |
| lw_item_discovery | 1 | update_idx | 1 | last_updated | A | 82 | NULL | NULL | | BTREE | | |
| lw_item_discovery | 1 | update_idx | 2 | market | A | 82 | NULL | NULL | | BTREE | | |
| lw_item_discovery | 1 | update_idx | 3 | sku | A | 82 | NULL | NULL | | BTREE | | |
| lw_item_discovery | 1 | description_idc | 1 | web_description | A | 82 | NULL | NULL | | BTREE | | |
| lw_item_discovery | 1 | category_idx | 1 | web_category | A | 82 | NULL | NULL | | BTREE | | |
| lw_item_discovery | 1 | category_idx | 2 | upc | A | 82 | NULL | NULL | | BTREE | | |
| lw_item_discovery | 1 | category_idx | 3 | sku | A | 82 | NULL | NULL | | BTREE | | |
| lw_item_discovery | 1 | upc_idx | 1 | upc | A | 82 | NULL | NULL | | BTREE | | |
| lw_item_discovery | 1 | item_id_idx | 1 | item_id | A | 82 | NULL | NULL | YES | BTREE | | |
| lw_item_discovery | 1 | item_id_idx | 2 | market | A | 82 | NULL | NULL | | BTREE | | |
| lw_item_discovery | 1 | item_id_idx | 3 | available | A | 82 | NULL | NULL | | BTREE | | |
| lw_item_discovery | 1 | cl_mkt_sku_upd_avail_idx | 1 | sku | A | 82 | NULL | NULL | | BTREE | | |
| lw_item_discovery | 1 | cl_mkt_sku_upd_avail_idx | 2 | market | A | 82 | NULL | NULL | | BTREE | | |
| lw_item_discovery | 1 | cl_mkt_sku_upd_avail_idx | 3 | last_updated | A | 82 | NULL | NULL | | BTREE | | |
| lw_item_discovery | 1 | cl_mkt_sku_upd_avail_idx | 4 | available | A | 82 | NULL | NULL | | BTREE | | |
| lw_item_discovery | 1 | market_sku_item_idx | 1 | market | A | 82 | NULL | NULL | | BTREE | | |
| lw_item_discovery | 1 | market_sku_item_idx | 2 | sku | A | 82 | NULL | NULL | | BTREE | | |
| lw_item_discovery | 1 | market_sku_item_idx | 3 | item_id | A | 82 | NULL | NULL | YES | BTREE | | |
+-------------------+------------+--------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
24 rows in set (0.00 sec)
I've had to increase the tokudb_lock_timeout from 4sec to 40sec in order to not have a bunch of lock wait contention. Am I missing something here?
Table definition
`lw_item_discovery` (
`item_id` bigint(20) unsigned DEFAULT '0',
`chain` varchar(12) NOT NULL DEFAULT 'lowes',
`market` varchar(4) NOT NULL DEFAULT '',
`available` varchar(1) NOT NULL DEFAULT 'y',
`last_updated` date NOT NULL DEFAULT '0000-00-00',
`itd_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`web_description` varchar(255) NOT NULL DEFAULT '',
`model_num` varchar(100) NOT NULL DEFAULT '' COMMENT 'its only 1char cause its not currently used. Its here for consistency',
`price` decimal(6,2) NOT NULL DEFAULT '0.00',
`item_link_url` text NOT NULL,
`item_img_url` text NOT NULL,
`store_shopped` smallint(5) unsigned NOT NULL DEFAULT '0',
`sku` varchar(32) NOT NULL DEFAULT '0',
`upc` varchar(12) NOT NULL DEFAULT '',
`web_category` varchar(255) NOT NULL DEFAULT '',
`mfr` varchar(100) NOT NULL DEFAULT '',
`class` tinyint(3) unsigned NOT NULL DEFAULT '0',
`subclass` tinyint(3) unsigned NOT NULL DEFAULT '0',
`first_found` date NOT NULL DEFAULT '0000-00-00' COMMENT 'first time it was seen in market',
`last_price_change` date NOT NULL DEFAULT '0000-00-00' COMMENT 'the date of the last price change observed',
`discontinued` varchar(1) NOT NULL DEFAULT 'n',
`discontinued_date` date NOT NULL DEFAULT '0000-00-00',
`prev_price` decimal(6,2) unsigned NOT NULL DEFAULT '0.00',
`rating` decimal(4,2) NOT NULL DEFAULT '-1.00',
`review_count` int(11) NOT NULL DEFAULT '-1',
PRIMARY KEY (`itd_id`),
UNIQUE KEY `cl_unique_idx` (`sku`,`market`,`upc`,`model_num`,`item_id`),
KEY `update_idx` (`last_updated`,`market`,`sku`),
KEY `description_idc` (`web_description`),
KEY `category_idx` (`web_category`,`upc`,`sku`),
KEY `upc_idx` (`upc`),
KEY `item_id_idx` (`item_id`,`market`,`available`) USING BTREE,
KEY `cl_mkt_sku_upd_avail_idx` (`sku`,`market`,`last_updated`,`available`),
CLUSTERING KEY `market_sku_item_idx` (`market`,`sku`,`item_id`)
) ENGINE=TokuDB AUTO_INCREMENT=8858224 DEFAULT CHARSET=latin1
The number of rows updated should be 1-3 max for each update. The updates can happen at a rate of probably 1 per second to perhaps 3-4 perhaps up to several dozen per second generally.
This is on Percona Server 5.7.
