[Pgpool-general] hanging pgpool processes during massive UPDATEs
Felix J. Ogris
fjo-lists at ogris.de
Thu Oct 11 07:38:11 UTC 2007
Yoshiyuki Asaba (y-asaba at sraoss.co.jp) wrote:
> Could you show us pgpool.conf and backtrace?
>
> % gdb -p <PID> --batch --ex bt
Hi,
pgpool.conf:
listen_addresses = '*'
port = 5432
socket_dir = '/tmp'
pcp_port = 9898
pcp_socket_dir = '/tmp'
pcp_timeout = 10
num_init_children = 256
child_life_time = 300
connection_life_time = 300
child_max_connection = 10
logdir = '/var/run'
print_timestamp = true
connection_cache = true
health_check_timeout = 20
health_check_period = 30
health_check_user = pgpool
ignore_leading_white_space = true
log_statement = false
log_hostname = false
log_connections = false
enable_pool_hba = false
replication_mode = true
replication_strict = true
replication_timeout = 60000
load_balance_mode = true
replication_stop_on_mismatch = false
insert_lock = true
backend_hostname0 = '10.0.0.21'
backend_port0 = 5432
backend_weight0 = 1
backend_hostname1 = '10.0.0.22'
backend_port1 = 5432
backend_weight1 = 1
backend_hostname2 = '10.0.0.23'
backend_port2 = 5432
backend_weight2 = 1
Backtrace as with `gdb -p <PID>` and manually "bt" cmd (gdb -p <PID> --batch
--ex bt doesn't work with my 'GNU gdb Red Hat Linux (6.3.0.0-1.143.el4rh)'):
(gdb) bt
#0 0x0000000000443e87 in base_yylex () at scan.c:1080
#1 0x000000000042d638 in sql_yylex () at parser.c:87
#2 0x00000000004393aa in yyparse () at gram.c:11084
#3 0x000000000042d5f4 in raw_parser (str=0x5a36f0 "\n\t\t\tUPDATE
\n\t\t\t\t\"communityGuestbook3\" \n\t\t\tSET \n\t\t\t\t\"deleted\"=1
\n\t\t\tWHERE \n\t\t\t\t\"communityGuestbookIdx='132536334';\n\t\t")
at parser.c:53
#4 0x000000000041351a in SimpleQuery (frontend=0x5ad8d0, backend=0x5a02a0,
query=0x20 <Address 0x20 out of bounds>) at pool_process_query.c:828
#5 0x00000000004151f8 in ProcessFrontendResponse (frontend=0x5ad8d0,
backend=0x5a02a0) at pool_process_query.c:2566
#6 0x0000000000415ed6 in pool_process_query (frontend=0x5ad8d0,
backend=0x5a02a0, connection_reuse=0,
first_ready_for_query_received=Variable "first_ready_for_query_received" is
not available.
) at pool_process_query.c:373
#7 0x0000000000407977 in do_child (unix_fd=3, inet_fd=4) at child.c:415
#8 0x00000000004034c1 in fork_a_child (unix_fd=3, inet_fd=4, id=207) at
main.c:717
#9 0x0000000000404753 in reaper () at main.c:1375
#10 0x0000000000404b66 in pool_sleep (second=Variable "second" is not
available.
) at main.c:1499
#11 0x00000000004051bb in main (argc=Variable "argc" is not available.
) at main.c:506
> Could you show us a result of the following command?
>
> % grep 559 log | grep -A1 statement2
Ok, once again I have scrambled all statements for the sake of our
customers:
2007-10-10 15:20:52 DEBUG: pid 559: statement2: SELECT * FROM ...
2007-10-10 15:20:52 DEBUG: pid 559: waiting for backend 2 completing the
query
--
2007-10-10 15:20:52 DEBUG: pid 559: statement2: SELECT
*,(NOW()-"lastModified") as days FROM ...
2007-10-10 15:20:52 DEBUG: pid 559: waiting for backend 2 completing the
query
--
2007-10-10 15:20:52 DEBUG: pid 559: statement2: SELECT "fromUser" FROM ...
LIMIT 1 OFFSET 0
2007-10-10 15:20:52 DEBUG: pid 559: waiting for backend 2 completing the
query
--
2007-10-10 15:20:52 DEBUG: pid 559: statement2: RESET ALL
2007-10-10 15:20:52 DEBUG: pid 559: waiting for backend 0 completing the
query
--
2007-10-10 15:20:52 DEBUG: pid 559: statement2: SET SESSION AUTHORIZATION
DEFAULT
2007-10-10 15:20:52 DEBUG: pid 559: waiting for backend 0 completing the
query
--
2007-10-10 15:20:52 DEBUG: pid 559: statement2: SELECT * FROM ...
2007-10-10 15:20:52 DEBUG: pid 559: waiting for backend 0 completing the
query
--
2007-10-10 15:20:52 DEBUG: pid 559: statement2: SELECT
*,(NOW()-"lastModified") as days FROM ...
2007-10-10 15:20:52 DEBUG: pid 559: waiting for backend 0 completing the
query
--
2007-10-10 15:20:52 DEBUG: pid 559: statement2: SELECT "fromUser" FROM ...
LIMIT 1 OFFSET 0
2007-10-10 15:20:52 DEBUG: pid 559: waiting for backend 0 completing the
query
--
2007-10-10 15:20:53 DEBUG: pid 559: statement2: RESET ALL
2007-10-10 15:20:53 DEBUG: pid 559: waiting for backend 0 completing the
query
--
2007-10-10 15:20:53 DEBUG: pid 559: statement2: SET SESSION AUTHORIZATION
DEFAULT
2007-10-10 15:20:53 DEBUG: pid 559: waiting for backend 0 completing the
query
--
2007-10-10 15:20:53 DEBUG: pid 559: statement2: SELECT * FROM ...
2007-10-10 15:20:53 DEBUG: pid 559: waiting for backend 0 completing the
query
--
2007-10-10 15:20:53 DEBUG: pid 559: statement2: SELECT
*,(NOW()-"lastModified") as days FROM ...
2007-10-10 15:20:53 DEBUG: pid 559: waiting for backend 0 completing the
query
--
2007-10-10 15:20:53 DEBUG: pid 559: statement2: SELECT "fromUser" FROM ...
LIMIT 1 OFFSET 0
2007-10-10 15:20:53 DEBUG: pid 559: waiting for backend 0 completing the
query
--
2007-10-10 15:20:53 DEBUG: pid 559: statement2: RESET ALL
2007-10-10 15:20:53 DEBUG: pid 559: waiting for backend 0 completing the
query
--
2007-10-10 15:20:53 DEBUG: pid 559: statement2: SET SESSION AUTHORIZATION
DEFAULT
2007-10-10 15:20:53 DEBUG: pid 559: waiting for backend 0 completing the
query
--
2007-10-10 15:20:54 DEBUG: pid 559: statement2: SELECT * FROM "session"
WHERE "SID" = ...
2007-10-10 15:20:54 DEBUG: pid 559: waiting for backend 0 completing the
query
--
2007-10-10 15:20:54 DEBUG: pid 559: statement2: SELECT * FROM ...
2007-10-10 15:20:54 DEBUG: pid 559: waiting for backend 0 completing the
query
--
2007-10-10 15:20:54 DEBUG: pid 559: statement2: SELECT * FROM ...
2007-10-10 15:20:54 DEBUG: pid 559: waiting for backend 0 completing the
query
--
2007-10-10 15:20:54 DEBUG: pid 559: statement2:
2007-10-10 15:20:54 DEBUG: pid 559: waiting for backend 0 completing the
query
--
2007-10-10 15:20:54 DEBUG: pid 559: statement2:
2007-10-10 15:20:54 DEBUG: pid 559: waiting for backend 0 completing the
query
--
2007-10-10 15:20:54 DEBUG: pid 559: statement2:
2007-10-10 15:20:54 DEBUG: pid 559: waiting for backend 0 completing the
query
--
2007-10-10 15:20:54 DEBUG: pid 559: statement2:
2007-10-10 15:20:54 DEBUG: pid 559: waiting for backend 0 completing the
query
--
2007-10-10 15:20:54 DEBUG: pid 559: statement2:
2007-10-10 15:20:54 DEBUG: pid 559: waiting for backend 0 completing the
query
--
2007-10-10 15:20:54 DEBUG: pid 559: statement2:
2007-10-10 23:35:07 DEBUG: pid 559: child receives shutdown request signal 2
(Shutdown at 23:25 has manually been requested by us.)
Thanks for your help,
Felix
More information about the Pgpool-general
mailing list