суббота, 12 декабря 2009 г.

sqlite3-rdiff: master-slave replication for SQLite

Link: http://mobigroup.ru/files/sqlite-ext/sqlite3-rdiff

Depends: tcl 8.5, sqlite3, murmurhash SQLite extension

I'm glad to annonce the sqlite3-diff utility for SQLite replication. Are used the ROWID value as unique key of row and murmurhash for build signatures for each row. It's enough for master-slave replication. The INTEGER PRIMARY KEY is not mandatory becouse sqlite3-rdiff may store the ROWID values.

The signature and delta files are valid SQLite3 databases too. They can be dumped/restored, updated and analyzed by any SQLite3 client application.

Hash collisions are resolved by using unique salt for each signature. So after first sync the theoretical frequency of collision (when any record is changed but the hash is same) is about 10^-9, after second sync - 10^-18, after N syncs - 10^-N*9. The salt is unix epoch time which is saved by using "PRAGMA user_version" in signature and delta databases.

Note: the master-master replication is now unsupported becouse is can't be so universal and are needed some conflict-resolution strategies. But it possible with uuid extension to have really unique identifier for each record and use it as key for master-master replication (and of course the ROWID values must be ignored). But the signature and delta files will be too big.

Usage

NAME

sqlite3-rdiff - compute and apply signature-based row differences for SQLite3 databases

SYNOPSYS
sqlite3-rdiff [options] signature old-file signature-file

sqlite3-rdiff [options] delta signature-file new-file delta-file

sqlite3-rdiff [options] patch old-file delta-file result-file


DESCRIPTION

Signature is solving by murmurhash2 algorithm for full row. Hash collision is resolved by using unique salt for each replication session.

OPTIONS:

--table-name regular expression for table names, use ? as any single symbol and % for any set of symbols.

AUTHOR
Alexey Pechnikov <pechnikov@mobigroup.ru>

SEE ALSO
The main rdiff-backup web page is at http://geomapx.blogspot.com/2009/12/sqlite3-rdiff-master-slave-replication.html.


Example.

The test on my Core2 Duo laptop.


# add test triggers to block table inserts
# the replication utility may disable triggers when patching database
$ sqlite3 2009-11-01.db "create trigger telephony_log_i before insert on telephony_log \
begin select raise(ABORT,'trigger fire');end"

# make slave database with some changes
$ cp 2009-11-01.db 2009-11-01.db.slave
$ sqlite3 2009-11-01.db.slave "delete from telephony_log where rowid%100000=0;"
$ sqlite3 2009-11-01.db.slave "delete from telephony_log_error where rowid%10=0;"

# how many rows are deleted in previous commands?
$ sqlite3 2009-11-01.db "select count(*) from telephony_log where rowid%100000=0;"
14
$ sqlite3 2009-11-01.db "select count(*) from telephony_log_error where rowid%10=0;"
41


$ time ./sqlite3-rdiff signature 2009-11-01.db.slave 2009-11-01.db.signature
signature 2009-11-01.db.slave 2009-11-01.db.signature --table-name %
=1465379 telephony_log
=303709 telephony_log_rating
=377 telephony_log_error
=140 telephony_log_counter

real 0m40.361s
user 0m28.850s
sys 0m0.936s


$ time ./sqlite3-rdiff delta 2009-11-01.db.signature 2009-11-01.db 2009-11-01.db.delta
delta 2009-11-01.db.signature 2009-11-01.db 2009-11-01.db.delta --table-name %
-0 +14 telephony_log
-0 +0 telephony_log_rating
-0 +41 telephony_log_error
-0 +0 telephony_log_counter

real 1m50.502s
user 1m34.338s
sys 0m1.268s


$ time ./sqlite3-rdiff patch 2009-11-01.db.slave 2009-11-01.db.delta 2009-11-01.db.replicated
patch 2009-11-01.db.slave 2009-11-01.db.delta 2009-11-01.db.replicated --table-name %
-0 +14 telephony_log (2 triggers disabled)
-0 +0 telephony_log_rating (0 triggers disabled)
-0 +41 telephony_log_error (0 triggers disabled)
-0 +0 telephony_log_counter (0 triggers disabled)

real 0m24.624s
user 0m0.168s
sys 0m2.764s

$ ls -lh|grep 2009-11-01.db|awk '{print $5 "\t" $9}'
342M 2009-11-01.db
44K 2009-11-01.db.delta
342M 2009-11-01.db.replicated
23M 2009-11-01.db.signature
342M 2009-11-01.db.slave


We can check the success of replication by starting replication again. There are no new changes in replicated database and so the we may get the empty patch.


$ ./sqlite3-rdiff signature 2009-11-01.db.replicated 2009-11-01.db.signature2
signature 2009-11-01.db.replicated 2009-11-01.db.signature2 --table-name %
=1465393 telephony_log
=303709 telephony_log_rating
=418 telephony_log_error
=140 telephony_log_counter

