Re: Performance tuning in Pgsql

Scott Marlowe wrote:
> 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(dot)sharma(at)orkash(dot)com> wrote:
>
>> Scott Marlowe wrote:
>>
>>> 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
>>>
>>>
>> 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.
>
Sorry Sir, but I simply followed your steps. I think those are
sufficient. But my server didn't start after these changes. Here are my
steps :

cd /hrd2-p/postgres_data
/etc/init.d/postgresql-8.4 stop
mkdir -p /opt/pg_xlog
chown -R postgres.postgres /opt/pg_xlog
chmod 700 /opt/pg_xlog
cp -rf pg_xlog/* /opt/pg_xlog
mv pg_xlog pg_xlog_old
ln -s /opt/pg_xlog pg_xlog
/etc/init.d/postgresql-8.4 start

Starting PostgreSQL 8.4:
waiting for server to
start...............................................................could
not start server
PostgreSQL 8.4 did not start in a timely fashion, please see
/hrd2-p/postgres_data/pg_log/startup.log for details

uima-server:/hrd2-p/postgres_data # vim
/hrd2-p/postgres_data/pg_log/startup.log

At this time this log is empty. Also I didn/t make any changes in
postgresql.conf

I'm using Linux ( Linux uima-server 2.6.16.46-0.12-smp #1 SMP Thu May 17
14:00:09 UTC 2007 x86_64 x86_64 x86_64 GNU/Linux )
and postgres ( Postgres 8.4 )

Thanks & Regards

Adarsh Sharma