Thread: Slow Performance
I have a monster db server, with 2 gigs of memory all for PostgresQL 7.0.2. The machine is a PIII - 800 Zeon with 10,000 RPM SCSI drives. I have a database with about 8 tables with over 1.5 Million rows. It's running slower than I like when running simple sql queries on one and two tables. When I look at postgres I notice it's only using 80 megs. 1) Would increasing the memory postgresql is using increase performance? Here's my current command line: /usr/local/pgsql/bin/postmaster -S -D /usr/local/pgsql/data -i -o '-F' -B 2048 -N 1024 & 2) Are there any other suggestions to how I run Postgresql to increase my performance issues? TIA, Gavin
* Gavin M. Roy <gmr@readysetnet.com> [001024 11:09] wrote: > I have a monster db server, with 2 gigs of memory all for PostgresQL > 7.0.2. The machine is a PIII - 800 Zeon with 10,000 RPM SCSI drives. I > have a database with about 8 tables with over 1.5 Million rows. It's > running slower than I like when running simple sql queries on one and > two tables. When I look at postgres I notice it's only using 80 megs. > > 1) Would increasing the memory postgresql is using increase performance? > > Here's my current command line: > /usr/local/pgsql/bin/postmaster -S -D /usr/local/pgsql/data -i -o '-F' > -B 2048 -N 1024 & Yes, try running a shared memorry segment of at least 256 megs. > 2) Are there any other suggestions to how I run Postgresql to increase > my performance issues? Make sure you periodically do a 'vacuum analyze' on the tables and make sure you have proper indexing. -- -Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org] "I have the heart of a child; I keep it in a jar on my desk."
Am looking here: http://www.postgresql.org/devel-corner/docs/admin/runtime-config.htm but not seeing anything on shared memory. Do you know the configuration option for this? Gavin Alfred Perlstein wrote: > * Gavin M. Roy <gmr@readysetnet.com> [001024 11:09] wrote: > > I have a monster db server, with 2 gigs of memory all for PostgresQL > > 7.0.2. The machine is a PIII - 800 Zeon with 10,000 RPM SCSI drives. I > > have a database with about 8 tables with over 1.5 Million rows. It's > > running slower than I like when running simple sql queries on one and > > two tables. When I look at postgres I notice it's only using 80 megs. > > > > 1) Would increasing the memory postgresql is using increase performance? > > > > Here's my current command line: > > /usr/local/pgsql/bin/postmaster -S -D /usr/local/pgsql/data -i -o '-F' > > -B 2048 -N 1024 & > > Yes, try running a shared memorry segment of at least 256 megs. > > > 2) Are there any other suggestions to how I run Postgresql to increase > > my performance issues? > > Make sure you periodically do a 'vacuum analyze' on the tables and > make sure you have proper indexing. > > -- > -Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org] > "I have the heart of a child; I keep it in a jar on my desk."
* Gavin M. Roy <gmr@readysetnet.com> [001024 13:12] wrote: > Am looking here: > http://www.postgresql.org/devel-corner/docs/admin/runtime-config.htm > > but not seeing anything on shared memory. Do you know the configuration > option for this? -B <some number of pages> > > Gavin > > Alfred Perlstein wrote: > > > * Gavin M. Roy <gmr@readysetnet.com> [001024 11:09] wrote: > > > I have a monster db server, with 2 gigs of memory all for PostgresQL > > > 7.0.2. The machine is a PIII - 800 Zeon with 10,000 RPM SCSI drives. I > > > have a database with about 8 tables with over 1.5 Million rows. It's > > > running slower than I like when running simple sql queries on one and > > > two tables. When I look at postgres I notice it's only using 80 megs. > > > > > > 1) Would increasing the memory postgresql is using increase performance? > > > > > > Here's my current command line: > > > /usr/local/pgsql/bin/postmaster -S -D /usr/local/pgsql/data -i -o '-F' > > > -B 2048 -N 1024 & > > > > Yes, try running a shared memorry segment of at least 256 megs. > > > > > 2) Are there any other suggestions to how I run Postgresql to increase > > > my performance issues? > > > > Make sure you periodically do a 'vacuum analyze' on the tables and > > make sure you have proper indexing. > > > > -- > > -Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org] > > "I have the heart of a child; I keep it in a jar on my desk." -- -Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org] "I have the heart of a child; I keep it in a jar on my desk."
Thanks, I am now running -B 32768 and Postgres is nicely occupying 800+ megs of memory. We're doing testing now to see how it effects performance. Alfred Perlstein wrote: > * Gavin M. Roy <gmr@readysetnet.com> [001024 13:12] wrote: > > Am looking here: > > http://www.postgresql.org/devel-corner/docs/admin/runtime-config.htm > > > > but not seeing anything on shared memory. Do you know the configuration > > option for this? > > -B <some number of pages> > > > > > Gavin > > > > Alfred Perlstein wrote: > > > > > * Gavin M. Roy <gmr@readysetnet.com> [001024 11:09] wrote: > > > > I have a monster db server, with 2 gigs of memory all for PostgresQL > > > > 7.0.2. The machine is a PIII - 800 Zeon with 10,000 RPM SCSI drives. I > > > > have a database with about 8 tables with over 1.5 Million rows. It's > > > > running slower than I like when running simple sql queries on one and > > > > two tables. When I look at postgres I notice it's only using 80 megs. > > > > > > > > 1) Would increasing the memory postgresql is using increase performance? > > > > > > > > Here's my current command line: > > > > /usr/local/pgsql/bin/postmaster -S -D /usr/local/pgsql/data -i -o '-F' > > > > -B 2048 -N 1024 & > > > > > > Yes, try running a shared memorry segment of at least 256 megs. > > > > > > > 2) Are there any other suggestions to how I run Postgresql to increase > > > > my performance issues? > > > > > > Make sure you periodically do a 'vacuum analyze' on the tables and > > > make sure you have proper indexing. > > > > > > -- > > > -Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org] > > > "I have the heart of a child; I keep it in a jar on my desk." > > -- > -Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org] > "I have the heart of a child; I keep it in a jar on my desk."