Menu
▾
▴
Re: [Postgres-xc-developers] PGXC hangs when run with concurrent inserts
|
From: Koichi S. <koi...@gm...> - 2013-04-09 05:41:20
|
Because insert is being done in parallel, I'm afraid there could be a possibility that we have internal lock conflicts, which should not happen. Regards; ---------- Koichi Suzuki 2013/4/9 Amit Khandekar <ami...@en...> > > > > 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 >> >> > > > ------------------------------------------------------------------------------ > 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 > > |
