PostgreSQL backend: a waste of time?

[Brasseur Valéry]

> -----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?
> 
> 
> Hello!
> 
> 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 
> connection 
> 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 
> commands 
> 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 
> transactions.
> 
> 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 
> connections.
> 
> 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 :)
> 
> Regards,
> 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...
> > 
> > 
> > 
> > 
> > 
>