> -----Original Message----- > From: Nuno Silva [mailto:[EMAIL PROTECTED]] > Sent: Saturday, November 30, 2002 2:50 AM > To: Nicola Ranaldo > Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] > 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... > > > > > > > > > > >