Thread: Optimizations for busy DB??
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)
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, 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:
> 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: > > 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)
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
June 1st On Thu, 13 May 1999, Kevin Heflin wrote: > 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, 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, 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
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
> 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, 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.
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. ____________