On Fri, Feb 11, 2005, Hans Kristian Rosbach <[EMAIL PROTECTED]> said:
>> P.S.: If it was really meant to read everything into memory and then copy
>> the same message again to the insert query, why not consider reading
>> everything into a temp file, and then store the msg in db using something
>> like this:
>>
>> insert msg set id=xxx, body="first 512 bytes of msg goes here...";
>> update msg set body=body+"another 512 bytes..." where id=xxx;
>> update msg set body=body+"yet another 512bytes" where id=xxx;
>
> Lets see.. our current max attachment size is (on our mailservers)
> 50MB, with 512bytes blocks this would lead to 102400 database queries.
> That would surely kill the database performance even under light load.
Your suggestion of a maximum memory usage would be perfect here. Right now
we have no sanity checking against the maximum query size. That should be
added. Then we take the smaller of the two -- if the dbmail.conf
MAXMESSAGESIZE size is smaller, we just use that, but if the query size is
smaller, we use it and give an error message so that the sysadmin knows to
up this variable in the database. In any event, we should keep memory
usage below an admin specified limit and make sure never to give a query
that's too large to the database.
[snip]
> 2. We can use a tempfile
> Now, this would lead to more database calls depending on block size
> and it would probably lead to worse performance.
So wait -- the MTA takes the message, puts it in a file to queue for
delivery. Then the MTA reads the file either into a pipe or tcp connection
to dbmail. Then DBMail puts the message into a file to piece into the
database. So for every message, we put it on disk twice and into memory
three times...
If DBMail became an MTA, then we could write messages to disk in CSV in
the first place, and use LOAD DATA INFILE (which is standard ANSI, I
believe) to stuff it into the database once we've done the delivery stuff.
> 4. [I snipped this one, but it was about "stream to database"]
Take a look at the fifo pipe they use as the 'datafile' about halfway down
this page:
http://dev.mysql.com/doc/mysql/en/load-data.html
We could actually use this! As we read from the network, we write to the
named pipe. It's up to the operating system to do the buffering and
whatnot, and we're not limited by any size constraints because LOAD DATA
INFILE implementations are built for big stuff (I think...).
Aaron
