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(at)postgresql(dot)org
>[mailto:pgsql-performance-owner(at)postgresql(dot)org]De la part de Albert
>Cervera Areny
>Envoyé : mardi 14 février 2006 17:07
>À : pgsql-performance(at)postgresql(dot)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(at)postgresql(dot)org
>>[mailto:pgsql-performance-owner(at)postgresql(dot)org]De la part de Albert
>>Cervera Areny
>>Envoyé : mardi 14 février 2006 12:38
>>À : pgsql-performance(at)postgresql(dot)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
>
>