Am 27.03.2013, 13:53 Uhr, schrieb Paul J Stevens <p...@nfg.nl>:
On 03/11/2013 12:16 PM, Harald Leithner wrote:
Am 10.03.2013, 00:26 Uhr, schrieb Paul J Stevens <p...@nfg.nl>:
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;
What happens if a couple of users mark big mailboxes as 'all read' at
the same time.
You are updating 2 instead of 1 table that should not be a problem, imap
protocol can only set on flag per mail per request correct? If not its
even better because you have only n+1 updates if you can set multiple mail
flags in von update query.
START TRANSACTION
insert into dbmail_messages (message_idnr,...) VALUES(xyz, ...);
update dbmail_mailbox set msg_count=msg_count+1;
COMMIT;
Same scenario for copying messages.
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?
The write-lock contention on the mailbox table will become very large,
very fast.
That should not be a problem because innodb is row-locking (I think thats
the same for all supported databases), so only one user (maybe more for
shared mailboxes) can only change one row in dbmail_mailbox.
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.
This means we wouldn't have to touch the dbmail code. Other than that, I
still see the same scaling problems: for each changed message, update
the mailbox table.
correct the native code would be better if we can collect multiple update
statements for seen flag and only run one update query.
We have to be sure that not messages get counted that already have the
value for the flag...
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?
I run development sandboxes for that. All fully isolated. The imaptest
tool is very good at generating massif amounts of imap traffic. Dbmail
itself contains a couple of nice load-generators, esp:
test-scripts/loadimap.py and test-scripts/testlmtp.py.
Thats good ;-)
--
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
