Re: copy and postgresql.conf

i'm using Solaris8
i tried changing only postgresql parameters
and time has increased of 10mn

i keep in mind your idea, we will soon upgraded to solaris 10

regards

Will

-----Message d'origine-----
De : J(dot)K(dot)Shah(at)Sun(dot)COM [mailto:J(dot)K(dot)Shah(at)Sun(dot)COM]
Envoyé : mardi 14 février 2006 22:47
À : FERREIRA, William (VALTECH)
Cc : Albert Cervera Areny; pgsql-performance(at)postgresql(dot)org
Objet : Re: [PERFORM] 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
>
>

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.