Query Cache

[alexlii1971]

Hello,

On a fresh installed wordpress, I checked Query Cache by show variables like 'query_cache_%' ;

+------------------------------+--------+
| Variable_name | Value |
+------------------------------+--------+
| query_cache_limit | 131072 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_strip_comments | OFF |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
+------------------------------+--------+
6 rows in set (0.001 sec)

It seems not the default setting since query_cache_size is 0, and query_cache_limit is 131072, but not the default value of 1048576.

For reference
https://www.digitalocean.com/community/tutorials/how-to-optimize-mysql-with-query-cache-on-ubuntu-18-04#step-2-checking-the-default-query-cache-variables

SHOW STATUS LIKE 'Qcache%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
+-------------------------+-------+
8 rows in set (0.001 sec)

All of value is 0.

So, what is default configuration value in Wordops, is there anything wrong setting with my site?

If yes, any way to fix it or update the configurations?

Thanks

[alexlii1971]

Hello @VirtuBox

On fresh installed wordpress site, Mysql or Mariadb are always killed by OOM, but we have 8 GB Memory / 4 AMD vCPUs / 160 GB Disk. so, generally, it use very limited resource:

So, I just suppose there is mis configration on Mysql.

wo -v
WordOps v3.16.3
Copyright (c) 2023 WordOps.

 wp cli info --allow-root
wp cli info --allow-root
OS: Linux 5.15.0-70-generic https://github.com/WordOps/WordOps/issues/77-Ubuntu SMP Tue Mar 21 14:02:37 UTC 2023 x86_64
Shell: /bin/bash
PHP binary: /usr/bin/php8.2
PHP version: 8.2.5
php.ini used: /etc/php/8.2/cli/php.ini
MySQL binary: /usr/bin/mysql
MySQL version: mysql Ver 15.1 Distrib 10.6.12-MariaDB, for debian-linux-gnu (x86_64) using EditLine wrapper
SQL modes: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
WP-CLI root dir: phar://wp-cli.phar/vendor/wp-cli/wp-cli
WP-CLI vendor dir: phar://wp-cli.phar/vendor
WP_CLI phar path: /root
WP-CLI packages dir:
WP-CLI cache dir: /root/.wp-cli/cache
WP-CLI global config:
WP-CLI project config:
WP-CLI version: 2.7.1

Here are logs:

Jun 27 03:48:40 SF-mysite-2 kernel: [305537.425404] oom-kill:constraint=CONSTRAINT_NONE,nodemask=(null),cpuset=init.scope,mems_allowed=0,global_oom,task_memcg=/system.slice/mariadb.service,task=mariadbd,pid=40975,uid=114
Jun 27 03:48:40 SF-mysite-2 kernel: [305537.425507] Out of memory: Killed process 40975 (mariadbd) total-vm:6105184kB, anon-rss:2703552kB, file-rss:0kB, shmem-rss:0kB, UID:114 pgtables:6352kB oom_score_adj:0
Jun 27 03:48:40 SF-mysite-2 systemd[1]: mariadb.service: A process of this unit has been killed by the OOM killer.
Jun 27 03:48:40 SF-mysite-2 systemd[1]: mariadb.service: Main process exited, code=killed, status=9/KILL
Jun 27 03:48:40 SF-mysite-2 systemd[1]: mariadb.service: Failed with result 'oom-kill'.
Jun 27 03:48:40 SF-mysite-2 systemd[1]: mariadb.service: Consumed 4min 13.562s CPU time.
Jun 27 03:48:43 SF-mysite-2 sm-mta[2473]: rejecting connections on daemon MTA-v4: load average: 51
Jun 27 03:48:43 SF-mysite-2 sm-mta[2473]: rejecting connections on daemon MSP-v4: load average: 51
Jun 27 03:48:58 SF-mysite-2 sm-mta[2473]: accepting connections again for daemon MTA-v4
Jun 27 03:48:58 SF-mysite-2 sm-mta[2473]: accepting connections again for daemon MSP-v4
Jun 27 03:49:06 SF-mysite-2 sendmail[42479]: unable to qualify my own domain name (SF-mysite-2) -- using short name
Jun 27 03:49:06 SF-mysite-2 sendmail[42481]: unable to qualify my own domain name (SF-mysite-2) -- using short name
Jun 27 03:49:06 SF-mysite-2 sendmail[42479]: 35R3n68X042479: from=netdata, size=46631, class=0, nrcpts=1, msgid=<202306270349.35R3n68X042479@SF-mysite-2>, relay=netdata@localhost
Jun 27 03:49:06 SF-mysite-2 sendmail[42481]: 35R3n6IO042481: from=netdata, size=46846, class=0, nrcpts=1, msgid=<202306270349.35R3n6IO042481@SF-mysite-2>, relay=netdata@localhost
Jun 27 03:49:06 SF-mysite-2 sm-mta[42569]: 35R3n6rw042569: from=<netdata@SF-mysite-2>, size=47112, class=0, nrcpts=1, msgid=<202306270349.35R3n6IO042481@SF-mysite-2>, proto=ESMTP, daemon=MTA-v4, relay=localhost [127.0.0.1]
Jun 27 03:49:06 SF-mysite-2 sm-mta[42568]: 35R3n6tM042568: from=<netdata@SF-mysite-2>, size=46897, class=0, nrcpts=1, msgid=<202306270349.35R3n68X042479@SF-mysite-2>, proto=ESMTP, daemon=MTA-v4, relay=localhost [127.0.0.1]
Jun 27 03:49:06 SF-mysite-2 sendmail[42481]: 35R3n6IO042481: to=root, ctladdr=netdata (998/999), delay=00:00:00, xdelay=00:00:00, mailer=relay, pri=76846, relay=[127.0.0.1] [127.0.0.1], dsn=2.0.0, stat=Sent (35R3n6rw042569 Message accepted for delivery)
Jun 27 03:49:06 SF-mysite-2 sendmail[42479]: 35R3n68X042479: to=root, ctladdr=netdata (998/999), delay=00:00:00, xdelay=00:00:00, mailer=relay, pri=76631, relay=[127.0.0.1] [127.0.0.1], dsn=2.0.0, stat=Sent (35R3n6tM042568 Message accepted for delivery)
Jun 27 03:49:06 SF-mysite-2 sm-mta[42571]: 35R3n6tM042568: to=<root@SF-mysite-2>, ctladdr=<netdata@SF-mysite-2> (998/999), delay=00:00:00, xdelay=00:00:00, mailer=local, pri=77103, dsn=2.0.0, stat=Sent
Jun 27 03:49:07 SF-mysite-2 sendmail[42605]: My unqualified host name (SF-mysite-2) unknown; sleeping for retry
Jun 27 03:49:11 SF-mysite-2 systemd[1]: Started Session 538 of User root.
Jun 27 03:49:14 SF-mysite-2 sm-mta[42570]: 35R3n6rw042569: to=<root@SF-mysite-2>, ctladdr=<netdata@SF-mysite-2> (998/999), delay=00:00:08, xdelay=00:00:08, mailer=local, pri=77318, dsn=2.0.0, stat=Sent
Jun 27 03:49:30 SF-mysite-2 systemd[1]: Starting MariaDB 10.6.14 database server...
Jun 27 03:49:30 SF-mysite-2 mariadbd[42872]: 2023-06-27  3:49:30 0 [Note] Starting MariaDB 10.6.14-MariaDB-1:10.6.14+maria~ubu2204-log source revision c93754d45e5d9379e3e23d7ada1d5f21d2711f66 as process 42872

