Обсуждение: Performance nightmare with dspam (urgent)

We've seen PostgreSQL performance as a dspam database be simply stellar on
some machines with absolutely no tuning to the postgres.conf, and no
statistics target altering.

Some months ago, I moved my domains from a crusty old generic PIII 733 to a
brand new Athlon 3000+ server that I was leasing.  The load was very high,
and it was all PostgreSQL.  I cried and screamed on #postgresql for hours,
and eventually it was discovered that the following command fixed everything
and suddenly performance was lightning fast again:

alter table "dspam_token_data" alter "token" set statistics 200; analyze;

We had set up about 200 domains on a SuperMicro P4 2.4GHz server, and it was
working great too (without the above tweak!), but then the motherboard
started having issues and the machine would lock up every few weeks.  So we
moved everything to a brand new SuperMicro P4 3.0GHz server last week, and
now performance is simply appalling.  Whereas before the load average was
something around 0.02, it's now regularly at 4 (all postgres), and there's
hundreds of messages in the queue waiting.  Lots of people are complaining
about slow mail delivery, and I've been up for 2 days trying to fix this with
no success.

Originally, the problem was a lot worse, but I spent a lot of time tuning the
postgresql.conf, and changed the statistics target shown above, and this made
things okay (by okay I mean that it's okay at night, but during the day
several hundred messages will regularly be waiting for delivery).

I found this response to my original post, and tried every single suggestion
in it, which has not helped:

http://archives.postgresql.org/pgsql-performance/2004-11/msg00416.php

I'm sorry to come begging for help, but this is a MAJOR problem with no
logical explanation, and is almost certainly the fault of PostgreSQL, because
the database and contents have been identical across all the hosts, and some
work beautifully with no tuning whatsoever; so I don't feel I'm wrong in
placing blame...

All machines run Gentoo Linux.  All have the same package versions.  Disk I/O
doesn't seem to be related - the 733MHz server had a 33MB/s IDE drive, the
2.4GHz server had a RAID 5 with 3 ultra320 drives:  neither of those required
any tuning.  The new 3.0GHz has a mirror raid with 2 ultra320 drives, and the
3000+ that tuning fixed had an ultra160 disk not in a RAID.

I really like PostgreSQL, and really don't want to use MySQL for dspam, but if
I can't get this worked out ASAP I'm going to have to change for the sake of
our customers.  Any help is GREATLY appreciated!

I'm online on instant messengers (contact IDs shown below), monitoring my
email, and will be on #postgresql on Freenode.

Cheers,
--
Casey Allen Shobe | http://casey.shobe.info
cshobe@seattleserver.com | cell 425-443-4653
AIM & Yahoo:  SomeLinuxGuy | ICQ:  1494523
SeattleServer.com, Inc. | http://www.seattleserver.com

On Wednesday 01 June 2005 20:19, Casey Allen Shobe wrote:
> We've seen PostgreSQL performance as a dspam database be simply stellar on
> some machines with absolutely no tuning to the postgres.conf, and no
> statistics target altering.

Wow.  That took a phenomenally long time to post.  I asked on IRC, and they
said it is "normal" for the PG lists to bee so horribly slow.  What gives?  I
think you guys really need to stop using majordomo, but I'll avoid blaming
that for the time being.  Maybe a good time for the performance crew to look
at the mailing list software instead of just PG.

> We had set up about 200 domains on a SuperMicro P4 2.4GHz server, and it was
> working great too (without the above tweak!), but then the motherboard
> started having issues and the machine would lock up every few weeks.  So we
> moved everything to a brand new SuperMicro P4 3.0GHz server last week, and
> now performance is simply appalling.

Well, we actually added about 10 more domains right around the time of the
move, not thinking anything of it.  Turns out that simply set the disk usage
over the threshhold of what the drive could handle.  At least, that's the
best guess of the situation - I don't really know whether to believe that
because the old machine had a 3-disk RAID5 so it should have been half the
speed of the new machine.  However, analyzing the statements showed that they
were all using index scans as they should, and no amount of tuning managed to
reduce the I/O to an acceptable level.

After lots of tuning, we moved pg_xlog onto a separate disk, and switched
dspam from TEFT to TOE mode (which reduces the number of inserts).  By doing
this, the immediate problem was alleviated.

Indeed the suggestion in link in my previous email to add an extra index was a
BAD idea, since it increased the amount of work that had to be done per
write, and didn't help anything.

Long-term, whenever we hit the I/O limit again, it looks like we really don't
have much of a solution except to throw more hardware (mainly lots of disks
in RAID0's) at the problem. :(  Fortunately, with the above two changes I/O
usage on the PG data disk is a quarter of what it was, so theoretically we
should be able to quadruple the number of users on current hardware.

Our plan forward is to increase the number of disks in the two redundant mail
servers, so that each has a single ultra320 disk for O/S and pg_xlog, and a
3-disk RAID0 for the data.  This should triple our current capacity.

The general opinion of the way dspam uses the database among people I've
talked to on #postgresql is not very good, but of course the dspam folk blame
PostgreSQL and say to use MySQL if you want reasonable performance.  Makes it
real fun to be a DSpam+PostgreSQL user when limits are reached, since
everyone denies responsibility.  Fortunately, PostgreSQL people are pretty
helpful even if they think the client software sucks. :)

Cheers,
--
Casey Allen Shobe | http://casey.shobe.info
cshobe@seattleserver.com | cell 425-443-4653
AIM & Yahoo:  SomeLinuxGuy | ICQ:  1494523
SeattleServer.com, Inc. | http://www.seattleserver.com

Casey Allen Shobe wrote:
> On Wednesday 01 June 2005 20:19, Casey Allen Shobe wrote:
>
...
> Long-term, whenever we hit the I/O limit again, it looks like we really don't
> have much of a solution except to throw more hardware (mainly lots of disks
> in RAID0's) at the problem. :(  Fortunately, with the above two changes I/O
> usage on the PG data disk is a quarter of what it was, so theoretically we
> should be able to quadruple the number of users on current hardware.
>

Be very careful in this situation. If any disks in a RAID0 fails, the
entire raid is lost. You *really* want a RAID10. It takes more drives,
but then if anything dies you don't lose everything.

If you are running RAID0 and you *really* want performance, and aren't
concerned about safety (at all), you could also set fsync=false. That
should also speed things up. But you are really risking corruption/data
loss on your system.

> Our plan forward is to increase the number of disks in the two redundant mail
> servers, so that each has a single ultra320 disk for O/S and pg_xlog, and a
> 3-disk RAID0 for the data.  This should triple our current capacity.

I don't know if you can do it, but it would be nice to see this be 1
RAID1 for OS, 1 RAID10 for pg_xlog, and another RAID10 for data. That is
the recommended performance layout. It takes quite a few drives (minimum
of 10). But it means your data is safe, and your performance should be
very good.

>
> The general opinion of the way dspam uses the database among people I've
> talked to on #postgresql is not very good, but of course the dspam folk blame
> PostgreSQL and say to use MySQL if you want reasonable performance.  Makes it
> real fun to be a DSpam+PostgreSQL user when limits are reached, since
> everyone denies responsibility.  Fortunately, PostgreSQL people are pretty
> helpful even if they think the client software sucks. :)
>

I can't say how dspam uses the database. But they certainly could make
assumptions about how certain actions are done by the db, which are not
quite true with postgres. (For instance MySQL can use an index to return
information, because Postgres supports transactions, it cannot, because
even though a row is in the index, it may not be visible to the current
transaction.)

They also might be doing stuff like "select max(row)" instead of "select
row ORDER BY row DESC LIMIT 1". In postgres the former will be a
sequential scan, the latter will be an index scan. Though I wonder about
"select max(row) ORDER BY row DESC LIMIT 1". to me, that should still
return the right answer, but I'm not sure.

> Cheers,

Good luck,
John
=:->

> PostgreSQL and say to use MySQL if you want reasonable performance.

    If you want MySQL performance and reliability with postgres, simply run
it with fsync deactivated ;)
    I'd suggest a controller with battery backed up cache to get rid of the 1
commit = 1 seek boundary.

> Makes it
> real fun to be a DSpam+PostgreSQL user when limits are reached, since
> everyone denies responsibility.  Fortunately, PostgreSQL people are
> pretty
> helpful even if they think the client software sucks. :)
>
> Cheers,

On Mon, Jun 06, 2005 at 10:08:23AM -0500, John A Meinel wrote:
>I don't know if you can do it, but it would be nice to see this be 1
>RAID1 for OS, 1 RAID10 for pg_xlog,

That's probably overkill--it's a relatively small sequential-write
partition with really small writes; I don't see how pg_xlog would
benefit from raid10 as opposed to raid1.

Mike Stone

On Monday 06 June 2005 15:08, John A Meinel wrote:
> Be very careful in this situation. If any disks in a RAID0 fails, the
> entire raid is lost. You *really* want a RAID10. It takes more drives,
> but then if anything dies you don't lose everything.

We have redundancy at the machine level using DRBD, so this is not a concern.

> I don't know if you can do it, but it would be nice to see this be 1
> RAID1 for OS, 1 RAID10 for pg_xlog, and another RAID10 for data. That is
> the recommended performance layout. It takes quite a few drives (minimum
> of 10). But it means your data is safe, and your performance should be
> very good.

The current servers have 4 drive bays, and we can't even afford to fill them
all right now...we just invested what amounts to "quite a lot" on our budget
for these 2 servers, so replacing them is not an option at all right now.

I think the most cost-effective road forward is to add 2 more drives to each
of the existing servers (which currently have 2 each).

Cheers,
--
Casey Allen Shobe | http://casey.shobe.info
cshobe@seattleserver.com | cell 425-443-4653
AIM & Yahoo:  SomeLinuxGuy | ICQ:  1494523
SeattleServer.com, Inc. | http://www.seattleserver.com

