问题现象
Tpcc压测报错,使用并发300报错,使用并发100执行正常.
17:07:51,213 [Thread-43] ERROR jTPCCTData : Unexpected SQLException in NEW_ORDER
17:07:51,213 [Thread-43] ERROR jTPCCTData : [10.xxx26:31726/10.xxx6000] ERROR: memory is temporarily unavailable
详细：Failed on request of size 488 bytes under queryid 4503599683236857 in nodeBitmapIndexscan.cpp:353.
org.postgresql.util.PSQLException: [1xx26:26000] ERROR: memory is temporarily unavailable
详细：Failed on request of size 488 bytes under queryid 4503599683236857 in nodeBitmapIndexscan.cpp:353.
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2820)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2550)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:329)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:453)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:377)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:149)
at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:112)
17:07:51,216 [Thread-245] ERROR jTPCCTData : Unexpected SQLException in DELIVERY_BG
问题分析
尝试加大内存-故障处理
current memory 参数大小
shared_buffers = 30GB # min 128kB
bulk_write_ring_size = 2GB # for bulkload, max shared_buffers
work_mem = 64MB # min 64kB
maintenance_work_mem = 1GB # min 1MB
max_process_memory = 43GB
work_mem = 64MB # min 64kB
maintenance_work_mem = 1GB # min 1MB
cstore_buffers = 16MB #min 16MB
change 1
work_mem = 2GB
[omm@mogdb01 data]$ gs_ctl stop
[omm@mogdb01 data]$ gs_ctl start
没有解决问题,TPCC压测依然报错。
change 2
max_process_memory = 50GB
[root@mogdb01 run]#sh ./runBenchmark.sh props.mogdb
TPCC 压测正常.
数据库大小如下
[omm@mogdb01 ~]$ gsql -d tpcc -p26000 -h 10.xx6 -U tpcc -W tpcc@123
gsql ((MogDB 3.0.4 build cc068866) compiled at 2023-03-03 18:35:11 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
tpcc=> \l+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Compatibility | Size | Tablespace | Description
-----------+-------+----------+---------+-------+-------------------+---------------+---------+------------+--------------------------------------
------
postgres | omm | UTF8 | C | C | | A | 1047 MB | pg_default | default administrative connection dat
abase
template0 | omm | UTF8 | C | C | =c/omm +| A | 13 MB | pg_default | default template for new databases
| | | | | omm=CTc/omm | | | |
template1 | omm | UTF8 | C | C | =c/omm +| A | 13 MB | pg_default | unmodifiable empty database
| | | | | omm=CTc/omm | | | |
test | omm | UTF8 | C | C | | A | 13 MB | pg_default |
tpcc | omm | UTF8 | C | C | | A | 17 GB | pg_default |
(5 rows)
内存组件大小-问题分析
参考
https://mp.weixin.qq.com/s/tBym1J7zzNAwxiaWKu_bgA
修改前
MogDB=# select * from pg_catalog.pg_total_memory_detail;
nodename | memorytype | memorymbytes
----------+-------------------------+--------------
dn_6001 | max_process_memory | 51200
dn_6001 | process_used_memory | 39811
dn_6001 | max_dynamic_memory | 12162
dn_6001 | dynamic_used_memory | 1761
dn_6001 | dynamic_peak_memory | 5771
dn_6001 | dynamic_used_shrctx | 976
dn_6001 | dynamic_peak_shrctx | 993
dn_6001 | max_backend_memory | 244
dn_6001 | backend_used_memory | 1
dn_6001 | max_shared_memory | 38777
dn_6001 | shared_used_memory | 36400
dn_6001 | max_cstore_memory | 16
dn_6001 | cstore_used_memory | 0
dn_6001 | max_sctpcomm_memory | 0
dn_6001 | sctpcomm_used_memory | 0
dn_6001 | sctpcomm_peak_memory | 0
dn_6001 | other_used_memory | 1532
dn_6001 | gpu_max_dynamic_memory | 0
dn_6001 | gpu_dynamic_used_memory | 0
dn_6001 | gpu_dynamic_peak_memory | 0
dn_6001 | pooler_conn_memory | 0
dn_6001 | pooler_freeconn_memory | 0
dn_6001 | storage_compress_memory | 0
dn_6001 | udf_reserved_memory | 0
(24 rows)
修改内存组件大小
max_process_memory = 42GB -8G
shared_buffers = 30GB =
bulk_write_ring_size = 2GB =
work_mem = 1GB -1G
maintenance_work_mem = 1GB =
cstore_buffers = 16MB =
[omm@mogdb01 data]$ gs_ctl stop
gs_ctl start
gsql -d postgres -p26000 -r
MogDB=# select * from pg_catalog.pg_total_memory_detail;
nodename | memorytype | memorymbytes
----------+-------------------------+--------------
dn_6001 | max_process_memory | 43008 -42G=max_process_memory
dn_6001 | process_used_memory | 6687 -6.5G
dn_6001 | max_dynamic_memory | 3970 -3.8G
dn_6001 | dynamic_used_memory | 740
dn_6001 | dynamic_peak_memory | 760
dn_6001 | dynamic_used_shrctx | 336
dn_6001 | dynamic_peak_shrctx | 336
dn_6001 | max_backend_memory | 244
dn_6001 | backend_used_memory | 1
dn_6001 | max_shared_memory | 38777 -37.8G
dn_6001 | shared_used_memory | 5741
dn_6001 | max_cstore_memory | 16
dn_6001 | cstore_used_memory | 0
dn_6001 | max_sctpcomm_memory | 0
dn_6001 | sctpcomm_used_memory | 0
dn_6001 | sctpcomm_peak_memory | 0
dn_6001 | other_used_memory | 88
dn_6001 | gpu_max_dynamic_memory | 0
dn_6001 | gpu_dynamic_used_memory | 0
dn_6001 | gpu_dynamic_peak_memory | 0
dn_6001 | pooler_conn_memory | 0
dn_6001 | pooler_freeconn_memory | 0
dn_6001 | storage_compress_memory | 0
dn_6001 | udf_reserved_memory | 0
(24 rows)
重启数据库后,加大并发数,观察内存组件的内存值的波动变化
vi /root/software/tpcc/benchmarksql/benchmarksql-5.0-arm/run/props.mogdb
terminals=900
sh ./runBenchmark.sh props.mogdb
=# select * from pg_catalog.pg_total_memory_detail;
nodename | memorytype | memorymbytes
----------+-------------------------+--------------
dn_6001 | max_process_memory | 43008
dn_6001 | process_used_memory | 8509
dn_6001 | max_dynamic_memory | 3970
dn_6001 | dynamic_used_memory | 1890
dn_6001 | dynamic_peak_memory | 1892
dn_6001 | dynamic_used_shrctx | 376
dn_6001 | dynamic_peak_shrctx | 376
dn_6001 | max_backend_memory | 244
dn_6001 | backend_used_memory | 1
dn_6001 | max_shared_memory | 38777
dn_6001 | shared_used_memory | 6111
dn_6001 | max_cstore_memory | 16
dn_6001 | cstore_used_memory | 0
dn_6001 | max_sctpcomm_memory | 0
dn_6001 | sctpcomm_used_memory | 0
dn_6001 | sctpcomm_peak_memory | 0
dn_6001 | other_used_memory | 390
dn_6001 | gpu_max_dynamic_memory | 0
dn_6001 | gpu_dynamic_used_memory | 0
dn_6001 | gpu_dynamic_peak_memory | 0
dn_6001 | pooler_conn_memory | 0
dn_6001 | pooler_freeconn_memory | 0
dn_6001 | storage_compress_memory | 0
dn_6001 | udf_reserved_memory | 0
(24 rows)
MogDB=#
MogDB=# select * from pg_catalog.pg_total_memory_detail;
nodename | memorytype | memorymbytes
----------+-------------------------+--------------
dn_6001 | max_process_memory | 43008
dn_6001 | process_used_memory | 9666
dn_6001 | max_dynamic_memory | 3970
dn_6001 | dynamic_used_memory | 2697
dn_6001 | dynamic_peak_memory | 2700
dn_6001 | dynamic_used_shrctx | 389
dn_6001 | dynamic_peak_shrctx | 389
dn_6001 | max_backend_memory | 244
dn_6001 | backend_used_memory | 1
dn_6001 | max_shared_memory | 38777
dn_6001 | shared_used_memory | 6688
dn_6001 | max_cstore_memory | 16
dn_6001 | cstore_used_memory | 0
dn_6001 | max_sctpcomm_memory | 0
dn_6001 | sctpcomm_used_memory | 0
dn_6001 | sctpcomm_peak_memory | 0
dn_6001 | other_used_memory | 163
dn_6001 | gpu_max_dynamic_memory | 0
dn_6001 | gpu_dynamic_used_memory | 0
dn_6001 | gpu_dynamic_peak_memory | 0
dn_6001 | pooler_conn_memory | 0
dn_6001 | pooler_freeconn_memory | 0
dn_6001 | storage_compress_memory | 0
dn_6001 | udf_reserved_memory | 0
(24 rows)
MogDB=# select * from pg_catalog.pg_total_memory_detail;
nodename | memorytype | memorymbytes
----------+-------------------------+--------------
dn_6001 | max_process_memory | 43008
dn_6001 | process_used_memory | 13167
dn_6001 | max_dynamic_memory | 3970
dn_6001 | dynamic_used_memory | 4006
dn_6001 | dynamic_peak_memory | 4009
dn_6001 | dynamic_used_shrctx | 506
dn_6001 | dynamic_peak_shrctx | 506
dn_6001 | max_backend_memory | 244
dn_6001 | backend_used_memory | 1
dn_6001 | max_shared_memory | 38777
dn_6001 | shared_used_memory | 8698
dn_6001 | max_cstore_memory | 16
dn_6001 | cstore_used_memory | 0
dn_6001 | max_sctpcomm_memory | 0
dn_6001 | sctpcomm_used_memory | 0
dn_6001 | sctpcomm_peak_memory | 0
dn_6001 | other_used_memory | 345
dn_6001 | gpu_max_dynamic_memory | 0
dn_6001 | gpu_dynamic_used_memory | 0
dn_6001 | gpu_dynamic_peak_memory | 0
dn_6001 | pooler_conn_memory | 0
dn_6001 | pooler_freeconn_memory | 0
dn_6001 | storage_compress_memory | 0
dn_6001 | udf_reserved_memory | 0
(24 rows)
14:11:27,439 [Thread-816] ERROR jTPCCTData : Unexpected SQLException in NEW_ORDER
14:11:27,439 [Thread-816] ERROR jTPCCTData : [1xxx:26000] ERROR: memory is temporarily unavailable
详细：Failed on request of size 8 bytes under queryid 5629499534856699 in tuptoaster.cpp:173.
org.postgresql.util.PSQLException: [10.xxx:26000] ERROR: memory is temporarily unavailable
详细：Failed on request of size 8 bytes under queryid 5629499534856699 in tuptoaster.cpp:173.
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2820)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2550)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:329)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:453)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:377)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:149)
at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:112)
at jTPCCTData.executeNewOrder(jTPCCTData.java:362)
at jTPCCTData.execute(jTPCCTData.java:95)
at jTPCCTerminal.executeTransactions(jTPCCTerminal.java:277)
at jTPCCTerminal.run(jTPCCTerminal.java:88)
at java.base/java.lang.Thread.run(Thread.java:1623)
14:11:27,439 [Thread-379] ERROR jTPCCTData : Unexpected SQLException in NEW_ORDER
14:11:27,439 [Thread-379] ERROR jTPCCTData : [10xxx00] ERROR: memory is temporarily unavailable
详细：Failed on request of size 424 bytes under queryid 5629499534856695 in tupdesc.cpp:71.
org.postgresql.util.PSQLException: [10.9xxx0.26:26000] ERROR: memory is temporarily unavailable
详细：Failed on request of size 424 bytes under queryid 5629499534856695 in tupdesc.cpp:71.
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2820)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2550)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:329)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:453)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:377)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:149)
at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:112)
at jTPCCTData.executeNewOrder(jTPCCTData.java:362)
at jTPCCTData.execute(jTPCCTData.java:95)
at jTPCCTerminal.executeTransactions(jTPCCTerminal.java:277)
at jTPCCTerminal.run(jTPCCTerminal.java:88)
at java.base/java.lang.Thread.run(Thread.java:1623)
小结：
1.对于oracle sga max来说不会存在内存不足报错的问题,例如buffer_cache是存在LRU算法,会自动删除老旧的buffer cache,如果无法删除,则会处于等待事件free buffer busy；那么如果是shared pool不足可能会报ORA-04031指向某个内存组件的不足;Oracle pga再11g max是OS free及只要操作系统有可用内存,pga session就可用用，到了12c才开始有pga的内存参数进行限制。
2.那么对于mogdb max_process_memory 参数是数据库的总内存大小,对于shared_buffers 参数是数据库行缓冲区大小,暂时不清楚session 类似pga的内存参数限制。
3. 之前的理解是tpcc压测的客户端session使用的是os free,但是可用发现os free有内存，但是TPCC还是报错内存不足；
4. 通过测试的部分我们可用发现，当dynamic_used_memory的内存区域超出max_dynamic_memory 内存大小时,TPCC报错,说明TPCC的客户端Session使用的还是数据库的内存，使用的内存区域是max_process_memory - shared_buffers -other 其它组件或者使用的内存，及mogdb可用动态调整的空闲内存大小，当空闲内存不足时，无法申请到足够的内存，则报错；
5. 依据上述第四点的观察，解决办法除了加大max_process_memory ，还可以减少shared_buffers 这些内存，目的是加大max_dynamic_memory ，减少tpcc压测并发可用减少申请内存的大小。
最后修改时间：2023-06-15 18:15:58
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
