Message-ID: <d9656e32-f1ea-5401-d07b-dab34569236a@ardentperf.com>
Date: Wed, 7 Jun 2023 15:37:31 -0700
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:102.0)
 Gecko/20100101 Thunderbird/102.11.2
Subject: Re: Let's make PostgreSQL multi-threaded
Content-Language: en-US
To: pgsql-hackers@lists.postgresql.org
References: <31cc6df9-53fe-3cd9-af5b-ac0d801163f4@iki.fi>
 <4178104.1685978307@sss.pgh.pa.us>
 <4ce6c0f8-e8a4-1672-93fd-49d3fa975ee5@iki.fi>
 <29fe5f48-a6ed-d896-45ed-16b5904353a9@enterprisedb.com>
 <41c1e20d-f179-f87e-5929-80ca9ee0c105@gmx.net>
From: Jeremy Schneider <schneider@ardentperf.com>
Cc: Thomas Kellerer <shammat@gmx.net>,
 Tomas Vondra <tomas.vondra@enterprisedb.com>,
 Heikki Linnakangas <hlinnaka@iki.fi>, Tom Lane <tgl@sss.pgh.pa.us>
In-Reply-To: <41c1e20d-f179-f87e-5929-80ca9ee0c105@gmx.net>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/d9656e32-f1ea-5401-d07b-dab34569236a%40ardentperf.com>
Precedence: bulk

On 6/7/23 2:39 PM, Thomas Kellerer wrote:
> Tomas Vondra schrieb am 07.06.2023 um 21:20:
>> Also, which other projects did this transition? Is there something we
>> could learn from them? Were they restricted to much smaller list of
>> platforms?
> 
> Not open source, but Oracle was historically multi-threaded on Windows
> and multi-process on all other platforms.
> I _think_ starting with 19c you can optionally run it multi-threaded on
> Linux as well.
Looks like it actually became publicly available in 12c. AFAICT Oracle
supports both modes today, with a config parameter to switch between them.

This is a very interesting case study.

Concepts Manual:

https://docs.oracle.com/en/database/oracle/oracle-database/23/cncpt/process-architecture.html#GUID-4B460E97-18A0-4F5A-A62F-9608FFD43664

Reference:

https://docs.oracle.com/en/database/oracle/oracle-database/23/refrn/THREADED_EXECUTION.html#GUID-7A668A49-9FC5-4245-AD27-10D90E5AE8A8

List of Oracle process types, which ones can run as threads and which
ones always run as processes:

https://docs.oracle.com/en/database/oracle/oracle-database/23/refrn/background-processes.html#GUID-86184690-5531-405F-AA05-BB935F57B76D

Looks like they have four processes that will never run in threads:
* dbwriter (writes dirty blocks in background)
* process monitor (cleanup after process crash to avoid full server
restarts) <jealous>
* process spawner (like postmaster)
* time keeper process

Per Tim Hall's oracle-base, it seems that plenty of people are sticking
with the process model, and that one use case for threads was:
"consolidating lots of instances onto a single server without using the
multitennant option. Without the multithreaded model, the number of OS
processes could get very high."

https://oracle-base.com/articles/12c/multithreaded-model-using-threaded_execution_12cr1

I did google search for "oracle threaded_execution" and browsed a bit;
didn't see anything that seems earth shattering so far.

Ludovico Caldara and Martin Bach published blogs when it was first
released, which just introduced but didn't test or hammer on it. The
feature has existed for 10 years now and I don't see any blog posts
saying that "everyone should use this because it doubles your
performance" or anything like that. I think if there were really
significant performance gains then there would be many interesting blog
posts on the internet by now from the independent Oracle professional
community - I know many of these people.

In fact, there's an interesting blog by Kamil Stawiarski from 2015 where
he actually observed one case of /slower/ performance with threads. That
blog post ends with: "So I raise the question: why and when use threaded
execution? If ever?"

https://blog.ora-600.pl/2015/12/17/oracle-12c-internals-of-threaded-execution/

I'm not sure if he ever got an answer

-Jeremy

-- 
http://about.me/jeremy_schneider