I am a bit unsure why we are seeing these memory numbers while the cpu usage is actually very low.
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in MyISAM tables: 11.1M (Tables: 6)
[--] Data in InnoDB tables: 15.1G (Tables: 1033)
[!!] Total fragmented tables: 2
-------- 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 are 620 basic passwords in the list.
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 5s (1K q [255.800 qps], 31 conn, TX: 15M, RX: 252K)
[--] Reads / Writes: 92% / 8%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory : 125.4G
[--] Max MySQL memory : 109.0G
[--] Other process memory: 0B
[--] Total buffers: 30.7G global + 266.7M per thread (300 max threads)
[--] P_S Max memory usage: 129M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 31.4G (25.01% of installed RAM)
[!!] Maximum possible memory usage: 109.0G (86.87% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/1K)
[OK] Highest usage of available connections: 0% (2/300)
[!!] Aborted connections: 3.23% (1/31)
[!!] name resolution is active : a reverse name resolution is made for each new connection and 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 / 109 sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 0% (0 on disk / 70 total)
[OK] Thread cache hit rate: 93% (2 created / 31 connections)
[OK] Table cache hit rate: 92% (72 open / 78 opened)
[OK] table_definition_cache(8000) is upper than number of tables(1313)
[OK] Open file limit used: 0% (25/158K)
[OK] Table locks acquired immediately: 100% (11 immediate / 11 locks)
[OK] Binlog cache memory access: 100.00% (89 Memory / 89 Total)
-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 129.9M
[--] Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 16 thread(s).
[--] Using default value is good enough for your version (10.3.22-MariaDB-log)
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.3% (6M used / 33M cache)
[OK] Key buffer size / total MyISAM indexes: 32.0M/3.6M
[!!] Read Key buffer hit rate: 50.0% (8 cached / 4 reads)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 10
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 30.0G/15.1G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 3.0G * 2/30.0G should be equal to 25%
[OK] InnoDB buffer pool instances: 30
[--] Number of InnoDB Buffer Pool Chunk : 240 for 30 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: 91.93% (153107 hits/ 166539 total)
[!!] InnoDB Write Log efficiency: 57.72% (172 hits/ 298 total)
[OK] InnoDB log waits: 0.00% (0 waits / 126 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
-------- 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: MIXED
[--] XA support enabled: ON
[--] Semi synchronous replication Master: OFF
[--] Semi synchronous replication Slave: OFF
[--] This is a standalone server
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
/var/log/mysqld.log is > 32Mb, you should analyze why or implement a rotation log strategy such as logrotate!
Control warning line(s) into /var/log/mysqld.log file
Control error line(s) into /var/log/mysqld.log file
Run OPTIMIZE TABLE to defragment tables for better performance
Total freed space after theses OPTIMIZE TABLE : 1262 Mb
MySQL was started within the last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Reduce or eliminate unclosed connections and network issues
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
[mysql]
# CLIENT #
port =3306
socket ="/var/lib/mysql/mysql.sock"
[mysqld]
bind-address = 127.0.0.1
tmpdir = "/dev/shm"
interactive_timeout = 3600
connect_timeout = 3600
performance_schema = on
# GENERAL #
user = mysql
default-storage-engine = InnoDB
socket = "/var/lib/mysql/mysql.sock"
pid-file = "hidden"
local-infile = 0
# MyISAM #
key-buffer-size = 32M
myisam-recover-options = FORCE,BACKUP
# SAFETY #
max-allowed-packet = 256M #was 32M
max-connect-errors = 1000000
#sql-mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
sysdate-is-now = 1
# DATA STORAGE #
datadir = "/var/lib/mysql"
# BINARY LOGGING #
log-bin = "/var/lib/mysql/mysql-bin"
expire-logs-days = 7
sync-binlog = 0
binlog_format = mixed
binlog_cache_size = 64M #Default = 32M, increase due to faults
# CACHES AND LIMITS #
tmp-table-size = 64M #was 32M
max-heap-table-size = 64M #was 32M
max-connections = 300 #was150
thread-cache-size = 75 #was 50
table-definition-cache = 8000
table-open-cache = 79230 #was 22000
open_files_limit = 98460 #was 65000
# Optimization by #
query_cache_type = 0
query_cache_size = 0
query_cache_limit = 0
join_buffer_size = 8M
# INNODB #
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 3G
innodb-flush-log-at-trx-commit = 2
innodb-file-per-table = 1
innodb-buffer-pool-size = 30G
innodb-thread_concurrency = 10
concurrent_insert = ALWAYS
innodb_log_buffer_size = 512M
innodb_buffer_pool_instances = 30
innodb_data_file_path = "ibdata1:10M:autoextend:max:10G"
innodb_flush_log_at_trx_commit = 2
innodb_stats_on_metadata = 0
innodb_lru_scan_depth = 100
# LOGGING #
log_queries_not_using_indexes = 0
slow_query_log = 1
slow_query_log_file = /var/log/slow_mysql.log
long_query_time = 1
log-error = /var/log/mysqld.log
[mysqldump]
max_allowed_packet =256M
quick
quote_names#
The server specs are:
128GB memory 16 core CPU 960GB NVMe disk RAID10
I would have normally have expected the server to use far more CPU and less memory that what we are currently seeing.
Anyone have an idea as to what I am missing?
