Hi
On Wed, Sep 25, 2013 at 1:30 AM, Jeff Janes <jeff.ja...@gmail.com> wrote:
> On Tue, Sep 24, 2013 at 11:03 AM, didier <did...@gmail.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
