Re: SOS ---- Could you help me with postgresql.....???? - Mailing list pgsql-admin

From alvaro@audifarma.com.co
Subject Re: SOS ---- Could you help me with postgresql.....????
Date
Msg-id 13916.200.68.150.54.1079952528.squirrel@audifarm.audifarma.com.co
Whole thread Raw
In response to SOS ---- Could you help me with postgresql.....????  (alvaro@audifarma.com.co)
List pgsql-admin
 Mr. Szabo this is part of the output of the vacuumdb command


 DETAIL:   dead row versions cannot be removed yet.
There were  unused item pointers.
  pages are entirely empty.
 CPU 0.00s/0.00u sec elapsed 0.02 sec.
 INFO:  vacuuming "public.tbl_fact_consumos"
 INFO:  index "ind_fact_consumos" now contains 233211 row versions in 1174
 pages
 DETAIL:   index pages have been deleted,  are currently reusable.
 CPU 0.06s/0.03u sec elapsed 0.53 sec.
 INFO:  "tbl_fact_consumos": found  removable, 233211 nonremovable row
 versions in 5220 pages
 DETAIL:   dead row versions cannot be removed yet.
 There were 1846 unused item pointers.
  pages are entirely empty.
> CPU 0.24s/0.04u sec elapsed 2.19 sec.
 INFO:  vacuuming "public.tbl_reposiciones"
 INFO:  index "uc_tbl_reposiciones" now contains 385590 row versions in
 2727 pages
 DETAIL:  2 index pages have been deleted, 2 are currently reusable.
 CPU 0.24s/0.04u sec elapsed 1.39 sec.
 INFO:  "tbl_reposiciones": found  removable, 385590 nonremovable row
 versions in 7905 pages
 DETAIL:   dead row versions cannot be removed yet.
 There were 3828 unused item pointers.
  pages are entirely empty.
 CPU 0.68s/0.15u sec elapsed 3.73 sec.
 INFO:  vacuuming "public.tbl_saldos_caf"
 INFO:  index "uc_tbl_saldos_caf1" now contains 1045711 row versions in
 7116 pages
 DETAIL:   index pages have been deleted,  are currently reusable.
 CPU 0.66s/0.07u sec elapsed 4.50 sec.
 INFO:  "tbl_saldos_caf": found  removable, 1045711 nonremovable row
 versions in 9942 pages
 DETAIL:   dead row versions cannot be removed yet.
 There were 305538 unused item pointers.
  pages are entirely empty.
 CPU 0.99s/0.22u sec elapsed 12.45 sec.
 INFO:  vacuuming "public.tbl_kardex"

 I have to tell you that we made a cron that goes like this:

 echo Inicio vacum `date` >> /bk/scripts/vacuum.log
 su postgres -c '/var/lib/pgsql/bin/vacuumdb -d dbsa'
 echo Fin vacum `date` >> /bk/scripts/vacuum.log

 This cron activates each day at 23:45, when I look at the log file
 (/bk/scripts/vacuum.log), it looks that it started ok, sometimes this
 operation takes jues few minutes and some others It can take hours, I
 don´t pay to much attention to this because som me day our database has to
 serve too many operations that involves deleting and updating rows in
 several tables and I consider this differences norma. Am I wrong...?

 Here's part of the log file (/bk/scripts/vaccum.log):

 Inicio vacum Thu Mar 11 23:45:00 COT 2004
 Fin vacum Fri Mar 12 01:35:55 COT 2004
 Inicio vacum Fri Mar 12 23:45:00 COT 2004
 Fin vacum Sat Mar 13 01:35:46 COT 2004
 Inicio vacum Sat Mar 13 23:45:00 COT 2004
 Fin vacum Sun Mar 14 00:44:01 COT 2004
 Inicio vacum Sun Mar 14 23:45:00 COT 2004
 Fin vacum Mon Mar 15 00:24:07 COT 2004
 Inicio vacum Mon Mar 15 23:45:00 COT 2004
 Fin vacum Tue Mar 16 01:34:52 COT 2004
 Inicio vacum Tue Mar 16 23:45:00 COT 2004
 Fin vacum Wed Mar 17 02:32:23 COT 2004
 Inicio vacum Wed Mar 17 23:45:00 COT 2004
 Fin vacum Thu Mar 18 01:46:20 COT 2004
 Inicio vacum Fri Mar 19 23:45:00 COT 2004
 Fin vacum Sat Mar 20 00:02:49 COT 2004
 Inicio vacum Sat Mar 20 23:45:00 COT 2004
 Fin vacum Sun Mar 21 00:00:27 COT 2004
 Inicio vacum Sun Mar 21 23:45:01 COT 2004
 Fin vacum Sun Mar 21 23:59:19 COT 2004

>
> On Thu, 18 Mar 2004 alvaro@audifarma.com.co wrote:
>>
>> Recently We Update our Postgresql database server by installing
>> Postgresql
>> 7.4.1, we followed the basic installation steps that appear in the
>> install
>> file, everything was fine till this morning when we realize that the
>> partition (/var) where we storage the data (/var/lib/pgsql/data) was
>> full,
>> this partition is 30GB size!!! could you belive it????. When we
>> checkout
>> the size of the data directory it was 28GB, at the end it was imposible
>> for us to startup the database normally so we have to remove the
>> /var/lib/pgsql/data directory, and restore the db from a previous
>> backup
>> made with (pg_dumpall) and this time the /var/lib/pgsql/data was 8GB
>> size
>> that is a more accurate size for our database.
>>
>> Well till now the story goes to good..
>>
>> BUT I've been the whole morning monitoring (df -v or df -h) the size of
>> the partition (/var) and is growing up every moment by a rate of 6kb to
>> 8kb at 11:00 am it was 8GB and now 14:00 is 8.9GB (what a hell is going
>> on????) if things goes like this next week I'm gonna have the same
>> problem
>> that today ... please help me
>
> Does a vacuum full verbose lower the space taken?  If so, can you post
> its
> output?
>







pgsql-admin by date:

Previous
From: Josh Berkus
Date:
Subject: Re: [PERFORM] Databases Vs. Schemas
Next
From: CoL
Date:
Subject: Re: Disable Trigger