我可以在使用数据库后激活 PITR 吗？

Question

gsiems

Asked: 2012-10-27 08:16:48 +0800 CST2012-10-27 08:16:48 +0800 CST 2012-10-27 08:16:48 +0800 CST

故障排除 Postgresql 空间查询失败。下一步？

772

我们有一组失败的空间查询，我正在努力解决它们。我怀疑我们遇到了一些错误，但我想把事情弄清楚一点，以确保生成的错误报告既有用又指向正确的一方（postgresql、postgis、或其他）。

有人对下一步有什么建议吗？

给定：从http://yum.postgresql.org安装的 PostgreSQL v9.1完全安装了补丁的 32 位 CentOS 5

$ rpm -q postgresql91-server
postgresql91-server-9.1.6-1PGDG.rhel5

$ rpm -q geos
geos-3.3.5-1.rhel5

$ rpm -q proj
proj-4.7.0-1.rhel5

$ rpm -q postgis91
postgis91-1.5.5-1.rhel5

$ uname --kernel-name --kernel-release --kernel-version --machine --processor --hardware-platform --operating-system
Linux 2.6.18-308.16.1.el5PAE #1 SMP Tue Oct 2 22:49:17 EDT 2012 i686 i686 i386 GNU/Linux

$ cat /proc/meminfo | grep MemTotal
MemTotal:      5126968 kB

涉及三个表（为了便于阅读，名称已更改），它们都很小。

db=# select count(1), st_isclosed(the_geom) from table_1 group by st_isclosed(the_geom);
 count | st_isclosed
-------+-------------
 13268 | f
     1 | t
(2 rows)


db=# select count(1), st_isclosed(the_geom) from table_2 group by st_isclosed(the_geom);
 count | st_isclosed
-------+-------------
    18 | t
(1 row)


db=# select count(1), st_isclosed(the_geom) from table_3 group by st_isclosed(the_geom);
 count | st_isclosed 
-------+-------------
   103 | t
(1 row)

然而以下查询失败：

db=# SELECT sc.auid,
db-#        h6.huc_6
db-#     FROM table_1 sc
db-#     INNER JOIN table_2 h6
db-#         ON st_intersects(sc.the_geom, h6.the_geom)
db-#     ;
ERROR:  out of memory
DETAIL:  Failed on request of size 452262.

db=# SELECT sc.auid,
db-#        h8.huc_8
db-#     FROM table_1 sc
db-#     INNER JOIN table_3 h8
db-#         ON st_intersects(sc.the_geom, h8.the_geom)
db-#     ;
NOTICE:  St9bad_alloc
ERROR:  GEOS intersects() threw an error!

FWIW，第三个查询有效：

db=# SELECT h6.huc_6,
db-#        h8.huc_8
db-#     FROM table_2 h6
db-#     INNER JOIN table_3 h8
db-#         ON st_intersects(h6.the_geom, h8.the_geom)
db-#     ;

 huc_6  |  huc_8
--------+----------
 040102 | 07030003
 040102 | 09030005
 040102 | 07010103
 .
 .
 .
 070802 | 07100003
 070802 | 07020009
(256 rows)

这些查询适用于 postgres 8.4/CentOS 5（64 位）。在 9.1 下，它们还会导致内存使用量激增。

我已经在数据库上运行 vacuum full、analyze 和 reindex，并且还调整了 postgresql.conf 中的内存参数（使用 pgtune）——没有区别。

$ diff postgresql.conf postgresql.conf.20121025 | grep ...
> checkpoint_segments = 16
> default_statistics_target = 100
> effective_cache_size = 2816MB
> maintenance_work_mem = 224MB
> max_connections = 80
> shared_buffers = 896MB
> work_mem = 22MB
< 
< checkpoint_segments = 64
< default_statistics_target = 500
< effective_cache_size = 3584MB
< maintenance_work_mem = 576MB
< max_connections = 20
< shared_buffers = 1152MB
< wal_buffers = 32MB
< work_mem = 120MB

对于内存不足错误，pg_log 如下所示：

