Hi, am not sure what to write in my.cnf, attach is mysqltuner report, please advice what to add in my.cnf
Code:
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 2h 18m 33s (1M q [137.757 qps], 6K conn, TX: 19G, RX: 643M)
[--] Reads / Writes: 94% / 6%
[--] Binary logging is disabled
[--] Physical Memory : 49.0G
[--] Max MySQL memory : 836.0M
[--] Other process memory: 1.0G
[--] Total buffers: 416.0M global + 2.8M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 435.5M (0.87% of installed RAM)
[OK] Maximum possible memory usage: 836.0M (1.67% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/1M)
[OK] Highest usage of available connections: 4% (7/151)
[OK] Aborted connections: 0.02% (1/6592)
[!!] 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.
[!!] Query cache efficiency: 0.0% (0 cached / 1M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (266 temp sorts / 225K sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 81% (218K on disk / 268K total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 153% (158 open / 103 opened)
[OK] Open file limit used: 1% (125/10K)
[OK] Table locks acquired immediately: 100% (1M immediate / 1M locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema isn't installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 10 thread(s).
[--] Using default value is good enough for your version (10.0.31-MariaDB)
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.7% (25M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/18.3M
[OK] Read Key buffer hit rate: 99.9% (447K cached / 292 reads)
[OK] Write Key buffer hit rate: 98.6% (25K cached / 25K writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 128.0M/1.6G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal 25%
[!!] InnoDB buffer pool <= 1G and Innodb_buffer_pool_instances(!=1).
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 98.69% (2290994126 hits/ 2321482987 total)
[!!] InnoDB Write Log efficiency: 47.76% (46589 hits/ 97538 total)
[OK] InnoDB log waits: 0.00% (0 waits / 50949 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 99.7% (15M cached / 51K reads)
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.
-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.
-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect 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:
Control warning line(s) into /var/lib/mysql/vmi116126.contaboserver.net.err file
Control error line(s) into /var/lib/mysql/vmi116126.contaboserver.net.err file
MySQL started within 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
Set thread_cache_size to 4 as a starting value
Performance should be activated for better diagnostics
Consider installing Sys schema from GitHub - mysql/mysql-sys: The MySQL sys schema
Variables to adjust:
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
thread_cache_size (start at 4)
performance_schema = ON enable PFS
innodb_buffer_pool_size (>= 1G) if possible.
innodb_log_file_size * innodb_log_files_in_group should be equal to 1/4 of buffer pool size (=64M) if possible.
innodb_buffer_pool_instances (=1)
Code:
MySQLTuner 1.7.2 - Major Hayden [email protected]
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
[!!] Currently running unsupported MySQL version 10.0.31-MariaDB
[OK] Operating on 64-bit architecture
Last edited by a moderator:
