ETL's questions

Estou tentando instalar o PostgreSQL 9.4.1 no Ubuntu 14.04.2. Estou usando os pacotes do apt.postgresql.org.

O pacote é instalado, mas falha na execução pg_createcluster. Se eu executar manualmente, recebo:

Creating new cluster 9.4/main... config /etc/postgresql/9.4/main data /var/lib/postgresql/9.4/main locale en_US.UTF-8 Flags of /var/lib/postgresql/9.4/main set as --------------e-C Error: initdb failed

Não consigo encontrar detalhes sobre o motivo da initdbfalha.

Se eu executar manualmente initdbcomo postgresusuário, isso funcionará, mas não criará os arquivos de configuração.

Verifiquei tudo o que pude pensar, inclusive on-line e não consigo encontrar uma solução.

Estou enfrentando um problema em que tenho duas conexões de um usuário para meu servidor PostgreSQL que estão em execução há cerca de 4 horas e estão em estado de confirmação há algum tempo (pelo menos 1 hora que estou assistindo) . Essas conexões estão impedindo a execução de outras consultas, mas elas mesmas não estão bloqueadas.

Aqui estão as duas conexões em questão.

postgres=# select * from pg_stat_activity where usename = 'xxxxx';
 datid | datname | procpid | usesysid | usename | current_query | waiting |          xact_start           |          query_start          |         backend_start         |  client_addr  | client_port
-------+---------+---------+----------+---------+---------------+---------+-------------------------------+-------------------------------+-------------------------------+---------------+-------------
 20394 | xxxxxx  |   17509 |    94858 | xxxxx   | COMMIT        | f       | 2014-01-30 05:51:11.311363-05 | 2014-01-30 05:51:12.042515-05 | 2014-01-30 05:51:11.294444-05 | xx.xx.xxx.xxx |       63531
 20394 | xxxxxx  |    9593 |    94858 | xxxxx   | COMMIT        | f       | 2014-01-30 06:45:17.032651-05 | 2014-01-30 06:45:17.694533-05 | 2014-01-30 06:45:16.992576-05 | xx.xx.xxx.xxx |       63605

PID 9593 é o mais problemático que outros usuários são bloqueados por este. Até onde o usuário está admitindo, ele truncou sua tabela e fez inserções em lotes de 1.000 confirmações após cada lote.

Atualmente este PID apresenta os seguintes bloqueios:

postgres=# select * from pg_locks where pid = 9593;
   locktype    | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid  |        mode         | granted
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+------+---------------------+---------
 relation      |    20394 | 29173472 |      |       |            |               |         |       |          | 261/0              | 9593 | AccessExclusiveLock | t
 relation      |    20394 | 27794470 |      |       |            |               |         |       |          | 261/0              | 9593 | RowExclusiveLock    | t
 relation      |    20394 | 27794470 |      |       |            |               |         |       |          | 261/0              | 9593 | ShareLock           | t
 relation      |    20394 | 27794470 |      |       |            |               |         |       |          | 261/0              | 9593 | AccessExclusiveLock | t
 virtualxid    |          |          |      |       | 261/503292 |               |         |       |          | 261/0              | 9593 | ExclusiveLock       | t
 transactionid |          |          |      |       |            |     503213304 |         |       |          | 261/0              | 9593 | ExclusiveLock       | t

Não consigo matar este PID (nada acontece quando eu emito o comando kill). Não tenho certeza do que fazer para diagnosticar isso ainda mais e, obviamente, resolver isso.

Qualquer entrada de alguém?

Executando o PostgreSQL 8.4 no servidor Ubuntu Linux.

EDITAR:

Como encontrei outras conexões em um estado semelhante em que o commit estava travando, procurei mais e encontrei o seguinte nos logs do servidor:

Jan 30 02:29:45 server001 kernel: [3521062.240540] postgres      D 0000000000000000     0 23220   8154 0x00000004
Jan 30 02:29:45 server001 kernel: [3521062.240550]  ffff8800174c9d08 0000000000000082 ffff88041cd24728 0000000000015880
Jan 30 02:29:45 server001 kernel: [3521062.240559]  ffff8806c678b110 0000000000015880 0000000000015880 0000000000015880
Jan 30 02:29:45 server001 kernel: [3521062.240567]  0000000000015880 ffff8806c678b110 0000000000015880 0000000000015880
Jan 30 02:29:45 server001 kernel: [3521062.240575] Call Trace:
Jan 30 02:29:45 server001 kernel: [3521062.240582]  [<ffffffff810da010>] ? sync_page+0x0/0x50
Jan 30 02:29:45 server001 kernel: [3521062.240590]  [<ffffffff81528488>] io_schedule+0x28/0x40
Jan 30 02:29:45 server001 kernel: [3521062.240596]  [<ffffffff810da04d>] sync_page+0x3d/0x50
Jan 30 02:29:45 server001 kernel: [3521062.240603]  [<ffffffff815289a7>] __wait_on_bit+0x57/0x80
Jan 30 02:29:45 server001 kernel: [3521062.240610]  [<ffffffff810da1be>] wait_on_page_bit+0x6e/0x80
Jan 30 02:29:45 server001 kernel: [3521062.240618]  [<ffffffff81078540>] ? wake_bit_function+0x0/0x40
Jan 30 02:29:45 server001 kernel: [3521062.240627]  [<ffffffff810e4480>] ? pagevec_lookup_tag+0x20/0x30
Jan 30 02:29:45 server001 kernel: [3521062.240634]  [<ffffffff810da665>] wait_on_page_writeback_range+0xf5/0x190
Jan 30 02:29:45 server001 kernel: [3521062.240644]  [<ffffffff81053668>] ? try_to_wake_up+0x118/0x340
Jan 30 02:29:45 server001 kernel: [3521062.240651]  [<ffffffff810da727>] filemap_fdatawait+0x27/0x30
Jan 30 02:29:45 server001 kernel: [3521062.240659]  [<ffffffff811431b4>] vfs_fsync+0xa4/0xf0
Jan 30 02:29:45 server001 kernel: [3521062.240667]  [<ffffffff81143239>] do_fsync+0x39/0x60
Jan 30 02:29:45 server001 kernel: [3521062.240674]  [<ffffffff8114328b>] sys_fsync+0xb/0x10
Jan 30 02:29:45 server001 kernel: [3521062.240682]  [<ffffffff81012042>] system_call_fastpath+0x16/0x1b

Como um DBA, eu observo regularmente meu banco de dados, ajusto algumas consultas (fazendo com que os programadores o reescrevam ou adicionem índices ou atualizem estatísticas, se necessário, etc.). Eu também ajustei as opções do servidor, etc.

Mas o que não me agrada é não ter boas métricas para dizer se meu trabalho vale a pena ou não.

Em uma otimização de consultas, posso dizer se a consulta foi executada mais rapidamente ou não. Posso usar logs e pgfouine para detectar consultas lentas.

Mas existem maneiras gerais de quantificar o desempenho do banco de dados, métricas para acompanhar que me dirão se geralmente estou melhorando as coisas ou não realmente fazendo diferença, etc.?

Estou procurando métricas capturadas no nível do banco de dados, não no nível do aplicativo.

Estou executando o PG 8.4

Eu tinha uma tabela com cerca de 20 mil registros. Qualquer consulta acessando esta tabela com mais de 1 registro (por exemplo, joins) seria muito, muito lenta. Mesmo uma contagem levaria cerca de 20 segundos.

As estatísticas mostraram 5 milhões de live_tuples.

Tentei vácuo, análise de vácuo e, no final, vácuo completo. Nada muda em termos de velocidade. E as estatísticas ainda mostravam a mesma coisa.

Acabei criando uma nova tabela, inseri todos os registros na nova tabela e derrubei/renomeei a antiga.

A nova mesa agora funciona muito rápido.

Alguma ideia de:

1) Qual teria sido o manejo mais correto? Estou procurando uma maneira melhor de fazer isso, pois um dos efeitos colaterais é que as exibições que apontavam para a tabela foram modificadas pela instrução "alter table rename to" e, sem o meu conhecimento, as exibições apontavam para o antigo mesa para 2 dias!

2) Por que isso aconteceria em primeiro lugar?