Re: Postgres 7.3.1 poor insert/update/search performance
От | Seth Robertson |
---|---|
Тема | Re: Postgres 7.3.1 poor insert/update/search performance |
Дата | |
Msg-id | 200301212207.h0LM7Dt21080@winwood.sysdetect.com обсуждение исходный текст |
Ответ на | Re: Postgres 7.3.1 poor insert/update/search performance (Stephan Szabo <sszabo@megazone23.bigpanda.com>) |
Ответы |
Re: Postgres 7.3.1 poor insert/update/search performance
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-performance |
In message <20030121134242.Q84028-100000@megazone23.bigpanda.com>, Stephan Szabo writes: On Tue, 21 Jan 2003, Seth Robertson wrote: > The postgresql.conf file is default (my sysadmin nuked all of my > changes when he upgraded to 7.3.1--grr) and there are some shared > memory configs: kernel.sem = 250 32000 100 128, kernel.shmmax = > 2147483648, kernel.shmmni = 100, kernel.shmmax = 134217728 The > WAL is not seperated (but see below). You almost certainly want to raise shared_buffers from the default (64?) to say 1k-10k. I'm not sure how much that'll help but it should help some. I'll try that and report back later, but I was under the (false?) impression that it was primarily important when you had multiple database connections using the same table. > A "vacuum analyze" is performed between the write phase and the > read phase. However, for your analysis pleasure, here are the > results of a full verbose analyze and some explain results (both > before and after). BTW: what does explain analyze (rather than plain explain) show? /\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\ seth=> explain analyze select accum from test where val = 5; QUERY PLAN ---------------------------------------------------------------------------------------------- Seq Scan on test (cost=0.00..0.00 rows=1 width=4) (actual time=94.55..94.55 rows=0 loops=1) Filter: (val = 5) Total runtime: 99.20 msec (3 rows) seth=> explain analyze update test set accum = accum + 53 where val = '5'; QUERY PLAN ----------------------------------------------------------------------------------------------- Seq Scan on test (cost=0.00..0.00 rows=1 width=18) (actual time=31.95..31.95 rows=0 loops=1) Filter: (val = 5::bigint) Total runtime: 32.04 msec (3 rows) seth=> explain analyze insert into test values (5, 53); QUERY PLAN ---------------------------------------------------------------------------------- Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.01..0.01 rows=1 loops=1) Total runtime: 7.50 msec (2 rows) seth=> vacuum full verbose analyze test seth-> ; INFO: --Relation public.test-- INFO: Pages 541: Changed 1, reaped 539, Empty 0, New 0; Tup 18071: Vac 81930, Keep/VTL 0/0, UnUsed 0, MinLen 40, MaxLen40; Re-using: Free/Avail. Space 3298208/3298176; EndEmpty/Avail. Pages 0/540. CPU 0.03s/0.00u sec elapsed 0.02 sec. INFO: Index test_pkey: Pages 355; Tuples 18071: Deleted 81930. CPU 0.04s/0.41u sec elapsed 1.96 sec. INFO: Rel test: Pages: 541 --> 98; Tuple(s) moved: 18046. CPU 0.95s/0.42u sec elapsed 12.74 sec. INFO: Index test_pkey: Pages 355; Tuples 18071: Deleted 18046. CPU 0.02s/0.05u sec elapsed 0.31 sec. INFO: Analyzing public.test VACUUM seth=> explain analyze select accum from test where val = 5; QUERY PLAN ----------------------------------------------------------------------------------------------- Seq Scan on test (cost=0.00..323.89 rows=1 width=4) (actual time=0.13..14.20 rows=1 loops=1) Filter: (val = 5) Total runtime: 14.26 msec (3 rows) seth=> explain analyze select accum from test where val = 2147483648; QUERY PLAN -------------------------------------------------------------------------------------------------------------- Index Scan using test_pkey on test (cost=0.00..5.99 rows=1 width=4) (actual time=0.11..0.11 rows=0 loops=1) Index Cond: (val = 2147483648::bigint) Total runtime: 0.16 msec (3 rows) seth=> explain analyze update test set accum = accum + 53 where val = '5'; QUERY PLAN --------------------------------------------------------------------------------------------------------------- Index Scan using test_pkey on test (cost=0.00..5.99 rows=1 width=18) (actual time=0.24..0.24 rows=1 loops=1) Index Cond: (val = 5::bigint) Total runtime: 0.39 msec (3 rows) seth=> explain analyze insert into test values (6, 53); QUERY PLAN ---------------------------------------------------------------------------------- Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.01..0.01 rows=1 loops=1) Total runtime: 0.08 msec (2 rows) seth=> explain analyze insert into test values (2147483647, 53); QUERY PLAN ---------------------------------------------------------------------------------- Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.01..0.01 rows=1 loops=1) Total runtime: 0.33 msec (2 rows) /\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\ -Seth Robertson
В списке pgsql-performance по дате отправления:
Предыдущее
От: Stephan SzaboДата:
Сообщение: Re: Postgres 7.3.1 poor insert/update/search performance