Help with MY.CNF Configuration

[TekkForCe 6]

Hello,

I need help to configurate my MySQL Server.

I´m ready to pay for good help.


MYsqltuner:

 

[--] Skipped version check for MySQLTuner script
[!!] Successfully authenticated with no password - SECURITY RISK!
[OK] Currently running supported MySQL version 5.6.35
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 254M (Tables: 134)
[!!] InnoDB is enabled but isn't being used
[OK] Total fragmented tables: 0

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

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

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 3d 8h 5m 13s (43M q [152.240 qps], 104K conn, TX: 16G, RX: 4G)
[--] Reads / Writes: 16% / 84%
[--] Binary logging is disabled
[--] Physical Memory     : 63.8G
[--] Max MySQL memory    : 1726.4G
[--] Other process memory: 24.8G
[--] Total buffers: 320.0M global + 17.5M per thread (99999 max threads)
[--] P_S Max memory usage: 17G
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 19.9G (31.24% of installed RAM)
[!!] Maximum possible memory usage: 1726.4G (2704.47% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (3/43M)
[OK] Highest usage of available connections: 0% (147/99999)
[OK] Aborted connections: 0.23%  (242/104177)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 23K sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 6% (206 on disk / 3K total)
[OK] Thread cache hit rate: 99% (147 created / 104K connections)
[OK] Table cache hit rate: 96% (175 open / 182 opened)
[OK] Open file limit used: 0% (189/180K)
[OK] Table locks acquired immediately: 99% (37M immediate / 38M locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is enabled.
[--] Memory used by P_S: 17.1G
[--] Sys schema isn't installed.

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

-------- MyISAM Metrics ----------------------------------------------------------------------------
[OK] Key buffer used: 100.0% (33M used / 33M cache)
[OK] Key buffer size / total MyISAM indexes: 32.0M/79.8M
[OK] Read Key buffer hit rate: 100.0% (737M cached / 266K reads)
[!!] Write Key buffer hit rate: 2.4% (9M cached / 9M writes)

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

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is disabled.
[!!] InnoDB Storage engine is disabled. InnoDB is the default storage engine

-------- 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:
    Add skip-innodb to MySQL configuration to disable InnoDB
    Remove Anonymous User accounts - there are 2 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' = PASSWORD('secure_password'); )
    Restrict Host for user@% to user@SpecificDNSorIp
    Reduce your overall MySQL memory footprint for system stability
    Dedicate this server to your database for highest performance.
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_type (=0)

Hardware:

 

Intel i7-7700k Overclocked 4c /8t @4,7GHz / 5.0GHz
64GB DDR4 2400MHz
2x450GB NVMe Intel® P3520

My my.cnf:

 

[mysqld]
query_cache_limit=128M
query_cache_size=128M
query_cache_type=1
max_connections=99999
interactive_timeout=100
wait_timeout=100
connect_timeout=20
thread_cache_size=128
key_buffer=32M
join_buffer=1M
max_allowed_packet=32M
sort_buffer_size=8M
read_buffer_size=8M
max_connect_errors=9999999
thread_concurrency=8
myisam_sort_buffer_size=128M

[safe_mysqld]
open_files_limit=8192

[isamchk]
key_buffer=128M
sort_buffer=128M
read_buffer=32M
write_buffer=32M

[myisamchk]
key_buffer=128M
sort_buffer=128M
read_buffer=32M
write_buffer=32M

Mysqltuner result:

[TekkForCe 6]

bump

[TekkForCe 6]

bump