Re: big database performance - Mailing list pgsql-performance

From Jared Mauch
Subject Re: big database performance
Date
Msg-id 20080110142122.GA79941@puck.nether.net
Whole thread Raw
In response to Re: big database performance  (Adrian Moisey <adrian@careerjunction.co.za>)
List pgsql-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@puck.nether.net
clue++;      | http://puck.nether.net/~jared/  My statements are only mine.

pgsql-performance by date:

Previous
From: Oleg Bartunov
Date:
Subject: Re: Search for fixed set of keywords
Next
From: Jared Mauch
Date:
Subject: Re: big database performance