Optimizations for busy DB??

On Thu, 13 May 1999, Brett W. McCoy wrote:

On Thu, 13 May 1999, Brian wrote:

We are running a Database that is having between 100-500 simultaneous
accesses at any given time. Are their any flags, switches or
optimizations on the postgres level that can be done? such as options
passed to postgres etc? We run 6.3.2, and start it like:

su postgres -c '/usr/bin/postmaster -i -S -D/var/lib/pgsql

I usually pass back -F -B 256 to the backend (using -o with postmaster).
-F turns off the fsync, and speeds up writes significantly (especially
useful for bulk copies and updates). However, if your system crashes in
the middle of a transaction, you can lose data. The -B sets the number
of 8k buffers. It defaults to 64, but if you have more memory, crank
that number up. For sorts, -S can be used to specify how much memory to
use (in 1k chunks) before disk files are used. The default is 512, but
again, if you have the memory to spare, jack that guy up and see how well
it works. Make sure you preface these backend options with -o, since the
postmaster has its own options distinct from the backend. Your complete
invocation might look like

postmaster -i -S -D/var/lib/pgsql -o -F -B 256 -S 1024

ok, so specifying -B etc to the postmaster itself has no advantages? I
mean I guess you wouldn't want/need to specify those optimizations to both
postgres backend AND the postmaster?

I will give it all a shot, appreciate the help.

Experiment and see what works.

Oh, yeah, you should upgrade to 6.4.2!

I am assuming I will have to export all my data and reimport into 6.4.2,
which is a task for sure............I will make this jump soon when I have
some downtime.

Brett W. McCoy
http://www.lan2wan.com/~bmccoy
-----------------------------------------------------------------------
The only way to get rid of a temptation is to yield to it.
-- Oscar Wilde

-----------------------------------------------------
Brian Feeny (BF304) signal@shreve.net
318-222-2638 x 109 http://www.shreve.net/~signal
Network Administrator ShreveNet Inc. (ASN 11881)

On Thu, 13 May 1999, Brian wrote:

Oh, yeah, you should upgrade to 6.4.2!

I am assuming I will have to export all my data and reimport into 6.4.2,
which is a task for sure............I will make this jump soon when I have
some downtime.

Are there any estimates on a final release of postgresql 6.5 ?

Kevin

--------------------------------------------------------------------
Kevin Heflin | ShreveNet, Inc. | Ph:318.222.2638 x103
VP/Mac Tech | 333 Texas St #175 | FAX:318.221.6612
kheflin@shreve.net | Shreveport, LA 71101 | http://www.shreve.net
--------------------------------------------------------------------

On Thu, 13 May 1999, Brett W. McCoy wrote:

On Thu, 13 May 1999, Brian wrote:

ok, so specifying -B etc to the postmaster itself has no advantages? I
mean I guess you wouldn't want/need to specify those optimizations to both
postgres backend AND the postmaster?

Actually, you're right -- you only want to pass -B to the backend if you
are running it standalone, otherwise use the postmaster option. Note,

standalone as opposed to what? I run it with -i and have clients all over
connecting to it?

though that passing -S to the postmaster is a different option than
passing -S to the backend (postmaster recognizes -S as 'silent mode'
whereas the backend recognizes -S as the sort allocation). Check the man
pages for the specifics. Just remember that the postmaster has its own
options while postgres (the actual backend) has its own options distinct
from the postmaster.

I am assuming I will have to export all my data and reimport into 6.4.2,
which is a task for sure............I will make this jump soon when I have
some downtime.

Good project for a long weekend!

Brett W. McCoy
http://www.lan2wan.com/~bmccoy
-----------------------------------------------------------------------
"If the King's English was good enough for Jesus, it's good enough for
me!"
-- "Ma" Ferguson, Governor of Texas (circa 1920)

-----------------------------------------------------
Brian Feeny (BF304) signal@shreve.net
318-222-2638 x 109 http://www.shreve.net/~signal
Network Administrator ShreveNet Inc. (ASN 11881)

On Thu, 13 May 1999, Brett W. McCoy wrote:

On Thu, 13 May 1999, Brian wrote:

ok, so specifying -B etc to the postmaster itself has no advantages? I
mean I guess you wouldn't want/need to specify those optimizations to both
postgres backend AND the postmaster?

