Re: postgres bogged down beyond tolerance

Hi Scott,

Many thanks for your suggestion. I have issued a
bit heavier command prior to reading your mail.

At psql prompt, I (as superuser) typed:
# vacuum full verbose analyse;
and it spewing many, many lines. The tail end of
the output from it looks like:

INFO: vacuuming "public.allele"
INFO: "allele": found 2518282 removable, 1257262 nonremovable row versions in 31511 pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 64 to 64 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 161940948 bytes.
21011 pages are or will become empty, including 0 at the end of the table.
21042 pages containing 161815320 free bytes are potential move destinations.
CPU 0.28s/0.20u sec elapsed 3.43 sec.
INFO: index "allele_pkey" now contains 1257262 row versions in 14539 pages
DETAIL: 2518282 index row versions were removed.
9648 index pages have been deleted, 9648 are currently reusable.
CPU 1.01s/1.48u sec elapsed 10.05 sec.
INFO: index "allele_markerid_idx" now contains 1257262 row versions in 10385 pages
DETAIL: 2518282 index row versions were removed.
6900 index pages have been deleted, 6900 are currently reusable.
CPU 0.79s/1.22u sec elapsed 8.26 sec.
INFO: "allele": moved 1257262 row versions, truncated 31511 to 10478 pages
DETAIL: CPU 119.89s/34.19u sec elapsed 318.23 sec.
INFO: index "allele_pkey" now contains 1257262 row versions in 14539 pages
DETAIL: 1257262 index row versions were removed.
4849 index pages have been deleted, 4849 are currently reusable.
CPU 0.54s/0.86u sec elapsed 2.98 sec.
INFO: index "allele_markerid_idx" now contains 1257262 row versions in 10385 pages
DETAIL: 1257262 index row versions were removed.
3467 index pages have been deleted, 3467 are currently reusable.
CPU 0.36s/0.81u sec elapsed 2.27 sec.
INFO: vacuuming "pg_toast.pg_toast_16524"
INFO: "pg_toast_16524": found 0 removable, 0 nonremovable row versions in 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 0 to 0 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 0 bytes.
0 pages are or will become empty, including 0 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "pg_toast_16524_index" now contains 0 row versions in 1 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: analyzing "public.allele"
INFO: "allele": scanned 3000 of 10478 pages, containing 360000 live rows and 0 dead rows; 3000 rows in sample, 1257360 estimated total rows

I would appreciate it if you could give me a bit of
interpretation from psql.

Regards,

Tena Sakai
tsakai(at)gallo(dot)ucsf(dot)edu

-----Original Message-----
From: Scott Marlowe [mailto:scott(dot)marlowe(at)gmail(dot)com]
Sent: Wed 11/14/2007 11:47 AM
To: Tena Sakai
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] postgres bogged down beyond tolerance

On Nov 14, 2007 12:56 PM, Tena Sakai <tsakai(at)gallo(dot)ucsf(dot)edu> wrote:
>
> The postgres server I have (on redhat linux 2.6 with recent
> Dell hardware (4 cpus)) is running terribly slow.
>
> A job it should have gotten done in less than 1 hour
> took 7.5 hours last night.

OK, before you try to fix the problem by blindly changing settings,
let's figure out what the problem IS.

Sounds to me like you've got bloat in your storage subsystem.

psql into your database as a superuser (postgres, etc...) and run

vacuum verbose;

and post the last 10 or so lines here.