Jump to content
Maintenance ×
×
×
  • Create New...

Help with MY.CNF Configuration


TekkForCe

Recommended Posts


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 [email protected] ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
    Restrict Host for [email protected]% to [email protected]
    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:

Link to comment
Share on other sites

  • Replies 2
  • Created
  • Last Reply

Top Posters In This Topic

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    • No registered users viewing this page.


  • Activity

    1. 0

      erorrs after update granny to 2.9

    2. 0

      Navicat Premium 16.1.1 x64

    3. 5

      problem Dargon Soul Changre Attribute

    4. 5

      problem Dargon Soul Changre Attribute

    5. 41

      Global Chat

    6. 5

      problem Dargon Soul Changre Attribute

    7. 2

      Startup Loading Window Library

Important Information

Terms of Use / Privacy Policy / Guidelines / We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.