Thread: Performance tuning in Pgsql
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. Also i want to change my WAL directory to seperate directory. Same I couldn,t locate pg_xlog or how to change it. I am very new to postgresql. Please help and guide me if any other thing needed for Make our Postgresql Server Faster and give better performance. Thanks & Regards Adarsh Sharma
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