pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
TableSpace Design issues on Postgres 8.0 beta 5
4+ messages / 2 participants
[nested] [flat]

* TableSpace Design issues on Postgres 8.0 beta 5
@ 2004-12-06 13:52 Rosny <sehatrosny@hotmail.com>
  2004-12-06 20:28 ` Re: TableSpace Design issues on Postgres 8.0 beta 5 Rosny <sehatrosny@hotmail.com>
  2004-12-07 04:44 ` Re: TableSpace Design issues on Postgres 8.0 beta 5 Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 4+ messages in thread

From: Rosny @ 2004-12-06 13:52 UTC (permalink / raw)
  To: pgsql-performance

Hi,

This kind of long email.

 After searching the mailing list, have not found good answer
 for TableSpace. So, I try to post this question.

My question
Question :
  1 Which option from below scenario will be good in term of performance and
future scalability?
  2. Is it Option B1 below the right approach?
  3. Is progresql will have problems if I have 7000 tablespace?

------------------------------------------------------------------- 
Environment :
 - Windows 2003
 - Postgresql 8.0 beta 5

Scenario :
Original Design:
  Total Tables 40:
     - 20 tables are main tables responsible for the others 20 tables
     - the others 20 tables are specific for each department.
     - from these 20 tables(departments)
        there are 4-5 tables that will contain approx 20 millions records
       (these tables will be hit every times access to the website).

Refering to 20 tables which can be partition
A. All departments tables is put into 20 tables.
   some querying of 20 millions records.

B. For each department create tablespace. (Which means, if there
 are 7000 departments, there will be 7000 tablespace each contains
 20 tables).


Question : Which option will be good in term of performance
           and future scalability?

A1. Use A option,
    As tables become huge. partition the tables which hits often
    and has large size file(usually when it bigger than 2-3 GB size)
    into separate tablespace.

    Problems in A1 approach :
      1. query take very long. It might be resolved
        - indexing, better written pgsql statement.

    Advantage : total files are small. around 1000 in one directory


B1. Use B option,
    Creating 7000 TableSpace for Departments
      - One Department has one tablespace
      - Each Department has 20 tables

    Advantage :
      - each table is small and query is very fast.
      - scalability. As the sites grows, contents grows. will
        not effect future scalability as much as A1.
        in A1 the query already max out for performance partition.
        in B1 the query has not max out yet because the data is
        already distribute across thousands of tables

    Disadvantage:
      - total numbers of files is huge.
        (after creating 7000 tablespace, and start
         table automatic generator to create 20 tables
         for each 7000 tablespace.
         After running the 1500th tablespace.
         Each TableSpace has : 35 files
         Surprisingly the default table space already has 20000 files)
       - Need to use dynamic table name query. (this is ok,
         since there are not very complex sql statement logic)

I am trying to choose option B1, as it is good for future scability.

Question :
  1. Is it B1 the right approach?
  2. Is progresql will have problems if I have 7000 tablespace?


Thank you,
Rosny

note: previously posted on cygwin. but I think it is more appropriate for
this group

















^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: TableSpace Design issues on Postgres 8.0 beta 5
  2004-12-06 13:52 TableSpace Design issues on Postgres 8.0 beta 5 Rosny <sehatrosny@hotmail.com>