Actually, you're right -- you only want to pass -B to the backend if you
are running it standalone, otherwise use the postmaster option. Note,
though that passing -S to the postmaster is a different option than
passing -S to the backend (postmaster recognizes -S as 'silent mode'
whereas the backend recognizes -S as the sort allocation). Check the man
pages for the specifics. Just remember that the postmaster has its own
options while postgres (the actual backend) has its own options distinct
from the postmaster.

Would something like this be appropriate?

/usr/bin/postmaster -B 256 -i -S -D/var/lib/pgsql -o -F -B 256 -S 1024

or should -B just be in their once? in the postmaster setting?

I am assuming I will have to export all my data and reimport into 6.4.2,
which is a task for sure............I will make this jump soon when I have
some downtime.

Good project for a long weekend!

Brett W. McCoy
http://www.lan2wan.com/~bmccoy
-----------------------------------------------------------------------
"If the King's English was good enough for Jesus, it's good enough for
me!"
-- "Ma" Ferguson, Governor of Texas (circa 1920)

-----------------------------------------------------
Brian Feeny (BF304) signal@shreve.net
318-222-2638 x 109 http://www.shreve.net/~signal
Network Administrator ShreveNet Inc. (ASN 11881)

June 1st

On Thu, 13 May 1999, Kevin Heflin wrote:

On Thu, 13 May 1999, Brett W. McCoy wrote:

On Thu, 13 May 1999, Brian wrote:

Would something like this be appropriate?

/usr/bin/postmaster -B 256 -i -S -D/var/lib/pgsql -o -F -B 256 -S 1024

or should -B just be in their once? in the postmaster setting?

Just once. If you pass it back to a backend from the postmaster, the
postmaster handles the allocation as shared memory buffers. Here's what
the man page for postgres says:

-B n_buffers
If the backend is running under the postmaster,
n_buffers is the number of shared-memory buffers
that the postmaster has allocated for the backend
server processes that it starts. If the backend is
running standalone, this specifies the number of
buffers to allocate. This value defaults to 64,
and each buffer is 8k bytes.

I am assuming here, of course, that this didn't change betwen 6.3 and 6.4
(which is what I am using).

ok, so then I am assuming:

/usr/bin/postmaster -B 256 -i -S -D/var/lib/pgsql -o -F -S 1024

is what I would want...............

Brett W. McCoy
http://www.lan2wan.com/~bmccoy
-----------------------------------------------------------------------
"A raccoon tangled with a 23,000 volt line today. The results blacked
out 1400 homes and, of course, one raccoon."
-- Steel City News

-----------------------------------------------------
Brian Feeny (BF304) signal@shreve.net
318-222-2638 x 109 http://www.shreve.net/~signal
Network Administrator ShreveNet Inc. (ASN 11881)

On Thu, 13 May 1999, Brian wrote:

Would something like this be appropriate?

/usr/bin/postmaster -B 256 -i -S -D/var/lib/pgsql -o -F -B 256 -S 1024

or should -B just be in their once? in the postmaster setting?

Just once. If you pass it back to a backend from the postmaster, the
postmaster handles the allocation as shared memory buffers. Here's what
the man page for postgres says:

-B n_buffers
If the backend is running under the postmaster,
n_buffers is the number of shared-memory buffers
that the postmaster has allocated for the backend
server processes that it starts. If the backend is
running standalone, this specifies the number of
buffers to allocate. This value defaults to 64,
and each buffer is 8k bytes.

Because the buffers are shared by all postmaster backends, it is a
postmaster option. If you are running standalone, you will have the
postgres backend allocate its own buffers.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

it works. Make sure you preface these backend options with -o, since the
postmaster has its own options distinct from the backend. Your complete
invocation might look like

postmaster -i -S -D/var/lib/pgsql -o -F -B 256 -S 1024

From the man page for postmaster:

-o backend_options
The postgres(1) options specified in backend_options
are passed to all backend server processes started by
this postmaster. If the option string contains any
spaces, the entire string must be quoted.

So therefore:

postmaster -i -S -D/var/lib/pgsql -o '-F -S 1024'

--
Karl DeBisschop <kdebisschop@spaceheater.infoplease.com>
617.832.0332 (Fax: 617.956.2696)

Information Please - your source for FREE online reference
http://www.infoplease.com - Your Ultimate Fact Finder
http://kids.infoplease.com - The Great Homework Helper

On Thu, 13 May 1999, Brian wrote:

We are running a Database that is having between 100-500 simultaneous
accesses at any given time. Are their any flags, switches or
optimizations on the postgres level that can be done? such as options
passed to postgres etc? We run 6.3.2, and start it like:

su postgres -c '/usr/bin/postmaster -i -S -D/var/lib/pgsql

I usually pass back -F -B 256 to the backend (using -o with postmaster).
-F turns off the fsync, and speeds up writes significantly (especially
useful for bulk copies and updates). However, if your system crashes in
the middle of a transaction, you can lose data. The -B sets the number
of 8k buffers. It defaults to 64, but if you have more memory, crank
that number up. For sorts, -S can be used to specify how much memory to
use (in 1k chunks) before disk files are used. The default is 512, but
again, if you have the memory to spare, jack that guy up and see how well
it works. Make sure you preface these backend options with -o, since the
postmaster has its own options distinct from the backend. Your complete
invocation might look like

postmaster -i -S -D/var/lib/pgsql -o -F -B 256 -S 1024

Experiment and see what works.

Oh, yeah, you should upgrade to 6.4.2!

Brett W. McCoy
http://www.lan2wan.com/~bmccoy
-----------------------------------------------------------------------
The only way to get rid of a temptation is to yield to it.
-- Oscar Wilde

On Thu, 13 May 1999, Brian wrote:

ok, so specifying -B etc to the postmaster itself has no advantages? I
mean I guess you wouldn't want/need to specify those optimizations to both
postgres backend AND the postmaster?

Actually, you're right -- you only want to pass -B to the backend if you
are running it standalone, otherwise use the postmaster option. Note,
though that passing -S to the postmaster is a different option than
passing -S to the backend (postmaster recognizes -S as 'silent mode'
whereas the backend recognizes -S as the sort allocation). Check the man
pages for the specifics. Just remember that the postmaster has its own
options while postgres (the actual backend) has its own options distinct
from the postmaster.

I am assuming I will have to export all my data and reimport into 6.4.2,
which is a task for sure............I will make this jump soon when I have
some downtime.

Good project for a long weekend!

Brett W. McCoy
http://www.lan2wan.com/~bmccoy
-----------------------------------------------------------------------
"If the King's English was good enough for Jesus, it's good enough for
me!"
-- "Ma" Ferguson, Governor of Texas (circa 1920)

On Thu, 13 May 1999, Brian wrote:

Actually, you're right -- you only want to pass -B to the backend if you
are running it standalone, otherwise use the postmaster option. Note,

standalone as opposed to what? I run it with -i and have clients all over
connecting to it?

You can run postgres directly without the postmaster, for debugging
purposes. This isn't recommended for regular usage, of course.

Brett W. McCoy
http://www.lan2wan.com/~bmccoy
-----------------------------------------------------------------------
Brook's Law:
Adding manpower to a late software project makes it later

On Thu, 13 May 1999, Brian wrote:

Would something like this be appropriate?

/usr/bin/postmaster -B 256 -i -S -D/var/lib/pgsql -o -F -B 256 -S 1024

or should -B just be in their once? in the postmaster setting?

Just once. If you pass it back to a backend from the postmaster, the
postmaster handles the allocation as shared memory buffers. Here's what
the man page for postgres says:

-B n_buffers
If the backend is running under the postmaster,
n_buffers is the number of shared-memory buffers
that the postmaster has allocated for the backend
server processes that it starts. If the backend is
running standalone, this specifies the number of
buffers to allocate. This value defaults to 64,
and each buffer is 8k bytes.

I am assuming here, of course, that this didn't change betwen 6.3 and 6.4
(which is what I am using).

Brett W. McCoy
http://www.lan2wan.com/~bmccoy
-----------------------------------------------------------------------
"A raccoon tangled with a 23,000 volt line today. The results blacked
out 1400 homes and, of course, one raccoon."
-- Steel City News

On Thu, 13 May 1999, Brian wrote:

One obvious thing might be to try to avoid having so many things
hitting the database. I've found that often when people have that many
connections in use, there's a better way to do it. This may not be the
case for you since I don't know your application, but the best database
optimizations I've put into place were in the application, having it avoid
hitting the database.

