0

I have actually installed percona mysql and trying to move from oracle mysql. I have been using innodb for quite some time. I heard and read a lot of about tokudb being able to support large db etc. So I did try to give it a try. It has been working fine suddenly today I run yum update and it stopped working.

Below is my mysqld.cnf

# Percona Server template configuration

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#default-storage-engine=tokudb

Initially I made tokudb as my default storage after the update everything is messed and I had comment this line #default-storage-engine=tokudb only then my mysql started.

Here is how my updates looks when I ran the yum update.

 Updating   : Percona-Server-shared-compat-57-5.7.18-14.1.el7.x86_64      1/10
  Updating   : Percona-Server-shared-57-5.7.18-14.1.el7.x86_64             2/10
  Updating   : Percona-Server-client-57-5.7.18-14.1.el7.x86_64             3/10
  Updating   : Percona-Server-server-57-5.7.18-14.1.el7.x86_64             4/10
 -------------
   *  The suggested mysql options and settings are in /etc/percona-server.conf.d/mysqld.cnf
   *  If you want to use mysqld.cnf as default configuration file please make backup of /etc/my.cnf
   *  Once it is done please execute the following commands:
 rm -rf /etc/my.cnf
 update-alternatives --install /etc/my.cnf my.cnf "/etc/percona-server.cnf" 200
 -------------
Percona Server is distributed with several useful UDF (User Defined Function) from Percona Toolkit.
Run the following commands to create these functions:
mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'"
mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'"
mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'"
See http://www.percona.com/doc/percona-server/5.7/management/udf_percona_toolkit.html for more details
  Updating   : Percona-Server-tokudb-57-5.7.18-14.1.el7.x86_64             5/10
  Cleanup    : Percona-Server-tokudb-57-5.7.17-13.1.el7.x86_64             6/10
  Cleanup    : Percona-Server-server-57-5.7.17-13.1.el7.x86_64             7/10
  Cleanup    : Percona-Server-client-57-5.7.17-13.1.el7.x86_64             8/10
  Cleanup    : Percona-Server-shared-57-5.7.17-13.1.el7.x86_64             9/10
  Cleanup    : Percona-Server-shared-compat-57-5.7.17-13.1.el7.x86_64     10/10
  Verifying  : Percona-Server-tokudb-57-5.7.18-14.1.el7.x86_64             1/10
  Verifying  : Percona-Server-shared-57-5.7.18-14.1.el7.x86_64             2/10
  Verifying  : Percona-Server-server-57-5.7.18-14.1.el7.x86_64             3/10
  Verifying  : Percona-Server-shared-compat-57-5.7.18-14.1.el7.x86_64      4/10
  Verifying  : Percona-Server-client-57-5.7.18-14.1.el7.x86_64             5/10
  Verifying  : Percona-Server-server-57-5.7.17-13.1.el7.x86_64             6/10
  Verifying  : Percona-Server-shared-compat-57-5.7.17-13.1.el7.x86_64      7/10
  Verifying  : Percona-Server-tokudb-57-5.7.17-13.1.el7.x86_64             8/10
  Verifying  : Percona-Server-shared-57-5.7.17-13.1.el7.x86_64             9/10
  Verifying  : Percona-Server-client-57-5.7.17-13.1.el7.x86_64            10/10

Updated:
  Percona-Server-client-57.x86_64 0:5.7.18-14.1.el7
  Percona-Server-server-57.x86_64 0:5.7.18-14.1.el7
  Percona-Server-shared-57.x86_64 0:5.7.18-14.1.el7
  Percona-Server-shared-compat-57.x86_64 0:5.7.18-14.1.el7
  Percona-Server-tokudb-57.x86_64 0:5.7.18-14.1.el7

Complete!

Here is my current mysql.log.

