Re: PostgreSQL (9.3 and 9.6) eats all memory when using many tables
От | hubert depesz lubaczewski |
---|---|
Тема | Re: PostgreSQL (9.3 and 9.6) eats all memory when using many tables |
Дата | |
Msg-id | 20160613161459.GA15390@depesz.com обсуждение исходный текст |
Ответ на | Re: PostgreSQL (9.3 and 9.6) eats all memory when using many tables (hubert depesz lubaczewski <depesz@depesz.com>) |
Список | pgsql-bugs |
On Mon, Jun 13, 2016 at 03:22:17PM +0200, hubert depesz lubaczewski wrote: > On Mon, Jun 13, 2016 at 08:56:56AM -0400, Peter Eisentraut wrote: > > On 6/9/16 11:46 AM, hubert depesz lubaczewski wrote: > > > Basically it looks that postgresql "caches" query plans? parsed elements? for > > > queries, but doesn't put any kind of limit to size of this cache. Which means > > > that if our app is using LOTS of different queries, the memory usage will grow > > > in time. > > > > If you compile with -DSHOW_MEMORY_STATS, it will print out memory allocation > > after every command, so you might be able to see where the memory is going. > > Compiled, am running it now, but it is much slower now. And the output > is huge. > > After ~5k queries, it looks like: > TopMemoryContext: 5892000 total in 701 blocks; 18344 free (68 chunks); 5873656 used > TableSpace cache: 8192 total in 1 blocks; 3216 free (0 chunks); 4976 used > Operator lookup cache: 24576 total in 2 blocks; 11888 free (5 chunks); 12688 used > MessageContext: 32768 total in 3 blocks; 6216 free (5 chunks); 26552 used > Operator class cache: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used > smgr relation table: 4186112 total in 9 blocks; 1374144 free (31 chunks); 2811968 used > TransactionAbortContext: 32768 total in 1 blocks; 32736 free (0 chunks); 32 used > Portal hash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used > PortalMemory: 8192 total in 1 blocks; 8160 free (1 chunks); 32 used > Relcache by OID: 2088960 total in 8 blocks; 1008496 free (16 chunks); 1080464 used > CacheMemoryContext: 182443144 total in 422 blocks; 2623776 free (1 chunks); 179819368 used > MdSmgr: 1040384 total in 7 blocks; 192512 free (0 chunks); 847872 used > ident parser context: 3072 total in 2 blocks; 1416 free (1 chunks); 1656 used > hba parser context: 130048 total in 7 blocks; 42496 free (2 chunks); 87552 used > LOCALLOCK hash: 24576 total in 2 blocks; 13920 free (4 chunks); 10656 used > Timezones: 83472 total in 2 blocks; 3744 free (0 chunks); 79728 used > ErrorContext: 8192 total in 1 blocks; 8160 free (6 chunks); 32 used > > I'll try to get to the end of the test (~70k tables), and then show the > same info, plus some statistics about "CacheMemoryContext" if it helps > (I'd rather not show *all* of it, though :) OK. I can't really wait to get it all done. Ran it for ~ 44000 queries. Sum on anonymous memory from smaps is 1337912 kB. Main information looks like: TopMemoryContext: 28073888 total in 3408 blocks; 18264 free (71 chunks); 28055624 used TableSpace cache: 8192 total in 1 blocks; 3216 free (0 chunks); 4976 used Operator lookup cache: 24576 total in 2 blocks; 11888 free (5 chunks); 12688 used MessageContext: 65536 total in 4 blocks; 34464 free (5 chunks); 31072 used Operator class cache: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used smgr relation table: 16769024 total in 11 blocks; 2725888 free (38 chunks); 14043136 used TransactionAbortContext: 32768 total in 1 blocks; 32736 free (0 chunks); 32 used Portal hash: 8192 total in 1 blocks; 1680 free (0 chunks); 6512 used PortalMemory: 8192 total in 1 blocks; 8160 free (1 chunks); 32 used Relcache by OID: 8380416 total in 10 blocks; 2996848 free (21 chunks); 5383568 used CacheMemoryContext: 734127048 total in 2791 blocks; 5143808 free (7 chunks); 728983240 used <101491 lines removed> MdSmgr: 8380416 total in 10 blocks; 4141120 free (0 chunks); 4239296 used ident parser context: 3072 total in 2 blocks; 1416 free (1 chunks); 1656 used hba parser context: 130048 total in 7 blocks; 42496 free (2 chunks); 87552 used LOCALLOCK hash: 24576 total in 2 blocks; 13920 free (4 chunks); 10656 used Timezones: 83472 total in 2 blocks; 3744 free (0 chunks); 79728 used ErrorContext: 8192 total in 1 blocks; 8160 free (5 chunks); 32 used The 101491 lines that I removed were looking like: index_context_module_progressions_on_context_module_id: 1024 total in 1 blocks; 200 free (0 chunks); 824 used removing index/table name, and making a summary, I got: 34859 1024 total in 1 blocks; 152 free (0 chunks); 872 used 31877 1024 total in 1 blocks; 200 free (0 chunks); 824 used 10294 1024 total in 1 blocks; 16 free (0 chunks); 1008 used 5791 1024 total in 1 blocks; 64 free (0 chunks); 960 used 3221 3072 total in 2 blocks; 1736 free (2 chunks); 1336 used 3165 3072 total in 2 blocks; 2008 free (2 chunks); 1064 used 3146 3072 total in 2 blocks; 1784 free (2 chunks); 1288 used 2240 3072 total in 2 blocks; 1960 free (1 chunks); 1112 used 1791 3072 total in 2 blocks; 1872 free (0 chunks); 1200 used 934 3072 total in 2 blocks; 1408 free (1 chunks); 1664 used 793 3072 total in 2 blocks; 1824 free (0 chunks); 1248 used 304 3072 total in 2 blocks; 1488 free (1 chunks); 1584 used 224 3072 total in 2 blocks; 1528 free (1 chunks); 1544 used 218 3072 total in 2 blocks; 1488 free (0 chunks); 1584 used 217 3072 total in 2 blocks; 1536 free (0 chunks); 1536 used 188 3072 total in 2 blocks; 736 free (1 chunks); 2336 used 164 1024 total in 1 blocks; 40 free (0 chunks); 984 used 158 3072 total in 2 blocks; 1216 free (0 chunks); 1856 used 156 3072 total in 2 blocks; 1328 free (0 chunks); 1744 used 128 3072 total in 2 blocks; 1576 free (1 chunks); 1496 used 121 3072 total in 2 blocks; 1648 free (1 chunks); 1424 used 121 3072 total in 2 blocks; 1296 free (0 chunks); 1776 used 118 7168 total in 3 blocks; 3064 free (1 chunks); 4104 used 115 3072 total in 2 blocks; 1632 free (0 chunks); 1440 used 115 3072 total in 2 blocks; 1336 free (1 chunks); 1736 used 110 3072 total in 2 blocks; 1792 free (0 chunks); 1280 used 108 3072 total in 2 blocks; 928 free (0 chunks); 2144 used 107 3072 total in 2 blocks; 792 free (1 chunks); 2280 used 105 3072 total in 2 blocks; 1184 free (1 chunks); 1888 used 94 3072 total in 2 blocks; 1192 free (1 chunks); 1880 used 91 3072 total in 2 blocks; 1032 free (1 chunks); 2040 used 79 3072 total in 2 blocks; 1736 free (0 chunks); 1336 used 64 3072 total in 2 blocks; 1080 free (1 chunks); 1992 used 49 3072 total in 2 blocks; 1440 free (1 chunks); 1632 used 48 3072 total in 2 blocks; 2008 free (1 chunks); 1064 used 42 3072 total in 2 blocks; 1240 free (1 chunks); 1832 used 39 3072 total in 2 blocks; 1784 free (0 chunks); 1288 used 38 3072 total in 2 blocks; 1136 free (1 chunks); 1936 used 31 3072 total in 2 blocks; 1264 free (0 chunks); 1808 used 8 3072 total in 2 blocks; 784 free (1 chunks); 2288 used 8 3072 total in 2 blocks; 1696 free (1 chunks); 1376 used 4 3072 total in 2 blocks; 1744 free (0 chunks); 1328 used 3 3072 total in 2 blocks; 1680 free (0 chunks); 1392 used 3 3072 total in 2 blocks; 1384 free (1 chunks); 1688 used 2 3072 total in 2 blocks; 1376 free (0 chunks); 1696 used First number is how many lines end with given information. Does it help in any way? depesz
В списке pgsql-bugs по дате отправления:
Предыдущее
От: Tom LaneДата:
Сообщение: Re: BUG #14185: Indentation error in win32.mak let libpq compile fail on USE_OPENSSL=1 -- fix available
Следующее
От: Jeff JanesДата:
Сообщение: Re: PostgreSQL (9.3 and 9.6) eats all memory when using many tables