Im running a dedicated server with around 20-25 sites, almost all of them running wordpress installations. Running it over a cpanel setup. For a while its mostly mysql eating most of the cpu and hitting high load times

mysql	0	  61.69(cpu)  6.92(ram)	/usr/sbin/mysqld

**Server config is**

Uptime             70 days 
Operating System   CentOS Linux 7 (Core) x64 File
Handles            14560 of 6511967 
Processes          342  
CPU Model          AMD Ryzen 5 3600 6-Core Processor
Ram                64GB

I am trying to improve this and came across mysql tuner, here is what it has to say after running mysql with performance schema on for 2 days or more. This isnt exactly my forte so the mycnf is just a blend of what I tried with an older mysqltuner suggestion but ive heard that the application would still require a human touch. Would appreciate some help in optimizing the settings.

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

-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/lib/mysql/server.name.here.err exists
[--] Log file: /var/lib/mysql/server.name.here.err(4M)
[OK] Log file /var/lib/mysql/server.name.here.err is readable.
[OK] Log file /var/lib/mysql/server.name.here.err is not empty
[OK] Log file /var/lib/mysql/server.name.here.err is smaller than 32 Mb
[!!] /var/lib/mysql/server.name.here.err contains 31430 warning(s).
[!!] /var/lib/mysql/server.name.here.err contains 23132 error(s).
[--] 60 start(s) detected in /var/lib/mysql/server.name.here.err
[--] 1) 2020-12-07  7:35:16 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2020-12-01 14:35:35 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2020-11-30 16:10:14 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2020-11-30 16:07:53 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2020-11-01  1:57:12 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2020-10-10 19:28:45 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2020-10-10 19:28:32 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 2020-09-30  3:36:14 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 9) 2020-09-28 17:58:16 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 10) 2020-09-25 18:38:33 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 51 shutdown(s) detected in /var/lib/mysql/server.name.here.err
[--] 1) 2020-12-07  7:35:07 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2020-12-01 14:35:27 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2020-11-30 16:09:53 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2020-11-30 16:07:33 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 2020-11-01  1:57:09 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 2020-10-10 19:28:39 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 2020-10-10 19:28:26 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 8) 2020-09-30  3:34:34 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 9) 2020-09-28 17:56:38 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 10) 2020-09-25 18:36:55 0 [Note] /usr/sbin/mysqld: Shutdown complete

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in MyISAM tables: 2.1G (Tables: 1387)
[--] Data in InnoDB tables: 3.2G (Tables: 2207)
[--] Data in MEMORY tables: 586.4K (Tables: 3)
[OK] Total fragmented tables: 0

-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!

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

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 2d 5h 57m 24s (167M q [862.613 qps], 2M conn, TX: 21024G, RX: 379G)
[--] Reads / Writes: 98% / 2%
[--] Binary logging is disabled
[--] Physical Memory     : 62.8G
[--] Max MySQL memory    : 43.5G
[--] Other process memory: 0B
[--] Total buffers: 5.0G global + 260.7M per thread (151 max threads)
[--] P_S Max memory usage: 104M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 26.5G (42.17% of installed RAM)
[OK] Maximum possible memory usage: 43.5G (69.35% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (324K/167M)
[OK] Highest usage of available connections: 55% (84/151)
[OK] Aborted connections: 0.00%  (55/2878495)
[!!] 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] Query cache efficiency: 40.4% (102M cached / 254M selects)
[!!] Query cache prunes per day: 3479297
[OK] Sorts requiring temporary tables: 0% (11K temp sorts / 6M sorts)
[!!] Joins performed without indexes: 12813
[!!] Temporary tables created on disk: 66% (2M on disk / 3M total)
[OK] Thread cache hit rate: 98% (40K created / 2M connections)
[OK] Table cache hit rate: 95% (4K open / 4K opened)
[OK] table_definition_cache(2097152) is upper than number of tables(3862)
[OK] Open file limit used: 7% (2K/40K)
[OK] Table locks acquired immediately: 99% (13M immediate / 13M locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 104.0M
[--] Sys schema is installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 12 thread(s).
[--] Using default value is good enough for your version (10.3.27-MariaDB-log)

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 24.9% (182M used / 734M cache)
[OK] Key buffer size / total MyISAM indexes: 700.0M/460.2M
[OK] Read Key buffer hit rate: 100.0% (1B cached / 41K reads)
[!!] Write Key buffer hit rate: 69.5% (804K cached / 558K writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 4.0G/3.2G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (0.78125 %): 16.0M * 2/4.0G should be equal to 25%
[!!] InnoDB buffer pool instances: 8
[--] Number of InnoDB Buffer Pool Chunk : 32 for 8 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 100.00% (102924116296 hits/ 102924220151 total)
[!!] InnoDB Write Log efficiency: 73.75% (4679039 hits/ 6344450 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1665411 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 99.1% (277M cached / 2M reads)

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

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

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

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: MIXED
[--] XA support enabled: ON
[--] Semi synchronous replication Master: OFF
[--] Semi synchronous replication Slave: OFF
[--] This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Control warning line(s) into /var/lib/mysql/server.name.here.err file
    Control error line(s) into /var/lib/mysql/server.name.here.err file
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    Increasing the query_cache size over 128M may reduce performance
    We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
             See https://dev.mysql.com/doc/internals/en/join-buffer-size.html 
             (specially the conclusions at the bottom of the page).
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU 
Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_size (> 128M) [see warning above]
    join_buffer_size (> 2.0M, or always use indexes with JOINs)
    tmp_table_size (> 32M)
    max_heap_table_size (> 32M)
    innodb_log_file_size should be (=512M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
    innodb_buffer_pool_instances(=4)

**And here is what my.cnf currently has** **Note:** This is now edited based on suggestions by Wilson and Rick, Will save and restart mysql and get back to you guys with updates.

[client-server]

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

[mysqld]
# Logging and performance measurement here
log-error=/var/lib/mysql/ryzen.dogestream.com.err
# for enhanced slow query log
log_slow_verbosity=query_plan,explain
performance-schema=1


max_allowed_packet=268435456
max_heap_table=32M
tmp_table_size=32M
open_files_limit=40000


# Buffer sizes 
join_buffer_size=2M
key_buffer_size=700M
sort_buffer_size=2M


# InnoDB stuff goes here
innodb_file_per_table=1
innodb_buffer_pool_size=15G
innodb_log_file_size=16M
local-infile=0
# from 1024 to conserve 90% of CPU cycles used for function
innodb_lru_scan_depth=100
# should always match table_open_cache
innodb_open_files=9000


# Query stuff goes here
# from 128M to conserve RAM for more useful purposes
query_cache_size=0
# from 1 for OFF to avoid 3 million+ query cache prunes per day
query_cache_type=0
# from 2M to conserve RAM
query_cache_limit=0
slow_query_log=1
slow_query_log_file=mysql-slow.log
long_query_time=0.1


# It says cache it is here
table_definition_cache=-1
# from 3000 to reduce tables opened_tables count
table_open_cache=9000
# from 16 to accomodate your 84 concurrent users
thread_cache_size=256

Thanks for reading! So I'm kind of banging my head against a wall but I firstly want to say that I think this is a DB setup problem rather than the code as even when stripped back to bare bones WC the queries are still slow so no interfering 3rd party code. Basically I have a WP/WooCommerce website that has quite a lot of data (11GB DB) and the WooCommerce core admin queries like listing orders on the orders page is taking 2 seconds plus other queries which totals to 9 seconds in DB queries. I really want to speed up these queries so query monitor plugin doesn't have any slow queries or at very least get much nearer to the 0.2s target but I have asked around and seem to be getting quite a few varied responses such as Redis, object cache and tools to add indexes but someone else fairly enough said that this will obviously add caching and speed up the queries but should the cache be cleared it will still be slow which doesn't (I guess) really solve the original issue? I'm not a DB admin expert by any means so I've previously just chucked memory at the VPS (48GB) and set the InnoDB pool size as high as mysqltuner told me to at 28GB but I get the feeling this is wrong as most google results suggest it should be 8GB or at very max be the same GB amount as the DB is big and the DB is back to being slow! Any ideas on what's going wrong? I've seen other things like increasing innodb_io_capacity (which is currently set to 200 but max is set to 2000 and the VPS is using an SSD) but mysqltuner has not mentioned these variables and I've not updated these values before. Would just like to know if I do just need to implement Redis etc or to start I do need more memory or I do need to change some variables or I need to do them all! Happy to provide what info I can. Thanks in advance, Brad

I am not a database guru. But here's the matter with which I'd like some help. I have a relatively freshly-minted server running AlmaLinux 8.7, with 64GB RAM and a pair of terabyte SSDs. After updating to MariaDB 10.6.x, I configured the my.cnf file to represent a prior server with a similar setup: # # This group is read both both by the client and the server # use it for options that affect everything # [client-server] # # include all files from the config directory # !includedir /etc/my.cnf.d [mysqld] sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION bind-address = 127.0.0.1 # skip-name-resolve=1 query_cache_size=0 query_cache_type=0 local-infile=0 innodb_buffer_pool_size=1024M query_cache_size=64M performance_schema=ON tmp_table_size=96M max_heap_table_size=96M unix_socket=OFF table_definition_cache=2400 key_buffer_size=1024M innodb_buffer_pool_size=3G innodb_log_file_size=750M max_connections=300 !includedir /etc/mysql/releem.conf.d Several days have passed, and I have run the MySQL Tuner. I'd like some guidance about what to change/add/remove form my.cnf based on these suggestions: We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found. See MySQL: Welcome (specially the conclusions at the bottom of the page). 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: skip-name-resolve=1 query_cache_size (=0) query_cache_type (=0) query_cache_size (> 64M) join_buffer_size (> 256.0K, or always use indexes with JOINs) tmp_table_size (> 96M) max_heap_table_size (> 96M) table_definition_cache (2400) > 3113 or -1 (autosizing if supported) key_buffer_size (~ 195M) innodb_buffer_pool_size (>= 3.3G) if possible. NOTE: skip-name-resolve=1 produces a database error in our backup logs, which is why it is disabled. Thanks for any further assistance. I am obviously not a database guru.

UPDATE 6/23/2020: Haven't noticed any odd CPU spikes lately, but noticed that the MySQL memory footprint grows indefinitely until it goes OOM and is killed by the kernel. Where should I start looking first? --- I'm looking for some MySQL tuning help. It seems that every so often mysqld process' CPU consumption shoots to way over 100%, and I'm thinking it's likely due to poor configuration. This server is used to host about 50 accounts along with emails, mostly simple wordpress/joomla installs, along with 5 Magento 1.9 installations. On a linode dedicated server:

16x AMD EPYC 7501 Cores
32 GB RAM
640 GB SSD Space
7 TB Bandwidth

PROCESSLIST: https://pastebin.com/8y12Kbj5 GLOBAL STATUS: https://pastebin.com/LgY6RMT3 GLOBAL VARIABLES: https://pastebin.com/VafyvKaQ ulimit -a

core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 128365
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 128365
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

iostat -xm 5 3

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.92    0.13    0.23    0.02    0.01   98.69

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
loop0             0.00     0.00    0.00    0.59     0.00     0.00    13.75     0.00    0.28    1.15    0.27   0.49   0.03
sda               0.61    18.91   12.09   13.38     0.56     0.69   100.41     0.00    0.56    0.29    0.81   0.48   1.22
sdb               0.00     0.03    0.00    0.00     0.00     0.00    70.58     0.00    2.53    0.39    3.41   3.46   0.00

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           7.01    0.00    0.89    0.01    0.01   92.07

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
loop0             0.00     0.00    0.00    1.80     0.00     0.01     8.00     0.00    0.22    0.00    0.22   0.78   0.14
sda               0.00    31.60    0.00   24.60     0.00     0.39    32.33     0.00    0.66    0.00    0.66   0.46   1.12
sdb               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.51    0.00    0.13    0.01    0.00   99.35

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
loop0             0.00     0.00    0.00    1.80     0.00     0.01     8.00     0.00    0.33    0.00    0.33   0.33   0.06
sda               0.00    11.20    0.00   29.80     0.00     0.30    20.83     0.00    0.92    0.00    0.92   0.20   0.60
sdb               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00

Server version: 5.7.29-log MySQL Community Server (GPL) My.cnf:

[mysqld]
# Required Settings
basedir                         = /usr/
bind_address                    = *
datadir                         = /var/lib/mysql/
max_allowed_packet              = 256M
max_connect_errors              = 1000000
port                            = 3306
skip_external_locking
socket                          = /var/lib/mysql/mysql.sock
tmpdir                          = /tmp
user                            = mysql
performance_schema              = 1 # FROM 0 to enable better diagnostics
sql_mode                        = ""

# InnoDB Settings
default_storage_engine          = InnoDB
innodb_buffer_pool_instances    = 18 # FROM 1

# Update from 1G > 12G
innodb_buffer_pool_size         = 18G
# ADDED 3/23/2020 IGNT
innodb_log_file_size		= 2G
innodb_file_per_table           = 1
innodb_flush_method             = O_DIRECT
innodb_log_buffer_size          = 16M
innodb_log_files_in_group       = 2
innodb_stats_on_metadata        = 0

#innodb_thread_concurrency      = 15
innodb_read_io_threads          = 8
innodb_write_io_threads         = 8

# MyISAM Settings
query_cache_limit               = 0 # from 4M
query_cache_size                = 0 # from 128M
query_cache_type                = 0 # from 1

low_priority_updates            = 1
concurrent_insert               = 2

# Connection Settings
max_connections                 = 150

# Buffer Settings
# IGNT DISABLED ALL TO CHECK 
join_buffer_size                = 128M
#read_buffer_size                = 32M
#read_rnd_buffer_size            = 64M
#sort_buffer_size                = 64M
open_files_limit		= 20000 # should be greater than table_open_cache

# Search Settings
ft_min_word_len                 = 4

# Logging
log_error                       = /var/lib/mysql/mysql_error.log
long_query_time                 = 5
slow_query_log                  = 1
slow_query_log_file             = /var/lib/mysql/mysql_slow.log


# Custom ADDS IGNT
#skip-name-resolve
innodb_flush_log_at_trx_commit	= 2
innodb_io_capacity		= 4000
innodb_io_capacity_max		= 8000
key_buffer_size			= 256M
table_open_cache		= 10000
table_definition_cache		= 10000
tmp_table_size			= 256M
max_heap_table_size		= 256M
innodb_buffer_pool_instances	= 12

MySQLtuner.pl results: (UPDATED 3/31/2020 8:48PM EST)

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

-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/lib/mysql/mysql_error.log exists
[--] Log file: /var/lib/mysql/mysql_error.log(692K)
[OK] Log file /var/lib/mysql/mysql_error.log is readable.
[OK] Log file /var/lib/mysql/mysql_error.log is not empty
[OK] Log file /var/lib/mysql/mysql_error.log is smaller than 32 Mb
[!!] /var/lib/mysql/mysql_error.log contains 2700 warning(s).
[!!] /var/lib/mysql/mysql_error.log contains 344 error(s).
[--] 32 start(s) detected in /var/lib/mysql/mysql_error.log
[--] 1) 2020-03-24T09:58:01.690843Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2020-03-24T04:50:09.880286Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2020-03-24T04:48:14.253324Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2020-03-24T04:33:47.976873Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2020-03-24T04:20:51.868881Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2020-03-24T04:20:46.399280Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2020-03-23T12:05:22.733706Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 2020-03-23T05:52:41.442704Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 9) 2020-03-23T05:48:46.305524Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 10) 2020-03-23T05:47:42.353869Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 47 shutdown(s) detected in /var/lib/mysql/mysql_error.log
[--] 1) 2020-03-24T09:57:57.724742Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2020-03-24T04:50:06.601039Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2020-03-24T04:48:04.688209Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2020-03-24T04:33:44.609318Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 2020-03-24T04:20:48.549320Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 2020-03-24T04:20:42.966162Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 2020-03-23T12:05:19.945269Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 8) 2020-03-23T05:52:38.732313Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 9) 2020-03-23T05:48:43.664346Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 10) 2020-03-23T05:47:39.754910Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 169.9M (Tables: 811)
[--] Data in InnoDB tables: 1.6G (Tables: 5313)
[--] Data in MEMORY tables: 0B (Tables: 169)
[OK] Total fragmented tables: 0

