Re: Performance tuning in Pgsql - Mailing list pgsql-general

Please keep the list cc'd as there are others who might be able to
help or could use this thread for help.

On Fri, Dec 10, 2010 at 2:53 AM, Adarsh Sharma <adarsh.sharma@orkash.com> wrote:
> Scott Marlowe wrote:
>>
>> On Fri, Dec 10, 2010 at 12:53 AM, Adarsh Sharma
>> <adarsh.sharma@orkash.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
>>
>
> Thanks Scott , Very Nicely Explained.
>
> I followed your commands. I copied pg_xlog directory into /hdd-1/pg_xlog and
> chmod 700 to it. Also i make a link into /root/ directory.
>
> But when I start the server , I got the exception in startup.log which is
> FATAL:  syntax error in file "/hrd2-p/postgres_data/postgresql.conf" line
> 245, near token "/"
> FATAL:  syntax error in file "/hrd2-p/postgres_data/postgresql.conf" line
> 245, near token "/"
> FATAL:  syntax error in file "/hrd2-p/postgres_data/postgresql.conf" line
> 247, near token "/"
> FATAL:  syntax error in file "/hrd2-p/postgres_data/postgresql.conf" line
> 247, near token "/"
> FATAL:  syntax error in file "/hrd2-p/postgres_data/postgresql.conf" line
> 247, near token "/"
>
> My postgresql.conf Line 247 is :
>
> #log_directory =/hrd2-1/pg_xlog         # directory where log files are
> written,
>                                       # can be absolute or relative to
> PGDATA
>
> I check it with # and without # but it doesn't work.
>
>
> But when I renamed pg_xlog_old to pg_xlog , Server starts.

That doesn't make a lot of sense.  The way I move pg_xlog doesn't
involve that line really but kind of bypasses it.  Got a complete
example of all the steps you took?

> Does i need to change something in Postgresql.conf file?


Possibly.  It's one of the two ways of moving pg_xlog.  More complete
step by step example of what you tried will help.