Mailing List Archive

This also underscores the need for transactions, otherwise it would be
possible for two messages to be inserted at almost the same time and lose
count of the size of one of those messages. A full recalculation as part of
the maintenance script might tide us over in the mean time...

Aaron


Ilja Booij <ilja@ic-s.nl> said:

> I've made some minor adjustments to the way quota are handled. Or
> rather, to the way that a user's quotum is updated. Instead of using
> db_calculate_quotum_used() after every insert, I've now used a new
> function db_add_quotum_used() which adds the message size to the new
> quotum.
>
> This is all fine, and it works, except for the fact that the
> curmail_size is not lowered again on deletion of a message. I'll fix
> that tomorrow. :)
>
> This stuff should gives us some more speed (less complex queries),
> especially on message insertion.
>
> Ilja
>
> _______________________________________________
> Dbmail-dev mailing list
> Dbmail-dev@dbmail.org
> http://twister.fastxs.net/mailman/listinfo/dbmail-dev
>



--

Full recalculation is indeed part of the maintenance script. When doing
this stuff, the need for transactions occured to me.. I guess the
maintenance program can serve as for a while, after which we should
really use transactions (and possibly other ACID related features).


Ilja



Aaron Stone wrote:

> This also underscores the need for transactions, otherwise it would be
> possible for two messages to be inserted at almost the same time and lose
> count of the size of one of those messages. A full recalculation as part of
> the maintenance script might tide us over in the mean time...
>
> Aaron
>
>
> Ilja Booij <ilja@ic-s.nl> said:
>
>
>>I've made some minor adjustments to the way quota are handled. Or
>>rather, to the way that a user's quotum is updated. Instead of using
>>db_calculate_quotum_used() after every insert, I've now used a new
>>function db_add_quotum_used() which adds the message size to the new
>>quotum.
>>
>>This is all fine, and it works, except for the fact that the
>>curmail_size is not lowered again on deletion of a message. I'll fix
>>that tomorrow. :)
>>
>>This stuff should gives us some more speed (less complex queries),
>>especially on message insertion.
>>
>>Ilja
>>
>>_______________________________________________
>>Dbmail-dev mailing list
>>Dbmail-dev@dbmail.org
>>http://twister.fastxs.net/mailman/listinfo/dbmail-dev
>>
>
>
>
>

I think we can do it as simply as

db_begin();
..stuff...
db_commit();

where those functions are essentially just...

db_begin()
{
db_query("BEGIN");
}

All versions of MySQL since 3.23.17 understand the BEGIN; statement and it's
up to the individual database drivers to do whatever they do with it. So we
can always safely emit the BEGIN; and COMMIT; statements regardless of the
selected database or version thereof. If someone is running MySQL without a
transactional table type, they can let dbmail-maintenance clean up any messes
on a periodic basis.

Aaron


Ilja Booij <ilja@ic-s.nl> said:

> Full recalculation is indeed part of the maintenance script. When doing
> this stuff, the need for transactions occured to me.. I guess the
> maintenance program can serve as for a while, after which we should
> really use transactions (and possibly other ACID related features).
>
>
> Ilja
>
>
>
> Aaron Stone wrote:
>
> > This also underscores the need for transactions, otherwise it would be
> > possible for two messages to be inserted at almost the same time and lose
> > count of the size of one of those messages. A full recalculation as part of
> > the maintenance script might tide us over in the mean time...
> >
> > Aaron
> >
> >
> > Ilja Booij <ilja@ic-s.nl> said:
> >
> >
> >>I've made some minor adjustments to the way quota are handled. Or
> >>rather, to the way that a user's quotum is updated. Instead of using
> >>db_calculate_quotum_used() after every insert, I've now used a new
> >>function db_add_quotum_used() which adds the message size to the new
> >>quotum.
> >>
> >>This is all fine, and it works, except for the fact that the
> >>curmail_size is not lowered again on deletion of a message. I'll fix
> >>that tomorrow. :)
> >>
> >>This stuff should gives us some more speed (less complex queries),
> >>especially on message insertion.
> >>
> >>Ilja
> >>
> >>_______________________________________________
> >>Dbmail-dev mailing list
> >>Dbmail-dev@dbmail.org
> >>http://twister.fastxs.net/mailman/listinfo/dbmail-dev
> >>
> >
> >
> >
> >
> _______________________________________________
> Dbmail-dev mailing list
> Dbmail-dev@dbmail.org
> http://twister.fastxs.net/mailman/listinfo/dbmail-dev
>



--

"Aaron Stone" <aaron@serendipity.palo-alto.ca.us> writes:
> This also underscores the need for transactions, otherwise it would be
> possible for two messages to be inserted at almost the same time and lose
> count of the size of one of those messages. A full recalculation as part of
> the maintenance script might tide us over in the mean time...

As long as you're doing the addition in your SQL (update blah set
quota = quota + size) statement, there should be no problem of lost
additions. Without begin/commit, transactions are implicit in
PostgreSQL, and such actions are atomic in MySQL.

Not that I wouldn't like to see transactions done for their
performance benefits.

Mike
--
You've got to promise not to stop when I say when -- Foo Fighters

Michael Alan Dorman wrote:

> "Aaron Stone" <aaron@serendipity.palo-alto.ca.us> writes:
>
>>This also underscores the need for transactions, otherwise it would be
>>possible for two messages to be inserted at almost the same time and lose
>>count of the size of one of those messages. A full recalculation as part of
>>the maintenance script might tide us over in the mean time...
>
>
> As long as you're doing the addition in your SQL (update blah set
> quota = quota + size) statement, there should be no problem of lost
> additions. Without begin/commit, transactions are implicit in
> PostgreSQL, and such actions are atomic in MySQL.
I'm using SQL addition and subtraction. So not much of a problem there.

BTW, I've commited some code which also decreases the user's current
mailsize when messages are deleted (or status set to >= 2).

I've done some tests inserting new messages and storing them as deleted
(no change in quotum use) and expunging. All seems to work OK.

I've done some other optimizations:
some functions (e.g. db_expunge()) need to have the user_idnr of the
owner of the mailbox. This was looked up every time using a query, even
though the function calling db_expunge() had the user_idnr already. So,
it's much better to pass the user_idnr along from the calling function.

There are a lot more functions that have this behaviour. We can gain
quite a lot if we eliminate this.

Ilja