pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedgo for a script! / ex: PostgreSQL vs. MySQL
42+ messages / 17 participants
[nested] [flat]
* go for a script! / ex: PostgreSQL vs. MySQL
@ 2003-10-09 08:29 Oliver Scheit <oliver.scheit@REALGARANT.DE>
0 siblings, 2 replies; 42+ messages in thread
From: Oliver Scheit @ 2003-10-09 08:29 UTC (permalink / raw)
To: pgsql-performance
Hi guys,
I followed the discussion and here are my 0.2$:
I think instead of thinking about where to put the
information about tuning, someone should provide a
"pgsql-autotune". Maybe even a shell script would do the
trick.
It's not so hard to find out, how much memory is installed,
and IMHO SHARED_BUFFERS, SORT_MEM and EFFECTIVE_CACHE_SIZE
depend heavily on this. a "cat /proc/sys/kernel/shmmax"
would give some valuable information on linux boxes,
there is probably other stuff for different OSes.
random_page_cost could be set after probing the harddisks,
maybe even do a hdparm -tT if they seem to be ATA, not SCSI.
Now, let's pretend the script finds out there is 1 GB RAM,
it could ask something like "Do you want to optimize the
settings for postgres (other applications may suffer from
having not enough RAM) or do you want to use moderate
settings?"
Something like this, you get the idea.
This would give new users a much more usable start than
the current default settings and would still leave all
the options to do fine-tuning later.
I guess my point is simply this:
instead of saying: "okay we use default settings that will
run on _old_ hardware too" we should go for a little script
that creates a "still save but much better" config file.
There's just no point in setting SHARED_BUFFERS to something
like 16 (what's the current default?) if the PC has >= 1 GB
of RAM. Setting it to 8192 would still be save, but 512 times
better... ;-) (IIRC 8192 would take 64 MB of RAM, which
should be save if you leave the default MAX_CONNECTIONS.)
As said before: just my $0.2
My opinion on this case is Open Source. Feel free to modify
and add. :-)
regards,
Oli
^ permalink raw reply [nested|flat] 42+ messages in thread
* Re: go for a script! / ex: PostgreSQL vs. MySQL
@ 2003-10-09 10:12 Paul Thomas <paul@tmsl.demon.co.uk>
parent: Oliver Scheit <oliver.scheit@REALGARANT.DE>
1 sibling, 0 replies; 42+ messages in thread
From: Paul Thomas @ 2003-10-09 10:12 UTC (permalink / raw)
To: pgsql-performance
On 09/10/2003 09:29 Oliver Scheit wrote:
> Hi guys,
>
> I followed the discussion and here are my 0.2$:
>
> I think instead of thinking about where to put the
> information about tuning, someone should provide a
> "pgsql-autotune". Maybe even a shell script would do the
> trick.
>
> It's not so hard to find out, how much memory is installed,
> and IMHO SHARED_BUFFERS, SORT_MEM and EFFECTIVE_CACHE_SIZE
> depend heavily on this. a "cat /proc/sys/kernel/shmmax"
> would give some valuable information on linux boxes,
> there is probably other stuff for different OSes.
>
> random_page_cost could be set after probing the harddisks,
> maybe even do a hdparm -tT if they seem to be ATA, not SCSI.
>
> Now, let's pretend the script finds out there is 1 GB RAM,
> it could ask something like "Do you want to optimize the
> settings for postgres (other applications may suffer from
> having not enough RAM) or do you want to use moderate
> settings?"
>
> Something like this, you get the idea.
ISR reading that 7.4 will use a default of shared_beffers = 1000 if the
machine can support it (most can). This alone should make a big difference
in out-of-the-box performance.
--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants |
http://www.thomas-micro-systems-ltd.co.uk |
+------------------------------+---------------------------------------------+
^ permalink raw reply [nested|flat] 42+ messages in thread
* Re: go for a script! / ex: PostgreSQL vs. MySQL
@ 2003-10-09 16:56 Josh Berkus <josh@agliodbs.com>
parent: Oliver Scheit <oliver.scheit@REALGARANT.DE>
1 sibling, 1 reply; 42+ messages in thread
From: Josh Berkus @ 2003-10-09 16:56 UTC (permalink / raw)
To: Oliver Scheit <oliver.scheit@REALGARANT.DE>; pgsql-performance
Oliver,
> I think instead of thinking about where to put the
> information about tuning, someone should provide a
> "pgsql-autotune". Maybe even a shell script would do the
> trick.
Well, you see, there's the issue. "I think someone." Lots of people have
spoken in favor of an "auto-conf" script; nobody so far has stepped forward
to get it done for 7.4, and I doubt we have time now.
I'll probably create a Perl script in a month or so, but not before that ....
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 42+ messages in thread
* Re: go for a script! / ex: PostgreSQL vs. MySQL
@ 2003-10-09 17:17 Dror Matalon <dror@zapatec.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 42+ messages in thread
From: Dror Matalon @ 2003-10-09 17:17 UTC (permalink / raw)
To: pgsql-performance
Yeah, I had similar thought to Oliver's and suspected that this would be
the answer.
Also, while it's not too hard to do this for a single platform, it gets
complecated once you start looking at different ones.
Josh, let me know when you're ready to do this. I'll try to help,
although my perl's kind of rusty. Also, can you even assume perl for a
postgres install? Does Solaris, for instance come with perl?
Dror
On Thu, Oct 09, 2003 at 09:56:11AM -0700, Josh Berkus wrote:
> Oliver,
>
> > I think instead of thinking about where to put the
> > information about tuning, someone should provide a
> > "pgsql-autotune". Maybe even a shell script would do the
> > trick.
>
> Well, you see, there's the issue. "I think someone." Lots of people have
> spoken in favor of an "auto-conf" script; nobody so far has stepped forward
> to get it done for 7.4, and I doubt we have time now.
>
> I'll probably create a Perl script in a month or so, but not before that ....
>
> --
> Josh Berkus
> Aglio Database Solutions
> San Francisco
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
Dror Matalon
Zapatec Inc
1700 MLK Way
Berkeley, CA 94709
http://www.zapatec.com
^ permalink raw reply [nested|flat] 42+ messages in thread
* Re: go for a script! / ex: PostgreSQL vs. MySQL
@ 2003-10-09 18:11 Sean Chittenden <sean@chittenden.org>
parent: Dror Matalon <dror@zapatec.com>
0 siblings, 1 reply; 42+ messages in thread
From: Sean Chittenden @ 2003-10-09 18:11 UTC (permalink / raw)
To: Dror Matalon <dror@zapatec.com>; +Cc: pgsql-performance
> Yeah, I had similar thought to Oliver's and suspected that this
> would be the answer. Also, while it's not too hard to do this for a
> single platform, it gets complecated once you start looking at
> different ones.
>
> Josh, let me know when you're ready to do this. I'll try to help,
> although my perl's kind of rusty. Also, can you even assume perl for
> a postgres install? Does Solaris, for instance come with perl?
Um, why not wait until the C version of initdb is committed, then
steak out a section that'll allow us to submit patches to have initdb
autotune to our hearts content? There's a tad bit of precedence with
having shared buffer's automatically set in initdb, why not continue
with it? I know under FreeBSD initdb will have some #ifdef's to wrap
around the syscall sysctl() to get info about kernel bits. Talking
about how to expand handle this gracefully for a gazillion different
platforms might be a more useful discussion at this point because I'm
sure people from their native OS will be able to contrib the necessary
patches to extract info from their OS so that initdb can make useful
decisions. Or, lastly, does anyone think that this should be in a
different, external program? -sc
--
Sean Chittenden
^ permalink raw reply [nested|flat] 42+ messages in thread
* go for a script! / ex: PostgreSQL vs. MySQL
@ 2003-10-10 12:35 Nick Barr <nicky@chuckie.co.uk>
0 siblings, 1 reply; 42+ messages in thread
From: Nick Barr @ 2003-10-10 12:35 UTC (permalink / raw)
To: pgsql-performance
Heya Guys n Gals,
Having been following the thread on "go for a script! / ex: PostgreSQL vs.
MySQL". I thought I would throw something together in Perl. My current issue
is that I only have access to a RH Linux box and so cannot make it
cross-platform on my own :-(. Anyhow please find it attached. It runs fine
on my box, it doesnt actually write to postgresql.conf because I didnt want
to mess it up, it does however write to postgresql.conf.new for the moment.
The diffs seem to be writing correctly. There are a set of parameters at the
top which may need to get tweaked for your platform. I can also carry on
posting to this list new versions if people want. Clearly this lot is open
source, so please feel free to play with it and post patches/new features
back either to the list or my email directly. In case you cant see my email
address, it is nicky at the domain below.
I will also post it on me website and as I develop it further new versions
will appear there
http://www.chuckie.co.uk/postgresql/pg_autoconfig.pl
Is this a useful start?
Nick
Attachments:
pg_autoconfig.pl (application/octet-stream, 6.6K) download
^ permalink raw reply [nested|flat] 42+ messages in thread
* Re: go for a script! / ex: PostgreSQL vs. MySQL
@ 2003-10-10 12:40 Nick Barr <nicky@chuckie.co.uk>
0 siblings, 0 replies; 42+ messages in thread
From: Nick Barr @ 2003-10-10 12:40 UTC (permalink / raw)
To: pgsql-performance
----- Original Message -----
From: "Nick Barr" <nicky@chuckie.co.uk>
To: <pgsql-performance@postgresql.org>
Sent: Friday, October 10, 2003 1:35 PM
Subject: go for a script! / ex: PostgreSQL vs. MySQL
> I will also post it on me website and as I develop it further new
versions
> will appear there
>
> http://www.chuckie.co.uk/postgresql/pg_autoconfig.pl
Make that
http://www.chuckie.co.uk/postgresql/pg_autoconfig.txt
Nick
^ permalink raw reply [nested|flat] 42+ messages in thread
* Re: go for a script! / ex: PostgreSQL vs. MySQL
@ 2003-10-10 17:34 Josh Berkus <josh@agliodbs.com>
parent: Nick Barr <nicky@chuckie.co.uk>
0 siblings, 1 reply; 42+ messages in thread
From: Josh Berkus @ 2003-10-10 17:34 UTC (permalink / raw)
To: Nick Barr <nicky@chuckie.co.uk>; pgsql-performance
Nick,
> Having been following the thread on "go for a script! / ex: PostgreSQL vs.
> MySQL". I thought I would throw something together in Perl.
Cool! Would you be willing to work with me so that I can inject some of my
knowledge of .conf tuning?
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 42+ messages in thread
* Re: go for a script! / ex: PostgreSQL vs. MySQL
@ 2003-10-10 18:08 Nick Barr <nicky@chuckie.co.uk>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 42+ messages in thread
From: Nick Barr @ 2003-10-10 18:08 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
Josh Berkus wrote:
>Nick,
>
>
>
>>Having been following the thread on "go for a script! / ex: PostgreSQL vs.
>>MySQL". I thought I would throw something together in Perl.
>>
>>
>
>Cool! Would you be willing to work with me so that I can inject some of my
>knowledge of .conf tuning?
>
>
>
Sounds good to me. I will carry on working on it but I would definitely
need some help, or at least a list of parameters to tweak, and some
recomended values based on data about the puter in question.
So far:
shared_buffers = 1/16th of total memory
effective_cache_size = 80% of the supposed kernel cache.
I guess we also may be able to offer a simple and advanced mode. Simple
mode would work on these recomended values, but kick it into advanced
mode and the user can tweak things more finely. This would only be
recomended for the Guru's out there of course. This may take a bit more
time to do though.
As I said in the previous email I have only got access to Linux, so
cross-platform help would be good too. I will try to make it as easy to
do cross platform stuff as possible of course.
Nick
^ permalink raw reply [nested|flat] 42+ messages in thread
* Re: go for a script! / ex: PostgreSQL vs. MySQL
@ 2003-10-10 20:22 Vivek Khera <khera@kcilink.com>
parent: Sean Chittenden <sean@chittenden.org>
0 siblings, 0 replies; 42+ messages in thread
From: Vivek Khera @ 2003-10-10 20:22 UTC (permalink / raw)
To: pgsql-performance
>>>>> "SC" == Sean Chittenden <sean@chittenden.org> writes:
SC> patches to extract info from their OS so that initdb can make useful
SC> decisions. Or, lastly, does anyone think that this should be in a
SC> different, external program? -sc
Well, there should definitely be a way to run a "get current best
tuning advice" for those times when I go and do something like add a
Gig of RAM. ;-)
Also, I'm sure the tuning advice will change over time, so having to
do initdb to get that advice would be a bit onerous.
As long as initdb has an option for just getting the tuning info, I
see no reason to make it separate.
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera@kciLink.com Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
^ permalink raw reply [nested|flat] 42+ messages in thread
* Re: go for a script! / ex: PostgreSQL vs. MySQL
@ 2003-10-10 20:26 Vivek Khera <khera@kcilink.com>
parent: Nick Barr <nicky@chuckie.co.uk>
1 sibling, 3 replies; 42+ messages in thread
From: Vivek Khera @ 2003-10-10 20:26 UTC (permalink / raw)
To: pgsql-performance
>>>>> "NB" == Nick Barr <nicky@chuckie.co.uk> writes:
NB> So far:
NB> shared_buffers = 1/16th of total memory
NB> effective_cache_size = 80% of the supposed kernel cache.
Please take into account the blocksize compiled into PG, too...
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera@kciLink.com Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
^ permalink raw reply [nested|flat] 42+ messages in thread
* Re: go for a script! / ex: PostgreSQL vs. MySQL
@ 2003-10-10 20:38 Josh Berkus <josh@agliodbs.com>
parent: Nick Barr <nicky@chuckie.co.uk>
1 sibling, 1 reply; 42+ messages in thread
From: Josh Berkus @ 2003-10-10 20:38 UTC (permalink / raw)
To: Nick Barr <nicky@chuckie.co.uk>; +Cc: pgsql-performance
Nick,
> Sounds good to me. I will carry on working on it but I would definitely
> need some help, or at least a list of parameters to tweak, and some
> recomended values based on data about the puter in question.
> shared_buffers = 1/16th of total memory
> effective_cache_size = 80% of the supposed kernel cache.
But only if it's a dedicated DB machine. If it's not, all memory values
should be cut in half.
> I guess we also may be able to offer a simple and advanced mode. Simple
> mode would work on these recomended values, but kick it into advanced
> mode and the user can tweak things more finely. This would only be
> recomended for the Guru's out there of course. This may take a bit more
> time to do though.
What I would prefer would be an interactive script which would, by asking the
user simple questions and system scanning, collect all the information
necessary to set:
max_connections
shared_buffers
sort_mem
vacuum_mem
effective_cache_size
random_page_cost
max_fsm_pages
checkpoint_segments & checkpoint_timeout
tcp_ip
and on the OS, it should set:
shmmax & shmmall
and should offer to create a chron job which does appropriate frequency VACUUM
ANALYZE.
> As I said in the previous email I have only got access to Linux, so
> cross-platform help would be good too. I will try to make it as easy to
> do cross platform stuff as possible of course.
Let's get it working on Linux; then we can rely on the community to port it to
other platforms. I myself can work on the ports to Solaris and OS X.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 42+ messages in thread
* Re: go for a script! / ex: PostgreSQL vs. MySQL
@ 2003-10-10 20:39 Josh Berkus <josh@agliodbs.com>
parent: Vivek Khera <khera@kcilink.com>
2 siblings, 1 reply; 42+ messages in thread
From: Josh Berkus @ 2003-10-10 20:39 UTC (permalink / raw)
To: Vivek Khera <khera@kcilink.com>; pgsql-performance
Vivek,
> NB> shared_buffers = 1/16th of total memory
> NB> effective_cache_size = 80% of the supposed kernel cache.
>
> Please take into account the blocksize compiled into PG, too...
We can;t change the blocksize in a script that only does the .conf file. Or
are you suggesting something else?
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 42+ messages in thread
* Re: go for a script! / ex: PostgreSQL vs. MySQL
@ 2003-10-10 21:14 Vivek Khera <khera@kcilink.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 42+ messages in thread
From: Vivek Khera @ 2003-10-10 21:14 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
>>>>> "JB" == Josh Berkus <josh@agliodbs.com> writes:
JB> Vivek,
NB> shared_buffers = 1/16th of total memory
NB> effective_cache_size = 80% of the supposed kernel cache.
>>
>> Please take into account the blocksize compiled into PG, too...
JB> We can;t change the blocksize in a script that only does the .conf
JB> file. Or are you suggesting something else?
when you compute optimal shared buffers and effective cache size,
these are in terms of blocksize. so if I have 16k block size, you
can't compute based on default 8k blocksize. at worst, it would have
to be a parameter you pass to the tuning script.
^ permalink raw reply [nested|flat] 42+ messages in thread
* Re: go for a script! / ex: PostgreSQL vs. MySQL
@ 2003-10-10 21:49 Josh Berkus <josh@agliodbs.com>
parent: Vivek Khera <khera@kcilink.com>
0 siblings, 0 replies; 42+ messages in thread
From: Josh Berkus @ 2003-10-10 21:49 UTC (permalink / raw)
To: Vivek Khera <khera@kcilink.com>; +Cc: pgsql-performance
Vivek,
> when you compute optimal shared buffers and effective cache size,
> these are in terms of blocksize. so if I have 16k block size, you
> can't compute based on default 8k blocksize. at worst, it would have
> to be a parameter you pass to the tuning script.
Oh, yes! Thank you.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 42+ messages in thread
* Re: go for a script! / ex: PostgreSQL vs. MySQL
@ 2003-10-10 22:59 Sean Chittenden <sean@chittenden.org>
parent: Vivek Khera <khera@kcilink.com>
2 siblings, 2 replies; 42+ messages in thread
From: Sean Chittenden @ 2003-10-10 22:59 UTC (permalink / raw)
To: Vivek Khera <khera@kcilink.com>; +Cc: pgsql-performance
> NB> So far:
>
> NB> shared_buffers = 1/16th of total memory
> NB> effective_cache_size = 80% of the supposed kernel cache.
>
> Please take into account the blocksize compiled into PG, too...
Would anyone object to a patch that exports the blocksize via a
readonly GUC? Too many tunables are page dependant, which is
infuriating when copying configs from DB to DB. I wish pgsql had some
notion of percentages for values that end with a '%'. -sc
--
Sean Chittenden
^ permalink raw reply [nested|flat] 42+ messages in thread
* Re: go for a script! / ex: PostgreSQL vs. MySQL
@ 2003-10-11 01:49 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Sean Chittenden <sean@chittenden.org>
1 sibling, 0 replies; 42+ messages in thread
From: Bruce Momjian @ 2003-10-11 01:49 UTC (permalink / raw)
To: Sean Chittenden <sean@chittenden.org>; +Cc: Vivek Khera <khera@kcilink.com>; pgsql-performance
Sean Chittenden wrote:
> > NB> So far:
> >
> > NB> shared_buffers = 1/16th of total memory
> > NB> effective_cache_size = 80% of the supposed kernel cache.
> >
> > Please take into account the blocksize compiled into PG, too...
>
> Would anyone object to a patch that exports the blocksize via a
> readonly GUC? Too many tunables are page dependant, which is
> infuriating when copying configs from DB to DB. I wish pgsql had some
> notion of percentages for values that end with a '%'. -sc
Makes sense to me --- we already have some read-only GUC variables.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 42+ messages in thread
* Re: go for a script! / ex: PostgreSQL vs. MySQL
@ 2003-10-11 01:55 Rod Taylor <rbt@rbt.ca>
parent: Sean Chittenden <sean@chittenden.org>
1 sibling, 1 reply; 42+ messages in thread
From: Rod Taylor @ 2003-10-11 01:55 UTC (permalink / raw)
To: Sean Chittenden <sean@chittenden.org>; +Cc: Vivek Khera <khera@kcilink.com>; pgsql-performance
On Fri, 2003-10-10 at 18:59, Sean Chittenden wrote:
> > NB> So far:
> >
> > NB> shared_buffers = 1/16th of total memory
> > NB> effective_cache_size = 80% of the supposed kernel cache.
> >
> > Please take into account the blocksize compiled into PG, too...
>
> Would anyone object to a patch that exports the blocksize via a
> readonly GUC? Too many tunables are page dependant, which is
> infuriating when copying configs from DB to DB. I wish pgsql had some
> notion of percentages for values that end with a '%'.
Rather than showing the block size, how about we change the tunables to
be physical sizes rather than block based?
effective_cache_size = 1.5GB
shared_buffers = 25MB
Percentages would be slick as well, but doing the above should fix most
of the issue -- and be friendlier to read.
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 42+ messages in thread
* Re: go for a script! / ex: PostgreSQL vs. MySQL
@ 2003-10-11 05:20 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Vivek Khera <khera@kcilink.com>
2 siblings, 1 reply; 42+ messages in thread
From: Christopher Kings-Lynne @ 2003-10-11 05:20 UTC (permalink / raw)
To: Vivek Khera <khera@kcilink.com>; +Cc: pgsql-performance
> NB> shared_buffers = 1/16th of total memory
> NB> effective_cache_size = 80% of the supposed kernel cache.
I think Sean(?) mentioned this one for FreeBSD (Bash code):
echo "effective_cache_size = $((`sysctl -n vfs.hibufspace` / 8192))"
I've used it for my dedicated servers. Is this calculation correct?
Chris
^ permalink raw reply [nested|flat] 42+ messages in thread
* Re: go for a script! / ex: PostgreSQL vs. MySQL
@ 2003-10-11 09:23 Sean Chittenden <sean@chittenden.org>
parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 1 reply; 42+ messages in thread
From: Sean Chittenden @ 2003-10-11 09:23 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: Vivek Khera <khera@kcilink.com>; pgsql-performance
> >NB> shared_buffers = 1/16th of total memory
> >NB> effective_cache_size = 80% of the supposed kernel cache.
>
> I think Sean(?) mentioned this one for FreeBSD (Bash code):
sh, not bash. :)
> echo "effective_cache_size = $((`sysctl -n vfs.hibufspace` / 8192))"
>
> I've used it for my dedicated servers. Is this calculation correct?
Yes, or it's real close at least. vfs.hibufspace is the amount of
kernel space that's used for caching IO operations (minus the
necessary space taken for the kernel). If you're real paranoid, you
could do some kernel profiling and figure out how much of the cache is
actually disk IO and multiply the above by some percentage, say 80%?
I haven't found it necessary to do so yet. Since hibufspace is all IO
and caching any net activity is kinda pointless and I assume that 100%
of it is used for a disk cache and don't use a multiplier. The 8192,
however, is the size of a PG page, so, if you tweak PG's page size,
you have to change this constant (*grumbles*).
-sc
--
Sean Chittenden
^ permalink raw reply [nested|flat] 42+ messages in thread
* Re: go for a script! / ex: PostgreSQL vs. MySQL
@ 2003-10-11 10:22 Harald Fuchs <nospam@sap.com>
parent: Rod Taylor <rbt@rbt.ca>
0 siblings, 1 reply; 42+ messages in thread
From: Harald Fuchs @ 2003-10-11 10:22 UTC (permalink / raw)
To: pgsql-performance
In article <camel@jester/">1065837333.12875.1.camel@jester>,
Rod Taylor <rbt@rbt.ca> writes:
>> Would anyone object to a patch that exports the blocksize via a
>> readonly GUC? Too many tunables are page dependant, which is
>> infuriating when copying configs from DB to DB. I wish pgsql had some
>> notion of percentages for values that end with a '%'.
> Rather than showing the block size, how about we change the tunables to
> be physical sizes rather than block based?
> effective_cache_size = 1.5GB
> shared_buffers = 25MB
Amen! Being forced to set config values in some obscure units rather
than bytes is an ugly braindamage which should be easy to fix.
^ permalink raw reply [nested|flat] 42+ messages in thread
* Re: go for a script! / ex: PostgreSQL vs. MySQL
@ 2003-10-11 10:43 Ron Johnson <ron.l.johnson@cox.net>
parent: Harald Fuchs <nospam@sap.com>
0 siblings, 0 replies; 42+ messages in thread
From: Ron Johnson @ 2003-10-11 10:43 UTC (permalink / raw)
To: pgsql-performance
On Sat, 2003-10-11 at 05:22, Harald Fuchs wrote:
> In article <camel@jester/">1065837333.12875.1.camel@jester>,
> Rod Taylor <rbt@rbt.ca> writes:
>
> >> Would anyone object to a patch that exports the blocksize via a
> >> readonly GUC? Too many tunables are page dependant, which is
> >> infuriating when copying configs from DB to DB. I wish pgsql had some
> >> notion of percentages for values that end with a '%'.
>
> > Rather than showing the block size, how about we change the tunables to
> > be physical sizes rather than block based?
>
> > effective_cache_size = 1.5GB
> > shared_buffers = 25MB
>
> Amen! Being forced to set config values in some obscure units rather
> than bytes is an ugly braindamage which should be easy to fix.
But it's too user-friendly to do it this way!
--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA
When Swedes start committing terrorism, I'll become suspicious of
Scandanavians.
^ permalink raw reply [nested|flat] 42+ messages in thread
* Re: go for a script! / ex: PostgreSQL vs. MySQL
@ 2003-10-11 14:55 Nick Barr <nicky@chuckie.co.uk>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 42+ messages in thread
From: Nick Barr @ 2003-10-11 14:55 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
Josh Berkus wrote:
>>shared_buffers = 1/16th of total memory
>>effective_cache_size = 80% of the supposed kernel cache.
>>
>>
>But only if it's a dedicated DB machine. If it's not, all memory values
>should be cut in half.
>
>
>
>What I would prefer would be an interactive script which would, by asking the
>user simple questions and system scanning, collect all the information
>necessary to set:
>
>max_connections
>shared_buffers
>sort_mem
>vacuum_mem
>effective_cache_size
>random_page_cost
>max_fsm_pages
>checkpoint_segments & checkpoint_timeout
>tcp_ip
>
>and on the OS, it should set:
>shmmax & shmmall
>and should offer to create a chron job which does appropriate frequency VACUUM
>ANALYZE.
>
>
I reckon do a system scan first, and parse the current PostgreSQL conf
file to figure out what the settings are. Also back it up with a date
and time appended to the end to make sure there is a backup before
overwriting the real conf file. Then a bunch of questions. What sort of
questions would need to be asked and which parameters would these
questions affect? So far, and from my limited understanding of the .conf
file, I reckon there should be the following
Here is your config of your hardware as detected. Is this correct ?
This could potentially be several questions, i.e. one for proc, mem,
os, hdd etc
Would affect shared_buffers, sort_mem, effective_cache_size,
random_page_cost
How was PostgreSQL compiled?
This would be parameters such as the block size and a few other
compile time parameters. If we can get to some of these read-only
parameters than that would make this step easier, certainly for the new
recruits amongst us.
Is PostgreSQL the only thing being run on this computer?
Then my previous assumptions about shared_buffers and
effective_cache_size would be true.
If shmmax and shmmall are too small, then:
PostgreSQL requires some more shared memory to cache some tables, x Mb,
do you want to increase your OS kernel parameters?
Tweak shmmax and shmmall
How are the clients going to connect?
i.e. TCP or Unix sockets
How many clients can connect to this database at once?
Affects max_connections
How many databases and how many tables in each database are going to be
present?
Affects max_fsm_pages, checkpoint_segments, checkpoint_timeout
Do you want to vacuum you database regularly?
Initial question for cron job
It is recomended that you vacuum analyze every night, do you want to do
this?
It is also recomended that you vacuum full every month, do you want to
do this?
Thoughts?
Nick
^ permalink raw reply [nested|flat] 42+ messages in thread
* Re: go for a script! / ex: PostgreSQL vs. MySQL
@ 2003-10-12 02:43 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Nick Barr <nicky@chuckie.co.uk>
1 sibling, 2 replies; 42+ messages in thread
From: Christopher Kings-Lynne @ 2003-10-12 02:43 UTC (permalink / raw)
To: Nick Barr <nicky@chuckie.co.uk>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-performance
> If shmmax and shmmall are too small, then:
>
> PostgreSQL requires some more shared memory to cache some tables, x Mb,
> do you want to increase your OS kernel parameters?
>
> Tweak shmmax and shmmall
Note that this still requires a kernel recompile on FreeBSD :(
Chris
^ permalink raw reply [nested|flat] 42+ messages in thread
* Re: go for a script! / ex: PostgreSQL vs. MySQL
@ 2003-10-12 20:30 Josh Berkus <josh@agliodbs.com>
parent: Nick Barr <nicky@chuckie.co.uk>
1 sibling, 1 reply; 42+ messages in thread
From: Josh Berkus @ 2003-10-12 20:30 UTC (permalink / raw)
To: Nick Barr <nicky@chuckie.co.uk>; +Cc: pgsql-performance
Nick,
> I reckon do a system scan first, and parse the current PostgreSQL conf
> file to figure out what the settings are. Also back it up with a date
> and time appended to the end to make sure there is a backup before
> overwriting the real conf file. Then a bunch of questions. What sort of
> questions would need to be asked and which parameters would these
> questions affect? So far, and from my limited understanding of the .conf
> file, I reckon there should be the following
Hmmm ... but I do think that there should be a file to store the user's
previous answers. That way, the script can easily be re-run to fix config
issues.
> Here is your config of your hardware as detected. Is this correct ?
>
> This could potentially be several questions, i.e. one for proc, mem,
> os, hdd etc
> Would affect shared_buffers, sort_mem, effective_cache_size,
> random_page_cost
Actually, I think this would break down into:
-- Are Proc & Mem correct? If not, type in correct values
-- Is OS correct? If not, select from list
-- Your HDD: is it:
1) IDE
2) Fast multi-disk SCSI or low-end RAID
3) Medium-to-high-end RAID
Other things, we don't care about.
> How was PostgreSQL compiled?
>
> This would be parameters such as the block size and a few other
> compile time parameters. If we can get to some of these read-only
> parameters than that would make this step easier, certainly for the new
> recruits amongst us.
Actually, from my perspective, we shouldn't bother with this; if an admin
knows enough to set an alternate blaock size for PG, then they know enough to
tweak the Conf file by hand. I think we should just issue a warning that
this script:
1) does not work for anyone who is using non-default block sizes,
2) may not work well for anyone using unusual locales, optimization flags, or
other non-default compile options except for language interfaces.
3) cannot produce good settings for embedded systems;
4) will not work well for systems which are extremely low on disk space,
memory, or other resouces.
Basically, the script only really needs to work for the people who are
installing PostgreSQL with the default options or from RPM on regular server
or workstation machines with plenty of disk space for normal database
purposes. People who have more complicated setups can read the darned
documentation and tune the conf file by hand.
> Is PostgreSQL the only thing being run on this computer?
First, becuase it affects a couple of other variables:
What kind of database server are you expecting to run?
A) Web Server (many small fast queries from many users, and not much update
activity)
B) Online Transaction Processing (OLTP) database (many small updates
constantly from many users; think "accounting application").
C) Online Analytical Reporting (OLAP) database (a few large and complicated
read-only queries aggregating large quantites of data for display)
D) Data Transformation tool (loading large amounts of data to process,
transform, and output to other software)
E) Mixed-Use Database Server (a little of all of the above)
F) Workstation (installing this database on a user machine which also has a
desktop, does word processing, etc.)
If the user answers anything but (F), then we ask:
Will you be running any other signficant software on this server, such as a
web server, a Java runtime engine, or a reporting application? (yes|no)
If yes, then:
How much memory do you expect this other software, in total, to regularly use
while PostgreSQL is in use? (# in MB; should offer default of 50% of the RAM
scanned).
> How are the clients going to connect?
>
> i.e. TCP or Unix sockets
We should warn them that they will still need to configure pg_hba.conf.
> How many clients can connect to this database at once?
>
> Affects max_connections
Should add a parenthetical comment that for applications which use pooled
connections, or intermittent connection, such as Web applications, the number
of concurrent connections is often much lower than the number of concurrent
users.
> How many databases and how many tables in each database are going to be
> present?
>
> Affects max_fsm_pages, checkpoint_segments, checkpoint_timeout
Also need to ask if they have an idea of the total size of all databases, in
MB or GB, which has a stronger relationship to those variables.
Also, this will give us a chance to check the free space on the PGDATA
partition, and kick the user out with a warning if there is not at least
2xExpected Size available.
> Do you want to vacuum you database regularly?
>
> Initial question for cron job
>
> It is recomended that you vacuum analyze every night, do you want to do
> this?
> It is also recomended that you vacuum full every month, do you want to
> do this?
Depends on size/type of database. For large OLTP databases, I recommend
vacuum as often as every 5 mintues, analyze every hour, and Vacuum Full +
Reindex once a week. For a workstation database, your frequencies are
probably OK.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 42+ messages in thread
* Re: go for a script! / ex: PostgreSQL vs. MySQL
@ 2003-10-12 20:31 Josh Berkus <josh@agliodbs.com>
parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
1 sibling, 3 replies; 42+ messages in thread
From: Josh Berkus @ 2003-10-12 20:31 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; Nick Barr <nicky@chuckie.co.uk>; +Cc: pgsql-performance
Chris,
> > PostgreSQL requires some more shared memory to cache some tables, x Mb,
> > do you want to increase your OS kernel parameters?
> >
> > Tweak shmmax and shmmall
>
> Note that this still requires a kernel recompile on FreeBSD :(
Not our fault, now is it? This would mean that we wouldn't be able to script
for FreeBSD. Bug the FreeBSD developers.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 42+ messages in thread
* Re: [PERFORM] go for a script! / ex: PostgreSQL vs. MySQL
@ 2003-10-12 22:10 Ron Johnson <ron.l.johnson@cox.net>
parent: Josh Berkus <josh@agliodbs.com>
2 siblings, 0 replies; 42+ messages in thread
From: Ron Johnson @ 2003-10-12 22:10 UTC (permalink / raw)
To: PgSQL General ML <pgsql-general@postgresql.org>
On Sun, 2003-10-12 at 15:31, Josh Berkus wrote:
> Chris,
>
> > > PostgreSQL requires some more shared memory to cache some tables, x Mb,
> > > do you want to increase your OS kernel parameters?
> > >
> > > Tweak shmmax and shmmall
> >
> > Note that this still requires a kernel recompile on FreeBSD :(
>
> Not our fault, now is it? This would mean that we wouldn't be able to script
> for FreeBSD. Bug the FreeBSD developers.
<TROLL=HAND-GRENADE>
Or use a good OS, instead.
</TROLL>
--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@cox.net
Jefferson, LA USA
"Oh, great altar of passive entertainment, bestow upon me thy
discordant images at such speed as to render linear thought
impossible"
Calvin, regarding TV
^ permalink raw reply [nested|flat] 42+ messages in thread
* Re: go for a script! / ex: PostgreSQL vs. MySQL
@ 2003-10-12 22:47 Ivan Voras <ivoras@fer.hr>
0 siblings, 2 replies; 42+ messages in thread
From: Ivan Voras @ 2003-10-12 22:47 UTC (permalink / raw)
To: pgsql-performance
> Date: Sun, 12 Oct 2003 13:30:45 -0700
> From: Josh Berkus <josh@agliodbs.com>
> To: Nick Barr <nicky@chuckie.co.uk>
> Cc: pgsql-performance@postgresql.org
> Subject: Re: go for a script! / ex: PostgreSQL vs. MySQL
> Message-ID: <200310121330.45115.josh@agliodbs.com>
>
>> This would be parameters such as the block size and a few other
>> compile time parameters. If we can get to some of these read-only
>> parameters than that would make this step easier, certainly for the new
>> recruits amongst us.
>
> Actually, from my perspective, we shouldn't bother with this; if an admin
> knows enough to set an alternate blaock size for PG, then they know
> enough to tweak the Conf file by hand. I think we should just issue a
> warning that this script:
> 1) does not work for anyone who is using non-default block sizes,
There was some talk, either on this list or freebsd-performance about
setting the default block size for PostgreSQL running on FreeBSD to be 16k
because of performance reasons. That is: *default* for the port, user is not
asked. So an automagical method to scale non-default block sizes is a very
needed thing.
> 2) may not work well for anyone using unusual locales, optimization
> flags, or other non-default compile options except for language
> interfaces.
Depends on what you consider 'unusual'? I hope not things like iso8859-x
(or, to be exact, European languages) :)
--
Logic is a systematic method of coming to the wrong conclusion with
confidence.
^ permalink raw reply [nested|flat] 42+ messages in thread
* Re: go for a script! / ex: PostgreSQL vs. MySQL
@ 2003-10-13 05:01 pginfo <pginfo@t1.unisoftbg.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 42+ messages in thread
From: pginfo @ 2003-10-13 05:01 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Nick Barr <nicky@chuckie.co.uk>; pgsql-performance
Hi,
Josh Berkus wrote:
> Nick,
>
> > I reckon do a system scan first, and parse the current PostgreSQL conf
> > file to figure out what the settings are. Also back it up with a date
> > and time appended to the end to make sure there is a backup before
> > overwriting the real conf file. Then a bunch of questions. What sort of
> > questions would need to be asked and which parameters would these
> > questions affect? So far, and from my limited understanding of the .conf
> > file, I reckon there should be the following
>
> Hmmm ... but I do think that there should be a file to store the user's
> previous answers. That way, the script can easily be re-run to fix config
> issues.
>
> > Here is your config of your hardware as detected. Is this correct ?
> >
> > This could potentially be several questions, i.e. one for proc, mem,
> > os, hdd etc
> > Would affect shared_buffers, sort_mem, effective_cache_size,
> > random_page_cost
>
> Actually, I think this would break down into:
> -- Are Proc & Mem correct? If not, type in correct values
> -- Is OS correct? If not, select from list
> -- Your HDD: is it:
> 1) IDE
> 2) Fast multi-disk SCSI or low-end RAID
> 3) Medium-to-high-end RAID
>
> Other things, we don't care about.
>
> > How was PostgreSQL compiled?
> >
> > This would be parameters such as the block size and a few other
> > compile time parameters. If we can get to some of these read-only
> > parameters than that would make this step easier, certainly for the new
> > recruits amongst us.
>
> Actually, from my perspective, we shouldn't bother with this; if an admin
> knows enough to set an alternate blaock size for PG, then they know enough to
> tweak the Conf file by hand. I think we should just issue a warning that
> this script:
> 1) does not work for anyone who is using non-default block sizes,
> 2) may not work well for anyone using unusual locales, optimization flags, or
> other non-default compile options except for language interfaces.
> 3) cannot produce good settings for embedded systems;
> 4) will not work well for systems which are extremely low on disk space,
> memory, or other resouces.
> Basically, the script only really needs to work for the people who are
> installing PostgreSQL with the default options or from RPM on regular server
> or workstation machines with plenty of disk space for normal database
> purposes. People who have more complicated setups can read the darned
> documentation and tune the conf file by hand.
>
> > Is PostgreSQL the only thing being run on this computer?
>
> First, becuase it affects a couple of other variables:
>
> What kind of database server are you expecting to run?
> A) Web Server (many small fast queries from many users, and not much update
> activity)
> B) Online Transaction Processing (OLTP) database (many small updates
> constantly from many users; think "accounting application").
> C) Online Analytical Reporting (OLAP) database (a few large and complicated
> read-only queries aggregating large quantites of data for display)
> D) Data Transformation tool (loading large amounts of data to process,
> transform, and output to other software)
> E) Mixed-Use Database Server (a little of all of the above)
> F) Workstation (installing this database on a user machine which also has a
> desktop, does word processing, etc.)
>
> If the user answers anything but (F), then we ask:
>
> Will you be running any other signficant software on this server, such as a
> web server, a Java runtime engine, or a reporting application? (yes|no)
>
> If yes, then:
>
> How much memory do you expect this other software, in total, to regularly use
> while PostgreSQL is in use? (# in MB; should offer default of 50% of the RAM
> scanned).
>
> > How are the clients going to connect?
> >
> > i.e. TCP or Unix sockets
>
> We should warn them that they will still need to configure pg_hba.conf.
>
> > How many clients can connect to this database at once?
> >
> > Affects max_connections
>
> Should add a parenthetical comment that for applications which use pooled
> connections, or intermittent connection, such as Web applications, the number
> of concurrent connections is often much lower than the number of concurrent
> users.
>
> > How many databases and how many tables in each database are going to be
> > present?
> >
> > Affects max_fsm_pages, checkpoint_segments, checkpoint_timeout
>
> Also need to ask if they have an idea of the total size of all databases, in
> MB or GB, which has a stronger relationship to those variables.
>
Why not to make a cron script that will detect this size fot hil self?In many
cases we do not have a good idea how many records(size) will be in data base.
> Also, this will give us a chance to check the free space on the PGDATA
> partition, and kick the user out with a warning if there is not at least
> 2xExpected Size available.
>
> > Do you want to vacuum you database regularly?
> >
> > Initial question for cron job
> >
> > It is recomended that you vacuum analyze every night, do you want to do
> > this?
> > It is also recomended that you vacuum full every month, do you want to
> > do this?
>
> Depends on size/type of database. For large OLTP databases, I recommend
> vacuum as often as every 5 mintues, analyze every hour, and Vacuum Full +
> Reindex once a week. For a workstation database, your frequencies are
> probably OK.
>
> --
> Josh Berkus
> Aglio Database Solutions
> San Francisco
>
regards,ivan.
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
^ permalink raw reply [nested|flat] 42+ messages in thread
* Re: go for a script! / ex: PostgreSQL vs. MySQL
@ 2003-10-13 14:04 Vivek Khera <khera@kcilink.com>
parent: Sean Chittenden <sean@chittenden.org>
0 siblings, 2 replies; 42+ messages in thread
From: Vivek Khera @ 2003-10-13 14:04 UTC (permalink / raw)
To: pgsql-performance
>>>>> "SC" == Sean Chittenden <sean@chittenden.org> writes:
>> echo "effective_cache_size = $((`sysctl -n vfs.hibufspace` / 8192))"
>>
>> I've used it for my dedicated servers. Is this calculation correct?
SC> Yes, or it's real close at least. vfs.hibufspace is the amount of
SC> kernel space that's used for caching IO operations (minus the
I'm just curious if anyone has a tip to increase the amount of memory
FreeBSD will use for the cache? It appears to me that even on my 2Gb
box, lots of memory is 'free' that could be used for the cache
(bumping up shared buffers is another option...) yet the disk is being
highly utilized according to systat.
^ permalink raw reply [nested|flat] 42+ messages in thread
* Re: go for a script! / ex: PostgreSQL vs. MySQL
@ 2003-10-13 14:14 Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
parent: Vivek Khera <khera@kcilink.com>
1 sibling, 0 replies; 42+ messages in thread
From: Shridhar Daithankar @ 2003-10-13 14:14 UTC (permalink / raw)
To: Vivek Khera <khera@kcilink.com>; pgsql-performance
On Monday 13 October 2003 19:34, Vivek Khera wrote:
> >>>>> "SC" == Sean Chittenden <sean@chittenden.org> writes:
> >>
> >> echo "effective_cache_size = $((`sysctl -n vfs.hibufspace` / 8192))"
> >>
> >> I've used it for my dedicated servers. Is this calculation correct?
>
> SC> Yes, or it's real close at least. vfs.hibufspace is the amount of
> SC> kernel space that's used for caching IO operations (minus the
>
> I'm just curious if anyone has a tip to increase the amount of memory
> FreeBSD will use for the cache? It appears to me that even on my 2Gb
> box, lots of memory is 'free' that could be used for the cache
> (bumping up shared buffers is another option...) yet the disk is being
> highly utilized according to systat.
Is this of any help?..reverse video sucks though.. especially spec'ed person
like me..
http://unix.derkeiler.com/Mailing-Lists/FreeBSD/performance/2003-07/0073.html
Shridhar
^ permalink raw reply [nested|flat] 42+ messages in thread
* Re: go for a script! / ex: PostgreSQL vs. MySQL
@ 2003-10-13 15:37 Vivek Khera <khera@kcilink.com>
parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
1 sibling, 1 reply; 42+ messages in thread
From: Vivek Khera @ 2003-10-13 15:37 UTC (permalink / raw)
To: pgsql-performance
>>>>> "CK" == Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
>> If shmmax and shmmall are too small, then:
>> PostgreSQL requires some more shared memory to cache some tables, x
>> Mb, do you want to increase your OS kernel parameters?
>> Tweak shmmax and shmmall
CK> Note that this still requires a kernel recompile on FreeBSD :(
According to whom? sysctl is your friend. Some sysctl settings may
require reboot, but I don't think the SHM ones do.
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera@kciLink.com Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
^ permalink raw reply [nested|flat] 42+ messages in thread
* Re: go for a script! / ex: PostgreSQL vs. MySQL
@ 2003-10-13 15:39 Vivek Khera <khera@kcilink.com>
parent: Josh Berkus <josh@agliodbs.com>
2 siblings, 1 reply; 42+ messages in thread
From: Vivek Khera @ 2003-10-13 15:39 UTC (permalink / raw)
To: pgsql-performance
>>>>> "JB" == Josh Berkus <josh@agliodbs.com> writes:
JB> Chris,
>> > PostgreSQL requires some more shared memory to cache some tables, x Mb,
>> > do you want to increase your OS kernel parameters?
>> >
>> > Tweak shmmax and shmmall
>>
>> Note that this still requires a kernel recompile on FreeBSD :(
JB> Not our fault, now is it? This would mean that we wouldn't be
JB> able to script for FreeBSD. Bug the FreeBSD developers.
"I read it on the net so it must be true" applies here. You /can/ set
these values via sysctl calls.
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera@kciLink.com Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
^ permalink raw reply [nested|flat] 42+ messages in thread
* Re: go for a script! / ex: PostgreSQL vs. MySQL
@ 2003-10-13 19:04 Sean Chittenden <sean@chittenden.org>
parent: Vivek Khera <khera@kcilink.com>
1 sibling, 0 replies; 42+ messages in thread
From: Sean Chittenden @ 2003-10-13 19:04 UTC (permalink / raw)
To: Vivek Khera <khera@kcilink.com>; +Cc: pgsql-performance
> >> echo "effective_cache_size = $((`sysctl -n vfs.hibufspace` / 8192))"
> >>
> >> I've used it for my dedicated servers. Is this calculation correct?
>
> SC> Yes, or it's real close at least. vfs.hibufspace is the amount
> of SC> kernel space that's used for caching IO operations (minus the
>
> I'm just curious if anyone has a tip to increase the amount of
> memory FreeBSD will use for the cache?
Recompile your kernel with BKVASIZE set to 4 times its current value
and double your nbuf size. According to Bruce Evans:
"Actually there is a way: the vfs_maxbufspace gives the amount of
space reserved for buffer kva (= nbuf * BKVASIZE). nbuf is easy to
recover from this, and the buffer kva space may be what is wanted
anyway."
[snip]
"I've never found setting nbuf useful, however. I want most
parametrized sizes including nbuf to scale with resource sizes, and
it's only with RAM sizes of similar sizes to the total virtual address
size that its hard to get things to fit. I haven't hit this problem
myself since my largest machine has only 1GB. I use an nbuf of
something like twice the default one, and a BKVASIZE of 4 times the
default. vfs.maxbufspace ends up at 445MB on the machine with 1GB, so
it is maxed out now."
YMMV.
-sc
--
Sean Chittenden
^ permalink raw reply [nested|flat] 42+ messages in thread
* Re: go for a script! / ex: PostgreSQL vs. MySQL
@ 2003-10-13 19:10 Sean Chittenden <sean@chittenden.org>
parent: Josh Berkus <josh@agliodbs.com>
2 siblings, 1 reply; 42+ messages in thread
From: Sean Chittenden @ 2003-10-13 19:10 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; Nick Barr <nicky@chuckie.co.uk>; pgsql-performance
> > > PostgreSQL requires some more shared memory to cache some
> > > tables, x Mb, do you want to increase your OS kernel parameters?
> > >
> > > Tweak shmmax and shmmall
> >
> > Note that this still requires a kernel recompile on FreeBSD :(
>
> Not our fault, now is it? This would mean that we wouldn't be able
> to script for FreeBSD. Bug the FreeBSD developers.
And if you do so, you're going to hear that shm* is an antiquated
interface that's dated, slow, inefficient and shouldn't be used. :)
Every few months one of the uber core BSD hackers threatens to rewrite
that part of PG because high up in the BSD camp, it's common belief
that shm* is a source of performance loss for PostgreSQL. One of
these days it'll happen, probably with mmap() mmap()'ing MAP_SHARED
files stored in a $PGDATA/data/shared dir as mmap() is by far and away
the fastest shared memory mechanism and certainly is very widely
deployed (I would be surprised if any of the supported PG platforms
didn't have mmap()).
-sc
--
Sean Chittenden
^ permalink raw reply [nested|flat] 42+ messages in thread
* Re: go for a script! / ex: PostgreSQL vs. MySQL
@ 2003-10-13 23:58 Christopher Browne <cbbrowne@acm.org>
parent: Vivek Khera <khera@kcilink.com>
0 siblings, 1 reply; 42+ messages in thread
From: Christopher Browne @ 2003-10-13 23:58 UTC (permalink / raw)
To: pgsql-performance
Centuries ago, Nostradamus foresaw when khera@kcilink.com (Vivek Khera) would write:
>>>>>> "JB" == Josh Berkus <josh@agliodbs.com> writes:
>
> JB> Chris,
>>> > PostgreSQL requires some more shared memory to cache some tables, x Mb,
>>> > do you want to increase your OS kernel parameters?
>>> >
>>> > Tweak shmmax and shmmall
>>>
>>> Note that this still requires a kernel recompile on FreeBSD :(
>
> JB> Not our fault, now is it? This would mean that we wouldn't be
> JB> able to script for FreeBSD. Bug the FreeBSD developers.
>
> "I read it on the net so it must be true" applies here. You /can/ set
> these values via sysctl calls.
Yes, indeed, sysctl can tweak these values fairly adequately.
Now, numbers of semaphors are not as readily tweaked; I wound up
limited, the other day, when I tried setting values for...
kern.ipc.semmns
kern.ipc.semmni
--
let name="cbbrowne" and tld="ntlug.org" in String.concat "@" [name;tld];;
http://www.ntlug.org/~cbbrowne/x.html
"So, when you typed in the date, it exploded into a sheet of blue
flame and burned the entire admin wing to the ground? Yes, that's a
known bug. We'll be fixing it in the next release. Until then, try not
to use European date format, and keep an extinguisher handy."
-- slam@pobox.com (Tequila Rapide)
^ permalink raw reply [nested|flat] 42+ messages in thread
* Re: go for a script! / ex: PostgreSQL vs. MySQL
@ 2003-10-14 00:11 Tom Lane <tgl@sss.pgh.pa.us>
parent: Sean Chittenden <sean@chittenden.org>
0 siblings, 0 replies; 42+ messages in thread
From: Tom Lane @ 2003-10-14 00:11 UTC (permalink / raw)
To: Sean Chittenden <sean@chittenden.org>; +Cc: Josh Berkus <josh@agliodbs.com>; Christopher Kings-Lynne <chriskl@familyhealth.com.au>; Nick Barr <nicky@chuckie.co.uk>; pgsql-performance
Sean Chittenden <sean@chittenden.org> writes:
> Every few months one of the uber core BSD hackers threatens to rewrite
> that part of PG because high up in the BSD camp, it's common belief
> that shm* is a source of performance loss for PostgreSQL.
They're full of it. RAM is RAM, no? Once you've got the memory mapped
into your address space, it's hard to believe that it matters how you
got hold of it.
In any case, mmap doesn't have the semantics we need. See past
discussions.
regards, tom lane
^ permalink raw reply [nested|flat] 42+ messages in thread
* Re: go for a script! / ex: PostgreSQL vs. MySQL
@ 2003-10-14 01:29 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Vivek Khera <khera@kcilink.com>
0 siblings, 0 replies; 42+ messages in thread
From: Christopher Kings-Lynne @ 2003-10-14 01:29 UTC (permalink / raw)
To: Vivek Khera <khera@kcilink.com>; +Cc: pgsql-performance
>>>If shmmax and shmmall are too small, then:
>>>PostgreSQL requires some more shared memory to cache some tables, x
>>>Mb, do you want to increase your OS kernel parameters?
>>>Tweak shmmax and shmmall
>
>
> CK> Note that this still requires a kernel recompile on FreeBSD :(
>
> According to whom? sysctl is your friend. Some sysctl settings may
> require reboot, but I don't think the SHM ones do.
Hmmm...you may be right - I can't prove it now...
houston# sysctl -w kern.ipc.shmmax=99999999999
kern.ipc.shmmax: 33554432 -> 2147483647
Hrm. Ok. Maybe they've changed that in some recent version :)
Chris
^ permalink raw reply [nested|flat] 42+ messages in thread
* Re: go for a script! / ex: PostgreSQL vs. MySQL
@ 2003-10-14 01:43 Christopher Kings-Lynne <chriskl@familyhealth.com.au>
parent: Christopher Browne <cbbrowne@acm.org>
0 siblings, 0 replies; 42+ messages in thread
From: Christopher Kings-Lynne @ 2003-10-14 01:43 UTC (permalink / raw)
To: Christopher Browne <cbbrowne@acm.org>; +Cc: pgsql-performance
>
> Yes, indeed, sysctl can tweak these values fairly adequately.
>
> Now, numbers of semaphors are not as readily tweaked; I wound up
> limited, the other day, when I tried setting values for...
>
> kern.ipc.semmns
> kern.ipc.semmni
Same. Maybe that was the option I was thinking was read-only:
houston# sysctl kern.ipc.semmns
kern.ipc.semmns: 60
houston# sysctl -w kern.ipc.semmns=70
sysctl: oid 'kern.ipc.semmns' is read only
houston# sysctl kern.ipc.semmni
kern.ipc.semmni: 10
houston# sysctl -w kern.ipc.semmni=30
sysctl: oid 'kern.ipc.semmni' is read only
I like how they use oids :P
Chris
^ permalink raw reply [nested|flat] 42+ messages in thread
* Re: go for a script! / ex: PostgreSQL vs. MySQL
@ 2003-10-14 17:09 Josh Berkus <josh@agliodbs.com>
parent: Ivan Voras <ivoras@fer.hr>
1 sibling, 1 reply; 42+ messages in thread
From: Josh Berkus @ 2003-10-14 17:09 UTC (permalink / raw)
To: Ivan Voras <ivoras@fer.hr>; pgsql-performance
Ivan,
> There was some talk, either on this list or freebsd-performance about
> setting the default block size for PostgreSQL running on FreeBSD to be 16k
> because of performance reasons. That is: *default* for the port, user is
> not asked. So an automagical method to scale non-default block sizes is a
> very needed thing.
Hmmm ... possibly. My concern is that if someone uses a very non-default
value, such as 256K, then they are probably better off doing their own tuning
because they've got an unusual system. However, we could easily limit it to
the range of 4K to 32K.
Of course, since there's no GUC var, we'd have to ask the user to confirm
their block size. I'm reluctant to take this approach because if the user
gets it wrong, then the settings will be *way* off ... and possibly cause
PostgreSQL to be unrunnable or have "out of memory" crashes.
Unless there's a way to find it in the compiled source?
> > 2) may not work well for anyone using unusual locales, optimization
> > flags, or other non-default compile options except for language
> > interfaces.
>
> Depends on what you consider 'unusual'? I hope not things like iso8859-x
> (or, to be exact, European languages) :)
On second thought, I'm not sure what an "unusual locale" would be. Scratch
that.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 42+ messages in thread
* Re: go for a script! / ex: PostgreSQL vs. MySQL
@ 2003-10-14 18:15 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 42+ messages in thread
From: Tom Lane @ 2003-10-14 18:15 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Ivan Voras <ivoras@fer.hr>; pgsql-performance
Josh Berkus <josh@agliodbs.com> writes:
> Unless there's a way to find it in the compiled source?
See pg_controldata.
regards, tom lane
^ permalink raw reply [nested|flat] 42+ messages in thread
* Re: go for a script! / ex: PostgreSQL vs. MySQL
@ 2003-10-14 18:42 Sean Chittenden <sean@chittenden.org>
parent: Ivan Voras <ivoras@fer.hr>
1 sibling, 0 replies; 42+ messages in thread
From: Sean Chittenden @ 2003-10-14 18:42 UTC (permalink / raw)
To: Ivan Voras <ivoras@fer.hr>; +Cc: pgsql-performance
> >> This would be parameters such as the block size and a few
> >> other compile time parameters. If we can get to some of these
> >> read-only parameters than that would make this step easier,
> >> certainly for the new recruits amongst us.
> >
> > Actually, from my perspective, we shouldn't bother with this; if an admin
> > knows enough to set an alternate blaock size for PG, then they know
> > enough to tweak the Conf file by hand. I think we should just issue a
> > warning that this script:
> > 1) does not work for anyone who is using non-default block sizes,
>
> There was some talk, either on this list or freebsd-performance
> about setting the default block size for PostgreSQL running on
> FreeBSD to be 16k because of performance reasons. That is: *default*
> for the port, user is not asked.
Real quick, this isn't true, the block size is tunable, but does not
change the default. You can set PGBLOCKSIZE to the values "16K" or
"32K" to change the block size, but the default remains 8K.
http://lists.freebsd.org/pipermail/freebsd-database/2003-October/000111.html
-sc
--
Sean Chittenden
^ permalink raw reply [nested|flat] 42+ messages in thread
end of thread, other threads:[~2003-10-14 18:42 UTC | newest]
Thread overview: 42+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-09 08:29 go for a script! / ex: PostgreSQL vs. MySQL Oliver Scheit <oliver.scheit@REALGARANT.DE>
2003-10-09 10:12 ` Paul Thomas <paul@tmsl.demon.co.uk>
2003-10-09 16:56 ` Josh Berkus <josh@agliodbs.com>
2003-10-09 17:17 ` Dror Matalon <dror@zapatec.com>
2003-10-09 18:11 ` Sean Chittenden <sean@chittenden.org>
2003-10-10 20:22 ` Vivek Khera <khera@kcilink.com>
2003-10-10 12:35 go for a script! / ex: PostgreSQL vs. MySQL Nick Barr <nicky@chuckie.co.uk>
2003-10-10 17:34 ` Josh Berkus <josh@agliodbs.com>
2003-10-10 18:08 ` Nick Barr <nicky@chuckie.co.uk>
2003-10-10 20:26 ` Vivek Khera <khera@kcilink.com>
2003-10-10 20:39 ` Josh Berkus <josh@agliodbs.com>
2003-10-10 21:14 ` Vivek Khera <khera@kcilink.com>
2003-10-10 21:49 ` Josh Berkus <josh@agliodbs.com>
2003-10-10 22:59 ` Sean Chittenden <sean@chittenden.org>
2003-10-11 01:49 ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-10-11 01:55 ` Rod Taylor <rbt@rbt.ca>
2003-10-11 10:22 ` Harald Fuchs <nospam@sap.com>
2003-10-11 10:43 ` Ron Johnson <ron.l.johnson@cox.net>
2003-10-11 05:20 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-10-11 09:23 ` Sean Chittenden <sean@chittenden.org>
2003-10-13 14:04 ` Vivek Khera <khera@kcilink.com>
2003-10-13 14:14 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-10-13 19:04 ` Sean Chittenden <sean@chittenden.org>
2003-10-10 20:38 ` Josh Berkus <josh@agliodbs.com>
2003-10-11 14:55 ` Nick Barr <nicky@chuckie.co.uk>
2003-10-12 02:43 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-10-12 20:31 ` Josh Berkus <josh@agliodbs.com>
2003-10-12 22:10 ` Ron Johnson <ron.l.johnson@cox.net>
2003-10-13 15:39 ` Vivek Khera <khera@kcilink.com>
2003-10-13 23:58 ` Christopher Browne <cbbrowne@acm.org>
2003-10-14 01:43 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-10-13 19:10 ` Sean Chittenden <sean@chittenden.org>
2003-10-14 00:11 ` Re: go for a script! / ex: PostgreSQL vs. MySQL Tom Lane <tgl@sss.pgh.pa.us>
2003-10-13 15:37 ` Vivek Khera <khera@kcilink.com>
2003-10-14 01:29 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2003-10-12 20:30 ` Josh Berkus <josh@agliodbs.com>
2003-10-13 05:01 ` pginfo <pginfo@t1.unisoftbg.com>
2003-10-10 12:40 Re: go for a script! / ex: PostgreSQL vs. MySQL Nick Barr <nicky@chuckie.co.uk>
2003-10-12 22:47 Re: go for a script! / ex: PostgreSQL vs. MySQL Ivan Voras <ivoras@fer.hr>
2003-10-14 17:09 ` Josh Berkus <josh@agliodbs.com>
2003-10-14 18:15 ` Re: go for a script! / ex: PostgreSQL vs. MySQL Tom Lane <tgl@sss.pgh.pa.us>
2003-10-14 18:42 ` Sean Chittenden <sean@chittenden.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox