View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0000270Pgpool-IIBugpublic2016-12-14 13:242017-05-01 13:48
Reportermarin_mtb Assigned Tot-ishii  
PriorityurgentSeverityblockReproducibilityalways
Status closedResolutionopen 
Platformx86_64OSubuntuOS Version14.04-1
Product Version3.5.4 
Summary0000270: pgpool system hung cause parallel many query
DescriptionBackEnd PostgreSQL 9.3(9.3.15-0ubuntu0.14.04)
Additional PostGIS(postgresql-9.3-postgis-2.1/2.1.2+dfsg-2ubuntu0.1)
tileserver(https://github.com/tilezen/vector-datasource/wiki/Mapzen-Vector-Tile-Service)
pgpool II is hungup(can't kill SIGTERM child processes so SIGQUIT kill process).
child process gdb debug below.

# gdb /usr/local/bin/pgpool -p 7686
GNU gdb (Ubuntu 7.7.1-0ubuntu5~14.04.2) 7.7.1
Copyright (C) 2014 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law. Type "show copying"
and "show warranty" for details.
This GDB was configured as "x86_64-linux-gnu".
Type "show configuration" for configuration details.
For bug reporting instructions, please see:
<http://www.gnu.org/software/gdb/bugs/>.
Find the GDB manual and other documentation resources online at:
<http://www.gnu.org/software/gdb/documentation/>.
For help, type "help".
Type "apropos word" to search for commands related to "word"...
Reading symbols from /usr/local/bin/pgpool...done.
Attaching to program: /usr/local/bin/pgpool, process 7686
Reading symbols from /usr/lib/libpq.so.5...(no debugging symbols found)...done.
Loaded symbols for /usr/lib/libpq.so.5
Reading symbols from /lib/x86_64-linux-gnu/libpthread.so.0...Reading symbols from /usr/lib/debug//lib/x86_64-linux-gnu/libpthread-2.19.so...done.
done.
[Thread debugging using libthread_db enabled]
Using host libthread_db library "/lib/x86_64-linux-gnu/libthread_db.so.1".
Loaded symbols for /lib/x86_64-linux-gnu/libpthread.so.0
Reading symbols from /usr/lib/x86_64-linux-gnu/libmemcached.so.10...(no debugging symbols found)...done.
Loaded symbols for /usr/lib/x86_64-linux-gnu/libmemcached.so.10
Reading symbols from /lib/x86_64-linux-gnu/libcrypt.so.1...Reading symbols from /usr/lib/debug//lib/x86_64-linux-gnu/libcrypt-2.19.so...done.
done.
Loaded symbols for /lib/x86_64-linux-gnu/libcrypt.so.1
Reading symbols from /lib/x86_64-linux-gnu/libm.so.6...Reading symbols from /usr/lib/debug//lib/x86_64-linux-gnu/libm-2.19.so...done.
done.
Loaded symbols for /lib/x86_64-linux-gnu/libm.so.6
Reading symbols from /lib/x86_64-linux-gnu/libc.so.6...Reading symbols from /usr/lib/debug//lib/x86_64-linux-gnu/libc-2.19.so...done.
done.
Loaded symbols for /lib/x86_64-linux-gnu/libc.so.6
Reading symbols from /lib/x86_64-linux-gnu/libssl.so.1.0.0...(no debugging symbols found)...done.
Loaded symbols for /lib/x86_64-linux-gnu/libssl.so.1.0.0
Reading symbols from /lib/x86_64-linux-gnu/libcrypto.so.1.0.0...(no debugging symbols found)...done.
Loaded symbols for /lib/x86_64-linux-gnu/libcrypto.so.1.0.0
Reading symbols from /usr/lib/x86_64-linux-gnu/libkrb5.so.3...(no debugging symbols found)...done.
Loaded symbols for /usr/lib/x86_64-linux-gnu/libkrb5.so.3
Reading symbols from /lib/x86_64-linux-gnu/libcom_err.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib/x86_64-linux-gnu/libcom_err.so.2
Reading symbols from /usr/lib/x86_64-linux-gnu/libgssapi_krb5.so.2...(no debugging symbols found)...done.
Loaded symbols for /usr/lib/x86_64-linux-gnu/libgssapi_krb5.so.2
Reading symbols from /usr/lib/x86_64-linux-gnu/libldap_r-2.4.so.2...(no debugging symbols found)...done.
Loaded symbols for /usr/lib/x86_64-linux-gnu/libldap_r-2.4.so.2
Reading symbols from /lib64/ld-linux-x86-64.so.2...Reading symbols from /usr/lib/debug//lib/x86_64-linux-gnu/ld-2.19.so...done.
done.
Loaded symbols for /lib64/ld-linux-x86-64.so.2
Reading symbols from /usr/lib/x86_64-linux-gnu/libstdc++.so.6...(no debugging symbols found)...done.
Loaded symbols for /usr/lib/x86_64-linux-gnu/libstdc++.so.6
Reading symbols from /lib/x86_64-linux-gnu/libgcc_s.so.1...(no debugging symbols found)...done.
Loaded symbols for /lib/x86_64-linux-gnu/libgcc_s.so.1
Reading symbols from /lib/x86_64-linux-gnu/libdl.so.2...Reading symbols from /usr/lib/debug//lib/x86_64-linux-gnu/libdl-2.19.so...done.
done.
Loaded symbols for /lib/x86_64-linux-gnu/libdl.so.2
Reading symbols from /usr/lib/x86_64-linux-gnu/libk5crypto.so.3...(no debugging symbols found)...done.
Loaded symbols for /usr/lib/x86_64-linux-gnu/libk5crypto.so.3
Reading symbols from /usr/lib/x86_64-linux-gnu/libkrb5support.so.0...(no debugging symbols found)...done.
Loaded symbols for /usr/lib/x86_64-linux-gnu/libkrb5support.so.0
Reading symbols from /lib/x86_64-linux-gnu/libkeyutils.so.1...(no debugging symbols found)...done.
Loaded symbols for /lib/x86_64-linux-gnu/libkeyutils.so.1
Reading symbols from /lib/x86_64-linux-gnu/libresolv.so.2...Reading symbols from /usr/lib/debug//lib/x86_64-linux-gnu/libresolv-2.19.so...done.
done.
Loaded symbols for /lib/x86_64-linux-gnu/libresolv.so.2
Reading symbols from /usr/lib/x86_64-linux-gnu/liblber-2.4.so.2...(no debugging symbols found)...done.
Loaded symbols for /usr/lib/x86_64-linux-gnu/liblber-2.4.so.2
Reading symbols from /usr/lib/x86_64-linux-gnu/libsasl2.so.2...(no debugging symbols found)...done.
Loaded symbols for /usr/lib/x86_64-linux-gnu/libsasl2.so.2
Reading symbols from /usr/lib/x86_64-linux-gnu/libgssapi.so.3...(no debugging symbols found)...done.
Loaded symbols for /usr/lib/x86_64-linux-gnu/libgssapi.so.3
Reading symbols from /usr/lib/x86_64-linux-gnu/libgnutls.so.26...(no debugging symbols found)...done.
Loaded symbols for /usr/lib/x86_64-linux-gnu/libgnutls.so.26
Reading symbols from /lib/x86_64-linux-gnu/libgcrypt.so.11...(no debugging symbols found)...done.
Loaded symbols for /lib/x86_64-linux-gnu/libgcrypt.so.11
Reading symbols from /usr/lib/x86_64-linux-gnu/libheimntlm.so.0...(no debugging symbols found)...done.
Loaded symbols for /usr/lib/x86_64-linux-gnu/libheimntlm.so.0
Reading symbols from /usr/lib/x86_64-linux-gnu/libkrb5.so.26...(no debugging symbols found)...done.
Loaded symbols for /usr/lib/x86_64-linux-gnu/libkrb5.so.26
Reading symbols from /usr/lib/x86_64-linux-gnu/libasn1.so.8...(no debugging symbols found)...done.
Loaded symbols for /usr/lib/x86_64-linux-gnu/libasn1.so.8
Reading symbols from /usr/lib/x86_64-linux-gnu/libhcrypto.so.4...(no debugging symbols found)...done.
Loaded symbols for /usr/lib/x86_64-linux-gnu/libhcrypto.so.4
Reading symbols from /usr/lib/x86_64-linux-gnu/libroken.so.18...(no debugging symbols found)...done.
Loaded symbols for /usr/lib/x86_64-linux-gnu/libroken.so.18
Reading symbols from /lib/x86_64-linux-gnu/libz.so.1...(no debugging symbols found)...done.
Loaded symbols for /lib/x86_64-linux-gnu/libz.so.1
Reading symbols from /usr/lib/x86_64-linux-gnu/libtasn1.so.6...(no debugging symbols found)...done.
Loaded symbols for /usr/lib/x86_64-linux-gnu/libtasn1.so.6
Reading symbols from /usr/lib/x86_64-linux-gnu/libp11-kit.so.0...(no debugging symbols found)...done.
Loaded symbols for /usr/lib/x86_64-linux-gnu/libp11-kit.so.0
Reading symbols from /lib/x86_64-linux-gnu/libgpg-error.so.0...(no debugging symbols found)...done.
Loaded symbols for /lib/x86_64-linux-gnu/libgpg-error.so.0
Reading symbols from /usr/lib/x86_64-linux-gnu/libwind.so.0...(no debugging symbols found)...done.
Loaded symbols for /usr/lib/x86_64-linux-gnu/libwind.so.0
Reading symbols from /usr/lib/x86_64-linux-gnu/libheimbase.so.1...(no debugging symbols found)...done.
Loaded symbols for /usr/lib/x86_64-linux-gnu/libheimbase.so.1
Reading symbols from /usr/lib/x86_64-linux-gnu/libhx509.so.5...(no debugging symbols found)...done.
Loaded symbols for /usr/lib/x86_64-linux-gnu/libhx509.so.5
Reading symbols from /usr/lib/x86_64-linux-gnu/libsqlite3.so.0...(no debugging symbols found)...done.
Loaded symbols for /usr/lib/x86_64-linux-gnu/libsqlite3.so.0
Reading symbols from /usr/lib/x86_64-linux-gnu/libffi.so.6...(no debugging symbols found)...done.
Loaded symbols for /usr/lib/x86_64-linux-gnu/libffi.so.6
Reading symbols from /lib/x86_64-linux-gnu/libnss_files.so.2...Reading symbols from /usr/lib/debug//lib/x86_64-linux-gnu/libnss_files-2.19.so...done.
done.
Loaded symbols for /lib/x86_64-linux-gnu/libnss_files.so.2
0x00007f85a3e6fc33 in __select_nocancel () at ../sysdeps/unix/syscall-template.S:81
81 ../sysdeps/unix/syscall-template.S: No such file or directory.
(gdb) bt
#0 0x00007f85a3e6fc33 in __select_nocancel () at ../sysdeps/unix/syscall-template.S:81
0000001 0x00000000004275af in read_packets_and_process (frontend=frontend@entry=0xdfdca0, backend=backend@entry=0xdfcc40,
    reset_request=reset_request@entry=0, state=state@entry=0x7ffc99603804, num_fields=num_fields@entry=0x7ffc99603802,
    cont=cont@entry=0x7ffc9960380c "\001") at protocol/pool_process_query.c:4646
0000002 0x0000000000428663 in pool_process_query (frontend=0xdfdca0, backend=0xdfcc40, reset_request=reset_request@entry=0)
    at protocol/pool_process_query.c:275
0000003 0x0000000000422d99 in do_child (fds=fds@entry=0xdf0b60) at protocol/child.c:377
0000004 0x0000000000407d55 in fork_a_child (fds=0xdf0b60, id=3) at main/pgpool_main.c:723
0000005 0x000000000040c73e in PgpoolMain (discard_status=discard_status@entry=0 '\000',
    clear_memcache_oidmaps=clear_memcache_oidmaps@entry=0 '\000') at main/pgpool_main.c:340
0000006 0x0000000000406514 in main (argc=<optimized out>, argv=<optimized out>) at main/main.c:305
(gdb) p oid
No symbol "oid" in current context.
(gdb) p
The history is empty.
(gdb) bt
#0 0x00007f85a3e6fc33 in __select_nocancel () at ../sysdeps/unix/syscall-template.S:81
0000001 0x00000000004275af in read_packets_and_process (frontend=frontend@entry=0xdfdca0, backend=backend@entry=0xdfcc40,
    reset_request=reset_request@entry=0, state=state@entry=0x7ffc99603804, num_fields=num_fields@entry=0x7ffc99603802,
    cont=cont@entry=0x7ffc9960380c "\001") at protocol/pool_process_query.c:4646
0000002 0x0000000000428663 in pool_process_query (frontend=0xdfdca0, backend=0xdfcc40, reset_request=reset_request@entry=0)
    at protocol/pool_process_query.c:275
0000003 0x0000000000422d99 in do_child (fds=fds@entry=0xdf0b60) at protocol/child.c:377
0000004 0x0000000000407d55 in fork_a_child (fds=0xdf0b60, id=3) at main/pgpool_main.c:723
0000005 0x000000000040c73e in PgpoolMain (discard_status=discard_status@entry=0 '\000',
    clear_memcache_oidmaps=clear_memcache_oidmaps@entry=0 '\000') at main/pgpool_main.c:340
0000006 0x0000000000406514 in main (argc=<optimized out>, argv=<optimized out>) at main/main.c:305
(gdb) q
Steps To ReproduceFrom other server/client
curl http://localhost:8080/buildings,landuse,water,roads/17/116388/51605.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116387/51605.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116388/51604.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116388/51606.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116389/51605.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116389/51604.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116387/51604.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116389/51606.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116387/51606.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116390/51605.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116386/51605.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116388/51607.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116388/51603.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116390/51604.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116386/51606.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116390/51606.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116387/51607.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116386/51604.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116389/51607.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116387/51603.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116389/51603.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116386/51607.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116390/51603.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116386/51603.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116390/51607.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116391/51605.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116388/51608.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116388/51602.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116385/51605.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116387/51608.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116389/51608.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116391/51606.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116391/51604.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116389/51602.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116385/51604.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116387/51602.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116385/51606.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116385/51603.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116391/51607.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116386/51608.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116390/51602.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116390/51608.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116385/51607.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116391/51603.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116386/51602.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116391/51608.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116391/51602.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116385/51602.json &
curl http://localhost:8080/buildings,landuse,water,roads/17/116385/51608.json &
Additional Informationrelcache_size = 5120
                                   # Number of relation cache
                                   # entry. If you see frequently:
                                   # "pool_search_relcache: cache replacement happend"
                                   # in the pgpool log, you might want to increate this number.

check_temp_table = on
                                   # If on, enable temporary table check in SELECT statements.
                                   # This initiates queries against system catalog of primary/master
                                   # thus increases load of master.
                                   # If you are absolutely sure that your system never uses temporary tables
                                   # and you want to save access to primary/master, you could turn this off.
                                   # Default is on.

check_unlogged_table = on
                                   # If on, enable unlogged table check in SELECT statements.
                                   # This initiates queries against system catalog of primary/master
                                   # thus increases load of master.
                                   # If you are absolutely sure that your system never uses unlogged tables
                                   # and you want to save access to primary/master, you could turn this off.
                                   # Default is on.

#------------------------------------------------------------------------------
# IN MEMORY QUERY MEMORY CACHE
#------------------------------------------------------------------------------
memory_cache_enabled = on
                                                                   # If on, use the memory cache functionality, off by default
#memqcache_method = 'shmem'
memqcache_method = 'memcached'
                                                                   # Cache storage method. either 'shmem'(shared memory) or
                                                                   # 'memcached'. 'shmem' by default
                                   # (change requires restart)
memqcache_memcached_host = 'localhost'
                                                                   # Memcached host name or IP address. Mandatory if
                                                                   # memqcache_method = 'memcached'.
                                                                   # Defaults to localhost.
                                   # (change requires restart)
memqcache_memcached_port = 11211
                                                                   # Memcached port number. Mondatory if memqcache_method = 'memca
ched'.
                                                                   # Defaults to 11211.
                                   # (change requires restart)
memqcache_total_size = 8192000000
                                                                   # Total memory size in bytes for storing memory cache.
                                                                   # Mandatory if memqcache_method = 'shmem'.
                                                                   # Defaults to 64MB.
                                   # (change requires restart)
memqcache_max_num_cache = 10000000
                                                                   # Total number of cache entries. Mandatory
                                                                   # if memqcache_method = 'shmem'.
                                                                   # Each cache entry consumes 48 bytes on shared memory.
                                                                   # Defaults to 1,000,000(45.8MB).
                                   # (change requires restart)
memqcache_expire = 0
                                                                   # Memory cache entry life time specified in seconds.
                                                                   # 0 means infinite life time. 0 by default.
                                   # (change requires restart)
memqcache_auto_cache_invalidation = on
                                                                   # If on, invalidation of query cache is triggered by correspond
ing
                                                                   # DDL/DML/DCL(and memqcache_expire). If off, it is only trigge
red
                                                                   # by memqcache_expire. on by default.
                                   # (change requires restart)
memqcache_maxcache = 40960000
                                                                   # Maximum SELECT result size in bytes.
                                                                   # Must be smaller than memqcache_cache_block_size. Defaults to
400KB.
                                   # (change requires restart)
memqcache_cache_block_size = 40960000
                                                                   # Cache block size in bytes. Mandatory if memqcache_method = 's
hmem'.
                                                                   # Defaults to 1MB.
                                   # (change requires restart)
memqcache_oiddir = '/var/log/pgpool/oiddir'
                                                                   # Temporary work directory to record table oids
                                   # (change requires restart)
white_memqcache_table_list = ''
                                   # Comma separated list of table names to memcache
                                   # that don't write to database
                                   # Regexp are accepted
black_memqcache_table_list = ''
                                   # Comma separated list of table names not to memcache
                                   # that don't write to database
                                   # Regexp are accepted
TagsNo tags attached.

Activities

marin_mtb

2016-12-14 13:30

reporter   ~0001223

near bug:http://www.pgpool.net/mantisbt/view.php?id=265&nbn=2

marin_mtb

2016-12-15 17:18

reporter   ~0001224

Addtional
Dec 15 17:15:33 10-138-171-15 pgpool[18178]: [4-1] 2016-12-15 17:15:33: pid 18178: LOG: child process received shutdown request signal 3
Dec 15 17:15:33 10-138-171-15 pgpool[18178]: [5-1] 2016-12-15 17:15:33: pid 18178: WARNING: failed to lock semaphore error:"Invalid argument"
Dec 15 17:15:33 10-138-171-15 pgpool[18178]: [6-1] 2016-12-15 17:15:33: pid 18178: WARNING: failed to unlock semaphore error:"Invalid argument"

marin_mtb

2016-12-20 09:54

reporter   ~0001233

Can you speak Japanese?
Japanese speaking because peculiar.

t-ishii

2016-12-20 10:08

developer   ~0001234

Yes, I am a native Japanese speaker.

What do you mean by "peculiar" BTW?

marin_mtb

2016-12-20 10:39

reporter   ~0001235

英語苦手な物で…^^;。
概要：Postgres9.3(9.6でも確認済み)+Postgis2.1(2.3でも確認済み)をバックエンドにpgpoolを設定し、いっぺんに多数のクエリを投げるとハングする、と言う問題です。
フロントはPythonでXYZ座標をURLで受け取り、それをPostgreSQLにクエリとして投げ、返ってきた値を加工して出力する、と言う物です。
これを当初PostgreSQL直でアクセスしておりましたが、応答時間に制約がある問題から、オンメモリクエリキャッシュを利用できないか、と言う動機で使用を始めたところ、ハングする問題にぶち当たった、と言うところです。
オンメモリクエリキャッシュを無効(つまりただのハブ)状態にしても再現することから、オンメモリクエリキャッシュの問題ではない感じがします。
また、pgpool2を再起動しても復旧しますし、PostgreSQLを再起動でも復旧しますので、Postgresとpgpool2のコネクションが残ってしまう、それも再利用できないのが問題なのかな、と考えております。
不足情報がございましたらご連絡くださいませ。

t-ishii

2017-04-03 15:59

developer   ~0001403

大変フォローが遅くなりました。
単純に num_init_children を超えたセッションを同時に実行しようとしているということはないですか？

t-ishii

2017-04-03 16:01

developer   ~0001404

後は、最近のstable treeをお試しいただけますか？
最近ハングするバグを色々と直しているので。

t-ishii

2017-05-01 13:47

developer   ~0001486

特にフィードバックがないので、クローズとさせていただきます。

Issue History

Date Modified Username Field Change
2016-12-14 13:24 marin_mtb New Issue
2016-12-14 13:30 marin_mtb Note Added: 0001223
2016-12-15 17:18 marin_mtb Note Added: 0001224
2016-12-20 09:32 t-ishii Assigned To => t-ishii
2016-12-20 09:32 t-ishii Status new => assigned
2016-12-20 09:54 marin_mtb Note Added: 0001233
2016-12-20 10:08 t-ishii Note Added: 0001234
2016-12-20 10:39 marin_mtb Note Added: 0001235
2017-04-03 15:59 t-ishii Note Added: 0001403
2017-04-03 16:01 t-ishii Note Added: 0001404
2017-04-03 16:01 t-ishii Status assigned => feedback
2017-05-01 13:47 t-ishii Note Added: 0001486
2017-05-01 13:48 t-ishii Status feedback => closed