pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedthe number of child tables --table partitioning
5+ messages / 5 participants
[nested] [flat]
* the number of child tables --table partitioning
@ 2011-09-29 14:14 Jian Shi <jshi@unitrends.com>
0 siblings, 1 reply; 5+ messages in thread
From: Jian Shi @ 2011-09-29 14:14 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: the number of child tables --table partitioning
@ 2011-09-29 15:08 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Jian Shi <jshi@unitrends.com>
0 siblings, 1 reply; 5+ messages in thread
From: Kevin Grittner @ 2011-09-29 15:08 UTC (permalink / raw)
To: pgsql-performance; Jian Shi <jshi@unitrends.com>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: the number of child tables --table partitioning
@ 2011-09-29 21:12 Ondrej Ivanič <ondrej.ivanic@gmail.com>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 5+ messages in thread
From: Ondrej Ivanič @ 2011-09-29 21:12 UTC (permalink / raw)
To: pgsql-performance
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)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: the number of child tables --table partitioning
@ 2011-09-30 17:01 Merlin Moncure <mmoncure@gmail.com>
parent: Ondrej Ivanič <ondrej.ivanic@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Merlin Moncure @ 2011-09-30 17:01 UTC (permalink / raw)
To: Ondrej Ivanič <ondrej.ivanic@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: the number of child tables --table partitioning
@ 2011-09-30 17:13 alexandre - aldeia digital <adaldeia@gmail.com>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: alexandre - aldeia digital @ 2011-09-30 17:13 UTC (permalink / raw)
To: pgsql-performance
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!
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2011-09-30 17:13 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-09-29 14:14 the number of child tables --table partitioning Jian Shi <jshi@unitrends.com>
2011-09-29 15:08 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-09-29 21:12 ` Ondrej Ivanič <ondrej.ivanic@gmail.com>
2011-09-30 17:01 ` Merlin Moncure <mmoncure@gmail.com>
2011-09-30 17:13 ` alexandre - aldeia digital <adaldeia@gmail.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