понедельник, 30 ноября 2009 г.

Trigger-based PostgreSQL to SQLite online replication

For some reasons may be useful online replication from PostgreSQL database to SQLite database or databases. I write this as set of pltclu procedures for my PostgreSQL database like to


CREATE OR REPLACE FUNCTION document_status_sqlite3()
RETURNS "trigger" AS
$BODY$

set t1 [clock clicks]
load /usr/share/tcltk/tcl8.5/sqlite3/libtclsqlite3.so sqlite3
set db_file /var/www/project_name/res/dataset/work.db
sqlite3 db $db_file
db timeout 4000
# db eval {PRAGMA journal_mode = PERSIST}
if { $TG_op eq "INSERT" } {
set save_date [string range $NEW(save_date) 0 18]
db eval {insert into document_status (save_date,document_id,
user_id,value,
is_last,is_last_status,is_first)
values (julianday($save_date,'-3 hour'),$NEW(document_id),
$NEW(user_id),$NEW(value),
case when $NEW(is_last)='t' then 1 else 0 end,
case when $NEW(is_last_status)='t' then 1 else 0 end,
case when $NEW(is_first)='t' then 1 else 0 end)}
} elseif { $TG_op eq "UPDATE"} {

} elseif { $TG_op eq "DELETE"} {

}
db close
set t2 [clock clicks]
set t [expr ($t2-$t1)/1000]
elog NOTICE "SQLITE trigger attribute: $t ms"
return OK

$BODY$
LANGUAGE 'pltclu' VOLATILE SECURITY DEFINER;
ALTER FUNCTION document_status_sqlite3() OWNER TO offline;


CREATE TRIGGER trigger_document_status_sqlite3
AFTER INSERT OR UPDATE OR DELETE
ON document_status
FOR EACH ROW
EXECUTE PROCEDURE document_status_sqlite3();


The SQLite db is placed on single WD VelociRaptor 10000 rpm drive.


$ tail -f /var/log/postgresql-8.1/current -n 2000|grep "SQLITE trigger attribute"
2009-11-30 13:54:10 MSK NOTICE: SQLITE trigger attribute: 0 ms
2009-11-30 13:54:10 MSK NOTICE: SQLITE trigger attribute: 0 ms
2009-11-30 13:54:10 MSK NOTICE: SQLITE trigger attribute: 23 ms
2009-11-30 13:54:35 MSK NOTICE: SQLITE trigger attribute: 0 ms
2009-11-30 13:54:35 MSK NOTICE: SQLITE trigger attribute: 0 ms
2009-11-30 13:54:35 MSK NOTICE: SQLITE trigger attribute: 13 ms
2009-11-30 13:54:41 MSK NOTICE: SQLITE trigger attribute: 0 ms
2009-11-30 13:54:41 MSK NOTICE: SQLITE trigger attribute: 10 ms
2009-11-30 13:54:43 MSK NOTICE: SQLITE trigger attribute: 6 ms
2009-11-30 13:54:54 MSK NOTICE: SQLITE trigger attribute: 0 ms
2009-11-30 13:54:54 MSK NOTICE: SQLITE trigger attribute: 0 ms
2009-11-30 13:54:54 MSK NOTICE: SQLITE trigger attribute: 11 ms
2009-11-30 13:55:20 MSK NOTICE: SQLITE trigger attribute: 0 ms
2009-11-30 13:55:20 MSK NOTICE: SQLITE trigger attribute: 8 ms
2009-11-30 13:55:48 MSK NOTICE: SQLITE trigger attribute: 0 ms
2009-11-30 13:55:48 MSK NOTICE: SQLITE trigger attribute: 14 ms
2009-11-30 13:56:20 MSK NOTICE: SQLITE trigger attribute: 6 ms
2009-11-30 13:56:57 MSK NOTICE: SQLITE trigger attribute: 6 ms


The reports building on SQLite is much faster on the same hardware. We can place SQLite db to tmpfs (in-memory temp storage) for increasing write TPS (transactions per seconds) but on my hardware SQLite has the similar write TPS as PostgreSQL 8.1 (with fsync enabled in postgresql.conf).

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


(C) Alexey Pechnikov aka MBG, mobigroup.ru