Random Error establishing a database connection, but no errors in log.

Lucky007 · Oct 11, 2021

Again thank you for your support.

Could you please help me to change the "Maximum possible memory usage: 6.2G" to below 2GB. I have only 2GB RAM.

>>>>>Are you using MyISAM tables?
No, all WordPress sites use InnoDB tables.

>>>There must be something in the logs to have some hint on what went wrong.
Today error log file is empty. Still, I didn't receive any corn failed email. I have little suspect about monit (it may restart MySQL when it uses high memory).

This is my mysqltuner output.

MySQLTuner 1.8.1 - 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
[OK] Currently running supported MySQL version 10.5.12-MariaDB-1:10.5.12+maria_focal-log
[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(0B)
[--] Log file /var/log/mysql/error.log is empty. Assuming log-rotation. Use --server-log={file} for explicit file

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in InnoDB tables: 138.8M (Tables: 407)
[--] Data in MyISAM tables: 4.6M (Tables: 2)
[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: 23h 32m 3s (492K q [5.814 qps], 6K conn, TX: 2G, RX: 111M)
[--] Reads / Writes: 80% / 20%
[--] Binary logging is disabled
[--] Physical Memory : 1.9G
[--] Max MySQL memory : 6.2G
[--] Other process memory: 0B
[--] Total buffers: 644.0M global + 71.5M per thread (80 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 2.4G (126.13% of installed RAM)
[!!] Maximum possible memory usage: 6.2G (320.71% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/492K)
[OK] Highest usage of available connections: 32% (26/80)
[OK] Aborted connections: 0.65% (40/6144)
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 23K sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 4% (11K on disk / 277K total)
[OK] Thread cache hit rate: 99% (31 created / 6K connections)
[OK] Table cache hit rate: 99% (213K hits / 213K requests)
[OK] table_definition_cache(700) is upper than number of tables(602)
[OK] Open file limit used: 0% (62/500K)
[OK] Table locks acquired immediately: 100% (1K immediate / 1K 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.5.12-MariaDB-1:10.5.12+maria_focal-log)

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (24M used / 134M cache)
[OK] Key buffer size / total MyISAM indexes: 128.0M/1.3M

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 256.0M/138.8M
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 74.0M * 1/256.0M should be equal to 25%
[--] Number of InnoDB Buffer Pool Chunk : 2 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% (11404902 hits/ 11411464 total)
[!!] InnoDB Write Log efficiency: 711.71% (30383 hits/ 4269 total)
[OK] InnoDB log waits: 0.00% (0 waits / 34652 writes)

-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/336.0K
[!!] Aria pagecache hit rate: 83.1% (64K cached / 10K 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
Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
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
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
performance_schema = ON enable PFS

Jjounin · Oct 11, 2021

upgrade your vps

Lucky007 · Oct 12, 2021

jounin It is useless. Because this server never each at least 80% RAM usage. Always it is below 1.2GB.

tyrro · Oct 13, 2021

He doesn't need to upgrade at this time, his amount of data is small. He just need to adjust MySQL my.cnf config file to use less resources. I'll try to send you my recommendations tomorrow.

Lucky007 · Oct 13, 2021

tyrro I did few changes. Right now it mysqltuner not showing high RAM usage warning.

I made the following changes in my 50-server.cnf. Is this optimization ok for 10 WordPress sites (FastCGI enabled). After changed max_allowed_packet it drastically change the max memory usage. Is it safe?

**Changed following values in wordops default 50-server.cnf**
max_allowed_packet  =	 32M
sort_buffer_size	   = 256K
read_rnd_buffer_size   = 256K
read_buffer_size	    = 256K

key_buffer_size	= 16M
open-files-limit	= 1024
query_cache_size	= 50M
query_cache_limit    = 256K

innodb_buffer_pool_size = 450M
innodb_log_file_size	= 112M
innodb_log_buffer_size	= 148M

**Newly added options**
skip-name-resolve
log-error = /var/log/mysql/error.log
table_definition_cache = -1
performance_schema = ON
innodb_lru_scan_depth = 1024
query_cache_min_res_unit = 2k
innodb_log_files_in_group	= 2

tyrro · Oct 13, 2021

You don't need to change your innodb_buffer_pool_size = 450M, 450 is a lot, you only use 139M of data according to sqltuner report. So reduce it back to 200M.
Reduce key_buffer_size to 64M.
Reduce myisam_sort_buffer_size from 512M to 128M.
Reduce your max_connections to 60.

Let's try with these and reload your mysql. Then run mysqltuner again to see the resulting data usage.

Most likely you're getting Error establishing a database connection because your mysql instance went down when it used 129.87% of installed RAM according to your data. Server just went out of memory.

Lucky007 · Oct 13, 2021

tyrro Again thank your support.

As per your recommendation, I change that value. However, If I use max_connections to 60 again mysqltunershows Maximum possible memory usage warning. If I set it to 30 then mysqltuner shows it is ok.

Are there any negative impact when change max_allowed_packet to 32M

tyrro · Oct 13, 2021

Do not reduce max_connections if you have seen max usage of around 40, let's just keep it at 60 and lower other settings. I don't have time now but I will take a look tomorrow. Reduce max_allowed_packet to 16M.

Lucky007 · Oct 13, 2021

tyrro Thank you. OK, once you get free please reply to this thread. No hurry.

For testing purposes, I have changed max_connectionsto 60, and max_allowed_packetto 16M, then mysqltuner shows [!!] Maximum possible memory usage: 1.7G (86.39% of installed RAM).

So I change max_connectionsto 50, now it is [OK] Maximum possible memory usage: 1.5G (77.69% of installed RAM)

But now it shows [!!] InnoDB Write Log efficiency: 188.45% (1321 hits/ 701 total)

tyrro · Oct 14, 2021

If you never gonna use replication you don't need to keep binary logs, here's a good article how to disable them.
https://geekflare.com/disable-binary-log-mariadb/
For InnoDB proper log size read these articles: https://dzone.com/articles/how-to-choose-the-mysql-innodb-log-file-size and https://scalegrid.io/blog/calculating-innodb-buffer-pool-size-for-your-mysql-server/

further reduce key_buffer_size to 32M
further reduce myisam_sort_buffer_size to 32M
if you don't trying to find slow queries, disable slow_query_log = 0, instead of 1.
reduce both
innodb_open_files = 500000
innodb_io_capacity = 500000 to say 400.
add innodb_buffer_pool_instances = 4
add innodb_file_format = barracuda

Lucky007 · Oct 14, 2021

tyrro Just applied. Thank you.

tyrro · Oct 16, 2021

So what's your sqltuner latest report? with new settings applied.

Lucky007 · Oct 16, 2021

tyrro

It seems that now everything ok.

MySQLTuner 1.8.1 - 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
[OK] Currently running supported MySQL version 10.5.12-MariaDB-1:10.5.12+maria_focal
[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(0B)
[--] Log file /var/log/mysql/error.log is empty. Assuming log-rotation. Use --server-log={file} for explicit file

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in InnoDB tables: 141.7M (Tables: 407)
[--] Data in MyISAM tables: 4.6M (Tables: 2)
[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 18h 6m 27s (784K q [5.177 qps], 13K conn, TX: 4G, RX: 178M)
[--] Reads / Writes: 73% / 27%
[--] Binary logging is disabled
[--] Physical Memory : 1.9G
[--] Max MySQL memory : 1.5G
[--] Other process memory: 0B
[--] Total buffers: 646.0M global + 17.3M per thread (50 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 905.3M (45.60% of installed RAM)
[OK] Maximum possible memory usage: 1.5G (76.08% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/784K)
[OK] Highest usage of available connections: 30% (15/50)
[!!] Aborted connections: 18.86% (2544/13489)
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Query cache efficiency: 32.0% (86K cached / 270K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 10K sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 3% (17K on disk / 483K total)
[OK] Thread cache hit rate: 99% (40 created / 13K connections)
[OK] Table cache hit rate: 96% (170K hits / 176K requests)
[OK] table_definition_cache(2097152) is upper than number of tables(602)
[OK] Open file limit used: 0% (4/1K)
[OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 72B
[--] 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.5.12-MariaDB-1:10.5.12+maria_focal)

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.3% (6M used / 33M cache)
[OK] Key buffer size / total MyISAM indexes: 32.0M/1.3M

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 256.0M/141.7M
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 64.0M * 1/256.0M should be equal to 25%[--] Number of InnoDB Buffer Pool Chunk : 2 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% (9661860 hits/ 9668846 total)
[!!] InnoDB Write Log efficiency: 823.04% (56551 hits/ 6871 total)
[OK] InnoDB log waits: 0.00% (0 waits / 63422 writes)

-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/336.0K
[!!] Aria pagecache hit rate: 82.4% (99K cached / 17K 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:
Reduce or eliminate unclosed connections and network issues
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
Variables to adjust:
query_cache_size (=0)
query_cache_type (=0)