Wed Apr 4 09:12:02 PDT 2012
- Previous message: [Slony1-hackers] Re : CreateEvent and Lokcs on replicated objects
- Next message: [Slony1-hackers] Number of lock for CreateEvents and performance
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Steve I wrote a mistake. I see it only for sequences. However I see Slony's tables: sl_event, sl_seqlog and so on... I want to be sure that it is normal that "CreateEvent" uses a lot of "AccessShareLock" on all sequences. Any mistake for Slony 1.2.22 or is it the requested behavior? biopacs_production=# select pg_class.relname,pg_locks.transactionid, pg_locks.mode, & nbs p; substr(pg_stat_activity.current_query,1,30), pg_stat_activity.query_start, &a mp; nbsp; age(now(),pg_stat_activity.query_start) as "age", pg_stat_activity.procpid from pg_stat_activity,pg_locks left &am p;n bsp; outer join pg_class on (pg_locks.relation = pg_class.oid) ; where pg_locks.pid=pg_stat_activity.procpid and not ( pg_class.relname ~ '_seq$' )order by query_start limit 30; relname | transactionid | mode | substr | query_start | age | procpid -----------------------------------------------------------------+---------------+------------------+--------------------------------+----------------------------------+-------------+--------- ExpectedProtocolSequenceAttribute_ExpectedProtocolSequenceAttri | | AccessShareLock | select "_biocluster".createEve | 04-APR-12 12:03:06.372674 -04:00 | @ 0.96 secs | 25021 ExpectedProtocolSequenceAttribute_ExpectedProtocolSequenceAttri | | AccessShareLock | select "_biocluster".createEve | 04-APR-12 12:03:06.372674 -04:00 | @ 0.96 secs | 25021 ExpectedProtocolSequenceAttribute_ExpectedProtocolSequenceAttri | | AccessShareLock | select "_biocluster".createEve | 04-APR-12 12:03:06.372674 -04:00 | @ 0.96 secs | 25021 ExpectedProtocolSequenceAttribute_ExpectedProtocolSequenceAttri | | AccessShareLock | select "_biocluster".createEve | 04-APR-12 12:03:06.372674 -04:00 | @ 0.96 secs | 25021 sl_seqlog | | RowExclusiveLock | select "_biocluster".createEve | 04-APR-12 12:03:06.372674 -04:00 | @ 0.96 secs | 25021 ExpectedProtocolSequenceAttribute_ExpectedProtocolSequenceAttri | | AccessShareLock | select "_biocluster".createEve | 04-APR-12 12:03:06.372674 -04:00 | @ 0.96 secs | 25021 ExpectedProtocolSequenceAttribute_ExpectedProtocolSequenceAttri | | AccessShareLock | select "_biocluster".createEve | 04-APR-12 12:03:06.372674 -04:00 | @ 0.96 secs | 25021 sl_event | | RowExclusiveLock | select "_biocluster".createEve | 04-APR-12 12:03:06.372674 -04:00 | @ 0.96 secs | 25021 sl_event | | ExclusiveLock | select "_biocluster".createEve | 04-APR-12 12:03:06.372674 -04:00 | @ 0.96 secs | 25021 sl_set | | AccessShareLock | select "_biocluster".createEve | 04-APR-12 12:03:06.372674 -04:00 | @ 0.96 secs | 25021 pg_class_oid_index | | AccessShareLock | select "_biocluster".createEve | 04-APR-12 12:03:06.372674 -04:00 | @ 0.96 secs | 25021 ________________________________ De : Steve Singer <ssinger at ca.afilias.info> À : David TECHER <davidtecher at yahoo.fr> Cc : Slony Hackers <slony1-hackers at lists.slony.info> Envoyé le : Mercredi 4 avril 2012 17h51 Objet : Re: Re : [Slony1-hackers] CreateEvent and Lokcs on replicated objects On 12-04-04 11:40 AM, David TECHER wrote: > Steve > > Using a query taken from > Also, I ONLY see sequences listed in the below output. I don't see any examples of tables. Are you only seeing this for sequences or did you just not paste enough lines to cover tables. > http://wiki.postgresql.org/wiki/Lock_Monitoring > > I've got (the 10 first lines) > > biopacs_production=# select pg_class.relname,pg_locks.transactionid, > pg_locks.mode, &nbs p; substr(pg_stat_activity.current_query,1,30), > pg_stat_activity.query_start, & nbsp; > age(now(),pg_stat_activity.query_start) as "age", > pg_stat_activity.procpid from pg_stat_activity,pg_locks left &n bsp; > outer join pg_class on (pg_locks.relation = pg_class.oid) ; where > pg_locks.pid=pg_stat_activity.procpid order by query_start limit 10; > relname | transactionid | mode | substr | query_start | age | procpid > ---------------------------------------------------------+---------------+-----------------+--------------------------------+----------------------------------+-------------+--------- > AuditTimePoint_ID_seq | | AccessShareLock | select > "_biocluster".createEve | 04-APR-12 11:38:11.809813 -04:00 | @ 0.97 secs > | 25021 > AuditDegradedImage_ID_seq | | AccessShareLock | select > "_biocluster".createEve | 04-APR-12 11:38:11.809813 -04:00 | @ 0.97 secs > | 25021 > Screenshot_ScreenshotID_seq | | AccessShareLock | select > "_biocluster".createEve | 04-APR-12 11:38:11.809813 -04:00 | @ 0.97 secs > | 25021 > QuestionRule_QuestionRuleID_seq | | AccessShareLock | select > "_biocluster".createEve | 04-APR-12 11:38:11.809813 -04:00 | @ 0.97 secs > | 25021 > AuditAllocationStatus_ID_seq | | AccessShareLock | select > "_biocluster".createEve | 04-APR-12 11:38:11.809813 -04:00 | @ 0.97 secs > | 25021 > AuditQCCheckList_ID_seq | | AccessShareLock | select > "_biocluster".createEve | 04-APR-12 11:38:11.809813 -04:00 | @ 0.97 secs > | 25021 > QueueItemType_QueueItemTypeID_seq | | AccessShareLock | select > "_biocluster".createEve | 04-APR-12 11:38:11.809813 -04:00 | @ 0.97 secs > | 25021 > AuditInstitutionType_ID_seq | | AccessShareLock | select > "_biocluster".createEve | 04-APR-12 11:38:11.809813 -04:00 | @ 0.97 secs > | 25021 > AuditExportPublishTables_AuditExportPublishTablesID_seq | | > AccessShareLock | select "_biocluster".createEve | 04-APR-12 > 11:38:11.809813 -04:00 | @ 0.97 secs | 25021 > AuditCheckParameterRule_ExpectedProtocolSequence_ID_seq | | > AccessShareLock | select "_biocluster".createEve | 04-APR-12 > 11:38:11.809813 -04:00 | @ 0.97 secs | 25021 > (10 rows) > > Please let me understand > > ------------------------------------------------------------------------ > *De :* Steve Singer <ssinger at ca.afilias.info> > *À :* David TECHER <davidtecher at yahoo.fr> > *Cc :* Slony Hackers <slony1-hackers at lists.slony.info> > *Envoyé le :* Mercredi 4 avril 2012 17h25 > *Objet :* Re: [Slony1-hackers] CreateEvent and Lokcs on replicated objects > > On 12-04-04 06:25 AM, David TECHER wrote: > > Hi > > > I can't think of an obvious reason why a generic SYNC event would get a > lock on all of your tables. I don't think the SYNC event looks at > replicated tables . > > What transactions are holding the locks? You should be able to see this > in your pg_locks output (joining back to pg_class to get the tablenames > from the oids) > > > > > > > > > > I am using Slony 1.2.22 > > > > I've noticed that when a CreateEvent occurs > > > > biopacs_production=# select * from pg_stat_activity where procpid=25021; > > datid | datname | procpid | usesysid | usename | current_query | waiting > > | query_start | backend_start | client_addr | client_port > > > -------+--------------------+---------+----------+--------------+----------------------------------------------------------------+---------+----------------------------------+----------------------------------+-------------+------------- > > 16387 | biopacs_production | 25021 | 10 | enterprisedb | select > > "_biocluster".createEvent('_biocluster', 'SYNC', NULL); | f | 04-APR-12 > > 06:22:24.804654 -04:00 | 01-APR-12 06:53:33.363283 -04:00 | 127.0.1.1 | > > 42050 > > (1 row) > > > > > > Then by querying pg_locks I've noticed that there is a 'AccessSharelock' > > on all replicated objects (tables and sequences). > > > > It is normal? > > > > Since our database used intensive CPU (high load) I asked myself if it > > could explained my issue. > > > > Thanks for letting me know. > > > > Kind regards. > > > > David. > > > > > > > > _______________________________________________ > > Slony1-hackers mailing list > > Slony1-hackers at lists.slony.info <mailto:Slony1-hackers at lists.slony.info> > > http://lists.slony.info/mailman/listinfo/slony1-hackers > > > -------------- next part -------------- An HTML attachment was scrubbed... URL: http://lists.slony.info/pipermail/slony1-hackers/attachments/20120404/a4479a60/attachment-0001.htm
- Previous message: [Slony1-hackers] Re : CreateEvent and Lokcs on replicated objects
- Next message: [Slony1-hackers] Number of lock for CreateEvents and performance
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
More information about the Slony1-hackers mailing list