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 :-)

regards

Mark


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

> 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 <matt.casters@ibridge.be>
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 majordomo@postgresql.org)

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

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 memory page disk 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" <Matt.Casters@advalvas.be>
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 <matt.casters@ibridge.be>
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 majordomo@postgresql.org)




---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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" <Matt.Casters@advalvas.be>
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 <matt.casters@ibridge.be>
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 majordomo@postgresql.org)




---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

/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" <gspiegelberg@cranel.com>
To: <stange@rentec.com>
Cc: "Kevin Schroeder" <kschroeder@mirageworks.com>;
<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: gspiegelberg@cranel.com
> 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

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 majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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: gspiegelberg@cranel.com
Technology. Integrity. Focus.


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

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" <stange@rentec.com>
To: "Kevin Schroeder" <kschroeder@mirageworks.com>
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

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" <stange@rentec.com>
To: "Kevin Schroeder" <kschroeder@mirageworks.com>
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