Improvements in Slony-I 2.2.0beta

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:

http://git.postgresql.org/gitweb/?p=slony1-engine.git;a=blob_plain;f=RELEASE;h=be03be66d8f39ee148b50dadf71ddbe20a1e5ad8;hb=e3e785c93d14b009abc8424fa7e53e8b75c0f098

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