PostgreSQL 9.0.1 on Windows performance tunning help please

From: tuanhoanganh <hatuan05(at)gmail(dot)com>
To: pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: PostgreSQL 9.0.1 on Windows performance tunning help please
Date: 2011-08-05 16:43:33
Message-ID: CAJg-yaMyR03J43RLMToB27YdtK7w-vguot2fYKz+0sFHVRsjwA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

I have postgresql 9.0.1 on windows 2003 ent with 6GB ram, 4 disk SATA RAID
10.
I am running SymmetricDS to replication over WAN. But yesterday there was a
big problem, i updated alot of rows and query to gap data of SymmetricDS run
verry very slowly.

Here is my postgresql.conf to tunning PostgreSQL
effective_cache_size = 4GB
work_mem = 2097151
shared_buffers = 1GB

Here is query :
explain analyze select d.data_id, d.table_name, d.event_type, d.row_data,
d.pk_data, d.old_data, d.create_time, d.trigger_hist_id, d.channel_id,
d.transaction_id, d.source_node_id, d.external_data, '' from sym_data d
inner join sym_data_gap g on g.status='GP' and d.data_id between g.start_id
and g.end_id where d.channel_id='sale_transaction' order by d.data_id asc;

And here is result :
Nested Loop (cost=0.00..1517515125.95 rows=26367212590 width=1403) (actual
time=14646.390..7745828.163 rows=2764140 loops=1)
-> Index Scan using sym_data_pkey on sym_data d (cost=0.00..637148.72
rows=3129103 width=1403) (actual time=71.989..55643.665 rows=3124631
loops=1)
Filter: ((channel_id)::text = 'sale_transaction'::text)
-> Index Scan using sym_data_gap_pkey on sym_data_gap g
(cost=0.00..358.37 rows=8426 width=8) (actual time=2.459..2.459 rows=1
loops=3124631)
Index Cond: ((d.data_id >= g.start_id) AND (d.data_id <= g.end_id))
Filter: (g.status = 'GP'::bpchar)
Total runtime: 7746577.478 ms

Here is table sym_data it have 437319 rows with data_id between start_id and
end_id of sym_data_gap has status = 'GP'

CREATE TABLE sym_data
(
data_id serial NOT NULL,
table_name character varying(50) NOT NULL,
event_type character(1) NOT NULL,
row_data text,
pk_data text,
old_data text,
trigger_hist_id integer NOT NULL,
channel_id character varying(20),
transaction_id character varying(255),
source_node_id character varying(50),
external_data character varying(50),
create_time timestamp without time zone,
CONSTRAINT sym_data_pkey PRIMARY KEY (data_id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE sym_data OWNER TO postgres;

-- Index: idx_d_channel_id

-- DROP INDEX idx_d_channel_id;

CREATE INDEX idx_d_channel_id
ON sym_data
USING btree
(data_id, channel_id);

And here is sym_data_gap table it have 57838 rows have status = 'GP'

CREATE TABLE sym_data_gap
(
start_id integer NOT NULL,
end_id integer NOT NULL,
status character(2),
create_time timestamp without time zone NOT NULL,
last_update_hostname character varying(255),
last_update_time timestamp without time zone NOT NULL,
CONSTRAINT sym_data_gap_pkey PRIMARY KEY (start_id, end_id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE sym_data_gap OWNER TO postgres;

-- Index: idx_dg_status

-- DROP INDEX idx_dg_status;

CREATE INDEX idx_dg_status
ON sym_data_gap
USING btree
(status);

Because the query run very slowly so data is not replication between to
distance. Please help me.

Sorry for my English
Tuan Hoang ANh

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Shaun Thomas 2011-08-05 17:09:57 Re: Postgres 8.4 memory related parameters
Previous Message Kevin Grittner 2011-08-05 15:05:43 Re: Postgres 8.4 memory related parameters