PostgreSQL backend: a waste of time?

[Nuno Silva]

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.

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...
> 
> 
> 
> 
>