Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.1.29
-
None
-
None
-
Debian stretch
Description
From time to time queries to tokudb table became very slow (10M rows table).
Show proccesslist shows that range queries are doing full table scans instead of using appropriate indexes.
Analyze table does not help.
Optimize table does not help.
The only solution is to ALTER TABLE engine=TokuDB;
show create table ended;
CREATE TABLE `ended` ( |
`item_id` int(11) NOT NULL DEFAULT '0', |
`bid` int(11) NOT NULL DEFAULT '0', |
`quant` int(11) NOT NULL DEFAULT '0', |
`answer` enum('0','1','2','3') NOT NULL DEFAULT '0', |
`id` int(11) NOT NULL AUTO_INCREMENT, |
`r_end` int(11) NOT NULL DEFAULT '0', |
`orders` int(11) NOT NULL DEFAULT '0', |
`o_s` double(16,2) DEFAULT NULL, |
`p_payed` enum('Y','N') NOT NULL DEFAULT 'N', |
`r_end_year` smallint(5) unsigned NOT NULL DEFAULT '2006', |
`r_end_month` tinyint(3) unsigned NOT NULL DEFAULT '10', |
`salesman_delete` enum('Y','N') NOT NULL DEFAULT 'N', |
`seller_note` mediumtext,
|
`buyer_note` mediumtext,
|
`user_seller` int(10) unsigned NOT NULL DEFAULT '0', |
`user_buyer` int(10) unsigned NOT NULL DEFAULT '0', |
`seller_partner` int(10) unsigned NOT NULL DEFAULT '0', |
`buyer_partner` int(10) unsigned NOT NULL DEFAULT '0', |
`archive` tinyint(4) NOT NULL DEFAULT '0', |
`master` enum('Y','N') NOT NULL DEFAULT 'Y', |
`num_of_items` int(10) unsigned NOT NULL DEFAULT '1', |
`buyer_delete` tinyint(4) NOT NULL DEFAULT '0', |
`ok` tinyint(4) NOT NULL DEFAULT '0', |
PRIMARY KEY (`id`,`archive`), |
KEY `index_bid` (`bid`), |
KEY `index_orders` (`orders`), |
KEY `r_end_index` (`r_end`), |
KEY `r_edn_y_i` (`r_end_year`), |
KEY `r_edn_m_i` (`r_end_month`), |
KEY `u_s_y_m_i` (`user_seller`,`r_end_year`,`r_end_month`), |
KEY `u_b_y_m_i` (`user_buyer`,`r_end_year`,`r_end_month`), |
KEY `s_p_p_p_i` (`seller_partner`,`p_payed`), |
KEY `b_p_p_p_r_e_d_i` (`buyer_partner`,`p_payed`,`r_end`), |
KEY `i_s_b_i` (`item_id`,`user_seller`,`user_buyer`), |
KEY `us_m_r_end_i` (`user_seller`,`master`,`r_end`), |
KEY `us_master_sd_r_end_i` (`user_seller`,`master`,`salesman_delete`,`r_end`), |
KEY `i_b_i` (`item_id`,`user_buyer`), |
KEY `ub_master_bd_r_end_i` (`user_buyer`,`master`,`buyer_delete`,`r_end`), |
KEY `a_m_a_e_end_i` (`archive`,`master`,`answer`,`r_end`), |
KEY `master_orders` (`master`,`orders`) |
) ENGINE=TokuDB AUTO_INCREMENT=11674820 DEFAULT CHARSET=utf8 |
/*!50100 PARTITION BY LIST (`archive`)
|
(PARTITION `current` VALUES IN (0) ENGINE = TokuDB,
|
PARTITION archive VALUES IN (1) ENGINE = TokuDB) */ |
Toku config:
plugin-load-add=ha_tokudb.so
|
tokudb_cache_size=128G
|
tokudb_commit_sync=ON
|
tokudb_fsync_log_period=0
|
tokudb_disable_prefetching=OFF
|
tokudb_bulk_fetch=ON
|
tokudb_lock_timeout=50000
|
 |
tokudb_checkpointing_period=60
|
tokudb_load_save_space=1
|
tokudb_data_dir=/db/main/toku/
|
tokudb_log_dir=/db/main/tokuLog/
|
tokudb_dir_per_db=ON
|
tokudb_auto_analyze=0
|
tokudb_analyze_in_background=ON
|