TopMemoryContext: 46106288 total in 5587 blocks; 5104 free (6 chunks); 46101184 used
  PostGIS Prepared Geometry Backend MemoryContext Hash: 253952 total in 5 blocks; 116408 free (16 chunks); 137544 used
  CFuncHash: 8192 total in 1 blocks; 4936 free (0 chunks); 3256 used
  TableSpace cache: 8192 total in 1 blocks; 5640 free (0 chunks); 2552 used
  Operator lookup cache: 24576 total in 2 blocks; 14072 free (6 chunks); 10504 used
  TopTransactionContext: 8192 total in 1 blocks; 7696 free (0 chunks); 496 used
  MessageContext: 57344 total in 3 blocks; 23320 free (7 chunks); 34024 used
  Operator class cache: 8192 total in 1 blocks; 4872 free (0 chunks); 3320 used
  smgr relation table: 8192 total in 1 blocks; 760 free (0 chunks); 7432 used
  TransactionAbortContext: 32768 total in 1 blocks; 32752 free (0 chunks); 16 used
  Portal hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used
  PortalMemory: 8192 total in 1 blocks; 8040 free (0 chunks); 152 used
    PortalHeapMemory: 1024 total in 1 blocks; 920 free (0 chunks); 104 used
      ExecutorState: 1872363424 total in 6099 blocks; 5819408 free (12 chunks); 1866544016 used
