Aaron Stone wrote:
On Wed, Feb 16, 2005, ""Matthew T. O'Connor"" <matthew@zeut.net> said:
I think that there is already a lot
of contention on the dbmail_users table having to update the mail_size
field (we have heard reports of significant performance boosts on busy
servers by disabling this feature), the contention will be that much
greater if every mail received for every user in a domain all have to
update the same record.
I think a better solution is to create the table you suggested without
the client_curmail field. That way you can still specify a maxmail size
for any given client, but don't have the update contention. When
delivering a message you can check the quota by doing something like
"select sum(curmail_size) from dbmail_users where client_idnr = x".
Off the cuff, I would take it to be that one UPDATE is faster than one
SELECT, but only up to a certain concurrency rate, after which the
non-locking SELECT is faster. Yes, no? Are there some statistics we can
put to this?
This smacks of needing a configuration option so that a site can use the
method best for their own usage pattern.
You may very well be right that each will outperform the other depending
on the usage level, however I also not a fan of giving lots of different
configuration options unless it's proven they are needed (think
configuring sendmail *ugh*). We should find a single solution that
works for both workloads that doesn't require additional knobs for the
admin to learn how to use. Also, we shouldn't optimize for a problem
that may or may not be real.
For now, I would prefer we try the "select sum()" method. My reasoning
is as follows: As long as the "select sum()" method is fast enough, why
optimize for the lesser workload. If it's proven that the "select
sum()" method isn't fast enough for the lesser workload, then we can try
the "one update" method. If and when that proves problematic we should
find a better solution.
