Need to tune for Heavy Write

On Thu, Aug 4, 2011 at 6:56 AM, Adarsh Sharma wrote:

Dear all,

From the last few days, I researched a lot on Postgresql Performance Tuning
due to slow speed of my server.
My application selects data from mysql database about 100000 rows , process
it & insert into postgres 2 tables by making about 45 connections.

I set my postgresql parameters in postgresql.conf as below: ( OS : Ubuntu,
RAM : 16 GB, Postgres : 8.4.2 )

max_connections = 80
shared_buffers = 2048MB
work_mem = 32MB
maintenance_work_mem = 512MB
fsync=off full_page_writes=off synchronous_commit=off checkpoint_segments =
32
checkpoint_completion_target = 0.7 effective_cache_size = 4096MB


After this I change my pg_xlog directory to a separate directory other than
data directory by symlinking.


By Application issue insert statements through postgresql connections only.

Please let me know if I missing any other important configuration.



Thanks



--
Sent via pgsql-performance mailing list ([email protected].**
org <[email protected]>)
To make changes to your subscription:
http://www.postgresql.org/**mailpref/pgsql-performance<http://www.postgresql.org/mailpref/pgsql-performance>

On Thu, Aug 4, 2011 at 6:56 AM, Adarsh Sharma wrote:
After this I change my pg_xlog directory to a separate directory other than
data directory by symlinking.
(...)
Please let me know if I missing any other important configuration.

On Thu, Aug 4, 2011 at 2:34 AM, Willy-Bas Loos wrote:
Moving the pg_xlog to a different directory only helps when that
directory is on a different harddisk (or whatever I/O device).

Not entirely true. By simply being on a different mounted file
system this moves the fsync calls on the pg_xlog directories off of
the same file system as the main data store. Previous testing has
shown improvements in performance from just using a different file
system.

That said, the only real solution to a heavy write load is a heavy
duty IO subsystem, with lots of drives and battery backed cache.

--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

-----Original Message-----
From: p[email protected] [mailto:pgsql-performance-
[email protected]] On Behalf Of Scott Marlowe
Sent: Thursday, August 04, 2011 4:46 AM
To: Willy-Bas Loos
Cc: Adarsh Sharma; [email protected]
Subject: Re: [PERFORM] Need to tune for Heavy Write

Not entirely true. By simply being on a different mounted file
system this moves the fsync calls on the pg_xlog directories off of
the same file system as the main data store. Previous testing has
shown improvements in performance from just using a different file
system.

Is this still the case for xfs or ext4 where fsync is properly flushing only the correct blocks to disk, or was this referring to the good old ext3 flush everything on fysnc issue?

On 4/08/2011 12:56 PM, Adarsh Sharma wrote:
Dear all,

From the last few days, I researched a lot on Postgresql Performance
Tuning due to slow speed of my server.
My application selects data from mysql database about 100000 rows ,
process it & insert into postgres 2 tables by making about 45 connections.

Adarsh Sharma wrote:

Postgres : 8.4.2

That should probably be more like 12GB to 15GB.  It probably won't
affect the load time here, but could affect other queries.

On Aug 4, 2011, at 10:07 AM, Scott Marlowe wrote:
On Thu, Aug 4, 2011 at 7:57 AM, Kevin Grittner
wrote:
Actually on a heavily written database a large effective cache size
makes things slower.

On Wed, Aug 3, 2011 at 9:56 PM, Adarsh Sharma wrote:

Dear all,

From the last few days, I researched a lot on Postgresql Performance Tuning
due to slow speed of my server.
My application selects data from mysql database about 100000 rows , process
it & insert into postgres 2 tables by making about 45 connections.

On 05/08/11 05:40, Samuel Gendler wrote:

On Wed, Aug 3, 2011 at 9:56 PM, Adarsh Sharma
wrote:

Dear all,

From the last few days, I researched a lot on Postgresql
Performance Tuning due to slow speed of my server.
My application selects data from mysql database about 100000 rows
, process it & insert into postgres 2 tables by making about 45
connections.


It's already been mentioned, but is worth reinforcing, that if you are
inserting 100,000 rows in 100,000 transactions, you'll see a huge
performance improvement by doing many more inserts per transaction.
Try doing at least 500 inserts in each transaction (though you can
possibly go quite a bit higher than that without any issues, depending
upon what other traffic the database is handling in parallel). You
almost certainly don't need 45 connections in order to insert only
100,000 rows. I've got a crappy VM with 2GB of RAM in which inserting
100,000 relatively narrow rows requires less than 10 seconds if I do
it in a single transaction on a single connection. Probably much less
than 10 seconds, but the code I just tested with does other work while
doing the inserts, so I don't have a pure test at hand.