#
# We are running a Database that is having between 100-500 simultaneous
# accesses at any given time. Are their any flags, switches or
# optimizations on the postgres level that can be done? such as options
# passed to postgres etc? We run 6.3.2, and start it like:
#
# su postgres -c '/usr/bin/postmaster -i -S -D/var/lib/pgsql
#
# Thanks for any help.
#
# Brian
#
#
# -----------------------------------------------------
# Brian Feeny (BF304) signal@shreve.net
# 318-222-2638 x 109 http://www.shreve.net/~signal
# Network Administrator ShreveNet Inc. (ASN 11881)
#
#
#

--
SA, beyond.com My girlfriend asked me which one I like better.
pub 1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin@spy.net>
| Key fingerprint = 87 02 57 08 02 D0 DA D6 C8 0F 3E 65 51 98 D8 BE
L_______________________ I hope the answer won't upset her. ____________

On Thu, 13 May 1999, Dustin Sallings wrote:

On Thu, 13 May 1999, Brian wrote:

One obvious thing might be to try to avoid having so many things
hitting the database. I've found that often when people have that many
connections in use, there's a better way to do it. This may not be the
case for you since I don't know your application, but the best database
optimizations I've put into place were in the application, having it avoid
hitting the database.

its a really busy website retreviing data from the database.

#
# We are running a Database that is having between 100-500 simultaneous
# accesses at any given time. Are their any flags, switches or
# optimizations on the postgres level that can be done? such as options
# passed to postgres etc? We run 6.3.2, and start it like:
#
# su postgres -c '/usr/bin/postmaster -i -S -D/var/lib/pgsql
#
# Thanks for any help.
#
# Brian
#
#
# -----------------------------------------------------
# Brian Feeny (BF304) signal@shreve.net
# 318-222-2638 x 109 http://www.shreve.net/~signal
# Network Administrator ShreveNet Inc. (ASN 11881)
#
#
#

--
SA, beyond.com My girlfriend asked me which one I like better.
pub 1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin@spy.net>
| Key fingerprint = 87 02 57 08 02 D0 DA D6 C8 0F 3E 65 51 98 D8 BE
L_______________________ I hope the answer won't upset her. ____________

-----------------------------------------------------
Brian Feeny (BF304) signal@shreve.net
318-222-2638 x 109 http://www.shreve.net/~signal
Network Administrator ShreveNet Inc. (ASN 11881)

On Thu, 13 May 1999, Brian wrote:

# its a really busy website retreviing data from the database.

Right. I run some pretty busy web sites that get data from the
database as well. Most of the data doesn't need to be retreived from the
database for every single request. I've got some pretty good caching
stuff I use that speeds it up *significantly*, and avoids hitting the
database for the same information over and over (and over).

--
SA, beyond.com My girlfriend asked me which one I like better.
pub 1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin@spy.net>
| Key fingerprint = 87 02 57 08 02 D0 DA D6 C8 0F 3E 65 51 98 D8 BE
L_______________________ I hope the answer won't upset her. ____________

On Thu, 13 May 1999, Brian wrote:

I am assuming here, of course, that this didn't change betwen 6.3 and 6.4
(which is what I am using).

ok, so then I am assuming:

/usr/bin/postmaster -B 256 -i -S -D/var/lib/pgsql -o -F -S 1024

is what I would want...............

Yep. You can also put the -B as a backend option, but under postmaster,
it still does the shared buffer allocation.

Brett W. McCoy
http://www.lan2wan.com/~bmccoy
-----------------------------------------------------------------------
"When are you BUTTHEADS gonna learn that you can't oppose Gestapo
tactics *with* Gestapo tactics?"
-- Reuben Flagg

On Thu, 13 May 1999, Karl DeBisschop wrote:

-o backend_options
The postgres(1) options specified in backend_options
are passed to all backend server processes started by
this postmaster. If the option string contains any
spaces, the entire string must be quoted.

So therefore:

postmaster -i -S -D/var/lib/pgsql -o '-F -S 1024'

Yes, quite so. Thanks for pointing that out.

Brett W. McCoy
http://www.lan2wan.com/~bmccoy
-----------------------------------------------------------------------
Law of the Perversity of Nature:
You cannot successfully determine beforehand which side of the
bread to butter.