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.
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
Disabling symbolic-links is recommended to prevent assorted security risks
validate_password_policy = LOW
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
query_cache_type=2 # on-demand only
port = 3306
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
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
log_slave_updates = 1
relay_log = mysql-relay-bin
binlog_format = ROW
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
The database is about 100GB total. There are several tables with many indexes and about 7-10m rows.