From: Jared Mauch <jared@puck.nether.net>
To: Adrian Moisey <adrian@careerjunction.co.za>
Cc: Jared Mauch <jared@puck.nether.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: big database performance
Date: Thu, 10 Jan 2008 09:21:22 -0500
Message-ID: <20080110142122.GA79941@puck.nether.net> (raw)
In-Reply-To: <4785DE0A.6020501@careerjunction.co.za>
References: <4784835A.8070604@careerjunction.co.za>
<47848575.8000007@commandprompt.com>
<20080109174243.GA8915@puck.nether.net>
<4785DE0A.6020501@careerjunction.co.za>
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.
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: jared@puck.nether.net, adrian@careerjunction.co.za
Subject: Re: big database performance
In-Reply-To: <20080110142122.GA79941@puck.nether.net>
* 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