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