-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!

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

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 7d 14h 46m 59s (35M q [53.712 qps], 464K conn, TX: 62G, RX: 9G)
[--] Reads / Writes: 96% / 4%
[--] Binary logging is disabled
[--] Physical Memory     : 31.3G
[--] Max MySQL memory    : 74.9G
[--] Other process memory: 0B
[--] Total buffers: 18.5G global + 384.9M per thread (150 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 67.4G (214.94% of installed RAM)
[!!] Maximum possible memory usage: 74.9G (238.92% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (3K/35M)
[!!] Highest connection usage: 86%  (130/150)
[OK] Aborted connections: 0.03%  (134/464170)
[!!] 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% (3K temp sorts / 9M sorts)
[!!] Joins performed without indexes: 70704
[!!] Temporary tables created on disk: 56% (3M on disk / 6M total)
[OK] Thread cache hit rate: 99% (658 created / 464K connections)
[!!] Table cache hit rate: 0% (7K open / 1M opened)
[OK] table_definition_cache(10000) is upper than number of tables(6572)
[OK] Open file limit used: 7% (1K/15K)
[OK] Table locks acquired immediately: 99% (9M immediate / 9M locks)

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

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

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 23.3% (62M used / 268M cache)
[OK] Key buffer size / total MyISAM indexes: 256.0M/27.0M
[OK] Read Key buffer hit rate: 100.0% (90M cached / 36K reads)
[!!] Write Key buffer hit rate: 73.2% (447K cached / 327K writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 18.0G/1.6G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 2.0G * 2/18.0G should be equal to 25%
[!!] InnoDB buffer pool instances: 12
[--] Number of InnoDB Buffer Pool Chunk : 144 for 12 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 100.00% (11369995313 hits/ 11370050077 total)
[!!] InnoDB Write Log efficiency: 68.92% (1625414 hits/ 2358577 total)
[OK] InnoDB log waits: 0.00% (0 waits / 733163 writes)

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

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

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

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

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Control warning line(s) into /var/lib/mysql/mysql_error.log file
    Control error line(s) into /var/lib/mysql/mysql_error.log file
    Reduce your overall MySQL memory footprint for system stability
    Dedicate this server to your database for highest performance.
    Reduce or eliminate persistent connections to reduce connection usage
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
             See https://dev.mysql.com/doc/internals/en/join-buffer-size.html 
             (specially the conclusions at the bottom of the page).
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: https://bit.ly/2Fulv7r 
    Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/ 
    This is MyISAM only table_cache scalability problem, InnoDB not affected.
    See more details here: https://bugs.mysql.com/bug.php?id=49177 
    This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
    Beware that open_files_limit (15000) variable
    should be greater than table_open_cache (7420)
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    max_connections (> 150)
    wait_timeout ( 128.0M, or always use indexes with JOINs)
    table_open_cache (> 7420)
    innodb_buffer_pool_instances(=18)

I ran the mysqltuner script on my server that has been working continuously for 1 week. I share the values I got and the existing mysql configuration file. Could you help me? When I looked on the internet, I couldn't see a very detailed solution. **MySQL Tuner Output**

[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials from Debian maintenance account.
[OK] Currently running supported MySQL version 5.7.32-0ubuntu0.18.04.1
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[!!] log_error is set to stderr MT can't read stderr

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 173.9K (Tables: 5)
[--] Data in InnoDB tables: 1.0G (Tables: 110)
[OK] Total fragmented tables: 0

-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[!!] User 'root@localhost' has no password set.
[!!] There is no basic password file list!

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

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 3d 6h 34m 46s (40M q [143.364 qps], 346K conn, TX: 133G, RX: 8G)
[--] Reads / Writes: 94% / 6%
[--] Binary logging is disabled
[--] Physical Memory     : 3.9G
[--] Max MySQL memory    : 51.7G
[--] Other process memory: 0B
[--] Total buffers: 1.4G global + 257.8M per thread (200 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 4.7G (121.05% of installed RAM)
[!!] Maximum possible memory usage: 51.7G (1342.95% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/40M)
[OK] Highest usage of available connections: 6% (13/200)
[OK] Aborted connections: 0.00%  (0/346968)
[--] Skipped name resolution test due to skip_networking=ON in system variables.
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Query cache efficiency: 72.5% (27M cached / 38M selects)
[!!] Query cache prunes per day: 105070
[OK] Sorts requiring temporary tables: 0% (720 temp sorts / 421K sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 18% (35K on disk / 188K total)
[OK] Thread cache hit rate: 99% (13 created / 346K connections)
[OK] Table cache hit rate: 95% (1K open / 1K opened)
[OK] table_definition_cache(2048) is upper than number of tables(394)
[OK] Open file limit used: 0% (48/5K)
[OK] Table locks acquired immediately: 100% (789 immediate / 789 locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema is installed.

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

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (48M used / 268M cache)
[OK] Key buffer size / total MyISAM indexes: 256.0M/102.0K
[OK] Read Key buffer hit rate: 99.8% (8K cached / 16 reads)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 4
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 1.0G/1.0G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (9.375 %): 48.0M * 2/1.0G should be equal to 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 8 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.97% (2850068636 hits/ 2850872999 total)
[!!] InnoDB Write Log efficiency: 23.15% (238067 hits/ 1028533 total)
[OK] InnoDB log waits: 0.00% (0 waits / 790466 writes)

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

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

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

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

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Set up a Secure Password for root@localhost user: SET PASSWORD FOR 'root'@'SpecificDNSorIp' = PASSWORD('secure_password');
    Reduce your overall MySQL memory footprint for system stability
    Dedicate this server to your database for highest performance.
    Increasing the query_cache size over 128M may reduce performance
    Performance schema should be activated for better diagnostics
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU 
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_size (> 128M) [see warning above]
    performance_schema = ON enable PFS
    innodb_buffer_pool_size (>= 1.0G) if possible.
    innodb_log_file_size should be (=128M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

**MySQL Configuration File**

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html 

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0

[mysqld]
#
# * Basic Settings
#
user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /var/lib/mysql
tmpdir      = /var/tmpfs
tmp_table_size=2K
lc-messages-dir	= /usr/share/mysql
skip-external-locking
bind-address		= 127.0.0.1

performance_schema=off
skip-networking
tmpdir=/tmp
max_connections=200
max_user_connections=200
key_buffer_size=256M
myisam_sort_buffer_size=128M
join_buffer_size=1M
read_buffer_size=128K
sort_buffer_size=128K
table_open_cache=3072
thread_cache_size=1024
table_definition_cache=2048
open_files_limit=52000
wait_timeout=120
connect_timeout=120
max_heap_table_size=128M
max_allowed_packet=268435456
query_cache_limit=4M
query_cache_size=128M
query_cache_type=1
interactive_timeout=120
max_connect_errors=15
local-infile=0
#innodb_additional_mem_pool_size=64M
innodb_flush_method=O_DIRECT
default-storage-engine=InnoDB
innodb_buffer_pool_size=1000M
innodb_buffer_pool_instances=2	
innodb_log_buffer_size=16M
innodb_file_per_table=1
innodb_file_format = barracuda
innodb_thread_concurrency=4
innodb_flush_log_at_trx_commit = 1
innodb_write_io_threads=2
innodb_read_io_threads=2

#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1

[isamchk]
key_buffer=512M
sort_buffer_size=2M
read_buffer=2M
write_buffer=2M

[myisamchk]
tmpdir=/tmp
key_buffer=512M
sort_buffer=2M
read_buffer=2M
write_buffer=2M

I would be glad if you help. What settings should I change?

I have a server that is giving me CPU spikes and and is slow in serving pages: ### Server information Processor: X3440 (2.53GHz Quad Core + HT) Memory Type: 8GB DDR3-1333 REG ECC ### Memory information root@core [/programas/tuning-primer]# free -m total used free shared buff/cache available Mem: 7814 1651 1770 342 4392 5517 Swap: 8064 391 7673 ### MySQL tuning primer results root@core [/programas/tuning-primer]# ./tuning-primer.sh -- MYSQL PERFORMANCE TUNING PRIMER 2.0.1-r1 -- - By: Matthew Montgomery - - By: Markus Kohlmeyer - MySQL Version 5.7.29-log x86_64 Uptime = 2 days 14 hrs 50 min 22 sec Avg. qps = 1111 Total Questions = 251486181 Threads Connected = 9 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 enabled. Current long_query_time = 10.000000 sec. You have 3939000 out of 251487114 that take longer than 10.000000 sec. to complete Your long_query_time may be too high, I typically set this under 5 sec. BINARY UPDATE LOG The binary update log is NOT enabled. You will not be able to do point in time recovery See http://dev.mysql.com/doc/refman/5.7/en/point-in-time-recovery.html WORKER THREADS Current thread_cache_size = 9 Current threads_cached = 2 Current threads_per_sec = 0 Historic threads_per_sec = 0 Your thread_cache_size is fine MAX CONNECTIONS Current max_connections = 151 Current threads_connected = 8 Historic max_used_connections = 21 The number of used connections is 13% of the configured maximum. Your max_connections variable seems to be fine. INNODB STATUS Current InnoDB index space = 650 M Current InnoDB data space = 1.03 G Current InnoDB buffer pool free = 10 % Current innodb_buffer_pool_size = 128 M Depending on how much space your innodb indexes take up it may be safe to increase this value to up to 2 / 3 of total system memory MEMORY USAGE Max Memory Ever Allocated : 176 M Configured Max Per-thread Buffers : 169 M Configured Max Global Buffers : 153 M Configured Max Memory Limit : 322 M Plus 16 M per temporary table created Physical Memory : 7.63 G Max memory limit seem to be within acceptable norms KEY BUFFER Current MyISAM index space = 211 K Current key_buffer_size = 8 M Key cache miss rate is 1 : 69 Key buffer free ratio = 81 % Your key_buffer_size seems to be fine QUERY CACHE Query cache is enabled Current query_cache_size = 1 M Current query_cache_used = 16 K Current query_cache_limit = 1 M Current Query cache Memory fill ratio = 1.59 % Current query_cache_min_res_unit = 4 K Your query_cache_size seems to be too high. Perhaps you can use these resources elsewhere MySQL won't cache query results that are larger than query_cache_limit in 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 363 queries where a join could not use an index properly You should enable "log-queries-not-using-indexes" Then look for non indexed joins in the slow query log. OPEN FILES LIMIT Current open_files_limit = 10000 files The open_files_limit should typically be set to at least 2x-3x that of table_open_cache if you have heavy MyISAM usage. Your open_files_limit value seems to be fine TABLE CACHE Current table_open_cache = 2000 tables Current table_definition_cache = 1400 tables You have a total of 1865 tables You have 2000 open tables. Current table_open_cache hit rate is 0% , while 100% of your table cache is in use You should probably increase your table_open_cache You should probably increase your table_definition_cache value. TEMP TABLES Current max_heap_table_size = 16 M Current tmp_table_size = 16 M Of 123362 temp tables, 39% were created on disk Perhaps you should increase your tmp_table_size and/or max_heap_table_size to reduce the number of disk-based temporary tables Note! BLOB and TEXT columns are not allow in memory tables. If you are using these columns raising these values might not impact your ratio of on disk temp tables. TABLE SCANS Current read_buffer_size = 128 K Current table scan ratio = 279 : 1 read_buffer_size seems to be fine TABLE LOCKING Current Lock Wait ratio = 1 : 8031 Your table locking seems to be fine ## MySQLTuner results root@core [/programas]# ./mysqltuner.pl >> MySQLTuner 1.7.15 - 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 [OK] Currently running supported MySQL version 5.7.29-log [OK] Operating on 64-bit architecture -------- Log file Recommendations ------------------------------------------------------------------ [--] Log file: /var/log/mysql/mysql_error.log(1K) [OK] Log file /var/log/mysql/mysql_error.log exists [OK] Log file /var/log/mysql/mysql_error.log is readable. [OK] Log file /var/log/mysql/mysql_error.log is not empty [OK] Log file /var/log/mysql/mysql_error.log is smaller than 32 Mb [!!] /var/log/mysql/mysql_error.log contains 5 warning(s). [!!] /var/log/mysql/mysql_error.log contains 5 error(s). [--] 0 start(s) detected in /var/log/mysql/mysql_error.log [--] 0 shutdown(s) detected in /var/log/mysql/mysql_error.log -------- Storage Engine Statistics ----------------------------------------------------------------- [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA [--] Data in MyISAM tables: 352.7K (Tables: 12) [--] Data in InnoDB tables: 1.7G (Tables: 1734) [OK] Total fragmented tables: 0 -------- Analysis Performance Metrics -------------------------------------------------------------- [--] innodb_stats_on_metadata: OFF [OK] No stat updates during querying INFORMATION_SCHEMA. -------- Security Recommendations ------------------------------------------------------------------ [OK] There are no anonymous accounts for any database users [!!] failed to execute: SELECT CONCAT(user, '@', host) FROM mysql.user WHERE (IF(plugin='mysql_native_password', authentication_string, password) = '' OR IF(plugin='mysql_native_password', authentication_string, password) IS NULL) AND plugin NOT IN ('unix_socket', 'win_socket', 'auth_pam_compat') [!!] FAIL Execute SQL / return code: 256 [OK] All database users have passwords assigned [!!] failed to execute: SELECT CONCAT(user, '@', host) FROM mysql.user WHERE CAST(IF(plugin='mysql_native_password', authentication_string, password) as Binary) = PASSWORD(user) OR CAST(IF(plugin='mysql_native_password', authentication_string, password) as Binary) = PASSWORD(UPPER(user)) OR CAST(IF(plugin='mysql_native_password', authentication_string, password) as Binary) = PASSWORD(CONCAT(UPPER(LEFT(User, 1)), SUBSTRING(User, 2, LENGTH(User)))) [!!] FAIL Execute SQL / return code: 256 [!!] User 'sce01@%' does not specify hostname restrictions. [!!] User 'sce01_cdmx@%' does not specify hostname restrictions. [!!] User 'sce01_frap@%' does not specify hostname restrictions. [!!] User 'sce01_rest@%' does not specify hostname restrictions. [!!] User 'sce01_resumen@%' does not specify hostname restrictions. [!!] User 'sce01_roberto@%' does not specify hostname restrictions. [!!] User 'sce01_sce@%' does not specify hostname restrictions. [!!] User 'sce06@%' does not specify hostname restrictions. [!!] User 'sce06_mexico@%' does not specify hostname restrictions. [!!] User 'sce09@%' does not specify hostname restrictions. [!!] User 'sce09_chiapas@%' does not specify hostname restrictions. [!!] User 'sce09_fraps@%' does not specify hostname restrictions. [!!] User 'sce09_resumen@%' does not specify hostname restrictions. [!!] User 'sce09_roberto@%' does not specify hostname restrictions. [!!] User 'sce09_sce@%' does not specify hostname restrictions. [!!] User 'sce13@%' does not specify hostname restrictions. [!!] User 'sce13_coahuila@%' does not specify hostname restrictions. [!!] User 'sce13_resumen@%' does not specify hostname restrictions. [!!] User 'sce13_roberto@%' does not specify hostname restrictions. [!!] User 'sce13_sce@%' does not specify hostname restrictions. [!!] User 'sce13_usrDB1@%' does not specify hostname restrictions. [!!] There is no basic password file list! -------- CVE Security Recommendations -------------------------------------------------------------- [--] Skipped due to --cvefile option undefined -------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 2d 15h 3m 34s (252M q [1K qps], 3M conn, TX: 348G, RX: 10G) [--] Reads / Writes: 97% / 3% [--] Binary logging is disabled [--] Physical Memory : 7.6G [--] Max MySQL memory : 338.9M [--] Other process memory: 0B [--] Total buffers: 169.0M global + 1.1M per thread (151 max threads) [--] P_S Max memory usage: 0B [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 192.6M (2.46% of installed RAM) [OK] Maximum possible memory usage: 338.9M (4.34% of installed RAM) [OK] Overall possible memory usage with other process is compatible with memory available [OK] Slow queries: 1% (3M/252M) [OK] Highest usage of available connections: 13% (21/151) [OK] Aborted connections: 0.15% (5155/3394076) [!!] 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 / 109M selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (6K temp sorts / 1M sorts) [OK] No joins without indexes [!!] Temporary tables created on disk: 64% (81K on disk / 125K total) [OK] Thread cache hit rate: 99% (4K created / 3M connections) [!!] Table cache hit rate: 0% (2K open / 421K opened) [OK] Open file limit used: 0% (25/10K) [OK] Table locks acquired immediately: 99% (16K immediate / 16K locks) -------- Performance schema ------------------------------------------------------------------------ [--] Performance schema is disabled. [--] Memory used by P_S: 0B [--] Sys schema is installed. -------- ThreadPool Metrics ------------------------------------------------------------------------ [--] ThreadPool stat is disabled. -------- MyISAM Metrics ---------------------------------------------------------------------------- [!!] Key buffer used: 18.4% (1M used / 8M cache) [OK] Key buffer size / total MyISAM indexes: 8.0M/211.0K [OK] Read Key buffer hit rate: 98.6% (22K cached / 315 reads) [OK] Write Key buffer hit rate: 100.0% (6 cached / 6 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.7G [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal to 25% [OK] InnoDB buffer pool instances: 1 [--] Number of InnoDB Buffer Pool Chunk : 1 for 1 Buffer Pool Instance(s) [OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances [OK] InnoDB Read buffer efficiency: 99.93% (16944063226 hits/ 16956171653 total) [!!] InnoDB Write Log efficiency: 13.72% (357933 hits/ 2608008 total) [OK] InnoDB log waits: 0.00% (0 waits / 2250075 writes) -------- AriaDB Metrics ---------------------------------------------------------------------------- [--] AriaDB is disabled. -------- TokuDB Metrics ---------------------------------------------------------------------------- [--] TokuDB is disabled. -------- XtraDB Metrics ---------------------------------------------------------------------------- [--] XtraDB is disabled. -------- Galera Metrics ---------------------------------------------------------------------------- [--] Galera is disabled. -------- Replication Metrics ----------------------------------------------------------------------- [--] Galera Synchronous replication: NO [--] No replication slave(s) for this server. [--] Binlog format: ROW [--] XA support enabled: ON [--] Semi synchronous replication Master: Not Activated [--] Semi synchronous replication Slave: Not Activated [--] This is a standalone server -------- Recommendations --------------------------------------------------------------------------- General recommendations: Control warning line(s) into /var/log/mysql/mysql_error.log file Control error line(s) into /var/log/mysql/mysql_error.log file Restrict Host for user@% to user@SpecificDNSorIp 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 Increase table_open_cache gradually to avoid file descriptor limits Read this before increasing table_open_cache over 64: https://bit.ly/1mi7c4C Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/ This is MyISAM only table_cache scalability problem, InnoDB not affected. See more details here: https://bugs.mysql.com/bug.php?id=49177 This bug already fixed in MySQL 5.7.9 and newer MySQL versions. Beware that open_files_limit (10000) variable should be greater than table_open_cache (2000) Performance schema should be activated for better diagnostics Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU 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) table_open_cache (> 2000) performance_schema = ON enable PFS innodb_buffer_pool_size (>= 1.7G) if possible. innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size. root@core [/programas]# ### My.cnf [mysqld] performance-schema=0 default-storage-engine=MyISAM innodb_file_per_table=1 max_allowed_packet=268435456 open_files_limit=10000 #Logueo [mysqld_safe] log_error=/var/log/mysql/mysql_error.log [mysqld] log_error=/var/log/mysql/mysql_error.log general_log_file = /var/log/mysql/mysql.log general_log = 1 slow_query_log_file = /var/log/mysql/mysql-slow.log slow_query_log = 1 log-queries-not-using-indexes What do I need to adjust in order to increase the performance of my MySQL instance?

I am facing more CPU usage for my app server. Server configuration is as below. 1> CPU: Intel(R) Xeon(R) CPU E5-2650 v4 @ 2.20GHz 8 core 2> RAM: 32 GB 3> 600 GB SSD NVRAM 4> Mariadb version 10.3.17. 5> OS: Debian 9 I have run mysql tuning script to optimize server performance and add some parameter in my.cnf. But yet it's not creating big impact. I can see that RAM used very less compare to CPU. How can I balance in both and improve server performance? Here is my tuner script output tunner . Here is current my.cnf output on my.cnf

I'm not a DB Administrator but am looking for advice on tuning our MySQL database. It's a small storefront that is running Prestashop, and lags out for several minutes when saving changes to a product pack. That is, a product that is made up of 2 or more other products. I just ran MySQLTuner-Perl and it looks alright for the most part, but there are several points flagged for review. [!!] Aborted connections: 6.83% (337899/4948004) [!!] CPanel and Flex system skip-name-resolve should be on [!!] Joins performed without indexes: 10213707 I suspect the 10M Joins performed without indexes is the likely culprit here, but not entirely sure what that means or how it should be addressed. The server has tons of spare memory so I could increase the allocation 2-3x as a temporary bandaid, but I imagine this won't be resolved until I figure out the indexing issue. Any advice or clarity on these results would be greatly appreciated.

Hi we faced an issue on our server and it go verry slow for more tan 1 month and it go worst every day. I am really not server administrator, This is mysqltuner.pl result,

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE 
[--] Data in InnoDB tables: 16.9G (Tables: 4633)
[--] Data in MyISAM tables: 13.3M (Tables: 150)
[--] Data in Aria tables: 4.1M (Tables: 1)
[OK] Total fragmented tables: 0
 
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.

[--] Up for: 14m 59s (9M q [10K qps], 7K conn, TX: 5G, RX: 2G)
[--] Reads / Writes: 99% / 1%
[--] Binary logging is enabled (GTID MODE: ON)
[--] Physical Memory     : 125.8G
[--] Max MySQL memory    : 367.5G
[--] Other process memory: 0B
[--] Total buffers: 52.4G global + 322.6M per thread (1000 max threads)
[--] Performance_schema Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 75.8G (60.21% of installed RAM)
[!!] Maximum possible memory usage: 367.5G (292.12% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (22/9M)
[OK] Highest usage of available connections: 7% (74/1000)
[OK] Aborted connections: 0.00% (0/7652)
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Query cache efficiency: 48.7% (8M cached / 16M selects)
[!!] Query cache prunes per day: 27571979
[OK] Sorts requiring temporary tables: 0% (73 temp sorts / 135K sorts)
[!!] Joins performed without indexes: 5959
[OK] Temporary tables created on disk: 16% (27K on disk / 164K total)
[OK] Thread cache hit rate: 99% (74 created / 7K connections)
[OK] Table cache hit rate: 99% (1M hits / 1M requests)
[OK] table_definition_cache (7000) is greater than number of tables (4945)
[OK] Open file limit used: 1% (364/32K)
[OK] Table locks acquired immediately: 100% (884 immediate / 884 locks)
[OK] Binlog cache memory access: 99.45% (5746 Memory / 5778 Total)-------- Performance schema ------------------------------------------------------------------------
[!!] Performance_schema should be activated.
[--] Sys schema is not installed.
 
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
 
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.3% (23.4M used / 128.0M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/5.9M
[!!] Read Key buffer hit rate: 91.5% (317 cached / 27 reads)
 
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 16
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 50.0G / 16.9G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 7.0G * 2/50.0G should be equal to 25%
[OK] InnoDB buffer pool instances: 50
[--] Number of InnoDB Buffer Pool Chunk: 400 for 50 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.87% (693596513 hits / 694467712 total)
[OK] InnoDB Write Log efficiency: 99.80% (6253500 hits / 6265916 total)
[OK] InnoDB log waits: 0.00% (0 waits / 12416 writes)
 
-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/40.0K
[OK] Aria pagecache hit rate: 95.4% (151K cached / 6K reads)
 
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
 
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
 
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
 
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] This server is acting as master for 4 server(s).
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: OFF
[--] Semi synchronous replication Slave: OFF
[--] No replication setup for this server or replication not started.
Reduce your overall MySQL memory footprint for system stability
    Dedicate this server to your database for highest performance.
    We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
             See https://dev.mysql.com/doc/internals/en/join-buffer-size.html 
             (specially the conclusions at the bottom of the page).
    Performance schema should be activated for better diagnostics
    Consider installing Sys schema from https://github.com/FromDual/mariadb-sys  for MariaDB
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_size (> 64M)
    join_buffer_size (> 256.0M, or always use indexes with JOINs)
    performance_schema=ON
    key_buffer_size (~ 24M)

I see Maximum possible memory usage: 367.5G (292.12% of installed RAM) is very high but dont find how to reduce it, Also if you can help me to configure my.cnf better for have real better performance i will be really greatfull :) edit adding .cnf

#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]

#
# * Basic Settings
#
user                    = mysql
pid-file                = /run/mysqld/mysqld.pid
socket                  = /run/mysqld/mysqld.sock
port                   = 3306
basedir                 = /usr
datadir                 = /var/lib/mysql
tmpdir                  = /tmp
lc-messages-dir         = /usr/share/mysql
skip-external-locking
skip-name-resolve
sql_mode		= "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 0.0.0.0

#
# * Fine Tuning
#
open_files_limit               = 20240
sort_buffer_size               = 2097144
table_cache		       = 7000
table_open_cache               = 7000
table_definition_cache	       = 7000
tmp_table_size                 = 128M
max_heap_table_size            = 128M
max_connections                = 1000
connect_timeout                = 10
wait_timeout                   = 120
interactive_timeout            = 1800
max_allowed_packet             = 64M
bulk_insert_buffer_size        = 16M
thread_stack                   = 256K
thread_cache_size              = 128
thread_pool_size               = 24
log_warnings                   = 1

# MyISAM
key_buffer_size                = 128M
join_buffer_size               = 256M
myisam_recover_options         = BACKUP
myisam_repair_threads          = 1
myisam_sort_buffer_size        = 512M
concurrent_insert              = 2
read_buffer_size               = 131072
read_rnd_buffer_size           = 262144

#
# * Query Cache Configuration
#
query_cache_type	= 1
query_cache_limit      = 32M
query_cache_size        = 64M

#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
general_log_file       = /var/log/mysql/mysql.log
general_log            = 0
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Enable the slow query log to see queries with especially long duration
slow_query_log         = on
slow_query_log_file    = /var/log/mysql/mariadb-slow.log
long_query_time        = 4
#log_slow_rate_limit    = 1000
#log_slow_verbosity     = query_plan
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
server-id              = 1
auto_increment_increment       = 1
auto_increment_offset          = 1
log_bin                = /var/log/mysql/mysql-bin.log
expire_logs_days        = 2
max_binlog_size        = 100M
binlog_format                  = ROW
sync_binlog                    = 0
#binlog_do_db           = include_database_name
#binlog_ignore_db       = exclude_database_name

#
# * Security Features
#
# Read the manual, too, if you want chroot!
#chroot = /var/lib/mysql/
#
# For generating SSL certificates you can use for example the GUI tool "tinyca".
#
#ssl-ca = /etc/mysql/cacert.pem
#ssl-cert = /etc/mysql/server-cert.pem
#ssl-key = /etc/mysql/server-key.pem
#
# Accept only connections using the latest and most secure TLS protocol version.
# ..when MariaDB is compiled with OpenSSL:
#ssl-cipher = TLSv1.2
# ..when MariaDB is compiled with YaSSL (default in Debian):
#ssl = on

#
# * Character sets
#
# MySQL/MariaDB default is Latin1, but in Debian we rather default to the full
# utf8 4-byte character set. See also client.cnf
#
character-set-server  = utf8mb4
collation-server      = utf8mb4_general_ci

#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!

## InnoDB tuning

innodb_file_per_table          = on
innodb_buffer_pool_size        = 50G
innodb_buffer_pool_instances   = 50
innodb_log_file_size           = 7G
innodb_log_buffer_size         = 2G
innodb_thread_concurrency      = 16
innodb_read_io_threads         = 16
innodb_write_io_threads        = 16
innodb_open_files              = 1000
innodb_io_capacity             = 800
innodb_io_capacity_max         = 2000
innodb_lock_wait_timeout       = 60
innodb_flush_method            = O_DIRECT
innodb_doublewrite             = true
innodb_use_native_aio          = 1
innodb_flush_log_at_trx_commit = 2

default_storage_engine         = InnoDB

#
# * Unix socket authentication plugin is built-in since 10.0.22-6
#
# Needed so the root database user can authenticate without a password but
# only when running as the unix root user.
#
# Also available for other users if required.
# See https://mariadb.com/kb/en/unix_socket-authentication-plugin/ 

# this is only for embedded server
[embedded]

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

# This group is only read by MariaDB-10.3 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.3]

Adding Top command but dont know how to fix :) with top commande i have this

PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND   
 4162 mysql     20   0   64,4g  17,4g  10608 S 102,6  13,9 941:18.01 mysqld    
 9985 www-fou+  20   0  395960 221516 126856 R 100,0   0,2  14:55.02 php-fpm7.1
 1593 www-fou+  20   0  381424 203876 123500 R  99,7   0,2  11:24.43 php-fpm7.1
19099 www-fou+  20   0  381788 204924 124484 S  93,4   0,2  10:35.16 php-fpm7.1
24495 www-fou+  20   0  383684 212224 129636 R  69,1   0,2  11:37.65 php-fpm7.1
15019 www-fou+  20   0  339196 127520  89412 S  66,4   0,1   2:51.54 php-fpm7.1
11226 www-fou+  20   0  361036 181036 121012 S  65,8   0,1   8:38.26 php-fpm7.1
13478 www-fou+  20   0  333628 157600 125252 S  12,2   0,1  12:15.50 php-fpm7.1
26195 www-fou+  20   0  343160 165036 123072 S   6,2   0,1  11:20.56 php-fpm7.1
22301 www-fou+  20   0  340908 148244 108692 S   3,0   0,1  12:58.10 php-fpm7.1
21292 www-data  20   0 2316000  23908   4088 S   1,6   0,0   0:30.03 apache2   
29112 www-fou+  20   0  330464 156296 126860 S   1,6   0,1  10:24.75 php-fpm7.1
 3229 root      20   0   12680   5084   2944 R   1,3   0,0   0:02.16 top       
18330 www-data  20   0 2249248  28736   3908 S   1,3   0,0   4:00.44 apache2   
31957 www-fou+  20   0  326216 150860 125900 S   1,3   0,1  12:48.35 php-fpm7.1
  892 www-fou+  20   0 1044388 195248  15528 S   1,0   0,1 766:53.93 node      
14357 www-fou+  20   0  331084 121516  91724 S   1,0   0,1   2:17.92 php-fpm7.1

Thank you, Mathieu

I'm currently working on optimizing the performance of my MariaDB database and have come across a tool called mysqltuner. I've installed and run mysqltuner on my server, and it provided me with some recommendations and insights into the current state of my MariaDB configuration. However, I'm looking for advice and additional guidance on how to interpret and implement the suggestions provided by mysqltuner effectively. I'm using the server for this database only but user tell me that slow queries because server not properly tuning MariaDB 10.6, Apache 2.4.52. How can I interpret the recommendations given by mysqltuner accurately? What do the various metrics and values signify, and how do they relate to the performance of my MariaDB database? server requirements

CPU(s) 32 x Intel(R) Xeon(R) Silver 4314 CPU @ 2.40GHz
RAM 32 GB

MYSQL Tuner Output

>>  MySQLTuner 2.2.6
[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials from Debian maintenance account.
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics ---------------------------------------------                                                                                        --------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA                                                                                         +SEQUENCE
[--] Data in Aria tables: 32.0K (Tables: 1)
[--] Data in MyISAM tables: 13.3M (Tables: 165)
[--] Data in InnoDB tables: 33.4G (Tables: 2578)
[--] Data in MEMORY tables: 0B (Tables: 6)
[OK] Total fragmented tables: 0

[OK] Currently running supported MySQL version 10.6.14-MariaDB-1:10.6.14+maria~u                                                                                        bu2204
-------- Log file Recommendations ----------------------------------------------                                                                                        --------------------
[!!] Log file  doesn't exist

-------- Analysis Performance Metrics ------------------------------------------                                                                                        --------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
Use of uninitialized value $opt{"structstat"} in numeric eq (==) at
        ./mysqltuner.pl line 5810 (#1)
    (W uninitialized) An undefined value was used as if it were already
    defined.  It was interpreted as a "" or a 0, but maybe it was a mistake.
    To suppress this warning assign a defined value to your variables.

    To help you figure out what was undefined, perl will try to tell you
    the name of the variable (if any) that was undefined.  In some cases
    it cannot do this, so it also tells you what operation you used the
    undefined value in.  Note, however, that perl optimizes your program
    and the operation displayed in the warning may not necessarily appear
    literally in your program.  For example, "that $foo" is usually
    optimized into "that " . $foo, and the warning will refer to the
    concatenation (.) operator, even though there is no . in
    your program.

-------- CVE Security Recommendations ------------------------------------------                                                                                        --------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION

-------- Performance Metrics ---------------------------------------------------                                                                                        --------------------
[--] Up for: 3d 0h 50m 58s (27M q [106.285 qps], 5M conn, TX: 29G, RX: 3G)
[--] Reads / Writes: 92% / 8%
[--] Binary logging is disabled
[--] Physical Memory     : 31.3G
[--] Max MySQL memory    : 86.1G
[--] Other process memory: 0B
[--] Total buffers: 15.3G global + 480.6M per thread (151 max threads)
[--] Performance_schema Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 86.6G (276.42% of installed RAM)
[!!] Maximum possible memory usage: 86.1G (274.92% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (27/27M)
[!!] Highest connection usage: 100% (152/151)
[OK] Aborted connections: 0.21% (12101/5634183)
[!!] Name resolution is active: a reverse name resolution is made for each new c                                                                                        onnection which can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocesso                                                                                        r machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1M sorts)
[!!] Joins performed without indexes: 30037
[OK] Temporary tables created on disk: 2% (28K on disk / 997K total)
[OK] Thread cache hit rate: 99% (253 created / 5M connections)
[OK] Table cache hit rate: 99% (26M hits / 27M requests)
[!!] table_definition_cache (400) is less than number of tables (3041)
[OK] Open file limit used: 0% (121/16K)
[OK] Table locks acquired immediately: 100% (189K immediate / 189K locks)

-------- Performance schema ----------------------------------------------------                                                                                        --------------------
[!!] Performance_schema should be activated.
[--] Sys schema is installed.

-------- ThreadPool Metrics ----------------------------------------------------                                                                                        --------------------
[--] ThreadPool stat is disabled.
Use of uninitialized value $opt{"myisamstat"} in numeric eq (==) at
        ./mysqltuner.pl line 3860 (#1)

-------- InnoDB Metrics --------------------------------------------------------                                                                                        --------------------
[--] InnoDB is enabled.
[OK] InnoDB File per table is activated
[OK] InnoDb Buffer Pool size ( 128.0M ) under limit for 64 bits architecture: (1                                                                                        7179869184.0G )
[!!] InnoDB buffer pool / data size: 128.0M / 33.4G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75%): 96.0M * 1 / 128                                                                                        .0M should be equal to 25%
[--] Number of InnoDB Buffer Pool Chunk: 1 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb                                                                                        _buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.87% (157285991602 hits / 157486582048 tot                                                                                        al)
[!!] InnoDB Write Log efficiency: 49.38% (563905 hits / 1141959 total)
[OK] InnoDB log waits: 0.00% (0 waits / 578054 writes)

-------- Aria Metrics ----------------------------------------------------------                                                                                        --------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/336.0K
[OK] Aria pagecache hit rate: 99.9% (27M cached / 27K reads)

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

-------- XtraDB Metrics --------------------------------------------------------                                                                                        --------------------
[--] XtraDB is disabled.

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

-------- Replication Metrics ---------------------------------------------------                                                                                        --------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: MIXED
[--] XA support enabled: ON
[--] Semi synchronous replication Master: OFF
[--] Semi synchronous replication Slave: OFF
[--] This is a standalone server

mysqltuner recommendation

-------- Recommendations -------------------------------------------------------                                                                                        --------------------
General recommendations:
    Reduce your overall MySQL memory footprint for system stability
    Dedicate this server to your database for highest performance.
    Reduce or eliminate persistent connections to reduce connection usage
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=ON
    We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
             See https://dev.mysql.com/doc/internals/en/join-buffer-size.html 
             (specially the conclusions at the bottom of the page).
    Performance schema should be activated for better diagnostics
    Be careful, increasing innodb_log_file_size / innodb_log_files_in_group means higher crash recovery mean time
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    max_connections (> 151)
    wait_timeout ( 256.0K, or always use indexes with JOINs)
    table_definition_cache (400) > 3041 or -1 (autosizing if supported)
    performance_schema=ON
    innodb_buffer_pool_size (>= 33.4G) if possible.
    innodb_log_file_size should be (=32M) if possible, so InnoDB total log file                                                                                         size equals 25% of buffer pool size.

my cnf please give suggestion please what need to add

[client-server]
# Port or socket location where to connect
# port = 3306
socket = /run/mysqld/mysqld.sock

# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

I would greatly appreciate any insights, explanations, or resources related to using mysqltuner for MariaDB performance tuning. Thank you in advance for your valuable assistance!

I have installed MySQL 8.0.29 on a dedicated server with 16 vCores and 60 GB RAM but the memory usage is still growing. I restarted the mysql service 2 months ago and it takes about 91% RAM now:

total        used        free      shared  buff/cache   available
Mem:           57Gi        51Gi       511Mi       2.0Mi       5.5Gi       5.3Gi
Swap:            0B          0B          0B

my configuration is:

[mysqld]
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
datadir		= /var/lib/mysql
log-error	= /var/log/mysql/error.log

bind-address = 10.10.0.2
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_expire_logs_seconds = 259200
binlog_do_db = dbname

skip_name_resolve = 1
innodb_dedicated_server = 1
max_allowed_packet = 536870912
max_connections = 1000
join_buffer_size = 1048576

mysqltuner.pl outputs:

>>  MySQLTuner 2.0.11
	 * Jean-Marie Renouard 
	 * Major Hayden 
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.pl/ 
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 8.0.29-21
[OK] Operating on 64-bit architecture
 
-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/log/mysql/error.log exists
[--] Log file: /var/log/mysql/error.log (300K)
[OK] Log file /var/log/mysql/error.log is not empty
[OK] Log file /var/log/mysql/error.log is smaller than 32 Mb
[OK] Log file /var/log/mysql/error.log is readable.
[!!] /var/log/mysql/error.log contains 2313 warning(s).
[!!] /var/log/mysql/error.log contains 1 error(s).
[--] 143 start(s) detected in /var/log/mysql/error.log
[--] 1) 2022-12-17T05:00:49.782964Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.29-21'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Percona Server (GPL), Release '21', Revision 'c59f87d2854'.
[--] 2) 2022-12-17T05:00:49.782926Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
[--] 3) 2022-12-07T05:25:48.429925Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.29-21'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Percona Server (GPL), Release '21', Revision 'c59f87d2854'.
[--] 4) 2022-12-07T05:25:48.429905Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
[--] 5) 2022-11-16T05:45:33.898403Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
[--] 6) 2022-11-16T05:45:33.896585Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.29-21'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Percona Server (GPL), Release '21', Revision 'c59f87d2854'.
[--] 7) 2022-10-19T12:55:01.812148Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.29-21'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Percona Server (GPL), Release '21', Revision 'c59f87d2854'.
[--] 8) 2022-10-19T12:55:01.811935Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
[--] 9) 2022-10-19T12:43:10.091727Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.29-21'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  Percona Server (GPL), Release '21', Revision 'c59f87d2854'.
[--] 10) 2022-10-19T12:43:10.091690Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
[--] 16 shutdown(s) detected in /var/log/mysql/error.log
[--] 1) 2022-12-17T04:53:22.580264Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.29-21)  Percona Server (GPL), Release '21', Revision 'c59f87d2854'.
[--] 2) 2022-12-07T05:24:48.272111Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.29-21)  Percona Server (GPL), Release '21', Revision 'c59f87d2854'.
[--] 3) 2022-11-16T05:43:23.710144Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.29-21)  Percona Server (GPL), Release '21', Revision 'c59f87d2854'.
[--] 4) 2022-10-15T23:45:59.932949Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.29-21)  Percona Server (GPL), Release '21', Revision 'c59f87d2854'.
[--] 5) 2022-08-29T18:05:15.236277Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.29-21)  Percona Server (GPL), Release '21', Revision 'c59f87d2854'.
[--] 6) 2022-08-29T16:07:59.235204Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.28-20)  Percona Server (GPL), Release '20', Revision 'fd4b5a776a6'.
[--] 7) 2022-08-05T12:31:06.292042Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.28-20)  Percona Server (GPL), Release '20', Revision 'fd4b5a776a6'.
[--] 8) 2022-07-29T13:58:25.716035Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.28-20)  Percona Server (GPL), Release '20', Revision 'fd4b5a776a6'.
[--] 9) 2022-07-28T19:27:27.158294Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.28-20)  Percona Server (GPL), Release '20', Revision 'fd4b5a776a6'.
[--] 10) 2022-07-27T21:26:21.431830Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.28-20)  Percona Server (GPL), Release '20', Revision 'fd4b5a776a6'.
 
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA 
[--] Data in InnoDB tables: 15.9G (Tables: 604)
[!!] Total fragmented tables: 1
 
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
 
