[PERFORM] Insert performance with composite index
Hi, I am trying to tune my libpq program for insert performance. When I tried inserting 1M rows into a table with a Primary Key, it took almost 62 seconds. After adding a composite index of 2 columns, the performance degrades to 125 seconds. I am using COPY to insert all data in 1 transaction. the table definition is CREATE TABLE ABC ( event integer, innodeid character varying(80), innodename character varying(80), sourceid character varying(300), intime timestamp(3) without time zone, outnodeid character varying(80), outnodename character varying(80), destinationid character varying(300), outtime timestamp(3) without time zone, bytes integer, cdrs integer, tableindex integer NOT NULL, noofsubfilesinfile integer, recordsequenceintegerlist character varying(1000), CONSTRAINT tableindex_pkey PRIMARY KEY (tableindex) ) the index definition is CREATE INDEX PK_AT2 ON ABC USING btree (event, tableindex) TABLESPACE sample; Any tip to increase the insert performance in this case? It would also be helpful if someone can send comprehensive libpq programming guide for PG 9.x. Online doc of libpq is not much helpful for a newbie like me. Best Regards, Divakar
Re: [PERFORM] Insert performance with composite index
Hi Marti, Thanks for your tips. i will try those. I am on Solaris Sparc 5.10 Best Regards, Divakar From: Marti Raudsepp ma...@juffo.org To: Divakar Singh dpsma...@yahoo.com Cc: pgsql-performance@postgresql.org Sent: Mon, November 1, 2010 6:23:17 PM Subject: Re: [PERFORM] Insert performance with composite index On Mon, Nov 1, 2010 at 14:49, Divakar Singh dpsma...@yahoo.com wrote: I am trying to tune my libpq program for insert performance. When I tried inserting 1M rows into a table with a Primary Key, it took almost 62 seconds. After adding a composite index of 2 columns, the performance degrades to 125 seconds. This sounds a lot like the bottleneck I was hitting. What Linux kernel version are you running? If it's 2.6.33 or later, see: http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server#wal_sync_method_wal_buffers http://archives.postgresql.org/pgsql-performance/2010-10/msg00602.php Regards, Marti
Re: [PERFORM] Insert performance with composite index
On Mon, Nov 1, 2010 at 14:49, Divakar Singh dpsma...@yahoo.com wrote: I am trying to tune my libpq program for insert performance. When I tried inserting 1M rows into a table with a Primary Key, it took almost 62 seconds. After adding a composite index of 2 columns, the performance degrades to 125 seconds. This sounds a lot like the bottleneck I was hitting. What Linux kernel version are you running? If it's 2.6.33 or later, see: http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server#wal_sync_method_wal_buffers http://archives.postgresql.org/pgsql-performance/2010-10/msg00602.php Regards, Marti -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Insert performance with composite index
On Mon, Nov 1, 2010 at 14:56, Divakar Singh dpsma...@yahoo.com wrote: Thanks for your tips. i will try those. I am on Solaris Sparc 5.10 Sorry, I assumed you were running Linux. But still it could be the same problem as I had. Be careful changing your wal_sync_method, as it has the potential to corrupt your database. I have no experience with Solaris. For what it's worth, Jignesh Shah recommends using wal_sync_method=fsync on Solaris: http://blogs.sun.com/jkshah/entry/postgresql_on_solaris_better_use http://blogs.sun.com/jkshah/entry/postgresql_wal_sync_method_and Regards, Marti -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Insert performance with composite index
2010/11/1 Divakar Singh dpsma...@yahoo.com: Hi, I am trying to tune my libpq program for insert performance. When I tried inserting 1M rows into a table with a Primary Key, it took almost 62 seconds. After adding a composite index of 2 columns, the performance degrades to 125 seconds. I am using COPY to insert all data in 1 transaction. the table definition is CREATE TABLE ABC ( event integer, innodeid character varying(80), innodename character varying(80), sourceid character varying(300), intime timestamp(3) without time zone, outnodeid character varying(80), outnodename character varying(80), destinationid character varying(300), outtime timestamp(3) without time zone, bytes integer, cdrs integer, tableindex integer NOT NULL, noofsubfilesinfile integer, recordsequenceintegerlist character varying(1000), CONSTRAINT tableindex_pkey PRIMARY KEY (tableindex) ) the index definition is CREATE INDEX PK_AT2 ON ABC USING btree (event, tableindex) TABLESPACE sample; Indexing twice the same column is useless. (perhaps move your PK to the tablespace 'sample' is good too ?) Any tip to increase the insert performance in this case? If you create or truncate table then copy to it, you should create index after the copy order. It would also be helpful if someone can send comprehensive libpq programming guide for PG 9.x. Online doc of libpq is not much helpful for a newbie like me. Best Regards, Divakar -- Cédric Villemain 2ndQuadrant http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Insert performance with composite index
Hi, On Monday 01 November 2010 13:49:14 Divakar Singh wrote: When I tried inserting 1M rows into a table with a Primary Key, it took almost 62 seconds. After adding a composite index of 2 columns, the performance degrades to 125 seconds. I am using COPY to insert all data in 1 transaction. Without seeing your config its hard to suggest anything here. Did you do basic tuning of your pg installation? wal_buffers, shared_buffers, checkpoint_segments, maintenance_work_mem are likely most relevant for that specific case. Andres -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Insert performance with composite index
On Monday 01 November 2010 15:08:10 Divakar Singh wrote: here are my parameters: Which pg version is that? -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Insert performance with composite index
I am using 9.0.1 Best Regards, Divakar From: Andres Freund and...@anarazel.de To: Divakar Singh dpsma...@yahoo.com Cc: pgsql-performance@postgresql.org Sent: Mon, November 1, 2010 7:44:31 PM Subject: Re: [PERFORM] Insert performance with composite index On Monday 01 November 2010 15:08:10 Divakar Singh wrote: here are my parameters: Which pg version is that?
Re: [PERFORM] Insert performance with composite index
On Monday 01 November 2010 15:16:49 Divakar Singh wrote: I am using 9.0.1 Either thats not true or you cargo culted loads of your config from a significantly older pg version. Things like: #bgwriter_delay = 200# 10-1 milliseconds between rounds bgwriter_lru_percent = 0# 0-100% of LRU buffers scanned/round #bgwriter_lru_maxpages = 5# 0-1000 buffers max written/round #bgwriter_all_percent = 0.333# 0-100% of all buffers scanned/round bgwriter_all_maxpages = 0# 0-1000 buffers max written/round make me very suspicious. As I said, I would check the variables I referenced in my first post... Andres -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Insert performance with composite index
Do you mean these parameters have been removed starting 9.X? As I see on http://www.network-theory.co.uk/docs/postgresql/vol3/BackgroundWriter.html ,these parameters were added starting from 8.0 right? Best Regards, Divakar From: Andres Freund and...@anarazel.de To: Divakar Singh dpsma...@yahoo.com Cc: pgsql-performance@postgresql.org Sent: Mon, November 1, 2010 7:50:59 PM Subject: Re: [PERFORM] Insert performance with composite index On Monday 01 November 2010 15:16:49 Divakar Singh wrote: I am using 9.0.1 Either thats not true or you cargo culted loads of your config from a significantly older pg version. Things like: #bgwriter_delay = 200# 10-1 milliseconds between rounds bgwriter_lru_percent = 0# 0-100% of LRU buffers scanned/round #bgwriter_lru_maxpages = 5# 0-1000 buffers max written/round #bgwriter_all_percent = 0.333# 0-100% of all buffers scanned/round bgwriter_all_maxpages = 0# 0-1000 buffers max written/round make me very suspicious. As I said, I would check the variables I referenced in my first post... Andres
Re: [PERFORM] Insert performance with composite index
On Monday 01 November 2010 15:28:19 Divakar Singh wrote: Do you mean these parameters have been removed starting 9.X? As I see on http://www.network-theory.co.uk/docs/postgresql/vol3/BackgroundWriter.html ,these parameters were added starting from 8.0 right? No, I mean setting to 0 is a bit of a strange value in many situations. And you have comments like: #max_fsm_pages = 2# min max_fsm_relations*16, 6 bytes each #max_fsm_relations = 1000# min 100, ~70 bytes each Which reference config options which do not exist anymore. And you have shared_buffers = 81920 Which indicates that you started from 8.1/8.2 or so... Andres -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] A query become very slow after upgrade from 8.1.10 to 8.4.5
Hi Sorry, my previous post haven't shown in this list, so I repost this one. I have a sql become very slow after upgrade to 8.4.5. The table creation sql like this. begin; CREATE TABLE t_a ( id INT NOT NULL PRIMARY KEY ); CREATE TABLE t_b ( id INT NOT NULL PRIMARY KEY ); CREATE TABLE t_c ( id INT NOT NULL PRIMARY KEY, flag boolean ); INSERT INTOt_a SELECT s FROMgenerate_series(1, 600) s; INSERT INTOt_b SELECT s FROMgenerate_series(1, 3000) s; SELECT SETSEED(0.1); INSERT INTOt_c SELECT s, RANDOM() 0.5 FROMgenerate_series(1, 12000) s; -- insert some id not in t_b into t_a INSERT INTO t_a values( 2); ANALYZE t_a; ANALYZE t_b; ANALYZE t_c; end; The query sql is like this. SELECT t_a.id FROM t_a WHERE EXISTS ( SELECT t_b.id FROM t_b, t_c WHERE t_b.id = t_a.id AND t_c.flag = 'f') I extract this part form a big query.I known this query is not very good.The query plan is different between 8.1.10 and 8.4.5, 8.1.10 use a index scan, 8.4.5 use two table scan. PostgreSQL 8.1.10 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.4 (mingw special) Seq Scan on t_a (cost=0.00..34.67 rows=300 width=4) (actual time=0.025..5.350 rows=600 loops=1) Filter: (subplan) SubPlan - Nested Loop (cost=0.00..248.44 rows=6042 width=4) (actual time=0.007..0.007 rows=1 loops=601) - Index Scan using t_b_pkey on t_b (cost=0.00..3.02 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=601) Index Cond: (id = $0) - Seq Scan on t_c (cost=0.00..185.00 rows=6042 width=0) (actual time=0.001..0.001 rows=1 loops=600) Filter: (NOT flag) Total runtime: 5.574 ms PostgreSQL 8.4.5, compiled by Visual C++ build 1400, 32-bit Nested Loop Semi Join (cost=0.00..134044.44 rows=601 width=4) (actual time=0.033..17375.045 rows=600 loops=1) Join Filter: (t_a.id = t_b.id) - Seq Scan on t_a (cost=0.00..9.01 rows=601 width=4) (actual time=0.008..0.172 rows=601 loops=1) - Nested Loop (cost=0.00..447282.00 rows=18126000 width=4) (actual time=0.011..20.922 rows=30460 loops=601) - Seq Scan on t_c (cost=0.00..174.00 rows=6042 width=0) (actual time=0.004..0.011 rows=11 loops=601) Filter: (NOT flag) - Seq Scan on t_b (cost=0.00..44.00 rows=3000 width=4) (actual time=0.004..0.652 rows=2756 loops=6642) Total runtime: 17375.247 ms If some t_a.id not in t_b.id 8.4.5 will become very slow. I confirmed this behavior on default configuration. Regards, Yao -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
