Am 10.03.2013, 00:26 Uhr, schrieb Paul J Stevens <p...@nfg.nl>:
On 03/07/2013 10:22 AM, Harald Leithner wrote:
Hmm, SQL...
Whats with triggers and holding this information hot?
for example adding 3 columns to the mailbox table with msg_count,
msg_count_seen, msg_count_recent
adding a trigger to the messages table to updated this values on update
in the messages table.
I've tried that. It will very quickly degrade into write-storms and
subsequent deadlocks in the sql server. It didn't scale at all in my
attempts and I didn't see a way around it. Stored procedures are
supposedly very effective on postgresql.
2 approaches:
useing TRANSACTION in DBMAIL every time the flag seen/recent/status is
updated or a new row is created something like this:
START TRANSACTION
update dbmail_messages set seen_flag = 1 WHERE message_idnr=x;
update dbmail_mailbox set msg_count_seen=msg_count_seen+1;
COMMIT;
START TRANSACTION
insert into dbmail_messages (message_idnr,...) VALUES(xyz, ...);
update dbmail_mailbox set msg_count=msg_count+1;
COMMIT;
START TRANSACTION
update dbmail_messages set status = 2 WHERE message_idnr=x;
update dbmail_mailbox set msg_count=msg_count-1;
COMMIT;
some query like this, that should not create any problems or I'm wrong?
2nd, the same logic in triggers some things like this (pseudo code):
CREATE
TRIGGER messages_update AFTER UPDATE
ON messages FOR EACH ROW BEGIN
IF (old.status <> new.status) THEN
IF (new.status > 1) THEN
update dbmail_mailbox set msg_count=msg_count-1;
END IF
END IF
IF (old.seen_flag <> new.seen_flag) THEN
IF (new.seen_flag = 1) THEN
update dbmail_mailbox set msg_count_seen=msg_count_seen+1;
ELSE
update dbmail_mailbox set msg_count_seen=msg_count_seen-1;
END IF
END IF
END;
some procedure like this.
Another thing are the VIEWs in MySQL are not MATERIALIZED so there is no
performance gain using it, using a table with triggers would bring much
better performance.
I didn't even know materialized views existed.
I would like that only materialized views exists ;-)
Or is there a reason not using this, for example your sql schema for
oracles doesn't use MATERIALIZED VIEWs.
I don't maintain the oracle tables.
no problem ;-)
Personally I've come to the point were I'd prefer to first define an API
to use that allows swapping in and out different solutions, be it in the
required local database, or in optional external facilities (sharded
sql, key-value stores, solr, varnish).
Some of the current hotspots are easier dealt with than others. The one
you've located (mailbox counters) looks like a hard nut to crack because
of the required atomicity.
.... I always like to postpone working on hard problems, and look for
similar but simpler ones until I feel ready to tackle the big one ...
cheers!
I can understand you completely I do it the same, do you have a test
environment (like dedicated hw or vm) where I could connect and help you
test?
If not maybe I could supply a vm for this.
--
Harald Leithner
ITronic
Vogelweidplatz 12, 1150 Wien, Austria
Tel: +43-1-786 23 88
Fax: +43-1-98 52 077
Mobil: +43-699-123 78 4 78
Mail: leith...@itronic.at | itronic.at
_______________________________________________
DBmail mailing list
DBmail@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
