Re: [PERFORM] Swapping on Solaris
Kevin Schroeder wrote: It looks to me like you are using no (device or file) swap at all, and have 1.3G of real memory free, so could in fact give Postgres more of it :-) Indeed. If you DO run into trouble after giving Postgres more RAM, use the vmstat command. You can use this command like vmstat 10. (ignore the first line) Keep an eye on the pi and po parameters. (kilobytes paged in and out) HTH, Matt -- Matt Casters [EMAIL PROTECTED] i-Bridge bvba, http://www.kettle.be Fonteinstraat 70, 9400 Okegem, Belgium Phone +32 (0) 486/97.29.37 ---(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] Increasing RAM for more than 4 Gb. using postgresql
[EMAIL PROTECTED] wrote: I would like to upgrade both OS kernel and PGsql version , so in my opinion the best way to handle it is to *backup* the data in .tar Just remember if you're going from 7.3.2 = 7.4.x or 8.0 then you'll need to use pg_dump not just tar up the directories. If you do use tar, remember to backup *all* the directories. -- Richard Huxton Archonet Ltd ---(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] Increasing RAM for more than 4 Gb. using postgresql
You can *not* go from any major release to another major release using any kind of file backup. Please use pg_dump. Additionally there are known issues dumping and restoring from 7.3 - 7.4 if you use the default copy command. Use the pg_dump --inserts option. I would still tar the directory just in case you *have* to fall back to 7.3 for some reason (Better safe than sorry ) Dave Richard Huxton wrote: [EMAIL PROTECTED] wrote: I would like to upgrade both OS kernel and PGsql version , so in my opinion the best way to handle it is to *backup* the data in .tar Just remember if you're going from 7.3.2 = 7.4.x or 8.0 then you'll need to use pg_dump not just tar up the directories. If you do use tar, remember to backup *all* the directories. -- Richard Huxton Archonet Ltd ---(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 -- Dave Cramer http://www.postgresintl.com 519 939 0336 ICQ#14675561 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Swapping on Solaris
Mark Kirkwood wrote: Kevin Schroeder wrote: Ignoring the fact that the sort and vacuum numbers are really high, this is what Solaris shows me when running top: Memory: 2048M real, 1376M free, 491M swap in use, 2955M swap free Maybe check the swap usage with 'swap -l' which reports reliably if any (device or file) swap is actually used. I think Solaris 'top' does some strange accounting to calculate the 'swap in use' value (like including used memory). It looks to me like you are using no (device or file) swap at all, and have 1.3G of real memory free, so could in fact give Postgres more of it :-) I suspect that free memory is in fact being used for the file system cache. There were some changes in the meaning of free in Solaris 8 and 9. The memstat command gives a nice picture of memory usage on the system. I don't think memstat came with Solaris 8, but you can get it from solarisinternals.com. The Solaris Internals book is an excellent read as well; it explains all of this in gory detail. Note that files in /tmp are usually in a tmpfs file system. These files may be the usage of swap that you're seeing (as they will be paged out on an active system with some memory pressure) Finally, just as everyone suggests upgrading to newer postgresql releases, you probably want to get to a newer Solaris release. -- Alan ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Swapping on Solaris
po and pi are relatively low, but do pick up when there's an increase in activity. I am seeing a lot of minor faults, though. vmstat -S 5 reports [9:38am]# vmstat -S 5 procs memorypagedisk faults cpu r b w swap free si so pi po fr de sr s0 s1 s3 -- in sy cs us sy id 0 0 0 3235616 1414536 0 0 303 11 10 0 0 6 24 0 0 13 192 461 17 11 72 1 0 0 3004376 1274912 0 0 0 0 0 0 0 3 16 0 0 494 1147 441 52 25 23 494 in faults 1147 sy faults Generally faults are a bad thing. Is that the case here? Kevin - Original Message - From: Matt Casters [EMAIL PROTECTED] To: pgsql-performance@postgresql.org Sent: Wednesday, January 19, 2005 3:57 AM Subject: Re: [PERFORM] Swapping on Solaris Kevin Schroeder wrote: It looks to me like you are using no (device or file) swap at all, and have 1.3G of real memory free, so could in fact give Postgres more of it :-) Indeed. If you DO run into trouble after giving Postgres more RAM, use the vmstat command. You can use this command like vmstat 10. (ignore the first line) Keep an eye on the pi and po parameters. (kilobytes paged in and out) HTH, Matt -- Matt Casters [EMAIL PROTECTED] i-Bridge bvba, http://www.kettle.be Fonteinstraat 70, 9400 Okegem, Belgium Phone +32 (0) 486/97.29.37 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [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] Swapping on Solaris
I take that back. There actually is some paging going on. I ran sar -g 5 10 and when a request was made (totally about 10 DB queries) my pgout/s jumped to 5.8 and my ppgout/s jumped to 121.8. pgfree/s also jumped to 121.80. Kevin - Original Message - From: Matt Casters [EMAIL PROTECTED] To: pgsql-performance@postgresql.org Sent: Wednesday, January 19, 2005 3:57 AM Subject: Re: [PERFORM] Swapping on Solaris Kevin Schroeder wrote: It looks to me like you are using no (device or file) swap at all, and have 1.3G of real memory free, so could in fact give Postgres more of it :-) Indeed. If you DO run into trouble after giving Postgres more RAM, use the vmstat command. You can use this command like vmstat 10. (ignore the first line) Keep an eye on the pi and po parameters. (kilobytes paged in and out) HTH, Matt -- Matt Casters [EMAIL PROTECTED] i-Bridge bvba, http://www.kettle.be Fonteinstraat 70, 9400 Okegem, Belgium Phone +32 (0) 486/97.29.37 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Swapping on Solaris
I suspect that the memory is being used to cache files as well since the email boxes are using unix mailboxes, for the time being. With people checking their email sometimes once per minute I can see why Solaris would want to cache those files. Perhaps my question would be more appropriate to a Solaris mailing list since what I really want to do is get Solaris to simply allow PostgreSQL to use more RAM and reduce the amount of RAM used for file caching. I would have thought that Solaris gives some deference to a running application that's being swapped than for a file cache. Is there any way to set custom parameters on Solaris' file-caching behavior to allow PostgreSQL to use more physical RAM? I will also check out memstat. It's not on my system, but I'll get it from the site you noted. Thanks Kevin - Original Message - From: Alan Stange [EMAIL PROTECTED] Cc: Kevin Schroeder [EMAIL PROTECTED]; pgsql-performance@postgresql.org Sent: Wednesday, January 19, 2005 7:51 AM Subject: Re: [PERFORM] Swapping on Solaris Mark Kirkwood wrote: Kevin Schroeder wrote: Ignoring the fact that the sort and vacuum numbers are really high, this is what Solaris shows me when running top: Memory: 2048M real, 1376M free, 491M swap in use, 2955M swap free Maybe check the swap usage with 'swap -l' which reports reliably if any (device or file) swap is actually used. I think Solaris 'top' does some strange accounting to calculate the 'swap in use' value (like including used memory). It looks to me like you are using no (device or file) swap at all, and have 1.3G of real memory free, so could in fact give Postgres more of it :-) I suspect that free memory is in fact being used for the file system cache. There were some changes in the meaning of free in Solaris 8 and 9. The memstat command gives a nice picture of memory usage on the system. I don't think memstat came with Solaris 8, but you can get it from solarisinternals.com. The Solaris Internals book is an excellent read as well; it explains all of this in gory detail. Note that files in /tmp are usually in a tmpfs file system. These files may be the usage of swap that you're seeing (as they will be paged out on an active system with some memory pressure) Finally, just as everyone suggests upgrading to newer postgresql releases, you probably want to get to a newer Solaris release. -- Alan ---(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] Swapping on Solaris
/tmp doesn't seem to be much of a problem. I have about 1k worth of data in there and 72k in /var/tmp. Would turning swap off help in tuning the database in this regard? top is reporting that there's 1.25GB of RAM free on a 2GB system so, in my estimation, there's no need for PostgreSQL to be swapped unless that free memory is Solaris caching files in RAM. Kevin - Original Message - From: Greg Spiegelberg [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: Kevin Schroeder [EMAIL PROTECTED]; pgsql-performance@postgresql.org Sent: Wednesday, January 19, 2005 9:07 AM Subject: Re: [PERFORM] Swapping on Solaris Alan Stange wrote: Note that files in /tmp are usually in a tmpfs file system. These files may be the usage of swap that you're seeing (as they will be paged out on an active system with some memory pressure) You can do a couple things with /tmp. Create a separate file system for it so it will have zero impact on swap and use the noatime mount option. Alternatively, limit the size of /tmp using the mount option size=MBm replacing MB with the size you want it to be in MBytes. If your application uses /tmp heavily, be sure to put it on a speedy, local LUN. Finally, just as everyone suggests upgrading to newer postgresql releases, you probably want to get to a newer Solaris release. If you really want to avoid swapping I'd suggest tuning your database first with swap turned off and put it under a normal load while watching both top and vmstat. When you're happy with it, turn swap back on for those heavy load times and move on. Greg -- Greg Spiegelberg Product Development Manager Cranel, Incorporated. Phone: 614.318.4314 Fax: 614.431.8388 Email: [EMAIL PROTECTED] Technology. Integrity. Focus. ---(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] Swapping on Solaris
Kevin Schroeder wrote: I suspect that the memory is being used to cache files as well since the email boxes are using unix mailboxes, for the time being. With people checking their email sometimes once per minute I can see why Solaris would want to cache those files. Perhaps my question would be more appropriate to a Solaris mailing list since what I really want to do is get Solaris to simply allow PostgreSQL to use more RAM and reduce the amount of RAM used for file caching. I would have thought that Solaris gives some deference to a running application that's being swapped than for a file cache. Is there any way to set custom parameters on Solaris' file-caching behavior to allow PostgreSQL to use more physical RAM? Your explanation doesn't sound quite correct. If postgresql malloc()'s some memory and uses it, the file cache will be reduced in size and the memory given to postgresql. But if postgresql doesn't ask for or use the memory, then solaris is going to use it for something else. There's nothing in Solaris that doesn't allow postgresql to use more RAM. -- Alan ---(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] Swapping on Solaris
Alan Stange wrote: Note that files in /tmp are usually in a tmpfs file system. These files may be the usage of swap that you're seeing (as they will be paged out on an active system with some memory pressure) You can do a couple things with /tmp. Create a separate file system for it so it will have zero impact on swap and use the noatime mount option. Alternatively, limit the size of /tmp using the mount option size=MBm replacing MB with the size you want it to be in MBytes. If your application uses /tmp heavily, be sure to put it on a speedy, local LUN. Finally, just as everyone suggests upgrading to newer postgresql releases, you probably want to get to a newer Solaris release. If you really want to avoid swapping I'd suggest tuning your database first with swap turned off and put it under a normal load while watching both top and vmstat. When you're happy with it, turn swap back on for those heavy load times and move on. Greg -- Greg Spiegelberg Product Development Manager Cranel, Incorporated. Phone: 614.318.4314 Fax: 614.431.8388 Email: [EMAIL PROTECTED] Technology. Integrity. Focus. ---(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] Swapping on Solaris
Maybe, I'm just seeing a problem where none exists. I ran sar -w 3 100 and I actually did not see any swap activity despite the fact that I've got 500+MB of swap file being used. Kevin - Original Message - From: Alan Stange [EMAIL PROTECTED] To: Kevin Schroeder [EMAIL PROTECTED] Cc: pgsql-performance@postgresql.org Sent: Wednesday, January 19, 2005 9:42 AM Subject: Re: [PERFORM] Swapping on Solaris Kevin Schroeder wrote: I take that back. There actually is some paging going on. I ran sar -g 5 10 and when a request was made (totally about 10 DB queries) my pgout/s jumped to 5.8 and my ppgout/s jumped to 121.8. pgfree/s also jumped to 121.80. I'm fairly sure that the pi and po numbers include file IO in Solaris, because of the unified VM and file systems. -- Alan ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Disk configuration
The primary goal is to reduce the number of seeks a disk or array has to perform. Serial write throughput is much higher than random write throughput. If you are performing very high volume throughput on a server that is doing multiple things, then it maybe advisable to have one partition for OS, one for postgresql binaries, one for xlog and one for table data (or multiple if you are PG8.0). This is the ultimate configuration, but most people don't require this level of seperation. If you do need this level of seperation, also bare in mind that table data writes are more likely to be random writes so you want an array that can sustain a high levels of IO/sec, so RAID 10 with 6 or more drives is ideal. If you want fault tolerance, then RAID 1 for OS and postgresql binaries is a minimum, and I believe that xlog can also go on a RAID 1 unless you need more MB/sec. Ultimately you will need to benchmark any configuration you build in order to determine if it's successfull and meets your needs. This of course sucks, because you don't want to buy too much because it's a waste of $$s. What I can tell you is my own experience which is a database running with xlog, software and OS on a RAID 1, with Data partition running on 3 disk RAID 5 with a database of about 3 million rows total gets an insert speed of about 200 rows/sec on an average size table using a compaq proliant ML370 Dual Pentium 933 w/2G RAM. Most of the DB is in RAM, so read times are very good with most queries returning sub second. Hope this helps at least a little Alex Turner NetEconomist On Wed, 19 Jan 2005 09:03:44 +1100, Benjamin Wragg [EMAIL PROTECTED] wrote: I just wanted to bounce off the list the best way to configure disks for a postgresql server. My gut feeling is as follows: Keep the OS and postgresql install on seperate disks to the postgresql /data directory? Is a single hard disk drive acceptable for the OS and postgresql program, or will this create a bottle neck? Would a multi disk array be more appropriate? Cheers, Benjamin Wragg -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.7.0 - Release Date: 17/01/2005 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Swapping on Solaris
Kevin Schroeder wrote: I take that back. There actually is some paging going on. I ran sar -g 5 10 and when a request was made (totally about 10 DB queries) my pgout/s jumped to 5.8 and my ppgout/s jumped to 121.8. pgfree/s also jumped to 121.80. I'm fairly sure that the pi and po numbers include file IO in Solaris, because of the unified VM and file systems. -- Alan ---(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] Swapping on Solaris
I may be asking the question the wrong way, but when I start up PostgreSQL swap is what gets used the most of. I've got 1282MB free RAM right now and and 515MB swap in use. Granted, swap file usage probably wouldn't be zero, but I would guess that it should be a lot lower so something must be keeping PostgreSQL from using the free RAM that my system is reporting. For example, one of my postgres processes is 201M in size but on 72M is resident in RAM. That extra 130M is available in RAM, according to top, but postgres isn't using it. Kevin - Original Message - From: Alan Stange [EMAIL PROTECTED] To: Kevin Schroeder [EMAIL PROTECTED] Cc: pgsql-performance@postgresql.org Sent: Wednesday, January 19, 2005 9:30 AM Subject: Re: [PERFORM] Swapping on Solaris Kevin Schroeder wrote: I suspect that the memory is being used to cache files as well since the email boxes are using unix mailboxes, for the time being. With people checking their email sometimes once per minute I can see why Solaris would want to cache those files. Perhaps my question would be more appropriate to a Solaris mailing list since what I really want to do is get Solaris to simply allow PostgreSQL to use more RAM and reduce the amount of RAM used for file caching. I would have thought that Solaris gives some deference to a running application that's being swapped than for a file cache. Is there any way to set custom parameters on Solaris' file-caching behavior to allow PostgreSQL to use more physical RAM? Your explanation doesn't sound quite correct. If postgresql malloc()'s some memory and uses it, the file cache will be reduced in size and the memory given to postgresql. But if postgresql doesn't ask for or use the memory, then solaris is going to use it for something else. There's nothing in Solaris that doesn't allow postgresql to use more RAM. -- Alan ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Swapping on Solaris
On Jan 19, 2005, at 10:42 AM, Alan Stange wrote: Kevin Schroeder wrote: I take that back. There actually is some paging going on. I ran sar -g 5 10 and when a request was made (totally about 10 DB queries) my pgout/s jumped to 5.8 and my ppgout/s jumped to 121.8. pgfree/s also jumped to 121.80. I'm fairly sure that the pi and po numbers include file IO in Solaris, because of the unified VM and file systems. Curiously, what are your shared_buffers and sort_mem set too? Perhaps they are too high? -- Jeff Trout [EMAIL PROTECTED] http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(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] Swapping on Solaris
I think it's probably just reserving them. I can't think of anything else. Also, when I run swap activity with sar I don't see any activity, which also points to reserved swap space, not used swap space. swap -s reports total: 358336k bytes allocated + 181144k reserved = 539480k used, 2988840k available Kevin - Original Message - From: Alan Stange [EMAIL PROTECTED] To: Kevin Schroeder [EMAIL PROTECTED] Cc: pgsql-performance@postgresql.org Sent: Wednesday, January 19, 2005 11:04 AM Subject: Re: [PERFORM] Swapping on Solaris Kevin Schroeder wrote: I may be asking the question the wrong way, but when I start up PostgreSQL swap is what gets used the most of. I've got 1282MB free RAM right now and and 515MB swap in use. Granted, swap file usage probably wouldn't be zero, but I would guess that it should be a lot lower so something must be keeping PostgreSQL from using the free RAM that my system is reporting. For example, one of my postgres processes is 201M in size but on 72M is resident in RAM. That extra 130M is available in RAM, according to top, but postgres isn't using it. The test you're doing doesn't measure what you think you're measuring. First, what else is running on the machine?Note that some shared memory allocations do reserve backing pages in swap, even though the pages aren't currently in use. Perhaps this is what you're measuring? swap -s has better numbers than top. You'd be better by trying a reboot then starting pgsql and seeing what memory is used. Just because you start a process and see the swap number increase doesn't mean that the new process is in swap. It means some anonymous pages had to be evicted to swap to make room for the new process or some pages had to be reserved in swap for future use. Typically a new process won't be paged out unless something else is causing enormous memory pressure... -- Alan ---(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] Swapping on Solaris
Kevin Schroeder wrote: I may be asking the question the wrong way, but when I start up PostgreSQL swap is what gets used the most of. I've got 1282MB free RAM right now and and 515MB swap in use. Granted, swap file usage probably wouldn't be zero, but I would guess that it should be a lot lower so something must be keeping PostgreSQL from using the free RAM that my system is reporting. For example, one of my postgres processes is 201M in size but on 72M is resident in RAM. That extra 130M is available in RAM, according to top, but postgres isn't using it. The test you're doing doesn't measure what you think you're measuring. First, what else is running on the machine?Note that some shared memory allocations do reserve backing pages in swap, even though the pages aren't currently in use. Perhaps this is what you're measuring? swap -s has better numbers than top. You'd be better by trying a reboot then starting pgsql and seeing what memory is used. Just because you start a process and see the swap number increase doesn't mean that the new process is in swap. It means some anonymous pages had to be evicted to swap to make room for the new process or some pages had to be reserved in swap for future use. Typically a new process won't be paged out unless something else is causing enormous memory pressure... -- Alan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Swapping on Solaris
On Jan 19, 2005, at 10:40 AM, Kevin Schroeder wrote: I may be asking the question the wrong way, but when I start up PostgreSQL swap is what gets used the most of. I've got 1282MB free RAM right now and and 515MB swap in use. Granted, swap file usage probably wouldn't be zero, but I would guess that it should be a lot lower so something must be keeping PostgreSQL from using the free RAM that my system is reporting. For example, one of my postgres processes is 201M in size but on 72M is resident in RAM. That extra 130M is available in RAM, according to top, but postgres isn't using it. Can you please give us your exact shared_buffer and sort_mem settings? This will help greatly. As a general thing, we say don't use more than 10k shared bufs unless you have done testing and enjoy a benefit. Managing all those buffers isn't free. I'm also not sure how Solaris reports shared memory usage for apps... a lot of that could be shared mem. Can you watch say, vmstat 1 for a minute or two while PG is running and see if you're actually swapping? -- Jeff Trout [EMAIL PROTECTED] http://www.jefftrout.com/ http://www.stuarthamm.net/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Swapping on Solaris
This page may be of use: http://www.serverworldmagazine.com/monthly/2003/02/solaris.shtml From personal experience, for god's sake don't think Solaris' VM/swap implementation is easy - it's damn good, but it ain't easy! Matt Kevin Schroeder wrote: I think it's probably just reserving them. I can't think of anything else. Also, when I run swap activity with sar I don't see any activity, which also points to reserved swap space, not used swap space. swap -s reports total: 358336k bytes allocated + 181144k reserved = 539480k used, 2988840k available Kevin - Original Message - From: Alan Stange [EMAIL PROTECTED] To: Kevin Schroeder [EMAIL PROTECTED] Cc: pgsql-performance@postgresql.org Sent: Wednesday, January 19, 2005 11:04 AM Subject: Re: [PERFORM] Swapping on Solaris Kevin Schroeder wrote: I may be asking the question the wrong way, but when I start up PostgreSQL swap is what gets used the most of. I've got 1282MB free RAM right now and and 515MB swap in use. Granted, swap file usage probably wouldn't be zero, but I would guess that it should be a lot lower so something must be keeping PostgreSQL from using the free RAM that my system is reporting. For example, one of my postgres processes is 201M in size but on 72M is resident in RAM. That extra 130M is available in RAM, according to top, but postgres isn't using it. The test you're doing doesn't measure what you think you're measuring. First, what else is running on the machine?Note that some shared memory allocations do reserve backing pages in swap, even though the pages aren't currently in use. Perhaps this is what you're measuring? swap -s has better numbers than top. You'd be better by trying a reboot then starting pgsql and seeing what memory is used. Just because you start a process and see the swap number increase doesn't mean that the new process is in swap. It means some anonymous pages had to be evicted to swap to make room for the new process or some pages had to be reserved in swap for future use. Typically a new process won't be paged out unless something else is causing enormous memory pressure... -- Alan ---(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 ---(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] Swapping on Solaris
Well, easy it ain't and I believe it's good. One final question: When I run sar -w I get no swap activity, but the process switch column registers between 400 and 700 switches per second. Would that be in the normal range for a medium-use system? Thanks Kevin - Original Message - From: Matt Clark [EMAIL PROTECTED] To: Kevin Schroeder [EMAIL PROTECTED] Cc: pgsql-performance@postgresql.org Sent: Wednesday, January 19, 2005 1:01 PM Subject: Re: [PERFORM] Swapping on Solaris This page may be of use: http://www.serverworldmagazine.com/monthly/2003/02/solaris.shtml From personal experience, for god's sake don't think Solaris' VM/swap implementation is easy - it's damn good, but it ain't easy! Matt Kevin Schroeder wrote: I think it's probably just reserving them. I can't think of anything else. Also, when I run swap activity with sar I don't see any activity, which also points to reserved swap space, not used swap space. swap -s reports total: 358336k bytes allocated + 181144k reserved = 539480k used, 2988840k available Kevin - Original Message - From: Alan Stange [EMAIL PROTECTED] To: Kevin Schroeder [EMAIL PROTECTED] Cc: pgsql-performance@postgresql.org Sent: Wednesday, January 19, 2005 11:04 AM Subject: Re: [PERFORM] Swapping on Solaris Kevin Schroeder wrote: I may be asking the question the wrong way, but when I start up PostgreSQL swap is what gets used the most of. I've got 1282MB free RAM right now and and 515MB swap in use. Granted, swap file usage probably wouldn't be zero, but I would guess that it should be a lot lower so something must be keeping PostgreSQL from using the free RAM that my system is reporting. For example, one of my postgres processes is 201M in size but on 72M is resident in RAM. That extra 130M is available in RAM, according to top, but postgres isn't using it. The test you're doing doesn't measure what you think you're measuring. First, what else is running on the machine?Note that some shared memory allocations do reserve backing pages in swap, even though the pages aren't currently in use. Perhaps this is what you're measuring? swap -s has better numbers than top. You'd be better by trying a reboot then starting pgsql and seeing what memory is used. Just because you start a process and see the swap number increase doesn't mean that the new process is in swap. It means some anonymous pages had to be evicted to swap to make room for the new process or some pages had to be reserved in swap for future use. Typically a new process won't be paged out unless something else is causing enormous memory pressure... -- Alan ---(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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[PERFORM] areca raid controller
Hi, Has anyone had any experiance with any of the Areca SATA RAID controllers? I was looking at a 3ware onebut it won't fit in the 2U case we have so the sales guy recommended these. Cheers, Benjamin Wragg -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.7.0 - Release Date: 17/01/2005
Re: [PERFORM] Swapping on Solaris
On Wed, 2005-01-19 at 09:40 -0600, Kevin Schroeder wrote: I may be asking the question the wrong way, but when I start up PostgreSQL swap is what gets used the most of. I've got 1282MB free RAM right now and and 515MB swap in use. Granted, swap file usage probably wouldn't be zero, but I would guess that it should be a lot lower so something must be keeping PostgreSQL from using the free RAM that my system is reporting. For example, one of my postgres processes is 201M in size but on 72M is resident in RAM. That extra 130M is available in RAM, according to top, but postgres isn't using it. You probably need to look at the way Solaris memory allocation works. On Linux 2.6, my understanding is that if a process allocates memory, but doesn't actually use it, then the OS is smart enough to swap the overallocated portion out to swap. The effect of that is that the program stays happy because it has all the memory it thinks it needs, while the OS is happy because it conserves it valuable physical RAM for memory that is actually being used. If what I say is correct, you should actually observe very low swapping I/O rates on the system. Anyway, look at how the algorithms work if you are worried by what you see. But mostly, if the system is performing OK, then no need to worry - if your only measure of that is system performance data then you need to instrument your application better, so you can look at the data that really matters. -- Best Regards, Simon Riggs ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Disk configuration
Thanks. That sorts out all my questions regarding disk configuration. One more regarding RAID. Is RAID 1+0 and 0+1 essentially the same at a performance level? Thanks, Benjamin -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Alex Turner Sent: Thursday, 20 January 2005 2:53 AM To: Benjamin Wragg Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Disk configuration The primary goal is to reduce the number of seeks a disk or array has to perform. Serial write throughput is much higher than random write throughput. If you are performing very high volume throughput on a server that is doing multiple things, then it maybe advisable to have one partition for OS, one for postgresql binaries, one for xlog and one for table data (or multiple if you are PG8.0). This is the ultimate configuration, but most people don't require this level of seperation. If you do need this level of seperation, also bare in mind that table data writes are more likely to be random writes so you want an array that can sustain a high levels of IO/sec, so RAID 10 with 6 or more drives is ideal. If you want fault tolerance, then RAID 1 for OS and postgresql binaries is a minimum, and I believe that xlog can also go on a RAID 1 unless you need more MB/sec. Ultimately you will need to benchmark any configuration you build in order to determine if it's successfull and meets your needs. This of course sucks, because you don't want to buy too much because it's a waste of $$s. What I can tell you is my own experience which is a database running with xlog, software and OS on a RAID 1, with Data partition running on 3 disk RAID 5 with a database of about 3 million rows total gets an insert speed of about 200 rows/sec on an average size table using a compaq proliant ML370 Dual Pentium 933 w/2G RAM. Most of the DB is in RAM, so read times are very good with most queries returning sub second. Hope this helps at least a little Alex Turner NetEconomist On Wed, 19 Jan 2005 09:03:44 +1100, Benjamin Wragg [EMAIL PROTECTED] wrote: I just wanted to bounce off the list the best way to configure disks for a postgresql server. My gut feeling is as follows: Keep the OS and postgresql install on seperate disks to the postgresql /data directory? Is a single hard disk drive acceptable for the OS and postgresql program, or will this create a bottle neck? Would a multi disk array be more appropriate? Cheers, Benjamin Wragg -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.7.0 - Release Date: 17/01/2005 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.7.0 - Release Date: 17/01/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.7.1 - Release Date: 19/01/2005 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[PERFORM] index scan of whole table, can't see why
Hi folks, Running on 7.4.2, recently vacuum analysed the three tables in question. The query plan in question changes dramatically when a WHERE clause changes from ports.broken to ports.deprecated. I don't see why. Well, I do see why: a sequential scan of a 130,000 rows. The query goes from 13ms to 1100ms because the of this. The full plans are at http://rafb.net/paste/results/v8ccvQ54.html I have tried some tuning by: set effective_cache_size to 4000, was 1000 set random_page_cost to 1, was 4 The resulting plan changes, but no speed improvment, are at http://rafb.net/paste/results/rV8khJ18.html Any suggestions please? -- Dan Langille : http://www.langille.org/ BSDCan - The Technical BSD Conference - http://www.bsdcan.org/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] Query performance and understanding explain analzye
Hi, I'm trying to tune a query that is taking to long to execute. I haven't done much sql tuning and have only had a little exposure to explain and explain analyze but from what I've read on the list and in books the following is generally true: Seq Scans are the almost always evil (except if a table has only a few values) Nested Joins are generally evil as every node below it is executed the number of times the loops= value says. Hash Joins are extremely quick. This is because when postgres uses Hash joins it creates a copy of the values of the table in memory and then Hashes (some type of memory join) to the other table. Is that correct? If so, I'm after some help on the following query which I feel is taking too long. At the outset I want to apologise for the length of this email, I just wanted to provide as much info as possible. I just can't seem to make sense of it and have been trying for days! SELECT abs(item.area-userpolygon.area) as area,item.title as item_title,item.id as item_id,item.collection_id as item_collection_id,item.type_id as item_type_id,item.scale as item_scale,publisher.publisher as publisher_publisher,publisher.description as publisher_description,language.language as language_language,language.description as language_description,language.code2 as language_code2,language.code3 as language_code3,collection.collection as collection_collection,collection.description as collection_description,item_base_type.type as item_type_combination_type,item_subtype.subtype as item_type_combination_subtype,item_format.format as item_type_combination_format,status.status as status_status,status.description as status_description,currency.code as currency_code,currency.description as currency_description,item.subtitle as item_subtitle,item.description as item_description,item.item_number as item_item_number,item.edition as item_edition,item.h_datum as item_h_datum,item.v_datum as item_v_datum,item.projection as item_projection,item.isbn as item_isbn,client_item_field.stock as client_item_field_stock,client_item_field.price as client_item_field_price,client_freight.freight as client_freight_freight,client_freight.description as client_freight_description FROM item INNER JOIN (client INNER JOIN client_item ON (client.id=client_item.client_id)) ON (client_item.item_id=item.id ) INNER JOIN publisher ON (item.publisher_id = publisher.id) INNER JOIN language ON (item.language_id = language.id) LEFT OUTER JOIN collection ON (item.collection_id = collection.id) INNER JOIN item_base_type ON (item.type_id = item_base_type.id) INNER JOIN item_subtype ON (item.subtype_id = item_subtype.id) INNER JOIN item_format ON (item.format_id = item_format.id) INNER JOIN status ON (item.status_id = status.id) INNER JOIN currency ON (item.publisher_currency_id = currency.id) LEFT OUTER JOIN client_item_field ON (client_item.client_id=client_item_field.client_id) AND (client_item.item_id=client_item_field.item_id) LEFT OUTER JOIN client_item_freight ON (client_item.client_id=client_item_freight.client_id) AND (client_item.item_id=client_item_freight.item_id) LEFT OUTER JOIN client_freight ON (client_freight.id=client_item_freight.client_freight_id), userpolygon WHERE item.the_geom userpolygon.the_geom AND distance(item.the_geom, userpolygon.the_geom)=0 AND userpolygon.session_id='TestQuery' AND client.id=1 ORDER BY area asc When I explain analyze it I get: QUERY PLAN Sort (cost=4793.89..4793.91 rows=7 width=622) (actual time=4066.52..4067.79 rows=4004 loops=1) Sort Key: abs((item.area - userpolygon.area)) - Nested Loop (cost=533.45..4793.79 rows=7 width=622) (actual time=66.89..4054.01 rows=4004 loops=1) Join Filter: ((outer.the_geom inner.the_geom) AND (distance(outer.the_geom, inner.the_geom) = 0::double precision)) - Hash Join (cost=533.45..4548.30 rows=14028 width=582) (actual time=63.79..3826.16 rows=14028 loops=1) Hash Cond: (outer.client_freight_id = inner.id) - Hash Join (cost=532.38..4437.64 rows=14028 width=540) (actual time=63.52..3413.48 rows=14028 loops=1) Hash Cond: (outer.item_id = inner.item_id) Join Filter: (outer.client_id = inner.client_id) - Hash Join (cost=532.38..4367.49 rows=14028 width=528) (actual time=62.95..2993.37 rows=14028 loops=1) Hash Cond: (outer.item_id = inner.item_id) Join Filter: (outer.client_id = inner.client_id) - Hash Join (cost=532.38..4297.33 rows=14028 width=508) (actual time=62.48..2576.46 rows=14028 loops=1) Hash Cond: (outer.publisher_currency_id = inner.id)
[PERFORM] Tips and tunning for pgsql on HP-UX PA-RISC (RP3410)
Hi, Anyone have tips for performance of Postgresql, running on HP-UX 11.11, PA-RISC (HP RP3410)? What is better compiler (GCC or HP C/ANSI), flags of compilation, kernel and FS tunning? I have installed pgsql, and compiled with gcc -O2 -fexpensive-optimizations flags only. Another question: Postgres running well on HP-UX? What is the better: HP-UX or Linux on HP RP3410? Thanks! Gustavo Franklin Nóbrega Infraestrutura e Banco de Dados Planae Tecnologia da Informação (+55) 14 3224-3066 Ramal 209 www.planae.com.br ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] index scan of whole table, can't see why
Let's see if I have been paying enough attention to the SQL gurus. The planner is making a different estimate of how many deprecated'' versus how many broken ''. I would try SET STATISTICS to a larger number on the ports table, and re-analyze. ---(end of broadcast)--- TIP 8: explain analyze is your friend