-------- Views Metrics -----------------------------------------------------------------------------
 
-------- Triggers Metrics --------------------------------------------------------------------------
 
-------- Routines Metrics --------------------------------------------------------------------------
 
-------- Security Recommendations ------------------------------------------------------------------
[--] Skipped due to unsupported feature for MySQL 8
 
-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
 
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 53d 5h 22m 49s (7B q [1K qps], 94M conn, TX: 57240G, RX: 6285G)
[--] Reads / Writes: 98% / 2%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory     : 57.5G
[--] Max MySQL memory    : 547.0G
[--] Other process memory: 0B
[--] Total buffers: 44.0G global + 514.6M per thread (1000 max threads)
[--] Performance_schema Max memory usage: 400M
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 192.7G (335.17% of installed RAM)
[!!] Maximum possible memory usage: 547.0G (951.48% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/7B)
[OK] Highest usage of available connections: 29% (295/1000)
[OK] Aborted connections: 0.00% (1/94397108)
[--] Query cache has been removed since MySQL 8.0
[OK] Sorts requiring temporary tables: 0% (11M temp sorts / 2B sorts)
[!!] Joins performed without indexes: 723947
[OK] Temporary tables created on disk: 0% (285K on disk / 412M total)
[OK] Thread cache hit rate: 99% (803K created / 94M connections)
[OK] Table cache hit rate: 99% (14B hits / 14B requests)
[OK] table_definition_cache (2000) is greater than number of tables (945)
[OK] Open file limit used: 0% (6/10K)
[OK] Table locks acquired immediately: 100% (131 immediate / 131 locks)
[OK] Binlog cache memory access: 98.13% (41213195 Memory / 41998131 Total)
 
-------- Performance schema ------------------------------------------------------------------------
[--] Performance_schema is activated.
[--] Memory used by Performance_schema: 400.6M
[--] Sys schema is installed.
 
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
 
-------- MyISAM Metrics ----------------------------------------------------------------------------
[--] MyISAM Metrics are disabled since MySQL 8.0.
 
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 44.0G / 15.9G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75%): 1.0G * 33 / 44.0G should be equal to 25%
[!!] InnoDB buffer pool instances: 8
[--] Number of InnoDB Buffer Pool Chunk: 352 for 8 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 100.00% (8303978165000 hits / 8303978910176 total)
[OK] InnoDB Write log efficiency: 96.33% (8325309995 hits / 8642424121 total)
[OK] InnoDB log waits: 0.00% (5 waits / 317114126 writes)
 
-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine not available.
 
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
 
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
 
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
 
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server
 
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Check warning line(s) in /var/log/mysql/error.log file
    Check error line(s) in /var/log/mysql/error.log file
    Run ALTER TABLE ... FORCE or OPTIMIZE TABLE to defragment tables for better performance
      ALTER TABLE magento.mageplaza_smtp_log FORCE; -- can free 2024 MiB
    Total freed space after defragmentation : 2024 MiB
    Reduce your overall MySQL memory footprint for system stability
    Dedicate this server to your database for highest performance.
    We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
             See https://dev.mysql.com/doc/internals/en/join-buffer-size.html 
             (specially the conclusions at the bottom of the page).
    Buffer Key MyISAM set to 0, no MyISAM table detected
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU 
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    join_buffer_size (> 1.0M, or always use indexes with JOINs)
    key_buffer_size=0
    innodb_log_file_size should be (=341M) if possible, so InnoDB total log files size equals 25% of buffer pool size.
    innodb_buffer_pool_instances(=44)

