Re: Planner performance extremely affected by an hanging transaction (20-30 times)? - Mailing list pgsql-performance
| From | didier |
|---|---|
| Subject | Re: Planner performance extremely affected by an hanging transaction (20-30 times)? |
| Date | |
| Msg-id | CAJRYxuLTneRZdWwTd3eKEMiSSnBoUOsj37oquCowS5KO3oBQyQ@mail.gmail.com Whole thread Raw |
| In response to | Re: Planner performance extremely affected by an hanging transaction (20-30 times)? (Jeff Janes <jeff.janes@gmail.com>) |
| List | pgsql-performance |
Hi
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
On Wed, Sep 25, 2013 at 1:30 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
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.
INFO: vacuuming "public.categories" On Tue, Sep 24, 2013 at 11:03 AM, didier <did447@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: 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
pgsql-performance by date: