[Pgpool-general] pgpool-II-2.1-beta2 inserts stuck in parallel mode
Vlad Kosilov
vkosilov at contigo.com
Thu Jun 5 05:29:51 UTC 2008
I'm experimenting with pgpool-II-2.1-beta2 in parallel mode with two
back-end databases running pgsql-8.3.1, and 3rd host running pgpool and
pgsql-8.3.1 system/pgpool db.
I'm having with loading following file where only first insert
successfully gets through and second insert hangs.
/usr/local/pgsql-8.3.1/bin/psql -e -U postgres -h 192.168.0.102 -p 9999
-d MONSOONAUDIT -f ./tcp_audit_0.dmp
INSERT INTO tcp_audit_vlad (id, beacon_id, message, flow, "timestamp",
unique_id, byte_count) VALUES (531391045, 0, 'message_0', 'in',
1212303601, '352023005147018', 297);
INSERT 0 1
INSERT INTO tcp_audit_vlad (id, beacon_id, message, flow, "timestamp",
unique_id, byte_count) VALUES (531391046, 0, 'message_1', 'in',
1212303601, '352023005147018', 100);
... hangs here ..
cat ./tcp_audit_0.dmp
INSERT INTO tcp_audit_vlad (id, beacon_id, message, flow, "timestamp",
unique_id, byte_count) VALUES (531391045, 0, 'message_0', 'in',
1212303601, '352023005147018', 297);
INSERT INTO tcp_audit_vlad (id, beacon_id, message, flow, "timestamp",
unique_id, byte_count) VALUES (531391046, 0, 'message_1', 'in',
1212303601, '352023005147018', 100);
INSERT INTO tcp_audit_vlad (id, beacon_id, message, flow, "timestamp",
unique_id, byte_count) VALUES (531391048, 0, 'message_2', 'in',
1212303601, '352023005147018', 100);
here is pgpool debug output starting at the moment I run psql command:
2008-06-04 21:40:26 DEBUG: pid 17500: I am 17500 accept fd 6
2008-06-04 21:40:26 LOG: pid 17500: connection received:
host=192.168.0.200 port=60965
2008-06-04 21:40:26 DEBUG: pid 17500: Protocol Major: 3 Minor: 0
database: MONSOONAUDIT user: postgres
2008-06-04 21:40:26 DEBUG: pid 17500: new_connection: connecting 0 backend
2008-06-04 21:40:26 DEBUG: pid 17500: new_connection: connecting 1 backend
2008-06-04 21:40:26 DEBUG: pid 17500: pool_read_message_length: slot: 0
length: 8
2008-06-04 21:40:26 DEBUG: pid 17500: pool_read_message_length: slot: 1
length: 8
2008-06-04 21:40:26 DEBUG: pid 17500: pool_read_message_length2: master
slot: 0 length: 25
2008-06-04 21:40:26 DEBUG: pid 17500: pool_read_message_length2: master
slot: 1 length: 25
2008-06-04 21:40:26 DEBUG: pid 17500: 0 th backend: name:
client_encoding value: UTF8
2008-06-04 21:40:26 DEBUG: pid 17500: 1 th backend: name:
client_encoding value: UTF8
2008-06-04 21:40:26 DEBUG: pid 17500: pool_read_message_length2: master
slot: 0 length: 23
2008-06-04 21:40:26 DEBUG: pid 17500: pool_read_message_length2: master
slot: 1 length: 23
2008-06-04 21:40:26 DEBUG: pid 17500: 0 th backend: name: DateStyle
value: ISO, MDY
2008-06-04 21:40:26 DEBUG: pid 17500: 1 th backend: name: DateStyle
value: ISO, MDY
2008-06-04 21:40:26 DEBUG: pid 17500: pool_read_message_length2: master
slot: 0 length: 26
2008-06-04 21:40:26 DEBUG: pid 17500: pool_read_message_length2: master
slot: 1 length: 26
2008-06-04 21:40:26 DEBUG: pid 17500: 0 th backend: name:
integer_datetimes value: off
2008-06-04 21:40:26 DEBUG: pid 17500: 1 th backend: name:
integer_datetimes value: off
2008-06-04 21:40:26 DEBUG: pid 17500: pool_read_message_length2: master
slot: 0 length: 20
2008-06-04 21:40:26 DEBUG: pid 17500: pool_read_message_length2: master
slot: 1 length: 20
2008-06-04 21:40:26 DEBUG: pid 17500: 0 th backend: name: is_superuser
value: on
2008-06-04 21:40:26 DEBUG: pid 17500: 1 th backend: name: is_superuser
value: on
2008-06-04 21:40:26 DEBUG: pid 17500: pool_read_message_length2: master
slot: 0 length: 25
2008-06-04 21:40:26 DEBUG: pid 17500: pool_read_message_length2: master
slot: 1 length: 25
2008-06-04 21:40:26 DEBUG: pid 17500: 0 th backend: name:
server_encoding value: UTF8
2008-06-04 21:40:26 DEBUG: pid 17500: 1 th backend: name:
server_encoding value: UTF8
2008-06-04 21:40:26 DEBUG: pid 17500: pool_read_message_length2: master
slot: 0 length: 25
2008-06-04 21:40:26 DEBUG: pid 17500: pool_read_message_length2: master
slot: 1 length: 25
2008-06-04 21:40:26 DEBUG: pid 17500: 0 th backend: name: server_version
value: 8.3.1
2008-06-04 21:40:26 DEBUG: pid 17500: 1 th backend: name: server_version
value: 8.3.1
2008-06-04 21:40:26 DEBUG: pid 17500: pool_read_message_length2: master
slot: 0 length: 35
2008-06-04 21:40:26 DEBUG: pid 17500: pool_read_message_length2: master
slot: 1 length: 35
2008-06-04 21:40:26 DEBUG: pid 17500: 0 th backend: name:
session_authorization value: postgres
2008-06-04 21:40:26 DEBUG: pid 17500: 1 th backend: name:
session_authorization value: postgres
2008-06-04 21:40:26 DEBUG: pid 17500: pool_read_message_length2: master
slot: 0 length: 36
2008-06-04 21:40:26 DEBUG: pid 17500: pool_read_message_length2: master
slot: 1 length: 36
2008-06-04 21:40:26 DEBUG: pid 17500: 0 th backend: name:
standard_conforming_strings value: off
2008-06-04 21:40:26 DEBUG: pid 17500: 1 th backend: name:
standard_conforming_strings value: off
2008-06-04 21:40:26 DEBUG: pid 17500: pool_read_message_length2: master
slot: 0 length: 28
2008-06-04 21:40:26 DEBUG: pid 17500: pool_read_message_length2: master
slot: 1 length: 28
2008-06-04 21:40:26 DEBUG: pid 17500: 0 th backend: name: TimeZone
value: Canada/Pacific
2008-06-04 21:40:26 DEBUG: pid 17500: 1 th backend: name: TimeZone
value: Canada/Pacific
2008-06-04 21:40:26 DEBUG: pid 17500: pool_read_message_length: slot: 0
length: 12
2008-06-04 21:40:26 DEBUG: pid 17500: pool_read_message_length: slot: 1
length: 12
2008-06-04 21:40:26 DEBUG: pid 17500: pool_send_auth_ok: send pid 6289
to frontend
2008-06-04 21:40:26 DEBUG: pid 17500: read_kind_from_backend: read kind
from 0 th backend Z NUM_BACKENDS: 2
2008-06-04 21:40:26 DEBUG: pid 17500: read_kind_from_backend: read kind
from 1 th backend Z NUM_BACKENDS: 2
2008-06-04 21:40:26 DEBUG: pid 17500: pool_process_query: kind from
backend: Z
2008-06-04 21:40:26 DEBUG: pid 17500: pool_read_message_length: slot: 0
length: 5
2008-06-04 21:40:26 DEBUG: pid 17500: pool_read_message_length: slot: 1
length: 5
2008-06-04 21:40:26 DEBUG: pid 17500: ReadyForQuery: message length: 5
2008-06-04 21:40:26 DEBUG: pid 17500: ReadyForQuery: transaction state: I
2008-06-04 21:40:26 DEBUG: pid 17500: read kind from frontend Q(51)
2008-06-04 21:40:26 LOG: pid 17500: statement: INSERT INTO
tcp_audit_vlad (id, beacon_id, message, flow, "timestamp", unique_id,
byte_count) VALUES (531391045, 0, 'message_0', 'in', 1212303601,
'352023005147018', 297);
2008-06-04 21:40:26 DEBUG: pid 17500: insert node_number =0
2008-06-04 21:40:26 DEBUG: pid 17500: OneNode_do_command: Query: INSERT
INTO "tcp_audit_vlad"("id", "beacon_id", "message", "flow", "timestamp",
"unique_id", "byte_count") VALUES
(531391045,0,'message_0','in',1212303601,'352023005147018',297)
2008-06-04 21:40:26 DEBUG: pid 17500: pool_rewrite_stmt: XXX rule 702
... hangs here ...
on pgpool node: ps -ef | grep INSERT
root 17500 17457 0 21:38 pts/0 00:00:00 pgpool: postgres
MONSOONAUDIT 192.168.0.200(60965) INSERT
I'm getting similar negative result trying to pg_restore dump file
crated with pg_dump -D option - only first insert line gets inserted and
client hangs, however using same setup had no problems restoring using
standard dump/restore (COPY from STDIN) partitioning function worked and
data was distributed as expected. although the restore was VERY slow -
mostly cpu bound on pgpool with postgres at 70% cpu time and 20-28% on
pgpool.
here is my pgpool.conf details:
listen_addresses = '*'
port = 9999
pcp_port = 9898
socket_dir = '/tmp'
pcp_socket_dir = '/tmp'
backend_socket_dir = '/tmp'
pcp_timeout = 10
num_init_children = 32
max_pool = 2
child_life_time = 300
connection_life_time = 0
child_max_connections = 0
client_idle_limit = 0
authentication_timeout = 60
logdir = '/tmp'
replication_mode = false
load_balance_mode = false
replication_stop_on_mismatch = false
replicate_select = false
reset_query_list = 'ABORT; RESET ALL; SET SESSION AUTHORIZATION DEFAULT'
print_timestamp = true
master_slave_mode = false
connection_cache = false
health_check_timeout = 20
health_check_period = 0
health_check_user = 'nobody'
failover_command = ''
failback_command = ''
insert_lock = false
ignore_leading_white_space = true
log_statement = true
log_connections = true
log_hostname = false
parallel_mode = true
enable_query_cache = false
pgpool2_hostname = ''
system_db_hostname = '192.168.0.102'
system_db_port = 5432
system_db_dbname = 'pgpool'
system_db_schema = 'pgpool_catalog'
system_db_user = 'pgpool'
system_db_password = ''
backend_hostname0 = '192.168.0.131'
backend_port0 = 5432
backend_weight0 = 1
backend_data_directory0 = '/usr/local/pgsql/data'
backend_hostname1 = '192.168.0.132'
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/usr/local/pgsql/data'
enable_pool_hba = false
recovery_user = 'nobody'
recovery_password = ''
recovery_1st_stage_command = ''
recovery_2nd_stage_command = ''
recovery_timeout = 90
did I miss something ?
Thanks in advance,
Vlad
More information about the Pgpool-general
mailing list