I checked several tutorials at https://easyengine.io/tutorials/mysql/query-cache/
However, it claim Note that the query cache cannot be enabled in certain environments. See [Limitations](https://mariadb.com/kb/en/query-cache/#limitations).

and it shows The query cache also needs to be disabled for MariaDB [Galera](https://mariadb.com/kb/en/galera/) cluster versions prior to "5.5.40-galera", "10.0.14-galera" and "10.1.2".

Since we are using the latest version of 10.6.12-MariaDB, I am confused upon what should be the default setting in Maraidb.conf, should we enable or disable on query cache for 10.6.12-MariaDB ?

Or, what is the default setting on Query cache by Wordops?

By the way, the issue was from the post at #516, and I created the new post since I just suppose it is new topic.

Thanks, and have a nice day.

[vikas5914]

@alexlii1971 The limitation is talking about MariaDB's Galera cluster mode. So for the regular MariaDB server, It should be enabled.

Also, the default MariaDB configuration takes too much memory usage. So I usually check the internet and bring it down using the values using mysqltuner.

[alexlii1971]

@vikas5914

Thanks,

The issue of Mariadb being killed by OOM was from the post at #516, and @VirtuBox said:

Hello @alexlii1971, I will update the documentation because you should not run wo stack migrate --mariadb if you are already using MariaDB 10.6, and it's look like there is a bug because WordOps should tell you you are already running the latest supported MariaDB release. I will take a look on this. Thanks for your report.

Is there a built-in optimized conf setting by Wordops? If no, is there are recommend or optimized setting so that we can fix the issue of MariaDB being killed by OOM?

By the way, when run mysqltuner --user dbuser --pass dbPW, 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 passed on the command line
[!!] Failed to execute: SHOW REPLICA STATUS\G
[!!] FAIL Execute SQL / return code: 256
[!!] Failed to execute: SHOW SLAVE STATUS
[!!] FAIL Execute SQL / return code: 256
 
[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: 253)
[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] No Role user detected
[!!] Failed to execute: SELECT CONCAT(QUOTE(user), '@', QUOTE(host)) FROM mysql.user WHERE  (TRIM(USER) = '' OR USER IS NULL)
[!!] FAIL Execute SQL / return code: 256
[OK] There are no anonymous accounts for any database users
[!!] Failed to execute: SELECT CONCAT(QUOTE(user), '@', QUOTE(host)) FROM mysql.global_priv WHERE
    ( user != ''
    AND JSON_CONTAINS(Priv, '"mysql_native_password"', '$.plugin') AND JSON_CONTAINS(Priv, '""', '$.authentication_string')
    AND NOT JSON_CONTAINS(Priv, 'true', '$.account_locked')
    )
[!!] FAIL Execute SQL / return code: 256
[OK] All database users have passwords assigned
[!!] Failed to execute: SELECT CONCAT(QUOTE(user), '@', QUOTE(host)) FROM mysql.user WHERE user != '' AND (CAST(password as Binary) = PASSWORD(user) OR CAST(password as Binary) = PASSWORD(UPPER(user)) OR CAST(password as Binary) = PASSWORD(CONCAT(UPPER(LEFT(User, 1)), SUBSTRING(User, 2, LENGTH(User)))))
[!!] FAIL Execute SQL / return code: 256
[!!] Failed to execute: SELECT CONCAT(QUOTE(user), '@', host) FROM mysql.user WHERE HOST='%'
[!!] FAIL Execute SQL / return code: 256
[!!] There is no basic password file list!
 
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
 
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 51m 43s (623K q [200.890 qps], 605 conn, TX: 8G, RX: 136M)
[--] Reads / Writes: 97% / 3%
[--] Binary logging is disabled
[--] Physical Memory     : 7.8G
[--] Max MySQL memory    : 4.0G
[--] Other process memory: 0B
[--] Total buffers: 3.2G global + 7.6M per thread (100 max threads)
[--] Performance_schema Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 3.3G (42.61% of installed RAM)
[OK] Maximum possible memory usage: 4.0G (51.09% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/623K)
[OK] Highest usage of available connections: 11% (11/100)
[OK] Aborted connections: 0.00% (0/605)
[!!] Name resolution is active: a reverse name resolution is made for each new connection which can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 95K sorts)
[!!] Joins performed without indexes: 393
[OK] Temporary tables created on disk: 5% (5K on disk / 89K total)
[OK] Thread cache hit rate: 98% (11 created / 605 connections)
[OK] Table cache hit rate: 99% (803K hits / 804K requests)
[OK] table_definition_cache (400) is greater than number of tables (332)
[OK] Open file limit used: 0% (56/500K)
[OK] Table locks acquired immediately: 100% (232 immediate / 232 locks)
 
-------- Performance schema ------------------------------------------------------------------------
[!!] Performance_schema should be activated.
[--] Sys schema is not 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
[OK] InnoDB buffer pool / data size: 2.4G / 1.5G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (12.2121710526316%): 297.0M * 1 / 2.4G should be equal to 25%
[--] Number of InnoDB Buffer Pool Chunk: 19 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.94% (81464552 hits / 81515854 total)
[!!] InnoDB Write Log efficiency: 230.11% (8123 hits / 3530 total)
[OK] InnoDB log waits: 0.00% (0 waits / 11653 writes)
 
-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/0B
[OK] Aria pagecache hit rate: 99.1% (567K cached / 4K 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
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=OFF
    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
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
    skip-name-resolve=OFF
    join_buffer_size (> 256.0K, or always use indexes with JOINs)
    performance_schema=ON
    innodb_log_file_size should be (=608M) if possible, so InnoDB total log file size equals 25% of buffer pool size.

Really expect your guiding!

Thanks

[alexlii1971]

Hello @VirtuBox

I just did a fresh install on a fresh server.

Here is the fresh installed schema of "50-server.cnf" at /etc/mysql/mariaddb.conf.d/50-server.cnf, it seems not a optimized schema, it would be great if you could provide a optimized schema:

# 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]
#
# * 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		= 100
connect_timeout		= 5
wait_timeout		= 60
max_allowed_packet	= 64M
thread_cache_size		 = 128
sort_buffer_size	= 4M
bulk_insert_buffer_size	= 16M
tmp_table_size		= 32M
max_heap_table_size	= 32M
#
# * 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		= 128K
query_cache_size		= 0
# for more write intensive setups, set to DEMAND or OFF
query_cache_type		= 0
#
# * 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	= 36M
innodb_buffer_pool_size	= 290M
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.
#

[alexlii1971]

@VirtuBox

Sorry mislead you.
After compare two 50-server.cnf, I just note there are some difference.

I tried to replace/overwrite the old one with the latest one, the Mariadb was not crashed, but show up:

Error establishing a database connection:

And the front page will show up after refresh, because mariadb was not crashed, This is great.

However, is there a way to avoid the issue "Error establishing a database connection:"?

there is no error log by wo log show mysite --mysql, and here is Mysql status:

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 Sun 2023-07-02 02:24:10 UTC; 1h 19min ago
       Docs: man:mariadbd(8)
             https://mariadb.com/kb/en/library/systemd/
    Process: 44318 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
    Process: 44319 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Process: 44321 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ]   && s>
    Process: 44479 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Process: 44488 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
   Main PID: 44470 (mariadbd)
     Status: "Taking your SQL requests now..."
      Tasks: 21 (limit: 9477)
     Memory: 834.9M
        CPU: 9min 35.403s
     CGroup: /system.slice/mariadb.service
             └─44470 /usr/sbin/mariadbd

Jul 02 02:24:10 SF-mysite-2 mariadbd[44470]: 2023-07-02  2:24:10 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
Jul 02 02:24:10 SF-mysite-2 mariadbd[44470]: 2023-07-02  2:24:10 0 [Note] Server socket created on IP: '::1'.
Jul 02 02:24:10 SF-mysite-2 mariadbd[44470]: 2023-07-02  2:24:10 0 [Note] Server socket created on IP: '127.0.0.1'.
Jul 02 02:24:10 SF-mysite-2 mariadbd[44470]: 2023-07-02  2:24:10 0 [Note] /usr/sbin/mariadbd: ready for connections.
Jul 02 02:24:10 SF-mysite-2 mariadbd[44470]: Version: '10.6.14-MariaDB-1:10.6.14+maria~ubu2204-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  mari>
Jul 02 02:24:10 SF-mysite-2 systemd[1]: Started MariaDB 10.6.14 database server.
Jul 02 02:24:10 SF-mysite-2 /etc/mysql/debian-start[44490]: Upgrading MySQL tables if necessary.
Jul 02 02:24:10 SF-mysite-2 /etc/mysql/debian-start[44501]: Checking for insecure root accounts.
Jul 02 02:24:10 SF-mysite-2 /etc/mysql/debian-start[44505]: Triggering myisam-recover for all MyISAM tables and aria-recover for all Aria tables
Jul 02 02:24:12 SF-mysite-2 mariadbd[44470]: 2023-07-02  2:24:12 0 [Note] InnoDB: Buffer pool(s) load completed at 230702  2:24:12

Here is the old conf file for referance:

# 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]
#
# * 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		= 100
connect_timeout		= 5
wait_timeout		= 60
max_allowed_packet	= 64M
thread_cache_size		 = 128
sort_buffer_size	= 4M
bulk_insert_buffer_size	= 16M
tmp_table_size		= 128M
max_heap_table_size	= 128M
#
# * 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		= 128K
query_cache_size		= 0
# for more write intensive setups, set to DEMAND or OFF
query_cache_type		= 0
#
# * 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	= 297M
innodb_buffer_pool_size	= 2381M
innodb_log_buffer_size	= 595M
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.
#

[vikas5914]

@alexlii1971 For the question you asked me, There is no universal optimized config for the MariaDB since values depend on the memory you have.

I usually search MariaDB config for 4GB RAM on Stackoverflow or Reddit to get the values. Sometimes on ChatGPT as well. Then read about the values and modify them.

Also, Could you tell me how you installed the mysqltunner? Did you use the WordOps command?

[alexlii1971]

@vikas5914

Great thanks.

I install all stack by wo stack install -all

Yes, I do use wordops command. So mysqltuner was installed at the beginning. But, frankly I have not used it before, so I am quite fresh with Sqltuner.

If it is not too much I would ask your recommendation on the server with 8 memory 4cpus 150gb storage.

Great thanks

[github-actions]

This issue is stale because it has been open 30 days with no activity. Remove stale label or comment or this will be closed in 5 days.

[github-actions]

This issue was closed because it has been stalled for 5 days with no activity.