Expect experienced Mysql tuning by Mysqltuner ?

alex

Hello,

I migrate my site to a new server with 8 GB Memory / 4 AMD vCPUs / 160 GB Disk, but the site is not stable, because There is no errors if run wo log show lovcour.com --php --mysql --nginx, but, sometimes the mysql will get down, sometimes get errors or warnings.

Really expect experienced instruction on how to tune the database, or share an optimized 50-server.cnf

Thanks in advance, and the following information for reference:

service mysql status
● mariadb.service - MariaDB 10.6.14 database server
     Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
    Drop-In: /etc/systemd/system/mariadb.service.d
             └─limits.conf, migrated-from-my.cnf-settings.conf
     Active: active (running) since Fri 2023-07-14 12:23:58 UTC; 1h 29min ago
       Docs: man:mariadbd(8)
             https://mariadb.com/kb/en/library/systemd/
    Process: 35342 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
    Process: 35343 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Process: 35346 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? >
    Process: 35521 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Process: 35523 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
   Main PID: 35504 (mariadbd)
     Status: "Taking your SQL requests now..."
      Tasks: 29 (limit: 9477)
     Memory: 1.1G
        CPU: 26min 31.025s
     CGroup: /system.slice/mariadb.service
             └─35504 /usr/sbin/mariadbd

Jul 14 12:23:58 SF-mysite-2 mariadbd[35504]: 2023-07-14 12:23:58 0 [Note] Server socket created on IP: '::1'.
Jul 14 12:23:58 SF-mysite-2 mariadbd[35504]: 2023-07-14 12:23:58 0 [Note] Server socket created on IP: '127.0.0.1'.
Jul 14 12:23:58 SF-mysite-2 mariadbd[35504]: 2023-07-14 12:23:58 0 [Note] /usr/sbin/mariadbd: ready for connections.
Jul 14 12:23:58 SF-mysite-2 mariadbd[35504]: Version: '10.6.14-MariaDB-1:10.6.14+maria~ubu2204-log'  socket: '/var/run/mysqld/mysqld.sock'  po>
Jul 14 12:23:58 SF-mysite-2 systemd[1]: Started MariaDB 10.6.14 database server.
**Jul 14 12:23:58 SF-mysite-2 /etc/mysql/debian-start[35525]: Upgrading MySQL tables if necessary.
Jul 14 12:23:59 SF-mysite-2 /etc/mysql/debian-start[35541]: Triggering myisam-recover for all MyISAM tables and aria-recover for all Aria tabl>**
Jul 14 12:23:59 SF-mysite-2 mariadbd[35504]: 2023-07-14 12:23:59 0 [Note] InnoDB: Buffer pool(s) load completed at 230714 12:23:59
**Jul 14 13:44:43 SF-mysite-2 mariadbd[35504]: 2023-07-14 13:44:43 1198 [Warning] Aborted connection 1198 to db: 'mysite_com_AtxdyVmk' user: 'l>
Jul 14 13:49:46 SF-mysite-2 mariadbd[35504]: 2023-07-14 13:49:46 1305 [Warning] Aborted connection 1305 to db: 'mysite_com_AtxdyVmk' user: 'l>**
lines 1-30/30 (END)

If run mysqltuner, it show:

>>  MySQLTuner 2.1.7
         * Jean-Marie Renouard <jmrenouard@gmail.com>
         * Major Hayden <major@mhtx.net>
 >>  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.
 
[OK] Currently running supported MySQL version 10.6.14-MariaDB-1:10.6.14+maria~ubu2204-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: 1.5G (Tables: 262)
[--] Data in Aria tables: 32.0K (Tables: 1)
[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: 19m 20s (305K q [263.022 qps], 327 conn, TX: 4G, RX: 55M)
[--] Reads / Writes: 99% / 1%
[--] Binary logging is disabled
[--] Physical Memory     : 7.8G
[--] Max MySQL memory    : 2.6G
[--] Other process memory: 0B
[--] Total buffers: 1.1G global + 7.6M per thread (200 max threads)
[--] Performance_schema Max memory usage: 106M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 1.2G (15.71% of installed RAM)
[OK] Maximum possible memory usage: 2.6G (34.01% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/305K)
[OK] Highest usage of available connections: 4% (8/200)
[OK] Aborted connections: 0.00% (0/327)
[OK] Query cache efficiency: 24.4% (96K cached / 393K selects)
[!!] Query cache prunes per day: 7499892
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 28K sorts)
[!!] Joins performed without indexes: 15
[OK] Temporary tables created on disk: 6% (1K on disk / 27K total)
[OK] Thread cache hit rate: 97% (8 created / 327 connections)
[OK] Table cache hit rate: 99% (262K hits / 263K requests)
[!!] table_definition_cache (400) is less than number of tables (554) 
[OK] Open file limit used: 0% (56/500K)
[OK] Table locks acquired immediately: 100% (205 immediate / 205 locks)
 
-------- Performance schema ------------------------------------------------------------------------
[--] Performance_schema is activated.
[--] Memory used by Performance_schema: 106.5M
[--] Sys schema is installed.
 
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
 
-------- MyISAM Metrics ----------------------------------------------------------------------------
[--] No MyISAM table(s) detected ....
 
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 768.0M / 1.5G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (16.6666666666667%): 128.0M * 1 / 768.0M should be equal to 25%
[--] Number of InnoDB Buffer Pool Chunk: 6 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.82% (15171281 hits / 15199064 total)
[!!] InnoDB Write Log efficiency: 57.03% (641 hits / 1124 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1765 writes)
 
-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/352.0K
[OK] Aria pagecache hit rate: 96.0% (41K 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: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: OFF
[--] Semi synchronous replication Slave: OFF
[--] This is a standalone server
 
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    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).
    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 (> 64M)
    join_buffer_size (> 256.0K, or always use indexes with JOINs)
    table_definition_cache (400) > 554 or -1 (autosizing if supported)
    innodb_buffer_pool_size (>= 1.5G) if possible.
    innodb_log_file_size should be (=192M) if possible, so InnoDB total log file size equals 25% of buffer pool size.

Here is 50-server.cnf on server:


# MariaDB database server configuration file.
# Optimized by WordOps 3.16.3
#
# You can copy this file 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.
[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock

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

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0

[mysqld]
performance_schema=ON
skip-name-resolve

#
# * 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		= /tmp
lc_messages_dir	= /usr/share/mysql
lc_messages	= en_US
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address		= localhost
#
# * Fine Tuning
#
max_connections		= 200
connect_timeout		= 5
wait_timeout		= 60
max_allowed_packet	= 128M
thread_cache_size		 = 128
sort_buffer_size	= 4M
bulk_insert_buffer_size	= 16M
tmp_table_size		= 32M
max_heap_table_size	= 32M
join_buffer_size                = 256K



#
# * MyISAM
#
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched. On error, make copy and try a repair.
myisam_recover_options = BACKUP
key_buffer_size		= 16M
open-files-limit	= 500000
table_open_cache	= 16000
myisam_sort_buffer_size	= 128M
concurrent_insert	= 2
read_buffer_size	= 2M
read_rnd_buffer_size	= 1M
#
# * Query Cache Configuration
#
# Cache only tiny result sets, so we can fit more in the query cache.
query_cache_limit		= 4M
query_cache_size		= 64M
# for more write intensive setups, set to DEMAND or OFF
query_cache_type		= 1
#
# * 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             = 1
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# we do want to know about network errors and such
log_warnings		= 2
#
# Enable the slow query log to see queries with especially long duration
slow_query_log = 1
slow_query_log_file	= /var/log/mysql/mariadb-slow.log
long_query_time = 10
#log_slow_rate_limit	= 1000
log_slow_verbosity	= query_plan

#log-queries-not-using-indexes
#log_slow_admin_statements
#
# 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
#report_host		= master1
#auto_increment_increment = 2
#auto_increment_offset	= 1
#log_bin			= /var/log/mysql/mariadb-bin
#log_bin_index		= /var/log/mysql/mariadb-bin.index
# not fab for performance, but safer
#sync_binlog		= 1
#expire_logs_days	= 10
#max_binlog_size         = 100M
transaction_isolation = READ-COMMITTED
binlog_format = ROW

# slaves
#relay_log		= /var/log/mysql/relay-bin
#relay_log_index	= /var/log/mysql/relay-bin.index
#relay_log_info_file	= /var/log/mysql/relay-bin.info
#log_slave_updates
#read_only
#
# If applications support it, this stricter sql_mode prevents some
# mistakes like inserting invalid dates etc.
#sql_mode		= NO_ENGINE_SUBSTITUTION,TRADITIONAL
#
# * 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!
default_storage_engine	= InnoDB
# you can't just change log file size, requires special procedure
innodb_log_file_size	= 128M
innodb_buffer_pool_size	= 768M
innodb_log_buffer_size	= 72M
innodb_file_per_table	= 1
innodb_open_files	= 500000
innodb_io_capacity	= 500000
innodb_flush_method	= O_DIRECT
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

#
# * Galera-related settings
#
[galera]
# Mandatory settings
#wsrep_on=ON
#wsrep_provider=
#wsrep_cluster_address=
#binlog_format=row
#default_storage_engine=InnoDB
#innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
#bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0

[mysqldump]
quick
quote-names
max_allowed_packet	= 64M

[mysql]
#no-auto-rehash	# faster start of mysql but no tab completion

[isamchk]
key_buffer		= 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#

# === Table Settings ===

table_definition_cache          = 1000

marty

The first line in mysqltuner says:
MySQL was started within the last 24 hours: recommendations may be inaccurate
Wait longer so it has some useful data to work with, then run it again, then try what it says.
But maybe you need a bigger server, maybe a better VPS company (some companies vastly oversell the claimed resources).

alex

marty

Thanks

But maybe you need a bigger server, maybe a better VPS company (some companies vastly oversell the claimed resources).

I am using Digitalocean server, 8 GB Memory / 4 AMD vCPUs / 160 GB Disk, and only 10% disk is used.

For now, the resource is enough, and all of images were offloaded to Digital ocean Cloud storage.

Wait longer so it has some useful data to work with, then run it again, then try what it says.

Acutally, I think that is just the issue, when Mysql got down, I have to run service mysql restart, and that is why it show: MySQL was started within the last 24 hours:

It would be great if you could share the full content of 50-server.cnf on your site, would you?

Thanks in advance.