Thread: tweaks for write-intensive dbs ?

can anyone offer some suggestions on tweaking a system for write-
intensive operations?

my new box is running great for reads / selects , and with 60
connections on tests.

however i'm in the middle of a schema migration / audit, and its
taking ungodly slow.

selecting + analyzing 10,000 records with thousands of queries is no
issue -- its only taking ~ 3 seconds.

but the updating of the records as having been audited ( a bool
flag ) takes 5-10 minutes per set.

the read and write operations are pulling up the data via the
relevant indexes according to analyze.

i've tried turning fsync off, but no luck there.

if anyone could suggest some improvement tips, i'd be grateful.  the
system isn't huge (only ~5M records ) , but its taking a rather long
enough time that any extra speed i can come up with would be a huge
improvement..


// Jonathan Vanasco

| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - -
| 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
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - -

Jonathan Vanasco wrote:
> can anyone offer some suggestions on tweaking a system for
> write-intensive operations?

> but the updating of the records as having been audited ( a bool flag )
> takes 5-10 minutes per set.

What's the limiting factor? Disk? CPU?

Any chance of seeing the queries that are causing the problem?
How many rows do they affect?
Do you have lots of indexes or foreign-key constraints that might be
taking up time to update/check?
Do your logs show messages about checkpointing happening too often?

--
   Richard Huxton
   Archonet Ltd

Richard Huxton <dev@archonet.com> writes:
> Jonathan Vanasco wrote:
>> can anyone offer some suggestions on tweaking a system for
>> write-intensive operations?

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

If it's I/O bound, you probably need to increase checkpoint_segments.

            regards, tom lane

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
| - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - - - - - - - - - - -