Thread
-
Optimizations for busy DB??
Brian <signal@shreve.net> — 1999-05-13T13:24:33Z
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)
-
Re: [GENERAL] Optimizations for busy DB??
Brian <signal@shreve.net> — 1999-05-13T14:53:10Z
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)
-
Re: [GENERAL] Optimizations for busy DB??
Kevin Heflin <kheflin@shreve.net> — 1999-05-13T15:07:01Z
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 --------------------------------------------------------------------
-
Re: [GENERAL] Optimizations for busy DB??
Brian <signal@shreve.net> — 1999-05-13T15:10:28Z
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)
-
Re: [GENERAL] Optimizations for busy DB??
Brian <signal@shreve.net> — 1999-05-13T15:13:40Z
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)
-
Re: [GENERAL] Optimizations for busy DB??
Jeff MacDonald <jeff@hub.org> — 1999-05-13T15:26:41Z
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 > -------------------------------------------------------------------- > > >
-
Re: [GENERAL] Optimizations for busy DB??
Brian <signal@shreve.net> — 1999-05-13T15:56:12Z
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)
-
Re: [GENERAL] Optimizations for busy DB??
Bruce Momjian <maillist@candle.pha.pa.us> — 1999-05-13T16:17:29Z
> 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
-
Re: [GENERAL] Optimizations for busy DB??
Karl DeBisschop <kdebisschop@spaceheater.infoplease.com> — 1999-05-13T16:58:34Z
> 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 -
Re: [GENERAL] Optimizations for busy DB??
Brett W. McCoy <bmccoy@lan2wan.com> — 1999-05-13T17:40:40Z
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 -
Re: [GENERAL] Optimizations for busy DB??
Brett W. McCoy <bmccoy@lan2wan.com> — 1999-05-13T18:22:29Z
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) -
Re: [GENERAL] Optimizations for busy DB??
Brett W. McCoy <bmccoy@lan2wan.com> — 1999-05-13T18:40:37Z
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 -
Re: [GENERAL] Optimizations for busy DB??
Brett W. McCoy <bmccoy@lan2wan.com> — 1999-05-13T19:00:51Z
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 -
Re: [GENERAL] Optimizations for busy DB??
Dustin Sallings <dustin@spy.net> — 1999-05-13T19:43:26Z
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. ____________
-
Re: [GENERAL] Optimizations for busy DB??
Brian <signal@shreve.net> — 1999-05-13T19:47:29Z
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)
-
Re: [GENERAL] Optimizations for busy DB??
Dustin Sallings <dustin@spy.net> — 1999-05-13T19:51:26Z
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. ____________
-
Re: [GENERAL] Optimizations for busy DB??
Brett W. McCoy <bmccoy@lan2wan.com> — 1999-05-13T20:07:04Z
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 -
Re: [GENERAL] Optimizations for busy DB??
Brett W. McCoy <bmccoy@lan2wan.com> — 1999-05-13T21:28:17Z
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.