Início / user-6393

gsiems's questions

Martin Hope
gsiems
Asked: 2012-10-27 08:16:48 +0800 CST
  • 6

Temos um conjunto de consultas espaciais que estão falhando e estou lutando para solucioná-las. Suspeito que estejamos encontrando algum bug, mas gostaria de definir as coisas um pouco melhor para ter certeza e também para que o relatório de bug resultante seja útil e direcionado para a parte correta (postgresql, postgis, ou outro).

Alguém tem alguma recomendação para os próximos passos?

Dado: PostgreSQL v9.1 instalado de http://yum.postgresql.org em uma instalação totalmente corrigida de 32 bits do 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

Existem três tabelas envolvidas (os nomes foram alterados para facilitar a leitura), todas elas pequenas.

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)

No entanto, as seguintes consultas falham:

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, a terceira consulta funciona:

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)

Essas consultas funcionam no postgres 8.4/CentOS 5 (64 bits). Em 9.1, eles também resultam em um aumento no uso de memória.

Eu executei o vacuum full, analisei e reindexei no banco de dados e também ajustei os parâmetros de memória no postgresql.conf (usando pgtune)-- não faz diferença.

$ 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

Para o erro de falta de memória, o pg_log se parece com:

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.

Atualização: Acontece que foi um bug no pacote GEOS.

postgresql troubleshooting
Martin Hope
gsiems
Asked: 2011-08-27 10:48:35 +0800 CST
  • 11

Estou procurando alterar como os backups são feitos e estou me perguntando se existe uma maneira de determinar quais bancos de dados em um cluster postgreql não foram alterados recentemente?

Em vez de usar pg_dumpall, gostaria de usar pg_dump e despejar apenas os bancos de dados que foram alterados desde o último backup (alguns bancos de dados não são atualizados com muita frequência) - a ideia é que, se nada mudou, o backup atual deve ainda ser bom.

Alguém conhece uma maneira de determinar quando um banco de dados específico foi atualizado/alterado pela última vez?

Obrigado...

Atualizar:

Eu esperava não ter que escrever gatilhos em todo o lugar, pois não tenho controle sobre a criação de bancos de dados em um cluster específico (muito menos a criação de objetos de banco de dados em um banco de dados).

Indo mais fundo, parece que há uma correlação entre o conteúdo do arquivo $PGDATA/global/pg_database (especificamente o segundo campo) e os nomes de diretório em $PGDATA/base.

Indo em um membro, eu acho que o segundo campo do arquivo pg_database é o id do banco de dados e que cada banco de dados tem seu próprio subdiretório em $PGDATA/base (com o oid para o nome do subdiretório). Isso é correto? Em caso afirmativo, é razoável usar os registros de data e hora dos arquivos em $PGDATA/base/* como o gatilho para a necessidade de um backup?

...Ou há um modo melhor?

Obrigado novamente...

postgresql backup