Tuesday, July 9, 2013

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

2 comments:

Anonymous said...

Hello,
We are final year Computer Engineering students from Pune. Our final year project is based on PostgreSQL. The aim of our project is to enhance query execution in PostgreSQL by adding columnar storage support to it. We have a lot of doubts currently, so can you please help us? This is a link of my Facebook profile. Kindly contact me on https://www.facebook.com/SwapnilBhoite28

Raghavendra said...

Great to know you have chosen PostgreSQL as your final year projects. However, I would recommend to publish your queries on pgsql-general or slony-general forums for faster response. Also you can send queries to ragavendra.dba@gmail.com (expect late replies).

Post a Comment