Обсуждение: the number of child tables --table partitioning
Hey,
Is there a suggested number of child tables for table partitioning, I ran a stress test on a master
table (with 800 thousand rows), trying to create 500,000 child tables for it, each child table has 2
indexes and 3 constraints (Primary key and foreign key). I wrote a script to do it: after 17 hours,
only 7600 child tables are created.
The script is still running, I can see that one child tables is created about every minute. The CPU
Usage is 100%. The query speed is really slow now (I set constraint_exclusion=on).
This stress test is for the partition plan I’m going to make, since we don’t want to add another
Field just for partitioning. So is there something I did wrong? Or postgres cannot handle too many
Child tables? That way I need to come up with a new partition plan.
The system is 32-bit Linux, dual core, 4G memory. Postgres version is 8.1.21.
Thanks,
John
Jian Shi <jshi@unitrends.com> wrote: [moving the last sentence to the top] > The system is 32-bit Linux, dual core, 4G memory. Postgres version > is 8.1.21. Version 8.1 is out of support and doesn't perform nearly as well as modern versions. http://wiki.postgresql.org/wiki/PostgreSQL_Release_Support_Policy The system you're talking about is the same as what I bought as a home computer four years ago. You don't mention your disk system, but that doesn't sound like server-class hardware to me. > Is there a suggested number of child tables for table > partitioning, Generally, don't go over about 100 partitions per table. > I ran a stress test on a master table (with 800 thousand rows), > trying to create 500,000 child tables for it, each child table has > 2 indexes and 3 constraints (Primary key and foreign key). That probably at least 5 disk files per table, to say nothing of the system table entries and catalog caching. Some file systems really bog down with millions of disk files in a single subdirectory. That is never going to work on the hardware you cite, and is a very, very, very bad design on any hardware. > This stress test is for the partition plan I'm going to make, > since we don't want to add another Field just for partitioning. Why not? -Kevin
Hi, On 30 September 2011 01:08, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: >> Is there a suggested number of child tables for table >> partitioning, > > Generally, don't go over about 100 partitions per table. Having 365 partitions per table is fine... -- Ondrej Ivanic (ondrej.ivanic@gmail.com)
2011/9/29 Ondrej Ivanič <ondrej.ivanic@gmail.com>: > Hi, > > On 30 September 2011 01:08, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: >>> Is there a suggested number of child tables for table >>> partitioning, >> >> Generally, don't go over about 100 partitions per table. > > Having 365 partitions per table is fine... yeah -- the system was certainly designed to support 'dozens to hundreds', but 'hundreds of thousands' is simply not realistic. any measurable benefit gained from partitioning is going to be var exceeded by the database having to track so many tables. btw, partitioning for purposes of performance is a dubious strategy unless you can leverage non-uniform access patterns of the data or do other tricks that allow simplification of structures (like removing 'company_id' from all tables and indexes because it's implied by the partition itself). merlin
Em 30-09-2011 14:01, Merlin Moncure escreveu: > 2011/9/29 Ondrej Ivanič<ondrej.ivanic@gmail.com>: >> Hi, >> >> On 30 September 2011 01:08, Kevin Grittner<Kevin.Grittner@wicourts.gov> wrote: >>>> Is there a suggested number of child tables for table >>>> partitioning, >>> Generally, don't go over about 100 partitions per table. >> Having 365 partitions per table is fine... > yeah -- the system was certainly designed to support 'dozens to > hundreds', but 'hundreds of thousands' is simply not realistic. any > measurable benefit gained from partitioning is going to be var > exceeded by the database having to track so many tables. > > btw, partitioning for purposes of performance is a dubious strategy > unless you can leverage non-uniform access patterns of the data or do > other tricks that allow simplification of structures (like removing > 'company_id' from all tables and indexes because it's implied by the > partition itself). > > merlin > Can we see the transparent table partitioningimplemented in Postgres 9.2 version before the end of the world in 2012? ;) Today, it is very difficult to maintain table partitioning schemes even with small number of partitions. Anyway, congrats for the superb 9.1 version!