Skip site navigation (1) Skip section navigation (2)

Re: tweaks for write-intensive dbs ?

From: Jonathan Vanasco <postgres(at)2xlp(dot)com>
To: pgsql general <pgsql-general(at)postgresql(dot)org>
Subject: Re: tweaks for write-intensive dbs ?
Date: 2007-03-28 18:09:16
Message-ID: 87032E53-5E42-4138-8547-8FC2838E90C4@2xlp.com (view raw or flat)
Thread:
Lists: pgsql-general
The audit finished up overnight, but i'll try running a test tonight  
so i can get a better idea of what is going on.

On Mar 28, 2007, at 3:44 AM, Richard Huxton wrote:

> What's the limiting factor? Disk? CPU?

i'm imagining disk io.    its a simple query

> Any chance of seeing the queries that are causing the problem?
essentially:
	update table_a set is_audited = true where id = :serial_id
	update table_a set is_audited = true where id in :serial_ids

yes, just toggling a single flag

> How many rows do they affect?
1 per query.  i tried doing batch queries from 10-10k -- no difference.

> Do you have lots of indexes or foreign-key constraints that might  
> be taking up time to update/check?
nothing fkeys on the column that is updated.
1 index must be updated by the operation.

> Do your logs show messages about checkpointing happening too often?
new server - i forgot to enable logging.  fixed, and I'll check tonight.
i have a feeling that might be the reason though - my wal archive was  
~ 40gb


On Mar 28, 2007, at 10:18 AM, Tom Lane wrote:
> If it's I/O bound, you probably need to increase checkpoint_segments.

i tried playing with # of segments , didn't affect anything.  maybe  
segment size ?




// Jonathan Vanasco

| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -  
- - - - - - - - - - - - - - - - - - -
| SyndiClick.com
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -  
- - - - - - - - - - - - - - - - - - -
|      FindMeOn.com - The cure for Multiple Web Personality Disorder
|      Web Identity Management and 3D Social Networking
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -  
- - - - - - - - - - - - - - - - - - -
|      RoadSound.com - Tools For Bands, Stuff For Fans
|      Collaborative Online Management And Syndication Tools
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -  
- - - - - - - - - - - - - - - - - - -



In response to

pgsql-general by date

Next:From: Joseph SDate: 2007-03-28 18:16:55
Subject: Re: redhat debug info
Previous:From: A. KretschmerDate: 2007-03-28 17:15:31
Subject: Re: how to know a table size ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group