[MDEV-30808] 5GB Load Data Insert to Memory table Loses 5Gb each run Created: 2023-03-07  Updated: 2023-10-23

Status: Open
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.5
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: Larry David Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None
Environment:

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



 Comments   
Comment by Sergei Golubchik [ 2023-03-30 ]

That the server uses ~5GB more memory after you load 5GB file into the memory — it's expected. May be a bit more, because of indexes.

Second load will need 5GB more memory, because you load the new file first and only then drop the old table, so you'll have 10GB in memory at some point. But the memory usage shouldn't grow beyond that, in my opinion.

this MySQLTuner output — is it after the first load? Please, run it after you've repeated the load few times and the used memory is more than 11GB

Comment by Larry David [ 2023-03-30 ]

Thank you for your interest.

The last restart was two days ago. 4 loads have happened since then. Here's the memory usage

cat /proc/meminfo
 
MemTotal:       32815788 kB
 
MemFree:         5614720 kB
 
MemAvailable:   11694284 kB
 
Buffers:           77860 kB
 
Cached:          7240576 kB
 
SwapCached:        21904 kB
 
Active:         12327584 kB
 
Inactive:       13566524 kB
 
Active(anon):    6459992 kB
 
Inactive(anon): 13027084 kB
 
Active(file):    5867592 kB
 
Inactive(file):   539440 kB
 
Unevictable:      872096 kB
 
Mlocked:               0 kB
 
SwapTotal:      33554428 kB
 
SwapFree:       27600636 kB
 
Dirty:               212 kB
 
Writeback:             0 kB
 
AnonPages:      19435472 kB
 
Mapped:            79684 kB
 
Shmem:             39300 kB
 
KReclaimable:      81668 kB
 
Slab:             181260 kB
 
SReclaimable:      81668 kB
 
SUnreclaim:        99592 kB
 
KernelStack:        5472 kB
 
PageTables:        64444 kB
 
NFS_Unstable:          0 kB
 
Bounce:                0 kB
 
WritebackTmp:          0 kB
 
CommitLimit:    49962320 kB
 
Committed_AS:    1816380 kB
 
VmallocTotal:   34359738367 kB
 
VmallocUsed:           0 kB
 
VmallocChunk:          0 kB
 
Percpu:            14016 kB
 
HardwareCorrupted:     0 kB
 
AnonHugePages:  18270208 kB
 
ShmemHugePages:        0 kB
 
ShmemPmdMapped:        0 kB
 
FileHugePages:         0 kB
 
FilePmdMapped:         0 kB
 
HugePages_Total:       0
 
HugePages_Free:        0
 
HugePages_Rsvd:        0
 
HugePages_Surp:        0
 
Hugepagesize:       2048 kB
 
Hugetlb:               0 kB
 
DirectMap4k:    11570192 kB
 
DirectMap2M:    21882880 kB
 
DirectMap1G:           0 kB


free
 
              total        used        free      shared  buff/cache   available
 
Mem:       32815788    19827272     5400744       37852     7587772    11482588
 
Swap:      33554428     5953792    27600636

and here's the latest MySQLTuner output

 >>  MySQLTuner 2.0.9
 
         * Jean-Marie Renouard <jmrenouard@gmail.com>
 
         * 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] Currently running supported MySQL version 10.5.19-MariaDB-log
 
[OK] Operating on 64-bit architecture
 
 
 
-------- Log file Recommendations ------------------------------------------------------------------
 
[OK] Log file /secondary/var/log/mysql/mysql-error.log exists
 
[--] Log file: /secondary/var/log/mysql/mysql-error.log (808K)
 
[OK] Log file /secondary/var/log/mysql/mysql-error.log is not empty
 
[OK] Log file /secondary/var/log/mysql/mysql-error.log is smaller than 32 Mb
 
[OK] Log file /secondary/var/log/mysql/mysql-error.log is readable.
 
[!!] /secondary/var/log/mysql/mysql-error.log contains 394 warning(s).
 
[!!] /secondary/var/log/mysql/mysql-error.log contains 206 error(s).
 
[--] 119 start(s) detected in /secondary/var/log/mysql/mysql-error.log
 
[--] 1) 2023-03-28  9:14:53 0 [Note] /usr/sbin/mariadbd: ready for connections.
 
[--] 2) 2023-03-24 10:21:42 0 [Note] /usr/sbin/mariadbd: ready for connections.
 
[--] 3) 2023-03-20  9:38:56 0 [Note] /usr/sbin/mariadbd: ready for connections.
 
[--] 4) 2023-03-18  0:10:30 0 [Note] /usr/sbin/mariadbd: ready for connections.
 
[--] 5) 2023-03-13 11:01:56 0 [Note] /usr/sbin/mariadbd: ready for connections.
 
[--] 6) 2023-03-09 22:15:23 0 [Note] /usr/sbin/mariadbd: ready for connections.
 
[--] 7) 2023-03-05 10:02:46 0 [Note] /usr/sbin/mariadbd: ready for connections.
 
[--] 8) 2023-03-04  9:41:48 0 [Note] /usr/sbin/mariadbd: ready for connections.
 
[--] 9) 2023-03-02 22:15:34 0 [Note] /usr/sbin/mariadbd: ready for connections.
 
