On Dec 5, 2007, at 22:34, David Rees wrote:
On Dec 5, 2007 12:53 PM, Kenneth Marshall <[EMAIL PROTECTED]> wrote:
On Wed, Dec 05, 2007 at 12:29:13PM -0800, David Rees wrote:
It would be interesting to find out why PostgreSQL was
underperforming
for you. What kind of tuning have you tried? I suspect that the new
async commit options in 8.3 would improve things a lot.
The performance problem was caused by the I/O pattern degrading to
completely random I/O over time. The initial performance or the
performance immediately after a CLUSTER of the tables was easily
as good as the MySQL backend (~20 messages/sec). The steady state
(~6 messages/sec) did not provide sufficient headroom for our
system to allow for clearing out a backlog following any type
of delivery problem. The async commit option in 8.3 will help, but
I expect the HOT updates to be the real win, by allowing the tables
to maintain their cluster order over time. We will report back once
we have some test data.
Thanks, that is one performance concern I have with PostgreSQL
frequently updated/inserted tables/indexes over time, they tend to get
fragmented. I suspect that using XFS along with regular runs of
xfs_fsr would take care of any fragmentation issues, but also agree
that the HOT patches should also help significantly. Looking at the
tables/indexes on one of my systems reveals fairly significant
table/index bloat.
AFAIK it's not the file that gets fragmented, but its contents.
It can grow significantly too because of that (twice as large is not
uncommon for index files if you don't vacuum frequently, for example).
If you have many updates on a table you need to vacuum it more often
to turn outdated records into reusable space, so it usually pretty
much comes down to your settings (vacuum timings, workmem, that
stuff). Clustering a table has the same end result, but that's not
really meant for solving this problem and is in many cases not an
adequate tool for this job.
There was some work going on creating multiple vacuum worker
processes so vacuums on large tables wouldn't prevent small tables
from being vacuumed in the meantime. That's probably in 8.3, but I
haven't been following the PG ML much as of late.
Also, I was told it's apparently better to use a fast but adequately
robust filesystem than a slower but very robust filesystem, as
PostgreSQL pretty much handles its own files. That doesn't mean that
it's insensitive to filesystem errors of course, there used to be a
problem causing data-file corruption due to an obscure bug in the
history rewriting in ext3fs, for example.
Regards,
--
Alban Hertroys
"If you throw your hands up in the air,
how're you gonna catch them?"
!DSPAM:5,475918429651398015752!
