Re: Postgres with pthread

On 21.12.2017 16:25, Konstantin Knizhnik wrote:
> I continue experiments with my pthread prototype.
> Latest results are the following:
>
> 1. I have eliminated all (I hope) calls of non-reentrant functions
> (getopt, setlocale, setitimer, localtime, ...). So now parallel tests
> are passed.
>
> 2. I have implemented deallocation of top memory context (at thread
> exit) and cleanup of all opened file descriptors.
> I have to replace several place where malloc is used with top_malloc:
> allocation in top context.
>
> 3. Now my prototype is passing all regression tests now. But handling
> of errors is still far from completion.
>
> 4. I have performed experiments with replacing synchronization
> primitives used in Postgres with pthread analogues.
> Unfortunately it has almost now influence on performance.
>
> 5. Handling large number of connections.
> The maximal number of postgres connections is almost the same: 100k.
> But memory footprint in case of pthreads was significantly smaller:
> 18Gb vs 38Gb.
> And difference in performance was much higher: 60k TPS vs . 600k TPS.
> Compare it with performance for 10k clients: 1300k TPS.
> It is read-only pgbench -S test with 1000 connections.
> As far as pgbench doesn't allow to specify more than 1000 clients, I
> spawned several instances of pgbench.
>
> Why handling large number of connections is important?
> It allows applications to access postgres directly, not using
> pgbouncer or any other external connection pooling tool.
> In this case an application can use prepared statements which can
> reduce speed of simple queries almost twice.
>
> Unfortunately Postgres sessions are not lightweight. Each backend
> maintains its private catalog and relation caches, prepared statement
> cache,...
> For real database size of this caches in memory will be several
> megabytes and warming this caches can take significant amount of time.
> So if we really want to support large number of connections, we should
> rewrite caches to be global (shared).
> It will allow to save a lot of memory but add synchronization
> overhead. Also at NUMA private caches may be more efficient than one
> global cache.
>
> My proptotype can be found at:
> git://github.com/postgrespro/postgresql.pthreads.git
>
>
Finally I managed to run Postgres with 100k active connections.
Not sure that this result can pretend for been mentioned in Guiness
records, but I am almost sure that nobody has done it before (at least
with original version of Postgres).
But it was really "Pyrrhic victory". Performance for 100k connections is
1000 times slower than for 10k. All threads are blocked in semaphores.
This is more or less expected result, but still scale of degradation is
impressive:

#Connections
TPS
100k
550
10k
558k
6k
745k
4k
882k
2k
1100k
1k
1300k

As it is clear from this stacktraces, shared catalog and statement cache
are highly needed to provide good performance with such large number of
active backends:

(gdb) thread apply all bt

Thread 17807 (LWP 660863):
#0  0x00007f4c1cb46576 in do_futex_wait.constprop () from
/lib64/libpthread.so.0
#1  0x00007f4c1cb46668 in __new_sem_wait_slow.constprop.0 () from
/lib64/libpthread.so.0
#2  0x0000000000697a32 in PGSemaphoreLock ()
#3  0x0000000000702a64 in LWLockAcquire ()
#4  0x00000000006fbf2d in LockAcquireExtended ()
#5  0x00000000006f9fa3 in LockRelationOid ()
#6  0x00000000004b2ffd in relation_open ()
#7  0x00000000004b31d6 in heap_open ()
#8  0x00000000007f1ed1 in CatalogCacheInitializeCache ()
#9  0x00000000007f3835 in SearchCatCache1 ()
#10 0x0000000000800510 in get_tablespace ()
#11 0x00000000008006e1 in get_tablespace_page_costs ()
#12 0x000000000065a4e1 in cost_seqscan ()
#13 0x000000000068bf92 in create_seqscan_path ()
#14 0x00000000006568b4 in set_rel_pathlist ()
#15 0x0000000000656eb8 in make_one_rel ()
#16 0x00000000006740d0 in query_planner ()
#17 0x0000000000676526 in grouping_planner ()
#18 0x0000000000679812 in subquery_planner ()
#19 0x000000000067a66c in standard_planner ()
#20 0x000000000070ffe1 in pg_plan_query ()
#21 0x00000000007100b6 in pg_plan_queries ()
#22 0x00000000007f6c6f in BuildCachedPlan ()
#23 0x00000000007f6e5c in GetCachedPlan ()
#24 0x0000000000711ccf in PostgresMain ()
#25 0x00000000006a5535 in backend_main_proc ()
#26 0x00000000006a353d in thread_trampoline ()
#27 0x00007f4c1cb3d36d in start_thread () from /lib64/libpthread.so.0
#28 0x00007f4c1c153b8f in clone () from /lib64/libc.so.6

Thread 17806 (LWP 660861):
#0  0x00007f4c1cb46576 in do_futex_wait.constprop () from
/lib64/libpthread.so.0
#1  0x00007f4c1cb46668 in __new_sem_wait_slow.constprop.0 () from
/lib64/libpthread.so.0
#2  0x0000000000697a32 in PGSemaphoreLock ()
#3  0x0000000000702a64 in LWLockAcquire ()
#4  0x00000000006fbf2d in LockAcquireExtended ()
#5  0x00000000006f9fa3 in LockRelationOid ()
#6  0x00000000004b2ffd in relation_open ()
#7  0x00000000004b31d6 in heap_open ()
#8  0x00000000007f1ed1 in CatalogCacheInitializeCache ()
#9  0x00000000007f3835 in SearchCatCache1 ()
#10 0x0000000000800510 in get_tablespace ()
#11 0x00000000008006e1 in get_tablespace_page_costs ()
#12 0x000000000065a4e1 in cost_seqscan ()
#13 0x000000000068bf92 in create_seqscan_path ()
#14 0x00000000006568b4 in set_rel_pathlist ()
#15 0x0000000000656eb8 in make_one_rel ()
#16 0x00000000006740d0 in query_planner ()
#17 0x0000000000676526 in grouping_planner ()
#18 0x0000000000679812 in subquery_planner ()
#19 0x000000000067a66c in standard_planner ()
#20 0x000000000070ffe1 in pg_plan_query ()
#21 0x00000000007100b6 in pg_plan_queries ()
#22 0x00000000007f6c6f in BuildCachedPlan ()
---Type <return> to continue, or q <return> to quit---
#23 0x00000000007f6e5c in GetCachedPlan ()
#24 0x0000000000711ccf in PostgresMain ()
#25 0x00000000006a5535 in backend_main_proc ()
#26 0x00000000006a353d in thread_trampoline ()
#27 0x00007f4c1cb3d36d in start_thread () from /lib64/libpthread.so.0
#28 0x00007f4c1c153b8f in clone () from /lib64/libc.so.6

Thread 17805 (LWP 660856):
#0  0x00007f4c1cb46576 in do_futex_wait.constprop () from
/lib64/libpthread.so.0
#1  0x00007f4c1cb46668 in __new_sem_wait_slow.constprop.0 () from
/lib64/libpthread.so.0
#2  0x0000000000697a32 in PGSemaphoreLock ()
#3  0x0000000000702a64 in LWLockAcquire ()
#4  0x00000000006fcb1c in LockRelease ()
#5  0x00000000006fa059 in UnlockRelationId ()
#6  0x00000000004b31c5 in relation_close ()
#7  0x00000000007f2e86 in SearchCatCacheMiss ()
#8  0x00000000007f37fd in SearchCatCache1 ()
#9  0x0000000000800510 in get_tablespace ()
#10 0x00000000008006e1 in get_tablespace_page_costs ()
#11 0x000000000065a4e1 in cost_seqscan ()
#12 0x000000000068bf92 in create_seqscan_path ()
#13 0x00000000006568b4 in set_rel_pathlist ()
#14 0x0000000000656eb8 in make_one_rel ()
#15 0x00000000006740d0 in query_planner ()
#16 0x0000000000676526 in grouping_planner ()
#17 0x0000000000679812 in subquery_planner ()
#18 0x000000000067a66c in standard_planner ()
#19 0x000000000070ffe1 in pg_plan_query ()
#20 0x00000000007100b6 in pg_plan_queries ()
#21 0x00000000007f6c6f in BuildCachedPlan ()
#22 0x00000000007f6e5c in GetCachedPlan ()
#23 0x0000000000711ccf in PostgresMain ()
#24 0x00000000006a5535 in backend_main_proc ()
#25 0x00000000006a353d in thread_trampoline ()
#26 0x00007f4c1cb3d36d in start_thread () from /lib64/libpthread.so.0
#27 0x00007f4c1c153b8f in clone () from /lib64/libc.so.6
...

--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company