Re: Performance tuning in Pgsql

On Fri, Dec 10, 2010 at 12:53 AM, Adarsh Sharma
<adarsh(dot)sharma(at)orkash(dot)com> wrote:
> Dear all,
>
> I am researched a lot about Performance tuning in Pgsql.
>
> I found that we have to change shared_buffer parameter and
> effective_cache_size parameter.
> I changed shared_buffer to 2 GB but I can't able to locate
> effective_cache_size parameter in postgresql.conf file.

Odd, it's there in mine.

So, what OS are you using, what pg version, etc.

First place to look for performance tuning is the pg wiki entry on just that:

http://wiki.postgresql.org/wiki/Performance_Optimization

> Also i want to change my WAL directory to seperate directory. Same I
> couldn,t locate pg_xlog or how to change it.

OK, so the way I do this, is to locate my data directory. On a stock
Ubuntu machine that would be /var/lib/postgresql/8.4/main . In that
dir is a directory called pg_xlog, what we're looking for. So, as
root, I'd do:

cd /var/lib/postgresql/8.4/main
/etc/init.d/postgresql-8.4 stop
mkdir /myothervolume/pg_xlog
chown postgres.postgres /myothervolume/pg_xlog
chmod 700 /myothervolume/pg_xlog
cp -rf pg_xlog/* /myothervolume/pg_xlog
mv pg_xlog pg_xlog_old
ln -s /myothervolume/pg_xlog pg_xlog
/etc/init.d/postgresql-8.4 start