pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Performance nightmare with dspam (urgent) (resolved)
7+ messages / 4 participants
[nested] [flat]
* Re: Performance nightmare with dspam (urgent) (resolved)
@ 2005-06-06 14:54 Casey Allen Shobe <lists@seattleserver.com>
0 siblings, 2 replies; 7+ messages in thread
From: Casey Allen Shobe @ 2005-06-06 14:54 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Performance nightmare with dspam (urgent) (resolved)
@ 2005-06-06 15:08 John A Meinel <john@arbash-meinel.com>
parent: Casey Allen Shobe <lists@seattleserver.com>
1 sibling, 2 replies; 7+ messages in thread
From: John A Meinel @ 2005-06-06 15:08 UTC (permalink / raw)
To: Casey Allen Shobe <lists@seattleserver.com>; +Cc: pgsql-performance
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
=:->
Attachments:
signature.asc (application/pgp-signature, 250B) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Performance nightmare with dspam (urgent) (resolved)
@ 2005-06-06 15:11 PFC <lists@boutiquenumerique.com>
parent: Casey Allen Shobe <lists@seattleserver.com>
1 sibling, 0 replies; 7+ messages in thread
From: PFC @ 2005-06-06 15:11 UTC (permalink / raw)
To: Casey Allen Shobe <lists@seattleserver.com>; pgsql-performance
> 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,
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Performance nightmare with dspam (urgent) (resolved)
@ 2005-06-06 15:19 Michael Stone <mstone+postgres@mathom.us>
parent: John A Meinel <john@arbash-meinel.com>
1 sibling, 1 reply; 7+ messages in thread
From: Michael Stone @ 2005-06-06 15:19 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Performance nightmare with dspam (urgent) (resolved)
@ 2005-06-06 15:52 John A Meinel <john@arbash-meinel.com>
parent: Michael Stone <mstone+postgres@mathom.us>
0 siblings, 1 reply; 7+ messages in thread
From: John A Meinel @ 2005-06-06 15:52 UTC (permalink / raw)
To: Michael Stone <mstone+postgres@mathom.us>; +Cc: pgsql-performance
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
=:->
Attachments:
signature.asc (application/pgp-signature, 250B) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Performance nightmare with dspam (urgent) (resolved)
@ 2005-06-06 16:04 Casey Allen Shobe <lists@seattleserver.com>
parent: John A Meinel <john@arbash-meinel.com>
1 sibling, 0 replies; 7+ messages in thread
From: Casey Allen Shobe @ 2005-06-06 16:04 UTC (permalink / raw)
To: John A Meinel <john@arbash-meinel.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Performance nightmare with dspam (urgent) (resolved)
@ 2005-06-06 17:27 Michael Stone <mstone+postgres@mathom.us>
parent: John A Meinel <john@arbash-meinel.com>
0 siblings, 0 replies; 7+ messages in thread
From: Michael Stone @ 2005-06-06 17:27 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2005-06-06 17:27 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-06 14:54 Re: Performance nightmare with dspam (urgent) (resolved) Casey Allen Shobe <lists@seattleserver.com>
2005-06-06 15:08 ` John A Meinel <john@arbash-meinel.com>
2005-06-06 15:19 ` Michael Stone <mstone+postgres@mathom.us>
2005-06-06 15:52 ` John A Meinel <john@arbash-meinel.com>
2005-06-06 17:27 ` Michael Stone <mstone+postgres@mathom.us>
2005-06-06 16:04 ` Casey Allen Shobe <lists@seattleserver.com>
2005-06-06 15:11 ` PFC <lists@boutiquenumerique.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox