PostgreSQL backend: a waste of time?
Valery.Brasseur at atosorigin.com
Mon Dec 2 03:41:34 EST 2002
> -----Original Message-----
> From: Nuno Silva [mailto:nuno.silva at sectium.com]
> Sent: Saturday, November 30, 2002 2:50 AM
> To: Nicola Ranaldo
> Cc: info-cyrus at lists.andrew.cmu.edu; Valery.Brasseur at atosorigin.com
> Subject: Re: PostgreSQL backend: a waste of time?
> Nicola Ranaldo wrote:
> > I cannot spread sql commands of a unique transaction over
> multiple pgsql
> > connection, and a connection cannot handle parallel transactions.
> > So if i have 1000 imapd process starting a transaction the
> mailbox daemon
> > has to open 1000 pgsql connection.
> Reading from the DB should be trivial, right?
> I'm not 100% sure, but I suppose that one can virtualize the
> connections. What I mean is: imapd (or pop3d or lmtpd...)
> wants to write
> something -> ask the daemon and the daemon will choose a free
> and commit those changes. This is the "one operation simple case".
> Some of the DBs that cyrus maintains appear to be this simple (the
> mailboxes file).
> Other cyrus' DBs seem to require transactions (seen and
> delivery DB's)..
> This makes it harder to manage with a single daemon connecting to a
> RDBMS with onky a few connections.
> CMU people or Ken: comments? :)
> > One solution could be:
> > BEGIN -> Allocate a new buffer to store sql commands
> > SQL COMMANDS -> add commands to buffer
> > COMMIT -> send all the buffered commands to the mailbox
> daemon and cleanup
> > the buffer
> > ABORT -> cleanup the buffer
> > Do you think this is a good solution ?
> I really don't know... This buffer is in the daemon?
> Don't you have to receive responses from the SQL DB? Or these
> are only writes (UPDATE, INSERT)? If these are only writes it seams a
> good ideia, but if you need to SELECT (inside the
> transaction) too there
> is the problem of different connections getting different
> Brasseur Valéry posted a mysql-backend patch for cyrus recently.
> Brasseur, do you use a mysql connection for each cyrus
> process too? And
> do you use transactions? Last time I checked mysql didn't
> support these.
the patch is using a connection per process, but I am considering usibg
mbdeamon (look at Nuno's mail from 27/11) modify to use the mysql back-end.
in this case I will have a few (one for now) connection du Mysql, the
mbdaemon will handle all the processes connection.
for the transaction part.
I use mysql 4.0 which support transaction now. But I don't use this for
handling transaction in the code !!!
> If transactions aren't required than it's easy to have 1 connection
> shared amongst 100 processes, right? :)
> > Howewer i think a pgsql connection for every master child
> could not be a
> > problem, on my production server (7500 very active users,
> cyrus.log is
> > 20MB/day) the average number of imapd is 15, pop3d is 30,
> lmtpd is 5 (under
> > mail-bombing lmtpd process was 45). Howewer it is an
> AlphaServer ES45 with 4
> > 1ghz CPU and 700Gb of raid disk and is quite fast. Wath's
> your experience
> > with huge number of users or slow server ?
> It depends on you user base:
> If your system is a backend for a webmail, for instance, your "users"
> (the php or perl script) will always connect, fetch something,
> disconnect. In this situation you'll never see lots of simultaneous
> If you have 50.000 users on a campus setup using IMAP you'll get 5000
> concurrent connection easily.
> The same way if you have a company with 500 desktops all of them
> checking the email with IMAP you can easily get 1000 (the double)
> concurrent connections.
> As internet people says, YMMV :)
> Nuno Silva
> > Bye
> > Nicola Ranaldo
> >>IIRC someone implemented such a daemon and patched cyrus to
> use it. This
> >>daemon's backend was a text-file but the "protocol" is there.
> >>a drawing :)
> >>imapd1 imapd2 imapd3 ... imap1000
> >> | | | |
> >> -------------------------------
> >> |
> >> daemon
> >> |
> >>| | | | | | |
> >>1 2 3 4 5 6 7
> >>1 to 7 would be postgresql connections. This number may
> vary... maybe 1
> >>connection per 100 imapds? Or a user defined number. IMMV...
More information about the Info-cyrus