为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】:03134284058-20230726-197096-20046
【操作系统】:Anolis OS 8.4
【CPU】:Intel(R) Xeon(R) Platinum 8358 CPU @ 2.60GHz
【问题描述】*:应用程序压力测试,数据库服务被kill掉
/var/log/messages报错如下:
Dec 11 13:12:14 iZma601ll3e306uoipv3s2Z kernel: oom-kill:constraint=CONSTRAINT_NONE,nodemask=(null),cpuset=/,mems_allowed=0,global_oom,task_memcg=/user.slice/user-0.slice/session-3.scope,task=dmserver,pid
=2408,uid=12345
Dec 11 13:12:14 iZma601ll3e306uoipv3s2Z kernel: Out of memory: Killed process 2408 (dmserver) total-vm:84530628kB, anon-rss:64580228kB, file-rss:0kB, shmem-rss:0kB, UID:12345 pgtables:130568kB oom_score_a
dj:0
Dec 11 13:12:15 iZma601ll3e306uoipv3s2Z kernel: oom_reaper: reaped process 2408 (dmserver), now anon-rss:0kB, file-rss:0kB, shmem-rss:0kB
//数据库参数max_os_memeory=50,为什么会把内存用完呢?
PARA_NAME PARA_VALUE SESS_VALUE FILE_VALUE PARA_TYPE
HUGE_BUFFER 80 80 80 IN FILE
HUGE_BUFFER_POOLS 4 4 4 IN FILE
BUFFER 13000 13000 13000 IN FILE
BUFFER_POOLS 41 41 41 IN FILE
BUFFER_MODE 0 0 0 IN FILE
BUFFER_FAST_RELEASE 1 1 1 SYS
MAX_BUFFER 13000 13000 1000 IN FILE
MEMORY_TARGET 4000 4000 4000 SYS
MAX_OS_MEMORY 50 50 50 IN FILE
//服务器cpu配置
$ lscpu
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 16
On-line CPU(s) list: 0-15
Thread(s) per core: 2
Core(s) per socket: 8
Socket(s): 1
NUMA node(s): 1
Vendor ID: GenuineIntel
CPU family: 6
Model: 106
Model name: Intel(R) Xeon(R) Platinum 8358 CPU @ 2.60GHz
Stepping: 6
CPU MHz: 2593.906
BogoMIPS: 5187.81
Virtualization: VT-x
Hypervisor vendor: KVM
Virtualization type: full
L1d cache: 32K
L1i cache: 32K
L2 cache: 4096K
L3 cache: 16384K
NUMA node0 CPU(s): 0-15
Flags: fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ss ht syscall nx pdpe1gb rdtscp lm constant_tsc arch_perfmon rep_good nopl xtopology cpuid tsc_known_freq pni pclmulqdq vmx ssse3 fma cx16 pdcm pcid sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand hypervisor lahf_lm abm 3dnowprefetch cpuid_fault invpcid_single pti ssbd ibrs ibpb stibp tpr_shadow vnmi flexpriority ept vpid ept_ad tsc_adjust bmi1 hle avx2 smep bmi2 erms invpcid rtm avx512f avx512dq rdseed adx smap avx512ifma clflushopt clwb avx512cd sha_ni avx512bw avx512vl xsaveopt xsavec xgetbv1 xsaves arat avx512vbmi umip pku ospke avx512_vbmi2 gfni vaes vpclmulqdq avx512_vnni avx512_bitalg avx512_vpopcntdq la57 rdpid md_clear
//服务器内存配置
$ cat /proc/meminfo
MemTotal: 65662980 kB
MemFree: 55135356 kB
MemAvailable: 55808672 kB
Buffers: 76140 kB
Cached: 1107936 kB
SwapCached: 0 kB
Active: 244024 kB
Inactive: 9703460 kB
Active(anon): 328 kB
Inactive(anon): 8765140 kB
Active(file): 243696 kB
Inactive(file): 938320 kB
Unevictable: 0 kB
Mlocked: 0 kB
SwapTotal: 0 kB
SwapFree: 0 kB
Dirty: 712 kB
Writeback: 4 kB
AnonPages: 8743544 kB
Mapped: 111520 kB
Shmem: 2040 kB
KReclaimable: 168216 kB
Slab: 266268 kB
SReclaimable: 168216 kB
SUnreclaim: 98052 kB
KernelStack: 12048 kB
PageTables: 25612 kB
NFS_Unstable: 0 kB
Bounce: 0 kB
WritebackTmp: 0 kB
CommitLimit: 32831488 kB
Committed_AS: 25890108 kB
VmallocTotal: 13743895347199 kB
VmallocUsed: 0 kB
VmallocChunk: 0 kB
Percpu: 97280 kB
HardwareCorrupted: 0 kB
AnonHugePages: 8151040 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: 192360 kB
DirectMap2M: 9244672 kB
DirectMap1G: 59768832 kB
[dmdba@iZma601ll3e306uoipv3s2Z ~]$ free -h
total used free shared buff/cache available
Mem: 62Gi 8.8Gi 52Gi 1.0Mi 1.3Gi 53Gi
Swap: 0B 0B 0B
[dmdba@iZma601ll3e306uoipv3s2Z ~]$
MALLOC_ARENA_MAX这个操作系统层面的参数值是多少呢,建议配置为4
根据你的配置,建议执行以下sql进行参数优化,以控制内存使用
SP_SET_PARA_VALUE(2,'WORKER_THREADS',16);
SP_SET_PARA_VALUE(2,'IO_THR_GROUPS',4);
SP_SET_PARA_VALUE(2,'GEN_SQL_MEM_RECLAIM',0);
SP_SET_PARA_VALUE(2,'MAX_OS_MEMORY', 100);
SP_SET_PARA_VALUE(2,'MEMORY_POOL', 2000);
SP_SET_PARA_VALUE(2,'MEMORY_N_POOLS', 11);
SP_SET_PARA_VALUE(2,'MEMORY_TARGET', 5000);
SP_SET_PARA_VALUE(2,'BUFFER', 18000);
SP_SET_PARA_VALUE(2,'MAX_BUFFER', 18000);
SP_SET_PARA_VALUE(2,'BUFFER_POOLS', 59);
SP_SET_PARA_VALUE(2,'RECYCLE', 2000);
SP_SET_PARA_VALUE(2,'RECYCLE_POOLS', 19);
SP_SET_PARA_VALUE(2,'FAST_POOL_PAGES', 9999);
SP_SET_PARA_VALUE(2,'FAST_ROLL_PAGES', 1000);
SP_SET_PARA_VALUE(2,'ENABLE_FREQROOTS',1);
SP_SET_PARA_VALUE(2,'MULTI_PAGE_GET_NUM',1);
SP_SET_PARA_VALUE(2,'PRELOAD_SCAN_NUM',0);
SP_SET_PARA_VALUE(2,'PRELOAD_EXTENT_NUM',0);
SP_SET_PARA_VALUE(2,'TASK_THREADS',4);
SP_SET_PARA_VALUE(2,'MEMORY_MAGIC_CHECK', 1);
SP_SET_PARA_VALUE(1,'HJ_BUF_GLOBAL_SIZE', 3000);
SP_SET_PARA_VALUE(1,'HJ_BUF_SIZE', 250);
SP_SET_PARA_VALUE(1,'HAGR_BUF_GLOBAL_SIZE',3000);
SP_SET_PARA_VALUE(1,'HAGR_BUF_SIZE', 250);
SP_SET_PARA_VALUE(2,'SORT_FLAG',0);
SP_SET_PARA_VALUE(2,'SORT_BLK_SIZE',1);
SP_SET_PARA_VALUE(2,'SORT_BUF_SIZE', 2);
SP_SET_PARA_VALUE(2,'SORT_BUF_GLOBAL_SIZE', 2000);
SP_SET_PARA_VALUE(2,'RLOG_POOL_SIZE', 1024);
SP_SET_PARA_VALUE(2,'CACHE_POOL_SIZE', 1024);
SP_SET_PARA_VALUE(2,'DICT_BUF_SIZE', 50);
SP_SET_PARA_VALUE(2,'VM_POOL_TARGET', 16384);
SP_SET_PARA_VALUE(2,'SESS_POOL_TARGET', 16384);
SP_SET_PARA_VALUE(2,'USE_PLN_POOL', 1);
SP_SET_PARA_VALUE(2,'ENABLE_MONITOR', 1);
SP_SET_PARA_VALUE(2,'SVR_LOG', 0);
SP_SET_PARA_VALUE(2,'TEMP_SIZE', 1024);
SP_SET_PARA_VALUE(2,'TEMP_SPACE_LIMIT', 102400);
SP_SET_PARA_VALUE(2,'MAX_SESSIONS', 1500);
SP_SET_PARA_VALUE(2,'MAX_SESSION_STATEMENT', 20000);
SP_SET_PARA_VALUE(2,'PK_WITH_CLUSTER', 1);
SP_SET_PARA_VALUE(2,'ENABLE_ENCRYPT',0);
SP_SET_PARA_VALUE(2,'OLAP_FLAG',2);
SP_SET_PARA_VALUE(2,'VIEW_PULLUP_FLAG',1);
SP_SET_PARA_VALUE(2,'OPTIMIZER_MODE',1);
SP_SET_PARA_VALUE(2,'ADAPTIVE_NPLN_FLAG',0);
SP_SET_PARA_VALUE(2,'PARALLEL_PURGE_FLAG',1);
SP_SET_PARA_VALUE(2,'PARALLEL_POLICY',2);
SP_SET_PARA_VALUE(2,'UNDO_EXTENT_NUM',16);
SP_SET_PARA_VALUE(2,'ENABLE_INJECT_HINT',1);
SP_SET_PARA_VALUE(2,'BTR_SPLIT_MODE',1);
SP_SET_PARA_VALUE(2,'FAST_LOGIN',1);
SP_SET_PARA_VALUE(2,'ENABLE_MONITOR_BP',0);