Re: copy and postgresql.conf

What version of Solaris are you using?

Do you have the recommendations while using COPY on Solaris?
http://blogs.sun.com/roller/page/jkshah?entry=postgresql_on_solaris_better_use

wal_sync_method = fsync
wal_buffers = 128
checkpoint_segments = 128
bgwriter_percent = 0
bgwriter_maxpages = 0


And also for /etc/system on Solaris 10, 9 SPARC use the following

set maxphys=1048576
set md:md_maxphys=1048576
set segmap_percent=50
set ufs:freebehind=0
set msgsys:msginfo_msgmni = 3584
set semsys:seminfo_semmni = 4096
set shmsys:shminfo_shmmax = 15392386252
set shmsys:shminfo_shmmni = 4096


Can you try putting in one run with this values and send back your
experiences on whether it helps your workload or not?

Atleast I saw improvements using the above settings with COPY with
Postgres 8.0 and Postgres 8.1 on Solaris.

Regards,
Jignesh




FERREIRA, William (VALTECH) wrote:

>30% faster !!! i will test this new version ...
>
>thanks a lot
>
>-----Message d'origine-----
>De : pgsql-performance-owner@postgresql.org
>[mailto:pgsql-performance-owner@postgresql.org]De la part de Albert
>Cervera Areny
>Envoyé : mardi 14 février 2006 17:07
>À : pgsql-performance@postgresql.org
>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@postgresql.org
>>[mailto:pgsql-performance-owner@postgresql.org]De la part de Albert
>>Cervera Areny
>>Envoyé : mardi 14 février 2006 12:38
>>À : pgsql-performance@postgresql.org
>>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 6: explain analyze is your friend
>
>