博客
关于我
强烈建议你试试无所不能的chatGPT，快点击我
PostgreSQL中1000分区的继承分区表更新失败的原因
阅读量：6983 次
发布时间：2019-06-27

本文共 16340 字，大约阅读时间需要 54 分钟。

之前的一篇博客 中，遇到1000分区的分区表更新和删除都执行失败(问题3)的问题。经过简单的调查发现原因竟然是OOM导致进程被杀。以下是相关错误消息

1.错误现象

命令行( SQL执行失败）：

点击(此处)折叠或打开

  1. -bash-4.1$ pgbench -n -r -c 10 -j 10 -T 2 -f update.sql db1000
  2. Client 2 aborted in state 1. Probably the backend died while processing.
  3. WARNING: terminating connection because of crash of another server process
  4. DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
  5. HINT: In a moment you should be able to reconnect to the database and repeat your command.
  6. WARNING: terminating connection because of crash of another server process
  7. DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
  8. HINT: In a moment you should be able to reconnect to the database and repeat your command.
  9. WARNING: terminating connection because of crash of another server process
  10. DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
  11. HINT: In a moment you should be able to reconnect to the database and repeat your command.
  12. WARNING: terminating connection because of crash of another server process
  13. DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
  14. HINT: In a moment you should be able to reconnect to the database and repeat your command.
  15. WARNING: terminating connection because of crash of another server process
  16. DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
  17. HINT: In a moment you should be able to reconnect to the database and repeat your command.
  18. WARNING: terminating connection because of crash of another server process
  19. DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
  20. HINT: In a moment you should be able to reconnect to the database and repeat your command.
  21. WARNING: terminating connection because of crash of another server process
  22. DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
  23. HINT: In a moment you should be able to reconnect to the database and repeat your command.
  24. WARNING: terminating connection because of crash of another server process
  25. DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
  26. HINT: In a moment you should be able to reconnect to the database and repeat your command.
  27. WARNING: terminating connection because of crash of another server process
  28. DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
  29. HINT: In a moment you should be able to reconnect to the database and repeat your command.
  30. Client 3 aborted in state 1. Probably the backend died while processing.
  31. Client 0 aborted in state 1. Probably the backend died while processing.
  32. Client 9 aborted in state 1. Probably the backend died while processing.
  33. Client 8 aborted in state 1. Probably the backend died while processing.
  34. Client 7 aborted in state 1. Probably the backend died while processing.
  35. Client 1 aborted in state 1. Probably the backend died while processing.
  36. Client 4 aborted in state 1. Probably the backend died while processing.
  37. Client 6 aborted in state 1. Probably the backend died while processing.
  38. Client 5 aborted in state 1. Probably the backend died while processing.
  39. transaction type: Custom query
  40. scaling factor: 1
  41. query mode: simple
  42. number of clients: 10
  43. number of threads: 10
  44. duration: 2 s
  45. number of transactions actually processed: 0
  46. tps = 0.000000 (including connections establishing)
  47. tps = 0.000000 (excluding connections establishing)
  48. statement latencies in milliseconds:
  49.     0.271800    \setrandom id 1 10000000
  50.     0.000000    update maintb set name = 'aaaaa12345' where id=:id;
pg服务端日志(服务端进程被杀并重启):

点击(此处)折叠或打开

  1. LOG: server process (PID 18565) was terminated by signal 9: Killed
  2. DETAIL: Failed process was running: update maintb set name = 'aaaaa12345' where id=150458;
  3. LOG: terminating any other active server processes
  4. WARNING: terminating connection because of crash of another server process
  5. DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
  6. HINT: In a moment you should be able to reconnect to the database and repeat your command.
  7. WARNING: terminating connection because of crash of another server process
  8. DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
  9. HINT: In a moment you should be able to reconnect to the database and repeat your command.
  10. WARNING: terminating connection because of crash of another server process
  11. DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
  12. HINT: In a moment you should be able to reconnect to the database and repeat your command.
  13. WARNING: terminating connection because of crash of another server process
  14. DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
  15. HINT: In a moment you should be able to reconnect to the database and repeat your command.
  16. WARNING: terminating connection because of crash of another server process
  17. DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
  18. HINT: In a moment you should be able to reconnect to the database and repeat your command.
  19. WARNING: terminating connection because of crash of another server process
  20. DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
  21. HINT: In a moment you should be able to reconnect to the database and repeat your command.
  22. WARNING: terminating connection because of crash of another server process
  23. DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
  24. HINT: In a moment you should be able to reconnect to the database and repeat your command.
  25. WARNING: terminating connection because of crash of another server process
  26. DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
  27. HINT: In a moment you should be able to reconnect to the database and repeat your command.
  28. WARNING: terminating connection because of crash of another server process
  29. DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
  30. HINT: In a moment you should be able to reconnect to the database and repeat your command.
  31. WARNING: terminating connection because of crash of another server process
  32. DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
  33. HINT: In a moment you should be able to reconnect to the database and repeat your command.
  34. WARNING: terminating connection because of crash of another server process
  35. DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
  36. HINT: In a moment you should be able to reconnect to the database and repeat your command.
  37. LOG: all server processes terminated; reinitializing
  38. LOG: database system was interrupted; last known up at 2015-01-16 14:18:26 CST
  39. LOG: database system was not properly shut down; automatic recovery in progress
  40. LOG: redo starts at 3/4A153AC8
  41. LOG: record with zero length at 3/4AD01498
  42. LOG: redo done at 3/4AD01468
  43. LOG: last completed transaction was at log time 2015-01-16 14:35:11.990522+08
  44. LOG: database system is ready to accept connections
  45. LOG: autovacuum launcher started
/var/log/messages(OOM):

点击(此处)折叠或打开

  1. Jan 16 14:35:40 hanode1 kernel: Out of memory: Kill process 18565 (postgres) score 94 or sacrifice child
  2. Jan 16 14:35:40 hanode1 kernel: Killed process 18565, UID 26, (postgres) total-vm:608864kB, anon-rss:150480kB, file-rss:3180kB

2.PG进程究竟分配了多少内存?

通过debug PG9.4的代码，发现生成分区表的1个update语句的执行计划居然耗费了717M的内存。
sql:

点击(此处)折叠或打开

  1. db1000=# update maintb set name='aaaaa12345' where id =3;
  2. UPDATE 19
src/backend/tcop/postgres.c:

点击(此处)折叠或打开

  1. /*
  2.          * OK to analyze, rewrite, and plan this query.
  3.          *
  4.          * Switch to appropriate context for constructing querytrees (again,
  5.          * these must outlive the execution context).
  6.          */
  7.         oldcontext = MemoryContextSwitchTo(MessageContext);
  9.         querytree_list = pg_analyze_and_rewrite(parsetree, query_string,
  10.                                                 NULL, 0);
  12.         plantree_list = pg_plan_queries(querytree_list, 0, NULL);
  14.         /* Done with the snapshot used for parsing/planning */
  15.         if (snapshot_set)
  16.             PopActiveSnapshot();
在上面的pg_plan_queries()调用前后分别在gdb中执行“call MemoryContextStats(MessageContext)”，从输出结果可以看出 pg_plan_queries分配了717M内存。

点击(此处)折叠或打开

  1. MessageContext: 16384 total in 2 blocks; 9976 free (9 chunks); 6408 used
  2. MessageContext: 721420288 total in 96 blocks; 3715776 free (13 chunks); 717704512 used
1个客户端700多M
，10个客户端
并行执行就是7个G
，我的VM只有2G
内存，难怪被kill掉
。

3.问题在哪里？

同样的方法，测量了另外两种情况的内存消耗。
100个分区的update
sql:

点击(此处)折叠或打开

  1. db100=# update maintb set name='aaaaa12345' where id =3;
  2. UPDATE 8
生成执行计划消耗的内存:8M

点击(此处)折叠或打开

  1. MessageContext: 24576 total in 2 blocks; 18168 free (9 chunks); 6408 used
  2. MessageContext: 8380416 total in 10 blocks; 5056 free (3 chunks); 8375360 used
从8M到700M，不难看出：分区表update的执行计划消耗内存与分区数目n是n^2的倍数关系。
进一步细化，发现关键点在下面两处内存分配。
src/backend/optimizer/plan/planner.c

点击(此处)折叠或打开

  1. static Plan *
  2. inheritance_planner(PlannerInfo *root)
  3. {
  4. ...
  5.     foreach(lc, root->append_rel_list)//循环1001次
  6.     {
  7.         AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(lc);
  8.         PlannerInfo subroot;
  9.         Plan     *subplan;
  10.         Index        rti;
  12.         /* append_rel_list contains all append rels; ignore others */
  13.         if (appinfo->parent_relid != parentRTindex)
  14.             continue;
  16.         /*
  17.          * We need a working copy of the PlannerInfo so that we can control
  18.          * propagation of information back to the main copy.
  19.          */
  20.         memcpy(&subroot, root, sizeof(PlannerInfo));
  22.         /*
  23.          * Generate modified query with this rel as target. We first apply
  24.          * adjust_appendrel_attrs, which copies the Query and changes
  25.          * references to the parent RTE to refer to the current child RTE,
  26.          * then fool around with subquery RTEs.
  27.          */
  28.         subroot.parse = (Query *)
  29.             adjust_appendrel_attrs(root,
  30.                                  (Node *) parse,
  31.                                  appinfo);//大约分配了300K
  33.         /*
  34.          * The rowMarks list might contain references to subquery RTEs, so
  35.          * make a copy that we can apply ChangeVarNodes to. (Fortunately, the
  36.          * executor doesn't need to see the modified copies --- we can just
  37.          * pass it the original rowMarks list.)
  38.          */
  39.         subroot.rowMarks = (List *) copyObject(root->rowMarks);
  41.         /*
  42.          * The append_rel_list likewise might contain references to subquery
  43.          * RTEs (if any subqueries were flattenable UNION ALLs). So prepare
  44.          * to apply ChangeVarNodes to that, too.
  45.          */
  46.         subroot.append_rel_list = (List *) copyObject(root->append_rel_list);//大约分配了400K
  48.         /*
  49.          * Add placeholders to the child Query's rangetable list to fill the
  50.          * RT indexes already reserved for subqueries in previous children.
  51.          * These won't be referenced, so there's no need to make them very
  52.          * valid-looking.
  53.          */
  54.         while (list_length(subroot.parse->rtable) list_length(final_rtable))
  55.             subroot.parse->rtable = lappend(subroot.parse->rtable,
  56.                                             makeNode(RangeTblEntry));
  57. ...
  58. }

4. select为什么没有问题？

select和update走的是不同的分支，没有走上面的inheritance_planner()函数，内存消耗只有7M。
1000个分区的select
sql:

点击(此处)折叠或打开

  1. db1000=# select * from maintb where id =3;
  2.  id | name
  3. ----+------------
  4.   3 | aaaaa12345
  5.   3 | aaaaa12345
  6.   3 | aaaaa12345
  7.   3 | aaaaa12345
  8.   3 | aaaaa12345
  9.   3 | aaaaa12345
  10.   3 | aaaaa12345
  11.   3 | aaaaa12345
  12.   3 | aaaaa12345
  13.   3 | aaaaa12345
  14.   3 | aaaaa12345
  15.   3 | aaaaa12345
  16.   3 | aaaaa12345
  17.   3 | aaaaa12345
  18.   3 | aaaaa12345
  19.   3 | aaaaa12345
  20.   3 | aaaaa12345
  21.   3 | aaaaa12345
  22.   3 | aaaaa12345
  23. (19 rows)
生成 执行计划消耗的内存:7M

点击(此处)折叠或打开

  1. MessageContext: 8192 total in 1 blocks; 1864 free (1 chunks); 6328 used
  2. MessageContext: 8388608 total in 11 blocks; 1176776 free (6 chunks); 7211832 used
update的调用栈：
点击(
此处
)折叠或打开
  1. (gdb) bt
  2. #0 inheritance_planner (root=0x1fd6548) at planner.c:787
  3. #1 0x00000000005fe7c0 in subquery_planner (glob=0x1fd64b8, parse=0x1fd59e0, parent_root=value optimized out>,
  4.     hasRecursion=value optimized out>, tuple_fraction=0, subroot=0x7fffbf4c70b8) at planner.c:569
  5. #2 0x00000000005fe935 in standard_planner (parse=0x1fd59e0, cursorOptions=0, boundParams=0x0) at planner.c:210
  6. #3 0x00000000006784fa in pg_plan_query (querytree=value optimized out>, cursorOptions=value optimized out>,
  7.     boundParams=value optimized out>) at postgres.c:750
  8. #4 0x00000000006785e4 in pg_plan_queries (querytrees=value optimized out>, cursorOptions=0, boundParams=0x0)
  9.     at postgres.c:809
  10. #5 0x0000000000678bcc in exec_simple_query (
  11.     query_string=0x1fd4b30 "update maintb set name='aaaaa12345' where id =3;") at postgres.c:974
  12. #6 0x000000000067a179 in PostgresMain (argc=value optimized out>, argv=value optimized out>,
  13.     dbname=0x1f70fb0 "db1000", username=value optimized out>) at postgres.c:4016
  14. #7 0x00000000006283cb in BackendRun (argc=value optimized out>, argv=value optimized out>) at postmaster.c:4123
  15. #8 BackendStartup (argc=value optimized out>, argv=value optimized out>) at postmaster.c:3797
  16. #9 ServerLoop (argc=value optimized out>, argv=value optimized out>) at postmaster.c:1576
  17. #10 PostmasterMain (argc=value optimized out>, argv=value optimized out>) at postmaster.c:1223
  18. #11 0x00000000005c0cd8 in main (argc=3, argv=0x1f6fa10) at main.c:227
select的调用栈:

点击(此处)折叠或打开

  1. (gdb) bt
  2. #0 grouping_planner (root=0x1fd6568, tuple_fraction=0) at planner.c:1080
  3. #1 0x00000000005fe5b4 in subquery_planner (glob=0x1fd64d8, parse=0x1fd5b20, parent_root=value optimized out>,
  4.     hasRecursion=value optimized out>, tuple_fraction=0, subroot=0x7fffbf4c70b8) at planner.c:572
  5. #2 0x00000000005fe935 in standard_planner (parse=0x1fd5b20, cursorOptions=0, boundParams=0x0) at planner.c:210
  6. #3 0x00000000006784fa in pg_plan_query (querytree=value optimized out>, cursorOptions=value optimized out>,
  7.     boundParams=value optimized out>) at postgres.c:750
  8. #4 0x00000000006785e4 in pg_plan_queries (querytrees=value optimized out>, cursorOptions=0, boundParams=0x0)
  9.     at postgres.c:809
  10. #5 0x0000000000678bcc in exec_simple_query (query_string=0x1fd4b30 "select * from maintb where id =3;")
  11.     at postgres.c:974
  12. #6 0x000000000067a179 in PostgresMain (argc=value optimized out>, argv=value optimized out>,
  13.     dbname=0x1f70fb0 "db1000", username=value optimized out>) at postgres.c:4016
  14. #7 0x00000000006283cb in BackendRun (argc=value optimized out>, argv=value optimized out>) at postmaster.c:4123
  15. #8 BackendStartup (argc=value optimized out>, argv=value optimized out>) at postmaster.c:3797
  16. #9 ServerLoop (argc=value optimized out>, argv=value optimized out>) at postmaster.c:1576
  17. #10 PostmasterMain (argc=value optimized out>, argv=value optimized out>) at postmaster.c:1223
  18. #11 0x00000000005c0cd8 in main (argc=3, argv=0x1f6fa10) at main.c:227

5.最后

按目前PG对分区表的处理逻辑，确实处理不了很多分区的情况。（但是update的那2处内存分配应该是可以优化的）。

6.参考

http://blog.chinaunix.net/xmlrpc.php?r=blog/article&uid=20726500&id=4732253

转载地址：http://nfxpl.baihongyu.com/

你可能感兴趣的文章
综合布线系统的构成
查看>>
计算机硬件 — 计算机简介
查看>>
关于重写session实现的时候可能会导至nginx 502的问题
查看>>
7z(p7zip)压缩软件在Linux下的安装和使用
查看>>
jetbrick-template 1.1.0 发布，支持 #tag, #macro, layout
查看>>
TCP的六个控制位
查看>>
进制转换
查看>>
我的友情链接
查看>>
新书上市：《FLUENT 14.0超级学习手册》
查看>>
mysql数据库query cache
查看>>
使用docker commit 来扩展一个image
查看>>
jsp 防止sql注入 之 preparestatement篇（转载）
查看>>
Linux之Ansible入门用法（实验解析）
查看>>
Linux系统如何在开机时修改root密码
查看>>
Anychat的绝对路径与相对路径
查看>>
我的友情链接
查看>>
如何使用网络库实现应用级消息收发
查看>>
Single Area OSPF
查看>>
rhel6之yum
查看>>
selenium+ant+testng测试框架简单介绍
查看>>
喝酒易醉，品茶养心，人生如梦，品茶悟道，何以解忧？唯有杜康！-- 愿君每日到此一游！
当前时间: 2025-02-05 05:05:19   当前IP: 3.143.246.245   联系邮箱:javaeecc@qq.com     Copyright © 2020 - 2022 baihongyu.com 京ICP备2021015314号-2
强烈建议你试试无所不能的CHAT-GPT，快点击我
强烈建议你试试无所不能的CHAT-GPT，快点击我