2017-05-15T06:03:04.579874Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-05-15T06:03:04.581301Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.18-14) starting as process 2454 ...
2017-05-15T06:03:04.585394Z 0 [Note] InnoDB: PUNCH HOLE support available
2017-05-15T06:03:04.585425Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-05-15T06:03:04.585433Z 0 [Note] InnoDB: Uses event mutexes
2017-05-15T06:03:04.585440Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2017-05-15T06:03:04.585449Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.7
2017-05-15T06:03:04.585460Z 0 [Note] InnoDB: Using Linux native AIO
2017-05-15T06:03:04.585733Z 0 [Note] InnoDB: Number of pools: 1
2017-05-15T06:03:04.585851Z 0 [Note] InnoDB: Using CPU crc32 instructions
2017-05-15T06:03:04.587208Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2017-05-15T06:03:04.591145Z 0 [Note] InnoDB: Completed initialization of buffer pool
2017-05-15T06:03:04.592947Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2017-05-15T06:03:04.602996Z 0 [Note] InnoDB: The first innodb_system data file 'ibdata1' did not exist. A new tablespace will be created!
2017-05-15T06:03:04.603164Z 0 [Note] InnoDB: Setting file './ibdata1' size to 12 MB. Physically writing the file full; Please wait ...
2017-05-15T06:03:04.762580Z 0 [Note] InnoDB: File './ibdata1' size is now 12 MB.
2017-05-15T06:03:04.763040Z 0 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2017-05-15T06:03:05.221084Z 0 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2017-05-15T06:03:05.862770Z 0 [Note] InnoDB: Created parallel doublewrite buffer at /var/lib/mysql/xb_doublewrite, size 3932160 bytes
2017-05-15T06:03:05.946047Z 0 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2017-05-15T06:03:05.946134Z 0 [Warning] InnoDB: New log files created, LSN=45790
2017-05-15T06:03:05.946160Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2017-05-15T06:03:05.946222Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2017-05-15T06:03:06.096069Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2017-05-15T06:03:06.096267Z 0 [Note] InnoDB: Doublewrite buffer not found: creating new
2017-05-15T06:03:06.296244Z 0 [Note] InnoDB: Doublewrite buffer created
2017-05-15T06:03:06.310384Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2017-05-15T06:03:06.310410Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2017-05-15T06:03:06.310601Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2017-05-15T06:03:06.354573Z 0 [Note] InnoDB: Foreign key constraint system tables created
2017-05-15T06:03:06.354637Z 0 [Note] InnoDB: Creating tablespace and datafile system tables.
2017-05-15T06:03:06.387884Z 0 [Note] InnoDB: Tablespace and datafile system tables created.
2017-05-15T06:03:06.387937Z 0 [Note] InnoDB: Creating sys_virtual system tables.
2017-05-15T06:03:06.421245Z 0 [Note] InnoDB: sys_virtual table created
2017-05-15T06:03:06.421303Z 0 [Note] InnoDB: Creating zip_dict and zip_dict_cols system tables.
2017-05-15T06:03:06.454581Z 0 [Note] InnoDB: zip_dict and zip_dict_cols system tables created.
2017-05-15T06:03:06.454793Z 0 [Note] InnoDB: Waiting for purge to start
2017-05-15T06:03:06.504972Z 0 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.7.18-14 started; log sequence number 0
2017-05-15T06:03:06.505428Z 0 [Note] Plugin 'FEDERATED' is disabled.
2017-05-15T06:03:06.510037Z 0 [Warning] InnoDB: Cannot open table mysql/plugin from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
mysqld: Table 'mysql.plugin' doesn't exist
2017-05-15T06:03:06.510134Z 0 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2017-05-15T06:03:06.511046Z 0 [Warning] InnoDB: Cannot open table mysql/gtid_executed from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
mysqld: Table 'mysql.gtid_executed' doesn't exist
2017-05-15T06:03:06.511091Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2017-05-15T06:03:06.514560Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2017-05-15T06:03:06.514581Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
2017-05-15T06:03:06.536783Z 0 [Warning] CA certificate ca.pem is self signed.
2017-05-15T06:03:06.536866Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2017-05-15T06:03:06.537595Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2017-05-15T06:03:06.537649Z 0 [Note] IPv6 is available.
2017-05-15T06:03:06.537671Z 0 [Note]   - '::' resolves to '::';
2017-05-15T06:03:06.537754Z 0 [Note] Server socket created on IP: '::'.
2017-05-15T06:03:06.571570Z 0 [Warning] InnoDB: Cannot open table mysql/server_cost from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2017-05-15T06:03:06.571611Z 0 [Warning] Failed to open optimizer cost constant tables

2017-05-15T06:03:06.572621Z 0 [Warning] InnoDB: Cannot open table mysql/time_zone_leap_second from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2017-05-15T06:03:06.572655Z 0 [Warning] Can't open and lock time zone table: Table 'mysql.time_zone_leap_second' doesn't exist trying to live without them
2017-05-15T06:03:06.573327Z 0 [Warning] InnoDB: Cannot open table mysql/servers from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2017-05-15T06:03:06.573357Z 0 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
2017-05-15T06:03:06.582207Z 0 [Note] Event Scheduler: Loaded 0 events
2017-05-15T06:03:06.582530Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.18-14'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Percona Server (GPL), Release 14, Revision 2c06f4d
2017-05-15T06:03:06.582552Z 0 [Note] Executing 'SELECT * FROM INFORMATION_SCHEMA.TABLES;' to get a list of tables using the deprecated partition engine. You may use the startup option '--disable-partition-engine-check' to skip this check. 
2017-05-15T06:03:06.582560Z 0 [Note] Beginning of list of non-natively partitioned tables
2017-05-15T06:03:06.606414Z 0 [Note] End of list of non-natively partitioned tables
2017-05-15T06:10:22.125681Z 5 [Warning] InnoDB: Cannot open table mysql/plugin from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2017-05-15T06:14:47.492149Z 8 [Warning] InnoDB: Cannot open table mysql/plugin from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2017-05-15T06:16:05.397217Z 11 [Warning] InnoDB: Cannot open table mysql/plugin from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.

What is the best solution moving forward should I drop tokudb and just continue with innodb or is it a bug with percona itself?

1

I recommend you

  • keep default-storage-engine=tokudb commented out
  • manually specify ENGINE=tokudb only on tables that will benefit from it
  • keep InnoDB for other tables
  • file a bug report

For specific queries

With InnoDB (and probably TokuDB):

Select stuff (whatever is in the table) about one vehicle for one time or a time range:

INDEX(vehicle_id, datetime)

Select stuff for a particular vehicle in a particular fleet (assuming the above does not suffice):

INDEX(vehicle_id, fleet_id, datetime)  -- or --
INDEX(fleet_id, vehicle_id, datetime)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.