Justin wrote:
[Since] PostgreSql is not multi-threaded but a single thread application which spawns little exe's how is hyper threading helping Postgresql at all ??
Multiple threads and multiple processes are two ways to tackle a similar problem - that of how to do more than one thing on the CPU(s) at once.
Applications that use multiple cooperating processes benefit from more CPUs, CPU cores, and CPU hardware execution threads (HT) just like applications that use multiple threads do, so long as there is enough work to keep multiple CPU cores busy.
There's really not *that* much difference between a multi-threaded executable and an executable with multiple processes cooperating using shared memory (like PostgreSQL). Nor is there much difference in how they use multiple logical CPUs.
The main difference between the two models is that multiple processes with shared memory don't share address space except where they have specifically mapped it. This means that it's relatively hard for one process to mangle other processes' state, especially if it's properly careful with its shared memory. By contrast, it's depressingly easy for one thread to corrupt the shared heap or even to corrupt other threads' stacks in a multi-threaded executable.
On Windows, threads are usually preferred because Windows has such a horrible per-process overhead, but it's very good at creating threads quickly and cheaply. On UNIX, which has historically been bad at threading and very good at creating and destroying processes, the use of multiple processes is preferred.
It's also worth noting that you can combine multi-process and multi-threaded operation. For example, if PostgreSQL was ever to support evaluating a single query on multiple CPU cores one way it could do that would be to spawn multiple threads within a single backend. (Note: I know it's not even remotely close to that easy - I've been doing way too much threaded coding lately).
So ... honestly, whether PostgreSQL is multi-threaded or multi-process just doesn't matter. Even if it was muti-threaded instead of multi-process, so long as it can only execute each query on a maximum of one core then single queries will not benefit (much) from having multiple CPU cores, multiple physical CPUs, or CPUs with hyperthreading. However, multiple CPU bound queries running in parallel will benefit massively from extra cores or physical CPUs, and might also benefit from hyperthreading.
To perfectly honest my programming skills with milti-threading apps is non-existent along with Linux world but in the Windows world single threaded apps saw no measurable performance boost
Of course. They cannot use the extra logical core for anything, so it's just overhead. You will find, though, that hyperthreading may improve system responsiveness under load even when using only single threaded apps, because two different single threaded apps can run (kind of) at the same time.
It's pretty useless compared to real multiple cores, though.
and allot of multi-threaded apps also got there performance smashed?
That will depend a lot on details of CPU cache use, exactly what they were doing on their various threads, how their thread priorities were set up, etc. Some apps benefit, some lose.
-- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
