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 speed, then it is time to upgrade your storage system / aka to increase I/O performance.
On Aug 4, 2011 10:46 AM, "Scott Marlowe" <scott.marlowe@xxxxxxxxx> wrote:
> On Thu, Aug 4, 2011 at 2:34 AM, Willy-Bas Loos <willybas@xxxxxxxxx> wrote:
>> On Thu, Aug 4, 2011 at 6:56 AM, Adarsh Sharma <adarsh.sharma@xxxxxxxxxx> 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 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 (pgsql-performance@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
> On Thu, Aug 4, 2011 at 2:34 AM, Willy-Bas Loos <willybas@xxxxxxxxx> wrote:
>> On Thu, Aug 4, 2011 at 6:56 AM, Adarsh Sharma <adarsh.sharma@xxxxxxxxxx> 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 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 (pgsql-performance@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
