Re: Performance nightmare with dspam (urgent) (resolved) - Mailing list pgsql-performance

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