Menu
▾
▴
[Postgres-xc-developers] PGXC hangs when run with concurrent inserts
|
From: Venky K. <ve...@ad...> - 2013-04-09 01:01:35
|
All,
We have been running into a hang issue on our app that appears to be related to PGXC. Our app processes messages from RabbitMQ and inserts/updates tables. We run 5 concurrent threads. The incoming queues are replicated, one feeding Postgres 9.1 and the other feeding PGXC (current git master). PGXC is hanging on inserts after processing a few transactions. It does not appear to be related to the actual data itself. IT looks like all the sessions are waiting for something. There is no information on locks available from pg_locks.
An strace simply says recfrom(10.
The are no errors in the logs from gtm, coordinator or datanodes.
The tables have referential integrity and use a shared sequence to get the next id. Is it possible that something is going on with the logic to retrieve sequence numbers? The tables are all replicated.
Unfortunately, we have not been able to reproduce a reliable test case.
[postgres@gnode0 pgxc]$ /usr/local/pgsql/bin/psql -p 5433 -U postgres -d postgres -c 'select * from pg_catalog.pg_stat_activity;'
datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change | waiting | state |
query
-------+--------------+-------+----------+----------+------------------+----------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+---------+---------------------+--------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
12893 | postgres | 22330 | 10 | postgres | pgxc | 192.168.53.109 | | 47025 | 2013-03-31 21:42:16.724845-07 | | 2013-04-08 15:43:52.313325-07 | 2013-04-08 15:26:11.444754-07 | f | idle | COMMIT PREPARED 'T1
32273'
16393 | master | 4267 | 16392 | xcadmin | pgxc | 192.168.53.109 | | 54961 | 2013-04-08 15:24:28.668023-07 | | 2013-04-08 15:33:17.586836-07 | 2013-04-08 15:33:17.587942-07 | f | idle | SELECT count(*) FRO
M ONLY bicommon.account_datasource WHERE true
16395 | adchemy10013 | 4363 | 16392 | xcadmin | pgxc | 192.168.53.109 | | 55084 | 2013-04-08 15:28:48.822939-07 | | 2013-04-08 15:50:21.650727-07 | 2013-04-08 15:50:07.916753-07 | f | idle | SELECT prd_id, prd_
semid, prd_name, prd_line, prd_model, prd_brand, prd_image_url, prd_dest_url, created_ts, updated_ts, source_msg_ts, modified_by FROM biods.product
16393 | master | 4486 | 16392 | xcadmin | pgxc | 192.168.53.109 | | 55246 | 2013-04-08 15:33:21.019388-07 | | 2013-04-08 15:43:51.321376-07 | 2013-04-08 15:43:51.322675-07 | f | idle | SET SESSION AUTHORI
ZATION DEFAULT;RESET ALL;
16393 | master | 4781 | 16392 | xcadmin | pgxc | 192.168.53.109 | | 55515 | 2013-04-08 15:42:42.122785-07 | | 2013-04-08 17:02:21.023713-07 | 2013-04-08 17:02:20.804751-07 | f | idle | SET SESSION AUTHORI
ZATION DEFAULT;RESET ALL;
16393 | master | 4787 | 16392 | xcadmin | pgxc | 192.168.53.109 | | 55521 | 2013-04-08 15:42:42.142662-07 | | 2013-04-08 16:17:19.26364-07 | 2013-04-08 16:17:19.126163-07 | f | idle | SET SESSION AUTHORI
ZATION DEFAULT;RESET ALL;
16393 | master | 4792 | 16392 | xcadmin | pgxc | 192.168.53.109 | | 55526 | 2013-04-08 15:42:42.159009-07 | | 2013-04-08 15:45:11.915026-07 | 2013-04-08 15:45:11.886392-07 | f | idle | SET SESSION AUTHORI
ZATION DEFAULT;RESET ALL;
16393 | master | 4799 | 16392 | xcadmin | pgxc | 192.168.53.109 | | 55533 | 2013-04-08 15:42:42.678387-07 | | 2013-04-08 17:02:21.195332-07 | 2013-04-08 17:02:20.805074-07 | f | idle | SET SESSION AUTHORI
ZATION DEFAULT;RESET ALL;
16393 | master | 4804 | 16392 | xcadmin | pgxc | 192.168.53.109 | | 55538 | 2013-04-08 15:42:42.694802-07 | | 2013-04-08 15:45:11.904619-07 | 2013-04-08 15:45:11.888493-07 | f | idle | SET SESSION AUTHORI
ZATION DEFAULT;RESET ALL;
16395 | adchemy10013 | 4977 | 17361 | adchemy | pgxc | 192.168.53.109 | | 55732 | 2013-04-08 15:47:34.901175-07 | 2013-04-08 15:48:08.345331-07 | 2013-04-08 15:48:08.528818-07 | 2013-04-08 15:48:08.410815-07 | f | idle in transaction | INSERT INTO biods.p
roduct_feature (prd_feature_id, category_id, prd_id, feature_id, feature_value_id, category_semid, prd_semid, feature_semid, feature_value_semid, created_ts, updated_ts, modified_by) VALUES ($12, $1, $9, $4, $6, $2, $10, $5, $7, $3, $11, $8)
16395 | adchemy10013 | 4979 | 17361 | adchemy | pgxc | 192.168.53.109 | | 55734 | 2013-04-08 15:47:35.042778-07 | 2013-04-08 15:48:16.384763-07 | 2013-04-08 15:48:16.506899-07 | 2013-04-08 15:48:16.388503-07 | t | active | INSERT INTO biods.p
roduct_feature (prd_feature_id, category_id, prd_id, feature_id, feature_value_id, category_semid, prd_semid, feature_semid, feature_value_semid, created_ts, updated_ts, modified_by) VALUES ($12, $1, $9, $4, $6, $2, $10, $5, $7, $3, $11, $8)
16395 | adchemy10013 | 4985 | 17361 | adchemy | pgxc | 192.168.53.109 | | 55740 | 2013-04-08 15:47:35.235945-07 | 2013-04-08 15:48:14.38895-07 | 2013-04-08 15:48:14.445351-07 | 2013-04-08 15:48:14.446752-07 | t | active | UPDATE biods.featur
e SET feature_semid = $3, feature_name = $2, created_ts = $1, updated_ts = $6, source_msg_ts = $5, modified_by = $4 WHERE (feature_id = $7)
16395 | adchemy10013 | 4986 | 17361 | adchemy | pgxc | 192.168.53.109 | | 55741 | 2013-04-08 15:47:35.238843-07 | 2013-04-08 15:48:18.201043-07 | 2013-04-08 15:48:18.273204-07 | 2013-04-08 15:48:18.205647-07 | t | active | INSERT INTO biods.p
roduct_feature (prd_feature_id, category_id, prd_id, feature_id, feature_value_id, category_semid, prd_semid, feature_semid, feature_value_semid, created_ts, updated_ts, modified_by) VALUES ($12, $1, $9, $4, $6, $2, $10, $5, $7, $3, $11, $8)
16395 | adchemy10013 | 4998 | 17361 | adchemy | pgxc | 192.168.53.109 | | 55753 | 2013-04-08 15:47:35.910309-07 | 2013-04-08 15:48:08.412038-07 | 2013-04-08 15:48:08.566945-07 | 2013-04-08 15:48:08.415026-07 | t | active | INSERT INTO biods.p
roduct_feature (prd_feature_id, category_id, prd_id, feature_id, feature_value_id, category_semid, prd_semid, feature_semid, feature_value_semid, created_ts, updated_ts, modified_by) VALUES ($12, $1, $9, $4, $6, $2, $10, $5, $7, $3, $11, $8)
16395 | adchemy10013 | 6340 | 17361 | adchemy | pgxc | 192.168.53.109 | | 57002 | 2013-04-08 16:31:44.414804-07 | 2013-04-08 16:31:50.293828-07 | 2013-04-08 16:31:50.433988-07 | 2013-04-08 16:31:50.297752-07 | t | active | INSERT INTO biods.p
roduct_feature (prd_feature_id, category_id, prd_id, feature_id, feature_value_id, category_semid, prd_semid, feature_semid, feature_value_semid, created_ts, updated_ts, modified_by) VALUES ($12, $1, $9, $4, $6, $2, $10, $5, $7, $3, $11, $8)
16395 | adchemy10013 | 6341 | 17361 | adchemy | pgxc | 192.168.53.109 | | 57003 | 2013-04-08 16:31:44.418356-07 | 2013-04-08 16:31:49.450704-07 | 2013-04-08 16:31:49.599946-07 | 2013-04-08 16:31:49.45562-07 | t | active | INSERT INTO biods.p
roduct_feature (prd_feature_id, category_id, prd_id, feature_id, feature_value_id, category_semid, prd_semid, feature_semid, feature_value_semid, created_ts, updated_ts, modified_by) VALUES ($12, $1, $9, $4, $6, $2, $10, $5, $7, $3, $11, $8)
16395 | adchemy10013 | 6348 | 17361 | adchemy | pgxc | 192.168.53.109 | | 57010 | 2013-04-08 16:31:45.065767-07 | 2013-04-08 16:31:50.699979-07 | 2013-04-08 16:31:50.817425-07 | 2013-04-08 16:31:50.704669-07 | t | active | INSERT INTO biods.p
roduct_feature (prd_feature_id, category_id, prd_id, feature_id, feature_value_id, category_semid, prd_semid, feature_semid, feature_value_semid, created_ts, updated_ts, modified_by) VALUES ($12, $1, $9, $4, $6, $2, $10, $5, $7, $3, $11, $8)
16395 | adchemy10013 | 6349 | 17361 | adchemy | pgxc | 192.168.53.109 | | 57011 | 2013-04-08 16:31:45.06926-07 | 2013-04-08 16:31:51.528207-07 | 2013-04-08 16:31:51.582036-07 | 2013-04-08 16:31:51.532618-07 | t | active | INSERT INTO biods.p
roduct_feature (prd_feature_id, category_id, prd_id, feature_id, feature_value_id, category_semid, prd_semid, feature_semid, feature_value_semid, created_ts, updated_ts, modified_by) VALUES ($12, $1, $9, $4, $6, $2, $10, $5, $7, $3, $11, $8)
16395 | adchemy10013 | 6350 | 17361 | adchemy | pgxc | 192.168.53.109 | | 57012 | 2013-04-08 16:31:45.072711-07 | 2013-04-08 16:31:50.085336-07 | 2013-04-08 16:31:50.223221-07 | 2013-04-08 16:31:50.088908-07 | t | active | INSERT INTO biods.p
roduct_feature (prd_feature_id, category_id, prd_id, feature_id, feature_value_id, category_semid, prd_semid, feature_semid, feature_value_semid, created_ts, updated_ts, modified_by) VALUES ($12, $1, $9, $4, $6, $2, $10, $5, $7, $3, $11, $8)
16395 | adchemy10013 | 7269 | 17361 | adchemy | pgxc | 192.168.53.109 | | 57774 | 2013-04-08 16:57:15.563006-07 | 2013-04-08 16:57:21.849156-07 | 2013-04-08 16:57:21.978984-07 | 2013-04-08 16:57:21.853289-07 | t | active | INSERT INTO biods.p
roduct_feature (prd_feature_id, category_id, prd_id, feature_id, feature_value_id, category_semid, prd_semid, feature_semid, feature_value_semid, created_ts, updated_ts, modified_by) VALUES ($12, $1, $9, $4, $6, $2, $10, $5, $7, $3, $11, $8)
16395 | adchemy10013 | 7271 | 17361 | adchemy | pgxc | 192.168.53.109 | | 57776 | 2013-04-08 16:57:15.63199-07 | 2013-04-08 16:57:16.575535-07 | 2013-04-08 16:57:17.00605-07 | 2013-04-08 16:57:17.007747-07 | t | active | INSERT INTO biods.f
eature_value (feature_value_id, feature_value_semid, feature_value, feature_semid, feature_id, created_ts, updated_ts, source_msg_ts, modified_by) VALUES ($9, $5, $4, $3, $2, $1, $8, $7, $6)
16395 | adchemy10013 | 7283 | 17361 | adchemy | pgxc | 192.168.53.109 | | 57788 | 2013-04-08 16:57:16.292702-07 | 2013-04-08 16:57:21.849125-07 | 2013-04-08 16:57:21.978824-07 | 2013-04-08 16:57:21.853251-07 | t | active | INSERT INTO biods.p
roduct_feature (prd_feature_id, category_id, prd_id, feature_id, feature_value_id, category_semid, prd_semid, feature_semid, feature_value_semid, created_ts, updated_ts, modified_by) VALUES ($12, $1, $9, $4, $6, $2, $10, $5, $7, $3, $11, $8)
16395 | adchemy10013 | 7284 | 17361 | adchemy | pgxc | 192.168.53.109 | | 57789 | 2013-04-08 16:57:16.295879-07 | 2013-04-08 16:57:24.233166-07 | 2013-04-08 16:57:24.321938-07 | 2013-04-08 16:57:24.237514-07 | t | active | INSERT INTO biods.p
roduct_feature (prd_feature_id, category_id, prd_id, feature_id, feature_value_id, category_semid, prd_semid, feature_semid, feature_value_semid, created_ts, updated_ts, modified_by) VALUES ($12, $1, $9, $4, $6, $2, $10, $5, $7, $3, $11, $8)
16395 | adchemy10013 | 7285 | 17361 | adchemy | pgxc | 192.168.53.109 | | 57790 | 2013-04-08 16:57:16.299271-07 | 2013-04-08 16:57:22.119868-07 | 2013-04-08 16:57:22.197213-07 | 2013-04-08 16:57:22.128357-07 | t | active | INSERT INTO biods.p
roduct_feature (prd_feature_id, category_id, prd_id, feature_id, feature_value_id, category_semid, prd_semid, feature_semid, feature_value_semid, created_ts, updated_ts, modified_by) VALUES ($12, $1, $9, $4, $6, $2, $10, $5, $7, $3, $11, $8)
16395 | adchemy10013 | 7465 | 17361 | adchemy | pgxc | 192.168.53.109 | | 57954 | 2013-04-08 17:01:54.750113-07 | 2013-04-08 17:02:00.17336-07 | 2013-04-08 17:02:00.320469-07 | 2013-04-08 17:02:00.177758-07 | t | active | INSERT INTO biods.p
roduct_feature (prd_feature_id, category_id, prd_id, feature_id, feature_value_id, category_semid, prd_semid, feature_semid, feature_value_semid, created_ts, updated_ts, modified_by) VALUES ($12, $1, $9, $4, $6, $2, $10, $5, $7, $3, $11, $8)
16395 | adchemy10013 | 7466 | 17361 | adchemy | pgxc | 192.168.53.109 | | 57955 | 2013-04-08 17:01:54.753559-07 | 2013-04-08 17:01:59.49003-07 | 2013-04-08 17:01:59.602925-07 | 2013-04-08 17:01:59.493732-07 | t | active | INSERT INTO biods.p
roduct_feature (prd_feature_id, category_id, prd_id, feature_id, feature_value_id, category_semid, prd_semid, feature_semid, feature_value_semid, created_ts, updated_ts, modified_by) VALUES ($12, $1, $9, $4, $6, $2, $10, $5, $7, $3, $11, $8)
16395 | adchemy10013 | 7467 | 17361 | adchemy | pgxc | 192.168.53.109 | | 57956 | 2013-04-08 17:01:54.75699-07 | 2013-04-08 17:01:58.262083-07 | 2013-04-08 17:01:58.349452-07 | 2013-04-08 17:01:58.350822-07 | t | active | INSERT INTO biods.f
eature_value (feature_value_id, feature_value_semid, feature_value, feature_semid, feature_id, created_ts, updated_ts, source_msg_ts, modified_by) VALUES ($9, $5, $4, $3, $2, $1, $8, $7, $6)
16395 | adchemy10013 | 7473 | 17361 | adchemy | pgxc | 192.168.53.109 | | 57963 | 2013-04-08 17:01:55.49134-07 | 2013-04-08 17:02:00.313138-07 | 2013-04-08 17:02:00.420405-07 | 2013-04-08 17:02:00.318887-07 | t | active | INSERT INTO biods.p
roduct_feature (prd_feature_id, category_id, prd_id, feature_id, feature_value_id, category_semid, prd_semid, feature_semid, feature_value_semid, created_ts, updated_ts, modified_by) VALUES ($12, $1, $9, $4, $6, $2, $10, $5, $7, $3, $11, $8)
16395 | adchemy10013 | 7474 | 17361 | adchemy | pgxc | 192.168.53.109 | | 57964 | 2013-04-08 17:01:55.494777-07 | 2013-04-08 17:02:00.514142-07 | 2013-04-08 17:02:00.577239-07 | 2013-04-08 17:02:00.519572-07 | t | active | INSERT INTO biods.p
roduct_feature (prd_feature_id, category_id, prd_id, feature_id, feature_value_id, category_semid, prd_semid, feature_semid, feature_value_semid, created_ts, updated_ts, modified_by) VALUES ($12, $1, $9, $4, $6, $2, $10, $5, $7, $3, $11, $8)
12893 | postgres | 8517 | 10 | postgres | psql | | | -1 | 2013-04-08 17:35:28.217934-07 | | 2013-04-08 17:35:28.220366-07 | 2013-04-08 17:35:28.220369-07 | f | active | select * from pg_ca
talog.pg_stat_activity;
(30 rows)
________________________________________
Venky Kandaswamy
Principal Engineer, Adchemy Inc.
925-200-7124
|
