[Bucardo-general] Performance issues... (4.5.x)

Michelle Sullivan michelle at sorbs.net
Wed May 1 11:52:05 UTC 2013


Greg Sabino Mullane wrote:
> On Tue, Apr 30, 2013 at 07:49:11PM +0200, Michelle Sullivan wrote:
> ...
>   
>> I'm having trouble here... without bucardo (4.x) I get a 10x speed
>> improvement...
>>
>> Please suggestions on queries and expectations...
>>     
>
> With the triggers enabled, during the 10s delay, run:
>
> SELECT relation::regclass, * FROM pg_locks where not granted;
>
> Any rows that show up are strong clues for a locking issue.
>
>   
This is a pretty consistent view it's all 'pg_listener' with very
occasionally the htimestamps table appearing:

sorbs=# SELECT relation::regclass, * FROM pg_locks where not granted;
  relation   |   locktype    | database | relation | page  | tuple |
virtualxid | transactionid | classid | objid | objsubid |
virtualtransaction |  pid  |     mode      | granted
-------------+---------------+----------+----------+-------+-------+------------+---------------+---------+-------+----------+--------------------+-------+---------------+---------
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
25/133002          | 99253 | ExclusiveLock | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
45/130729          | 24920 | ExclusiveLock | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
141/72422          | 99236 | ExclusiveLock | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
107/106117         | 98529 | ExclusiveLock | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
164/44870          | 99090 | ExclusiveLock | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
69/110569          | 98335 | ExclusiveLock | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
38/37272           | 98759 | ExclusiveLock | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
121/99587          | 99052 | ExclusiveLock | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
165/71902          | 99218 | ExclusiveLock | f
             | transactionid |          |          |       |      
|            |     137186787 |         |       |          |
20/94731           | 99115 | ShareLock     | f
 htimestamps | tuple         |    16576 |    32629 | 46060 |    46
|            |               |         |       |          |
132/82785          | 99020 | ExclusiveLock | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
9/147888           | 98143 | ExclusiveLock | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
115/46523          | 99248 | ExclusiveLock | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
133/83687          | 99257 | ExclusiveLock | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
27/134780          | 99254 | ExclusiveLock | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
116/66506          | 99075 | ExclusiveLock | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
44/132006          | 99155 | ExclusiveLock | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
55/95460           | 98473 | ExclusiveLock | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
66/97620           | 98918 | ExclusiveLock | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
72/122199          | 99206 | ExclusiveLock | f
 htimestamps | tuple         |    16576 |    32629 | 46060 |    46
|            |               |         |       |          |
189/65288          | 99225 | ExclusiveLock | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
108/103157         | 93824 | ExclusiveLock | f
             | transactionid |          |          |       |      
|            |     137186816 |         |       |          |
112/96444          | 98804 | ShareLock     | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
145/82319          | 98106 | ExclusiveLock | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
70/118531          | 95792 | ExclusiveLock | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
53/77558           | 99034 | ExclusiveLock | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
76/72225           | 98980 | ExclusiveLock | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
150/78629          | 99265 | ExclusiveLock | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
222/55371          | 99094 | ExclusiveLock | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
100/105342         | 98689 | ExclusiveLock | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
75/130268          | 99210 | ExclusiveLock | f
 htimestamps | tuple         |    16576 |    32629 | 46060 |    46
|            |               |         |       |          |
159/72728          | 99069 | ExclusiveLock | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
23/143567          | 98151 | ExclusiveLock | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
103/105458         | 98976 | ExclusiveLock | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
106/110589         | 98872 | ExclusiveLock | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
105/111051         | 99263 | ExclusiveLock | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
30/129914          | 99231 | ExclusiveLock | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
1/125229           | 98907 | ExclusiveLock | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
175/67048          | 99135 | ExclusiveLock | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
151/80473          | 98994 | ExclusiveLock | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
87/113601          | 99197 | ExclusiveLock | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
180/68609          | 99181 | ExclusiveLock | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
2/164990           | 97158 | ExclusiveLock | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
144/52270          | 99121 | ExclusiveLock | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
36/138407          | 99018 | ExclusiveLock | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
86/110870          | 99165 | ExclusiveLock | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
4/137672           | 99239 | ExclusiveLock | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
156/77452          | 99243 | ExclusiveLock | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
19/124596          | 99259 | ExclusiveLock | f
 pg_listener | relation      |    16576 |     2614 |       |      
|            |               |         |       |          |
146/82468          | 98877 | ExclusiveLock | f
(50 rows)


-- 
Michelle Sullivan
http://www.mhix.org/



More information about the Bucardo-general mailing list