> - When we increased NUM_BUFFER_PARTITIONS to 1024, this problem is > disappeared for 8 core machines and come back with 16 core machines on > Amazon EC2. Would it be related with PostgreSQL locking mechanism?
If we build with -DLWLOCK_STATS to print locking stats from PostgreSQL, we see tons of contention with default value of NUM_BUFFER_PARTITIONS which is 16: $ tail -f /tmp/logfile | grep lwlock | egrep -v "blk 0" ... PID 15965 lwlock 0: shacq 0 exacq 33 blk 2 PID 15965 lwlock 34: shacq 14010 exacq 27134 blk 6192 PID 15965 lwlock 35: shacq 14159 exacq 27397 blk 5426 PID 15965 lwlock 36: shacq 14111 exacq 27322 blk 4959 PID 15965 lwlock 37: shacq 14211 exacq 27507 blk 4370 PID 15965 lwlock 38: shacq 14110 exacq 27294 blk 3980 PID 15965 lwlock 39: shacq 13962 exacq 27027 blk 3719 PID 15965 lwlock 40: shacq 14023 exacq 27156 blk 3273 PID 15965 lwlock 41: shacq 14107 exacq 27309 blk 3201 PID 15965 lwlock 42: shacq 14120 exacq 27304 blk 2904 PID 15965 lwlock 43: shacq 14007 exacq 27129 blk 2740 PID 15965 lwlock 44: shacq 13948 exacq 27027 blk 2616 PID 15965 lwlock 45: shacq 14041 exacq 27198 blk 2431 PID 15965 lwlock 46: shacq 14067 exacq 27277 blk 2345 PID 15965 lwlock 47: shacq 14050 exacq 27203 blk 2106 PID 15965 lwlock 48: shacq 13910 exacq 26910 blk 2155 PID 15965 lwlock 49: shacq 14170 exacq 27360 blk 1989 After we increased NUM_BUFFER_PARTITIONS to 1024, lock contention is decreased: ... PID 25220 lwlock 1000: shacq 247 exacq 494 blk 1 PID 25220 lwlock 1001: shacq 198 exacq 394 blk 1 PID 25220 lwlock 1002: shacq 203 exacq 404 blk 1 PID 25220 lwlock 1003: shacq 226 exacq 452 blk 1 PID 25220 lwlock 1004: shacq 235 exacq 470 blk 1 PID 25220 lwlock 1006: shacq 226 exacq 452 blk 2 PID 25220 lwlock 1007: shacq 214 exacq 428 blk 1 PID 25220 lwlock 1008: shacq 225 exacq 448 blk 1 PID 25220 lwlock 1010: shacq 209 exacq 418 blk 1 PID 25220 lwlock 1015: shacq 199 exacq 398 blk 1 PID 25220 lwlock 1016: shacq 214 exacq 426 blk 1 PID 25220 lwlock 1018: shacq 230 exacq 456 blk 1 PID 25220 lwlock 1019: shacq 222 exacq 444 blk 3 PID 25220 lwlock 1023: shacq 262 exacq 524 blk 1 PID 25220 lwlock 1027: shacq 213 exacq 426 blk 1 PID 25220 lwlock 1028: shacq 246 exacq 491 blk 1 PID 25220 lwlock 1029: shacq 226 exacq 452 blk 1