Published on Tue, Jul 9, 2013
Thanks to Slony-I team for releasing Slony-I 2.2.0Beta with many enhancements on key areas. Few are mentioned here:
Event capturing protocol changed for performance (sl_log_1 / sl_log_2):
Earlier release, any DML(INSERT/UPDATE/DELETE) event data must store as SQL statement in two tables(sl_log_1/sl_log_2). Now, the protocol has completely modified to COPY format, as a result of this there’ll be sensible performance improvement like lower processing overhead, lower memory consumption, less query processing work on subscriber database in slon process. Check below, on a simple INSERT statement how the event has been captured in previous and latest version.
Previous version 2.1.2:
==> slony1_log_2_00000000000000000005.sql <==
-- start of Slony-I data
------------------------------------------------------------------
insert into "public"."stest" ("id") values ('103');
Latest version 2.2.0Beta:
==> slony1_log_2_00000000000000000006.sql <==
------------------------------------------------------------------
COPY "_rep220"."sl_log_archive" ( log_origin, log_txid,log_tableid,log_actionseq,log_tablenspname, log_tablerelname, log_cmdtype, log_cmdupdnc
ols,log_cmdargs) FROM STDIN;
1 565688 1 2 public stest I 0 {id,1000}
\.
DDL handling: In old version, DDL’s are treated as a unique slony event and stored in sl_event table with flag “DDL_SCRIPT” and there clearly no indication whether applied or not. Now, unique slony events are shifted from “sl_event” to “sl_log_script” and a flag to share with if it has successfully applied or not. Sl_log_script.log_cmdtype column indicates, “S”(upper) if EXECUTE SCRIPT issues and registered as event to utilize and “s” (lower) indicates that script execution completed. Check below, previous and latest version:
Previous version 2.1.2:
postgres=# select ev_origin,ev_type,ev_data1,ev_data2 from _newbuild.sl_event where ev_type ilike 'ddl_script';
ev_origin | ev_type | ev_data1 | ev_data2
-----------+------------+----------+-----------------------------------------
1 | DDL_SCRIPT | 1 | begin; +
| | | alter table stest add column name text;+
| | | end;
Latest version 2.2.0Beta:
postgres=# select * from _rep220.sl_log_script ;
log_origin | log_txid | log_actionseq | log_cmdtype | log_cmdargs
------------+----------+---------------+-------------+---------------------------------------------
1 | 681589 | 1 | S | {"alter table stest add column name text;"}
1 | 681589 | 2 | S | {" +
| | | | "}
1 | 681589 | 3 | s | {}
New 3 catalogs added to Slony-I
schema:
sl_apply_sync : This table
gives clear picture on how many events like DML’s, DDL’s applied so far
including the event applying frequence.
postgres=# select * from _rep220.sl_apply_stats ;
-[ RECORD 1 ]--------+---------------------------------
as_origin | 1
as_num_insert | 21
as_num_update | 0
as_num_delete | 0
as_num_truncate | 0
as_num_script | 1
as_num_total | 21
as_duration | 00:00:11.84
as_apply_first | 2013-06-16 22:43:18.866365+05:30
as_apply_last | 2013-06-17 03:18:13.324941+05:30
as_cache_prepare | 2
as_cache_hit | 19
as_cache_evict | 0
as_cache_prepare_max | 1
sl_log_script : Its another catalog
to catch an unique event like DDL’s issued with EXECUTE SCRIPT. Earlier
release, these events were captured in sl_log_1/sl_log_2 without any
status of execution of those events. This new catalog can facilitate
user to grasp concerning the DDL event details.
sl_failover_targets: The view
sl_failover_targets displays the valid failover targets for every
origin node.
postgres=# select * from _rep220.sl_failover_targets ;
set_id | set_origin | backup_id
--------+------------+-----------
1 | 1 | 2
(1 row)
Many more changes in new release, you can refer to the release notes:
Slony-I 2.2.0 beta 4 can be downloaded from:
http://www.slony.info/downloads/2.2/source/slony1-2.2.0.b4.tar.bz2
http://www.slony.info/downloads/2.2/source/slony1-2.2.0.b4-docs.tar.bz2
–Raghav