Michael Stone wrote:
> On Mon, Jun 06, 2005 at 10:08:23AM -0500, John A Meinel wrote:
>
>> I don't know if you can do it, but it would be nice to see this be 1
>> RAID1 for OS, 1 RAID10 for pg_xlog,
>
>
> That's probably overkill--it's a relatively small sequential-write
> partition with really small writes; I don't see how pg_xlog would
> benefit from raid10 as opposed to raid1.
> Mike Stone
>

pg_xlog benefits from being super fast. Because it has to be fully
synced before the rest of the data can be committed. Yes they are small,
but if you can make it fast, you eliminate that overhead. It also
benefits from having it's own spindle, because you eliminate the seek
time. (Since it is always appending)

Anyway, my point is that pg_xlog isn't necessarily tiny. Many people
seem to set it as high as 100-200, and each one is 16MB.

But one other thing to consider is to make pg_xlog on a battery backed
ramdisk. Because it really *can* use the extra speed. I can't say that a
ramdisk is more cost effective than faster db disks. But if you aren't
using many checkpoint_segments, it seems like you could get a 1GB
ramdisk, and probably have a pretty good performance boost. (I have not
tested this personally, though).

Since he is using the default settings (mostly) for dspam, he could
probably get away with something like a 256MB ramdisk.

The only prices I could find with a few minutes of googleing was:
http://www.cenatek.com/store/category.cfm?Category=15
Which is $1.6k for 2GB.

But there is also a product that is being developed, which claims $60
for the PCI card, you supply the memory. It has 4 DDR slots
http://www.engadget.com/entry/1234000227045399/
And you can get a 128MB SDRAM ECC module for around $22
http://www.newegg.com/Product/Product.asp?Item=N82E16820998004
So that would put the total cost of a 512MB battery backed ramdisk at
$60 + 4*22 = $150.

That certainly seems less than what you would pay for the same speed in
hard-drives.
Unfortunately the Giga-byte iRam seems to just be in the demo stage. But
if they aren't lying in the press releases, it would certainly be
something to keep an eye on.

John
=:->

On Mon, Jun 06, 2005 at 10:52:09AM -0500, John A Meinel wrote:
>pg_xlog benefits from being super fast. Because it has to be fully
>synced before the rest of the data can be committed. Yes they are small,
>but if you can make it fast, you eliminate that overhead. It also
>benefits from having it's own spindle, because you eliminate the seek
>time. (Since it is always appending)

Eliminating the seeks is definately a win.

>Anyway, my point is that pg_xlog isn't necessarily tiny. Many people
>seem to set it as high as 100-200, and each one is 16MB.

It's not the size of the xlog, it's the size of the write. Unless you're
writing out a stripe size of data at once you're only effectively
writing to one disk pair at a time anyway. (Things change if you have a
big NVRAM cache to aggregate the writes, but you'd need a *lot* of
transaction activity to exceed the 50MB/s or so you could get from the
single raid1 pair in that scenario.)

Mike Stone

On Thu, 2 Jun 2005 06:19 am, Casey Allen Shobe wrote:
> I found this response to my original post, and tried every single suggestion
> in it, which has not helped:
>
> http://archives.postgresql.org/pgsql-performance/2004-11/msg00416.php
>
> I'm sorry to come begging for help, but this is a MAJOR problem with no
> logical explanation, and is almost certainly the fault of PostgreSQL, because
> the database and contents have been identical across all the hosts, and some
> work beautifully with no tuning whatsoever; so I don't feel I'm wrong in
> placing blame...

I would personally strongly suggest turing on logging
on the PG server for about an hour, sifting through the runtimes for the queries and
finding which ones are taking all the time.  I'd then run explain analyze and see what
is happening.  I have heard you could get much better performance by rewriting some of
the dspam queries to use PG features.  But I've never used dspam, so I can't verify that.

But a quick look through the dspam pg driver source...

    /* Declare Cursor */
#ifdef VIRTUAL_USERS
    strcpy (query, "DECLARE dscursor CURSOR FOR SELECT DISTINCT username FROM dspam_virtual_uids");
#else
    strcpy (query, "DECLARE dscursor CURSOR FOR SELECT DISTINCT uid FROM dspam_stats");
#endif

If that's run often, it probably won't give the best performance, but that's a guess.
Again I'd suggest turning up the logging.


>
> All machines run Gentoo Linux.  All have the same package versions.  Disk I/O
> doesn't seem to be related - the 733MHz server had a 33MB/s IDE drive, the
> 2.4GHz server had a RAID 5 with 3 ultra320 drives:  neither of those required
> any tuning.  The new 3.0GHz has a mirror raid with 2 ultra320 drives, and the
> 3000+ that tuning fixed had an ultra160 disk not in a RAID.
>
> I really like PostgreSQL, and really don't want to use MySQL for dspam, but if
> I can't get this worked out ASAP I'm going to have to change for the sake of
> our customers.  Any help is GREATLY appreciated!
Again I'd suggest turning up the logging.


>
> I'm online on instant messengers (contact IDs shown below), monitoring my
> email, and will be on #postgresql on Freenode.
>
> Cheers,