mysql cpanel database using too much memory!



  • I've been trying to figure out why mysqltuner is reporting such dangerous amounts of maximum possible memory usage 63.2G (404.10% of installed RAM).

    I'm an amature at this and could do with some help optimising this. Here's the output from mysql tuner.

     >>  MySQLTuner 1.9.6
             * 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.28
    [OK] Operating on 64-bit architecture

    -------- Log file Recommendations ------------------------------------------------------------------
    [OK] Log file /var/lib/mysql/connected.err exists
    [--] Log file: /var/lib/mysql/connected.err(17M)
    [OK] Log file /var/lib/mysql/connected.err is not empty
    [OK] Log file /var/lib/mysql/connected.err is smaller than 32 Mb
    [OK] Log file /var/lib/mysql/connected.err is readable.
    [!!] /var/lib/mysql/connected.err contains 636 warning(s).
    [!!] /var/lib/mysql/connected.err contains 15663 error(s).
    [--] 2 start(s) detected in /var/lib/mysql/connected.err
    [--] 1) 2022-04-22T08:40:59.695936Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.28' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server - GPL.
    [--] 2) 2022-04-22T08:28:53.431098Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.28' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server - GPL.
    [--] 2 shutdown(s) detected in /var/lib/mysql/connected.err
    [--] 1) 2022-04-22T08:40:56.716742Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.28) MySQL Community Server - GPL.
    [--] 2) 2022-04-22T08:24:09.905248Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.28) MySQL Community Server - GPL.

    -------- Storage Engine Statistics -----------------------------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
    [--] Data in InnoDB tables: 8.7G (Tables: 608)
    [--] Data in MyISAM tables: 1.2G (Tables: 498)
    [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 ------------------------------------------------------------------
    [--] Skipped due to unsupported feature for MySQL 8

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

    -------- Performance Metrics -----------------------------------------------------------------------
    [--] Up for: 1d 8h 11m 45s (19M q [168.833 qps], 83K conn, TX: 108G, RX: 7G)
    [--] Reads / Writes: 73% / 27%
    [--] Binary logging is disabled
    [--] Physical Memory : 15.6G
    [--] Max MySQL memory : 63.2G
    [--] Other process memory: 0B
    [--] Total buffers: 284.2M global + 257.9M per thread (250 max threads)
    [--] P_S Max memory usage: 72B
    [--] Galera GCache Max memory usage: 0B
    [OK] Maximum reached memory usage: 5.1G (32.35% of installed RAM)
    [!!] Maximum possible memory usage: 63.2G (404.10% of installed RAM)
    [!!] Overall possible memory usage with other process exceeded memory
    [OK] Slow queries: 0% (0/19M)
    [OK] Highest usage of available connections: 7% (19/250)
    [OK] Aborted connections: 0.02% (17/83627)
    [--] CPanel and Flex system skip-name-resolve should be on
    [--] Query cache have been removed in MySQL 8
    [OK] Sorts requiring temporary tables: 3% (115K temp sorts / 3M sorts)
    [OK] No joins without indexes
    [OK] Temporary tables created on disk: 0% (14 on disk / 1M total)
    [OK] Thread cache hit rate: 99% (50 created / 83K connections)
    [OK] Table cache hit rate: 99% (24M hits / 24M requests)
    [OK] table_definition_cache(2000) is upper than number of tables(1432)
    [OK] Open file limit used: 5% (2K/40K)
    [OK] Table locks acquired immediately: 99% (8M immediate / 8M locks)

    -------- Performance schema ------------------------------------------------------------------------
    [--] Performance_schema is activated.
    [--] 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
    [!!] InnoDB buffer pool / data size: 128.0M/8.7G
    [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (43.75 %): 28.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.52% (1833867572 hits/ 1842735982 total)
    [!!] InnoDB Write Log efficiency: 73.13% (23104852 hits/ 31593792 total)
    [OK] InnoDB log waits: 0.00% (0 waits / 8488940 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/lib/mysql/connected.err file
    Check error line(s) in /var/lib/mysql/connected.err file
    Reduce your overall MySQL memory footprint for system stability
    Dedicate this server to your database for highest performance.
    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 ***
    key_buffer_size=0
    innodb_buffer_pool_size (>= 8.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@connected sbl]#

    Updated with a longer sample time

    And here's my cnf config

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

    [mysqld]
    disable-log-bin=1
    default-authentication-plugin=mysql_native_password
    performance-schema=1

    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 the leading "# " to disable binary logging

    Binary logging captures changes between backups and is enabled by

    default. It's default setting is log_bin=binlog

    disable_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

    Remove leading # to revert to previous value for default_authentication_plugin,

    this will increase compatibility with older clients. For background, see:

    https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin

    default-authentication-plugin=mysql_native_password

    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock

    log-error="/var/lib/mysql/connected.err"
    pid-file=/var/run/mysqld/mysqld.pid
    max_allowed_packet=268435456
    open_files_limit=400000
    mysqlx=0
    skip-name-resolve=1
    performance_schema=ON
    key_buffer_size=134217728
    innodb_log_file_size=30331648
    innodb_buffer_pool_size=134217728
    slow_query_log=0
    interactive_timeout=14400
    max_connections=250
    innodb_log_buffer_size=12777216
    innodb_buffer_pool_instances=3
    wait_timeout=14400

    Any ideas on how i can improve this?



  • The is no 'perfect' formula to estimate max memory usage by MySQL. There are many pessimistic formulas floating around; they all assume max_connections is reached and each connection is simultaneously doing something very memory-intensive. That is not likely to happen. Ever.

    SHOW GLOBAL STATUS LIKE 'Max_used_connections'; -- that gives you a reasonable estimate of what max_connections should be set to. (But go higher than that, just in case.)

    If you are not using MyISAM, lower key_buffer_size from the wasteful 128M to only 40M.

    On the other hand, if you are using InnoDB (which you should be), then the setting for innodb_buffer_pool_size of only 128M; change it to about 70% of available RAM (after accounting for other apps.) That is likely to help performance significantly.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2