:/

[Piotr Wadas]

Well, after some tests for a few months on selected accounts with dbmail 2.3.6, I'm afraid
I must go back to previous solution for now.

To summarize experience:

+ dbmail offers uncomparable BETTER per-account performance, including imap and pop3 mailboxes
containing serveral gigabytes of messages, in hundreds/thousands accounts. This is probably the most
important advantage, which actually lead me to dbmail generally.

+ migration FROM any other pop3/imap suite to dbmail is quite easy, and does not
generate much work and end-user problems, ability to set-up a fixed UIDL in some dbmail table
with some self-made perl pop3 script, grabbed from old pop3 generated variables is a HUGE,
I mean really HUGE handicap, allowing to avoid re-downloading gigabytes of messages.

+ integration with sendmail.org's sendmail is easy achievable in debian through slight modification
in debian sensible-mda helper executable.

+ it was quite easy to migrate users continously (not thousands or users all-at-once). I setup
source of information (actually an sql table), and then on MTA (sensible-mta) I put sql query,
resulting "ok, user is already migrated to dbmail, use dbmail-deliver" OR "user is not migrated yet,
use procmail/maildrop/other file-based MDA). On the pop3/imap I set-up dovecot-proxy, which
used the same table. So finally I had three pop3 concurrently running - previously used
pop3/imap daemon bound to internal IP address A, dbmail daemons bound to internal IP address B,
and dovecot proxy bound to external address X.
In the effect, I achieved an MTA able to decide which MDA should be used per-user, and pop3/imap
proxy able to decide where forward connection, also per-user, and both (MTA/POP+IMAP) used
the same, one source of decision-critical information. Simple, and effective.

however

- dbmail daemons segfaults sometimes, enough often to force me to create a little script, making
sure the daemons are re-started in an instant (running in background, started on gnu screen utility)
probably better solution, until segfaults are code-fixed, would be starting dbmail from xinetd,
if possible (?), but I didn't dive into this.

-----------
#!/bin/bash

#[ `ps ax|egrep dbmail-imapd\|dbmail-pop3d\|dbmail-lmtpd|wc -l` -ne 3 ] &&
#   /etc/init.d/dbmail restart

if  ( [ `ps --no-headers -C dbmail-pop3d,dbmail-imapd,dbmail-lmtpd|wc -l` -ne 3 ] ) ; then
                        echo dbmail-just-restarted | mail -s dbmail-restart [e-mail]
                        /etc/init.d/dbmail restart
        else
                echo "DBMAIL: `date` alright"
fi
-----------

