Not the answer you need?
Register and ask your own question!

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

recarvrecarv EntrantInactive User Role Beginner
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

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
master-info-repository=TABLE # 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.

Comments

  • recarvrecarv Entrant Inactive User Role Beginner
    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
  • recarvrecarv Entrant Inactive User Role Beginner
    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.
  • George LorchGeorge Lorch Percona Percona Moderator Role Patron
    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 https://www.percona.com/doc/percona-server/5.6/tokudb/tokudb_background_analyze_table.html and https://www.percona.com/doc/percona-server/5.7/tokudb/tokudb_background_analyze_table.html 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
  • George LorchGeorge Lorch Percona Percona Moderator Role Patron
    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
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.