Re: Planner performance extremely affected by an hanging transaction (20-30 times)?

Hi

On Wed, Sep 25, 2013 at 1:30 AM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:

> On Tue, Sep 24, 2013 at 11:03 AM, didier <did447(at)gmail(dot)com> wrote:
>
>>
>> As a matter of fact you get the same slow down after a rollback until
>> autovacuum, and if autovacuum can't keep up...
>>
>
> Have you experimentally verified the last part? btree indices have some
> special kill-tuple code which should remove aborted tuples from the index
> the first time they are encountered, without need for a vacuum.
>
> Cheers,
>
> Jeff
>

>
>

Yes my bad, it works but there's leftover junk and a vacuum is still
needed

Running above test with autovacuum off, 1 client and insert 50 000 on
postgresql 9.4 qit version.
Before insert 2 000 queries/s
after insert 80/s
after rollback 800/s (back to 2 000/s if commit)
after vacuum 2 000 /s again and vacuum output:

INFO: vacuuming
"public.categories"

INFO: scanned index "categories_pkey" to remove 50000 row
versions

DETAIL: CPU 0.00s/0.00u sec elapsed 0.01
sec.

INFO: "categories": removed 50000 row versions in 319
pages

DETAIL: CPU 0.00s/0.00u sec elapsed 0.00
sec.

INFO: index "categories_pkey" now contains 1000 row versions in 278
pages

DETAIL: 50000 index row versions were
removed.

272 index pages have been deleted, 136 are currently
reusable.

CPU 0.00s/0.00u sec elapsed 0.00
sec.

INFO: "categories": found 50000 removable, 1000 nonremovable row versions
in 325 out of 325 pages
DETAIL: 0 dead row versions cannot be removed
yet.

There were 0 unused item
pointers.

0 pages are entirely
empty.

CPU 0.00s/0.01u sec elapsed 0.02
sec.

INFO: "categories": stopping truncate due to conflicting lock
request

INFO: vacuuming
"pg_toast.pg_toast_16783"

INFO: index "pg_toast_16783_index" now contains 0 row versions in 1
pages

DETAIL: 0 index row versions were
removed.

0 index pages have been deleted, 0 are currently
reusable.

CPU 0.00s/0.00u sec elapsed 0.00
sec.

INFO: "pg_toast_16783": found 0 removable, 0 nonremovable row versions in
0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed
yet.

There were 0 unused item
pointers.

0 pages are entirely
empty.

CPU 0.00s/0.00u sec elapsed 0.00
sec.

INFO: analyzing
"public.categories"

INFO: "categories": scanned 325 of 325 pages, containing 1000 live rows
and 0 dead rows; 1000 rows in sample, 1000 estimated total rows

perf output after rollback but before vacuum
93.36% postgres
/var/lib/vz/root/165/usr/local/pgsql/bin/postgres
|
|--41.51%-- _bt_checkkeys
| |
| |--93.03%-- _bt_readpage
| | |
| | |--97.46%-- _bt_steppage
| | | _bt_first
| | | btgettuple
| | | FunctionCall2Coll
| | | index_getnext_tid

Didier