Re: memory problems and crash of db when deleting data from tablewith thousands of partitions
От | Tomas Vondra |
---|---|
Тема | Re: memory problems and crash of db when deleting data from tablewith thousands of partitions |
Дата | |
Msg-id | 20191030181612.733kmg5g4ts45qyc@development обсуждение исходный текст |
Ответ на | Re: memory problems and crash of db when deleting data from tablewith thousands of partitions (Josef Machytka <josef.machytka@gmail.com>) |
Список | pgsql-bugs |
On Wed, Oct 30, 2019 at 03:35:24PM +0100, Josef Machytka wrote: >Here are scripts which you can use to simulate problem: > >- create_tables.sql - creates all partitions >- generate_data.sql - generates some data (technically you need only a few >records, delete command will fail anyway) > >and try command: >DELETE FROM bi.test_multilevel WHERE period_name = '....'; > >PostgreSQL 12 will start to use more and more memory and will stop >operation with "out of memory" (PostgreSQL 11 would crash) > Thanks for the scripts, I'm able to reproduce the issue. It does seem most of the memory is allocated in inheritance_planner, where we do this (around line 1500) foreach(lc, child_appinfos) { ... /* * Generate modified query with this rel as target. We first apply * adjust_appendrel_attrs, which copies the Query and changes * references to the parent RTE to refer to the current child RTE, * then fool around with subquery RTEs. */ subroot->parse = (Query *) adjust_appendrel_attrs(subroot, (Node *) parent_parse, 1, &appinfo); ... } This unfortunately allocates a Query struct that is about ~4.3MB *per partition*, and you have ~10000 of them, so 43GB in total. Unfortunately, this does not seem like a memory leak - we actually do need the structure, and it happens to be pretty large :-( So IMHO it's working as designed, it just wasn't optimized for cases with many partitions yet :-( Chances are we'll improve this in future releases (13+), but I very much doubt we can do much in existing releases - we tend not to make big changes there, and I don't see a simple change addressing this. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
В списке pgsql-bugs по дате отправления: