Forum OpenACS Q&A: An attmept at understanding System V IPC with Postgresql, and Linux.

I have been trying to learn more about how Postgresql and Linux interact with the System V IPC. In doing this I have come up with some questions:

What type of damage can I do by setting SHMMAX to high?

How does one calculate what the highest SHMMAX value for the system?

How does one calculate how much ram is needed to put a table and/or an entire data base instance into ram (I figure I need this value in order to set SHMMAX correctly and for how much actual ram I need)?

Is my understanding (outlined below) of the impact of SHMMAX and available ram accurate?

When I run ipcs and a simple 'select * from logs2' query on a 2million+ row table that reports taking up 750mb on the hard drive, the results of ipcs never change. Ipcs allways shows four Shared Memory Segments with the same size every time (if I connect to the DB more again the nattch value goes up one that is the only change).

------ Shared Memory Segments --------
key       shmid     owner     perms     bytes     nattch    status
0x0052e3f6 19968     postgres  700       144       2
0x0052e3ed 19969     postgres  600       33050624  2
0x0052e3f3 19970     postgres  600       66060     2
0x00000000 3         nobody    600       46084     14        dest

------ Semaphore Arrays --------
key       semid     owner     perms     nsems     status
0x0052e3fa 12288     postgres  600       16
0x0052e3fb 12289     postgres  600       16

------ Message Queues --------
key       msqid     owner     perms     used-bytes  messages

SHMMAX is set to 33554432 and the -B swicth is set to 3900, which makes sense since I have a memory segment of 33mb (I figure there are probably some rounding errors in my calculation of what these values should be compared to the math of ipcs, mine: 3900*8192=31,948,800)

However vmstat and top show massive ram usage when I run the query. The 1gig of ram gets used up and sometimes all of the cache (an error is thrown by postgres when the cache is maxed out).

Initially I figured if Postgres is going to use more memory it should take a hold of more segments, yet it doesn't, but the ram usage goes up. So what I have concluded is that Shared Memory Segments are what are reserved for use and cannot be overwritten or used by other applications. When PG needs more room then allowed in the Shared Memory Segment it grabs what ever is not preassigned. This would explain the high top values and the stagnant ipcs values.

Does it use this extra ram in segments sized by -B's value (thus the more chunks the more overhead)?

If Postgres has enough ram available it uses it, otherwise it dumps an error. So when I dump an error it is because the system does not have enough ram, I may be able free up more ram by using higher SHMMAX and -B values. Additionally I will be able to do larger queries due to less overhead of having all/most of the work in Postgres' Shared Memory Segment, but I will still have a limit of my available ram for the system (1gig in this case). So getting an out of memory error does not mean I have just the wrong settings for SHMMAX and the -B option, it also can mean I do not have enough ram. I may get better results with better values for SHMMAX and the -B option, but it will reach a point where I simply do not have enough ram. If I had two times the ram I have now with the same settings for SHMMAX and the -B option it would most likely not be any faster then before.

So this can explain how even though I have my -B set to 3900 or 33mb when I add column to this one large table that is enough so it no longer fits into ram (on disk it went from 751mb to 769mb). Even when I bring the Shared Buffers up to 800mb (estimated max for this system).

I think that with what I have outlined above (if it is correct) and the answers two my first two questions I will have enough information to figure out how to size my machine to the data and how to setup Linux and Postgres to handle my data.

I am trying to learn a lot here, so if what I said above is best summarized in some document, point me to it! Thanks for taking the time to read this, and if you have any advice or answers, thank you!

- Jeff

P.S. if anyone can recommend some resources on how to deal with large data sets it would be greatly appreciated, this table is at 750mb this week and by next week it should be up another 50-100mb, so I have some learning to do!

What type of damage can I do by setting SHMMAX to high?

Shared memory segments don't go away unless they are explicitly destroyed, so a malicious local user can create orphan shared memory segments until it fills your RAM and swap. For servers with no untrusted users though, there isn't much harm you can do by raising the limit (I have it at 1.5Gb on a Oracle server with 2G of RAM, and it works fine).

How does one calculate what the highest SHMMAX value for the system?

The highest value you can set it to is 4Gb (probably more if you have a kernel with PAE support and more than 4Gb of RAM).

The shared memory segments are created once (at startup) with a fixed size, and Postgres does the management of that memory zone, so the size will not change.

You see the massive memory usage because the Postgres backends need to read the full data generated by the query into memory. Some of that data might also make it into the shared memory segment (which is used as a cache), so the error you're getting is not because the cache runs out (it can't, because old entries are discarded on a LRU-basis), but because the backend doesn't have enough memory to read the data it needs.