pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Qingqing Zhou <zhouqq@cs.toronto.edu>
To: pgsql-performance@postgresql.org
Subject: Re: How many tables is too many tables?
Date: Wed, 14 Sep 2005 18:49:06 -0700
Message-ID: <dga9d3$23n2$1@news.hub.org> (raw)
References: <1126643032.787041.127400@z14g2000cwz.googlegroups.com>


<cydatamatt@gmail.com> wrote
>
> One machine is simply not going to be able to scale with the quantities
> of links we hope to store information about and we want to move to some
> kind of cluster.  Because of the quantities of data, it seems to make
> sense to go for a cluster setup such that in a 4 machine cluster, each
> machine has a quarter of the data (is this "Share nothing," or, "Share
> everything"?).  To that end, we figured a good first step was to
> partition the data on one machine into multiple tables defining the
> logic which would find the appropriate table given a piece of data.
> Then, we assumed, adding the logic to find the appropriate machine and
> database in our cluster would only be an incremental upgrade.
>

So you set up 4 separate copies of PG in 4 machines? This is neither SN or 
SE.

The partition is good for performance if you distribute IOs and CPUs. In 
your design, I believe IO is distributed (to 4 machines), but since you 
sliced data into too small pieces, you will get penality from other places. 
For example, each table has to maintain separate indices (index becomes an 
useless burden when table is too small), so there will be so many Btree root 
...  System tables (pg_class/pg_attribute, etc) has to contains many rows to 
record your tables ... though we cached system table rows, but the memory 
space is limited ...

In short, too many tables. To design your new partition method, jsut keep in 
mind that database access data in a page-wise IO.

Regards,
Qingqing 

view thread (4+ messages)  latest in thread

Message-ID: <dga9d3$23n2$1@news.hub.org>
Permalink:  ../dga9d3$23n2$1@news.hub.org/
Also on:    postgresql.org/message-id/dga9d3$23n2$1@news.hub.org

copy link · copy postgr.es

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: zhouqq@cs.toronto.edu
  Subject: Re: How many tables is too many tables?
  In-Reply-To: <dga9d3$23n2$1@news.hub.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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