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

**URL:** <https://forums.percona.com/t/percona-5-7-w-tokudb-got-error-1152-from-storage-engine-errornr-1030/4864>\
**Category:** Percona Server for MySQL 5.7\
**Created:** [April 29, 2016, 6:57pm UTC](https://forums.percona.com/t/percona-5-7-w-tokudb-got-error-1152-from-storage-engine-errornr-1030/4864 "2016-04-29T18:57:51Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![recarv](https://avatars.discourse-cdn.com/v4/letter/r/9d8465/32.png) [@recarv](https://forums.percona.com/u/recarv)\
**Post date:** [April 29, 2016, 6:57pm UTC](https://forums.percona.com/t/percona-5-7-w-tokudb-got-error-1152-from-storage-engine-errornr-1030/4864/1 "2016-04-29T18:57:51Z")

</div>

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.

[http://dba.stackexchange.com/questio…d-index-chosen](http://dba.stackexchange.com/questions/135180/percona-5-7-tokudb-poor-query-performance-wrong-non-clustered-index-chosen)

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 # [http://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#option\_mysqld\_relay-log-recovery](http://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#option_mysqld_relay-log-recovery)  
master-info-repository=TABLE # [http://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#option\_mysqld\_master-info-repository](http://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#option_mysqld_master-info-repository)  
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.

---

<div class="post-metadata">

**Author:** ![recarv](https://avatars.discourse-cdn.com/v4/letter/r/9d8465/32.png) [@recarv](https://forums.percona.com/u/recarv)\
**Post date:** [April 30, 2016, 9:55am UTC](https://forums.percona.com/t/percona-5-7-w-tokudb-got-error-1152-from-storage-engine-errornr-1030/4864/2 "2016-04-30T09:55:08Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![recarv](https://avatars.discourse-cdn.com/v4/letter/r/9d8465/32.png) [@recarv](https://forums.percona.com/u/recarv)\
**Post date:** [April 30, 2016, 4:28pm UTC](https://forums.percona.com/t/percona-5-7-w-tokudb-got-error-1152-from-storage-engine-errornr-1030/4864/3 "2016-04-30T16:28:47Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![George\_Lorch](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/george_lorch/32/1108_2.png) [@George\_Lorch](https://forums.percona.com/u/George_Lorch)\
**Post date:** [May 1, 2016, 10:52am UTC](https://forums.percona.com/t/percona-5-7-w-tokudb-got-error-1152-from-storage-engine-errornr-1030/4864/4 "2016-05-01T10:52:06Z")

</div>

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](https://www.percona.com/doc/percona-server/5.6/tokudb/tokudb_background_analyze_table.html%5B/url%5D) and [url][https://www.percona.com/doc/percona-server/5.7/tokudb/tokudb\_background\_analyze\_table.html[/url]](https://www.percona.com/doc/percona-server/5.7/tokudb/tokudb_background_analyze_table.html%5B/url%5D) 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

---

<div class="post-metadata">

**Author:** ![George\_Lorch](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/george_lorch/32/1108_2.png) [@George\_Lorch](https://forums.percona.com/u/George_Lorch)\
**Post date:** [May 1, 2016, 1:33pm UTC](https://forums.percona.com/t/percona-5-7-w-tokudb-got-error-1152-from-storage-engine-errornr-1030/4864/5 "2016-05-01T13:33:09Z")

</div>

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