MySQL consuming too much memory
0
Version : mysql Ver 15.1 Distrib 10.3.9-MariaDB
OS : CentOS Linux release 7.5.1804 (Core)
Specification : 384GB RAM
Context :
I am using this server only for database (with no other processes running). Currently, I am doing mass insertions (migrating) into the this server (potentially few terabytes data).
Despite setting my buffer pool to only 240G, somehow the total used memory shown by top or free -m is almost close to 95% (370GB+) of my total physical RAM (384GB).
However, the strange thing is the total memory used by mysqld is shown as 240GB+ (via top command).
To further confirm that the 370GB+ RAM was used by mysqld and not some hidden processes, I restarted MariaDB via service mariadb restart. The moment it restarted successfully, I verified the memory usage via free -m and saw almost 340GB+ free (under available column).
Hence, am I missing out in some settings in my configuration file or is that how mysqld fundamentally work? Or could it also be there are memory leaks, which resulted in memory not being freed properly in mysqld?
free --mega (when database has been ran for several hours)
# free --mega
total used free shared buff/cache available
Mem: 386689 376532 8120 8 2036 7353
Swap: 4095 1189 2906
/etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
#[client-server]
[mysqld]
datadir=/zfs2/db/mysql/
user=mysql
socket=/zfs2/db/mysql/mysql.sock
innodb_file_per_table=1
innodb_buffer_pool_size=240G
innodb_log_file_size=768M
max_allowed_packet=128M
max_connections=50000
innodb_thread_concurrency=48
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_use_native_aio=0
innodb_flush_log_at_trx_commit=2
innodb_doublewrite=0
open_files_limit=50000
skip-name-resolve
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
/etc/my.cnf (END)
top command
top - 17:08:25 up 2 days, 3:32, 1 user, load average: 5.21, 4.61, 4.77
Tasks: 387 total, 2 running, 385 sleeping, 0 stopped, 0 zombie
%Cpu(s): 6.8 us, 3.7 sy, 0.0 ni, 82.4 id, 7.0 wa, 0.0 hi, 0.2 si, 0.0 st
KiB Mem : 39596972+total, 7963856 free, 38592006+used, 2085784 buff/cache
KiB Swap: 4194300 total, 2978664 free, 1215636 used. 7179308 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1230 mysql 20 0 271.8g 258.4g 14248 S 188.9 68.4 2391:28 mysqld
10260 root 20 0 162248 2488 1528 R 16.7 0.0 0:00.04 top
....
....
SHOW GLOBAL STATUS
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 538573, free list len 270263, seg size 808837, 6384375 merges
merged operations:
insert 228438676, delete mark 406242, delete 239439
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 63742607, node heap has 33386 buffer(s)
Hash table size 63742607, node heap has 0 buffer(s)
Hash table size 63742607, node heap has 106 buffer(s)
Hash table size 63742607, node heap has 552581 buffer(s)
Hash table size 63742607, node heap has 33558 buffer(s)
Hash table size 63742607, node heap has 1 buffer(s)
Hash table size 63742607, node heap has 81 buffer(s)
Hash table size 63742607, node heap has 499411 buffer(s)
38842.73 hash searches/s, 66931.80 non-hash searches/s
---
LOG
---
Log sequence number 6070350783605
Log flushed up to 6070347109837
Pages flushed up to 6069248723606
Last checkpoint at 6069205734998
0 pending log flushes, 0 pending chkp writes
1463927 log i/o's done, 20.87 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 263771389952
Dictionary memory allocated 5552286
Buffer pool size 15726960
Free buffers 7641
Database pages 14600195
Old database pages 5389362
Modified db pages 108091
Percent of dirty pages(LRU & free pages): 0.740
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 7116675, not young 154182163
146.02 youngs/s, 751.54 non-youngs/s
Pages read 13002091, created 20524731, written 50122771
103.42 reads/s, 316.99 creates/s, 704.93 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 2 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 14600195, unzip_LRU len: 0
I/O sum[289016]:cur[24], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 1966080
Free buffers 978
Database pages 1824943
Old database pages 673640
Modified db pages 15664
Percent of dirty pages(LRU & free pages): 0.858
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 886318, not young 21558561
19.48 youngs/s, 59.30 non-youngs/s
Pages read 1627657, created 2567248, written 7270228
12.76 reads/s, 36.39 creates/s, 112.55 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1824943, unzip_LRU len: 0
I/O sum[36127]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 1965840
Free buffers 989
Database pages 1825185
Old database pages 673729
Modified db pages 12978
Percent of dirty pages(LRU & free pages): 0.711
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 888506, not young 17872488
18.83 youngs/s, 87.37 non-youngs/s
Pages read 1620960, created 2551959, written 6091321
13.00 reads/s, 40.28 creates/s, 91.33 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 2 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1825185, unzip_LRU len: 0
I/O sum[36127]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 1965840
Free buffers 979
Database pages 1824947
Old database pages 673642
Modified db pages 13895
Percent of dirty pages(LRU & free pages): 0.761
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 886797, not young 18128418
17.33 youngs/s, 151.48 non-youngs/s
Pages read 1618497, created 2564253, written 6108565
12.46 reads/s, 36.33 creates/s, 98.29 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 3 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1824947, unzip_LRU len: 0
I/O sum[36127]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 1965840
Free buffers 999
Database pages 1825047
Old database pages 673678
Modified db pages 12517
Percent of dirty pages(LRU & free pages): 0.685
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 888765, not young 20029901
17.59 youngs/s, 59.42 non-youngs/s
Pages read 1628730, created 2569645, written 6152662
13.06 reads/s, 42.35 creates/s, 76.28 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 1 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1825047, unzip_LRU len: 0
I/O sum[36127]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 1965840
Free buffers 980
Database pages 1824878
Old database pages 673616
Modified db pages 13641
Percent of dirty pages(LRU & free pages): 0.747
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 878757, not young 17787806
18.41 youngs/s, 104.46 non-youngs/s
Pages read 1613388, created 2567887, written 6120053
13.30 reads/s, 46.02 creates/s, 86.76 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 2 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1824878, unzip_LRU len: 0
I/O sum[36127]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 1965840
Free buffers 877
Database pages 1825227
Old database pages 673745
Modified db pages 13217
Percent of dirty pages(LRU & free pages): 0.724
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 899041, not young 21347971
17.78 youngs/s, 104.68 non-youngs/s
Pages read 1640617, created 2569267, written 6121197
13.65 reads/s, 39.11 creates/s, 70.81 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 2 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1825227, unzip_LRU len: 0
I/O sum[36127]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 1965840
Free buffers 921
Database pages 1825177
Old database pages 673726
Modified db pages 13437
Percent of dirty pages(LRU & free pages): 0.736
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 895011, not young 19236578
17.28 youngs/s, 95.22 non-youngs/s
Pages read 1630603, created 2572247, written 6148992
11.98 reads/s, 47.04 creates/s, 86.09 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 2 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1825177, unzip_LRU len: 0
I/O sum[36127]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 1965840
Free buffers 918
Database pages 1824791
Old database pages 673586
Modified db pages 12742
Percent of dirty pages(LRU & free pages): 0.698
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 893480, not young 18220440
19.31 youngs/s, 89.61 non-youngs/s
Pages read 1621639, created 2562225, written 6109753
13.22 reads/s, 29.48 creates/s, 82.81 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 3 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1824791, unzip_LRU len: 0
I/O sum[36127]:cur[3], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=1230, Main thread ID=139849981024000, state: making checkpoint
Number of rows inserted 1244250277, updated 5901, deleted 187006, read 1627791678
17677.60 inserts/s, 0.13 updates/s, 0.00 deletes/s, 10865.63 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
mysql mariadb memory
asked Oct 26 '18 at 9:25
TestTubeRobotTestTubeRobot
11
bumped to the homepage by Community♦ 17 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
0
Version : mysql Ver 15.1 Distrib 10.3.9-MariaDB
OS : CentOS Linux release 7.5.1804 (Core)
Specification : 384GB RAM
Context :
I am using this server only for database (with no other processes running). Currently, I am doing mass insertions (migrating) into the this server (potentially few terabytes data).
Despite setting my buffer pool to only 240G, somehow the total used memory shown by top or free -m is almost close to 95% (370GB+) of my total physical RAM (384GB).
However, the strange thing is the total memory used by mysqld is shown as 240GB+ (via top command).
To further confirm that the 370GB+ RAM was used by mysqld and not some hidden processes, I restarted MariaDB via service mariadb restart. The moment it restarted successfully, I verified the memory usage via free -m and saw almost 340GB+ free (under available column).
Hence, am I missing out in some settings in my configuration file or is that how mysqld fundamentally work? Or could it also be there are memory leaks, which resulted in memory not being freed properly in mysqld?
free --mega (when database has been ran for several hours)
# free --mega
total used free shared buff/cache available
Mem: 386689 376532 8120 8 2036 7353
Swap: 4095 1189 2906
/etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
#[client-server]
[mysqld]
datadir=/zfs2/db/mysql/
user=mysql
socket=/zfs2/db/mysql/mysql.sock
innodb_file_per_table=1
innodb_buffer_pool_size=240G
innodb_log_file_size=768M
max_allowed_packet=128M
max_connections=50000
innodb_thread_concurrency=48
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_use_native_aio=0
innodb_flush_log_at_trx_commit=2
innodb_doublewrite=0
open_files_limit=50000
skip-name-resolve
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
/etc/my.cnf (END)
top command
top - 17:08:25 up 2 days, 3:32, 1 user, load average: 5.21, 4.61, 4.77
Tasks: 387 total, 2 running, 385 sleeping, 0 stopped, 0 zombie
%Cpu(s): 6.8 us, 3.7 sy, 0.0 ni, 82.4 id, 7.0 wa, 0.0 hi, 0.2 si, 0.0 st
KiB Mem : 39596972+total, 7963856 free, 38592006+used, 2085784 buff/cache
KiB Swap: 4194300 total, 2978664 free, 1215636 used. 7179308 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1230 mysql 20 0 271.8g 258.4g 14248 S 188.9 68.4 2391:28 mysqld
10260 root 20 0 162248 2488 1528 R 16.7 0.0 0:00.04 top
....
....
SHOW GLOBAL STATUS
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 538573, free list len 270263, seg size 808837, 6384375 merges
merged operations:
insert 228438676, delete mark 406242, delete 239439
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 63742607, node heap has 33386 buffer(s)
Hash table size 63742607, node heap has 0 buffer(s)
Hash table size 63742607, node heap has 106 buffer(s)
Hash table size 63742607, node heap has 552581 buffer(s)
Hash table size 63742607, node heap has 33558 buffer(s)
Hash table size 63742607, node heap has 1 buffer(s)
Hash table size 63742607, node heap has 81 buffer(s)
Hash table size 63742607, node heap has 499411 buffer(s)
38842.73 hash searches/s, 66931.80 non-hash searches/s
---
LOG
---
Log sequence number 6070350783605
Log flushed up to 6070347109837
Pages flushed up to 6069248723606
Last checkpoint at 6069205734998
0 pending log flushes, 0 pending chkp writes
1463927 log i/o's done, 20.87 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 263771389952
Dictionary memory allocated 5552286
Buffer pool size 15726960
Free buffers 7641
Database pages 14600195
Old database pages 5389362
Modified db pages 108091
Percent of dirty pages(LRU & free pages): 0.740
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 7116675, not young 154182163
146.02 youngs/s, 751.54 non-youngs/s
Pages read 13002091, created 20524731, written 50122771
103.42 reads/s, 316.99 creates/s, 704.93 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 2 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 14600195, unzip_LRU len: 0
I/O sum[289016]:cur[24], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 1966080
Free buffers 978
Database pages 1824943
Old database pages 673640
Modified db pages 15664
Percent of dirty pages(LRU & free pages): 0.858
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 886318, not young 21558561
19.48 youngs/s, 59.30 non-youngs/s
Pages read 1627657, created 2567248, written 7270228
12.76 reads/s, 36.39 creates/s, 112.55 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1824943, unzip_LRU len: 0
I/O sum[36127]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 1965840
Free buffers 989
Database pages 1825185
Old database pages 673729
Modified db pages 12978
Percent of dirty pages(LRU & free pages): 0.711
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 888506, not young 17872488
18.83 youngs/s, 87.37 non-youngs/s
Pages read 1620960, created 2551959, written 6091321
13.00 reads/s, 40.28 creates/s, 91.33 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 2 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1825185, unzip_LRU len: 0
I/O sum[36127]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 1965840
Free buffers 979
Database pages 1824947
Old database pages 673642
Modified db pages 13895
Percent of dirty pages(LRU & free pages): 0.761
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 886797, not young 18128418
17.33 youngs/s, 151.48 non-youngs/s
Pages read 1618497, created 2564253, written 6108565
12.46 reads/s, 36.33 creates/s, 98.29 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 3 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1824947, unzip_LRU len: 0
I/O sum[36127]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 1965840
Free buffers 999
Database pages 1825047
Old database pages 673678
Modified db pages 12517
Percent of dirty pages(LRU & free pages): 0.685
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 888765, not young 20029901
17.59 youngs/s, 59.42 non-youngs/s
Pages read 1628730, created 2569645, written 6152662
13.06 reads/s, 42.35 creates/s, 76.28 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 1 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1825047, unzip_LRU len: 0
I/O sum[36127]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 1965840
Free buffers 980
Database pages 1824878
Old database pages 673616
Modified db pages 13641
Percent of dirty pages(LRU & free pages): 0.747
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 878757, not young 17787806
18.41 youngs/s, 104.46 non-youngs/s
Pages read 1613388, created 2567887, written 6120053
13.30 reads/s, 46.02 creates/s, 86.76 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 2 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1824878, unzip_LRU len: 0
I/O sum[36127]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 1965840
Free buffers 877
Database pages 1825227
Old database pages 673745
Modified db pages 13217
Percent of dirty pages(LRU & free pages): 0.724
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 899041, not young 21347971
17.78 youngs/s, 104.68 non-youngs/s
Pages read 1640617, created 2569267, written 6121197
13.65 reads/s, 39.11 creates/s, 70.81 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 2 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1825227, unzip_LRU len: 0
I/O sum[36127]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 1965840
Free buffers 921
Database pages 1825177
Old database pages 673726
Modified db pages 13437
Percent of dirty pages(LRU & free pages): 0.736
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 895011, not young 19236578
17.28 youngs/s, 95.22 non-youngs/s
Pages read 1630603, created 2572247, written 6148992
11.98 reads/s, 47.04 creates/s, 86.09 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 2 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1825177, unzip_LRU len: 0
I/O sum[36127]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 1965840
Free buffers 918
Database pages 1824791
Old database pages 673586
Modified db pages 12742
Percent of dirty pages(LRU & free pages): 0.698
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 893480, not young 18220440
19.31 youngs/s, 89.61 non-youngs/s
Pages read 1621639, created 2562225, written 6109753
13.22 reads/s, 29.48 creates/s, 82.81 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 3 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1824791, unzip_LRU len: 0
I/O sum[36127]:cur[3], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=1230, Main thread ID=139849981024000, state: making checkpoint
Number of rows inserted 1244250277, updated 5901, deleted 187006, read 1627791678
17677.60 inserts/s, 0.13 updates/s, 0.00 deletes/s, 10865.63 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
mysql mariadb memory
asked Oct 26 '18 at 9:25
TestTubeRobotTestTubeRobot
11
bumped to the homepage by Community♦ 17 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
0
0
0
Version : mysql Ver 15.1 Distrib 10.3.9-MariaDB
OS : CentOS Linux release 7.5.1804 (Core)
Specification : 384GB RAM
Context :
I am using this server only for database (with no other processes running). Currently, I am doing mass insertions (migrating) into the this server (potentially few terabytes data).
Despite setting my buffer pool to only 240G, somehow the total used memory shown by top or free -m is almost close to 95% (370GB+) of my total physical RAM (384GB).
However, the strange thing is the total memory used by mysqld is shown as 240GB+ (via top command).
To further confirm that the 370GB+ RAM was used by mysqld and not some hidden processes, I restarted MariaDB via service mariadb restart. The moment it restarted successfully, I verified the memory usage via free -m and saw almost 340GB+ free (under available column).
Hence, am I missing out in some settings in my configuration file or is that how mysqld fundamentally work? Or could it also be there are memory leaks, which resulted in memory not being freed properly in mysqld?
free --mega (when database has been ran for several hours)
# free --mega
total used free shared buff/cache available
Mem: 386689 376532 8120 8 2036 7353
Swap: 4095 1189 2906
/etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
#[client-server]
[mysqld]
datadir=/zfs2/db/mysql/
user=mysql
socket=/zfs2/db/mysql/mysql.sock
innodb_file_per_table=1
innodb_buffer_pool_size=240G
innodb_log_file_size=768M
max_allowed_packet=128M
max_connections=50000
innodb_thread_concurrency=48
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_use_native_aio=0
innodb_flush_log_at_trx_commit=2
innodb_doublewrite=0
open_files_limit=50000
skip-name-resolve
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
/etc/my.cnf (END)
top command
top - 17:08:25 up 2 days, 3:32, 1 user, load average: 5.21, 4.61, 4.77
Tasks: 387 total, 2 running, 385 sleeping, 0 stopped, 0 zombie
%Cpu(s): 6.8 us, 3.7 sy, 0.0 ni, 82.4 id, 7.0 wa, 0.0 hi, 0.2 si, 0.0 st
KiB Mem : 39596972+total, 7963856 free, 38592006+used, 2085784 buff/cache
KiB Swap: 4194300 total, 2978664 free, 1215636 used. 7179308 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1230 mysql 20 0 271.8g 258.4g 14248 S 188.9 68.4 2391:28 mysqld
10260 root 20 0 162248 2488 1528 R 16.7 0.0 0:00.04 top
....
....
SHOW GLOBAL STATUS
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 538573, free list len 270263, seg size 808837, 6384375 merges
merged operations:
insert 228438676, delete mark 406242, delete 239439
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 63742607, node heap has 33386 buffer(s)
Hash table size 63742607, node heap has 0 buffer(s)
Hash table size 63742607, node heap has 106 buffer(s)
Hash table size 63742607, node heap has 552581 buffer(s)
Hash table size 63742607, node heap has 33558 buffer(s)
Hash table size 63742607, node heap has 1 buffer(s)
Hash table size 63742607, node heap has 81 buffer(s)
Hash table size 63742607, node heap has 499411 buffer(s)
38842.73 hash searches/s, 66931.80 non-hash searches/s
---
LOG
---
Log sequence number 6070350783605
Log flushed up to 6070347109837
Pages flushed up to 6069248723606
Last checkpoint at 6069205734998
0 pending log flushes, 0 pending chkp writes
1463927 log i/o's done, 20.87 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 263771389952
Dictionary memory allocated 5552286
Buffer pool size 15726960
Free buffers 7641
Database pages 14600195
Old database pages 5389362
Modified db pages 108091
Percent of dirty pages(LRU & free pages): 0.740
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 7116675, not young 154182163
146.02 youngs/s, 751.54 non-youngs/s
Pages read 13002091, created 20524731, written 50122771
103.42 reads/s, 316.99 creates/s, 704.93 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 2 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 14600195, unzip_LRU len: 0
I/O sum[289016]:cur[24], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 1966080
Free buffers 978
Database pages 1824943
Old database pages 673640
Modified db pages 15664
Percent of dirty pages(LRU & free pages): 0.858
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 886318, not young 21558561
19.48 youngs/s, 59.30 non-youngs/s
Pages read 1627657, created 2567248, written 7270228
12.76 reads/s, 36.39 creates/s, 112.55 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1824943, unzip_LRU len: 0
I/O sum[36127]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 1965840
Free buffers 989
Database pages 1825185
Old database pages 673729
Modified db pages 12978
Percent of dirty pages(LRU & free pages): 0.711
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 888506, not young 17872488
18.83 youngs/s, 87.37 non-youngs/s
Pages read 1620960, created 2551959, written 6091321
13.00 reads/s, 40.28 creates/s, 91.33 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 2 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1825185, unzip_LRU len: 0
I/O sum[36127]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 1965840
Free buffers 979
Database pages 1824947
Old database pages 673642
Modified db pages 13895
Percent of dirty pages(LRU & free pages): 0.761
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 886797, not young 18128418
17.33 youngs/s, 151.48 non-youngs/s
Pages read 1618497, created 2564253, written 6108565
12.46 reads/s, 36.33 creates/s, 98.29 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 3 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1824947, unzip_LRU len: 0
I/O sum[36127]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 1965840
Free buffers 999
Database pages 1825047
Old database pages 673678
Modified db pages 12517
Percent of dirty pages(LRU & free pages): 0.685
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 888765, not young 20029901
17.59 youngs/s, 59.42 non-youngs/s
Pages read 1628730, created 2569645, written 6152662
13.06 reads/s, 42.35 creates/s, 76.28 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 1 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1825047, unzip_LRU len: 0
I/O sum[36127]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 1965840
Free buffers 980
Database pages 1824878
Old database pages 673616
Modified db pages 13641
Percent of dirty pages(LRU & free pages): 0.747
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 878757, not young 17787806
18.41 youngs/s, 104.46 non-youngs/s
Pages read 1613388, created 2567887, written 6120053
13.30 reads/s, 46.02 creates/s, 86.76 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 2 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1824878, unzip_LRU len: 0
I/O sum[36127]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 1965840
Free buffers 877
Database pages 1825227
Old database pages 673745
Modified db pages 13217
Percent of dirty pages(LRU & free pages): 0.724
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 899041, not young 21347971
17.78 youngs/s, 104.68 non-youngs/s
Pages read 1640617, created 2569267, written 6121197
13.65 reads/s, 39.11 creates/s, 70.81 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 2 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1825227, unzip_LRU len: 0
I/O sum[36127]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 1965840
Free buffers 921
Database pages 1825177
Old database pages 673726
Modified db pages 13437
Percent of dirty pages(LRU & free pages): 0.736
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 895011, not young 19236578
17.28 youngs/s, 95.22 non-youngs/s
Pages read 1630603, created 2572247, written 6148992
11.98 reads/s, 47.04 creates/s, 86.09 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 2 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1825177, unzip_LRU len: 0
I/O sum[36127]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 1965840
Free buffers 918
Database pages 1824791
Old database pages 673586
Modified db pages 12742
Percent of dirty pages(LRU & free pages): 0.698
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 893480, not young 18220440
19.31 youngs/s, 89.61 non-youngs/s
Pages read 1621639, created 2562225, written 6109753
13.22 reads/s, 29.48 creates/s, 82.81 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 3 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1824791, unzip_LRU len: 0
I/O sum[36127]:cur[3], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=1230, Main thread ID=139849981024000, state: making checkpoint
Number of rows inserted 1244250277, updated 5901, deleted 187006, read 1627791678
17677.60 inserts/s, 0.13 updates/s, 0.00 deletes/s, 10865.63 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
mysql mariadb memory
asked Oct 26 '18 at 9:25
TestTubeRobotTestTubeRobot
11
Version : mysql Ver 15.1 Distrib 10.3.9-MariaDB
OS : CentOS Linux release 7.5.1804 (Core)
Specification : 384GB RAM
Context :
I am using this server only for database (with no other processes running). Currently, I am doing mass insertions (migrating) into the this server (potentially few terabytes data).
Despite setting my buffer pool to only 240G, somehow the total used memory shown by top or free -m is almost close to 95% (370GB+) of my total physical RAM (384GB).
However, the strange thing is the total memory used by mysqld is shown as 240GB+ (via top command).
To further confirm that the 370GB+ RAM was used by mysqld and not some hidden processes, I restarted MariaDB via service mariadb restart. The moment it restarted successfully, I verified the memory usage via free -m and saw almost 340GB+ free (under available column).
Hence, am I missing out in some settings in my configuration file or is that how mysqld fundamentally work? Or could it also be there are memory leaks, which resulted in memory not being freed properly in mysqld?
free --mega (when database has been ran for several hours)
# free --mega
total used free shared buff/cache available
Mem: 386689 376532 8120 8 2036 7353
Swap: 4095 1189 2906
/etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
#[client-server]
[mysqld]
datadir=/zfs2/db/mysql/
user=mysql
socket=/zfs2/db/mysql/mysql.sock
innodb_file_per_table=1
innodb_buffer_pool_size=240G
innodb_log_file_size=768M
max_allowed_packet=128M
max_connections=50000
innodb_thread_concurrency=48
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_use_native_aio=0
innodb_flush_log_at_trx_commit=2
innodb_doublewrite=0
open_files_limit=50000
skip-name-resolve
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
/etc/my.cnf (END)
top command
top - 17:08:25 up 2 days, 3:32, 1 user, load average: 5.21, 4.61, 4.77
Tasks: 387 total, 2 running, 385 sleeping, 0 stopped, 0 zombie
%Cpu(s): 6.8 us, 3.7 sy, 0.0 ni, 82.4 id, 7.0 wa, 0.0 hi, 0.2 si, 0.0 st
KiB Mem : 39596972+total, 7963856 free, 38592006+used, 2085784 buff/cache
KiB Swap: 4194300 total, 2978664 free, 1215636 used. 7179308 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1230 mysql 20 0 271.8g 258.4g 14248 S 188.9 68.4 2391:28 mysqld
10260 root 20 0 162248 2488 1528 R 16.7 0.0 0:00.04 top
....
....
SHOW GLOBAL STATUS
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 538573, free list len 270263, seg size 808837, 6384375 merges
merged operations:
insert 228438676, delete mark 406242, delete 239439
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 63742607, node heap has 33386 buffer(s)
Hash table size 63742607, node heap has 0 buffer(s)
Hash table size 63742607, node heap has 106 buffer(s)
Hash table size 63742607, node heap has 552581 buffer(s)
Hash table size 63742607, node heap has 33558 buffer(s)
Hash table size 63742607, node heap has 1 buffer(s)
Hash table size 63742607, node heap has 81 buffer(s)
Hash table size 63742607, node heap has 499411 buffer(s)
38842.73 hash searches/s, 66931.80 non-hash searches/s
---
LOG
---
Log sequence number 6070350783605
Log flushed up to 6070347109837
Pages flushed up to 6069248723606
Last checkpoint at 6069205734998
0 pending log flushes, 0 pending chkp writes
1463927 log i/o's done, 20.87 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 263771389952
Dictionary memory allocated 5552286
Buffer pool size 15726960
Free buffers 7641
Database pages 14600195
Old database pages 5389362
Modified db pages 108091
Percent of dirty pages(LRU & free pages): 0.740
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 7116675, not young 154182163
146.02 youngs/s, 751.54 non-youngs/s
Pages read 13002091, created 20524731, written 50122771
103.42 reads/s, 316.99 creates/s, 704.93 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 2 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 14600195, unzip_LRU len: 0
I/O sum[289016]:cur[24], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 1966080
Free buffers 978
Database pages 1824943
Old database pages 673640
Modified db pages 15664
Percent of dirty pages(LRU & free pages): 0.858
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 886318, not young 21558561
19.48 youngs/s, 59.30 non-youngs/s
Pages read 1627657, created 2567248, written 7270228
12.76 reads/s, 36.39 creates/s, 112.55 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1824943, unzip_LRU len: 0
I/O sum[36127]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 1965840
Free buffers 989
Database pages 1825185
Old database pages 673729
Modified db pages 12978
Percent of dirty pages(LRU & free pages): 0.711
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 888506, not young 17872488
18.83 youngs/s, 87.37 non-youngs/s
Pages read 1620960, created 2551959, written 6091321
13.00 reads/s, 40.28 creates/s, 91.33 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 2 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1825185, unzip_LRU len: 0
I/O sum[36127]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 1965840
Free buffers 979
Database pages 1824947
Old database pages 673642
Modified db pages 13895
Percent of dirty pages(LRU & free pages): 0.761
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 886797, not young 18128418
17.33 youngs/s, 151.48 non-youngs/s
Pages read 1618497, created 2564253, written 6108565
12.46 reads/s, 36.33 creates/s, 98.29 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 3 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1824947, unzip_LRU len: 0
I/O sum[36127]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 1965840
Free buffers 999
Database pages 1825047
Old database pages 673678
Modified db pages 12517
Percent of dirty pages(LRU & free pages): 0.685
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 888765, not young 20029901
17.59 youngs/s, 59.42 non-youngs/s
Pages read 1628730, created 2569645, written 6152662
13.06 reads/s, 42.35 creates/s, 76.28 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 1 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1825047, unzip_LRU len: 0
I/O sum[36127]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 1965840
Free buffers 980
Database pages 1824878
Old database pages 673616
Modified db pages 13641
Percent of dirty pages(LRU & free pages): 0.747
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 878757, not young 17787806
18.41 youngs/s, 104.46 non-youngs/s
Pages read 1613388, created 2567887, written 6120053
13.30 reads/s, 46.02 creates/s, 86.76 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 2 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1824878, unzip_LRU len: 0
I/O sum[36127]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 1965840
Free buffers 877
Database pages 1825227
Old database pages 673745
Modified db pages 13217
Percent of dirty pages(LRU & free pages): 0.724
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 899041, not young 21347971
17.78 youngs/s, 104.68 non-youngs/s
Pages read 1640617, created 2569267, written 6121197
13.65 reads/s, 39.11 creates/s, 70.81 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 2 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1825227, unzip_LRU len: 0
I/O sum[36127]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 1965840
Free buffers 921
Database pages 1825177
Old database pages 673726
Modified db pages 13437
Percent of dirty pages(LRU & free pages): 0.736
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 895011, not young 19236578
17.28 youngs/s, 95.22 non-youngs/s
Pages read 1630603, created 2572247, written 6148992
11.98 reads/s, 47.04 creates/s, 86.09 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 2 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1825177, unzip_LRU len: 0
I/O sum[36127]:cur[3], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 1965840
Free buffers 918
Database pages 1824791
Old database pages 673586
Modified db pages 12742
Percent of dirty pages(LRU & free pages): 0.698
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 893480, not young 18220440
19.31 youngs/s, 89.61 non-youngs/s
Pages read 1621639, created 2562225, written 6109753
13.22 reads/s, 29.48 creates/s, 82.81 writes/s
Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 3 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1824791, unzip_LRU len: 0
I/O sum[36127]:cur[3], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=1230, Main thread ID=139849981024000, state: making checkpoint
Number of rows inserted 1244250277, updated 5901, deleted 187006, read 1627791678
17677.60 inserts/s, 0.13 updates/s, 0.00 deletes/s, 10865.63 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
mysql mariadb memory
mysql mariadb memory
asked Oct 26 '18 at 9:25
TestTubeRobotTestTubeRobot
11
asked Oct 26 '18 at 9:25
TestTubeRobotTestTubeRobot
11
edited Oct 26 '18 at 9:44
TestTubeRobot
asked Oct 26 '18 at 9:25
TestTubeRobotTestTubeRobot
11
asked Oct 26 '18 at 9:25
TestTubeRobotTestTubeRobot
11
asked Oct 26 '18 at 9:25
TestTubeRobotTestTubeRobot
11
11
bumped to the homepage by Community♦ 17 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 17 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
0
Do not set max_connections so high. Suggest 1000.
innodb_buffer_pool_instances = 16
For further investigation, please provide SHOW GLOBAL STATUS; and SHOW VARIABLES; after it has been running for a day.
Have you done the math? How long will it take to load all the data? How fast does it come in? (I have tips if you need them.)
answered Oct 28 '18 at 1:13
Rick JamesRick James
43k22259
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f221083%2fmysql-consuming-too-much-memory%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
Do not set max_connections so high. Suggest 1000.
innodb_buffer_pool_instances = 16
For further investigation, please provide SHOW GLOBAL STATUS; and SHOW VARIABLES; after it has been running for a day.
Have you done the math? How long will it take to load all the data? How fast does it come in? (I have tips if you need them.)
answered Oct 28 '18 at 1:13
Rick JamesRick James
43k22259
add a comment |
0
Do not set max_connections so high. Suggest 1000.
innodb_buffer_pool_instances = 16
For further investigation, please provide SHOW GLOBAL STATUS; and SHOW VARIABLES; after it has been running for a day.
Have you done the math? How long will it take to load all the data? How fast does it come in? (I have tips if you need them.)
answered Oct 28 '18 at 1:13
Rick JamesRick James
43k22259
add a comment |
0
0
0
Do not set max_connections so high. Suggest 1000.
innodb_buffer_pool_instances = 16
For further investigation, please provide SHOW GLOBAL STATUS; and SHOW VARIABLES; after it has been running for a day.
Have you done the math? How long will it take to load all the data? How fast does it come in? (I have tips if you need them.)
answered Oct 28 '18 at 1:13
Rick JamesRick James
43k22259
Do not set max_connections so high. Suggest 1000.
innodb_buffer_pool_instances = 16
For further investigation, please provide SHOW GLOBAL STATUS; and SHOW VARIABLES; after it has been running for a day.
Have you done the math? How long will it take to load all the data? How fast does it come in? (I have tips if you need them.)
answered Oct 28 '18 at 1:13
Rick JamesRick James
43k22259
answered Oct 28 '18 at 1:13
Rick JamesRick James
43k22259
answered Oct 28 '18 at 1:13
Rick JamesRick James
43k22259
answered Oct 28 '18 at 1:13
Rick JamesRick James
43k22259
43k22259
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f221083%2fmysql-consuming-too-much-memory%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
