Tuning InnoDB

Il est complexe de faire le tuning d'InnoDB.

Une facon de faire est de laisser mysqltuner, un outil client vérifier la configuration.

Soit un fichier my.ini de base

[mysqld]

basedir=/home/pilou/Formation/mysql-8.0.20-linux-glibc2.12-x86_64

datadir=/home/pilou/Formation/simpleinit/data

log-error=/home/pilou/Formation/simpleinit/mysqld.log

port = 3306 

socket = /tmp/mysql.sock skip-external-locking 

key_buffer_size = 16K 

max_allowed_packet = 1M 

table_open_cache = 4 

sort_buffer_size = 64K 

read_buffer_size = 256K 

read_rnd_buffer_size = 256K 

net_buffer_length = 2K 

thread_stack = 128K

et executons mysqltuner au regard de ce fichier

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

chmod +x mysqltuner.pl

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt

wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv

Au premier run, nous avons:

/mysqltuner.pl --host 127.0.0.1 --user root --pass piloupilou

 >> MySQLTuner 1.7.19 - Major Hayden <major@mhtx.net>

 >> Bug reports, feature requests, and downloads at http://mysqltuner.com/

 >> Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script

[--] Performing tests on 127.0.0.1:3306

[OK] Logged in using credentials passed on the command line

[OK] Currently running supported MySQL version 8.0.20

[OK] Operating on 64-bit architecture

 

-------- Log file Recommendations ------------------------------------------------------------------

[OK] Log file /home/pilou/Formation/simpleinit/mysqld.log exists

[--] Log file: /home/pilou/Formation/simpleinit/mysqld.log(11K)

[OK] Log file /home/pilou/Formation/simpleinit/mysqld.log is readable.

[OK] Log file /home/pilou/Formation/simpleinit/mysqld.log is not empty

[OK] Log file /home/pilou/Formation/simpleinit/mysqld.log is smaller than 32 Mb

[!!] /home/pilou/Formation/simpleinit/mysqld.log contains 8 warning(s).

[!!] /home/pilou/Formation/simpleinit/mysqld.log contains 25 error(s).

[--] 9 start(s) detected in /home/pilou/Formation/simpleinit/mysqld.log

[--] 1) 2020-05-28T20:09:32.463843Z 0 [System] [MY-010931] [Server] /home/pilou/Formation/mysql-8.0.20-linux-glibc2.12-x86_64/bin/mysqld: ready for connections. Version: '8.0.20' socket: '/tmp/mysql.sock skip-external-locking' port: 3306 MySQL Community Server - GPL.

[--] 2) 2020-05-28T20:09:32.188942Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/tmp/mysqlx.sock' bind-address: '::' port: 33060

[--] 3) 2020-05-28T20:06:01.472567Z 0 [System] [MY-010931] [Server] /home/pilou/Formation/mysql-8.0.20-linux-glibc2.12-x86_64/bin/mysqld: ready for connections. Version: '8.0.20' socket: '/tmp/mysql.sock skip-external-locking' port: 0 MySQL Community Server - GPL.

[--] 4) 2020-05-28T20:06:01.309759Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/tmp/mysqlx.sock'

[--] 5) 2020-05-28T19:56:56.960909Z 0 [System] [MY-010931] [Server] /home/pilou/Formation/mysql-8.0.20-linux-glibc2.12-x86_64/bin/mysqld: ready for connections. Version: '8.0.20' socket: '/tmp/mysql.sock skip-external-locking' port: 3306 MySQL Community Server - GPL.

[--] 6) 2020-05-28T19:56:56.807408Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/tmp/mysqlx.sock' bind-address: '::' port: 33060

[--] 7) 2020-05-28T19:56:23.191817Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/tmp/mysqlx.sock' 

[--] 8) 2020-05-11T21:03:33.700583Z 0 [System] [MY-010931] [Server] /home/pilou/Formation/mysql-8.0.20-linux-glibc2.12-x86_64/bin/mysqld: ready for connections. Version: '8.0.20' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server - GPL.

[--] 9) 2020-05-11T21:03:33.566923Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/tmp/mysqlx.sock' bind-address: '::' port: 33060

[--] 11 shutdown(s) detected in /home/pilou/Formation/simpleinit/mysqld.log

[--] 1) 2020-05-28T20:08:34.761588Z 0 [System] [MY-010910] [Server] /home/pilou/Formation/mysql-8.0.20-linux-glibc2.12-x86_64/bin/mysqld: Shutdown complete (mysqld 8.0.20) MySQL Community Server - GPL.

[--] 2) 2020-05-28T20:08:12.745558Z 0 [System] [MY-010910] [Server] /home/pilou/Formation/mysql-8.0.20-linux-glibc2.12-x86_64/bin/mysqld: Shutdown complete (mysqld 8.0.20) MySQL Community Server - GPL.

[--] 3) 2020-05-28T20:07:50.132900Z 0 [System] [MY-010910] [Server] /home/pilou/Formation/mysql-8.0.20-linux-glibc2.12-x86_64/bin/mysqld: Shutdown complete (mysqld 8.0.20) MySQL Community Server - GPL.

[--] 4) 2020-05-28T20:05:52.370915Z 0 [System] [MY-010910] [Server] /home/pilou/Formation/mysql-8.0.20-linux-glibc2.12-x86_64/bin/mysqld: Shutdown complete (mysqld 8.0.20) MySQL Community Server - GPL.

[--] 5) 2020-05-28T19:56:24.617889Z 0 [System] [MY-010910] [Server] /home/pilou/Formation/mysql-8.0.20-linux-glibc2.12-x86_64/bin/mysqld: Shutdown complete (mysqld 8.0.20) MySQL Community Server - GPL.

[--] 6) 2020-05-11T21:21:48.880403Z 0 [System] [MY-010910] [Server] /home/pilou/Formation/mysql-8.0.20-linux-glibc2.12-x86_64/bin/mysqld: Shutdown complete (mysqld 8.0.20) MySQL Community Server - GPL.

[--] 7) 2020-05-11T21:03:07.368654Z 0 [System] [MY-010910] [Server] /home/pilou/Formation/mysql-8.0.20-linux-glibc2.12-x86_64/bin/mysqld: Shutdown complete (mysqld 8.0.20) MySQL Community Server - GPL.

[--] 8) 2020-05-11T21:02:10.209497Z 0 [System] [MY-010910] [Server] /home/pilou/Formation/mysql-8.0.20-linux-glibc2.12-x86_64/bin/mysqld: Shutdown complete (mysqld 8.0.20) MySQL Community Server - GPL.

[--] 9) 2020-05-11T21:02:00.245998Z 0 [System] [MY-010910] [Server] /home/pilou/Formation/mysql-8.0.20-linux-glibc2.12-x86_64/bin/mysqld: Shutdown complete (mysqld 8.0.20) MySQL Community Server - GPL.

[--] 10) 2020-05-11T21:01:56.588580Z 0 [System] [MY-010910] [Server] /home/pilou/Formation/mysql-8.0.20-linux-glibc2.12-x86_64/bin/mysqld: Shutdown complete (mysqld 8.0.20) MySQL Community Server - GPL.

 

-------- Storage Engine Statistics -----------------------------------------------------------------

[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA 

[--] Data in InnoDB tables: 16.0K (Tables: 1)

[OK] Total fragmented tables: 0

 

-------- Analysis Performance Metrics --------------------------------------------------------------

[--] innodb_stats_on_metadata: OFF

[OK] No stat updates during querying INFORMATION_SCHEMA.

 

-------- Security Recommendations ------------------------------------------------------------------

[--] Skipped due to unsupported feature for MySQL 8

 

-------- CVE Security Recommendations --------------------------------------------------------------

[OK] NO SECURITY CVE FOUND FOR YOUR VERSION

 

-------- Performance Metrics -----------------------------------------------------------------------

[--] Up for: 14m 50s (102 q [0.115 qps], 43 conn, TX: 254K, RX: 11K)

[--] Reads / Writes: 100% / 0%

[--] Binary logging is enabled (GTID MODE: OFF)

[--] Physical Memory : 2.9G

[--] Max MySQL memory : 452.6M

[--] Other process memory: 0B

[--] Total buffers: 160.0M global + 1.9M per thread (151 max threads)

[--] P_S Max memory usage: 72B

[--] Galera GCache Max memory usage: 0B

[OK] Maximum reached memory usage: 163.9M (5.48% of installed RAM)

[OK] Maximum possible memory usage: 452.6M (15.12% of installed RAM)

[OK] Overall possible memory usage with other process is compatible with memory available

[OK] Slow queries: 0% (0/102)

[OK] Highest usage of available connections: 1% (2/151)

[OK] Aborted connections: 0.00% (0/43)

[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance

[--] Query cache have been removed in MySQL 8

[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 7 sorts)

[OK] No joins without indexes

[!!] Temporary tables created on disk: 52% (12 on disk / 23 total)

[OK] Thread cache hit rate: 95% (2 created / 43 connections)

[!!] Table cache hit rate: 0% (1 open / 6K opened)

[OK] table_definition_cache(402) is upper than number of tables(311)

[OK] Open file limit used: 0% (2/5K)

[OK] Table locks acquired immediately: 100% (8 immediate / 8 locks)

[OK] Binlog cache memory access: 100.00% (1 Memory / 1 Total)

 

-------- Performance schema ------------------------------------------------------------------------

[--] Memory used by P_S: 72B

[--] Sys schema is installed.

 

-------- ThreadPool Metrics ------------------------------------------------------------------------

[--] ThreadPool stat is disabled.

 

-------- MyISAM Metrics ----------------------------------------------------------------------------

[--] MyISAM Metrics are disabled on last MySQL versions.

 

-------- InnoDB Metrics ----------------------------------------------------------------------------

[--] InnoDB is enabled.

[--] InnoDB Thread Concurrency: 0

[OK] InnoDB File per table is activated

[OK] InnoDB buffer pool / data size: 128.0M/16.0K

[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal to 25%

[OK] InnoDB buffer pool instances: 1

[--] Number of InnoDB Buffer Pool Chunk : 1 for 1 Buffer Pool Instance(s)

[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances

[OK] InnoDB Read buffer efficiency: 97.89% (40289 hits/ 41156 total)

[OK] InnoDB Write log efficiency: 96.15% (4891 hits/ 5087 total)

[OK] InnoDB log waits: 0.00% (0 waits / 196 writes)

 

-------- AriaDB Metrics ----------------------------------------------------------------------------

[--] AriaDB is disabled.

 

-------- TokuDB Metrics ----------------------------------------------------------------------------

[--] TokuDB is disabled.

 

-------- XtraDB Metrics ----------------------------------------------------------------------------

[--] XtraDB is disabled.

 

-------- Galera Metrics ----------------------------------------------------------------------------

[--] Galera is disabled.

 

-------- Replication Metrics -----------------------------------------------------------------------

[--] Galera Synchronous replication: NO

[--] No replication slave(s) for this server.

[--] Binlog format: ROW

[--] XA support enabled: ON

[--] Semi synchronous replication Master: Not Activated

[--] Semi synchronous replication Slave: Not Activated

[--] This is a standalone server

 

-------- Recommendations ---------------------------------------------------------------------------

General recommendations:

 Control warning line(s) into /home/pilou/Formation/simpleinit/mysqld.log file

 Control error line(s) into /home/pilou/Formation/simpleinit/mysqld.log file

 MySQL was started within the last 24 hours - recommendations may be inaccurate

 Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1

 When making adjustments, make tmp_table_size/max_heap_table_size equal

 Reduce your SELECT DISTINCT queries which have no LIMIT clause

 Increase table_open_cache gradually to avoid file descriptor limits

 Read this before increasing table_open_cache over 64: https://bit.ly/2Fulv7r

 Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/

 This is MyISAM only table_cache scalability problem, InnoDB not affected.

 See more details here: https://bugs.mysql.com/bug.php?id=49177

 This bug already fixed in MySQL 5.7.9 and newer MySQL versions.

 Beware that open_files_limit (5000) variable 

 should be greater than table_open_cache (4)

 Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU

Variables to adjust:

 tmp_table_size (> 16M)

 max_heap_table_size (> 16M)

 table_open_cache (> 4)

 innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

Ajustement 1

Il n'est pas nécéssaire de charger des moteurs de stockage inutile dans la base de donnée:

Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA

On rajoute dans le fichier my.ini

disabled_storage_engines="ARCHIVE,BLACKHOLE,CSV,FEDERATED,MEMORY,MRG_MYISAM,MyISAM"

default_storage_engine=InnoDB

[mysqld]

# Required Settings

basedir=/home/pilou/Formation/mysql-8.0.20-linux-glibc2.12-x86_64

datadir=/home/pilou/Formation/simpleinit/data

log-error=/home/pilou/Formation/simpleinit/mysqld.log

bind_address = 127.0.0.1 # Change to 0.0.0.0 to allow remote connections

max_allowed_packet = 256M

max_connect_errors = 1000000

pid_file = /tmp/mysqld.pid

port = 3306

skip_external_locking

skip_name_resolve

socket = /tmp/mysqld.sock

# Enable for b/c with databases created in older MySQL/MariaDB versions (e.g. when using null dates)

#sql_mode = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES

tmpdir = /tmp

# InnoDB Settings

default_storage_engine = InnoDB

innodb_buffer_pool_instances = 2 # Use 1 instance per 1GB of InnoDB pool size

innodb_buffer_pool_size = 2G # Use up to 70-80% of RAM

innodb_file_per_table = 1

innodb_flush_log_at_trx_commit = 0

innodb_flush_method = O_DIRECT

innodb_log_buffer_size = 16M

innodb_log_file_size = 512M

innodb_stats_on_metadata = 0

#innodb_temp_data_file_path = ibtmp1:64M:autoextend:max:20G # Control the maximum size for the ibtmp1 file

#innodb_thread_concurrency = 4 # Optional: Set to the number of CPUs on your system (minus 1 or 2) to better

 # contain CPU usage. E.g. if your system has 8 CPUs, try 6 or 7 and check

 # the overall load produced by MySQL/MariaDB.

innodb_read_io_threads = 64

innodb_write_io_threads = 64

# MyISAM Settings

#query_cache_limit = 4M # UPD - Option supported by MariaDB & up to MySQL 5.7, remove this line on MySQL 8.x

#query_cache_size = 64M # UPD - Option supported by MariaDB & up to MySQL 5.7, remove this line on MySQL 8.x

#query_cache_type = 1 # Option supported by MariaDB & up to MySQL 5.7, remove this line on MySQL 8.x

key_buffer_size = 32M # UPD

low_priority_updates = 1

concurrent_insert = 2

# Connection Settings

max_connections = 100 # UPD

back_log = 512

thread_cache_size = 100

thread_stack = 192K

interactive_timeout = 180

wait_timeout = 180

# For MySQL 5.7+ only (disabled by default)

#max_execution_time = 30000 # Set a timeout limit for SELECT statements (value in milliseconds).

 # This option may be useful to address aggressive crawling on large sites,

 # but it can also cause issues (e.g. with backups). So use with extreme caution and test!

 # More info at: https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_execution_time

# For MariaDB 10.1.1+ only (disabled by default)

#max_statement_time = 30 # The equivalent of "max_execution_time" in MySQL 5.7+ (set above)

 # The variable is of type double, thus you can use subsecond timeout.

 # For example you can use value 0.01 for 10 milliseconds timeout.

 # More info at: https://mariadb.com/kb/en/aborting-statements/

# Buffer Settings

join_buffer_size = 4M # UPD

read_buffer_size = 3M # UPD

read_rnd_buffer_size = 4M # UPD

sort_buffer_size = 4M # UPD

# Table Settings

# In systemd managed systems like Ubuntu 16.04+ or CentOS 7+, you need to perform an extra action for table_open_cache & open_files_limit

# to be overriden (also see comment next to open_files_limit).

# E.g. for MySQL 5.7, please check: https://dev.mysql.com/doc/refman/5.7/en/using-systemd.html

# and for MariaDB check: https://mariadb.com/kb/en/library/systemd/

table_definition_cache = 40000 # UPD

table_open_cache = 40000 # UPD

open_files_limit = 60000 # UPD - This can be 2x to 3x the table_open_cache value or match the system's

 # open files limit usually set in /etc/sysctl.conf or /etc/security/limits.conf

 # In systemd managed systems this limit must also be set in:

 # /etc/systemd/system/mysqld.service.d/override.conf (for MySQL 5.7+) and

 # /etc/systemd/system/mariadb.service.d/override.conf (for MariaDB)

max_heap_table_size = 128M

tmp_table_size = 128M

# Search Settings

ft_min_word_len = 3 # Minimum length of words to be indexed for search results

# Logging

log_queries_not_using_indexes = 1

long_query_time = 5

slow_query_log = 0 # Disabled for production

slow_query_log_file = /home/pilou/Formation/simpleinit/mysql_slow.log

[mysqldump]

# Variable reference

# For MySQL 5.7: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

# For MariaDB: https://mariadb.com/kb/en/library/mysqldump/

quick

quote_names

max_allowed_packet = 64M