Greets,
I have recently been trying out table partitioning on 8.3.7 (on our
master->slaves cluster) and am experiencing an unexpectedly high file
descriptor penalty ('high', of course, being a relative term).
Before partitioning there were about 70 tables and lots of indexes and
file descriptor consumption would be 'normal'.
I successfully partitioned several very large multi-GB tables
(resulting in about 794/4827 tables/indexes). SELECTs are noticeably
faster, etc. Lovely.
However, once I start real processing and there's lots of updates and
selects occurring, file descriptor usage (by many Pg procs) will
gleefully claw it's way up to 400k (on the busy cluster master node)
and eventually bludgeon my arbitrary 500k limit. Each Pg process eats
about 991 fd's.
Slaves will hover around 180k - with low activity. I'm now dubious
about making them really work (with partitioning)... considering how
the master is being brought to it's knees.
~/data/base contains ~7700 files, many 0 length.
The machines are *slower* than they were without partitioning since
they're now swapping like crazy which I presume is a result of far
greater resource usage (data may be the same, but the underlying
structure is now huge).
So, is this expected? In other words, is the price of partitioning
monstrous additional resource usage? Certainly seems like it.
If this is true, then I'll have to roll back since partitioning might
make SELECTs faster, but at the price of a machine so burdened as to
be unusable.
Simply throwing more RAM at it is an obvious solution, but the idea
with using partitions (ironically) was to reduce memory consumption
and improve SQL performance since the most heavily used data would be
in smaller tables (partitions).
Thoughts?
Cheers
Henry