示例pg_top：
last pID: 6607; load avg: 3.59,2.32,2.61; up 16+09:17:29 20:49:51113 processes: 1 running,111 sleePing,1 uninterruptablecpu states: 22.5% user,0.0% nice,4.9% system,63.2% IDle,9.4% iowaitMemory: 29G used,186M free,7648K buffers,23G cachedDB activity: 2479 tps,1 rollbs/s,217 buffer r/s,99 hit%,11994 row r/s,3820 row w/s DB I/O: 0 reads/s,0 KB/s,0 writes/s,0 KB/s DB disk: 149.8 GB total,46.7 GB free (68% used)Swap:
示例顶部显示框中唯一的其他重要4GB进程：
top - 21:05:09 up 16 days,9:32,2 users,load average: 2.73,2.91,2.88Tasks: 247 total,3 running,244 sleePing,0 stopped,0 zombIE%cpu(s): 22.1 us,4.1 sy,0.0 ni,62.9 ID,9.8 wa,0.0 hi,0.7 si,0.3 stKiB Mem: 30827220 total,30642584 used,184636 free,7292 buffersKiB Swap: 0 total,0 used,0 free. 23449636 cached Mem PID USER PR NI VIRT RES SHR S %cpu %MEM TIME+ COMMAND 7407 postgres 20 0 7604928 10172 7932 S 29.6 0.0 2:51.27 postgres 10469 postgres 20 0 7617716 176032 160328 R 11.6 0.6 0:01.48 postgres 10211 postgres 20 0 7630352 237736 208704 S 10.6 0.8 0:03.64 postgres 18202 elastic+ 20 0 8726984 4.223g 4248 S 9.6 14.4 883:06.79 java 9711 postgres 20 0 7619500 354188 335856 S 7.0 1.1 0:08.03 postgres 3638 postgres 20 0 7634552 1.162g 1.127g S 6.6 4.0 0:50.42 postgres
postgresql.conf中：
max_connections = 1000 # (change requires restart)shared_buffers = 7GB # min 128kBwork_mem = 40MB # min 64kBmaintenance_work_mem = 1GB # min 1MBeffective_cache_size = 20GB....
日志：
ERROR: out of memoryDETAIL: Failed on request of size 67108864.STATEMENT: SELECT "package_texts".* FROM "package_texts" WHERE "package_texts"."ID" = liMIT 1topMemoryContext: 798624 total in 83 blocks; 11944 free (21 chunks); 786680 used topTransactionContext: 8192 total in 1 blocks; 7328 free (0 chunks); 864 used Prepared QuerIEs: 253952 total in 5 blocks; 136272 free (18 chunks); 117680 used Type information cache: 24240 total in 2 blocks; 3744 free (0 chunks); 20496 used Operator lookup cache: 24576 total in 2 blocks; 11888 free (5 chunks); 12688 used tableSpace cache: 8192 total in 1 blocks; 3216 free (0 chunks); 4976 used MessageContext: 8192 total in 1 blocks; 6976 free (0 chunks); 1216 used Operator class cache: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used smgr relation table: 24576 total in 2 blocks; 5696 free (4 chunks); 18880 used TransactionAbortContext: 32768 total in 1 blocks; 32736 free (0 chunks); 32 used Portal hash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used PortalMemory: 8192 total in 1 blocks; 7888 free (0 chunks); 304 used PortalHeapMemory: 1024 total in 1 blocks; 568 free (0 chunks); 456 used ExecutorState: 32928 total in 3 blocks; 15616 free (5 chunks); 17312 used printtup: 34002024 total in 2 blocks; 7056 free (7 chunks); 33994968 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used Relcache by OID: 24576 total in 2 blocks; 12832 free (3 chunks); 11744 used CacheMemoryContext: 1372624 total in 24 blocks; 38832 free (0 chunks); 1333792 used CachedplanSource: 7168 total in 3 blocks; 3080 free (1 chunks); 4088 used Cachedplanquery: 7168 total in 3 blocks; 2992 free (1 chunks); 4176 used CachedplanSource: 15360 total in 4 blocks; 7128 free (5 chunks); 8232 used Cachedplanquery: 15360 total in 4 blocks; 3320 free (1 chunks); 12040 used CachedplanSource: 3072 total in 2 blocks; 552 free (0 chunks); 2520 used Cachedplanquery: 7168 total in 3 blocks; 1592 free (1 chunks); 5576 used CachedplanSource: 3072 total in 2 blocks; 536 free (0 chunks); 2536 used... 2 Thousand snipped lines of Cachedplans ... CachedplanSource: 15360 total in 4 blocks; 7128 free (5 chunks); 8232 used Cachedplanquery: 15360 total in 4 blocks; 3320 free (1 chunks); 12040 used CachedplanSource: 7168 total in 3 blocks; 3880 free (3 chunks); 3288 used Cachedplanquery: 7168 total in 3 blocks; 4032 free (1 chunks); 3136 used CachedplanSource: 7168 total in 3 blocks; 3936 free (3 chunks); 3232 used Cachedplanquery: 7168 total in 3 blocks; 4032 free (1 chunks); 3136 used CachedplanSource: 7168 total in 3 blocks; 3080 free (1 chunks); 4088 used Cachedplanquery: 7168 total in 3 blocks; 2992 free (1 chunks); 4176 used CachedplanSource: 7168 total in 3 blocks; 3872 free (2 chunks); 3296 used Cachedplanquery: 7168 total in 3 blocks; 4032 free (1 chunks); 3136 used pg_toast_17305_index: 1024 total in 1 blocks; 16 free (0 chunks); 1008 used index_package_raises_on_natural_key: 3072 total in 2 blocks; 1648 free (1 chunks); 1424 used index_package_extensions_on_natural_key: 3072 total in 2 blocks; 1736 free (2 chunks); 1336 used index_package_mixins_on_natural_key: 3072 total in 2 blocks; 1736 free (2 chunks); 1336 used index_package_mixins_on_includes_ID: 1024 total in 1 blocks; 152 free (0 chunks); 872 used package_texts_pkey: 1024 total in 1 blocks; 152 free (0 chunks); 872 used index_package_file_objects_on_natural_key: 3072 total in 2 blocks; 1736 free (2 chunks); 1336 used index_package_symbols_on_natural_key: 3072 total in 2 blocks; 1136 free (1 chunks); 1936 used index_package_symbols_on_full_name: 3072 total in 2 blocks; 1736 free (2 chunks); 1336 used index_package_symbols_on_alias_for_ID: 1024 total in 1 blocks; 152 free (0 chunks); 872 used package_symbols_pkey: 1024 total in 1 blocks; 152 free (0 chunks); 872 used pg_toast_17313_index: 1024 total in 1 blocks; 16 free (0 chunks); 1008 used index_packages_on_natural_key: 3072 total in 2 blocks; 1736 free (2 chunks); 1336 used packages_pkey: 1024 total in 1 blocks; 152 free (0 chunks); 872 used index_package_files_on_natural_key: 1024 total in 1 blocks; 16 free (0 chunks); 1008 used package_files_pkey: 1024 total in 1 blocks; 152 free (0 chunks); 872 used pg_toast_2619_index: 1024 total in 1 blocks; 16 free (0 chunks); 1008 used index_projects_on_user_ID: 1024 total in 1 blocks; 152 free (0 chunks); 872 used index_projects_on_type: 1024 total in 1 blocks; 152 free (0 chunks); 872 used index_projects_on_name_and_type: 1024 total in 1 blocks; 16 free (0 chunks); 1008 used index_projects_on_claim_ticket: 1024 total in 1 blocks; 152 free (0 chunks); 872 used ruby_gem_Metadata_pkey: 1024 total in 1 blocks; 152 free (0 chunks); 872 used pg_constraint_contypID_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used pg_constraint_conrelID_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used pg_constraint_conname_nsp_index: 1024 total in 1 blocks; 16 free (0 chunks); 1008 used pg_attrdef_oID_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used pg_attrdef_adrelID_adnum_index: 1024 total in 1 blocks; 16 free (0 chunks); 1008 used pg_index_indrelID_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used pg_db_role_setting_databaseID_rol_index: 1024 total in 1 blocks; 64 free (0 chunks); 960 used pg_opclass_am_name_nsp_index: 3072 total in 2 blocks; 1736 free (2 chunks); 1336 used pg_foreign_data_wrapper_name_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used pg_enum_oID_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used pg_class_relname_nsp_index: 1024 total in 1 blocks; 16 free (0 chunks); 1008 used pg_foreign_server_oID_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used pg_statistic_relID_att_inh_index: 3072 total in 2 blocks; 1736 free (2 chunks); 1336 used pg_cast_source_target_index: 1024 total in 1 blocks; 16 free (0 chunks); 1008 used pg_language_name_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used pg_collation_oID_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used pg_amop_fam_strat_index: 3072 total in 2 blocks; 1736 free (2 chunks); 1336 used pg_index_indexrelID_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used pg_ts_template_tmplname_index: 1024 total in 1 blocks; 64 free (0 chunks); 960 used pg_ts_config_map_index: 3072 total in 2 blocks; 1784 free (2 chunks); 1288 used pg_opclass_oID_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used pg_foreign_data_wrapper_oID_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used pg_event_trigger_evtname_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used pg_ts_dict_oID_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used pg_event_trigger_oID_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used pg_conversion_default_index: 3072 total in 2 blocks; 1784 free (2 chunks); 1288 used pg_operator_oprname_l_r_n_index: 3072 total in 2 blocks; 1736 free (2 chunks); 1336 used pg_trigger_tgrelID_tgname_index: 1024 total in 1 blocks; 64 free (0 chunks); 960 used pg_enum_typID_label_index: 1024 total in 1 blocks; 64 free (0 chunks); 960 used pg_ts_config_oID_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used pg_user_mapPing_oID_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used pg_opfamily_am_name_nsp_index: 3072 total in 2 blocks; 1784 free (2 chunks); 1288 used pg_foreign_table_relID_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used pg_type_oID_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used pg_aggregate_fnoID_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used pg_constraint_oID_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 64 free (0 chunks); 960 used pg_ts_parser_prsname_index: 1024 total in 1 blocks; 64 free (0 chunks); 960 used pg_ts_config_cfgname_index: 1024 total in 1 blocks; 64 free (0 chunks); 960 used pg_ts_parser_oID_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used pg_operator_oID_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used pg_namespace_nspname_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used pg_ts_template_oID_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used pg_amop_opr_fam_index: 3072 total in 2 blocks; 1736 free (2 chunks); 1336 used pg_default_acl_role_nsp_obj_index: 3072 total in 2 blocks; 1784 free (2 chunks); 1288 used pg_collation_name_enc_nsp_index: 3072 total in 2 blocks; 1784 free (2 chunks); 1288 used pg_range_rngtypID_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used pg_ts_dict_dictname_index: 1024 total in 1 blocks; 64 free (0 chunks); 960 used pg_type_typname_nsp_index: 1024 total in 1 blocks; 16 free (0 chunks); 1008 used pg_opfamily_oID_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used pg_class_oID_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used pg_proc_proname_args_nsp_index: 3072 total in 2 blocks; 1736 free (2 chunks); 1336 used pg_attribute_relID_attnum_index: 1024 total in 1 blocks; 16 free (0 chunks); 1008 used pg_proc_oID_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used pg_language_oID_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used pg_namespace_oID_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used pg_amproc_fam_proc_index: 3072 total in 2 blocks; 1736 free (2 chunks); 1336 used pg_foreign_server_name_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used pg_attribute_relID_attnam_index: 1024 total in 1 blocks; 16 free (0 chunks); 1008 used pg_conversion_oID_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used pg_user_mapPing_user_server_index: 1024 total in 1 blocks; 64 free (0 chunks); 960 used pg_conversion_name_nsp_index: 1024 total in 1 blocks; 64 free (0 chunks); 960 used pg_authID_oID_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used pg_auth_members_member_role_index: 1024 total in 1 blocks; 64 free (0 chunks); 960 used pg_tablespace_oID_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used pg_database_datname_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used pg_auth_members_role_member_index: 1024 total in 1 blocks; 64 free (0 chunks); 960 used pg_database_oID_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used pg_authID_rolname_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used mdsmgr: 24576 total in 2 blocks; 13984 free (0 chunks); 10592 used IDent parser context: 0 total in 0 blocks; 0 free (0 chunks); 0 used hba parser context: 7168 total in 3 blocks; 304 free (1 chunks); 6864 used LOCALLOCK hash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used Timezones: 83472 total in 2 blocks; 3744 free (0 chunks); 79728 used ErrorContext: 8192 total in 1 blocks; 8160 free (6 chunks); 32 used如果我正确地读取你的顶部的输出,那么当你内存不足时就不会进行.
实际错误似乎很好 – 它没有请求大量的内存,因此可能是机器在那时没有内存.
我们来快速浏览一下您的设置：
max_connections = 1000 # (change requires restart)work_mem = 40MB # min 64kB
所以 – 您认为您可以支持1000个并发查询,每个使用10个40MB(有些可能使用40MB的倍数,但让我们合理).所以 – 这表明你的机器有> 500核心,说100GB的RAM.事实并非如此.
所以 – 获取核心数量并加倍 – 这是最大连接数的合理值.这将允许您对每个核心进行一次查询,而另一个正在等待I / O.然后,如果需要(pgbouncer / Java的连接池),请在DB前面放置一个连接池.
然后,如果需要,您甚至可以考虑增加work_mem.
哦 – 没有启用交换运行是完全合理的.一旦你开始交换,无论如何在数据库使用方面都处于痛苦的世界.
编辑：扩展work_mem vs shared
如有疑问,请始终参考documentation.
正如名称所示,shared_buffers值在后端之间共享. work_mem不仅是每个后端,它实际上是每种类型.所以 – 如果一个查询在三个子查询上进行排序,那么它可能会使用三到四倍的量.
总结以上是内存溢出为你收集整理的postgresql – 错误：在具有32GB RAM且没有交换文件的计算机上内存不足全部内容，希望文章能够帮你解决postgresql – 错误：在具有32GB RAM且没有交换文件的计算机上内存不足所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）