Configure MySQL 5.7

[Tallywa 35]

Hello, I understand not too mysql, and I would like your help to configure a better my server mysql 5.7. I will post you the results of mysqltuner:
 
 

>>  MySQLTuner 1.6.18 - 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
[OK] Currently running supported MySQL version 5.7.15-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics ---------------------------------------------           --------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +My           ISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 50M (Tables: 184)
[--] Data in MyISAM tables: 4G (Tables: 264)
[OK] Total fragmented tables: 0


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

-------- Performance Metrics ---------------------------------------------------           --------------------
[--] Up for: 2d 23h 40m 43s (33M q [131.500 qps], 2M conn, TX: 2G, RX: 4G)
[--] Reads / Writes: 12% / 88%
[--] Binary logging is enabled (GTID MODE: ON)
[--] Physical Memory     : 127.9G
[--] Max MySQL memory    : 111.1G
[--] Other process memory: 11.4G
[--] 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.4G (1.12% 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% (27K/33M)
[OK] Highest usage of available connections: 0% (139/100000)
[OK] Aborted connections: 0.00%  (103/2069287)
[!!] 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 multiprocesso           r machines.
[OK] Sorts requiring temporary tables: 0% (20 temp sorts / 112K sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 15% (9K on disk / 62K total)
[OK] Thread cache hit rate: 99% (139 created / 2M connections)
[OK] Table cache hit rate: 90% (7K open / 8K opened)
[OK] Open file limit used: 0% (4K/3M)
[!!] Table locks acquired immediately: 90%
[OK] Binlog cache memory access: 100.00% ( 36846 Memory / 36846 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 --------------------------------------------------------           --------------------
[OK] Key buffer used: 100.0% (268M used / 268M cache)
[OK] Key buffer size / total MyISAM indexes: 256.0M/1.4G
[OK] Read Key buffer hit rate: 100.0% (3B cached / 622K reads)
[!!] Write Key buffer hit rate: 28.2% (89M cached / 63M writes)

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

-------- InnoDB Metrics --------------------------------------------------------           --------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 1.0G/50.3M
[!!] 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 innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.99% (47681449 hits/ 47684561 total)
[!!] InnoDB Write Log efficiency: 30.41% (40904 hits/ 134527 total)
[OK] InnoDB log waits: 0.00% (0 waits / 93623 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 F           OR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
    Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDN           SorIp' = PASSWORD('secure_password'); )
    Restrict Host for user@% to user@SpecificDNSorIp
    Reduce your overall MySQL memory footprint for system stability
    Configure your accounts with ip or subnets only, then update your configurat           ion with skip-name-resolve=1
    Optimize queries and/or use InnoDB to reduce lock wait
Variables to adjust:
    innodb_buffer_pool_instances (=1)

und tuning-primer
 

MySQL Version 5.7.15-log amd64

Uptime = 2 days 23 hrs 46 min 34 sec
Avg. qps = 131
Total Questions = 33988751
Threads Connected = 79

Server has been running for over 48hrs.
It should be safe to follow these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 0.500000 sec.
You have 27838 out of 33988782 that take longer than 0.500000 sec. to complete
[: 0.00000000000000000000: bad number
Your long_query_time seems to be fine

BINARY UPDATE LOG
The binary update log is enabled

WORKER THREADS
Current thread_cache_size = 100
Current threads_cached = 60
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 100000
Current threads_connected = 79
Historic max_used_connections = 139
The number of used connections is 0% of the configured maximum.
You are using less than 10% of your configured max_connections.
Lowering max_connections could help to avoid an over-allocation of memory
See "MEMORY USAGE" section to make sure you are not over-allocating

No InnoDB Support Enabled!

MEMORY USAGE
Max Memory Ever Allocated : 3.59 G
Configured Max Per-thread Buffers : 1672.36 G
Configured Max Global Buffers : 1.26 G
Configured Max Memory Limit : 1673.62 G
Physical Memory : 127.99 G

Max memory limit exceeds 90% of physical memory

KEY BUFFER
Current MyISAM index space = 1.44 G
Current key_buffer_size = 256 M
Key cache miss rate is 1 : 5918
Key buffer free ratio = 0 %
You could increase key_buffer_size
It is safe to raise this up to 1/4 of total system memory;
assuming this is a dedicated database server.

QUERY CACHE
Query cache is supported but not enabled
Perhaps you should set the query_cache_size

SORT OPERATIONS
Current sort_buffer_size = 256 K
Current read_rnd_buffer_size = 256 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 260.00 K
You have had 98 queries where a join could not use an index properly
You have had 3 joins without keys that check for key usage after each row
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.

Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.

OPEN FILES LIMIT
Current open_files_limit = 3771216 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_open_cache = 16384 tables
Current table_definition_cache = 8192 tables
You have a total of 566 tables
You have 7945 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 16 M
Of 63757 temp tables, 13% were created on disk
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 128 K
Current table scan ratio = 996 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 10
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'
If you have a high concurrency of inserts on Dynamic row-length tables
consider setting 'concurrent_insert=ALWAYS'.

And the conf file :

[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              = 100M
## 64
## 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
innodb_flush_log_at_trx_commit  = 2
skip-symbolic-links
sql_mode=""

[mysqldump]
max_allowed_packet              = 256M
quote_names
quick

 

 

thanks you very much

 

[Tallywa 35]

bump

[Tallywa 35]

bump