DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version
	:content-type:content-transfer-encoding:content-disposition
	:references;
	b=XAy05XJy6zvbtpdg/msJRYKju+vC++fuKXapce8h+JEmIWTZ3wx5/mRoHlEXLn7d7F
	MISlzMLc0C2aLa8zGAw5I+tyABbGCNxVU3OnLcWNEBDt8+yleH/mYNrZ2AbSvaYiUY7B
	ePYv/CN/EdKcPVvVi1f6R4s9E3BplhLMbdp8U=
Message-ID: <b42b73150808070805p3f09f7e8t76e24b93063ca157@mail.gmail.com>
Date: Thu, 7 Aug 2008 11:05:30 -0400
From: "Merlin Moncure" <mmoncure@gmail.com>
To: dforums <dforums@vieonet.com>
Subject: Re: Plz Heeeelp! performance settings
Cc: "Richard Huxton" <dev@archonet.com>, pgsql-performance@postgresql.org
In-Reply-To: <489AF8E8.6070503@vieonet.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
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