mysql status:

mysql> SHOW STATUS WHERE variable_name = 'Max_used_connections';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 295   |
+----------------------+-------+
1 row in set (0.01 sec)

mysql> SHOW STATUS WHERE variable_name like 'Threads_%';
+-------------------+--------+
| Variable_name     | Value  |
+-------------------+--------+
| Threads_cached    | 10     |
| Threads_connected | 47     |
| Threads_created   | 804042 |
| Threads_running   | 8      |
+-------------------+--------+
4 rows in set (0.00 sec)

For variables and full output of show status command please look at https://gist.github.com/macdar/b044e725438ff574ff3a445c543be39d Is something wrong with my configuration?

I am very new to database design, I spun up an Ubuntu server version 20.04.3 LTS and installed mysql version mysql Ver 8.0.31-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu)). I designed my database like so **addresses**

use testdb;

DROP TABLE IF EXISTS addresses;

CREATE TABLE testdb.addresses (
  id INT NOT NULL AUTO_INCREMENT,
  hash VARCHAR(40) NOT NULL,
  street VARCHAR(50) NOT NULL,
  city VARCHAR(25) NOT NULL,
  state VARCHAR(20) NOT NULL,
  zipcode VARCHAR(5) NOT NULL,
  country VARCHAR(40) NOT NULL,
  coordinates POINT NOT NULL,
  PRIMARY KEY (id),
  UNIQUE INDEX id_UNIQUE (id ASC) VISIBLE,
  UNIQUE INDEX hash_UNIQUE (hash ASC) VISIBLE,
  SPATIAL KEY coordinates (coordinates)
  );

**sales**

use testdb;

DROP TABLE IF EXISTS sales;