I really regret I don't have enough time to debug/track/post to the list each issue :(
Probably most of them are already fixed in development branch, not in last release unfortunately :(

- there are still some problems on vacuuming/cleaning up the database, at least with postgresql 8.4+
Even if messages are properly deleted and removed from database (they don't remains as orphaned parts),
with thousands of mailboxes and hundreds of gigabytes of messages, nigtly (or weekly) vacuuming
took so long, that sometimes is still in progress when the next, or after-next business day starts,
interfering with overall server machines performance. IMHO the main problem is famous
"delete-yes-I-am-sure-then-purge-deleted-yes-I-am-sure-then-cleanup-db' feature.
In my environment all MUA's (including webmail), used has a IMAP "Trash", or local pop3 trash,
which actually makes real-unrecoverable-drop-into-space quite hard. With thousands deletions per-day,
this really impact used space. Users are aware they're responsible for mistaken deletions, and even
if they're not, there's still their problem. Such situations happen rarely, there are daily backups,
and this does not explain usage of gigabytes (performance impact) of space under "what-if-user-is-fool".
Disks are cheap, actually, but from administrative point of view, managing tens/hundreds of gigabytes
of unneeded data in  backups, various local-and-remote trashes, and more various yes-really-purge,
yes-really-cleanup features, is quite annoying.

IMHO, the solution is to disable, or get-off "really-purge" server features.
The vacuuming issues are discussed below.

- finally, the effective decision is to back-off from dbmail for now, and this is mainly
because quota and size limits management complexity. Limits for users in my environments are
counted globally, without differentiating between email/ftp/www/sql space usage, and dbmail's
construction really makes it harder. Will probably back to dbmail with next release.

With the vacuuming/cleanup issues (probably existing with mysql too (?), I really think, the
solution is to make possible per-user or per-group different database storage.
I'd gracefully pay connection's performance cost, if I could have separate database for
each user, or (even better), a separate database per posix group.

At database (or ldap) level, this could be achieved with some table specifying
WHICH database (or schema with postgres ?) keeps database for this user. A posix or non-posix
group of users would simply have the same setting for "which-db".

This would simplify per-user (per-group) backups, space usage limits (with dbmail's internal
quotas, or external FS-based quotas, combined with posix extended ACLs for database files),
and solve cleanup/vacuuming issues. With milions of users it is no problem to vacuum, or
backup, or space-count, or purge-everything-and-start-again per-group (or per-user).

IMHO Connection performance cost is really low cost, in comparison to cost of extremely
complicated global (dbmail + various file resources) diskspace usage counting, extremly complicated
giant all-users-in-one backups and restore operation, and annoying vacuuming/cleanup.
There's no , and probably won't be, a need for browsing all-universe-of-our-users messages set.

More - actually messages size, stored in db, is definitely not the all storage user's messages use,
in some cases indexes size may be significant, probably hard to count per-user. With different
database storage, simple count of total space used by user's (or group's) db is simple
and effective, may be combined with posix extended acls and kernel's quota.

Already implemented message-size information is of course useful, and needed - at least
for enduser informational purposes. But hard to use in addition to global-count usage and limits.

Note - I'm not saying, that dbmail system has to do something with the rest of environment.
It's messaging (imap/pop3) system, and not a hosting system, or file-usage counting system.
But behaviour, which avoids complicating the rest of the system is worthful, at least for the
sake of dbmail's popularity. Even, if messaging system has nothing to do with webserver subsystem,
internet domain management subsystem, and so on, in real life 90% of cases, the same physical person, (administrator), is responsible for the whole stuff. In small-and-medium-businesses , even if 5  admins are in business, each one of them has its part of 24/7 time of duty, when he's responsible
for functioning of the whole stuff. Even, if there are specialized admins for each service,
quotas/filesysytem/ftp admin won't be happy if db admin will say "sorry, I don't care about how you
include db usage in your quota stats".

To close - I have some problem dbmail-export utility, seems it generates mbox file
with invalid structure, for the first look mbox file seems to be ok, but MUA (pine), and
old pop3/imap server complains about invalid format, I'll post if I find what's actually wrong.

Hope to back to dbmail soon :)
Regards,
DT

[Peter Rabbitson-4]

Piotr Wadas wrote:
>
> Well, after some tests for a few months on selected accounts with dbmail
> 2.3.6

The 2.3 series is an experimental branch, akin to the 2.5.x series of the
linux kernel back in the day. Most subscribers of this list are quite
happy with the production-ready 2.2.x series. Be aware that 2.2.12 introduced
a regression, which apparently has been fixed and 2.2.13 is to be officially
released today.

Cheers
_______________________________________________
DBmail mailing list
DBmail@...
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

[Michael Monnerie-5]

On Dienstag 10 November 2009 Piotr Wadas wrote:
> - there are still some problems on vacuuming/cleaning up the
> database, at least with postgresql 8.4+

Paul, is PostgreSQL 8.4 supported in dbmail-2.2 ? I'd be happy and would
like to switch. Some time ago we switched from 8.1 to 8.3, and database
performance *doubled*. It was a nicemove :-)

Piotr, if you have problems with vacuum, you should ask on the
postgresql-admin list: <pgsql-admin@...>

The rest is really generic, you should explain what you *want*, not what
is not supported.

mfg zmi
--
// Michael Monnerie, Ing.BSc    -----      http://it-management.at
// Tel: 0660 / 415 65 31                      .network.your.ideas.
// PGP Key:         "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38  500E CE14 91F7 1C12 09B4
// Keyserver: wwwkeys.eu.pgp.net                  Key-ID: 1C1209B4

_______________________________________________
DBmail mailing list
DBmail@...
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

[Paul J Stevens]

Michael Monnerie wrote:
> On Dienstag 10 November 2009 Piotr Wadas wrote:
>> - there are still some problems on vacuuming/cleaning up the
>> database, at least with postgresql 8.4+
>
> Paul, is PostgreSQL 8.4 supported in dbmail-2.2 ? I'd be happy and would
> like to switch. Some time ago we switched from 8.1 to 8.3, and database
> performance *doubled*. It was a nicemove :-)

Please tell me. I don't know. I don't use pg myself, though actually
I've been doing a rollout on fedora8 all day today using pg - I think
that was version 8.4, but I haven't checked yet. Testing is planned for
another day.

I'm not aware of any showstoppers. You may need to tweak the pg config
to silence spurious warnings about slash-escaping strings - not an issue.




--
   ________________________________________________________________
   Paul Stevens                                      paul at nfg.nl
   NET FACILITIES GROUP                     GPG/PGP: 1024D/11F8CD31
   The Netherlands________________________________http://www.nfg.nl
_______________________________________________
DBmail mailing list
DBmail@...
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

[Michael Monnerie-5]

On Mittwoch 11 November 2009 Paul Stevens wrote:
> I'm not aware of any showstoppers. You may need to tweak the pg
> config to silence spurious warnings about slash-escaping strings -
> not an issue.

escape_string_warning = off
Yes, that needs to be in, starting from 8.3 already.

Good, openSUSE 11.2 is due tomorrow, so we can start testing soon.

mfg zmi
--
// Michael Monnerie, Ing.BSc    -----      http://it-management.at
// Tel: 0660 / 415 65 31                      .network.your.ideas.
// PGP Key:         "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38  500E CE14 91F7 1C12 09B4
// Keyserver: wwwkeys.eu.pgp.net                  Key-ID: 1C1209B4

_______________________________________________
DBmail mailing list
DBmail@...
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

[Piotr Wadas]

http://www.mail-archive.com/[e-mail]/msg12339.html

Is it  '~/.dbmail.db' ( or '/var/mail/$USER' or similar trick) available
with 2.3.6 ? And, if yes, what more symbols are resolved in db file name with sqlite driver?
Doesn't it require some setuid to deliver a message?

Regards,
DT

[Paul J Stevens]

Piotr Wadas wrote:
>
> http://www.mail-archive.com/dbmail@.../msg12339.html
>
> Is it  '~/.dbmail.db' ( or '/var/mail/$USER' or similar trick) available
> with 2.3.6 ?

The first one is, but it´s less useful than you would think because the
user is resolved to the effective uid of the dbmail process - not the
authenticated user.

It would be nice if dbmail would be able to switch backend after
authentication, but currently only one global database connection pool
is used per dbmail process.

What you need is however a planned feature: being able to connect to
multiple backends to allow grouping users on separate backends. Allowing
per-user sqlite databases is one of the leading use-cases for that project.

> And, if yes, what more symbols are resolved in db file name
> with sqlite driver?

Currently, a getenv("HOME") is done to expand the tilde char (~) in the
database name.

> Doesn't it require some setuid to deliver a message?

That not a problem: use a procmail->dbmail-deliver pipe. How to run a
setuid dbmail-imapd process is something I've never been able to figure
out. At least using the current design.

The planned design for project 'hydra' is to use one global database
that holds either the authentication tokens (authsql) or shadow records
(authldap). This global database will allow setting a database URI per user.

Dbmail will maintain one global connection pool for either the URI
delegations or for the fallback mailstorage for those users that have
not been delegated.

Transient connection pools will be created and collected as needed. Some
kind of garbage collection on idle connection pools will be needed to
avoid excessive (re)opening and closing of connections - especially if
you're using per-user sqlite databases with a lot of users and lots of
connections. If you're using a handful of postgresql/mysql backends
(mixed?) such garbage collection would probably not be needed at all.

So, the basic idea is to allow userbased backend delegation where the
actual granularity is decided by the administrator. You can use an
overflow model, per email-domain grouping, or even a dedicated database
per user.

Steps needed to make this happen are pretty much worked out by now. All
that is needed is some spare time to actually code it.

--
  ________________________________________________________________
  Paul Stevens                                      paul at nfg.nl
  NET FACILITIES GROUP                     GPG/PGP: 1024D/11F8CD31
  The Netherlands________________________________http://www.nfg.nl
_______________________________________________
DBmail mailing list
DBmail@...
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail