Fonctions avancées de l'administration
MySQLd Multi
Lors de la mise en place de la replication, il est interessant de pouvoir lancer plusieurs MySQL en une fois. L'utilitaire mysql_multi est la pour ca La première étape de la configuration de mysqld_multi est la création de deux groupes [mysqld] distincts dans le fichier my.cnf existant.
Assurez-vous que l'utilisateur MySQL, qui arrête les services mysqld, a le même mot de passe pour tous les serveurs MySQL accessibles par mysqld_multi.
Cet utilisateur doit avoir le privilège 'Shutdown_priv', mais pour des raisons de sécurité raisons ne devraient pas avoir d'autres privilèges. Il est conseillé de créer un utilisateur 'multi_admin' commun à tous les serveurs MySQL contrôlés par mysqld_multi.
GRANT SHUTDOWN ON *. * TO multi_admin @ localhost IDENTIFIÉ PAR 'password'
Sur les serveurs, il faut:
- Chaque serveur en nécessite un port TCP et un socket Unix unique.
- Chaque serveur doit avoir un datadir différents
Creation des serveurs
pilou@pilou-pc:~/Formation/mysql-8.0.20-linux-glibc2.12-x86_64$ ./bin/mysqld --initialize --basedir=/home/pilou/Formation/mysql-8.0.20-linux-glibc2.12-x86_64 --datadir=/home/pilou/Formation/mysqld_multi/data1 --log-error=/home/pilou/Formation/mysqld_multi/logerror1
pilou@pilou-pc:~/Formation/mysql-8.0.20-linux-glibc2.12-x86_64$ ./bin/mysqld --initialize --basedir=/home/pilou/Formation/mysql-8.0.20-linux-glibc2.12-x86_64 --datadir=/home/pilou/Formation/mysqld_multi/data2 --log-error=/home/pilou/Formation/mysqld_multi/logerror2
Sur chaque serveur, on modifie le mot de passe de root et on crée l'utilisateur multi_admin
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'piloupilou'
-> ;
Query OK, 0 rows affected (0.02 sec)
mysql> create user 'mysqlmulti'@'localhost' IDENTIFIED BY 'mysqlmulti'
-> ;
Query OK, 0 rows affected (0.02 sec)
mysql> GRANT SHUTDOWN ON *.* to 'mysqlmulti'@'localhost';
Query OK, 0 rows affected (0.02 sec)
Fichier de configuration.
Il faut ensuite créer le fichier de configuration pour mysqld_multi
[mysqld_multi]
mysqld = /home/pilou/Formation/mysql-8.0.20-linux-glibc2.12-x86_64/bin/mysqld
mysqladmin = /home/pilou/Formation/mysql-8.0.20-linux-glibc2.12-x86_64/bin/mysqladmin
log=/home/pilou/Formation/mysqld_multi/mysqld_multi.log
user = mysqlmulti
pass = mysqlmulti
[mysqld1]
port = 3306
mysqld = /home/pilou/Formation/mysql-8.0.20-linux-glibc2.12-x86_64/bin/mysqld
socket = /tmp/mysql.sock1
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
table_open_cache=500
datadir = "/home/pilou/Formation/mysqld_multi/data1"
pid-file = /home/pilou/Formation/mysqld_multi/data1/mysql1.pid
log-error=/home/pilou/Formation/mysqld_multi/logerror1.err
[mysqld2]
port = 3307
mysqld = /home/pilou/Formation/mysql-8.0.20-linux-glibc2.12-x86_64/bin/mysqld
socket = /tmp/mysql.sock2
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
table_open_cache=500
datadir = "/home/pilou/Formation/mysqld_multi/data2"
pid-file = /home/pilou/Formation/mysqld_multi/data2/mysql2.pid
log-error=/home/pilou/Formation/mysqld_multi/logerror2.errLe lancement se fait ainsi
./bin/mysqld_multi --defaults-file=/home/pilou/Formation/mysql-8.0.20-linux-glibc2.12-x86_64/mysqld_multi.ini --verbose start 1
et l'arret
./bin/mysqld_multi --defaults-file=/home/pilou/Formation/mysql-8.0.20-linux-glibc2.12-x86_64/mysqld_multi.ini --verbose stop
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 = 64MMysql Query Rewriter
Pour installer le plug-in de réécriture de requête Rewriter, exécutez install_rewriter.sql situé dans le répertoire de share de votre installation MySQL.
mysql -h localhost -u root --protocol=tcp -p < /home/pilou/Formation/mysql-8.0.20-linux-glibc2.12-x86_64/share/install_rewriter.sql
L'installation se constate :
mysql> SELECT * FROM mysql.plugin;
+----------+-------------+
| name | dl |
+----------+-------------+
| rewriter | rewriter.so |
+----------+-------------+
1 row in set (0.00 sec)
mysql> SHOW GLOBAL VARIABLES LIKE 'rewriter%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| rewriter_enabled | ON |
| rewriter_verbose | 1 |
+------------------+-------+
2 rows in set (0.00 sec)
La table rewrite_rules est une table persistante pour le plugin query_rewrite:
mysql> SHOW CREATE TABLE query_rewrite.rewrite_rules\G
*************************** 1. row ***************************
Table: rewrite_rules
Create Table: CREATE TABLE `rewrite_rules` (
`id` int NOT NULL AUTO_INCREMENT,
`pattern` varchar(5000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
`pattern_database` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
`replacement` varchar(5000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
`enabled` enum('YES','NO') CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT 'YES',
`message` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
`pattern_digest` varchar(64) DEFAULT NULL,
`normalized_pattern` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)
Vous pouvez activer le plugin soit via le my.ini soit
mysql> SET GLOBAL rewriter_enabled = ON;
mysql> SET GLOBAL rewriter_enabled = OFF;
Le plug-in de réécriture ne fonctionne qu'avec les instructions SELECT.
Commencons avec un exemple:
mysql> INSERT INTO query_rewrite.rewrite_rules (pattern, replacement) VALUES('SELECT ?', 'SELECT ? + 1');
Il est possible de les afficher sachant quel ne sont pas "compilé"
mysql> SELECT * FROM query_rewrite.rewrite_rules\G
*************************** 1. row ***************************
id: 1
pattern: SELECT ?
pattern_database: NULL
replacement: SELECT ? + 1
enabled: YES
message: NULL
pattern_digest: NULL
normalized_pattern: NULL
1 row in set (0.00 sec)
Puis nous allons les compiler:
mysql> CALL query_rewrite.flush_rewrite_rules();
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM query_rewrite.rewrite_rules\G
*************************** 1. row ***************************
id: 1
pattern: SELECT ?
pattern_database: NULL
replacement: SELECT ? + 1
enabled: YES
message: NULL
pattern_digest: d1b44b0c19af710b5a679907e284acd2ddc285201794bc69a2389d77baedddae
normalized_pattern: select ?
1 row in set (0.00 sec)
La requete est ainsi mise :
mysql> select 1;
+-------+
| 1 + 1 |
+-------+
| 2 |
+-------+
1 row in set, 1 warning (0.00 sec)
Usage 1: Optimization
Pour des raisons de performances, il est parfois souhaitable de réécrire une requete sans pouvoir le faire
-> SELECT count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE DATEDIFF(to_date, from_date) < {integer};
<= SELECT count(emp_no) FROM employees.employees WHERE emp_no IN ( SELECT emp_no FROM employees.salaries WHERE DATEDIFF(to_date, from_date) < {integer});
INSERT INTO query_rewrite.rewrite_rules
(
pattern,
replacement
)
VALUES
(
'SELECT count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE DATEDIFF(to_date, from_date) < ?',
'SELECT count(emp_no) FROM employees.employees WHERE emp_no IN ( SELECT emp_no FROM employees.salaries WHERE DATEDIFF(to_date, from_date) < ?)'
);
CALL query_rewrite.flush_rewrite_rules();Usage 2: Optimization
Il est possible de rajouter des optimizations en commentaires dans le SQL de MySQL. Par exemple,
-> SELECT count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE salary = {integer};
<= SELECT /*+ MAX_EXECUTION_TIME(10000)*/ count(distinct emp_no) FROM employees.employees INNER JOIN employees.salaries USING(emp_no) WHERE salary = {integer};
my.ini
Le but de ce chapitre est de mettre en place un bon fichier my.ini
En premier lieu, on regarde la mémoire libre ainsi que le nombre de processeur
cat /proc/meminfo
MemTotal: 3064328 kB
MemFree: 697228 kB
MemAvailable: 1337368 kB
cat /proc/cpuinfo
2 processeurRegardons ensuite le nombre de tables (nous prenons ici l'exemple de https://dev.mysql.com/doc/employee/en/employees-installation.html)
Remplissons la et faisons les requetes automatiques
mysql < employees.sql
mysql -t < test_employees_md5.sqlConfiguration réseau et de base
bind_address = 127.0.0.1 # mettre 0.0.0.0 pour les connexions distantes
max_allowed_packet = 256M # taille d'un packet de donnée
max_connect_errors = 1000000 # eviction d'un client apres max_connect_errors
skip_external_locking # pour MyISAM desaloue les lock systeme
skip_name_resolve # pas de résolution DNS
sql_mode ="NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES"
tmpdir = /tmp
#user = mysql En cas d'installation en mode service, il faut préciser l'utilisateur qui execute le service
Parametrage du nombre de connexion:
Cela est issue au runtime de :
show status like '%connected%'
et faire
max_connections = 151 # nombre de connexion maximal
max_user_connections = 145 # nombre de connexion pour un utilisateur
thread_cache_size = 151 # =max_connexionsVariable de sessions
sort_buffer_size = 2M # Could be too big for many small sorts
tmp_table_size = 32M # taille des table temporaire
read_buffer_size = 128k # a verifier avec le calcul mémoire
read_rnd_buffer_size = 256k # a verifier avec le calcul mémoire
join_buffer_size = 128k # a verifier avec le calcul mémoire
# Other buffers and caches
table_definition_cache = 1400 # nombre de table dans la base de donnée au max au min show global status like 'open_tables';
table_open_cache = 2000 # approximativement show global status like 'opened_tables'; / max_connection
table_open_cache_instances = 16 # New default in 5.7
Parametrage de InnoDB
default_storage_engine = InnoDB # le moteur de stockage est InnoDB
innodb_buffer_pool_instances = 1 # 1 instance par Giga, donc ici 1 pour buffer pool chunk size de 1G
innodb_buffer_pool_size = 410M # 80% de la RAM
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_file_format = Barracuda # format du fichier interne
innodb_log_buffer_size = 5M # entre 5 et 10% du log file size
innodb_log_file_size = 64M # 25% du buffer pool
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 # nombre de CPU -1/2
innodb_read_io_threads = 8
innodb_write_io_threads = 8Les deux derniers paramètres sont issue de SHOW ENGINE INNODB STATUS et en particulier du nombre de requete pending qui doivent être intéfieur a pending*64<io_thread
Parametrage de MyISAM
key_buffer_size = 8M # 25% de la RAM
myisam_recover_options = 'BACKUP,FORCE'
Et enfin les logs:
log_warnings = 2 # MySQL 5.6, equivalent à log_error_verbosity = 3
# log_error_verbosity = 3 # MySQL 5.7, equivalent ) log_warnings = 2,a supprimer pour mariadb
innodb_print_all_deadlocks = 1
# Slow Query Log
slow_query_log_file = %INSTANCEDIR%/log/%UNAME%_%INSTANCE%_slow.log
slow_query_log = 0
log_queries_not_using_indexes = 0 # pour les developpeurs
long_query_time = 0.5
min_examined_row_limit = 100
# Replication
#server_id = %SERVERID% # id
#log_bin = %INSTANCEDIR%/binlog/%UNAME%_%INSTANCE%_binlog #emplacement
# master_verify_checksum = ON # MySQL 5.6
#binlog_cache_size = 1M
#binlog_stmt_cache_size = 1M
#max_binlog_size = 128M # en fonction du trafic
#sync_binlog = 1 # Mettre a 0 pour avoir des problemes
#expire_logs_days = 5 # We will survive easter holidays
#binlog_format = ROW # ROW est ok pour la replication
# binlog_row_image = MINIMAL # Since 5.6
# auto_increment_increment = 2 # Pour Master/Master mettre 2
# auto_increment_offset = 1 # Pour Master/Master mettre 1 et 2
Calcul de la mémoire
Utiliser https://www.mysqlcalculator.com/ qui fait le calcul de la mémoire du serveur:
key_buffer_size
+ query_cache_size
+ tmp_table_size
+ innodb_buffer_pool_size
+ innodb_additional_mem_pool_size
+ innodb_log_buffer_size
+ max_connections
×
(sort_buffer_size
+ read_buffer_size
+ read_rnd_buffer_size
+ join_buffer_size
+ thread_stack
+ binlog_cache_size )
On remarqueras le nombre de connexions maximuns influe enormément sur la mémoire