Re: 9.2beta1, parallel queries, ReleasePredicateLocks, CheckForSerializableConflictIn in the oprofile - Mailing list pgsql-hackers

On Thu, May 24, 2012 at 6:26 PM, Sergey Koposov <koposov@ast.cam.ac.uk> wrote:
> On Thu, 24 May 2012, Jeff Janes wrote:
>>
>> Add
>> #define LWLOCK_STATS
>> near the top of:
>> src/backend/storage/lmgr/lwlock.c
>>
>> and recompile and run a reduced-size workload.  When the processes
>> exits, they will dump a lot of data about LWLock usage to the logfile.
>> Generally the LWLock with the most blocks on it will be the main
>> culprit.
>
>
> Here is the output from a multi-threaded run (8thtreads, 22 seconds) sorted
> by blk. Not sure whether that's of much use or not:
>
> PID 7112 lwlock 48: shacq 1124394 exacq 1350 blk 1373
> PID 7110 lwlock 48: shacq 1124460 exacq 1128 blk 1110
> PID 7114 lwlock 48: shacq 1124502 exacq 1041 blk 976
> PID 7111 lwlock 48: shacq 1124523 exacq 1009 blk 955
> PID 7113 lwlock 48: shacq 1124383 exacq 868 blk 871
> PID 7112 lwlock 44: shacq 1127148 exacq 1323 blk 838
> PID 7110 lwlock 44: shacq 1127256 exacq 1132 blk 774
> PID 7114 lwlock 44: shacq 1127418 exacq 1024 blk 702
> PID 7113 lwlock 44: shacq 1127179 exacq 920 blk 665
> PID 7111 lwlock 44: shacq 1127324 exacq 957 blk 651
> PID 7109 lwlock 48: shacq 1124402 exacq 384 blk 602
> PID 7108 lwlock 48: shacq 1125039 exacq 1592 blk 546
> PID 7108 lwlock 44: shacq 1127902 exacq 1548 blk 511
> PID 7109 lwlock 44: shacq 1127261 exacq 388 blk 466
> PID 7114 lwlock 47: shacq 227986 exacq 929 blk 449
> PID 7115 lwlock 44: shacq 1127495 exacq 633 blk 401
> PID 7115 lwlock 48: shacq 1124666 exacq 559 blk 397
> PID 7112 lwlock 47: shacq 227993 exacq 1248 blk 387
> PID 7110 lwlock 47: shacq 228218 exacq 1082 blk 378
> PID 7111 lwlock 47: shacq 228093 exacq 907 blk 321
> PID 7114 lwlock 42: shacq 232591 exacq 935 blk 318
> PID 7113 lwlock 47: shacq 228085 exacq 909 blk 306
> PID 7112 lwlock 34: shacq 343247 exacq 1255 blk 271
> PID 7110 lwlock 42: shacq 232599 exacq 1049 blk 262
> PID 7111 lwlock 34: shacq 343398 exacq 926 blk 255
> PID 7112 lwlock 42: shacq 232505 exacq 1262 blk 240
> PID 7108 lwlock 42: shacq 233215 exacq 1539 blk 237
> PID 7110 lwlock 43: shacq 438768 exacq 1026 blk 231
> PID 7114 lwlock 43: shacq 438795 exacq 898 blk 230
> PID 7113 lwlock 34: shacq 343283 exacq 832 blk 226
> PID 7110 lwlock 34: shacq 343338 exacq 1074 blk 226
> PID 7114 lwlock 0: shacq 0 exacq 14864 blk 219
> PID 7112 lwlock 43: shacq 438691 exacq 1123 blk 215
> PID 7111 lwlock 42: shacq 232645 exacq 885 blk 215
> PID 7114 lwlock 34: shacq 343362 exacq 939 blk 214
> PID 7111 lwlock 0: shacq 0 exacq 13638 blk 205
> PID 7111 lwlock 43: shacq 438817 exacq 899 blk 200
> PID 7114 lwlock 7: shacq 0 exacq 547 blk 193
> PID 7113 lwlock 42: shacq 232600 exacq 860 blk 192
> PID 7110 lwlock 0: shacq 0 exacq 16862 blk 191
> PID 7114 lwlock 38: shacq 333266 exacq 977 blk 183
> PID 7112 lwlock 38: shacq 333175 exacq 1246 blk 183
> PID 7113 lwlock 7: shacq 0 exacq 548 blk 178
> PID 7113 lwlock 43: shacq 438720 exacq 833 blk 178
> PID 7115 lwlock 7: shacq 0 exacq 549 blk 177
> PID 7108 lwlock 47: shacq 228682 exacq 1495 blk 177
> PID 7108 lwlock 34: shacq 343982 exacq 1595 blk 177
> PID 7112 lwlock 0: shacq 0 exacq 19538 blk 172
> PID 7111 lwlock 7: shacq 0 exacq 549 blk 172
> PID 7115 lwlock 17: shacq 0 exacq 9927 blk 167
> PID 7109 lwlock 34: shacq 343410 exacq 348 blk 167
> PID 7112 lwlock 7: shacq 0 exacq 548 blk 166
> PID 7110 lwlock 38: shacq 333249 exacq 1013 blk 165
> PID 7113 lwlock 38: shacq 333226 exacq 828 blk 163
> PID 7110 lwlock 7: shacq 0 exacq 548 blk 162
> PID 7109 lwlock 7: shacq 0 exacq 548 blk 161
> PID 7109 lwlock 47: shacq 228097 exacq 386 blk 158
> PID 7112 lwlock 45: shacq 130843 exacq 1201 blk 154
> PID 7109 lwlock 43: shacq 438787 exacq 314 blk 153
> PID 7111 lwlock 38: shacq 333306 exacq 882 blk 152
> PID 7108 lwlock 43: shacq 439454 exacq 1614 blk 151
> PID 7108 lwlock 17: shacq 0 exacq 23165 blk 147
> PID 7108 lwlock 7: shacq 0 exacq 549 blk 145
> PID 7113 lwlock 0: shacq 0 exacq 13394 blk 142
> PID 7112 lwlock 46: shacq 132972 exacq 1196 blk 142
> PID 7112 lwlock 36: shacq 204508 exacq 1180 blk 140
> PID 7109 lwlock 42: shacq 232611 exacq 363 blk 140
> PID 7115 lwlock 34: shacq 343582 exacq 600 blk 139
> PID 7114 lwlock 45: shacq 130818 exacq 903 blk 139
> PID 7115 lwlock 42: shacq 232846 exacq 517 blk 138
> PID 7108 lwlock 38: shacq 333989 exacq 1704 blk 137
> PID 7115 lwlock 43: shacq 438993 exacq 558 blk 126
> PID 7115 lwlock 0: shacq 0 exacq 3161 blk 125
> PID 7110 lwlock 40: shacq 129577 exacq 1021 blk 122
> PID 7113 lwlock 45: shacq 130833 exacq 814 blk 120
> PID 7111 lwlock 17: shacq 0 exacq 4607 blk 119
> PID 7109 lwlock 0: shacq 0 exacq 5711 blk 119
> PID 7110 lwlock 45: shacq 130865 exacq 1001 blk 117
> PID 7109 lwlock 38: shacq 333340 exacq 322 blk 115
> PID 7114 lwlock 46: shacq 132997 exacq 860 blk 112
> PID 7113 lwlock 36: shacq 204566 exacq 833 blk 112
> PID 7112 lwlock 40: shacq 129528 exacq 1234 blk 111
> PID 7115 lwlock 47: shacq 228397 exacq 516 blk 109
> PID 7114 lwlock 36: shacq 204634 exacq 930 blk 109
> PID 7112 lwlock 41: shacq 133600 exacq 1136 blk 109
> PID 7111 lwlock 36: shacq 204675 exacq 830 blk 108
> PID 7108 lwlock 0: shacq 0 exacq 6504 blk 108
> PID 7115 lwlock 38: shacq 333512 exacq 581 blk 106
> PID 7110 lwlock 36: shacq 204546 exacq 1014 blk 106
> PID 7113 lwlock 37: shacq 127040 exacq 790 blk 105
> PID 7111 lwlock 45: shacq 130893 exacq 863 blk 103
> PID 7114 lwlock 35: shacq 135342 exacq 879 blk 102
> PID 7113 lwlock 40: shacq 129560 exacq 853 blk 101
> PID 7110 lwlock 41: shacq 133697 exacq 1063 blk 101
> PID 7112 lwlock 35: shacq 135257 exacq 1229 blk 98
> PID 7110 lwlock 46: shacq 132990 exacq 1059 blk 98
> PID 7112 lwlock 37: shacq 127029 exacq 1229 blk 97
> PID 7114 lwlock 40: shacq 129597 exacq 913 blk 96
> PID 7110 lwlock 17: shacq 0 exacq 2655 blk 96
> PID 7109 lwlock 46: shacq 133014 exacq 326 blk 96
> PID 7111 lwlock 40: shacq 129720 exacq 890 blk 95
> PID 7111 lwlock 46: shacq 133057 exacq 876 blk 94
> PID 7114 lwlock 33: shacq 127651 exacq 921 blk 92
> PID 7112 lwlock 39: shacq 127288 exacq 1176 blk 91
> PID 7111 lwlock 39: shacq 127438 exacq 891 blk 91
> PID 7114 lwlock 37: shacq 127038 exacq 894 blk 89
> PID 7113 lwlock 33: shacq 127602 exacq 803 blk 89
> PID 7110 lwlock 37: shacq 127038 exacq 1085 blk 89
> PID 7110 lwlock 35: shacq 135333 exacq 1016 blk 88
> PID 7113 lwlock 46: shacq 132954 exacq 833 blk 87
> PID 7111 lwlock 41: shacq 133778 exacq 821 blk 87
> PID 7115 lwlock 36: shacq 204845 exacq 535 blk 86
> PID 7112 lwlock 33: shacq 127551 exacq 1149 blk 86
> PID 7109 lwlock 17: shacq 0 exacq 3463 blk 86
> PID 7110 lwlock 910: shacq 91 exacq 91 blk 84
> PID 7110 lwlock 39: shacq 127396 exacq 1068 blk 84
> PID 7109 lwlock 36: shacq 204581 exacq 363 blk 83
> PID 7113 lwlock 41: shacq 133648 exacq 765 blk 82
> PID 7114 lwlock 41: shacq 133757 exacq 897 blk 79
> PID 7114 lwlock 39: shacq 127429 exacq 923 blk 79

These are all on the buffer partition locks.  That makes sense...I was
wrong earlier: this case was in fact 'create table as', not 'insert
select' which rules out both the freelist lock and the wal insert lock
because create table as gets to use both a bulk insert strategy and
wal avoiding logic (assuming wal log level is not 'archive or higher'.So, why aren't the lock partitions helping here?

merlin