On 03/11/2013 12:16 PM, Harald Leithner wrote:
> Am 10.03.2013, 00:26 Uhr, schrieb Paul J Stevens <[email protected]>:
>> 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.
> 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.
> 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.
> 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.
--
________________________________________________________________
Paul J Stevens pjstevns @ gmail, twitter, skype, linkedin
* Premium Hosting Services and Web Application Consultancy *
www.nfg.nl/[email protected]/+31.85.877.99.97
________________________________________________________________
_______________________________________________
DBmail mailing list
[email protected]
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
