Configuration MySQL 5.7

J'ai du mal à configurer MySQL pour tirer un maximum des performances du serveur. Il y a tellement de paramètre qui peuvent influencer ... j'ai passé pas mal de temps à chercher mais je préfère venir demander un peu d'aide aux pros :slight_smile:
[code]
>> MySQLTuner 1.6.18 - Major Hayden
>> 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
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: [OK] Currently running supported MySQL ve rsion 5.7.15-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics ------------------------------------------------------- ----------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERF ORMANCE_SCHEMA
[--] Data in InnoDB tables: 49M (Tables: 182)
[--] Data in MyISAM tables: 2G (Tables: 257)
[OK] Total fragmented tables: 0

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

-------- CVE Security Recommendations ---------------------------------------------------- ----------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics ------------------------------------------------------------- ----------
[--] Up for: 17m 9s (21K q [21.279 qps], 236 conn, TX: 15M, RX: 2M)
[--] Reads / Writes: 98% / 2%
[--] Binary logging is enabled (GTID MODE: ON)
[--] Physical Memory : 127.9G
[--] Max MySQL memory : 111.1G
[--] Other process memory: 2.6G
[--] Total buffers: 1.3G global + 1.1M per thread (100000 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 1.3G (1.04% of installed RAM)
[!!] Maximum possible memory usage: 111.1G (86.92% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (2/21K)
[OK] Highest usage of available connections: 0% (39/100000)
[OK] Aborted connections: 0.42% (1/236)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines .
[OK] Sorts requiring temporary tables: 0% (1 temp sorts / 291 sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 32% (46 on disk / 143 total)
[OK] Thread cache hit rate: 83% (39 created / 236 connections)
[OK] Table cache hit rate: 98% (596 open / 603 opened)
[OK] Open file limit used: 0% (186/3M)
[OK] Table locks acquired immediately: 100% (26K immediate / 26K locks)
[OK] Binlog cache memory access: 100.00% ( 199 Memory / 199 Total)

-------- Performance schema -------------------------------------------------------------- ----------
[--] Performance schema is enabled.
[--] Memory used by P_S: 72B
[--] Sys schema is installed.

-------- ThreadPool Metrics -------------------------------------------------------------- ----------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ------------------------------------------------------------------ ----------
[!!] Key buffer used: 31.5% (84M used / 268M cache)
[!!] Key buffer size / total MyISAM indexes: 256.0M/865.7M
[!!] Read Key buffer hit rate: 94.2% (596K cached / 34K reads)

-------- AriaDB Metrics ------------------------------------------------------------------ ----------
[--] AriaDB is disabled.

-------- InnoDB Metrics ------------------------------------------------------------------ ----------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 1.0G/49.4M
[!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1).
[--] Number of InnoDB Buffer Pool Chunk : 8 for 8 Buffer Pool Instance(s)
[OK] innodb_buffer_pool_size is aligned with value innodb_buffer_pool_chunk_size and innod b_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.50% (390579 hits/ 392555 total)
[!!] InnoDB Write Log efficiency: 33.61% (239 hits/ 711 total)
[OK] InnoDB log waits: 0.00% (0 waits / 472 writes)

-------- TokuDB Metrics ------------------------------------------------------------------ ----------
[--] TokuDB is disabled.

-------- Galera Metrics ------------------------------------------------------------------ ----------
[--] Galera is disabled.

-------- Replication Metrics ------------------------------------------------------------- ----------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ----------------------------------------------------------------- ----------
General recommendations:
Remove Anonymous User accounts - there are 4 anonymous accounts.
Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@ 'SpecificDNSorIp' = PASSWORD('secure_password'); )
Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = P ASSWORD('secure_password'); )
Restrict Host for user@% to user@SpecificDNSorIp
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Configure your accounts with ip or subnets only, then update your configuration with s kip-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
Variables to adjust:
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
key_buffer_size (> 865.7M)
innodb_buffer_pool_instances (=1)
[/code]

et voici mon fichier de conf :
[code]
[client]
port = 3306
socket = /tmp/mysql.sock

[mysql]
prompt = \u@\h [\d]>\_
no_auto_rehash

[mysqld]
## Files
user = mysql
port = 3306
socket = /tmp/mysql.sock
bind-address = 0.0.0.0
basedir = /usr/local

## Logging
datadir = /var/db/mysql
tmpdir = /var/db/mysql_tmpdir
slave-load-tmpdir = /var/db/mysql_tmpdir
secure-file-priv = /var/db/mysql_secure
log-bin = mysql-bin
log-output = TABLE
master-info-repository = TABLE
relay-log-info-repository = TABLE
relay-log-recovery = 1
slow-query-log = 1
server-id = 1
sync_binlog = 1
sync_relay_log = 1
binlog_cache_size = 16M
expire_logs_days = 30
default_password_lifetime = 0
enforce-gtid-consistency = 1
gtid-mode = ON
safe-user-create = 1
lower_case_table_names = 1
explicit-defaults-for-timestamp = 1
myisam-recover-options = BACKUP,FORCE
open_files_limit = 32768
table_open_cache = 16384
table_definition_cache = 8192
net_retry_count = 16384
key_buffer_size = 256M
max_allowed_packet = 64M

## Query Cache
query_cache_type = 0
query_cache_size = 0
long_query_time = 0.5

## Connections
max_connections = 640000


innodb_buffer_pool_size = 1G
innodb_data_home_dir = /var/db/mysql
innodb_log_group_home_dir = /var/db/mysql
innodb_data_file_path = ibdata1:128M:autoextend
innodb_temp_data_file_path = ibtmp1:128M:autoextend
innodb_flush_method = O_DIRECT
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_autoinc_lock_mode = 2
skip-symbolic-links
sql_mode=""

[mysqldump]
max_allowed_packet = 256M
quote_names
quick
[/code]
Je vous remerci par avance