Re: Postgres with pthread

On Dec 7, 2017, at 10:41 AM, Simon Riggs wrote:

>> But it is a theory. The main idea of this prototype was to prove or disprove
>> this expectation at practice.
>
>> But please notice that it is very raw prototype. A lot of stuff is not
>> working yet.
>
>> And supporting all of exited Postgres functionality requires
>> much more efforts (and even more efforts are needed for optimizing Postgres
>> for this architecture).
>>
>> I just want to receive some feedback and know if community is interested in
>> any further work in this direction.
>
> Looks good. You are right, it is a theory. If your prototype does
> actually show what we think it does then it is a good and interesting
> result.
>
> I think we need careful analysis to show where these exact gains come
> from. The actual benefit is likely not evenly distributed across the
> list of possible benefits. Did they arise because you produced a
> stripped down version of Postgres? Or did they arise from using
> threads?
>
> It would not be the first time a result shown in protoype did not show
> real gains on a completed project.
>
> I might also read your results to show that connection concentrators
> would be a better area of work, since 100 connections perform better
> than 1000 in both cases, so why bother optimising for 1000 connections
> at all? In which case we should read the benefit at the 100
> connections line, where it shows the lower 28% gain, closer to the
> gain your colleague reported.
>
> So I think we don't yet have enough to make a decision.

Concerning optimal number of connection: one of my intentions was to eliminate meed in external connection pool (pgbouncer&Co).
In this case applications can use prepared statements which itself provides two times increase of performance.
I believe that threads have smaller footprint than processes, to it is possible to spawn more threads and directly access them without intermediate layer with connection pooling.

I have performed experiments at more power server:
144 virtual cores Intel(R) Xeon(R) CPU E7-8890 v3 @ 2.50GHz.

Here results of read-only queries are different: both pthreads and vanilla version shows almost the same speed both for 100 and 1000 connections: about 1300k TPS
with prepared statement. So there is no performance degradation with increased number of connections and no larger difference between processes and threads.

But at read-write workload (pgbench -N) there is still significant advantage of pthreads version (kTPS):

Connections
Vanilla
pthreads
100
165
154
1000
85
118

For some reasons (which I do not know yet) multiprocess version of postgres is slightly faster for 100 connections,
but degrades almost twice for 1000 connections, while degradation of multithreads version is not so large.

By the way, pthreads version make it possible to much easily check whats going on using gdb (manual "profiling") :

thread apply all bt
Thread 997 (Thread 0x7f6e08810700 (LWP 61345)):
#0 0x00007f7e03263576 in do_futex_wait.constprop () from /lib64/libpthread.so.0
#1 0x00007f7e03263668 in __new_sem_wait_slow.constprop.0 () from /lib64/libpthread.so.0
#2 0x0000000000698552 in PGSemaphoreLock ()
#3 0x0000000000702804 in LWLockAcquire ()
#4 0x00000000004f9ac4 in XLogInsertRecord ()
#5 0x0000000000503b97 in XLogInsert ()
#6 0x00000000004bb0d1 in log_heap_clean ()
#7 0x00000000004bd7c8 in heap_page_prune ()
#8 0x00000000004bd9c1 in heap_page_prune_opt ()
---Type <return> to continue, or q <return> to quit---
#9 0x00000000004c43d4 in index_fetch_heap ()
#10 0x00000000004c4410 in index_getnext ()
#11 0x00000000006037d2 in IndexNext ()
#12 0x00000000005f3a80 in ExecScan ()
#13 0x0000000000609eba in ExecModifyTable ()
#14 0x00000000005ed6fa in standard_ExecutorRun ()
#15 0x0000000000713622 in ProcessQuery ()
#16 0x0000000000713885 in PortalRunMulti ()
#17 0x00000000007143a5 in PortalRun ()
#18 0x0000000000711cf1 in PostgresMain ()
#19 0x00000000006a708b in backend_main_proc ()
#20 0x00007f7e0325a36d in start_thread () from /lib64/libpthread.so.0
#21 0x00007f7e02870b8f in clone () from /lib64/libc.so.6

Thread 996 (Thread 0x7f6e08891700 (LWP 61344)):
#0 0x00007f7e03263576 in do_futex_wait.constprop () from /lib64/libpthread.so.0
#1 0x00007f7e03263668 in __new_sem_wait_slow.constprop.0 () from /lib64/libpthread.so.0
#2 0x0000000000698552 in PGSemaphoreLock ()
#3 0x0000000000702804 in LWLockAcquire ()
#4 0x00000000004bc862 in RelationGetBufferForTuple ()
#5 0x00000000004b60db in heap_insert ()
#6 0x000000000060ad3b in ExecModifyTable ()
#7 0x00000000005ed6fa in standard_ExecutorRun ()
#8 0x0000000000713622 in ProcessQuery ()
#9 0x0000000000713885 in PortalRunMulti ()
#10 0x00000000007143a5 in PortalRun ()
#11 0x0000000000711cf1 in PostgresMain ()
#12 0x00000000006a708b in backend_main_proc ()
#13 0x00007f7e0325a36d in start_thread () from /lib64/libpthread.so.0
#14 0x00007f7e02870b8f in clone () from /lib64/libc.so.6

Thread 995 (Thread 0x7f6e08912700 (LWP 61343)):
#0 0x00007f7e03263576 in do_futex_wait.constprop () from /lib64/libpthread.so.0
#1 0x00007f7e03263668 in __new_sem_wait_slow.constprop.0 () from /lib64/libpthread.so.0
#2 0x0000000000698552 in PGSemaphoreLock ()
#3 0x00000000006f1dad in ProcArrayEndTransaction ()
#4 0x00000000004efae0 in CommitTransaction ()
#5 0x00000000004f0bd5 in CommitTransactionCommand ()
#6 0x000000000070e9cf in finish_xact_command ()
#7 0x0000000000711d13 in PostgresMain ()
#8 0x00000000006a708b in backend_main_proc ()
#9 0x00007f7e0325a36d in start_thread () from /lib64/libpthread.so.0
#10 0x00007f7e02870b8f in clone () from /lib64/libc.so.6

---Type <return> to continue, or q <return> to quit---
Thread 994 (Thread 0x7f6e08993700 (LWP 61342)):
#0 0x00007f7e03263576 in do_futex_wait.constprop () from /lib64/libpthread.so.0
#1 0x00007f7e03263668 in __new_sem_wait_slow.constprop.0 () from /lib64/libpthread.so.0
#2 0x0000000000698552 in PGSemaphoreLock ()
#3 0x00000000006f1dad in ProcArrayEndTransaction ()
#4 0x00000000004efae0 in CommitTransaction ()
#5 0x00000000004f0bd5 in CommitTransactionCommand ()
#6 0x000000000070e9cf in finish_xact_command ()
#7 0x0000000000711d13 in PostgresMain ()
#8 0x00000000006a708b in backend_main_proc ()
#9 0x00007f7e0325a36d in start_thread () from /lib64/libpthread.so.0
#10 0x00007f7e02870b8f in clone () from /lib64/libc.so.6

Thread 993 (Thread 0x7f6e08a14700 (LWP 61341)):
#0 0x00007f7e03263576 in do_futex_wait.constprop () from /lib64/libpthread.so.0
#1 0x00007f7e03263668 in __new_sem_wait_slow.constprop.0 () from /lib64/libpthread.so.0
#2 0x0000000000698552 in PGSemaphoreLock ()
#3 0x00000000006f1dad in ProcArrayEndTransaction ()
#4 0x00000000004efae0 in CommitTransaction ()
#5 0x00000000004f0bd5 in CommitTransactionCommand ()
#6 0x000000000070e9cf in finish_xact_command ()
#7 0x0000000000711d13 in PostgresMain ()
#8 0x00000000006a708b in backend_main_proc ()
#9 0x00007f7e0325a36d in start_thread () from /lib64/libpthread.so.0
#10 0x00007f7e02870b8f in clone () from /lib64/libc.so.6

Thread 992 (Thread 0x7f6e08a95700 (LWP 61340)):
#0 0x00007f7e03263576 in do_futex_wait.constprop () from /lib64/libpthread.so.0
#1 0x00007f7e03263668 in __new_sem_wait_slow.constprop.0 () from /lib64/libpthread.so.0
#2 0x0000000000698552 in PGSemaphoreLock ()
#3 0x00000000006f1dad in ProcArrayEndTransaction ()
#4 0x00000000004efae0 in CommitTransaction ()
#5 0x00000000004f0bd5 in CommitTransactionCommand ()
#6 0x000000000070e9cf in finish_xact_command ()
#7 0x0000000000711d13 in PostgresMain ()
#8 0x00000000006a708b in backend_main_proc ()
#9 0x00007f7e0325a36d in start_thread () from /lib64/libpthread.so.0
#10 0x00007f7e02870b8f in clone () from /lib64/libc.so.6

Thread 991 (Thread 0x7f6e08b16700 (LWP 61339)):
#0 0x00007f7e03263576 in do_futex_wait.constprop () from /lib64/libpthread.so.0
#1 0x00007f7e03263668 in __new_sem_wait_slow.constprop.0 () from /lib64/libpthread.so.0
#2 0x0000000000698552 in PGSemaphoreLock ()
#3 0x00000000006f1dad in ProcArrayEndTransaction ()
---Type <return> to continue, or q <return> to quit---
#4 0x00000000004efae0 in CommitTransaction ()
#5 0x00000000004f0bd5 in CommitTransactionCommand ()
#6 0x000000000070e9cf in finish_xact_command ()
#7 0x0000000000711d13 in PostgresMain ()
#8 0x00000000006a708b in backend_main_proc ()
#9 0x00007f7e0325a36d in start_thread () from /lib64/libpthread.so.0
#10 0x00007f7e02870b8f in clone () from /lib64/libc.so.6
....

I am not going to show stack traces of all 1000 threads.
But you may notice that proc array lock really seems be be a bottleneck.