Menu
▾
▴
Re: [Postgres-xc-developers] PGXC hangs when run with concurrent inserts
|
From: Amit K. <ami...@en...> - 2013-04-09 04:53:49
|
On 9 April 2013 06:46, Venky Kandaswamy <ve...@ad...> wrote: > 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. > Since most of the operations are inserts, it does not look like it is due to locks, unless something has acquired table locks. But just to rule out that possibility, it would be better if you check pg_locks on the datanodes, if you have checked it only on coordinator so far. > > 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 > > > ------------------------------------------------------------------------------ > Precog is a next-generation analytics platform capable of advanced > analytics on semi-structured data. The platform includes APIs for building > apps and a phenomenal toolset for data science. Developers can use > our toolset for easy data analysis & visualization. Get a free account! > http://www2.precog.com/precogplatform/slashdotnewsletter > _______________________________________________ > Postgres-xc-developers mailing list > Pos...@li... > https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers > > |
