Re: [PERFORM] Memory reporting on CentOS Linux
On Sat, 15 Aug 2009, Mark Mielke wrote: I vote for screwed up reporting over some PostgreSQL-specific explanation. My understanding of RSS is the same as you suggested earlier - if 50% RAM is listed as resident, then there should not be 90%+ RAM free. I cannot think of anything PostgreSQL might be doing into influencing this to be false. The only thing I would have thought that would allow this would be mmap. Just for kicks, I tried an mmap() scenario (I do not think PostgreSQL uses mmap()), and it showed a large RSS, but it did NOT show free memory. More details please. What did you do, and what happened? I would have thought that a large read-only mmapped file that has been read (and therefore is in RAM) would be counted as VIRT and RES of the process in top, but can clearly be evicted from the cache at any time, and therefore would show up as buffer or cache rather than process memory in the totals. +1 on the idea that Linux memory reporting is incomprehensible nowadays. Matthew -- There once was a limerick .sig that really was not very big It was going quite fine Till it reached the fourth line -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Getting time of a postgresql-request
Dear users, I try to optimize the time of my Postgresql-requests, but for that, the first step, I of course need to get that time. I know that with: EXPLAIN ANALYSE SELECT bundesland from bundesland WHERE ST_Contains(the_geom, $punktgeometrie_start) AND ST_Contains(the_geom, $punktgeometrie_ende) I can get that time on command line. But I would like to get it in a php-script, like $timerequest_result=pg_result($timerequest,0); (well, that does not work). I wonder: Is there another way to get the time a request needs? How do you handle this? Thank you very much, Kai -- GRATIS für alle GMX-Mitglieder: Die maxdome Movie-FLAT! Jetzt freischalten unter http://portal.gmx.net/de/go/maxdome01 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Memory reporting on CentOS Linux
I believe this is exactly what is happening. I see that the TOP output lists a large amount ov VIRT & RES size being used, but the kernel does not report this memory as being reserved and instead lists it as free memory or cached. If this is indeed the case, how does one determine if a PostgreSQL instance requires more memory? Or how to determine if the system is using memory efficiently? Thanks for the responses. On 8/17/09 6:03 AM, "Matthew Wakeling" wrote: On Sat, 15 Aug 2009, Mark Mielke wrote: > I vote for screwed up reporting over some PostgreSQL-specific explanation. My > understanding of RSS is the same as you suggested earlier - if 50% RAM is > listed as resident, then there should not be 90%+ RAM free. I cannot think of > anything PostgreSQL might be doing into influencing this to be false. The only thing I would have thought that would allow this would be mmap. > Just for kicks, I tried an mmap() scenario (I do not think PostgreSQL uses > mmap()), and it showed a large RSS, but it did NOT show free memory. More details please. What did you do, and what happened? I would have thought that a large read-only mmapped file that has been read (and therefore is in RAM) would be counted as VIRT and RES of the process in top, but can clearly be evicted from the cache at any time, and therefore would show up as buffer or cache rather than process memory in the totals. +1 on the idea that Linux memory reporting is incomprehensible nowadays. Matthew -- There once was a limerick .sig that really was not very big It was going quite fine Till it reached the fourth line -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] number of rows estimation for bit-AND operation
Hi,
I am using int8 field to pack a number of error flags. This is very common
technique for large tables to pack multiple flags in one integer field.
For most records - the mt_flags field is 0. Here is the statistics (taken from
pgAdmin Statistics tab for mt_flags column):
Most common Values: {0,128,2,4,8)
Most common Frequencies: {0.96797,0.023,0.0076,0.0005,0.00029)
What I notice that when bit-AND function is used - Postgres significantly
underestimates the amount of rows:
explain analyze select count(*) from mt__20090801 where mt_flags&8=0;
QUERY PLAN
-
Aggregate (cost=83054.43..83054.44 rows=1 width=0) (actual
time=2883.154..2883.154 rows=1 loops=1)
-> Seq Scan on mt__20090801 (cost=0.00..83023.93 rows=12200 width=0)
(actual time=0.008..2100.390 rows=2439435 loops=1)
Filter: ((mt_flags & 8) = 0)
Total runtime: 2883.191 ms
(4 rows)
This is not an issue for the particular query above, but I noticed that due to
that miscalculation in many cases Postgres chooses plan with Nested Loops for
other queries. I can fix it by setting enable_nest_loops to off, but it's not
something I should set for all queries.
Is there any way to help Postgres make a better estimation for number of rows
returned by bit function?
Thanks,
-Slava Moudry, Senior DW Engineer. 4Info Inc.
P.S. table definition:
\d mt__20090801
Table "dw.mt__20090801"
Column |Type | Modifiers
--+-+---
mt_id| bigint | not null
mt_ts| timestamp without time zone |
ad_cost | numeric(10,5) |
short_code | integer |
message_id | bigint | not null
mp_code | character(1)| not null
al_id| integer | not null
cust_id | integer |
device_id| integer | not null
broker_id| smallint|
partner_id | integer |
ad_id| integer |
keyword_id | integer |
sc_id| integer |
cp_id| integer |
src_alertlog_id | bigint |
src_query_id | bigint |
src_response_message_num | smallint|
src_gateway_message_id | bigint |
mt_flags | integer |
message_length | integer | not null
created_etl | timestamp without time zone |
Indexes:
"mt_device_id__20090801" btree (device_id) WITH (fillfactor=100),
tablespace "index2"
"mt_ts__20090801" btree (mt_ts) WITH (fillfactor=100) CLUSTER, tablespace
"index2"
Check constraints:
"mt__20090801_mt_ts_check" CHECK (mt_ts >= '2009-08-01 00:00:00'::timestamp
without time zone AND mt_ts < '2009-08-02 00:00:00'::timestamp without time
zone)
Inherits: mt
Tablespace: "dw_tables3"
Re: [PERFORM] Memory reporting on CentOS Linux
On 8/17/09 10:24 AM, "Jeremy Carroll" wrote: > I believe this is exactly what is happening. I see that the TOP output lists a > large amount ov VIRT & RES size being used, but the kernel does not report > this memory as being reserved and instead lists it as free memory or cached. Oh! I recall I found that fun behaviour Linux and thought it was a Postgres bug a while back. It has lot of other bad effects on how the kernel chooses to swap. I really should have recalled that one. Due to this behavior, I had initially blamed postgres for "pinning" memory in shared_buffers in the disk cache. But that symptom is one of linux thinking somehow that pages read into shared memory are still cached (or something similar). Basically, it thinks that there is more free memory than there is when there is a lot of shared memory. Run a postgres instance with over 50% memory assigned to shared_buffers and when memory pressure builds kswapd will go NUTS in CPU use, apparently confused. With high OS 'swappiness' value it will swap in and out too much, and with low 'swappiness' it will CPU spin, aware on one hand that it is low on memory, but confused by the large apparent amount free so it doesn't free up much and kswapd chews up all the CPU and the system almost hangs. It behaves as if the logic that determines where to get memory from for a process knows that its almost out, but the logic that decides what to swap out thinks that there is plenty free. The larger the ratio of shared memory to total memory in the system, the higher the CPU use by the kernel when managing the buffer cache. Bottom line is that Linux plus lots of SYSV shared mem doesn't work as well as it should. Setting shared_buffers past 35% RAM doesn't work well on Linux. Shared memory accounting is fundamentally broken in Linux (see some other threads on how the OOM killer works WRT shared memory for other examples). > > If this is indeed the case, how does one determine if a PostgreSQL instance > requires more memory? Or how to determine if the system is using memory > efficiently? Just be aware that the definite memory used per process is RES-SHR, and that the max SHR value is mostly duplicated in the 'cached' or 'free' columns. That mas SHR value IS used by postgres, and not the OS cache. If cached + memory free is on the order of your shared_buffers/SHR size, you're pretty much out of memory. Additionally, the OS will start putting things into swap before you reach that point, so pay attention to the swap used column in top or free. That is a more reliable indicator than anything else at the system level. If you want to know what postgres process is using the most memory on its own look at the DATA and CODE top columns, or calculate RES-SHR. I have no idea if more recent Linux Kernels have fixed this at all. > > Thanks for the responses. > > > On 8/17/09 6:03 AM, "Matthew Wakeling" wrote: > >> On Sat, 15 Aug 2009, Mark Mielke wrote: >>> I vote for screwed up reporting over some PostgreSQL-specific explanation. >>> My >>> understanding of RSS is the same as you suggested earlier - if 50% RAM is >>> listed as resident, then there should not be 90%+ RAM free. I cannot think >>> of >>> anything PostgreSQL might be doing into influencing this to be false. >> >> The only thing I would have thought that would allow this would be mmap. >> >>> Just for kicks, I tried an mmap() scenario (I do not think PostgreSQL uses >>> mmap()), and it showed a large RSS, but it did NOT show free memory. >> >> More details please. What did you do, and what happened? I would have >> thought that a large read-only mmapped file that has been read (and >> therefore is in RAM) would be counted as VIRT and RES of the process in >> top, but can clearly be evicted from the cache at any time, and therefore >> would show up as buffer or cache rather than process memory in the totals. >> >> +1 on the idea that Linux memory reporting is incomprehensible nowadays. >> >> Matthew >> >> -- >> There once was a limerick .sig >> that really was not very big >> It was going quite fine >> Till it reached the fourth line >> >> -- >> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-performance >> > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Memory reporting on CentOS Linux
On 8/17/09 4:43 PM, "Scott Carey" wrote: > > > On 8/17/09 10:24 AM, "Jeremy Carroll" > wrote: > >> I believe this is exactly what is happening. I see that the TOP output lists >> a >> large amount ov VIRT & RES size being used, but the kernel does not report >> this memory as being reserved and instead lists it as free memory or cached. > > Oh! I recall I found that fun behaviour Linux and thought it was a Postgres > bug a while back. It has lot of other bad effects on how the kernel chooses > to swap. I really should have recalled that one. Due to this behavior, I > had initially blamed postgres for "pinning" memory in shared_buffers in the > disk cache. But that symptom is one of linux thinking somehow that pages > read into shared memory are still cached (or something similar). > > Basically, it thinks that there is more free memory than there is when there > is a lot of shared memory. Run a postgres instance with over 50% memory > assigned to shared_buffers and when memory pressure builds kswapd will go > NUTS in CPU use, apparently confused. With high OS 'swappiness' value it > will swap in and out too much, and with low 'swappiness' it will CPU spin, > aware on one hand that it is low on memory, but confused by the large > apparent amount free so it doesn't free up much and kswapd chews up all the > CPU and the system almost hangs. It behaves as if the logic that determines > where to get memory from for a process knows that its almost out, but the > logic that decides what to swap out thinks that there is plenty free. The > larger the ratio of shared memory to total memory in the system, the higher > the CPU use by the kernel when managing the buffer cache. > Based on a little digging, I'd say that this patch to the kernel probably alleviates the performance problems I've seen with swapping when shared mem is high: http://lwn.net/Articles/286472/ Other patches have improved the shared memory tracking, but its not clear if tools like top have taken advantage of the new info available in /proc. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Getting time of a postgresql-request
Kai Behncke wrote: > > But I would like to get it in a php-script, like > > $timerequest_result=pg_result($timerequest,0); > > (well, that does not work). > > I wonder: Is there another way to get the time a request needs? > How do you handle this? > $time = microtime() $result = pg_result($query); echo "Time to run query and return result to PHP: ".(microtime() - $time); Something like that. Regards Russell -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Memory reporting on CentOS Linux
On Fri, 14 Aug 2009, Scott Carey wrote: The memory used by postgres for shared memory is the largest of all SHR columns for postgres columns. Or, about 7.9GB. So, postgres is using about 7.9GB for shared memory, and very little for anything else. It's a good idea to check this result against the actual shared memory block allocated. If the server has been up long enough to go through all of shared_buffers once, the results should be close. You can look at the block under Linux using "ipcs -m"; the one you want should look something like this: -- Shared Memory Segments keyshmid owner perms bytes nattch status 0x0052e2c1 21757972 gsmith 600548610048 10 That represents a bit over 512MB worth of allocated memory for the server. Alternately, you can use "pmap -d" on a PostgreSQL process to find the block, something like this works: $ pmap -d 13961 | egrep "^Address|shmid" Address Kbytes Mode Offset DeviceMapping 96c41000 535752 rw-s- 000:9 [ shmid=0x14c0014 ] I have given up on presuming the summary values top shows are good for anything on Linux. I look at /proc/meminfo to see how much RAM is free, and to figure out what's going on with the server processes I use: ps -e -o pid,rss,vsz,size,cmd | grep postgres And compute my own totals (one of these days I'm going to script that process). Useful reading on this topic: http://virtualthreads.blogspot.com/2006/02/understanding-memory-usage-on-linux.html http://mail.nl.linux.org/linux-mm/2003-03/msg00077.html http://forums.gentoo.org/viewtopic.php?t=175419 Most confusion about what's going on here can be resolved by spending some quality time with pmap. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
