Re: [PERFORM] Need to tune for Heavy Write

2011-08-17 Thread Jim Nasby
On Aug 4, 2011, at 10:07 AM, Scott Marlowe wrote: > On Thu, Aug 4, 2011 at 7:57 AM, Kevin Grittner > wrote: >>> RAM : 16 GB >> >>> effective_cache_size = 4096MB >> >> That should probably be more like 12GB to 15GB. It probably won't >> affect the load time here, but could affect other queries.

Re: [PERFORM] Need to tune for Heavy Write

2011-08-04 Thread Mark Kirkwood
On 05/08/11 05:40, Samuel Gendler wrote: On Wed, Aug 3, 2011 at 9:56 PM, Adarsh Sharma mailto:adarsh.sha...@orkash.com>> 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 f

Re: [PERFORM] Need to tune for Heavy Write

2011-08-04 Thread Samuel Gendler
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 10 rows , process > it & insert into postgres 2 tables by m

Re: [PERFORM] Need to tune for Heavy Write

2011-08-04 Thread Scott Marlowe
AM >> To: Willy-Bas Loos >> Cc: Adarsh Sharma; pgsql-performance@postgresql.org >> Subject: Re: [PERFORM] Need to tune for Heavy Write >> >> >> > Moving the pg_xlog to a different directory only helps when that >> > directory is on a different harddisk

Re: [PERFORM] Need to tune for Heavy Write

2011-08-04 Thread Scott Marlowe
On Thu, Aug 4, 2011 at 7:57 AM, Kevin Grittner wrote: >> RAM : 16 GB > >> effective_cache_size = 4096MB > > That should probably be more like 12GB to 15GB.  It probably won't > affect the load time here, but could affect other queries. Actually on a heavily written database a large effective cac

Re: [PERFORM] Need to tune for Heavy Write

2011-08-04 Thread Kevin Grittner
Adarsh Sharma wrote: > Postgres : 8.4.2 You should definitely update to a more recent bug patch level: http://www.postgresql.org/support/versioning > RAM : 16 GB > effective_cache_size = 4096MB That should probably be more like 12GB to 15GB. It probably won't affect the load time here

Re: [PERFORM] Need to tune for Heavy Write

2011-08-04 Thread Craig Ringer
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 10 rows , process it & insert into postgres 2 tables by making about 45 con

Re: [PERFORM] Need to tune for Heavy Write

2011-08-04 Thread Nicholson, Brad (Toronto, ON, CA)
Subject: Re: [PERFORM] Need to tune for Heavy Write > > > > 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

Re: [PERFORM] Need to tune for Heavy Write

2011-08-04 Thread Dusan Misic
Scott is right. His answer solves the problem in the long run. Even if your write load increases, it will perform fast enough. For now try increasing checkpoint_segments size, restart Postgres for new settings to take effect and try again with your write load. If you are not satisfied with write

Re: [PERFORM] Need to tune for Heavy Write

2011-08-04 Thread Scott Marlowe
On Thu, Aug 4, 2011 at 2:34 AM, Willy-Bas Loos wrote: > 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 configurat

Re: [PERFORM] Need to tune for Heavy Write

2011-08-04 Thread Dusan Misic
To put it simple, you need to set checkpoint_segments way higher than your current value! Link: wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server On Aug 4, 2011 6:57 AM, "Adarsh Sharma" wrote:

Re: [PERFORM] Need to tune for Heavy Write

2011-08-04 Thread Willy-Bas Loos
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. Moving the pg_xlog to a different directory only helps whe

Re: [PERFORM] Need to tune for Heavy Write

2011-08-04 Thread Dusan Misic
Hi Adarsh, Have you set checkpoint_segments and checkpoint_completion_target the right way? Tuning these parameters are a MUST if you want good write performance. See this link for more information: http://www.postgresql.org/docs/current/static/runtime-config-wal.html