Postgres Pro
Downloads
Home   >   mailing lists

Thread: tuning postgresql

tuning postgresql

From
Alberto González
Date:
Hello everyone!
    I need a tuning performance a database in postgresql v7.1.
What steps I must follow?

            Thanks
                    Alberto

Re: tuning postgresql

From
Andrew Sullivan
Date:
On Tue, Aug 13, 2002 at 11:52:54AM -0400, Alberto Gonz?lez wrote:
> Hello everyone!
>     I need a tuning performance a database in postgresql v7.1.
> What steps I must follow?

Probably wise to start by reading

    http://www.ca.postgresql.org/docs/momjian/hw_performance/

A

--
----
Andrew Sullivan                               87 Mowat Avenue
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110

Vacuum analyze infos

From
Jean-Arthur Silve
Date:
Hi,

We use postgresql (7.2) and we are happy with it !

But we would like to tune it the better is possible !

We use a P4 1800 Mhz with SCSI 160 disk and 1Go RAM.

Postgres use 512Mo for shared buffer.

My question is about the informations shown by vacuum analyze :

Example (on a table very sollicited everyday)


NOTICE:  --Relation codes--
NOTICE:  Index codes_pkey: Pages 1751; Tuples 154392: Deleted 6264.
         CPU 0.00s/0.20u sec elapsed 0.80 sec.
NOTICE:  Removed 6271 tuples in 851 pages.
         CPU 0.03s/0.09u sec elapsed 0.80 sec.
NOTICE:  Pages 7430: Changed 0, Empty 0; Tup 154392: Vac 6271, Keep 0,
UnUsed 451218.
         Total CPU 0.05s/0.34u sec elapsed 1.80 sec.
NOTICE:  Analyzing codes
VACUUM


what is the difference wetween Vac and and unUsed ?
Is unUsed spaces unUsed but not deleted ? Is this space will be reuse later ???

I read somewhere one day there was something to do with
max_fsm_relations
  and max_fsm_pages
parameters ?

Well, if you have any infos about that or URL to check, thank you !

jean-arthur





At 13:06 13/08/02 -0400, Andrew Sullivan wrote:
>On Tue, Aug 13, 2002 at 11:52:54AM -0400, Alberto Gonz?lez wrote:
> > Hello everyone!
> >     I need a tuning performance a database in postgresql v7.1.
> > What steps I must follow?
>
>Probably wise to start by reading
>
>         http://www.ca.postgresql.org/docs/momjian/hw_performance/
>
>A
>
>--
>----
>Andrew Sullivan                               87 Mowat Avenue
>Liberty RMS                           Toronto, Ontario Canada
><andrew@libertyrms.info>                              M6K 3E3
>                                          +1 416 646 3304 x110
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org

Re: Vacuum analyze infos

From
Tom Lane
Date:
Jean-Arthur Silve <jeanarthur@eurovox.fr> writes:
> NOTICE:  Pages 7430: Changed 0, Empty 0; Tup 154392: Vac 6271, Keep 0,
> UnUsed 451218.
>          Total CPU 0.05s/0.34u sec elapsed 1.80 sec.

> what is the difference wetween Vac and and unUsed ?

IIRC, "Vac" is the number of tuples freed in this VACUUM pass.
"UnUsed" is the total number of free tuple slots (linepointers) in the
table.  I'm not sure if that includes the just-freed ones or not.

> Is unUsed spaces unUsed but not deleted ?

Freed slot pointers are available for re-use later.  But the fact that
there are many more free pointers than used pointers (450k vs 155k in
your example) suggests to me that a VACUUM FULL might be appropriate.

If you find that VACUUM FULL reduces the file size (number of pages)
significantly, then you should plan on more frequent regular VACUUMs
and/or increasing the postmaster's FSM parameters to prevent dead
space from accumulating again.

            regards, tom lane