On Sun, 13 Dec 2015 09:57:21 +0100 Gerhard Wiesinger <lists@xxxxxxxxxxxxx> wrote: > > some further details from the original FreeBSD 10.1 machine: > > Mem: 7814M Active, 20G Inact, 2982M Wired, 232M Cache, 1661M Buf, 30M Free > Swap: 512M Total, 506M Used, 6620K Free, 98% Inuse > > PID USERNAME THR PRI NICE SIZE RES STATE C TIME WCPU COMMAND > 77941 pgsql 5 20 0 7925M 7296M usem 2 352:34 6.98% > postgres: username dbnamee 127.0.0.1(43367) (postgres) <snip> I see no evidence of an actual leak here. Each process is basically using the 7G of shared_buffers you have allocated in the config (which is only 7G _total_ for all processes, since it's shared memory) > Out of memory: > kernel: swap_pager_getswapspace(4): failed > kernel: swap_pager_getswapspace(8): failed > kernel: swap_pager_getswapspace(3): failed > > Main issue is IHMO (as far as I understood the FreeBSD Memory system) > that 20G are INACTIVE. When I subtract the shared memory, even ~13GB > should be available, but they are still allocated but inactive > (INACTIVE). INACTIVE memory might be clean or dirty. As we get into out > of memory situations it is likely that the memory is dirty (otherwise it > would have been reused). Not quite correct. Inactive memory is _always_ available for re-use. > Config: > Memory: 32GB, Swap: 512MB Probably not the cause of this problem, but this is a non-optimal layout. If you're going to use swap at all, it should generally be 2x the size of your RAM for optimal performance under memory pressure. > maintenance_work_mem = 512MB > effective_cache_size = 10GB > work_mem = 892MB I expect that this value is the cause of the problem. The scenario you describe below is sorting a large table on an unindexed column, meaning it will have to use all that work_mem. I'd be interested to see the output of: EXPLAIN ANALYZE SELECT * FROM t_random ORDER BY md5 LIMIT 100000; But even without that information, I'd recommend you reduce work_mem to about 16M or so. > wal_buffers = 8MB > checkpoint_segments = 16 > shared_buffers = 7080MB > max_connections = 80 > autovacuum_max_workers = 3 [snip] > > We are running PostgreSQL 9.4.5 on FreeBSD 10.1 and have multiple > > worker processes connected via persistent connections to PostgreSQL, > > they perform just simple queries with SELECT on primary keys and > > simple INSERTS/UPDATES. That's not at all the test scenario you show below. The scenario below is a large sort operation on a non-indexed column, which is vastly different than a single-row fetch based on an index. > > Normally nearly all the workers are idle but > > they still consume the maximum configured work mem on the PostgreSQL > > server and the memory is also resident. I see no evidence of that in your top output. Each process has a reference to the 7G of shared_buffers you allocated, which is memory shared by all processes, and is expected. I'm not as familiar with Linux top, but the results appear to be the same. > > If some other queries get in > > we get into out of memory situations. So it looks like PostgreSQL has > > memory leaks. > > > > I found a test scenario to reproduce it also on a newer FreeBSD 10.2 > > VM as well as in a Fedora 23 VM (both with PostgreSQL 9.4.5): > > > > Executions in psql with one persisent connection: > > -- Create the table > > CREATE TABLE t_random AS SELECT s, md5(random()::text) FROM > > generate_Series(1,100000000) s; > > -- Create the index > > CREATE INDEX ON t_random(s); > > > > -- Restart psql with a new connection: > > > > -- Memory goes slighty up after each execution even after canceling: > > -- Memory leak on FreeBSD 10.2/Fedora 23 and PostgreSQL 9.4.5 on > > cancel the query or multiple execution > > SELECT * FROM t_random ORDER BY md5 LIMIT 100000; > > > > -- Therefore I created a function: > > CREATE OR REPLACE FUNCTION execmultiplei(IN num int8) > > RETURNS void AS $$ > > BEGIN > > -- RAISE NOTICE 'num=%', num; > > FOR i IN 1..num LOOP > > PERFORM * FROM t_random WHERE s = i; > > END LOOP; > > END; > > $$ LANGUAGE plpgsql; > > > > -- Test it several times > > SELECT execmultiplei(10000000); > > > > -- Linux testing (FreeBSD is similar), relevant part is RES (resident > > memory): > > PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND > > -- after startup of psql > > 26851 postgres 20 0 2363276 7432 6292 S 0.0 0.2 0:00.00 > > postgres: postgres postgres [local] idle > > -- Memory goes up, ok so far > > 26851 postgres 20 0 2365732 255152 253548 R 99.0 6.3 0:10.77 > > postgres: postgres postgres [local] SELECT > > 26851 postgres 20 0 2365732 408464 406788 R 100.0 10.1 0:17.81 > > postgres: postgres postgres [local] SELECT > > 26851 postgres 20 0 2365732 864472 862576 R 100.0 21.4 0:38.90 > > postgres: postgres postgres [local] SELECT > > -- Function execmultiplei and transaction terminated, but memory still > > allocated!!! > > 26851 postgres 20 0 2365732 920668 918748 S 0.0 22.7 0:41.40 > > postgres: postgres postgres [local] idle > > -- Calling it again > > 26851 postgres 20 0 2365732 920668 918748 R 99.0 22.7 0:46.51 > > postgres: postgres postgres [local] SELECT > > -- idle again, memory still allocated > > 26851 postgres 20 0 2365732 920668 918748 S 0.0 22.7 1:22.54 > > postgres: postgres postgres [local] idle > > > > Memory will only be released if psql is exited. According to the > > PostgreSQL design memory should be freed when the transaction completed. > > > > top commands on FreeBSD: top -SaPz -o res -s 1 > > top commands on Linux: top -o RES d1 > > > > Config: VMs with 4GB of RAM, 2 vCPUs > > shared_buffers = 2048MB # min 128kB > > effective_cache_size = 2GB > > work_mem = 892MB > > wal_buffers = 8MB > > checkpoint_segments = 16 -- Bill Moran -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
