X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id A89EA52E8E for ; Mon, 25 Jul 2005 21:31:18 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 38776-02 for ; Tue, 26 Jul 2005 00:31:08 +0000 (GMT) Received: from mail.Mi8.com (d01gw01.mi8.com [63.240.6.47]) by svr1.postgresql.org (Postfix) with ESMTP id 8543352B57 for ; Mon, 25 Jul 2005 21:31:06 -0300 (ADT) Received: from 172.16.1.118 by mail.Mi8.com with ESMTP (- Welcome to Mi8 Corporation www.Mi8.com (D1)); Mon, 25 Jul 2005 20:31:03 -0400 X-Server-Uuid: 241911D6-425B-44B9-A073-E3FE0F8FC774 Received: from MI8NYCMAIL06.Mi8.com ([172.16.1.175]) by d01smtp03.Mi8.com with Microsoft SMTPSVC(6.0.3790.1830); Mon, 25 Jul 2005 20:31:03 -0400 Received: from 67.103.45.218 ([67.103.45.218]) by MI8NYCMAIL06.Mi8.com ( [172.16.1.219]) via Exchange Front-End Server mi8owa.mi8.com ( [172.16.1.104]) with Microsoft Exchange Server HTTP-DAV ; Mon, 25 Jul 2005 20:31:02 -0500 User-Agent: Microsoft-Entourage/11.1.0.040913 Date: Mon, 25 Jul 2005 17:31:02 -0700 Subject: Re: COPY insert performance From: "Luke Lonergan" To: "Chris Isaacson" , pgsql-performance@postgresql.org Message-ID: In-Reply-To: <07774C6E31D94A44A2A60E2085944F09071DF0@tbmail.tradebot.com> MIME-Version: 1.0 X-OriginalArrivalTime: 26 Jul 2005 00:31:03.0123 (UTC) FILETIME=[4DBD0230:01C59179] X-WSS-ID: 6EFB5BCD2B46231971-01-01 Content-Type: multipart/alternative; boundary=B_3205157462_11232091 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.718 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, HTML_30_40, HTML_FONT_BIG, HTML_MESSAGE, RCVD_NUMERIC_HELO X-Spam-Level: * X-Archive-Number: 200507/378 X-Sequence-Number: 13619 > This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3205157462_11232091 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Chris, You can try the Bizgres distribution of postgres (based on version 8.0.3), the COPY support is 30% faster as reported by OSDL (without indexes). This is due to very slow parsing within the COPY command, which is sped up using micro-optimized logic for parsing. There is a patch pending for the development version of Postgres which implements the same code, but you can use Bizgres and get it now instead of waiting for postgres 8.1 to come out. Also, Bizgres is QA tested with the enhanced features. Bizgres is a free / open source distribution of Postgres for Business Intelligence / Data Warehousing. Bizgres currently features postgres 8.0.3 plus these patches: * Bypass WAL when performing =B3CREATE TABLE AS SELECT=B2 * COPY is between 30% and 90% faster on machines with fast I/O * Enhanced support for data partitioning with partition elimination optimization=20 * Bitmap Scan support for multiple index use in queries and better low cardinality column performance * Improved optimization of queries with LIMIT See: http://www.bizgres.org for more. - Luke On 7/25/05 3:32 PM, "Chris Isaacson" wrote: > I need COPY via libpqxx to insert millions of rows into two tables. One = table > has roughly have as many rows and requires half the storage. In producti= on, > the largest table will grow by ~30M rows/day. To test the COPY performan= ce I > split my transactions into 10,000 rows. I insert roughly 5000 rows into = table > A for every 10,000 rows into table B. > =20 > Table A has one unique index: > =20 > "order_main_pk" UNIQUE, btree (cl_ord_id) > =20 > Table B has 1 unique index and 2 non-unique indexes: > =20 > "order_transition_pk" UNIQUE, btree (collating_seq) > "order_transition_ak2" btree (orig_cl_ord_id) > "order_transition_ak3" btree (exec_id) > =20 > My testing environment is as follows: > -Postgresql 8.0.1 > -libpqxx 2.5.0 > -Linux 2.6.11.4-21.7-smp x86_64 > -Dual Opteron 246 > -System disk (postgres data resides on this SCSI disk) - Seagate (ST3734= 53LC) > - 15K, 73 GB=20 > (http://www.seagate.com/cda/products/discsales/marketing/detail/0,1081,54= 9,00. > html) > -2nd logical disk - 10K, 36GB IBM SCSI (IC35L036UCDY10-0) - WAL reside on= this > disk > -NO RAID > =20 > PostgreSQL > Here are the results of copying in 10M rows as fast as possible: > (10K/transaction) > Total Time: 1129.556 s > Rows/sec: 9899.922 > Transaction>1.2s 225 > Transaction>1.5s 77 > Transaction>2.0s 4 > Max Transaction 2.325s > =20 > MySQL > I ran a similar test with MySQL 4.1.10a (InnoDB) which produced these res= ults: > (I used MySQL's INSERT INTO x VALUES (1,2,3)(4,5,6)(...,...,...) syntax) > (10K/transaction) > Total Time: 860.000 s > Rows/sec: 11627.91 > Transaction>1.2s 0 > Transaction>1.5s 0 > Transaction>2.0s 0 > Max Transaction 1.175s > =20 > Considering the configurations shown below, can anyone offer advice to cl= ose > the 15% gap and the much worse variability I'm experiencing. Thanks > =20 > My postgresql.conf has the following non-default values: > # ----------------------------- > # PostgreSQL configuration file > # ----------------------------- > listen_addresses =3D '*' # what IP interface(s) to listen on; > max_connections =3D 100 > #------------------------------------------------------------------------= --- > # RESOURCE USAGE (except WAL) > #------------------------------------------------------------------------= --- > shared_buffers =3D 65536 # min 16, at least max_connections*2, 8KB each > work_mem =3D 2048 # min 64, size in KB > maintenance_work_mem =3D 204800 # min 1024, size in KB > max_fsm_pages =3D 2250000 # min max_fsm_relations*16, 6 bytes each > bgwriter_delay =3D 200 # 10-10000 milliseconds between rounds > bgwriter_percent =3D 10 # 0-100% of dirty buffers in each round > bgwriter_maxpages =3D 1000 # 0-1000 buffers max per round > #------------------------------------------------------------------------= --- > # WRITE AHEAD LOG > #------------------------------------------------------------------------= --- > fsync =3D false # turns forced synchronization on or off > wal_buffers =3D 64 # min 4, 8KB each > checkpoint_segments =3D 40 # in logfile segments, min 1, 16MB each > checkpoint_timeout =3D 600 # range 30-3600, in seconds > #------------------------------------------------------------------------= --- > # QUERY TUNING > #------------------------------------------------------------------------= --- > effective_cache_size =3D 65536 # typically 8KB each > random_page_cost =3D 2 # units are one sequential page fetch cost > #------------------------------------------------------------------------= --- > # ERROR REPORTING AND LOGGING > #------------------------------------------------------------------------= --- > log_min_duration_statement =3D 250 # -1 is disabled, in milliseconds. > log_connections =3D true > log_disconnections =3D true > log_duration =3D true > log_line_prefix =3D '<%r%u%p%t%d%%' # e.g. '<%u%%%d> ' > # %u=3Duser name %d=3Ddatabase name > # %r=3Dremote host and port > # %p=3DPID %t=3Dtimestamp %i=3Dcommand tag > # %c=3Dsession id %l=3Dsession line number > # %s=3Dsession start timestamp %x=3Dtransaction id > # %q=3Dstop here in non-session processes > # %%=3D'%' > log_statement =3D 'none' # none, mod, ddl, all > #------------------------------------------------------------------------= --- > # RUNTIME STATISTICS > #------------------------------------------------------------------------= --- > # - Query/Index Statistics Collector - > stats_start_collector =3D true > stats_command_string =3D true > stats_block_level =3D true > stats_row_level =3D true > stats_reset_on_server_start =3D true > =20 > My MySQL my.ini has the following non default values: > innodb_data_home_dir =3D /var/lib/mysql/ > innodb_data_file_path =3D ibdata1:10M:autoextend > innodb_log_group_home_dir =3D /var/lib/mysql/ > innodb_log_arch_dir =3D /var/lib/mysql/ > # You can set .._buffer_pool_size up to 50 - 80 % > # of RAM but beware of setting memory usage too high > innodb_buffer_pool_size =3D 512M > innodb_additional_mem_pool_size =3D 64M > # Set .._log_file_size to 25 % of buffer pool size > innodb_log_file_size =3D 128M > innodb_log_buffer_size =3D 64M > innodb_flush_log_at_trx_commit =3D 1 > innodb_lock_wait_timeout =3D 50 > innodb_flush_method =3D O_DSYNC > max_allowed_packet =3D 16M > =20 > =20 > =20 >=20 --B_3205157462_11232091 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Re: [PERFORM] COPY insert performance Chris= ,

