• Questions

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

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

    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.

      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

        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.

          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

            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.

              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)

                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

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

                    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+mariafocal
                    [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+mariafocal)

                    -------- 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)

                      Hosted by VirtuBox