@ 2004-12-06 20:28 ` Rosny <sehatrosny@hotmail.com>
  1 sibling, 0 replies; 4+ messages in thread

From: Rosny @ 2004-12-06 20:28 UTC (permalink / raw)
  To: pgsql-performance

Additional update:
After spending time in searching more option.
Both Postgresql and MySQL Claims
1. Postgresql:
http://www.postgresql.org/users-lounge/limitations.html
2.MySQL
(Drawback creating large numbers of tables)
http://forums.devshed.com/t27778/s.html

This might sound like crazy ideas. I just try to experiment several option
which are easy todo before developing).

After running auto generate tables 12 tables each for each of 1600
Tablespaces
Here are some statistics
1. For every 12 tables created in each Tablespace will create 35 files in
tablespace directory
2. For every 12 tables for each 1 tables space will creates additional
approx 17 additional new(eventhough it has it's own   tablespace files for
default database directory
3. The above screnario creating 19200 tables in database
4. It take approximately 2 hours for pgAdmin III when starting just to load
all tables info when first starting.
    Good news(it did not hang, it able to handle all 19200 tables once
loaded and easily browse all tables info)

I have not try to actually query the database from the web.
Seems like the whole approach of "large number of tables" is not good.
(As College Database Design course said....:):).

By creating 12 tables for each of 7000 Tablespace. Now files in folder is
not as huge as when everything in one TableSpace.


Any opinion is welcome...



"Rosny" <sehatrosny@hotmail.com> wrote in message
news:cp1o7f$13ad$2@news.hub.org...
> Hi,
>
> This kind of long email.
>
>  After searching the mailing list, have not found good answer
>  for TableSpace. So, I try to post this question.
>
> My question
> Question :
>   1 Which option from below scenario will be good in term of performance
and
> future scalability?
>   2. Is it Option B1 below the right approach?
>   3. Is progresql will have problems if I have 7000 tablespace?
>
> ------------------------------------------------------------------- 
> Environment :
>  - Windows 2003
>  - Postgresql 8.0 beta 5
>
> Scenario :
> Original Design:
>   Total Tables 40:
>      - 20 tables are main tables responsible for the others 20 tables
>      - the others 20 tables are specific for each department.
>      - from these 20 tables(departments)
>         there are 4-5 tables that will contain approx 20 millions records
>        (these tables will be hit every times access to the website).
>
> Refering to 20 tables which can be partition
> A. All departments tables is put into 20 tables.
>    some querying of 20 millions records.
>
> B. For each department create tablespace. (Which means, if there
>  are 7000 departments, there will be 7000 tablespace each contains
>  20 tables).
>
>
> Question : Which option will be good in term of performance
>            and future scalability?
>
> A1. Use A option,
>     As tables become huge. partition the tables which hits often
>     and has large size file(usually when it bigger than 2-3 GB size)
>     into separate tablespace.
>
>     Problems in A1 approach :
>       1. query take very long. It might be resolved
>         - indexing, better written pgsql statement.
>
>     Advantage : total files are small. around 1000 in one directory
>
>
> B1. Use B option,
>     Creating 7000 TableSpace for Departments
>       - One Department has one tablespace
>       - Each Department has 20 tables
>
>     Advantage :
>       - each table is small and query is very fast.
>       - scalability. As the sites grows, contents grows. will
>         not effect future scalability as much as A1.
>         in A1 the query already max out for performance partition.
>         in B1 the query has not max out yet because the data is
>         already distribute across thousands of tables
>
>     Disadvantage:
>       - total numbers of files is huge.
>         (after creating 7000 tablespace, and start
>          table automatic generator to create 20 tables
>          for each 7000 tablespace.
>          After running the 1500th tablespace.
>          Each TableSpace has : 35 files
>          Surprisingly the default table space already has 20000 files)
>        - Need to use dynamic table name query. (this is ok,
>          since there are not very complex sql statement logic)
>
> I am trying to choose option B1, as it is good for future scability.
>
> Question :
>   1. Is it B1 the right approach?
>   2. Is progresql will have problems if I have 7000 tablespace?
>
>
> Thank you,
> Rosny
>
> note: previously posted on cygwin. but I think it is more appropriate for
> this group
>
>
>
>
>
>
>
>
>
>
>
>
>
>





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: TableSpace Design issues on Postgres 8.0 beta 5
  2004-12-06 13:52 TableSpace Design issues on Postgres 8.0 beta 5 Rosny <sehatrosny@hotmail.com>
@ 2004-12-07 04:44 ` Tom Lane <tgl@sss.pgh.pa.us>
  2004-12-07 07:58   ` Re: TableSpace Design issues on Postgres 8.0 beta 5 Rosny <sehatrosny@hotmail.com>
  1 sibling, 1 reply; 4+ messages in thread

From: Tom Lane @ 2004-12-07 04:44 UTC (permalink / raw)
  To: Rosny <sehatrosny@hotmail.com>; +Cc: pgsql-performance

"Rosny" <sehatrosny@hotmail.com> writes:
> B. For each department create tablespace. (Which means, if there
>  are 7000 departments, there will be 7000 tablespace each contains
>  20 tables).

If your system has seven thousand separate logical filesystems attached
to it, there might be some value in having seven thousand tablespaces.
But I will bet a great deal that it does not and there isn't.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: TableSpace Design issues on Postgres 8.0 beta 5
  2004-12-06 13:52 TableSpace Design issues on Postgres 8.0 beta 5 Rosny <sehatrosny@hotmail.com>
  2004-12-07 04:44 ` Re: TableSpace Design issues on Postgres 8.0 beta 5 Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-12-07 07:58   ` Rosny <sehatrosny@hotmail.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Rosny @ 2004-12-07 07:58 UTC (permalink / raw)
  To: pgsql-performance

Thanks for the response.

I just start to get a feel of where TableSpace will be used. You are right I
do not have 7000 logical filesystems.
I am assuming  using TableSpace as organization of files in folders in
Windows 2003 Environment. So, each
TableSpace will represent one folder(directory) in a drive. But this doesnot
work to well for my design.

Since pgAdmin III GUI take 2 hours just to load approximately 14000 tables.
I am not using TableSpace approach anymore. I am using multiple database
approach.

Anyway, thanks for the response. Today testing by taking several option to
the limit. I kind of having some ideas
for future scability.

Rosny


"Tom Lane" <tgl@sss.pgh.pa.us> wrote in message
news:10164.1102394676@sss.pgh.pa.us...
> "Rosny" <sehatrosny@hotmail.com> writes:
> > B. For each department create tablespace. (Which means, if there
> >  are 7000 departments, there will be 7000 tablespace each contains
> >  20 tables).
>
> If your system has seven thousand separate logical filesystems attached
> to it, there might be some value in having seven thousand tablespaces.
> But I will bet a great deal that it does not and there isn't.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>





^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2004-12-07 07:58 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-12-06 13:52 TableSpace Design issues on Postgres 8.0 beta 5 Rosny <sehatrosny@hotmail.com>
2004-12-06 20:28 ` Rosny <sehatrosny@hotmail.com>
2004-12-07 04:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-12-07 07:58   ` Rosny <sehatrosny@hotmail.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