Configuration file for mysql and to enabled by default only tokudb

MySQL & MariaDB Other MySQL® Questions
1

Hi All,
I am very new to percona and managed to installed it on centos 7. My issue now

  1. I want to use just tokudb cause I heard a lot about it. But when I check the mysql log I keep seeing thing regarding innodb xtradb etc.

2017-04-27T17:08:43.509967Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-04-27T17:08:43.512090Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.17-13) starting as process 910 …
2017-04-27T17:08:43.592417Z 0 [Note] InnoDB: PUNCH HOLE support available
2017-04-27T17:08:43.592450Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-04-27T17:08:43.592459Z 0 [Note] InnoDB: Uses event mutexes
2017-04-27T17:08:43.592466Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2017-04-27T17:08:43.592472Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.7
2017-04-27T17:08:43.592481Z 0 [Note] InnoDB: Using Linux native AIO
2017-04-27T17:08:43.594880Z 0 [Note] InnoDB: Number of pools: 1
2017-04-27T17:08:43.598132Z 0 [Note] InnoDB: Using CPU crc32 instructions
2017-04-27T17:08:43.600222Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2017-04-27T17:08:43.605967Z 0 [Note] InnoDB: Completed initialization of buffer pool
2017-04-27T17:08:43.608516Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2017-04-27T17:08:43.662151Z 0 [Note] InnoDB: Crash recovery did not find the parallel doublewrite buffer at /var/lib/mysql/xb_doublewrite
2017-04-27T17:08:43.662976Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2017-04-27T17:08:43.826975Z 0 [Note] InnoDB: Created parallel doublewrite buffer at /var/lib/mysql/xb_doublewrite, size 3932160 bytes
2017-04-27T17:08:43.913607Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2017-04-27T17:08:43.913763Z 0 [Note] InnoDB: Setting file ‘./ibtmp1’ size to 12 MB. Physically writing the file full; Please wait …
2017-04-27T17:08:44.060336Z 0 [Note] InnoDB: File ‘./ibtmp1’ size is now 12 MB.
2017-04-27T17:08:44.062553Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2017-04-27T17:08:44.062583Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2017-04-27T17:08:44.063094Z 0 [Note] InnoDB: Waiting for purge to start
2017-04-27T17:08:44.113344Z 0 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.7.17-13 started; log sequence number 2500000
2017-04-27T17:08:44.114205Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2017-04-27T17:08:44.114601Z 0 [Note] Plugin ‘FEDERATED’ is disabled.
2017-04-27T17:08:44.185647Z 0 [Note] InnoDB: Buffer pool(s) load completed at 170427 13:08:44
2017-04-27T17:08:44.418694Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2017-04-27T17:08:44.418726Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
2017-04-27T17:08:44.443612Z 0 [Warning] CA certificate ca.pem is self signed.
2017-04-27T17:08:44.443705Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2017-04-27T17:08:44.455210Z 0 [Note] Server hostname (bind-address): ‘*’; port: 3306
2017-04-27T17:08:44.455455Z 0 [Note] IPv6 is available.
2017-04-27T17:08:44.455485Z 0 [Note] - ‘::’ resolves to ‘::’;
2017-04-27T17:08:44.455566Z 0 [Note] Server socket created on IP: ‘::’.
2017-04-27T17:08:44.596979Z 0 [Note] Event Scheduler: Loaded 0 events
2017-04-27T17:08:44.597210Z 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-04-27T17:08:44.597235Z 0 [Note] Beginning of list of non-natively partitioned tables
2017-04-27T17:08:44.723014Z 0 [Note] End of list of non-natively partitioned tables
2017-04-27T17:08:44.723167Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: ‘5.7.17-13’ socket: ‘/var/lib/mysql/mysql.sock’ port: 3306 Percona Server (GPL), Release 13, Revision fd33d43

  1. I go into my.cnf I just see these lines

The Percona Server 5.7 configuration file.

* IMPORTANT: Additional settings that can override those from this file!

The files must end with ‘.cnf’, otherwise they’ll be ignored.

Please make any edits and changes to the appropriate sectional files

included below.

!includedir /etc/my.cnf.d/
!includedir /etc/percona-server.conf.d/

I dont see any configuration details either in includedir /etc/my.cnf.d/ nor includedir /etc/percona-server.conf.d/

  1. So how to default my engine to tokudb and how set the variable related to it ?
2

Hello newtomysql,

  1. This part (innodb stuff in mysqld log) should not bother you because mysqld keeps all (enabled) storage engines loaded and you can use more then one in the same time.
  2. I have similar setup of config files but my percona-server.conf.d directory includes these two files also (you wrote that it’s empty which is strange and I’m not sure right now how that happened):

[vagrant@t-centos7-64 percona-server.conf.d]$ ls -alh
-rw-r--r-- 1 root root 874 Tra 3 10:18 mysqld.cnf
-rw-r--r-- 1 root root 442 Tra 3 10:18 mysqld_safe.cnf

Here’s the content of both:
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

mysqld_safe.cnf


#
# The Percona Server 5.7 configuration file.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld_safe]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
nice = 0
  1. You can use default-storage-engine=tokudb in the mysqld.conf config file but tokudb plugins need to be installed before that so if not already installed first install tokudb plugin, set the option in config file and then restart mysql service. If not sure how to install tokudb you can use ps_tokudb_admin helper script (it has --enable parameter but you’ll have to give your username and pass at least).

I hope this helps.

3

Hi Tplavic,
My apology. Yes these 2 files exists when I checked again.

  1. 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

  1. mysqld_safe.cnf

The Percona Server 5.7 configuration file.

One can use all long options that the program supports.

Run program with --help to get a list of available options and with

–print-defaults to see which it would actually understand and use.

For explanations see

[url]http://dev.mysql.com/doc/mysql/en/server-system-variables.html[/url]

[mysqld_safe]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
nice = 0

So you said ignore should not bother you because mysqld keeps all (enabled) storage engines loaded and you can use more then one in the same time. My concern is that then since tokudb will need 50% as cache and innodb buffer_pool will require around 70 to 80% so being both enabled will there issue on memory side?

  1. So I will put this default-storage-engine=tokudb in the mysqld.conf then will innodb be there any eating the memory ? Yes I installed tokudb using this method ps_tokudb_admin ?

  2. I read a lot about tokudb they suggest to keep the data,indexes and log in different directory what is your suggestion ? Should I do that so normally where will the directories be in /var/lib/mysql or other directory? Is there anything else I need to tune for good start of tokudb?

  3. With tokudb having so many files unlike innodb is there higher chances for data or indexes corruption?