Percona 5.7 w/ TokuDB - "Got Error 1152 from storage engine" ErrorNr 1030

MySQL & MariaDB Percona Server for MySQL 5.7
1

Hi - we have recently moved one of our databases to its own server and migrated from an InnoDB table structure to TokuDB to test a number of purported improvements. The older server was a mySQL 5.6.x version and we upgraded to 5.7 when loading in the data from a flatfile dump. All of the standard b-tree indexes were recreated on the load-in and we opted for a pretty standard my.cnf setup.

The server itself has an upgraded IO subsystem (Dell R910 H730p with SATA SSD - EVO850s) and there is no contention contention for memory either (500GB of RAM).

Since migrating this database, we’ve had nothing but trouble. Query performance compared to the prior database has been not good - with inexplicable index choices and queries that took under a second to run on large tables taking upwards of 20 minutes.

Today, we got this error (subject line) “Got Error 1152 from storage engine” to which I can’t find nearly any reference to for tracking down the problem.

At this point I’m kinda at a loss as to where to begin - I’ve changed the hardware (which should be for the better, but might not I suppose), I’ve upgraded the mySQL and changed the storage engine.

Here is an example of my perplexity with what is going on prior to this new 1152 error - this is me posting on stackexchange.

[URL=“mysql - Percona 5.7 tokudb poor query performance - wrong (non-clustered) index chosen - Database Administrators Stack Exchange”]http://dba.stackexchange.com/questio...d-index-chosen[/URL]

I’m at a loss because we have a 5.6 InnoDB Percona slave that consistently outperforms a vanilla Oracle dist with the exact same data and setup, where do I begin to debug this nightmare?

Below is the my.cnf

[mysqld]
pid-file=/var/run/mysqld/mysqld.pid
log-error=/var/log/mysqld.log
datadir=/bigguy/mysql1
socket=/var/lib/mysql/mysql.sock
user=mysql

Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0
validate_password_policy = LOW

innodb_file_per_table=1
max_allowed_packet=500M
innodb_buffer_pool_size = 100G
innodb_buffer_pool_dump_at_shutdown = 1 # dump cache pool for re-warming on restart
innodb_buffer_pool_load_at_startup = 1 # reload cache pool on startup

innodb_flush_log_at_trx_commit=2 # reset to 0 when done import
innodb_doublewrite=0 # remove when done import
innodb_log_file_size = 1024M
innodb_flush_method = O_DIRECT
innodb_buffer_pool_instances=8
innodb_thread_concurrency=8

tokudb_directio=1

query_cache_type=2 # on-demand only
query_cache_size=67108864
query_cache_limit=4194304
max_connections=2048
max_allowed_packet=500M

port = 3306
server_id=1021220
relay-log-recovery=1 # [url]http://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#option_mysqld_relay-log-recovery[/url]
master-info-repository=TABLE # [url]http://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#option_mysqld_master-info-repository[/url]
relay-log-info-repository=TABLE
sync_master_info=10000
slave_net_timeout=30 # shorten the connection retry to 1 minute from the default 1 HOUR!!!
slave-skip-errors = 1062 # ignore duplicate key insertions
expire_logs_days = 14 # expunge log every 2 weeks (set to lower if your db is busy or freespahce is small
performance_schema=ON # reset to ON when done import
slow_query_log=0
slow_query_log_file=slow.log
long_query_time=10

log_bin=/bigguy/mysql1/binlogs
sync_binlog=1
max_binlog_size=500M
log_slave_updates = 1
relay_log = mysql-relay-bin
binlog_format = ROW
log_bin_trust_function_creators=TRUE
gtid_mode=OFF

max-connections = 1000
thread-cache-size = 100
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 10240

join_buffer_size = 1M
sort_buffer_size = 1M
read_rnd_buffer_size = 8M
bulk_insert_buffer_size=512M
sql_mode=NO_ENGINE_SUBSTITUTION,NO_UNSIGNED_SUBTRACTION

[mysqld_safe]
thp-setting=never
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

The database is about 100GB total. There are several tables with many indexes and about 7-10m rows.

2

And here is another interesting output from show indexes - I’ll post the table def and its indexes, but the question here is - why are the index cardinality all 0, why does the index type show as btree when some are clustered?

mysql> show index from retail.item_discovery;
±------------------±-----------±--------------------±-------------±------------------±----------±------------±---------±-------±-----±-----------±--------±--------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
±------------------±-----------±--------------------±-------------±------------------±----------±------------±---------±-------±-----±-----------±--------±--------------+
| item_discovery | 0 | PRIMARY | 1 | itd_id | A | 0 | NULL | NULL | | BTREE | | |
| item_discovery | 0 | cl_uni_idx | 1 | sku | A | 0 | NULL | NULL | | BTREE | | |
| item_discovery | 0 | cl_uni_idx | 2 | market | A | 0 | NULL | NULL | | BTREE | | |
| item_discovery | 0 | cl_uni_idx | 3 | upc | A | 0 | NULL | NULL | | BTREE | | |
| item_discovery | 0 | cl_uni_idx | 4 | item_id | A | 0 | NULL | NULL | | BTREE | | |
| item_discovery | 0 | cl_uni_idx | 5 | model_num | A | 0 | NULL | NULL | | BTREE | | |
| item_discovery | 1 | web_cat_idx | 1 | web_category | A | 0 | NULL | NULL | | BTREE | | |
| item_discovery | 1 | web_cat_idx | 2 | available | A | 0 | NULL | NULL | | BTREE | | |
| item_discovery | 1 | web_cat_idx | 3 | discontinued | A | 0 | NULL | NULL | | BTREE | | |
| item_discovery | 1 | price_update_idx | 1 | last_price_change | A | 0 | NULL | NULL | | BTREE | | |
| item_discovery | 1 | price_update_idx | 2 | sku | A | 0 | NULL | NULL | | BTREE | | |
| item_discovery | 1 | price_update_idx | 3 | web_category | A | 0 | NULL | NULL | | BTREE | | |
| item_discovery | 1 | item_idx | 1 | item_id | A | 0 | NULL | NULL | | BTREE | | |
| item_discovery | 1 | item_idx | 2 | market | A | 0 | NULL | NULL | | BTREE | | |
| item_discovery | 1 | updated_idx | 1 | last_updated | A | 0 | NULL | NULL | | BTREE | | |
| item_discovery | 1 | cl1_itemid_mkt | 1 | item_id | A | 0 | NULL | NULL | | BTREE | | |
| item_discovery | 1 | cl1_itemid_mkt | 2 | market | A | 0 | NULL | NULL | | BTREE | | |
| item_discovery | 1 | cl_sku_mkt_last_idx | 1 | market | A | 0 | NULL | NULL | | BTREE | | |
| item_discovery | 1 | cl_sku_mkt_last_idx | 2 | sku | A | 0 | NULL | NULL | | BTREE | | |
| item_discovery | 1 | cl_sku_mkt_last_idx | 3 | last_updated | A | 0 | NULL | NULL | | BTREE | | |
| item_discovery | 1 | cl_sku_mkt_last_idx | 4 | available | A | 0 | NULL | NULL | | BTREE | | |
| item_discovery | 1 | sku_mkt_last_idx | 1 | sku | A | 0 | NULL | NULL | | BTREE | | |
| item_discovery | 1 | sku_mkt_last_idx | 2 | market | A | 0 | NULL | NULL | | BTREE | | |
| item_discovery | 1 | sku_mkt_last_idx | 3 | last_updated | A | 0 | NULL | NULL | | BTREE | | |
| item_discovery | 1 | sku_idx | 1 | market | A | 0 | NULL | NULL | | BTREE | | |
| item_discovery | 1 | sku_idx | 2 | sku | A | 0 | NULL | NULL | | BTREE | | |
| item_discovery | 1 | sku_idx | 3 | store_shopped | A | 0 | NULL | NULL | | BTREE | | |
| item_discovery | 1 | rating_updated_idx | 1 | rating | A | 0 | NULL | NULL | | BTREE | | |
| item_discovery | 1 | rating_updated_idx | 2 | last_updated | A | 0 | NULL | NULL | | BTREE | | |
| item_discovery | 1 | market_updated_idx | 1 | market | A | 0 | NULL | NULL | | BTREE | | |
| item_discovery | 1 | market_updated_idx | 2 | last_updated | A | 0 | NULL | NULL | | BTREE | | |
| item_discovery | 1 | market_updated_idx | 3 | sku | A | 0 | NULL | NULL | | BTREE | | |
| item_discovery | 1 | cl_item_updated_idx | 1 | item_id | A | 0 | NULL | NULL | | BTREE | | |
| item_discovery | 1 | cl_item_updated_idx | 2 | last_updated | A | 0 | NULL | NULL | | BTREE | | |
| item_discovery | 1 | cl_item_updated_idx | 3 | available | A | 0 | NULL | NULL | | BTREE | | |
| item_discovery | 1 | cl_item_updated_idx | 4 | market | A | 0 | NULL | NULL | | BTREE | | |
±------------------±-----------±--------------------±-------------±------------------±----------±------------±---------±-------±-----±-----------±--------±--------------+

