Eu tenho uma tabela com cerca de 8,5 m de linhas. A tabela é tokudb e possui os índices descritos abaixo. Estou tendo um desempenho ruim ao tentar executar instruções de atualização como as seguintes:
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'
Leva mais de 40 segundos para executar esta atualização. Existem outras atualizações como essa acontecendo com frequência, mas o subsistema de E/S desta máquina não está sendo estressado nem um pouco (SSDs invadidos) e também há bastante RAM disponível.
EXPLAINrendimentos:
+----+-------------+-------------------+------------+-------+------------------------------------------------------------+---------+---------+------+------+----------+------------------------------+
| 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)
Com base nisso - está escolhendo o PRIMARYíndice em vez de um dos outros que, por exemplo, cl_unique_idxtem as duas colunas na instrução where nas duas primeiras posições. Portanto, estou perplexo com o motivo pelo qual o planejador está escolhendo o PRIMARYlugar e fazendo com que o desempenho seja tão ruim. Segue abaixo a lista dos índices:
+-------------------+------------+--------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| 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)
Eu tive que aumentar tokudb_lock_timeoutde 4 segundos para 40 segundos para não ter um monte de contenção de espera de bloqueio. Estou perdendo alguma coisa aqui?
Definição de tabela
`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
O número de linhas atualizadas deve ser de 1 a 3 no máximo para cada atualização. As atualizações podem acontecer a uma taxa de provavelmente 1 por segundo para talvez 3-4 talvez até várias dezenas por segundo em geral.
Isso está no Percona Server 5.7.