Re: big database performance

On Thu, Jan 10, 2008 at 10:57:46AM +0200, Adrian Moisey wrote:
> What sort of information do you need from me ?

Ratio of read vs write operations (select vs insert/copy).

average number of indicies per table

average table size. (analyze verbose <tablename> if you want to get
into more details).

What is the process doing (eg: in top, is it just on the CPU or
is it blocking for I/O?).

I/O information, from iostat -d (You may need to build an iostat
binary for Linux, the source is out there, i can give you a pointer if
you need it).

>> Is your problem with performance database reads? writes? (insert/copy?)
>> How many indicies do you have?
>
> I think the problem is related to load. Everything is slow because there
> are way too many connections. So everything is making everything else
> slow. Not much detail, is it?
>
> We have 345 indicies on the db.

If the tables are heavily indexed this could easily slow down
insert performance. Taking a large dataset and adding a second
index, postgres doesn't use threads to create the two indicies on
different cpus/cores in parallel. This could represent some of your
performance difference. If you're doing a lot of write operations
and fewer read, perhaps the cost of an index isn't worth it in the
cpu time spent creating it vs the amount of time for a seq scan.

- Jared

--
Jared Mauch | pgp key available via finger from jared(at)puck(dot)nether(dot)net
clue++; | http://puck.nether.net/~jared/ My statements are only mine.