CREATE TABLE testdb.sales (
  id INT NOT NULL AUTO_INCREMENT,
  saleId BINARY(16) NOT NULL,
  address_id INT NOT NULL,
  title VARCHAR(200) NOT NULL,
  description LONGTEXT NULL DEFAULT NULL,
  startDate DATE NOT NULL,
  endDate DATE NULL,
  link VARCHAR(2048) NULL DEFAULT NULL,
  source VARCHAR(200) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE INDEX saleId_idx (saleId ASC) VISIBLE,
  INDEX address_id_idx (address_id ASC) VISIBLE,
  UNIQUE INDEX sale_date_address_idx (address_id ASC, startDate ASC) VISIBLE,
  CONSTRAINT address_id
    FOREIGN KEY (address_id)
    REFERENCES testdb.addresses (id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

**images**

use sales;

DROP TABLE IF EXISTS images;

CREATE TABLE testdb.images (
  id INT NOT NULL AUTO_INCREMENT,
  url VARCHAR(2048) NOT NULL,
  sale_id INT NOT NULL,
  PRIMARY KEY (id),
  INDEX sale_id_idx (sale_id ASC) VISIBLE,
  CONSTRAINT sale_id
    FOREIGN KEY (sale_id)
    REFERENCES testdb.sales (id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

This is my first database design ever. I think my indicies are fine but I am not certain; when I run a query like the following

SELECT
	sales.id as id, BIN_TO_UUID(sales.saleId) as saleId, sales.title, sales.description, sales.startDate, sales.endDate, sales.link, sales.source,
	addresses.id as address_id, addresses.hash, addresses.street, addresses.city, addresses.state, addresses.zipcode, addresses.country, ST_X(addresses.coordinates) as latitude, ST_Y(addresses.coordinates) as longitude,
    GROUP_CONCAT(images.url) as images
from 
	sales
INNER JOIN 
	addresses ON sales.address_id = addresses.id
INNER JOIN
	images ON images.sale_id = sales.id
WHERE 
	sales.saleId = UUID_TO_BIN('8b56862c-47dc-49dd-9c82-b42c0e92d623')
GROUP BY
	id
LIMIT 
	1;

here is the execution plan it executes fairly fast on around 5k records; however, when I run mysqltuner on my database I get the following:

...
[!!] Joins performed without indexes: 18
...
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    join_buffer_size (> 256.0K, or always use indexes with JOINs)

Why does it say I am performing joins without indices when I am only using indices to perform my joins? I am lost, I have been researching without hope. For reference here are my **mysqld** configurations

.cnf
[mysqld]
user            = mysql
innodb_force_recovery = 0
bind-address            = 0.0.0.0
myisam-recover-options  = BACKUP
log_error = /var/log/mysql/error.log
binlog_expire_logs_seconds      = 86400

# mysqltuner.pl
skip-name-resolve=1
key_buffer_size=0
innodb_log_file_size=16M

# Suggested on https://dba.stackexchange.com/questions/218250/help-with-mysqls-maximum-memory-usage-which-is-dang >
join_buffer_size=256K
thread_cache_size=40
#query_cache_limit=0
innodb_lru_scan_depth=100
key_cache_age_threshold=7200
key_cache_division_limit=50
key_cache_block_size=16K
open_files_limit=30000
table_open_cache=10000
table_definition_cache=2000

**EDIT: Running EXPLAIN on the query returns the following.** **NOTE: I am using MySQLWorkbench to run my queries which is probably why it comes in a table format, not sure if I can output it in a different way**

I'm trying to understand what is not working in my configuration. When I run the

[root@server ~]# ./mysqltuner.pl
 >>  MySQLTuner 1.9.8
         * Jean-Marie Renouard 
         * Major Hayden 
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.pl/ 
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[!!] Your MySQL version 10.2.43-MariaDB-log is EOL software!  Upgrade soon!
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/log/mysqld.log exists
[--] Log file: /var/log/mysqld.log(1M)
[OK] Log file /var/log/mysqld.log is not empty
[OK] Log file /var/log/mysqld.log is smaller than 32 Mb
[OK] Log file /var/log/mysqld.log is readable.
[!!] /var/log/mysqld.log contains 7295 warning(s).
[!!] /var/log/mysqld.log contains 2416 error(s).
[--] 68 start(s) detected in /var/log/mysqld.log
[--] 1) 2022-04-15 15:28:49 139969276774592 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2022-04-15 15:01:58 139671548569792 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2022-04-15 15:00:37 139816143530176 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2022-04-11 15:13:19 140028901791936 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2022-03-29  2:44:09 140256877758656 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2022-03-22  2:42:21 140486199806144 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2022-02-14  2:42:44 139938060060864 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 2022-02-14  2:42:41 139960106510528 [Note] /usr/sbin/mysqld: ready for connections.
[--] 9) 2022-02-09  2:45:31 140350442416320 [Note] /usr/sbin/mysqld: ready for connections.
[--] 10) 2022-02-09  2:45:28 139988696365248 [Note] /usr/sbin/mysqld: ready for connections.
[--] 49 shutdown(s) detected in /var/log/mysqld.log
[--] 1) 2022-04-15 15:28:49 139670594152192 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2022-04-15 15:01:57 139815146489600 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2022-04-15 14:58:06 140027817096960 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2022-04-11 15:12:31 140255785293568 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 2022-03-29  2:44:09 140485092288256 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 2022-03-22  2:42:20 139937820497664 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 2022-02-14  2:42:44 139959094945536 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 8) 2022-02-14  2:42:40 140349468514048 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 9) 2022-02-09  2:45:31 139987684747008 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 10) 2022-02-09  2:45:27 140367091803904 [Note] /usr/sbin/mysqld: Shutdown complete

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in InnoDB tables: 3.9G (Tables: 718)
[OK] Total fragmented tables: 0

-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.

-------- Views Metrics -----------------------------------------------------------------------------

-------- Triggers Metrics --------------------------------------------------------------------------

