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
Attachment:
pgpBIYaUu1Hwa.pgp
Description: PGP Digital Signature
