From: Daniel van Ham Colchete <daniel.colchete@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: New to PostgreSQL, performance considerations
Date: Sun, 10 Dec 2006 23:15:31 -0200
Message-ID: <8a0c7af10612101715p6a1b00e6yc33b8ea0b95a683d@mail.gmail.com> (raw)
In-Reply-To: <457C9DA8.1000403@007Marketing.com>
References: <8a0c7af10612101141n2c7727c3r2f92345753960808@mail.gmail.com>
<457C9DA8.1000403@007Marketing.com>
Shane,
really good answer man!!! It really helped me!
I can't wait to have time to test this partitions thing. This will
really solve a lot of my problems (and another one that I was
delaying).
Instead of making partitions over days/weeks I can tune it to the
seconds (timestamp) and try to find the best partition size for
performance. That partition thing is really good.
That server you sent me handles about 15 times more writes than I would have to.
Thank you very much for your answer.
Best regards,
Daniel Colchete
On 12/10/06, Shane Ambler <pgsql@007marketing.com> wrote:
> Daniel van Ham Colchete wrote:
> > although I've worked with databases for more than 7 years now, I'm
> > petty new to PostgreSQL.
>
> Same here.
>
> > I need a db that can handle something like 500 operations/sec
> > continuously. It's something like 250 writes/sec and 250 reads/sec. My
> > databases uses indexes.
>
> Taken from an email to the admin list about a week ago -
>
> Stats about the system:
> Postgres 8.1.4
> db size: 200+ GB
> Inheritance is used extremely heavily, so in figuring out what could
> cause a create to hang, it may be of interest to know that there are:
> 101,745 tables
> 314,821 indexes
> 1,569 views
> The last averages taken on the number of writes per hour on this
> database: ~3 million (this stat is a few weeks old)
>
> Machine info:
> OS: Solaris 10
> Sunfire X4100 XL
> 2x AMD Opteron Model 275 dual core procs
> 8GB of ram
>
>
> > Each table would have to handle 5 million rows/day. So I'm thinking
> > about creating different tables (clusters?) to different days to make
> > queries return faster. Am I right or there is no problem in having a
> > 150 million (one month) rows on a table?
>
> Sounds to me that a month might be on the large size for real fast
> response times - I would think of seperating weekly rather than daily.
>
> Start with
> http://www.postgresql.org/docs/8.2/interactive/ddl-inherit.html
> then the next chapter explains using that to partition data into
> different tables dependant on specified criteria.
>
> You may be interested in tsearch2 which is in the contrib dir and adds
> full text indexing.
>
>
> --
>
> Shane Ambler
> pgSQL@007Marketing.com
>
> Get Sheeky @ http://Sheeky.Biz
>
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: daniel.colchete@gmail.com
Subject: Re: New to PostgreSQL, performance considerations
In-Reply-To: <8a0c7af10612101715p6a1b00e6yc33b8ea0b95a683d@mail.gmail.com>
* 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