Pessoal, segue as configurações do meu ambiente windows (PostgreSQL 9.2.4):
max_connections = 15
shared_buffers = 512MB
work_mem = 16MB
maintenance_work_mem = 128MB
fsync = off
synchronous_commit = off
full_pages_writes = off
effective_cache_size = 2GB
checkpoint_segments = 150
checkpoint_completion_target = 0.9
autovacuum = off
max_locks_per_transaction = 52048
Máquina física windows seven professional, com processador intel core
I5-2500K CPU 3.30GHZ (4 cores) e 4 GB de ram, 64 bit
configurações do meu ambiente linux (PostgreSQL 9.3.5), consegui uma
máquina com linux para os testes :)
max_connections = 10
shared_buffers = 1228MB
work_mem = 256MB
maintenance_work_mem = 128MB
fsync = off
synchronous_commit = off
full_pages_writes = off
effective_cache_size = 128MB
checkpoint_segments = 150
checkpoint_completion_target = 0.9
autovacuum = off
max_locks_per_transaction = 50064
Máquina virtual com Debian 6.0 squeeze, com processador intel xeon CPU
E5-2407 2.2GHZ (1 core) e 4 GB de ram, 64 bit
Trata-se de um sistema legado desktop, onde estão migrando de banco de
dados. O banco de dados antigo é baseado em arquivos .BTR e estão em fase
de testes com o postgres, então, no momento, os servidores estão em uso
apenas para testes.
Não é possível utilizar schemas (neste momento) visto isso ser uma
particularidade da aplicação, primeiro temos que efetuar a migração para
depois alterar a aplicação para utilizar o conceito de schemas.
Os testes...
Tentei efetuar vacuum full na máquina linux (vacuumdb -v -a -z -f -U
postgres), após quase 3 horas de execução ocorreu out of memory, abaixo
mensagem da tela
DETAIL: 0 dead row versions cannot be removed yet.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: analyzing "public.lctoinve_26_2007"
INFO: "lctoinve_26_2007": scanned 0 of 0 pages, containing 0 live rows and
0 dead rows; 0 rows in sample, 0 estimated total rows
INFO: vacuuming "public.lctos_26_2007"
INFO: "lctos_26_2007": found 25830 removable, 25832 nonremovable row
versions in 4686 pages
DETAIL: 0 dead row versions cannot be removed yet.
CPU 3.08s/2.09u sec elapsed 6.50 sec.
INFO: analyzing "public.lctos_26_2007"
vacuumdb: vacuuming of database "nk" failed: ERROR: out of memory
DETAIL: Failed on request of size 836.
real 176m48.716s
user 0m7.276s
sys 0m12.277s
root@debian-x86:~#
mensagem no log do postgres (últimas linhas)
MdSmgr: 2088960 total in 8 blocks; 529192 free (6 chunks); 1559768 used
ident parser context: 0 total in 0 blocks; 0 free (0 chunks); 0 used
hba parser context: 3072 total in 2 blocks; 616 free (1 chunks); 2456 used
LOCALLOCK hash: 8192 total in 1 blocks; 2880 free (0 chunks); 5312 used
Timezones: 78520 total in 2 blocks; 5968 free (0 chunks); 72552 used
ErrorContext: 8192 total in 1 blocks; 8176 free (8 chunks); 16 used
2014-08-07 13:53:38 BRT [2897]: [4-1] user=postgres,db=nk ERROR: out of
memory
2014-08-07 13:53:38 BRT [2897]: [5-1] user=postgres,db=nk DETAIL: Failed
on request of size 836.
2014-08-07 13:53:38 BRT [2897]: [6-1] user=postgres,db=nk STATEMENT:
VACUUM (FULL, VERBOSE, ANALYZE);
2014-08-07 13:53:39 BRT [2897]: [7-1] user=postgres,db=nk LOG:
disconnection: session time: 2:56:48.654 user=postgres database=nk
host=[local]
Tentei efetuar dump, mas também ocorreu out of memory após 32 minutos
(detalhe, nem tinha começado a parte dos copy)
Mensagem da tela
root@debian-x86:~# time /opt/PostgreSQL/9.3/bin/pg_dump -U postgres -x -O
-Fc nk > /opt/PostgreSQL/9.3/dump_nk.sql
pg_dump: [archiver (db)] query failed: ERROR: out of memory
DETAIL: Failed on request of size 52.
pg_dump: [archiver (db)] query was: SELECT t.tableoid, t.oid, t.relname AS
indexname, pg_catalog.pg_get_indexdef(i.indexrelid) AS indexdef, t.relnatts
AS indnkeys, i.indkey, i.indisclustered, t.relpages, c.contype, c.conname,
c.condeferrable, c.condeferred, c.tableoid AS contableoid, c.oid AS conoid,
pg_catalog.pg_get_constraintdef(c.oid, false) AS condef, (SELECT spcname
FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) AS
tablespace, array_to_string(t.reloptions, ', ') AS options FROM
pg_catalog.pg_index i JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid)
LEFT JOIN pg_catalog.pg_constraint c ON (i.indrelid = c.conrelid AND
i.indexrelid = c.conindid AND c.contype IN ('p','u','x')) WHERE i.indrelid
= '623151'::pg_catalog.oid AND i.indisvalid AND i.indisready ORDER BY
indexname
real 32m6.940s
user 0m51.899s
sys 0m19.325s
root@debian-x86:~#
Mensagem no log do postgres
MdSmgr: 8192 total in 1 blocks; 5704 free (0 chunks); 2488 used
ident parser context: 0 total in 0 blocks; 0 free (0 chunks); 0 used
hba parser context: 3072 total in 2 blocks; 616 free (1 chunks); 2456 used
LOCALLOCK hash: 16769024 total in 11 blocks; 5852960 free (31 chunks);
10916064 used
Timezones: 78520 total in 2 blocks; 5968 free (0 chunks); 72552 used
ErrorContext: 8192 total in 1 blocks; 8176 free (9 chunks); 16 used
2014-08-07 14:41:46 BRT [4398]: [1467188-1] user=postgres,db=nk ERROR: out
of memory
2014-08-07 14:41:46 BRT [4398]: [1467189-1] user=postgres,db=nk DETAIL:
Failed on request of size 52.
2014-08-07 14:41:46 BRT [4398]: [1467190-1] user=postgres,db=nk STATEMENT:
SELECT t.tableoid, t.oid, t.relname AS indexname,
pg_catalog.pg_get_indexdef(i.indexrelid) AS indexdef, t.relnatts AS
indnkeys, i.indkey, i.indisclustered, t.relpages, c.contype, c.conname,
c.condeferrable, c.condeferred, c.tableoid AS contableoid, c.oid AS conoid,
pg_catalog.pg_get_constraintdef(c.oid, false) AS condef, (SELECT spcname
FROM pg_catalog.pg_tablespace s WHERE s.oid = t.reltablespace) AS
tablespace, array_to_string(t.reloptions, ', ') AS options FROM
pg_catalog.pg_index i JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid)
LEFT JOIN pg_catalog.pg_constraint c ON (i.indrelid = c.conrelid AND
i.indexrelid = c.conindid AND c.contype IN ('p','u','x')) WHERE i.indrelid
= '623151'::pg_catalog.oid AND i.indisvalid AND i.indisready ORDER BY
indexname
2014-08-07 14:41:49 BRT [4398]: [1467191-1] user=postgres,db=nk LOG:
disconnection: session time: 0:32:07.074 user=postgres database=nk
host=[local]
Agora, na máquina windows, o vacuum full está rodando a quase 6 horas,
espero que termine... :)
Creio que a diferença de no linux ocorrer out of memory e no windows não se
deve ao fato de que o windows está em uma máquina física e com mais poder
de processamento, certo?
Pessoal, desculpa se a minha resposta as perguntas ficou grande, e
principalmente por ter respondido como top-posting :)
[]s
Danilo
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