$ ./sqlite3-rdiff delta 2009-11-01.db.signature2 2009-11-01.db 2009-11-01.db.delta2
delta 2009-11-01.db.signature2 2009-11-01.db 2009-11-01.db.delta2 --table-name %
-0 +0 telephony_log
-0 +0 telephony_log_rating
-0 +0 telephony_log_error
-0 +0 telephony_log_counter

$ ./sqlite3-rdiff patch 2009-11-01.db.replicated 2009-11-01.db.delta2 2009-11-01.db.replicated2
patch 2009-11-01.db.replicated 2009-11-01.db.delta2 2009-11-01.db.replicated2 --table-name %
-0 +0 telephony_log (1 triggers disabled)
-0 +0 telephony_log_rating (0 triggers disabled)
-0 +0 telephony_log_error (0 triggers disabled)
-0 +0 telephony_log_counter (0 triggers disabled)


All right, the patch is empty.

Upd.

The test on the Core Quad desktop with single 10 000 rpm HDD. Database work.db.copy is used for some tests and has different schema but the sqlite3-rdiff work fine on it.


$ time ./sqlite3-rdiff signature work.db.copy work.db.signature
signature work.db.copy work.db.signature --table-name %
=7 system_config
=160 center
=7 role
=1 macroregion
=988 point
=7 region
=1841 user
=2863845 document_status
=25 comment
=250561 document_comment
=0 operation
=16 datatype
=7 type_template
=51 template
=370 template_field
=510515 document
=1591502 document_field
=755659 document_file
=7329 document_photo
=16 status_switch
=486684 message
=7 system_config_history
=160 center_history
=7 role_history
=1 macroregion_history
=988 point_history
=7 region_history
=1841 user_history
=2863845 document_status_history
=25 comment_history
=250561 document_comment_history
=0 operation_history
=16 datatype_history
=7 type_template_history
=51 template_history
=370 template_field_history
=510515 document_history
=1591502 document_field_history
=755659 document_file_history
=7329 document_photo_history
=16 status_switch_history

real 1m58.328s
user 0m53.179s
sys 0m4.604s


$ time ./sqlite3-rdiff delta work.db.signature work.db work.db.delta
delta work.db.signature work.db work.db.delta --table-name %
-0 +0 system_config
-0 +0 center
-0 +0 role
-0 +0 macroregion
-0 +0 point
-0 +0 region
-0 +0 user
-995 +4316 document_status
-0 +0 comment
-205 +629 document_comment
-0 +0 operation
-0 +0 datatype
-0 +0 type_template
-0 +0 template
-0 +0 template_field
-0 +820 document
-0 +1914 document_field
-0 +1356 document_file
-0 +7 document_photo
-0 +0 status_switch
-0 +4445 message
-0 +0 system_config_history
-0 +0 center_history
-0 +0 role_history
-0 +0 macroregion_history
-0 +0 point_history
-0 +0 region_history
-0 +0 user_history
-0 +6147 document_status_history
-0 +0 comment_history
-0 +630 document_comment_history
-0 +0 operation_history
-0 +0 datatype_history
-0 +0 type_template_history
-0 +0 template_history
-0 +0 template_field_history
-0 +820 document_history
-0 +1914 document_field_history
-0 +1356 document_file_history
-0 +7 document_photo_history
-0 +0 status_switch_history

real 5m11.592s
user 3m23.989s
sys 0m5.704s


$ time ./sqlite3-rdiff patch work.db.copy work.db.delta work.db.copy
patch work.db.copy work.db.delta work.db.copy --table-name %
-0 +0 system_config (3 triggers disabled)
-0 +0 center (3 triggers disabled)
-0 +0 role (3 triggers disabled)
-0 +0 macroregion (3 triggers disabled)
-0 +0 point (3 triggers disabled)
-0 +0 region (3 triggers disabled)
-0 +0 user (3 triggers disabled)
-995 +4316 document_status (3 triggers disabled)
-0 +0 comment (3 triggers disabled)
-205 +629 document_comment (3 triggers disabled)
-0 +0 operation (3 triggers disabled)
-0 +0 datatype (3 triggers disabled)
-0 +0 type_template (3 triggers disabled)
-0 +0 template (3 triggers disabled)
-0 +0 template_field (3 triggers disabled)
-0 +820 document (3 triggers disabled)
-0 +1914 document_field (3 triggers disabled)
-0 +1356 document_file (3 triggers disabled)
-0 +7 document_photo (3 triggers disabled)
-0 +0 status_switch (3 triggers disabled)
-0 +4445 message (0 triggers disabled)
system_config_versioning
center_versioning
role_versioning
macroregion_versioning
point_versioning
region_versioning
user_versioning
document_status_versioning
comment_versioning
document_comment_versioning
operation_versioning
datatype_versioning
type_template_versioning
template_versioning
template_field_versioning
document_versioning
document_field_versioning
document_file_versioning
document_photo_versioning
status_switch_versioning

real 0m0.726s
user 0m0.500s
sys 0m0.124s


$ ls -lh|grep work.db|awk '{print $5 "\t" $8}'
1.5G work.db
2.0G work.db.copy
4.0M work.db.delta
159M work.db.signature

Комментариев нет:


(C) Alexey Pechnikov aka MBG, mobigroup.ru