Re: copy and postgresql.conf

["Jignesh K. Shah" <J.K.Shah@xxxxxxx>]

Actually  fsync is not the default on solaris (verify using "show all;)

(If you look closely in postgresql.conf it is commented out and 
mentioned as default but show all tells a different story)

In all my cases I saw the default as
wal_sync_method                 | open_datasync

Also I had seen quite an   improvement by changing the default 
checkpoint_segments from 3 to 64 or 128 and also increasing wal_buffers 
to 64  depending on how heavy is your load.

Also open_datasync type of operations benefit with forcedirectio on 
Solaris and hence either move wal to forcedirectio mounted file system 
or try changing default sync to fsync (the *said* default)

Now if you use fsync then you need a bigger file system cache since by 
default Solaris's segmap mechanism only maps 12% of your physical ram to 
be used for file system buffer cache. Increasing segmap_percent to 50 on 
SPARC allows to use 50% of your RAM to be mapped to be used for 50% ( 
NOTE: It does not reserve but just allow mapping of the memory which can 
be used for file system buffer cache)

Changing maxphys allows the file system buffer cache to coalesce writes 
from the 8Ks that PostgreSQL is doing to bigger writes/reads. Also since 
you are now exploiting file system buffer cache, file system Logging is 
very much recommended (available from a later update of Solaris 8 I 
believe).


Regards,
Jignesh





FERREIRA, William (VALTECH) wrote:

> with PostgreSQL 8.1.3, here are my parameters (it's the default configuration)
>
> wal_sync_method = fsync
> wal_buffers = 8
> checkpoint_segments = 3
> bgwriter_lru_percent = 1.0
> bgwriter_lru_maxpages = 5
> bgwriter_all_percent = 0.333
> bgwriter_all_maxpages = 5
>
> and you think times can be improved again ?
>
> -----Message d'origine-----
> De : J.K.Shah@xxxxxxx [mailto:J.K.Shah@xxxxxxx]
> Envoyé : mercredi 15 février 2006 15:14
> À : FERREIRA, William (VALTECH)
> Cc : Albert Cervera Areny; pgsql-performance@xxxxxxxxxxxxxx
> Objet : Re: [PERFORM] copy and postgresql.conf
>
>
>
> What's your postgresql.conf parameter for the equivalent ones that I
> suggested?
> I believe your wal_buffers and checkpoint_segments could be bigger. If
> that's the case then yep you are fine.
>
> As for the background writer I am seeing mixed results yet so not sure
> about that.
>
> But thanks for the feedback.
>
> -Jignesh
>
>
> FERREIRA, William (VALTECH) wrote:
>
>  
>
> > i tested the last version version of PostgreSQL
> > and for the same test :
> > before : 40mn
> > and now : 12mn :)
> > faster than Oracle (exactly what i wanted :p )
> >
> > thanks to everybody
> >
> > 	Will
> >
> >
> > -----Message d'origine-----
> > De : pgsql-performance-owner@xxxxxxxxxxxxxx
> > [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx]De la part de Albert
> > Cervera Areny
> > Envoyé : mardi 14 février 2006 17:07
> > À : pgsql-performance@xxxxxxxxxxxxxx
> > Objet : Re: [PERFORM] copy and postgresql.conf
> >
> >
> >
> > Sorry, COPY improvements came with 8.1
> >
> > (http://www.postgresql.org/docs/whatsnew)
> >
> > A Dimarts 14 Febrer 2006 14:26, FERREIRA, William (VALTECH) va escriure:
> >
> >
> >    
> >
> > > thanks,
> > >
> > > i'm using postgresql 8.0.3
> > > there is no primary key and no index on my tables
> > >
> > > regards
> > >
> > > -----Message d'origine-----
> > > De : pgsql-performance-owner@xxxxxxxxxxxxxx
> > > [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx]De la part de Albert
> > > Cervera Areny
> > > Envoyé : mardi 14 février 2006 12:38
> > > À : pgsql-performance@xxxxxxxxxxxxxx
> > > Objet : Re: [PERFORM] copy and postgresql.conf
> > >
> > >
> > >
> > > Hi William,
> > > 	which PostgreSQL version are you using? Newer (8.0+) versions have some
> > >
> > > important performance improvements for the COPY command.
> > >
> > > 	Also, you'll notice significant improvements by creating primary & foreign
> > >
> > > keys after the copy command. I think config tweaking can improve key and
> > >
> > > index creation but I don't think you can improve the COPY command itself.
> > >
> > > 	There are also many threads in this list commenting on this issue, you'll
> > >
> > > find it easely in the archives.
> > >
> > > A Dimarts 14 Febrer 2006 10:44, FERREIRA, William (VALTECH) va escriure:
> > >  
> > >
> > >      
> > >
> > > > hi,
> > > >
> > > > i load data from files using copy method.
> > > > Files contain between 2 and 7 millions of rows, spread on 5 tables.
> > > >
> > > > For loading all the data, it takes 40mn, and the same processing takes
> > > > 17mn with Oracle. I think that this time can be improved by changing
> > > > postgresql configuration file. But which parameters i need to manipulate
> > > > and with which values ?
> > > >
> > > > Here are the specifications of my system :
> > > > V250 architecture sun4u
> > > > 2xCPU UltraSparc IIIi 1.28 GHz.
> > > > 8 Go RAM.
> > > >
> > > > Regards.
> > > >
> > > > 	Will
> > > >
> > > >
> > > > This e-mail is intended only for the above addressee. It may contain
> > > > privileged information. If you are not the addressee you must not copy,
> > > > distribute, disclose or use any of the information in it. If you have
> > > > received it in error please delete it and immediately notify the sender.
> > > > Security Notice: all e-mail, sent to or from this address, may be
> > > > accessed by someone other than the recipient, for system management and
> > > > security reasons. This access is controlled under Regulation of
> > > > Investigatory Powers Act 2000, Lawful Business Practises.
> > > >
> > > > ---------------------------(end of broadcast)---------------------------
> > > > TIP 4: Have you searched our list archives?
> > > >
> > > >              http://archives.postgresql.org
> > > >    
> > > >
> > > >        
> > > --
> > >
> > > Albert Cervera Areny
> > > Dept. Informàtica Sedifa, S.L.
> > >
> > > Av. Can Bordoll, 149
> > > 08202 - Sabadell (Barcelona)
> > > Tel. 93 715 51 11
> > > Fax. 93 715 51 12
> > >
> > > ====================================================================
> > > ........................  AVISO LEGAL  ............................
> > > La   presente  comunicación  y sus anexos tiene como destinatario la
> > > persona a  la  que  va  dirigida, por  lo  que  si  usted lo  recibe
> > > por error  debe  notificarlo  al  remitente  y   eliminarlo   de  su
> > > sistema,  no  pudiendo  utilizarlo,  total  o   parcialmente,   para
> > > ningún  fin.  Su  contenido  puede  tener información confidencial o
> > > protegida legalmente   y   únicamente   expresa  la  opinión     del
> > > remitente.  El   uso   del   correo   electrónico   vía Internet  no
> > > permite   asegurar    ni  la   confidencialidad   de   los  mensajes
> > > ni    su    correcta     recepción.   En    el  caso   de   que   el
> > > destinatario no consintiera la utilización  del correo  electrónico,
> > > deberá ponerlo en nuestro conocimiento inmediatamente.
> > > ====================================================================
> > > ........................... DISCLAIMER .............................
> > > This message and its  attachments are  intended  exclusively for the
> > > named addressee. If you  receive  this  message  in   error,  please
> > > immediately delete it from  your  system  and notify the sender. You
> > > may  not  use  this message  or  any  part  of it  for any  purpose.
> > > The   message   may  contain  information  that  is  confidential or
> > > protected  by  law,  and  any  opinions  expressed  are those of the
> > > individual    sender.  Internet  e-mail   guarantees   neither   the
> > > confidentiality   nor  the  proper  receipt  of  the  message  sent.
> > > If  the  addressee  of  this  message  does  not  consent to the use
> > > of   internet    e-mail,    please    inform     us    inmmediately.
> > > ====================================================================
> > >
> > >
> > >
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 5: don't forget to increase your free space map settings
> > >
> > >
> > >
> > > This mail has originated outside your organization,
> > > either from an external partner or the Global Internet.
> > > Keep this in mind if you answer this message.
> > >
> > >
> > > This e-mail is intended only for the above addressee. It may contain
> > > privileged information. If you are not the addressee you must not copy,
> > > distribute, disclose or use any of the information in it. If you have
> > > received it in error please delete it and immediately notify the sender.
> > > Security Notice: all e-mail, sent to or from this address, may be
> > > accessed by someone other than the recipient, for system management and
> > > security reasons. This access is controlled under Regulation of
> > > Investigatory Powers Act 2000, Lawful Business Practises.
> > >  
> > >
> > >      
> > --
> >
> > Albert Cervera Areny
> > Dept. Informàtica Sedifa, S.L.
> >
> > Av. Can Bordoll, 149
> > 08202 - Sabadell (Barcelona)
> > Tel. 93 715 51 11
> > Fax. 93 715 51 12
> >
> > ====================================================================
> > ........................  AVISO LEGAL  ............................
> > La   presente  comunicación  y sus anexos tiene como destinatario la
> > persona a  la  que  va  dirigida, por  lo  que  si  usted lo  recibe
> > por error  debe  notificarlo  al  remitente  y   eliminarlo   de  su
> > sistema,  no  pudiendo  utilizarlo,  total  o   parcialmente,   para
> > ningún  fin.  Su  contenido  puede  tener información confidencial o
> > protegida legalmente   y   únicamente   expresa  la  opinión     del
> > remitente.  El   uso   del   correo   electrónico   vía Internet  no
> > permite   asegurar    ni  la   confidencialidad   de   los  mensajes
> > ni    su    correcta     recepción.   En    el  caso   de   que   el
> > destinatario no consintiera la utilización  del correo  electrónico,
> > deberá ponerlo en nuestro conocimiento inmediatamente.
> > ====================================================================
> > ........................... DISCLAIMER .............................
> > This message and its  attachments are  intended  exclusively for the
> > named addressee. If you  receive  this  message  in   error,  please
> > immediately delete it from  your  system  and notify the sender. You
> > may  not  use  this message  or  any  part  of it  for any  purpose.
> > The   message   may  contain  information  that  is  confidential or
> > protected  by  law,  and  any  opinions  expressed  are those of the
> > individual    sender.  Internet  e-mail   guarantees   neither   the
> > confidentiality   nor  the  proper  receipt  of  the  message  sent.
> > If  the  addressee  of  this  message  does  not  consent to the use
> > of   internet    e-mail,    please    inform     us    inmmediately.
> > ====================================================================
> >
> >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: don't forget to increase your free space map settings
> >
> >
> >
> > This mail has originated outside your organization,
> > either from an external partner or the Global Internet.
> > Keep this in mind if you answer this message.
> >
> >
> > This e-mail is intended only for the above addressee. It may contain
> > privileged information. If you are not the addressee you must not copy,
> > distribute, disclose or use any of the information in it. If you have
> > received it in error please delete it and immediately notify the sender.
> > Security Notice: all e-mail, sent to or from this address, may be
> > accessed by someone other than the recipient, for system management and
> > security reasons. This access is controlled under Regulation of
> > Investigatory Powers Act 2000, Lawful Business Practises.
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 9: In versions below 8.0, the planner will ignore your desire to
> >      choose an index scan if your joining column's datatypes do not
> >      match
> >
> >
> >    
>
>
> This mail has originated outside your organization,
> either from an external partner or the Global Internet.
> Keep this in mind if you answer this message.
>
>
> This e-mail is intended only for the above addressee. It may contain
> privileged information. If you are not the addressee you must not copy,
> distribute, disclose or use any of the information in it. If you have
> received it in error please delete it and immediately notify the sender.
> Security Notice: all e-mail, sent to or from this address, may be
> accessed by someone other than the recipient, for system management and
> security reasons. This access is controlled under Regulation of
> Investigatory Powers Act 2000, Lawful Business Practises.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>