Re: [PERFORM] Extreme high load averages
On Sunday 06 Jul 2003 5:54 am, Martin Foster wrote: > The only time that I have ever seen load averages of 30 or more under > OpenBSD is when one of my scripts goes wild.However, I can say that > I am also seeing these load averages under PostgreSQL 7.3.2 after a > migration to it from MySQL. [snip] > However, the system > handled 250 concurrent users without a singular problem, while under > Postgres with new scripts using functions, referential integrity, > transactions and lighter code, the system starts to buckle at even less > then 70 users. [snip] > PIII 1Ghz, 1GB > SDRAM, 2 IDE 20GB drives. > > I have changed settings to take advantage of the memory. So the > following settings are of interest: > shared_buffers = 16384 > wal_buffers = 256 > sort_mem = 16384 > vacuum_mem = 32768 You do know that sort_mem is in kB per sort (not per connection, but per sort being done by a connection). That's 16MB per sort you've allowed in main memory, or for 70 concurrent sorts up to 1.1GB of memory allocated to sorting. You're not going into swap by any chance? Might want to try halving shared_buffers too and see what happens. I don't know the *BSDs myself, but do you have the equivalent of iostat/vmstat output you could get for us? Also a snapshot of "top" output? People are going to want to see: - overall memory usage (free/buffers/cache/swap) - memory usage per process - disk activity (blocks in/out) >From that lot, someone will be able to point towards the issue, I'm sure. -- Richard Huxton ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Extreme high load averages
On 5 Jul 2003 at 22:54, Martin Foster wrote: > What I would like to know is. Why? The kernel has been compiled to > handle the number of concurrent connections, the server may not be the > best, but it should be able to handle the requests: PIII 1Ghz, 1GB > SDRAM, 2 IDE 20GB drives. > > I have changed settings to take advantage of the memory. So the > following settings are of interest: > shared_buffers = 16384 > wal_buffers = 256 > sort_mem = 16384 > vacuum_mem = 32768 As somebody else has already pointed out, your sort_mem is bit too high than required. Try lowering it. Secondly did you tune effective_cache_size? HTH Bye Shridhar -- Power, n.: The only narcotic regulated by the SEC instead of the FDA. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Extreme high load averages
Shridhar Daithankar wrote: On 5 Jul 2003 at 22:54, Martin Foster wrote: What I would like to know is. Why? The kernel has been compiled to handle the number of concurrent connections, the server may not be the best, but it should be able to handle the requests: PIII 1Ghz, 1GB SDRAM, 2 IDE 20GB drives. I have changed settings to take advantage of the memory. So the following settings are of interest: shared_buffers = 16384 wal_buffers = 256 sort_mem = 16384 vacuum_mem = 32768 As somebody else has already pointed out, your sort_mem is bit too high than required. Try lowering it. Secondly did you tune effective_cache_size? HTH Bye Shridhar -- Power, n.: The only narcotic regulated by the SEC instead of the FDA. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly I dropped the size of the sort_mem down to 8 megs. Since I am not swapping to cache at all this should not post much of a problem at that value. effective_cache_size seems interesting, though the description is somewhat lacking. Is this related to the swap partition and how much of it will be used by PostgreSQL? If I am correct, this should be fairly low? Martin Foster Creator/Designer Ethereal Realms [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Extreme high load averages
Richard Huxton wrote: On Sunday 06 Jul 2003 5:54 am, Martin Foster wrote: The only time that I have ever seen load averages of 30 or more under OpenBSD is when one of my scripts goes wild.However, I can say that I am also seeing these load averages under PostgreSQL 7.3.2 after a migration to it from MySQL. [snip] However, the system handled 250 concurrent users without a singular problem, while under Postgres with new scripts using functions, referential integrity, transactions and lighter code, the system starts to buckle at even less then 70 users. [snip] PIII 1Ghz, 1GB SDRAM, 2 IDE 20GB drives. I have changed settings to take advantage of the memory. So the following settings are of interest: shared_buffers = 16384 wal_buffers = 256 sort_mem = 16384 vacuum_mem = 32768 You do know that sort_mem is in kB per sort (not per connection, but per sort being done by a connection). That's 16MB per sort you've allowed in main memory, or for 70 concurrent sorts up to 1.1GB of memory allocated to sorting. You're not going into swap by any chance? Might want to try halving shared_buffers too and see what happens. I don't know the *BSDs myself, but do you have the equivalent of iostat/vmstat output you could get for us? Also a snapshot of "top" output? People are going to want to see: - overall memory usage (free/buffers/cache/swap) - memory usage per process - disk activity (blocks in/out) From that lot, someone will be able to point towards the issue, I'm sure. Actually, no I did not. Which is probably why it was as high as it is. When looking at the PostgreSQL Hardware Performance Tuning page, it seems to imply that you should calculate based on RAM to give it an appropriate value. http://www.postgresql.org/docs/aw_pgsql_book/hw_performance/node8.html I dropped that value, and will see if that helps. The thing is, the system always indicated plenty of memory available. Even when at a 30 load level the free memory was still roughly 170MB. Tomorrow will be a good gage to see if the changes will actually help matters.And if they do not, I will include vmstat, iostat, and top as requested. Thanks! Martin Foster Creator/Designer Ethereal Realms [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Extreme high load averages
On Sunday 06 July 2003 15:56, Martin Foster wrote: > effective_cache_size seems interesting, though the description is > somewhat lacking. Is this related to the swap partition and how much of > it will be used by PostgreSQL? If I am correct, this should be fairly > low? Martin Foster It gives hint to psotgresql how much file system cache is available in the system. You have 1GB memory and your application requirement does not exceed 400MB. So OS can use roughly 600MB for file system cache. In that case you can set this parameter to 400MB cache to leave room for other application in FS cache. IIRC, BSD needs sysctl tuning to make more memory available for FS cache other wise they max out at 300MB. Roughly this setting should be (total memory -application requirement)*(0.7/0.8) I guess that high kernel load you are seeing due to increased interaction between postgresql and OS when data is swapped to/fro in shared memory. If OS cache does well, postgresql should reduce this interaction as well. BTW, since you have IDE disks, heavy disk activity can eat CPU as well. Is your disk bandwidth totally maxed out? Check with vmstat or whatever equivalent you have on BSD. Shridhar ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] Another POC initdb patch
Hi all,
In addition to Tom's patch, this patch asks tuning parameters right away,
while doing initdb. I have also changed the notice displayed after initdb is
done.
Just an attempt to make defaults user friendly. I would also like to add other
paramters to this approach, like fsync and random_page_cost but first I
thought others should have look at these.
And one more thing, can we get all the parameters in postgresql.conf to follow
similar units? Some settings aer in 8KB pages, some in bytes etc. Can we haev
all of them to follow say MB or KB?
I tried but guc.h and guc.c were bit too much to be gulped at one go. I will
try again.
Shridhar
*** postgresql.conf.sample.orig Sun Jul 6 15:51:02 2003
--- postgresql.conf.sample Sun Jul 6 17:17:23 2003
***
*** 57,63
# -
# Memory
! #shared_buffers = 64 # min 16, at least max_connections*2, 8KB each
#sort_mem = 1024 # min 64, size in KB
#vacuum_mem = 8192 # min 1024, size in KB
--- 57,64
# -
# Memory
! # min 16, at least max_connections*2, 8KB each
! #shared_buffers
#sort_mem = 1024 # min 64, size in KB
#vacuum_mem = 8192 # min 1024, size in KB
***
*** 105,111
# Planner Cost Constants
! #effective_cache_size = 1000 # typically 8KB each
#random_page_cost = 4 # units are one sequential page fetch cost
#cpu_tuple_cost = 0.01 # (same)
#cpu_index_tuple_cost = 0.001 # (same)
--- 106,113
# Planner Cost Constants
! # typically 8KB each
! #effective_cache_size
#random_page_cost = 4 # units are one sequential page fetch cost
#cpu_tuple_cost = 0.01 # (same)
#cpu_index_tuple_cost = 0.001 # (same)
*** initdb.orig Sun Jul 6 15:51:22 2003
--- initdb Sun Jul 6 19:34:24 2003
***
*** 71,76
--- 71,133
echo "$ret"
}
+ #Set the values to default
+ new_shared_buffers=" 64"
+ new_effective_cache=" 1000"
+
+ #Get some good enough values for configuration
+ pg_getDefault_Config(){
+ echo
+ echo
+ echo " Now I am going to ask you some question regarding your system. "
+ echo " These will help you in getting decent startup performance. "
+ echo " The answers you provide here would be reflected in generated "
+ echo " postgresql config file."
+ echo
+ read -p " Press any key to continue. " wvar
+
+ echo
+ echo "Setting parameter: shared_buffers"
+ echo " Unit: 8KB"
+ echo " Type: Integer"
+ echo
+ echo " This parameter sets how much shared memory postgresql will use."
+ echo " Value of this parameter depends upon size of dataset handled at"
+ echo " a time and available memory."
+ echo
+ echo " This parameter is limited by maximum shared memory setting of the"
+ echo " system. You might need to tune your kernel for your requirement."
+ echo " Please refer to Administrator's guide for more details."
+ echo
+ echo " WARNING: Setting this parameter too high can cause loss of performance"
+ echo " or postgresql might not start at all. Please adhere to guideline shown "
+ echo " and apply your own judgement."
+ echo
+ echo " Description MemoryValue"
+ echo " Workstation or test database 4MB 512 "
+ echo " Medium size database and 256-512 MB RAM 16-32MB 2048-4096"
+ echo " Large database and >1GB memory 64-256MB 8192-32768"
+ echo
+ read -p "Enter value of shared_buffers: " new_shared_buffers
+
+ echo
+ echo "Setting paramter: effective_cache_size"
+ echo " Unit: 8KB"
+ echo " Type: Integer"
+ echo
+
+ echo " Postgresql will perform better if it knows how much file cache OS has."
+ echo " This parameters gives hint to postgresql about the amount of file"
+ echo " system cache."
+ echo
+ echo " There is nothing high or low about this parameter. It should be set to"
+ echo " the correct value. Only change required is to account for memory"
+ echo " requirement of other applications running on same machie. Being bit"
+ echo " conservative on this parameter is recommended."
+ echo
+ read -p "Enter value of effective_cache_size: " new_effective_cache
+
+ }
CMDNAME=`basename $0`
***
*** 583,597
$ECHO_N "creating configuration files... "$ECHO_C
cp "$PG_HBA_SAMPLE" "$PGDATA"/pg_hba.conf || exit_nicely
cp "$PG_IDENT_SAMPLE" "$PGDATA"/pg_ident.conf || exit_nicely
(
trigger="# These settings are initialized by initdb -- they may be changed"
! sed -n "1,/$trigger/p" "$POSTGRESQL_CONF_SAMPLE"
for cat in MESSAGES MONETARY NUMERIC TIME; do
! echo "LC_$cat = '`pg_getlocale $cat`'"
done
! sed -n "1,/$trigger/!p" "$POSTGRESQL_CONF_SAMPLE"
) > "$PGDATA"/postgresql.conf || exit_nicely
chmod 0600 "$PGDATA"/pg_hba.conf "$PGDATA"/pg_ident.conf \
--- 640,660
$ECHO_N "creating configuration files... "$ECHO_C
+ #Get the database tuning options
+ pg_getDefault_Config
+
cp "$PG_HBA_SAMPLE" "$PGDATA"/pg_hba.conf || exit_
Re: [PERFORM] Extreme high load averages
Martin Foster <[EMAIL PROTECTED]> writes: >> The only time that I have ever seen load averages of 30 or more under >> OpenBSD is when one of my scripts goes wild. Note also that "high load average" is not per se an indication that anything is wrong. In Postgres, if you have thirty queries waiting for disk I/O, that's thirty processes --- so if that's the average state then the kernel will report a load average of thirty. While I'm no MySQL expert, I believe that the equivalent condition in MySQL would be thirty threads blocked for I/O within one process. Depending on how your kernel is written, that might show as a load average of one ... but the difference is completely illusory, because what counts is the number of disk I/Os in flight, and that's the same. You didn't say whether you were seeing any real performance problems, like slow queries or performance dropping when query load rises, but that is the aspect to concentrate on. I concur with the nearby recommendations to drop your resource settings. The thing you have to keep in mind about Postgres is that it likes to have a lot of physical RAM available for kernel disk buffers (disk cache). In a correctly tuned system that's been up for any length of time, "free memory" should be nearly nada, and the amount of RAM used for disk buffers should be sizable (50% or more of RAM would be good IMHO). regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] [PATCHES] Another POC initdb patch
Shridhar Daithankar <[EMAIL PROTECTED]> writes: > In addition to Tom's patch, this patch asks tuning parameters right away, > while doing initdb. Sorry, there is zero chance of putting any interactivity into initdb. Most RPM installations run it from the RPM install script and would be unable to cope with this. I disagree with the concept of expecting someone to supply useful values at install time anyway, since a newbie is the *least* likely to have any idea what to say at that time. Heck, the experts can hardly agree on what to use ... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] Extreme high load averages
Shridhar Daithankar wrote: It gives hint to psotgresql how much file system cache is available in the system. You have 1GB memory and your application requirement does not exceed 400MB. So OS can use roughly 600MB for file system cache. In that case you can set this parameter to 400MB cache to leave room for other application in FS cache. IIRC, BSD needs sysctl tuning to make more memory available for FS cache other wise they max out at 300MB. Roughly this setting should be (total memory -application requirement)*(0.7/0.8) I guess that high kernel load you are seeing due to increased interaction between postgresql and OS when data is swapped to/fro in shared memory. If OS cache does well, postgresql should reduce this interaction as well. BTW, since you have IDE disks, heavy disk activity can eat CPU as well. Is your disk bandwidth totally maxed out? Check with vmstat or whatever equivalent you have on BSD. Shridhar ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org I changed the value of effective_cache_size seems interesting to 512. The database restarted without any problems and load averages seem to be a bit lower as a result. Since people have been asking for it, I added in most of the stat command outputs that I could think of. All located below my signature block, this will show you what roughly 127 client connections with Postgre will generate. The numbers are a lot nicer to see then a 30 load level. Note, that the high number of connections is a side effect of connection pooling under Apache using Apache::DBI. This means that for every client on the http server there is a connection to Postgres even if the connection is idle. The above may be a factor of performance as well. As I had noticed that with an idle child setting being too high, that server would show very high load averages as well. Probably an indication that the system is continually forking new children trying to just keep the idle child count at the right level. Martin Foster Creator/Designer Ethereal Realms [EMAIL PROTECTED] vmstat: 2:09PM up 16:45, 1 user, load averages: 0.36, 0.30, 0.35 vmstat: procs memory r b wavmfre 1 0 0 234036 687548 page flt re pi po fr sr 621 0 0 0 0 0 faults cpu insy cs us sy id 364 396 88 19 1 79 iostat: ttywd0 wd1 cpu tin tout KB/t t/s MB/s KB/t t/s MB/s us ni sy in id 0 1023 4.53 1 0.01 9.72 11 0.10 19 0 1 0 79 pstat -s: Device 512-blocks UsedAvail Capacity Priority swap_device41942880 4194288 0%0 top header: load averages: 0.31, 0.35, 0.42 147 processes: 2 running, 145 idle CPU states: 32.9% user, 0.0% nice, 0.9% system, 0.0% interrupt, 66.2% idle Memory: Real: 263M/377M act/tot Free: 630M Swap: 0K/2048M used/tot ps -uax: USER PID %CPU %MEM VSZ RSS TT STAT STARTED TIME COMMAND postgres 1561 0.0 0.5 2120 4812 p0 I 1:48PM0:00.10 /usr/local/bin/postmaster (postgres) postgres 9935 0.0 2.8 3832 29744 p0 I 1:48PM0:00.74 postmaster: ethereal ethereal 192.168.1.6 idle in tra postgres 7436 0.0 0.6 3640 6636 p0 S 1:48PM0:00.92 postmaster: ethereal ethereal 192.168.1.6 idle in tra postgres 18814 0.0 7.0 3876 72904 p0 I 1:48PM0:04.53 postmaster: ethereal ethereal 192.168.1.6 idle in tra postgres 15346 0.0 4.1 3820 42468 p0 I 1:48PM0:00.93 postmaster: ethereal ethereal 192.168.1.6 idle in tra postgres 13621 0.0 6.9 3832 71824 p0 I 1:48PM0:02.66 postmaster: ethereal ethereal 192.168.1.6 idle in tra postgres 20916 0.0 4.7 3812 49164 p0 I 1:48PM0:00.59 postmaster: ethereal ethereal 192.168.1.6 idle in tra postgres 21684 0.0 2.2 3688 23356 p0 S 1:48PM0:01.27 postmaster: ethereal ethereal 192.168.1.6 idle in tra postgres 19472 0.0 6.9 3824 72452 p0 I 1:48PM0:02.61 postmaster: ethereal ethereal 192.168.1.6 idle in tra postgres 27361 0.0 0.7 3664 6976 p0 S 1:48PM0:00.91 postmaster: ethereal ethereal 192.168.1.6 idle in tra postgres 28925 0.0 2.8 3840 29528 p0 I 1:48PM0:00.46 postmaster: ethereal ethereal 192.168.1.6 idle in tra postgres 12790 0.0 2.7 3800 28080 p0 I 1:48PM0:01.11 postmaster: ethereal ethereal 192.168.1.6 idle in tra postgres 13957 0.0 6.8 3820 71476 p0 I 1:48PM0:02.26 postmaster: ethereal ethereal 192.168.1.6 idle in tra postgres 29129 0.0 2.8 3828 29096 p0 I 1:48PM0:01.50 postmaster: ethereal ethereal 192.168.1.6 idle in tra postgres 24279 0.0 2.7 3824 27992 p0 S 1:48PM0:01.08 postmaster: ethereal ethereal 192.168.1.6 idle in tra postgres 20382 0.0 0.6 3640 6748 p0 S 1:48PM0:00.91 postmaster: ethereal ethereal 192.168.
Re: [PERFORM] Extreme high load averages
Tom Lane wrote: Martin Foster <[EMAIL PROTECTED]> writes: The only time that I have ever seen load averages of 30 or more under OpenBSD is when one of my scripts goes wild. Note also that "high load average" is not per se an indication that anything is wrong. In Postgres, if you have thirty queries waiting for disk I/O, that's thirty processes --- so if that's the average state then the kernel will report a load average of thirty. While I'm no MySQL expert, I believe that the equivalent condition in MySQL would be thirty threads blocked for I/O within one process. Depending on how your kernel is written, that might show as a load average of one ... but the difference is completely illusory, because what counts is the number of disk I/Os in flight, and that's the same. You didn't say whether you were seeing any real performance problems, like slow queries or performance dropping when query load rises, but that is the aspect to concentrate on. I concur with the nearby recommendations to drop your resource settings. The thing you have to keep in mind about Postgres is that it likes to have a lot of physical RAM available for kernel disk buffers (disk cache). In a correctly tuned system that's been up for any length of time, "free memory" should be nearly nada, and the amount of RAM used for disk buffers should be sizable (50% or more of RAM would be good IMHO). regards, tom lane Under a circumstance where we have 250 concurrent users, MySQL would report an uptime of 0.5 sometimes 0.8 depending on the tasks being performed. This would translate to wait times averaging less then a second, and under a heavy resource script 4 seconds.That system had less RAM however. This new system when showing a load average of 30, produced wait times of 12 seconds averages and about 30 seconds for the heavy resource script. The web server itself showed a load average of 0.5 showing that it was not heavy client interaction slowing things down. So there is a very noticeable loss of performance when the system skyrockets like that. All of the load as indicated by top is at user level, and not swap is even touched. This may help show why I was slightly concerned. Martin Foster Creator/Designer Ethereal Realms [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Moving postgresql.conf tunables into 2003...
> > Brian Suggests: > > I'm curious how many of the configuration values can be determined > > automatically, or with the help of some script. It seem like there > > could be some perl script in contrib that could help figure this out. > > Possibly you are asked a bunch of questions and then the values are > > computed based on that. Something like: > > This would be great! Wanna be in charge of it? > Is there a to-do list for this kind of stuff? Maybe there could be a "help wanted" sign on the website. Seems like there are lot's of good ideas that fly around here but never get followed up on. Additionally, I have an increasingly large production database that I would be willing to do some test-cases on. I don't really know how to do it though... If someone where able to give instructions I could run tests on three different platforms. Matthew Nuzum | Makers of "Elite Content Management System" www.followers.net | View samples of Elite CMS in action [EMAIL PROTECTED] | http://www.followers.net/portfolio/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Moving postgresql.conf tunables into 2003...
> > This sort of narrative belongs in the SGML docs, not in a CONF file. > > In fact, one could argue that we should take *all* commentary out of > > the CONF file in order to force people to read the docs. > > The SGML docs aren't in the DBA's face and are way out of the way for > DBAs rolling out a new system or who are tuning the system. SGML == > Developer, conf == DBA. > > > Database performance tuning will always be a "black art," as it > > necessitates a broad knowledge of PostgreSQL, OS architecture, and > > computer hardware. So I doubt that we can post docs that would > > allow any 10% time DBA to make PostgreSQL "fly", but hopefully over > > the next year we can make enough knowledge public to allow anyone to > > make PostgreSQL "sprint". > > I'm highly resistant to/disappointed in this attitude and firmly > believe that there are well understood algorithms that DBAs use to > diagnose and solve performance problems. It's only a black art > because it hasn't been documented. Performance tuning isn't voodoo, > it's adjusting constraints to align with the execution of applications > and we know what the applications do, therefore the database can mold > to the applications' needs. I agree. We often seem to forget simple lessons in human nature. Expecting someone to spend 20 extra seconds to do something is often too much. In many cases, the only "manual" that a person will see is the .conf files. At the very least, if there is good documentation for these parameters, maybe the conf file should provide a link to this info. About the documentation... The few times I've tried reading these sections of the docs it was like reading a dictionary. Bruce's book is a much better writing style because it starts out with a basic concept and then expands on it, sometimes several times until a thorough (but not exhaustive) example has been given. The exhaustive material in the docs is good when you know what you're looking for, and therefore is a critical piece of reference work. I don't want to belittle the authors of that material in any way. An illustration of this would be to compare the O'Reilly "... Nutshell" book series to something like the [fictitious] book "Learn PostgreSQL in 24 hours". To close this message, I would just like to add that one of the most successful open source projects of all time could be used as an example. The Apache httpd project is one of the few open source projects in wide spread use that holds more market share than all competing products combined. It uses a three phase (if not more) documentation level. The .conf file contains detailed instructions in an easy to read and not-to-jargon-ish structure. The docs provide detailed tutorials and papers that expand on configuration params in an easy to read format. Both of these refer to the thorough reference manual that breaks each possible option down into it's nitty gritty details so that a user can get more information if they so desire. Matthew Nuzum | Makers of "Elite Content Management System" www.followers.net | View samples of Elite CMS in action [EMAIL PROTECTED] | http://www.followers.net/portfolio/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Moving postgresql.conf tunables into 2003...
On Sun, 6 Jul 2003, Matthew Nuzum wrote: > At the very least, if there is good documentation for these parameters, > maybe the conf file should provide a link to this info. I believe that is what Josh is proposing: http://archives.postgresql.org/pgsql-performance/2003-07/msg00102.php > [Apache httpd] uses a three phase (if not more) documentation level. > The .conf file contains detailed instructions in an easy to read and > not-to-jargon-ish structure. The docs provide detailed tutorials and > papers that expand on configuration params in an easy to read format. > Both of these refer to the thorough reference manual that breaks each > possible option down into it's nitty gritty details so that a user can > get more information if they so desire. I agree that Apache's approach is primo. Often the .conf comments are enough to jog my memory about a directive I haven't used for a while. Or the comments are enough to let me know I don't need a directive, or that I need to go to the manual and read more. I appreciate that. michael ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Moving postgresql.conf tunables into 2003...
Michael Pohl wrote: On Sun, 6 Jul 2003, Matthew Nuzum wrote: At the very least, if there is good documentation for these parameters, maybe the conf file should provide a link to this info. I believe that is what Josh is proposing: http://archives.postgresql.org/pgsql-performance/2003-07/msg00102.php [Apache httpd] uses a three phase (if not more) documentation level. The .conf file contains detailed instructions in an easy to read and not-to-jargon-ish structure. The docs provide detailed tutorials and papers that expand on configuration params in an easy to read format. Both of these refer to the thorough reference manual that breaks each possible option down into it's nitty gritty details so that a user can get more information if they so desire. I agree that Apache's approach is primo. Often the .conf comments are enough to jog my memory about a directive I haven't used for a while. Or the comments are enough to let me know I don't need a directive, or that I need to go to the manual and read more. I appreciate that. michael One thing that may also help, is to include more sample .conf files. For example, you could include settings that would be commonly seen for decicated databases with generic specs and another with less resources and not dedicated for use with Postgres. This would allow users to see how certain setting changes will work. The default .conf is great if you want to setup a small test bed, but for a real life example chances are it won't exactly be what your looking for. Martin Foster Creator/Designer Ethereal Realms [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Extreme high load averages
Richard Huxton wrote:
I don't know the *BSDs myself, but do you have the equivalent of iostat/vmstat
output you could get for us? Also a snapshot of "top" output? People are
going to want to see:
- overall memory usage (free/buffers/cache/swap)
- memory usage per process
- disk activity (blocks in/out)
I changed a bit of the scripting code to cut down on the weight of a
query being run. This is the only thing in the entire system that
would cause scripts to run at high processor times for extended lengths.
With the corrections, postgres processes average more closely to < 1%
then before.
This is not stopping the system from getting high load averages.
Attached, is an example of the site running at 160 users with very slow
response rates (30 seconds for some scripts). According to top, and ps
nothing is taking up all that processing time.
The processor seems to be purposely sitting there twiddling it's thumbs.
Which leads me to believe that perhaps the nice levels have to be
changed on the server itself?And perhaps increase the file system
buffer to cache files in memory instead of always fetching/writing them?
Anyone more ideas?
Martin Foster
Creator/Designer Ethereal Realms
[EMAIL PROTECTED]
--- top ---
load averages: 5.00, 4.72, 3.75
21:45:56
134 processes: 6 running, 128 idle
CPU states: 91.7% user, 0.0% nice, 6.6% system, 1.6% interrupt, 0.2% idle
Memory: Real: 279M/390M act/tot Free: 617M Swap: 0K/2048M used/tot
PID USERNAME PRI NICE SIZE RES STATE WAIT TIMECPU COMMAND
23235 postgres 640 12M 95M run -0:02 9.42% postgres
5299 postgres 640 3872K 57M run -0:01 5.86% postgres
8933 postgres 640 3408K 55M run -0:01 5.47% postgres
16398 postgres 20 3776K 17M sleep netio0:02 0.05% postgres
14007 named 20 2528K 2572K sleep select 0:06 0.00% named
3684 postgres 20 2120K 4812K sleep select 0:07 0.00% postgres
23518 postgres 20 3664K 36M sleep netio0:03 0.00% postgres
571 postgres 20 3776K 51M sleep netio0:03 0.00% postgres
11159 postgres 20 3664K 35M sleep netio0:03 0.00% postgres
19184 postgres 20 3776K 16M sleep netio0:03 0.00% postgres
28931 postgres 20 3712K 16M sleep netio0:02 0.00% postgres
17523 postgres 20 3712K 14M sleep netio0:02 0.00% postgres
8272 postgres 20 3712K 14M sleep netio0:02 0.00% postgres
12034 postgres 20 3712K 14M sleep netio0:02 0.00% postgres
30825 postgres 20 3776K 17M sleep netio0:02 0.00% postgres
29173 postgres 20 3712K 15M sleep netio0:02 0.00% postgres
9472 postgres 20 3664K 34M sleep netio0:02 0.00% postgres
11542 postgres 20 3776K 16M sleep netio0:02 0.00% postgres
--- vmstat ---
procs memorypagedisks faults cpu
r b wavmfre flt re pi po fr sr wd0 wd1 insy cs us sy id
1 0 0 275352 642800 8173 0 0 0 0 0 18 21 534 853 243 50 5 45
--- iostat ---
ttywd0 wd1 cpu
tin tout KB/t t/s MB/s KB/t t/s MB/s us ni sy in id
0 74 13.00 18 0.23 10.08 21 0.21 50 0 4 1 45
--- pstat -s ---
Device 512-blocks UsedAvail Capacity Priority
swap_device41942880 4194288 0%0
--- dmesg ---
OpenBSD 3.3-stable (compile) #2: Sat Jul 5 15:17:30 MDT 2003
[EMAIL PROTECTED]:/usr/src/sys/arch/i386/compile
cpu0: Intel Pentium III (Coppermine) ("GenuineIntel" 686-class) 1 GHz
cpu0: FPU,V86,DE,PSE,TSC,MSR,PAE,MCE,CX8,SYS,MTRR,PGE,MCA,CMOV,PAT,PSE36,MMX,FXSR,SIMD
real mem = 1073250304 (1048096K)
avail mem = 992940032 (969668K)
using 4278 buffers containing 53764096 bytes (52504K) of memory
mainbus0 (root)
bios0 at mainbus0: AT/286+(a1) BIOS, date 07/20/01, BIOS32 rev. 0 @ 0xf0b20
apm0 at bios0: Power Management spec V1.2
apm0: AC on, battery charge unknown
pcibios0 at bios0: rev. 2.1 @ 0xf/0x1382
pcibios0: PCI IRQ Routing Table rev. 1.0 @ 0xf12d0/176 (9 entries)
pcibios0: PCI Interrupt Router at 000:04:0 ("VIA VT82C586 PCI-ISA" rev 0x00)
pcibios0: PCI bus #1 is the last bus
bios0: ROM list: 0xc/0x8000 0xc8000/0x1800
pci0 at mainbus0 bus 0: configuration mode 1 (no bios)
pchb0 at pci0 dev 0 function 0 "VIA VT82C691 Host-PCI" rev 0xc4
ppb0 at pci0 dev 1 function 0 "VIA VT82C598 PCI-AGP" rev 0x00
pci1 at ppb0 bus 1
vga1 at pci1 dev 0 function 0 "NVidia/SGS-Thomson Velocity128" rev 0x22
wsdisplay0 at vga1: console (80x25, vt100 emulation)
wsdisplay0: screen 1-5 added (80x25, vt100 emulation)
pcib0 at pci0 dev 4 function 0 "VIA VT82C686 PCI-ISA" rev 0x40
pciide0 at pci0 dev 4 function 1 "VIA VT82C571 IDE" rev 0x06: ATA100, channel 0
configured to compatibility, channel 1 configured to compatibility
wd0 a
