Performance tuning in Pgsql

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

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.

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.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.

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

On Sun, Dec 12, 2010 at 9:57 PM, Adarsh Sharma <adarsh.sharma@orkash.com> wrote:

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 )

Without that log file it's pretty much impossible to guess what went
wrong. Does your system have SELinux installed? Can it be disabled
for testing? Are the files in /hrd2-p/postgres_data owned by the
postgres user or someone else?

On Fri, Dec 10, 2010 at 01:55:14AM -0700, Scott Marlowe wrote:

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

Is there any particular reason that you're suggesting linking rather
than simply mounting the partition at
/var/lib/postgresql/8.4/main/pg_xlog (after copying the data across,
naturally)?

--
Rodger Donaldson rodgerd@diaspora.gen.nz