Обсуждение: about half processes are blocked by btree, btree is bottleneck?
I use benchmarksql with more than 200 clients on pg 9.3.3. when the test is going on, I collect all the process stack. Ifound about 100 processes are blocked by btree insert. Another 100 are blocked by xloginsert. Does btree has bad performance in concurrency scenarios? Sum:66 #0 0x00007f8273a77627 in semop () from /lib64/libc.so.6 #1 0x000000000060cda7 in PGSemaphoreLock () #2 0x00000000006511a9 in LWLockAcquire () #3 0x00000000004987f7 in _bt_relandgetbuf () #4 0x000000000049c116 in _bt_search () #5 0x0000000000497e13 in _bt_doinsert () #6 0x000000000049af52 in btinsert () #7 0x000000000072dce4 in FunctionCall6Coll () #8 0x000000000049592e in index_insert () #9 0x0000000000590ac5 in ExecInsertIndexTuples () Sum:36 #0 0x00007f8273a77627 in semop () from /lib64/libc.so.6 #1 0x000000000060cda7 in PGSemaphoreLock () #2 0x00000000006511a9 in LWLockAcquire () #3 0x0000000000497e31 in _bt_doinsert () #4 0x000000000049af52 in btinsert () #5 0x000000000072dce4 in FunctionCall6Coll () #6 0x000000000049592e in index_insert () #7 0x0000000000590ac5 in ExecInsertIndexTuples ()
On 09/11/2014 06:08 AM, Xiaoyulei wrote: > I use benchmarksql with more than 200 clients on pg 9.3.3. when the test is going on, I collect all the process stack.I found about 100 processes are blocked by btree insert. Another 100 are blocked by xloginsert. > > Does btree has bad performance in concurrency scenarios? Well, there's always a bottleneck. I'd suggest trying 9.4, there were changes to make WAL insertion more scalable, as well as small tweaks to the spinlock implementation. I'm not too familiar with BenchmarkSQL, but if 200 clients means 200 concurrent active connections to the database, that's a lot. You'll likely see better performance if you dial it down closer to the number of CPU cores in the server. Also make sure you use a large-enough scale factor. Otherwise all transactions try to access a small set of rows, which naturally becomes a bottleneck. - Heikki
On Wed, Sep 10, 2014 at 8:08 PM, Xiaoyulei <xiaoyulei@huawei.com> wrote: > Sum:66 > #0 0x00007f8273a77627 in semop () from /lib64/libc.so.6 > #1 0x000000000060cda7 in PGSemaphoreLock () > #2 0x00000000006511a9 in LWLockAcquire () > #3 0x00000000004987f7 in _bt_relandgetbuf () > #4 0x000000000049c116 in _bt_search () > #5 0x0000000000497e13 in _bt_doinsert () > #6 0x000000000049af52 in btinsert () > #7 0x000000000072dce4 in FunctionCall6Coll () > #8 0x000000000049592e in index_insert () > #9 0x0000000000590ac5 in ExecInsertIndexTuples () > > > Sum:36 > #0 0x00007f8273a77627 in semop () from /lib64/libc.so.6 > #1 0x000000000060cda7 in PGSemaphoreLock () > #2 0x00000000006511a9 in LWLockAcquire () > #3 0x0000000000497e31 in _bt_doinsert () > #4 0x000000000049af52 in btinsert () > #5 0x000000000072dce4 in FunctionCall6Coll () > #6 0x000000000049592e in index_insert () > #7 0x0000000000590ac5 in ExecInsertIndexTuples () I don't know what "Sum" indicates here, but if the ratio of total calls to LWLockAcquire() between each LWLockAcquire() caller listed here is roughly in line with the "Sum" ratio, this must be a pretty small B-Tree (or the average size of each B-Tree must be very small). I suppose you could still see a lot of contention without the B-Tree ever getting much bigger if there is a lot of non-HOT updates. -- Peter Geoghegan