PostGIS Prepared Geometry Context: Prepared context
        GiST temporary context: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
        GiST queue context: 8192 total in 1 blocks; 7248 free (3 chunks); 944 used
        ExprContext: 460480 total in 2 blocks; 8176 free (5 chunks); 452304 used
        ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
        ExprContext: 460480 total in 2 blocks; 8176 free (5 chunks); 452304 used
  Relcache by OID: 8192 total in 1 blocks; 2856 free (0 chunks); 5336 used
  CacheMemoryContext: 676968 total in 21 blocks; 190472 free (1 chunks); 486496 used
    pg_toast_90116_index: 1024 total in 1 blocks; 296 free (0 chunks); 728 used
    pg_toast_83744_index: 1024 total in 1 blocks; 296 free (0 chunks); 728 used
    pg_toast_2619_index: 1024 total in 1 blocks; 296 free (0 chunks); 728 used
    hu06_wbd_the_geom_gist: 1024 total in 1 blocks; 120 free (0 chunks); 904 used
    hu06_wbd_pkey: 1024 total in 1 blocks; 368 free (0 chunks); 656 used
    stream_auids_current_the_geom_gist: 1024 total in 1 blocks; 120 free (0 chunks); 904 used
    stream_auids_current_pkey: 1024 total in 1 blocks; 368 free (0 chunks); 656 used
    pg_index_indrelid_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
    pg_constraint_conrelid_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
    pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 296 free (0 chunks); 728 used
    pg_db_role_setting_databaseid_rol_index: 1024 total in 1 blocks; 296 free (0 chunks); 728 used
    pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 240 free (0 chunks); 784 used
    pg_foreign_data_wrapper_name_index: 1024 total in 1 blocks; 368 free (0 chunks); 656 used
    pg_enum_oid_index: 1024 total in 1 blocks; 368 free (0 chunks); 656 used
    pg_class_relname_nsp_index: 1024 total in 1 blocks; 296 free (0 chunks); 728 used
    pg_foreign_server_oid_index: 1024 total in 1 blocks; 368 free (0 chunks); 656 used
    pg_statistic_relid_att_inh_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used
    pg_cast_source_target_index: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
    pg_language_name_index: 1024 total in 1 blocks; 368 free (0 chunks); 656 used
    pg_collation_oid_index: 1024 total in 1 blocks; 368 free (0 chunks); 656 used
    pg_amop_fam_strat_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used
    pg_index_indexrelid_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
    pg_ts_template_tmplname_index: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
    pg_ts_config_map_index: 1024 total in 1 blocks; 240 free (0 chunks); 784 used
    pg_opclass_oid_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
    pg_foreign_data_wrapper_oid_index: 1024 total in 1 blocks; 368 free (0 chunks); 656 used
    pg_ts_dict_oid_index: 1024 total in 1 blocks; 368 free (0 chunks); 656 used
    pg_conversion_default_index: 1024 total in 1 blocks; 240 free (0 chunks); 784 used
    pg_operator_oprname_l_r_n_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used
    pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
    pg_enum_typid_label_index: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
    pg_ts_config_oid_index: 1024 total in 1 blocks; 368 free (0 chunks); 656 used
    pg_user_mapping_oid_index: 1024 total in 1 blocks; 368 free (0 chunks); 656 used
    pg_opfamily_am_name_nsp_index: 1024 total in 1 blocks; 240 free (0 chunks); 784 used
    pg_foreign_table_relid_index: 1024 total in 1 blocks; 368 free (0 chunks); 656 used
    pg_type_oid_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
    pg_aggregate_fnoid_index: 1024 total in 1 blocks; 368 free (0 chunks); 656 used
    pg_constraint_oid_index: 1024 total in 1 blocks; 368 free (0 chunks); 656 used
    pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
    pg_ts_parser_prsname_index: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
    pg_ts_config_cfgname_index: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
    pg_ts_parser_oid_index: 1024 total in 1 blocks; 368 free (0 chunks); 656 used
    pg_operator_oid_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
    pg_namespace_nspname_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
    pg_ts_template_oid_index: 1024 total in 1 blocks; 368 free (0 chunks); 656 used
    pg_amop_opr_fam_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used
    pg_default_acl_role_nsp_obj_index: 1024 total in 1 blocks; 240 free (0 chunks); 784 used
    pg_collation_name_enc_nsp_index: 1024 total in 1 blocks; 240 free (0 chunks); 784 used
    pg_ts_dict_dictname_index: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
    pg_type_typname_nsp_index: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
    pg_opfamily_oid_index: 1024 total in 1 blocks; 368 free (0 chunks); 656 used
    pg_class_oid_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
    pg_proc_proname_args_nsp_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used
    pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 296 free (0 chunks); 728 used
    pg_proc_oid_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
    pg_language_oid_index: 1024 total in 1 blocks; 368 free (0 chunks); 656 used
    pg_namespace_oid_index: 1024 total in 1 blocks; 368 free (0 chunks); 656 used
    pg_amproc_fam_proc_index: 1024 total in 1 blocks; 200 free (0 chunks); 824 used
    pg_foreign_server_name_index: 1024 total in 1 blocks; 368 free (0 chunks); 656 used
    pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
    pg_conversion_oid_index: 1024 total in 1 blocks; 368 free (0 chunks); 656 used
    pg_user_mapping_user_server_index: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
    pg_conversion_name_nsp_index: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
    pg_authid_oid_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
    pg_auth_members_member_role_index: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
    pg_tablespace_oid_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
    pg_database_datname_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
    pg_auth_members_role_member_index: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
    pg_database_oid_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
    pg_authid_rolname_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
  MdSmgr: 8192 total in 1 blocks; 7216 free (0 chunks); 976 used
  LOCALLOCK hash: 8192 total in 1 blocks; 4936 free (0 chunks); 3256 used
  Timezones: 78520 total in 2 blocks; 5968 free (0 chunks); 72552 used
  ErrorContext: 8192 total in 1 blocks; 8176 free (3 chunks); 16 used
2012-10-25 15:37:18 CDT postgres db 3124 ERROR:  out of memory
2012-10-25 15:37:18 CDT postgres db 3124 DETAIL:  Failed on request of size 452262.

更新：原来是 GEOS 包中的一个错误。

1 个回答

Voted

gsiems · Answer 1 · 2012-11-17T10:51:06+08:00

Best Answer

gsiems

2012-11-17T10:51:06+08:002012-11-17T10:51:06+08:00

原来是 GEOS 包中的一个错误——postgis-users列表上有一个公告。错误报告的链接在这里。

1

故障排除 Postgresql 空间查询失败。下一步？

如何查看 Oracle 中的数据库列表？

mysql innodb_buffer_pool_size 应该有多大？

列出指定表的所有列

从 .frm 和 .ibd 文件恢复表？

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

如何选择每组的第一行？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

故障排除 Postgresql 空间查询失败。下一步？

1 个回答

相关问题