[--] 10) 2023-02-27 22:43:38 0 [Note] /usr/sbin/mariadbd: ready for connections.
 
[--] 101 shutdown(s) detected in /secondary/var/log/mysql/mysql-error.log
 
[--] 1) 2023-03-28  9:14:50 0 [Note] /usr/sbin/mariadbd: Shutdown complete
 
[--] 2) 2023-03-24 10:21:41 0 [Note] /usr/sbin/mariadbd: Shutdown complete
 
[--] 3) 2023-03-20  9:38:54 0 [Note] /usr/sbin/mariadbd: Shutdown complete
 
[--] 4) 2023-03-18  0:10:28 0 [Note] /usr/sbin/mariadbd: Shutdown complete
 
[--] 5) 2023-03-13 11:01:54 0 [Note] /usr/sbin/mariadbd: Shutdown complete
 
[--] 6) 2023-03-09 22:15:21 0 [Note] /usr/sbin/mariadbd: Shutdown complete
 
[--] 7) 2023-03-05 10:01:34 0 [Note] /usr/sbin/mariadbd: Shutdown complete
 
[--] 8) 2023-03-04  9:41:45 0 [Note] /usr/sbin/mariadbd: Shutdown complete
 
[--] 9) 2023-03-02 22:15:31 0 [Note] /usr/sbin/mariadbd: Shutdown complete
 
[--] 10) 2023-02-27 22:43:35 0 [Note] /usr/sbin/mariadbd: Shutdown complete
 
 
 
-------- Storage Engine Statistics -----------------------------------------------------------------
 
[--] Status: +Aria +CSV -InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
 
[--] Data in MyISAM tables: 6.5M (Tables: 1)
 
[--] Data in Aria tables: 1.3G (Tables: 155)
 
[--] 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 9h 55m 45s (29M q [142.110 qps], 289K conn, TX: 575G, RX: 9G)
 
[--] Reads / Writes: 84% / 16%
 
[--] Binary logging is disabled
 
[--] Physical Memory     : 31.3G
 
[--] Max MySQL memory    : 6.6G
 
[--] Other process memory: 0B
 
[--] Total buffers: 4.3G global + 36.8M per thread (64 max threads)
 
[--] Performance_schema Max memory usage: 0B
 
[--] Galera GCache Max memory usage: 0B
 
[OK] Maximum reached memory usage: 4.9G (15.63% of installed RAM)
 
[OK] Maximum possible memory usage: 6.6G (21.14% of installed RAM)
 
[OK] Overall possible memory usage with other process is compatible with memory available
 
[OK] Slow queries: 0% (7/29M)
 
[OK] Highest usage of available connections: 25% (16/64)
 
[OK] Aborted connections: 0.00% (0/289192)
 
[--] 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% (5K on disk / 1M total)
 
[OK] Thread cache hit rate: 99% (16 created / 289K connections)
 
[OK] Table cache hit rate: 99% (26M hits / 27M requests)
 
[OK] table_definition_cache (512) is greater than number of tables (351)
 
[OK] Open file limit used: 2% (378/16K)
 
[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: 58.9% (2.4M used / 4.0M cache)
 
[OK] Key buffer size / total MyISAM indexes: 4.0M/3.2M
 
[OK] Read Key buffer hit rate: 99.0% (2M cached / 23K reads)
 
[!!] Write Key buffer hit rate: 11.4% (3K cached / 354 writes)
 
 
 
-------- 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: 320.0M/240.7M
 
[OK] Aria pagecache hit rate: 99.9% (2B cached / 4M 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
 
 
 
-------- Recommendations ---------------------------------------------------------------------------
 
General recommendations:
 
    Check warning line(s) in /secondary/var/log/mysql/mysql-error.log file
 
    Check error line(s) in /secondary/var/log/mysql/mysql-error.log file
 
    Performance schema should be activated for better diagnostics
 
    Consider installing Sys schema from https://github.com/mysql/mysql-sys for MySQL
 
Variables to adjust:
 
    performance_schema=ON
 
    key_buffer_size (~ 2M)

I have changed some of the buffer parameters, reducing all those in the myisamchk section to 128M, and the bulk_insert_buffer_size to 256M. The speed of inserts is still fine because the SSD is a 970PRO

Comment by Larry David [ 2023-03-31 ]

Here's today's free after two more loads

free
 
              total        used        free      shared  buff/cache   available
 
Mem:       32815788    22925732     7797132       33504     2092924     8472776
 
Swap:      33554428    12736000    20818428

Since I changed some of the buffer settings the total Mem: is not increasing as much as it used to but the swap now is. And it will eventually run out so I have to perform a restart.

I have just performed a service mariadb restart. Here the the figures now:

free
 
              total        used        free      shared  buff/cache   available
 
Mem:       32815788     7051360    23168092       34040     2596336    24358848
 
Swap:       33554428     76544     33477884

That restart has cleared 15Gb of Mem and 12Gb of Swap.

Comment by Larry David [ 2023-10-23 ]

I have tested this on a brand new rig and get the same issue.

MariaDB version 11.0.3
Rocky Linux 9.2
Ram 64GB

As this has happened on various versions of MariaDB, various versions of Linux, and with different RAM sizes then I appreciate this issue may be due to a procedure that I am running. I will try recoding the load data into memory table procedure.

Generated at Thu Feb 08 10:19:03 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.