From: Rural Hunter <ruralhunter@gmail.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Very slow planning performance on partition table
Date: Wed, 30 Jul 2014 18:03:30 +0800
Message-ID: <53D8C2F2.3080105@gmail.com> (raw)
In-Reply-To: <CAMkU=1wHC_w6D2kc1V6y1PDkRxDV3B+1fJBk3Z2L=SprGw7omw@mail.gmail.com>
References: <53CFB6BC.4050606@gmail.com>
<25010.1406253203@sss.pgh.pa.us>
<53D26875.2080604@gmail.com>
<53D51E49.102@gmail.com>
<24564.1406478487@sss.pgh.pa.us>
<CAMkU=1ydmpt9XRMxt0sPNnQsXEoF_c7bgp2kHxtDbPNGg5Vj5w@mail.gmail.com>
<53D7597E.2080209@gmail.com>
<CAMkU=1wHC_w6D2kc1V6y1PDkRxDV3B+1fJBk3Z2L=SprGw7omw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
I think I understand what happened now. I have another monitor script
runs periodically and calls pg_cancel_backend and pg_terminate_backend
for those hanging update sqls. However for some unkown reason the cancle
and termiante command doesn't work at pgsql side for those update sqls.
But I think pgbouncer side was notified by cancel or terminate command.
It then drops old connections and creates new ones while those old
connections still hang at pgsql side. That's why the connection status
shows CLOST_WAIT and there are more processes at pgsql side than
pgbouncer defined . So the root cause is still at pgsql side. It
shouldn't hang there. What the hanging process was doing is in my
previous posts. There many same concurrent sql which updates a
partitioned table witouth partition key specified in conditions. The gdb
trace shows this:
(gdb) bt
#0 0x00007f8cea310db7 in semop () from /lib/x86_64-linux-gnu/libc.so.6
#1 0x00000000005f97d3 in PGSemaphoreLock ()
#2 0x0000000000638153 in LWLockAcquire ()
#3 0x00000000004a90d0 in ginTraverseLock ()
#4 0x00000000004a9d0b in ginFindLeafPage ()
#5 0x00000000004a8377 in ginInsertItemPointers ()
#6 0x00000000004a4548 in ginEntryInsert ()
#7 0x00000000004ae687 in ginInsertCleanup ()
#8 0x00000000004af3d6 in ginHeapTupleFastInsert ()
#9 0x00000000004a4ab1 in gininsert ()
#10 0x0000000000709b15 in FunctionCall6Coll ()
#11 0x000000000047b6b7 in index_insert ()
#12 0x000000000057f475 in ExecInsertIndexTuples ()
#13 0x000000000058bf07 in ExecModifyTable ()
#14 0x00000000005766e3 in ExecProcNode ()
#15 0x0000000000575ad4 in standard_ExecutorRun ()
#16 0x000000000064718f in ProcessQuery ()
#17 0x00000000006473b7 in PortalRunMulti ()
#18 0x0000000000647e8a in PortalRun ()
#19 0x0000000000645160 in PostgresMain ()
#20 0x000000000060459e in ServerLoop ()
#21 0x00000000006053bc in PostmasterMain ()
#22 0x00000000005a686b in main ()
(gdb) q
It will just hangs there forever and finally blocks all other update
sqls if I don't stop pgbouncer. When this happens, all the cpus will be
utilized by those hanging processes and the server load is very very
high. It keeps at serveral hundreds comparing with about 20 normally
which causes the performance problem for all tasks on the server.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ruralhunter@gmail.com
Subject: Re: Very slow planning performance on partition table
In-Reply-To: <53D8C2F2.3080105@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox