Thread: Performance figures from DbMail list

The following appeared this afternoon on the DbMail list.  As someone
replied the MySql used is old, and the newer one is faster, but then
8.2 is faster than the older Postgresql versions.

This was posted by:- "Justin McAleer" <justin@fehuq.com>

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.

Quick follow up on this, the guy who ran this test retested with a much
newer version of MySQL and sent this message to the DBMail mailing list
today.

Ok, I just did the test on mysql 5.0.27. It took 73 seconds
to deliver the 1000 messages. So, it's a good bit faster
than 4.1.20's 95 seconds, but still pales in comparison to
postgres' 9 seconds. Mysql was still peaking both cpu cores
during delivery.


On Thu, 07 Dec 2006 11:23:58 -0800
  Michael Dean <mdean@sourceview.com> wrote:
 >> > > Lars Kneschke wrote:
 >>> > >> Justin McAleer <justin@fehuq.com> schrieb:
 > > I think a test of 5.0 and 8.2 would be great!  Recent
 > > benchmarks of the
 > > two show pg really blows the socks off mysql, so a
 > > confirmation of that in the email segmnent would be
 > > terrific!!!
 > > Michael
 > > _______________________________________________
 > > DBmail mailing list
 > > DBmail@dbmail.org
 > > https://mailman.fastxs.nl/mailman/listinfo/dbmail
 > >

_______________________________________________
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail



David Goodenough wrote:
> The following appeared this afternoon on the DbMail list.  As someone
> replied the MySql used is old, and the newer one is faster, but then
> 8.2 is faster than the older Postgresql versions.
>
> This was posted by:- "Justin McAleer" <justin@fehuq.com>
>
> 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.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>

--
Matthew T. O'Connor
V.P. Operations
Terrie O'Connor Realtors
201-934-4900

This link adds to the joy...

http://forums.mysql.com/read.php?25,93181,93181

So the most popular free "database" in the world is a lousy performing
product that accepts 'gabba gabba hey' as a valid timestamp. Someone
please, give me a reason not to get cynical...


> -----Original Message-----
> From: Matthew T. O'Connor [mailto:matthew@tocr.com]
> Sent: den 8 december 2006 19:35
> To: David Goodenough
> Cc: pgsql-general@postgresql.org
> Subject: Re: Performance figures from DbMail list
>
> Quick follow up on this, the guy who ran this test retested with a
much
> newer version of MySQL and sent this message to the DBMail mailing
list
> today.
>
> Ok, I just did the test on mysql 5.0.27. It took 73 seconds
> to deliver the 1000 messages. So, it's a good bit faster
> than 4.1.20's 95 seconds, but still pales in comparison to
> postgres' 9 seconds. Mysql was still peaking both cpu cores
> during delivery.
>
>
> On Thu, 07 Dec 2006 11:23:58 -0800
>   Michael Dean <mdean@sourceview.com> wrote:
>  >> > > Lars Kneschke wrote:
>  >>> > >> Justin McAleer <justin@fehuq.com> schrieb:
>  > > I think a test of 5.0 and 8.2 would be great!  Recent
>  > > benchmarks of the
>  > > two show pg really blows the socks off mysql, so a
>  > > confirmation of that in the email segmnent would be
>  > > terrific!!!
>  > > Michael
>  > > _______________________________________________
>  > > DBmail mailing list
>  > > DBmail@dbmail.org
>  > > https://mailman.fastxs.nl/mailman/listinfo/dbmail
>  > >
>
> _______________________________________________
> DBmail mailing list
> DBmail@dbmail.org
> https://mailman.fastxs.nl/mailman/listinfo/dbmail
>
>
>
> David Goodenough wrote:
> > The following appeared this afternoon on the DbMail list.  As
someone
> > replied the MySql used is old, and the newer one is faster, but then
> > 8.2 is faster than the older Postgresql versions.
> >
> > This was posted by:- "Justin McAleer" <justin@fehuq.com>
> >
> > 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.
> >
> > ---------------------------(end of
broadcast)---------------------------
> > TIP 1: if posting/reading through Usenet, please send an appropriate
> >        subscribe-nomail command to majordomo@postgresql.org so that
your
> >        message can get through to the mailing list cleanly
> >
>
> --
> Matthew T. O'Connor
> V.P. Operations
> Terrie O'Connor Realtors
> 201-934-4900

On Fri, 2006-12-08 at 15:04, Mikael Carneholm wrote:
> This link adds to the joy...
>
> http://forums.mysql.com/read.php?25,93181,93181
>
> So the most popular free "database" in the world is a lousy performing
> product that accepts 'gabba gabba hey' as a valid timestamp. Someone
> please, give me a reason not to get cynical...

