Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.5(EOL)
-
None
-
None
-
None
-
Centos 7, Rocky 8, 32GB Ram
Description
I am not sure if this is a bug or a procedure error on my behalf. I have been aware of this issue for a while now and I believe it is common to all 10.x versions.
I populate a Memory Table with ~5GB from a csv file. When ever this is performed the server memory loses 5GB. The 5Gb is accumulatively lost on each subsequent run and is never returned until I restart mariadb
1. Rebooted server to ensure clean memory
2. Run populate routine. Memory use is now around 6GB
3. A few hours later run populate routine. Memory use is now 11GB
4. A few hours later run populate routine. Memory use is now 16GB
5. A few hours later run populate routine. Memory use is now 21GB
6, If I run it again the swap becomes in use. I now get 26GB in use and 5GB in swap
7. Run it again the swap increases. I now get 26GB in use and 10GB in swap
8. I restart mariadb and I now get 6GB in use again. All figures noted from top command.
Here is the procedure for populating the memory table called s_runs
1. create a new table new_s_runs
2. ALTER TABLE new_s_runs ENGINE = MEMORY
3. ALTER TABLE new_runs add index ..... (approx 40 fields)
4. DISABLE KEYS
5. LOAD DATA INFILE '/secondary/temp/s_runs_upd.txt' INTO TABLE new_s_runs FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n'
6. ALTER TABLE new_s_runs ENABLE KEYS
7. DROP TABLE IF EXISTS s_runs_tmp
8. CREATE TABLE if not exists s_runs LIKE new_s_runs
9. RENAME TABLE s_runs TO s_runs_tmp, new_s_runs TO s_runs
10. DROP TABLE IF EXISTS s_runs_tmp
This is a live server and I find this is the fastest / safest way to repopulate the table (which is done twice a day).
But I get the memory shrinking and I have to restart mariadb every 2 or 3 days.
The problem only happens when I use the 4GB CSV file. Prior to this I was using a 2GB file and the memory never ran out. Only since using 4GB do I get this daily degradation in available memory.
I guess I could bump the RAM to 64GB but will the problem still exist and then I have to restart mariadb every 4 to 6 days?
Or is there something wrong with my table populating procedure? Or possibly the my.cnf settings?
I have the same problem on a Rocky 8 and a Centos 7 server. Both are running 10.5.19 but this has been happening with earlier versions
------------------------------------------
|
[mysql]
|
# CLIENT #
|
port = 3306
|
socket = /var/lib/mysql/mysql.sock
|
|
|
no_auto_rehash
|
|
|
[mysqld]
|
bind-address = 0.0.0.0
|
|
|
port = 3306
|
|
|
# GENERAL #
|
user = mysql
|
|
|
default-storage-engine = aria
|
aria_log_file_size = 536870912
|
|
|
socket = /var/lib/mysql/mysql.sock
|
pid-file = /var/lib/mysql/mysql.pid
|
|
|
#defatults to off
|
skip-external-locking
|
|
|
skip-innodb
|
skip-performance_schema
|
skip-networking
|
|
|
tmpdir = /tmp
|
|
|
optimizer_max_sel_arg_weight = 12000
|
|
|
performance_schema = OFF
|
|
|
delay_key_write = ALL
|
concurrent_insert = ALWAYS
|
wait_timeout = 60
|
interactive_timeout = 60
|
|
|
join_buffer_size = 512K
|
sort_buffer_size = 1M
|
read_buffer_size = 1M
|
read_rnd_buffer_size = 2M
|
|
|
bulk_insert_buffer_size = 256M
|
|
|
# MyISAM #
|
key-buffer-size = 1M
|
myisam-recover-options = FORCE,BACKUP
|
|
|
# SAFETY #
|
max-allowed-packet = 32M
|
max-connect-errors = 1000000
|
skip-name-resolve = 1
|
|
|
#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
|
sql-mode = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
|
sysdate-is-now = 1
|
|
|
# DATA STORAGE #
|
datadir = /data/mysql/
|
|
|
# CACHES AND LIMITS #
|
tmp-table-size = 4G
|
|
|
#high for MEM tables
|
max-heap-table-size = 8G
|
query-cache-type = 0
|
query-cache-size = 0
|
max-connections = 50
|
max-user-connections = 50
|
|
|
thread-cache-size = 24
|
|
|
open-files-limit = 4096
|
table-definition-cache = 512
|
table-open-cache = 2049
|
table_open_cache_instances = 2
|
|
|
# LOGGING #
|
log-error = /secondary/var/log/mysql/mysql-error.log
|
log-queries-not-using-indexes = 0
|
slow-query-log = 1
|
long_query_time = 10
|
slow-query-log-file = /secondary/var/log/mysql/mysql-slow.log
|
log_warnings = 2
|
|
|
myisam_sort_buffer_size = 32M
|
|
|
aria_pagecache_buffer_size = 512M
|
aria_repair_threads = 6
|
aria_sort_buffer_size = 256M
|
aria_pagecache_age_threshold = 200
|
|
|
[myisamchk]
|
key_buffer_size = 256M
|
sort_buffer_size = 256M
|
read_buffer_size = 256M
|
write_buffer_size = 256M
|
|
|
[mysqldump]
|
quick
|
------------------------------------------
|
------------------------------------------
|
-------- Storage Engine Statistics -----------------------------------------------------------------
|
[--] Status: +Aria +CSV -InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
|
[--] Data in Aria tables: 1.3G (Tables: 156)
|
[--] Data in MEMORY tables: 5.3G (Tables: 2)
|
[OK] Total fragmented tables: 0
|
|
|
-------- Analysis Performance Metrics --------------------------------------------------------------
|
[OK] No stat updates during querying INFORMATION_SCHEMA.
|
|
|
-------- Views Metrics -----------------------------------------------------------------------------
|
|
|
-------- Triggers Metrics --------------------------------------------------------------------------
|
|
|
-------- Routines Metrics --------------------------------------------------------------------------
|
|
|
-------- 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: 2d 11h 42m 33s (29M q [137.529 qps], 282K conn, TX: 560G, RX: 9G)
|
[--] Reads / Writes: 84% / 16%
|
[--] Binary logging is disabled
|
[--] Physical Memory : 31.3G
|
[--] Max MySQL memory : 6.3G
|
[--] Other process memory: 0B
|
[--] Total buffers: 4.5G global + 36.8M per thread (50 max threads)
|
[--] Performance_schema Max memory usage: 0B
|
[--] Galera GCache Max memory usage: 0B
|
[OK] Maximum reached memory usage: 5.1G (16.22% of installed RAM)
|
[OK] Maximum possible memory usage: 6.3G (20.12% of installed RAM)
|
[OK] Overall possible memory usage with other process is compatible with memory available
|
[OK] Slow queries: 0% (6/29M)
|
[OK] Highest usage of available connections: 32% (16/50)
|
[OK] Aborted connections: 0.00% (0/282084)
|
[--] Skipped name resolution test due to skip_networking=ON in system variables.
|
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
|
[OK] Sorts requiring temporary tables: 0% (2K temp sorts / 2M sorts)
|
[OK] No joins without indexes
|
[OK] Temporary tables created on disk: 0% (6K on disk / 1M total)
|
[OK] Thread cache hit rate: 99% (16 created / 282K connections)
|
[OK] Table cache hit rate: 99% (26M hits / 26M requests)
|
[OK] table_definition_cache (512) is greater than number of tables (351)
|
[OK] Open file limit used: 4% (361/8K)
|
[OK] Table locks acquired immediately: 99% (26M immediate / 26M locks)
|
|
|
-------- Performance schema ------------------------------------------------------------------------
|
[!!] Performance_schema should be activated.
|
[--] Sys schema is not installed.
|
|
|
-------- ThreadPool Metrics ------------------------------------------------------------------------
|
[--] ThreadPool stat is disabled.
|
|
|
-------- MyISAM Metrics ----------------------------------------------------------------------------
|
[!!] Key buffer used: 18.9% (194.0K used / 1.0M cache)
|
[OK] Key buffer size / total MyISAM indexes: 1.0M/4.0K
|
|
|
-------- InnoDB Metrics ----------------------------------------------------------------------------
|
[--] InnoDB is disabled.
|
[--] InnoDB Storage engine is disabled. Aria is the default storage engine
|
|
|
-------- Aria Metrics ------------------------------------------------------------------------------
|
[--] Aria Storage Engine is enabled.
|
[OK] Aria pagecache size / total Aria indexes: 512.0M/224.1M
|
[OK] Aria pagecache hit rate: 99.9% (2B cached / 3M 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: MIXED
|
[--] XA support enabled: ON
|
[--] Semi synchronous replication Master: OFF
|
[--] Semi synchronous replication Slave: OFF
|
[--] This is a standalone server
|
---------------------------------------------------
|