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.
Ao contrário do InnoDB, o TokuDB historicamente não computava automaticamente as estatísticas de cardinalidade. Como usuário, você precisava executar manualmente
ANALYZE TABLEpara calcular esses valores.Todas as tabelas e índices criados antes de 5.6.27-76.0 também não manteriam contagens de linha precisas. Depois de 5.6.27-76.0, novas tabelas e índices, e tabelas que tinham
RECOUNT ROWSanálise, rastreariam contagens de linhas com precisão. Isso é muito importante para métricas de cardinalidade e particularmente para cardinalidade com tabelas particionadas.Consulte os seguintes documentos que descrevem as alterações da análise:
Antes da versão 5.7.11-4, a análise automática de plano de fundo era desativada por padrão. De 5.7.11-4 em diante, a análise automática de fundo é habilitada por padrão quando ~30% da tabela foi alterada (inserir/atualizar/excluir). Você pode alterar esse limite e vários outros aspectos da análise manipulando as várias variáveis do sistema documentadas nos links acima.
O recarregamento de seus dados em um servidor mais recente que 5.6.27-76.0 teria corrigido as contagens de linhas imprecisas e a mudança para 5.7.11-4 teria habilitado a análise automática de segundo plano.
Se você for usar o TokuDB, deve ter certeza de seus motivos, o TokuDB não é 'apenas melhor que o InnoDB para todas as cargas'. Ele tem vantagens e compensações específicas e casos de uso em que não funcionará tão bem quanto o InnoDB e, em geral, não é tão maduro quanto o InnoDB.
Se você precisar de compactação, tiver uma carga de inserção pesada, armazenamento lento ou se o conjunto de dados exceder muito a memória disponível, o TokuDB pode ser uma boa opção. Se você precisa de desempenho bruto de consulta de ponto aleatório, tem exclusões sequenciais pesadas seguidas de consultas de cobertura, tem char/varchar/blobs grandes (> 32K), tem bastante armazenamento rápido (embora o TokuDB possa reduzir o desgaste do flash) ou tem um pequeno conjunto de dados que é um pequeno múltiplo do tamanho da memória física, o TokuDB provavelmente não é para você.
Também notei agora que você diz que tem apenas 100 GB de dados, mas 500 GB de memória (com pool de buffer innodb de 100 GB). Este é um caso em que a maioria/todos os seus dados caberão na memória. O InnoDB deve ser o claro vencedor de desempenho aqui. O TokuDB (ainda) não é otimizado para cargas de trabalho na memória e o InnoDB o vencerá quase 100% do tempo nessa situação. Agora, se você tivesse 100 GB de memória e um TB de dados e índices, valeria a pena considerar o TokuDB.
(Sou engenheiro de software na Percona.)
Por fim, um despejo da tabela e o recarregamento resolveram a estranha cardinalidade e o comportamento da contagem de linhas. Tentamos usar a tabela de análise, mas isso não resolveu o problema. A resposta de George é muito bem feita, mas infelizmente não resolveria meu problema.