Oh man, that poor guy.  He's got 4 or 5 machines in a cluster, and he's
still not catching up to the one machine postgresql server.

And he's switching because he wants better reliability?

Guess he's never heard of pgpool, slony, mammoth replicator, cjdbc, or a
half dozen other ways to get high reliability with postgresql.

I wonder what version of postgresql he was testing.

Scott Marlowe wrote:
> On Fri, 2006-12-08 at 15:04, Mikael Carneholm wrote:
>
>> This link adds to the joy...
>>
>> http://forums.mysql.com/read.php?25,93181,93181
>>
>> So the most popular free "database" in the world is a lousy performing
>> product that accepts 'gabba gabba hey' as a valid timestamp. Someone
>> please, give me a reason not to get cynical...
>>
>
> Oh man, that poor guy.  He's got 4 or 5 machines in a cluster, and he's
> still not catching up to the one machine postgresql server.
>
> And he's switching because he wants better reliability?
>
> Guess he's never heard of pgpool, slony, mammoth replicator, cjdbc, or a
> half dozen other ways to get high reliability with postgresql.
>
> I wonder what version of postgresql he was testing.
>
Please, remove pgpool from your list of "reliable" postgresql tools.
It's decent, but child procs tend to go zombie from time to time.

--
erik jones <erik@myemma.com>
software development
emma(r)

On Fri, 2006-12-08 at 15:44, Erik Jones wrote:
> Scott Marlowe wrote:
> > On Fri, 2006-12-08 at 15:04, Mikael Carneholm wrote:
> >
> >> This link adds to the joy...
> >>
> >> http://forums.mysql.com/read.php?25,93181,93181
> >>
> >> So the most popular free "database" in the world is a lousy performing
> >> product that accepts 'gabba gabba hey' as a valid timestamp. Someone
> >> please, give me a reason not to get cynical...
> >>
> >
> > Oh man, that poor guy.  He's got 4 or 5 machines in a cluster, and he's
> > still not catching up to the one machine postgresql server.
> >
> > And he's switching because he wants better reliability?
> >
> > Guess he's never heard of pgpool, slony, mammoth replicator, cjdbc, or a
> > half dozen other ways to get high reliability with postgresql.
> >
> > I wonder what version of postgresql he was testing.
> >
> Please, remove pgpool from your list of "reliable" postgresql tools.
> It's decent, but child procs tend to go zombie from time to time.

No, I don't think I will.  I've used it and tested it quite thoroughly,
and have never had that happen.  Bad hardware on your end maybe?  Or an
older version, or a bad compiler?

I've found it to be very stable and reliable.  If you've got a
reproduceable test case I'm sure Tatsuo (sp) would love to see it.

Scott Marlowe wrote:
> On Fri, 2006-12-08 at 15:44, Erik Jones wrote:
>
>> Scott Marlowe wrote:
>>
>>> On Fri, 2006-12-08 at 15:04, Mikael Carneholm wrote:
>>>
>>>
>>>> This link adds to the joy...
>>>>
>>>> http://forums.mysql.com/read.php?25,93181,93181
>>>>
>>>> So the most popular free "database" in the world is a lousy performing
>>>> product that accepts 'gabba gabba hey' as a valid timestamp. Someone
>>>> please, give me a reason not to get cynical...
>>>>
>>>>
>>> Oh man, that poor guy.  He's got 4 or 5 machines in a cluster, and he's
>>> still not catching up to the one machine postgresql server.
>>>
>>> And he's switching because he wants better reliability?
>>>
>>> Guess he's never heard of pgpool, slony, mammoth replicator, cjdbc, or a
>>> half dozen other ways to get high reliability with postgresql.
>>>
>>> I wonder what version of postgresql he was testing.
>>>
>>>
>> Please, remove pgpool from your list of "reliable" postgresql tools.
>> It's decent, but child procs tend to go zombie from time to time.
>>
>
> No, I don't think I will.  I've used it and tested it quite thoroughly,
> and have never had that happen.  Bad hardware on your end maybe?  Or an
> older version, or a bad compiler?
>
> I've found it to be very stable and reliable.  If you've got a
> reproduceable test case I'm sure Tatsuo (sp) would love to see it.
>
pgpool -h reports v. 3.1.  Note that this is pgpool-I and that the
release notes for the version we have say that an issue with procs dying
was fixed -- while it is certainly much better than it  was in version
previous to 3.1, we have seen it happen on occasion.  Test case?  Hah.
This tends to happen during off hours on our high-load web servers so
the best we can do is keep an eye on things and restart when we catch
it.  I see that pgpool-II has been released and since been integrated
with heartbeat which definitely sounds promising.  I'm going to show it
to our deciders...

--
erik jones <erik@myemma.com>
software development
emma(r)

On Fri, 2006-12-08 at 22:04 +0100, Mikael Carneholm wrote:
> This link adds to the joy...
>
> http://forums.mysql.com/read.php?25,93181,93181
>
> So the most popular free "database" in the world is a lousy performing
> product that accepts 'gabba gabba hey' as a valid timestamp. Someone
> please, give me a reason not to get cynical...
>
>

To be fair, he was running the cluster on a 100Mbps network. Depending
on his setup, that may have been his bottleneck. However, there's a good
chance that's not his only problem. Especially if he's so sold on MySQL
Cluster that he's trying to find a place to use it.

Regards,
    Jeff Davis

On Fri, 2006-12-08 at 16:13, Jeff Davis wrote:
> On Fri, 2006-12-08 at 22:04 +0100, Mikael Carneholm wrote:
> > This link adds to the joy...
> >
> > http://forums.mysql.com/read.php?25,93181,93181
> >
> > So the most popular free "database" in the world is a lousy performing
> > product that accepts 'gabba gabba hey' as a valid timestamp. Someone
> > please, give me a reason not to get cynical...
> >
> >
>
> To be fair, he was running the cluster on a 100Mbps network. Depending
> on his setup, that may have been his bottleneck. However, there's a good
> chance that's not his only problem. Especially if he's so sold on MySQL
> Cluster that he's trying to find a place to use it.

No, read on, he upgraded to gigabit halfway through the thread, and went
from 50 to 70 tps.

Jeff Davis wrote:
> On Fri, 2006-12-08 at 22:04 +0100, Mikael Carneholm wrote:
>> This link adds to the joy...
>>
>> http://forums.mysql.com/read.php?25,93181,93181
>>
>> So the most popular free "database" in the world is a lousy performing
>> product that accepts 'gabba gabba hey' as a valid timestamp. Someone
>> please, give me a reason not to get cynical...
>>
>>
>
> To be fair, he was running the cluster on a 100Mbps network. Depending
> on his setup, that may have been his bottleneck. However, there's a good
> chance that's not his only problem. Especially if he's so sold on MySQL
> Cluster that he's trying to find a place to use it.
>

Later in the thread he gets gigabit working which does help things
somewhat, but not enough.

On Fri, 2006-12-08 at 16:08, Erik Jones wrote:
> Scott Marlowe wrote:
> > On Fri, 2006-12-08 at 15:44, Erik Jones wrote:
> >
> >> Scott Marlowe wrote:
> >>

SNIP

> >>> Guess he's never heard of pgpool, slony, mammoth replicator, cjdbc, or a
> >>> half dozen other ways to get high reliability with postgresql.
> >>>
> >>> I wonder what version of postgresql he was testing.
> >>>
> >>>
> >> Please, remove pgpool from your list of "reliable" postgresql tools.
> >> It's decent, but child procs tend to go zombie from time to time.
> >>
> >
> > No, I don't think I will.  I've used it and tested it quite thoroughly,
> > and have never had that happen.  Bad hardware on your end maybe?  Or an
> > older version, or a bad compiler?
> >
> > I've found it to be very stable and reliable.  If you've got a
> > reproduceable test case I'm sure Tatsuo (sp) would love to see it.
> >
> pgpool -h reports v. 3.1.  Note that this is pgpool-I and that the
> release notes for the version we have say that an issue with procs dying
> was fixed -- while it is certainly much better than it  was in version
> previous to 3.1, we have seen it happen on occasion.  Test case?  Hah.
> This tends to happen during off hours on our high-load web servers so
> the best we can do is keep an eye on things and restart when we catch
> it.  I see that pgpool-II has been released and since been integrated
> with heartbeat which definitely sounds promising.  I'm going to show it
> to our deciders...

Hmmm.  I wonder if there's a difference in the kernels or threading libs
or what not between you and I.  All my testing was done on RHEL3 and
FC2, and honestly, beating the crap out of it, it never died once.

hmmm.

On Fri, 2006-12-08 at 16:23 -0600, Scott Marlowe wrote:
> > To be fair, he was running the cluster on a 100Mbps network. Depending
> > on his setup, that may have been his bottleneck. However, there's a good
> > chance that's not his only problem. Especially if he's so sold on MySQL
> > Cluster that he's trying to find a place to use it.
>
> No, read on, he upgraded to gigabit halfway through the thread, and went
> from 50 to 70 tps.
>

Wow, that's bad. This debunks the myth that native replication is
inherently easier to use or inherently better in some way. They spent a
whole thread talking about it, and still couldn't get half the
performance of a single PG box.

Regards,
    Jeff Davis