Hi Justin,
On 7-dec-2006, at 15:13, Justin McAleer wrote:
I figured I would go ahead and toss this out for anybody that may be interested, since I was so shocked by the results. I have two servers set up for testing, one running postfix/dbmail and one running the database servers. The database machine is a dual core AMD (4400+ I believe) with 4 gigs of memory, with the database files living on a fiber connected Apple SAN (XRaid). I have dbmail compiled with mysql and pgsql, so all I need to do to switch between the two is change the driver in the conf file and restart. I'm using dbmail-lmtpd running on a unix socket. Finally, I have the postfix delivery concurrency set to 5. For mysql, I'm using a 4GB InnoDB sample config that comes in the CentOS rpm (increased the buffer pool to 2.5 gigs though). Version is 4.1.20. For postgres, I'm using the default variables except for increasing the shared buffers to 256MB, setting effective cache size to 3 GB, and random page cost to 2. Version is 8.1.4. I've sent a good amount of real mail to each setup as well, but for quantifiable results I have a perl script that sends gibberish of a configurable size (3kb here) to a single recipient. Since we're inserting into a DB, the recipient of the messages should have no bearing on delivery performance, barring postfix concurrency. For the test, I sent one batch of mail through so postfix would already have a full lmtp connection pool when I began the real test. I had 10 perl processes each sending 100 messages as fast as postfix would accept them, for a total of 1000 3KB messages. Results... Mysql: 95 seconds to deliver all 1000 messages. Both cores on the DB server were effectively peaked during delivery. Postgres: 10 seconds to deliver all 1000 messages. DBMail was really close to being able to deliver as fast as postfix could queue to local disk (within a second or two for 1000th message). The cores on the DB server looked to average around 45%/30% usage during delivery. The CPU usage is just based on watching top output, so keep that in mind... however with such a huge variance, even eyeballing it I'm confident in reporting it. I intend to do further testing with multiple pop/imap sessions active and all that of course. Plus, I'll probably do some quantifiable real-world mail delivery comparison as well. However, as I said, these results just blew me away. I wanted to share them since, going through the list archive, it seemed many people used mysql just because that's what they've always used. We're a mysql shop as well, and it was a little bit frustrating getting postgres up and running/tuned since I didn't know how to do any of it off the bat. But it sure seems worth making our admins learn something new for this much of a performance gain. I'm now wondering if we could manage having only one DB setup for all 175,000 of our users...
Your results are quite interesting :-) Did you perform these insertion tests on empty databases, or did they already contain messages? Personally, I am quite curious about the difference in speed of POP and/or IMAP between MySQL and Postgresql, so please share any more test results you may obtain! Leander