You can try the Bizgres distribution of postgres (based on version 8.0.3), = the COPY support is 30% faster as reported by OSDL (without indexes).  = This is due to very slow parsing within the COPY command, which is sped up u= sing micro-optimized logic for parsing.  There is a patch pending for t= he development version of Postgres which implements the same code, but you c= an use Bizgres and get it now instead of waiting for postgres 8.1 to come ou= t.  Also, Bizgres is QA tested with the enhanced features.

Bizgres is a free / open source distribution of Postgres for Business Intel= ligence / Data Warehousing.

Bizgres currently features postgres 8.0.3 plus these patches:
  • Bypass WAL when performing “CREATE TABLE AS SELECT= 221;
  • COPY is between 30% and 90% faster on machines with fast I/O
  • Enhanced support for data partitioning with partition eliminati= on optimization
  • Bitmap Scan support for multiple index use in queries and bette= r low cardinality column performance
  • Improved optimization of queries with LIMIT

See: http://www.bizgres.org for more.<= BR>
- Luke


On 7/25/05 3:32 PM, "Chris Isaacson" <cisaacson@tradebotsystem= s.com> wrote:

I need COPY via libpqxx to insert millions of rows into two tables.  O= ne table has roughly have as many rows and requires half the storage.  = In production, the largest table will grow by ~30M rows/day.  To test t= he COPY performance I split my transactions into 10,000 rows.  I insert= roughly 5000 rows into table A for every 10,000 rows into table B.
Table A has one unique index:
"order_main_pk" UNIQUE, btree (cl_ord_i= d)
Table B has 1 unique index and 2 non-unique index= es:
"order_transition_pk" UNIQUE, btree (co= llating_seq)
"order_transition_ak2" btree (orig_cl_ord_id)
"order_transition_ak3" btree (exec_id)
My testing environment is as follows:
-Postgresql 8.0.1
-libpqxx 2.5.0
-Linux 2.6.11.4-21.7-smp x86_64
-Dual Opteron 246
-System disk (postgres data resides on this SCSI disk) - Seag= ate (ST373453LC) - 15K, 73 GB (http://www.seagate.com/cda/p= roducts/discsales/marketing/detail/0,1081,549,00.html)
-2nd logical disk - 10K, 36GB IBM SCSI (IC35L036UCDY10-0) - = WAL reside on this disk
-NO RAID
 
PostgreS= QL
Here are the results of copying in 10M rows as fast as possible: (10K/t= ransaction)
Total Time:           &nb= sp;1129.556 s
Rows/sec:            = ; 9899.922
Transaction>1.2s    225
Transaction>1.5s     77
Transaction>2.0s      4
Max Transaction       2.325s
 
MySQL
I ran a similar test with MySQL 4.1.10a (InnoDB) which produced these r= esults: (I used MySQL's INSERT INTO x VALUES (1,2,3)(4,5,6)(...,...,...) syn= tax) (10K/transaction)
Total Time:         860.000 s
Rows/sec:        11627.91
Transaction>1.2s      0
Transaction>1.5s      0
Transaction>2.0s      0
Max Transaction       1.175s
 
Considering the configurations shown below, can anyone offer advice to clos= e the 15% gap and the much worse variability I'm experiencing.  Thanks<= BR>
My postgresql.conf has the following non-d= efault values:
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
listen_addresses =3D '*' # what IP interface(s) to listen on;
max_connections =3D 100
#--------------------------------------------------------------------------= -
# RESOURCE USAGE (except WAL)
#--------------------------------------------------------------------------= -
shared_buffers =3D 65536  # min 16, at least max_connections*2, 8KB each=
work_mem =3D 2048   # min 64, size in KB
maintenance_work_mem =3D 204800 # min 1024, size in KB
max_fsm_pages =3D 2250000  # min max_fsm_relations*16, 6 bytes each
bgwriter_delay =3D 200  # 10-10000 milliseconds between rounds
bgwriter_percent =3D 10  # 0-100% of dirty buffers in each round
bgwriter_maxpages =3D 1000 # 0-1000 buffers max per round
#--------------------------------------------------------------------------= -
# WRITE AHEAD LOG
#--------------------------------------------------------------------------= -
fsync =3D false   # turns forced synchronization on or off
wal_buffers =3D 64  # min 4, 8KB each
checkpoint_segments =3D 40 # in logfile segments, min 1, 16MB each
checkpoint_timeout =3D 600 # range 30-3600, in seconds
#--------------------------------------------------------------------------= -
# QUERY TUNING
#--------------------------------------------------------------------------= -
effective_cache_size =3D 65536 # typically 8KB each
random_page_cost =3D 2  # units are one sequential page fetch cost
#--------------------------------------------------------------------------= -
# ERROR REPORTING AND LOGGING
#--------------------------------------------------------------------------= -   
log_min_duration_statement =3D    250 # -1 is disabled, in mil= liseconds.
log_connections =3D true
log_disconnections =3D true
log_duration =3D true
log_line_prefix =3D '<%r%u%p%t%d%%'  # e.g. '<%u%%%d> '
    # %u=3Duser name %d=3Ddatabase name
    # %r=3Dremote host and port
    # %p=3DPID %t=3Dtimestamp %i=3Dcommand tag
    # %c=3Dsession id %l=3Dsession line number
    # %s=3Dsession start timestamp %x=3Dtransaction id
    # %q=3Dstop here in non-session processes
    # %%=3D'%'
log_statement =3D 'none'  # none, mod, ddl, all
#--------------------------------------------------------------------------= -
# RUNTIME STATISTICS
#--------------------------------------------------------------------------= -
# - Query/Index Statistics Collector -
stats_start_collector =3D true
stats_command_string =3D true
stats_block_level =3D true
stats_row_level =3D true
stats_reset_on_server_start =3D true
My MySQL my.ini has the following non defa= ult values:
innodb_data_home_dir =3D /var/lib/mysql/
innodb_data_file_path =3D ibdata1:10M:autoextend
innodb_log_group_home_dir =3D /var/lib/mysql/
innodb_log_arch_dir =3D /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size =3D 512M
innodb_additional_mem_pool_size =3D 64M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size =3D 128M
innodb_log_buffer_size =3D 64M
innodb_flush_log_at_trx_commit =3D 1
innodb_lock_wait_timeout =3D 50
innodb_flush_method =3D O_DSYNC
max_allowed_packet =3D 16M
 


--B_3205157462_11232091--