CREATE TABLE item_discovery (
item_id int(10) unsigned NOT NULL DEFAULT ‘0’,
chain varchar(12) NOT NULL DEFAULT ‘’,
market varchar(4) NOT NULL DEFAULT ‘0’,
available varchar(1) NOT NULL DEFAULT ‘y’ COMMENT ‘will either by y or n’,
last_updated date NOT NULL DEFAULT ‘1990-01-01’,
itd_id int(10) unsigned NOT NULL AUTO_INCREMENT,
web_description varchar(255) NOT NULL DEFAULT ‘’,
model_num varchar(60) NOT NULL DEFAULT ‘’,
price decimal(8,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 COMMENT ‘the first time the item was seen in the market’,
discontinued varchar(1) NOT NULL DEFAULT ‘n’ COMMENT ‘n’,
discontinued_date date NOT NULL DEFAULT ‘0000-00-00’,
vendor_number int(10) unsigned NOT NULL DEFAULT ‘0’,
last_price_change date NOT NULL DEFAULT ‘0000-00-00’ COMMENT ‘the date of the last time the price changed’,
so_sku int(10) unsigned NOT NULL DEFAULT ‘0’,
available_online varchar(1) NOT NULL DEFAULT ‘y’,
prev_price decimal(8,2) NOT NULL DEFAULT ‘0.00’,
rating decimal(4,2) unsigned NOT NULL DEFAULT ‘0.00’,
review_count int(11) NOT NULL DEFAULT ‘-1’,
PRIMARY KEY (itd_id),
UNIQUE KEY cl_uni_idx (sku,market,upc,item_id,model_num),
KEY web_cat_idx (web_category,available,discontinued) USING BTREE,
KEY price_update_idx (last_price_change,sku,web_category),
KEY item_idx (item_id,market) USING BTREE,
KEY updated_idx (last_updated),
CLUSTERING KEY cl1_itemid_mkt (item_id,market),
CLUSTERING KEY cl_sku_mkt_last_idx (market,sku,last_updated,available),
CLUSTERING KEY sku_mkt_last_idx (sku,market,last_updated),
KEY sku_idx (market,sku,store_shopped) USING BTREE,
KEY rating_updated_idx (rating,last_updated),
KEY market_updated_idx (market,last_updated,sku),
CLUSTERING KEY cl_item_updated_idx (item_id,last_updated,available,market)
) ENGINE=TokuDB AUTO_INCREMENT=17242202 DEFAULT CHARSET=latin1

3

So I did a dump,truncate,reload of the table above and now the number of rows reported in the table is correct, the cardinalities are correct and queries which still appear to using the same query execution plan now ACTUALLY use them. Needless to say, while I’m relieved this solved the problem, it makes me very nervous given that all the data was there before and simply was reloaded.

4

Unlike InnoDB, TokuDB historically did not automatically compute cardinality statistics. As a user you were required to manually run ANALYZE TABLE in order to calculate these values. All tables and indices created prior to 5.6.27-76.0 would also not maintain accurate row counts. After 5.6.27-76.0, new tables and indices, and tables that had RECOUNT ROWS analysis, would all accurately track row counts. This is very important for cardinality metrics and particularly for cardinality with partitioned tables.

Please see [url]Percona Server for MySQL 8.0 - Documentation — Percona Server 8.0 Documentation and [url]https://www.percona.com/doc/percona-server/5.7/tokudb/tokudb_background_analyze_table.html[/url] that describes the analysis changes.

Prior to 5.7.11-4, automatic background analysis was disabled by default. From 5.7.11-4 onward, automatic background analysis is enabled by default when ~30% of the table had been changed (insert/update/delete). You can change this threshold and several other aspects of the analysis by manipulating the various system variables documented in the links above.

The reloading of your data into a server newer than 5.6.27-76.0 would have corrected the inaccurate row counts and the move to 5.7.11-4 would have enabled the automatic background analysis.

If you are going to use TokuDB, you should be sure of your reasons, TokuDB is not ‘just better than InnoDB for all loads’. It has specific benefits and tradeoffs and use cases where it will not perform as well as InnoDB and in general is not as mature as InnoDB. If you need compression, have a heavy insert load, slow storage, or if your data set greatly outsizes your available memory, TokuDB may be a good fit. If you need raw random point query performance, have heavy sequential deletes followed by covering queries, have large char/varchar/blobs (> 32K), have plenty of fast storage (although TokuDB can reduce flash wear), or have a small data set that is a small multiple of physical memory size, TokuDB is probably not for you.

–
George O. Lorch III
Software Engineer, Percona

5

I also now notice that you say you have only 100GB of data but 500GB of memory (with 100GB innodb buffer pool). This is a case where most/all of your data will fit into memory. InnoDB should be the clear performance winner here. TokuDB is not (yet) optimized for in-memory workloads and InnoDB will beat it almost 100% of the time in this situation. Now, if you had 100GB of memory and a TB of data & indexes, then TokuDB would be worth considering.

–
George O. Lorch III
Software Engineer, Percona