From: Merlin Moncure <mmoncure@gmail.com>
To: dforums <dforums@vieonet.com>
Cc: Richard Huxton <dev@archonet.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Plz Heeeelp! performance settings
Date: Thu, 7 Aug 2008 11:05:30 -0400
Message-ID: <b42b73150808070805p3f09f7e8t76e24b93063ca157@mail.gmail.com> (raw)
In-Reply-To: <489AF8E8.6070503@vieonet.com>
References: <35FABCF85D99464FB00BC5123DC2A70A051D0FB8@s228130hz1ew09.apptix-01.savvis.net>
<1240.1217440309@sss.pgh.pa.us>
<1217441061.6288.47.camel@localhost>
<489A21C2.8060801@vieonet.com>
<b42b73150808061916j74fd5334y318ef3819fa49ea8@mail.gmail.com>
<489AA5B5.9080109@vieonet.com>
<489AB375.3090901@archonet.com>
<489ABC8F.4030007@vieonet.com>
<489AD118.5010902@archonet.com>
<489AF8E8.6070503@vieonet.com>
On Thu, Aug 7, 2008 at 9:30 AM, dforums <dforums@vieonet.com> wrote:
> The performance problem is really only on the insertion and even more on the
> treatment for the aggregation.
>
> To treat the 3000 entrances and to insert, or update the tables it needs 10
> minutes.
>
> As I told you I inject 14000 query every 2 minutes, and it needs 10 minutes
> to treat 3000 of those query.
>
> As you can easly understand it's a big narrow section.
>
> I'm not doing the treatment in ones, cause I can't, but all is managed by
> procedure.
>
>> That wasn't a yes/no question. Please choose one of:
>> Are you updating 6Go per week? most of update
>> Are you adding 6Go per week? less of injection,
>
> This action depend if the data are already present in the database.
>
>
>>
>> OK. I assume you're happy with the plans you are getting on these
>> queries, since you've not provided any information about them.
>
> The plan seems ok as it use index as well.
> here is the plan :
>
> explain analyse SELECT "insertUpdateTracks"(137,2605, 852, ('2008-08-06
> 19:28:54'::text)::date,3,'dailydisplay',2,NULL);
> INFO: method 1
> QUERY PLAN
> ------------------------------------------------------------------------------------
> Result (cost=0.00..0.01 rows=1 width=0) (actual time=1.151..1.151 rows=1
> loops=1)
> Total runtime: 1.160 ms
> (2 lignes)
>
> Has you can see the runtime processs for an update in this table.
>
> multiplying this per 10000, it is too long.
>
please don't top-post (put your reply after previous comments).
With fsync on, you are lucky to get 10k inserts in 10 minutes on
single sata 1. The basic issue is that after each time function runs
postgesql tells disk drive to flush, guaranteeing data safety. You
have few different options here:
*) group multiple inserts into single transaction
*) modify function to take multiple 'update' records at once.
*) disable fsync (extremely unsafe as noted)
*) upgrade to 8.3 and disable synchronized_commit (the 'fsync lite', a
good compromise between fsync on/off)
merlin
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: mmoncure@gmail.com, dforums@vieonet.com, dev@archonet.com
Subject: Re: Plz Heeeelp! performance settings
In-Reply-To: <b42b73150808070805p3f09f7e8t76e24b93063ca157@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