-------- Routines Metrics --------------------------------------------------------------------------

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!

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

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 30m 9s (233K q [128.918 qps], 1K conn, TX: 1G, RX: 56M)
[--] Reads / Writes: 93% / 7%
[--] Binary logging is disabled
[--] Physical Memory     : 7.6G
[--] Max MySQL memory    : 45.1G
[--] Other process memory: 0B
[--] Total buffers: 416.0M global + 302.3M per thread (151 max threads)
[--] P_S Max memory usage: 93M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 3.4G (45.17% of installed RAM)
[!!] Maximum possible memory usage: 45.1G (590.18% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (33/233K)
[OK] Highest usage of available connections: 6% (10/151)
[OK] Aborted connections: 0.14%  (2/1476)
[!!] CPanel and Flex system skip-name-resolve should be on
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 40K sorts)
[!!] Joins performed without indexes: 559
[!!] Temporary tables created on disk: 87% (28K on disk / 32K total)
[OK] Thread cache hit rate: 99% (10 created / 1K connections)
[OK] Table cache hit rate: 99% (865 hits / 872 requests)
[OK] table_definition_cache(2097152) is upper than number of tables(984)
[OK] Open file limit used: 0% (38/40K)
[OK] Table locks acquired immediately: 100% (134 immediate / 134 locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance_schema is activated.
[--] Memory used by P_S: 93.6M
[--] Sys schema is installed.

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

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.3% (23.4M used / 128.0M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/137.0K
[OK] Read Key buffer hit rate: 96.6% (716 cached / 24 reads)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 128.0M/3.9G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 14.0M * 2/128.0M should be equal to 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 1 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.63% (202127202 hits/ 202873879 total)
[!!] InnoDB Write Log efficiency: 83.74% (66789 hits/ 79762 total)
[OK] InnoDB log waits: 0.00% (0 waits / 12973 writes)

-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/0B
[OK] Aria pagecache hit rate: 98.7% (1M cached / 24K reads)

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

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

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

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: MIXED
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    You are using n unsupported version for production environments
    Upgrade as soon as possible to a supported version !
    Check warning line(s) in /var/log/mysqld.log file
    Check error line(s) in /var/log/mysqld.log file
    MySQL was started within the last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Dedicate this server to your database for highest performance.
    name resolution is enabled due to cPanel doesn't support this disabled.
    We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
             See https://dev.mysql.com/doc/internals/en/join-buffer-size.html 
             (specially the conclusions at the bottom of the page).
    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:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    skip-name-resolve=0
    join_buffer_size (> 40.0M, or always use indexes with JOINs)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    key_buffer_size (~ 24M)
    innodb_buffer_pool_size (>= 3.9G) if possible.

I am really concerned by the "MySQL's maximum memory usage is dangerously high" message, because that means that the server can crash unexpectedly if MySQL tries to get more memory than RAM. Here is my my.cnf :

[root@server ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html 

[mysqld]
performance-schema=0
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
join_buffer_size = 40M
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
# pid-file=/var/run/mysqld/mysqld.pid
max_allowed_packet=16M
open_files_limit=40000
innodb_file_per_table=1

query_cache_size=0
query_cache_type=0
query_cache_limit=1M
tmp_table_size=16M
max_heap_table_size=16M
performance_schema=ON
innodb_log_file_size=14M
table_definition_cache=-1
slow_query_log = 1
slow-query_log_file = /var/log/mysql-slow.log
long_query_time = 2

I tried to change the join_buffer_size = 40M sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 2M but it seems to get worse. My initial my.cnf was :

[root@server ~]# cat /etc/my.cnf.backup
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html 

[mysqld]
performance-schema=0
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
# pid-file=/var/run/mysqld/mysqld.pid
innodb_buffer_pool_size=122683392
max_allowed_packet=268435456
open_files_limit=40000
innodb_file_per_table=1

query_cache_size=0
query_cache_type=0
query_cache_limit=1M
tmp_table_size=16M
max_heap_table_size=16M
performance_schema=ON
innodb_buffer_pool_size=260M
innodb_log_file_size=14M
table_definition_cache=-1
slow_query_log = 1
slow-query_log_file = /var/log/mysql-slow.log
long_query_time = 2

Any suggestion, or blatant error or misconfiguration that anyone could see ?

We are running a Wordpress site and are having major problems with extremely slow response time for pages that have to miss the Nginx cache. I'm looking at our database performance as a culprit, and have a MySQLTuner log. What should I be looking at to change/tweak here? >> MySQLTuner 1.7.9 - 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 [OK] Currently running supported MySQL version 10.1.32-MariaDB-1~jessie [OK] Operating on 64-bit architecture -------- Log file Recommendations ------------------------------------------------------------------ [--] Log file: /var/lib/mysql/shoegaze.err(0B) [!!] Log file /var/lib/mysql/shoegaze.err doesn't exist [!!] Log file /var/lib/mysql/shoegaze.err isn't readable. -------- Storage Engine Statistics ----------------------------------------------------------------- [--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE [--] Data in InnoDB tables: 477M (Tables: 229) [--] Data in MyISAM tables: 564M (Tables: 74) [OK] Total fragmented tables: 0 -------- Security Recommendations ------------------------------------------------------------------ [OK] There are no anonymous accounts for any database users [OK] All database users have passwords assigned [!!] There is no basic password file list! -------- CVE Security Recommendations -------------------------------------------------------------- [--] Skipped due to --cvefile option undefined -------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 2d 17h 4m 19s (8M q [38.070 qps], 143K conn, TX: 170G, RX: 8G) [--] Reads / Writes: 92% / 8% [--] Binary logging is enabled (GTID MODE: OFF) [--] Physical Memory : 1.0G [--] Max MySQL memory : 1.3G [--] Other process memory: 309.1M [--] Total buffers: 624.0M global + 7.5M per thread (100 max threads) [--] P_S Max memory usage: 0B [--] Galera GCache Max memory usage: 0B [!!] Maximum reached memory usage: 895.2M (87.42% of installed RAM) [!!] Maximum possible memory usage: 1.3G (134.49% of installed RAM) [!!] Overall possible memory usage with other process exceeded memory [OK] Slow queries: 0% (0/8M) [OK] Highest usage of available connections: 36% (36/100) [OK] Aborted connections: 0.01% (15/143505) [!!] 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] Query cache efficiency: 37.9% (4M cached / 11M selects) [!!] Query cache prunes per day: 20139 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 96K sorts) [OK] No joins without indexes [!!] Temporary tables created on disk: 31% (131K on disk / 421K total) [OK] Thread cache hit rate: 99% (36 created / 143K connections) [OK] Table cache hit rate: 57% (400 open / 691 opened) [OK] Open file limit used: 1% (219/16K) [OK] Table locks acquired immediately: 97% (3M immediate / 3M locks) [OK] Binlog cache memory access: 99.99% (15789 Memory / 15790 Total) -------- 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: 4 thread(s). [--] Using default value is good enough for your version (10.1.32-MariaDB-1~jessie) -------- MyISAM Metrics ---------------------------------------------------------------------------- [OK] Key buffer used: 100.0% (134M used / 134M cache) [OK] Key buffer size / total MyISAM indexes: 128.0M/226.3M [OK] Read Key buffer hit rate: 99.8% (53M cached / 134K reads) [OK] Write Key buffer hit rate: 99.3% (1M cached / 1M writes) -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [--] InnoDB Thread Concurrency: 0 [OK] InnoDB File per table is activated [!!] InnoDB buffer pool / data size: 256.0M/477.1M [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (37.5 %): 48.0M * 2/256.0M should be equal 25% [!!] InnoDB buffer pool 64M) tmp_table_size (> 32M) max_heap_table_size (> 32M) performance_schema = ON enable PFS innodb_buffer_pool_size (>= 477M) if possible. innodb_log_file_size should be (=32M) if possible, so InnoDB total log files size equals to 25% of buffer pool size. innodb_buffer_pool_instances (=1)

Mine is Digitalocean with 1vCPU, 1GB -25GB SSD Ubuntu server with nginx, MariaDB, phpmyadmin and running a wordpress blog. Mysql is taking too much cpu and on running running MySqlTuner, I got these suggestions:

``
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_size (> 16M)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    performance_schema = ON enable PFS
    innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

`` the ouput of ulimit -a:

`
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 3788
max locked memory       (kbytes, -l) 65536
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 3788
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

` The full MySqlTuner report is as follows:

`
 >>  MySQLTuner 1.8.1 - Major Hayden 
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.pl/ 
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials from Debian maintenance account.
[!!] failed to execute: SHOW REPLICA STATUS\G
[!!] FAIL Execute SQL / return code: 256
[OK] Currently running supported MySQL version 10.3.31-MariaDB-0ubuntu0.20.04.1
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[!!] Log file /var/log/mysql/error.log doesn't exist

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in MyISAM tables: 267.8M (Tables: 54)
[--] Data in InnoDB tables: 416.0K (Tables: 20)
[OK] Total fragmented tables: 0

-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!

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

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 1d 12h 39m 35s (5M q [39.556 qps], 41K conn, TX: 196G, RX: 657M)
[--] Reads / Writes: 96% / 4%
[--] Binary logging is disabled
[--] Physical Memory     : 981.3M
[--] Max MySQL memory    : 3.2G
[--] Other process memory: 0B
[--] Total buffers: 432.0M global + 18.9M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 564.4M (57.51% of installed RAM)
[!!] Maximum possible memory usage: 3.2G (335.01% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (8/5M)
[OK] Highest usage of available connections: 4% (7/151)
[OK] Aborted connections: 0.00%  (1/41165)
[!!] 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] Query cache efficiency: 33.8% (2M cached / 7M selects)
[!!] Query cache prunes per day: 13820
[OK] Sorts requiring temporary tables: 1% (9K temp sorts / 532K sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 77% (65K on disk / 84K total)
[OK] Thread cache hit rate: 99% (7 created / 41K connections)
[OK] Table cache hit rate: 99% (2M hits / 2M requests)
[OK] table_definition_cache(400) is upper than number of tables(235)
[OK] Open file limit used: 0% (189/32K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M 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: 1 thread(s).
[--] Using default value is good enough for your version (10.3.31-MariaDB-0ubuntu0.20.04.1)

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 28.5% (38M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/46.4M
[OK] Read Key buffer hit rate: 100.0% (230M cached / 12K reads)
[OK] Write Key buffer hit rate: 99.5% (744K cached / 740K writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 128.0M/416.0K
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal to 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 1 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[!!] InnoDB Read buffer efficiency: 83.72% (1661 hits/ 1984 total)
[!!] InnoDB Write Log efficiency: 41.67% (5 hits/ 12 total)
[OK] InnoDB log waits: 0.00% (0 waits / 7 writes)

-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/0B
[!!] Aria pagecache hit rate: 87.4% (515K cached / 65K reads)

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

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

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

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: MIXED
[--] XA support enabled: ON
[--] Semi synchronous replication Master: OFF
[--] Semi synchronous replication Slave: OFF
[--] This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    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
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Performance schema should be activated for better diagnostics
    Consider installing Sys schema from https://github.com/mysql/mysql-sys  for MySQL
    Consider installing Sys schema from https://github.com/FromDual/mariadb-sys  for MariaDB
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU 
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_size (> 16M)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    performance_schema = ON enable PFS
    innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

` htop What do I need to adjust in order to increase the performance of my MySQL instance and reduce cpu usgae?

we have noticed that our database server is getting so high CPU (more than 90 % used by mysqld process), the output of mysqltunner is shown below, can you help me please, i'm okay about the innodb_buffer_pool_instances(=7), but is there some other suggestions ?

I'm currently supporting, A Moodle 3.10 instance, running MySQL 8.0.20. This is running through an Azure Provider as a Standard DS11(2 vcpus, 14 GiB memory). Running as a LAMP stack server. Currently, there is a bottleneck on the Server with the load on the database and low usage of memory, during peak windows. Need to find optimal ways to tune the database to support more concurrent users (it slows down at ~100 users, would like to get it to support 250 users if possible. Attached a MySQL Tuner file below as well.

[--] Skipped version check for MySQLTuner script
Please enter your MySQL administrative password: [OK] Currently running supported MySQL version 8.0.20
[OK] Operating on 64-bit architecture
 
-------- Log file Recommendations ------------------------------------------------------------------
[!!] Log file /opt/bitnami/mysql/data/mysqld.log doesn't exist
 
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA 
[--] Data in InnoDB tables: 2.9G (Tables: 464)
[OK] Total fragmented tables: 0
 
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
 
-------- Security Recommendations ------------------------------------------------------------------
[--] Skipped due to unsupported feature for MySQL 8
 
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
 
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 6d 11h 7m 48s (99M q [178.845 qps], 10M conn, TX: 58G, RX: 11G)
[--] Reads / Writes: 85% / 15%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory     : 13.7G
[--] Max MySQL memory    : 10.9G
[--] Other process memory: 0B
[--] Total buffers: 6.0G global + 33.3M per thread (151 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 7.2G (52.85% of installed RAM)
[OK] Maximum possible memory usage: 10.9G (79.89% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/99M)
[OK] Highest usage of available connections: 24% (37/151)
[OK] Aborted connections: 0.00%  (7/10825746)
[--] Query cache have been removed in MySQL 8
[OK] Sorts requiring temporary tables: 0% (52 temp sorts / 2M sorts)
[!!] Joins performed without indexes: 81493
[OK] Temporary tables created on disk: 0% (1K on disk / 2M total)
[OK] Thread cache hit rate: 99% (37 created / 10M connections)
[OK] Table cache hit rate: 27% (4K open / 14K opened)
[OK] table_definition_cache(2000) is upper than number of tables(774)
[OK] Open file limit used: 0% (2/65K)
[OK] Table locks acquired immediately: 100% (141K immediate / 141K locks)
[OK] Binlog cache memory access: 99.95% (8182744 Memory / 8186895 Total)
 
-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 72B
[--] Sys schema is installed.
 
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
 
-------- MyISAM Metrics ----------------------------------------------------------------------------
[--] MyISAM Metrics are disabled on last MySQL versions.
 
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 6.0G/2.9G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 768.0M * 2/6.0G should be equal to 25%
[!!] InnoDB buffer pool instances: 8
[--] Number of InnoDB Buffer Pool Chunk : 48 for 8 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 100.00% (5172515853 hits/ 5172589612 total)
[!!] InnoDB Write Log efficiency: 78.71% (75749720 hits/ 96240545 total)
[OK] InnoDB log waits: 0.00% (0 waits / 20490825 writes)
 
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.
 
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
 
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
 
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
 
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
             See https://dev.mysql.com/doc/internals/en/join-buffer-size.html 
             (specially the conclusions at the bottom of the page).
Variables to adjust:
    join_buffer_size (> 256.0K, or always use indexes with JOINs)
    innodb_buffer_pool_instances(=6)

I recently switched galera synchronization method from rsync to mariabackup which greatly reduced CPU usage on our DB servers and also increased the innodb_log_file_size to 2G from about 600M or so. Since then (might be a coincidence) I started getting a lot of aborted connection errors and bad log write efficiency. I initially thought it had something to do with wait_timeout (which we set to 5 since our application has to be very fast ) and raised it to 60 as well as max_allowed_packet to 2G. But even after that the errors still remained. And AlertManager raises host crash errors almost hourly. [--] Skipped version check for MySQLTuner script [OK] Logged in using credentials passed on the command line [OK] Currently running supported MySQL version 10.4.14-MariaDB-1:10.4.14+maria~focal-log [OK] Operating on 64-bit architecture -------- Log file Recommendations ------------------------------------------------------------------ [!!] Log file doesn't exist -------- Storage Engine Statistics ----------------------------------------------------------------- [--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE [--] Data in InnoDB tables: 16.3G (Tables: 150) [OK] Total fragmented tables: 0 -------- Analysis Performance Metrics -------------------------------------------------------------- [--] innodb_stats_on_metadata: OFF [OK] No stat updates during querying INFORMATION_SCHEMA. -------- Security Recommendations ------------------------------------------------------------------ [OK] There are no anonymous accounts for any database users [!!] User 'mariadb.sys@localhost' has no password set. [!!] User 'developer@%' does not specify hostname restrictions. [--] There are 620 basic passwords in the list. -------- CVE Security Recommendations -------------------------------------------------------------- [OK] NO SECURITY CVE FOUND FOR YOUR VERSION -------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 4h 56m 41s (1M q [64.284 qps], 33K conn, TX: 758M, RX: 166M) [--] Reads / Writes: 40% / 60% [--] Binary logging is enabled (GTID MODE: OFF) [--] Physical Memory : 54.9G [--] Max MySQL memory : 46.1G [--] Other process memory: 0B [--] Total buffers: 19.2G global + 26.9M per thread (1000 max threads) [--] P_S Max memory usage: 623M [--] Galera GCache Max memory usage: 128M [OK] Maximum reached memory usage: 20.7G (37.77% of installed RAM) [OK] Maximum possible memory usage: 46.1G (83.90% of installed RAM) [OK] Overall possible memory usage with other process is compatible with memory available [OK] Slow queries: 4% (55K/1M) [OK] Highest usage of available connections: 3% (36/1000) [!!] Aborted connections: 10.61% (3560/33563) [OK] Query cache is disabled by default due to mutex contention on multiprocessor machines. [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 3 sorts) [OK] No joins without indexes [OK] Temporary tables created on disk: 12% (13 on disk / 107 total) [OK] Thread cache hit rate: 99% (69 created / 33K connections) [OK] Table cache hit rate: 97% (290 open / 296 opened) [OK] table_definition_cache(400) is upper than number of tables(313) [OK] Open file limit used: 0% (58/16K) [OK] Table locks acquired immediately: 100% (686 immediate / 686 locks) [OK] Binlog cache memory access: 100.00% (586009 Memory / 586009 Total) -------- Performance schema ------------------------------------------------------------------------ [--] Memory used by P_S: 623.7M [--] Sys schema isn't installed. -------- ThreadPool Metrics ------------------------------------------------------------------------ [--] ThreadPool stat is enabled. [--] Thread Pool Size: 14 thread(s). [--] Using default value is good enough for your version (10.4.14-MariaDB-1:10.4.14+maria~focal-log) -------- MyISAM Metrics ---------------------------------------------------------------------------- [!!] Key buffer used: 18.3% (6M used / 33M cache) [!!] Cannot calculate MyISAM index size - re-run script as root user -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [--] InnoDB Thread Concurrency: 0 [OK] InnoDB File per table is activated [OK] InnoDB buffer pool / data size: 19.0G/16.3G [OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 2.0G * 2/19.0G should be equal to 25% [OK] InnoDB buffer pool instances: 19 [--] Number of InnoDB Buffer Pool Chunk : 152 for 19 Buffer Pool Instance(s) [OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances [OK] InnoDB Read buffer efficiency: 100.00% (12044117752 hits/ 12044442245 total) [!!] InnoDB Write Log efficiency: 87.27% (874238 hits/ 1001803 total) [OK] InnoDB log waits: 0.00% (0 waits / 127565 writes) -------- AriaDB Metrics ---------------------------------------------------------------------------- [--] AriaDB is enabled. [OK] Aria pagecache size / total Aria indexes: 128.0M/312.0K [OK] Aria pagecache hit rate: 99.5% (2K cached / 14 reads) -------- TokuDB Metrics ---------------------------------------------------------------------------- [--] TokuDB is disabled. -------- XtraDB Metrics ---------------------------------------------------------------------------- [--] XtraDB is disabled. -------- Galera Metrics ---------------------------------------------------------------------------- [--] Galera is enabled. [--] GCache is using 0B [--] CPU core detected : 14 [--] wsrep_slave_threads: 32 [OK] wsrep_slave_threads is equal to 2, 3 or 4 times number of CPU(s) [OK] gcs.fc_limit should be equal to 5 * wsrep_slave_threads [--] wsrep parallel slave can cause frequent inconsistency crash. [OK] gcs.fc_limit is equal to 5 * wsrep_slave_threads [OK] gcs.fc_factor is equal to 0.8 [OK] Flow control fraction seems to be OK (wsrep_flow_control_paused1 and command"Sleep"; To try debugging slow queries but it's always an empty set yet the tuner reports over 5k slow queries (the slow query time is set to 5s).

After running mysqltuner.pl, I get an Innodb write log efficiency way above 100 %. Why? What does it mean? There is no specific reccomendation from mysqltuner. DB is MariaDB 10.1.26 on Debian 9.x with 1 GB RAM. -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [--] InnoDB Thread Concurrency: 0 [OK] InnoDB File per table is activated [OK] InnoDB buffer pool / data size: 64.0M/9.0M [OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 8.0M * 2/64.0M should be equal 25% [OK] InnoDB buffer pool instances: 1 [--] InnoDB Buffer Pool Chunk Size not used or defined in your version [OK] InnoDB Read buffer efficiency: 100.00% (116110977 hits/ 116111816 total) [!!] InnoDB Write Log efficiency: 4857.14% (10880 hits/ 224 total) [OK] InnoDB log waits: 0.00% (0 waits / 11104 writes) This is the configuration of InnoDb engine: innodb ON innodb-adaptive-flushing TRUE innodb-adaptive-flushing-lwm 10 innodb-adaptive-hash-index TRUE innodb-adaptive-hash-index-partitions 1 innodb-adaptive-max-sleep-delay 150000 innodb-additional-mem-pool-size 8388608 innodb-api-bk-commit-interval 5 innodb-api-disable-rowlock FALSE innodb-api-enable-binlog FALSE innodb-api-enable-mdl FALSE innodb-api-trx-level 0 innodb-autoextend-increment 64 innodb-autoinc-lock-mode 1 innodb-background-scrub-data-check-interval 3600 innodb-background-scrub-data-compressed FALSE innodb-background-scrub-data-interval 604800 innodb-background-scrub-data-uncompressed FALSE innodb-buf-dump-status-frequency 0 innodb-buffer-page ON innodb-buffer-page-lru ON innodb-buffer-pool-dump-at-shutdown FALSE innodb-buffer-pool-dump-now FALSE innodb-buffer-pool-dump-pct 100 innodb-buffer-pool-filename ib_buffer_pool innodb-buffer-pool-instances 1 innodb-buffer-pool-load-abort FALSE innodb-buffer-pool-load-at-startup FALSE innodb-buffer-pool-load-now FALSE innodb-buffer-pool-populate FALSE innodb-buffer-pool-size 67108864 innodb-buffer-pool-stats ON innodb-change-buffer-max-size 25 innodb-change-buffering all innodb-changed-pages ON innodb-checksum-algorithm INNODB innodb-checksums TRUE innodb-cleaner-lsn-age-factor HIGH_CHECKPOINT innodb-cmp ON innodb-cmp-per-index ON innodb-cmp-per-index-enabled FALSE innodb-cmp-per-index-reset ON innodb-cmp-reset ON innodb-cmpmem ON innodb-cmpmem-reset ON innodb-commit-concurrency 0 innodb-compression-algorithm zlib innodb-compression-failure-threshold-pct 5 innodb-compression-level 6 innodb-compression-pad-pct-max 50 innodb-concurrency-tickets 5000 innodb-corrupt-table-action assert innodb-data-file-path (No default value) innodb-data-home-dir (No default value) innodb-default-encryption-key-id 1 innodb-defragment FALSE innodb-defragment-fill-factor 0.9 innodb-defragment-fill-factor-n-recs 20 innodb-defragment-frequency 40 innodb-defragment-n-pages 7 innodb-defragment-stats-accuracy 0 innodb-disable-sort-file-cache FALSE innodb-doublewrite TRUE innodb-empty-free-list-algorithm BACKOFF innodb-encrypt-log FALSE innodb-encrypt-tables OFF innodb-encryption-rotate-key-age 1 innodb-encryption-rotation-iops 100 innodb-encryption-threads 0 innodb-fake-changes FALSE innodb-fast-shutdown 1 innodb-fatal-semaphore-wait-threshold 600 innodb-file-format Antelope innodb-file-format-check TRUE innodb-file-format-max Antelope innodb-file-io-threads 4 innodb-file-per-table TRUE innodb-flush-log-at-timeout 1 innodb-flush-log-at-trx-commit 1 innodb-flush-method (No default value) innodb-flush-neighbors 1 innodb-flushing-avg-loops 30 innodb-force-load-corrupted FALSE innodb-force-primary-key FALSE innodb-force-recovery 0 innodb-foreground-preflush EXPONENTIAL_BACKOFF innodb-ft-aux-table (No default value) innodb-ft-being-deleted ON innodb-ft-cache-size 8000000 innodb-ft-config ON innodb-ft-default-stopword ON innodb-ft-deleted ON innodb-ft-enable-diag-print FALSE innodb-ft-enable-stopword TRUE innodb-ft-index-cache ON innodb-ft-index-table ON innodb-ft-max-token-size 84 innodb-ft-min-token-size 3 innodb-ft-num-word-optimize 2000 innodb-ft-result-cache-limit 2000000000 innodb-ft-server-stopword-table (No default value) innodb-ft-sort-pll-degree 2 innodb-ft-total-cache-size 640000000 innodb-ft-user-stopword-table (No default value) innodb-idle-flush-pct 100 innodb-immediate-scrub-data-uncompressed FALSE innodb-instrument-semaphores FALSE innodb-io-capacity 200 innodb-io-capacity-max 18446744073709551615 innodb-kill-idle-transaction 0 innodb-large-prefix FALSE innodb-lock-schedule-algorithm fcfs innodb-lock-wait-timeout 50 innodb-lock-waits ON innodb-locking-fake-changes TRUE innodb-locks ON innodb-locks-unsafe-for-binlog FALSE innodb-log-arch-dir (No default value) innodb-log-arch-expire-sec 0 innodb-log-archive FALSE innodb-log-block-size 512 innodb-log-buffer-size 16777216 innodb-log-checksum-algorithm INNODB innodb-log-compressed-pages TRUE innodb-log-file-size 8388608 innodb-log-files-in-group 2 innodb-log-group-home-dir (No default value) innodb-lru-scan-depth 1024 innodb-max-bitmap-file-size 104857600 innodb-max-changed-pages 1000000 innodb-max-dirty-pages-pct 75 innodb-max-dirty-pages-pct-lwm 0.001 innodb-max-purge-lag 0 innodb-max-purge-lag-delay 0 innodb-metrics ON innodb-mirrored-log-groups 0 innodb-monitor-disable (No default value) innodb-monitor-enable all innodb-monitor-reset (No default value) innodb-monitor-reset-all (No default value) innodb-mtflush-threads 8 innodb-mutexes ON innodb-old-blocks-pct 37 innodb-old-blocks-time 1000 innodb-online-alter-log-max-size 134217728 innodb-open-files 0 innodb-optimize-fulltext-only FALSE innodb-page-size 16384 innodb-prefix-index-cluster-optimization FALSE innodb-print-all-deadlocks FALSE innodb-purge-batch-size 300 innodb-purge-threads 1 innodb-random-read-ahead FALSE innodb-read-ahead-threshold 56 innodb-read-io-threads 4 innodb-read-only FALSE innodb-replication-delay 0 innodb-rollback-on-timeout FALSE innodb-rollback-segments 128 innodb-sched-priority-cleaner 19 innodb-scrub-log FALSE innodb-scrub-log-speed 256 innodb-show-locks-held 10 innodb-show-verbose-locks 0 innodb-simulate-comp-failures 0 innodb-sort-buffer-size 1048576 innodb-spin-wait-delay 6 innodb-stats-auto-recalc TRUE innodb-stats-include-delete-marked FALSE innodb-stats-method nulls_equal innodb-stats-modified-counter 0 innodb-stats-on-metadata FALSE innodb-stats-persistent TRUE innodb-stats-persistent-sample-pages 20 innodb-stats-sample-pages 8 innodb-stats-traditional TRUE innodb-stats-transient-sample-pages 8 innodb-status-file FALSE innodb-status-output FALSE innodb-status-output-locks FALSE innodb-strict-mode TRUE innodb-support-xa TRUE innodb-sync-array-size 1 innodb-sync-spin-loops 30 innodb-sys-columns ON innodb-sys-datafiles ON innodb-sys-fields ON innodb-sys-foreign ON innodb-sys-foreign-cols ON innodb-sys-indexes ON innodb-sys-semaphore-waits ON innodb-sys-tables ON innodb-sys-tablespaces ON innodb-sys-tablestats ON innodb-table-locks TRUE innodb-tablespaces-encryption ON innodb-tablespaces-scrubbing ON innodb-thread-concurrency 0 innodb-thread-sleep-delay 10000 innodb-tmpdir (No default value) innodb-track-changed-pages FALSE innodb-trx ON innodb-undo-directory . innodb-undo-logs 128 innodb-undo-tablespaces 0 innodb-use-atomic-writes FALSE innodb-use-fallocate FALSE innodb-use-global-flush-log-at-trx-commit TRUE innodb-use-mtflush FALSE innodb-use-native-aio TRUE innodb-use-stacktrace FALSE innodb-use-sys-malloc TRUE innodb-use-trim FALSE innodb-write-io-threads 4

I'm running a small mariadb DB to serve some websites and monitoring tools (nexctloud, wordpress and icinga2). The machine that works as a DB server also run an elasticsearch + graylog instance so I wanted to lower the maximu memory footprint mariadb could reach. Running mysqltuner gives me the following memory results:

[--] Binary logging is disabled
[--] Physical Memory     : 7.4G
[--] Max MySQL memory    : 4.7G
[--] Other process memory: 0B
[--] Total buffers: 2.7G global + 19.7M per thread (100 max threads)
[--] P_S Max memory usage: 95M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 2.9G (38.83% of installed RAM)
[OK] Maximum possible memory usage: 4.7G (63.35% of installed RAM)

I cannot figure out how the 19.7 M per connection is calculated as the sum of read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size + binlog_cache_size + thread_stack is by far lower than that. Here's my my.cnf file

[client]
default_character_set = utf8mb4
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld_safe]
log_error = /var/log/mysql/mysql_error.log
nice = 0
socket = /var/run/mysqld/mysqld.sock

[mariadb]
ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem
tls_version = TLSv1.2,TLSv1.3

[mysqld]
basedir = /usr
#bind_address = 0.0.0.0
datadir = /var/lib/mysql
max_allowed_packet = 16M
pid_file = /var/run/mysqld/mysqld.pid
port = 3306
skip_external_locking
skip_name_resolve
socket = /var/run/mysqld/mysqld.sock
tmpdir = /dev/shm
user = mysql
##tmpdir = /tmp

character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
concurrent_insert = 2
connect_timeout = 5
interactive_timeout = 600
wait_timeout = 600
lc_messages_dir = /usr/share/mysql
lc_messages = en_US
transaction_isolation = READ-COMMITTED

###temp tables
tmp_table_size = 64M
max_heap_table_size = 64M

###innodb settings
default_storage_engine = InnoDB
innodb_buffer_pool_size = 2500M
innodb_buffer_pool_instances = 2
innodb_flush_log_at_trx_commit = 0
innodb_log_buffer_size = 16M
innodb_log_file_size = 384M
innodb_max_dirty_pages_pct = 90
innodb_file_per_table = 1
innodb_open_files = 400
innodb_io_capacity = 4000
innodb_flush_method = O_DIRECT
innodb_stats_on_metadata = 0

###connections
max_connections = 100

###buffer settings
read_buffer_size = 1M
read_rnd_buffer_size = 1M
sort_buffer_size = 1M
join_buffer_size = 512K
key_buffer_size = 128K

###log
expire_logs_days = 7
general_log_file = /var/log/mysql/mysql.log
general_log = 0
skip-log-bin
log_error=/var/log/mysql/mysql_error.log
log_slow_verbosity = query_plan
log_warnings = 2
slow_query_log_file = /var/log/mysql/mysql_slow.log
slow_query_log = 1

###query cache
query_cache_type = 0
query_cache_size = 0
table_definition_cache = 8000 # UPD
table_open_cache = 40000 # UPD
open_files_limit = 60000
thread_stack = 192K
thread_cache_size = 100
back_log = 512

myisam_recover_options = BACKUP

[mysqldump]
host = localhost
port = 3306
max_allowed_packet = 16M
quick
quote_names

[isamchk]
!include /etc/mysql/mariadb.cnf
!includedir /etc/mysql/conf.d/
key_buffer = 10M

thank you very much! This is my mysqltuner output, unfortunately the mysql instance is running only since 14 hours due to a kernel update but usually these are the usual results (in terms of warnings and recommendations) I get after weeks of uptime

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 14h 7m 30s (482K q [9.485 qps], 9K conn, TX: 1G, RX: 64M)
[--] Reads / Writes: 90% / 10%
[--] Binary logging is disabled
[--] Physical Memory     : 7.4G
[--] Max MySQL memory    : 4.7G
[--] Other process memory: 0B
[--] Total buffers: 2.7G global + 19.7M per thread (100 max threads)
[--] P_S Max memory usage: 95M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 3.0G (40.12% of installed RAM)
[OK] Maximum possible memory usage: 4.7G (63.35% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/482K)
[OK] Highest usage of available connections: 10% (10/100)
[OK] Aborted connections: 0.00%  (0/9725)
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 8K sorts)
[!!] Joins performed without indexes: 4747
[OK] Temporary tables created on disk: 9% (11K on disk / 122K total)
[OK] Thread cache hit rate: 99% (10 created / 9K connections)
[OK] Table cache hit rate: 97% (235 open / 241 opened)
[OK] table_definition_cache(8000) is upper than number of tables(353)
[OK] Open file limit used: 0% (57/80K)
[OK] Table locks acquired immediately: 100% (900 immediate / 900 locks)
 
-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 95.5M
[--] Sys schema is installed.
 
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 4 thread(s).
[--] Using default value is good enough for your version (10.4.12-MariaDB-1:10.4.12+maria~buster-log)
 
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 24.2% (31K used / 131K cache)
[!!] Cannot calculate MyISAM index size - re-run script as root user
 
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 2.5G/69.8M
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 384.0M * 2/2.5G should be equal to 25%
[OK] InnoDB buffer pool instances: 2
[--] Number of InnoDB Buffer Pool Chunk : 20 for 2 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.93% (9223051 hits/ 9229566 total)
[!!] InnoDB Write Log efficiency: 83.88% (41174 hits/ 49089 total)
[OK] InnoDB log waits: 0.00% (0 waits / 7915 writes)
 
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/312.0K
[OK] Aria pagecache hit rate: 99.7% (678K cached / 1K reads)
 
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
 
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
 
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
 
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: MIXED
[--] XA support enabled: ON
[--] Semi synchronous replication Master: OFF
[--] Semi synchronous replication Slave: OFF
[--] This is a standalone server
 
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Control warning line(s) into /var/log/mysql/mysql_error.log file
    Control error line(s) into /var/log/mysql/mysql_error.log file
    Set up a Secure Password for netdata@localhost user: SET PASSWORD FOR 'netdata'@'SpecificDNSorIp' = PASSWORD('secure_password');
    MySQL was started within the last 24 hours - recommendations may be inaccurate
    We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
             See https://dev.mysql.com/doc/internals/en/join-buffer-size.html 
             (specially the conclusions at the bottom of the page).
Variables to adjust:
    join_buffer_size (> 512.0K, or always use indexes with JOINs)