Re: Need for help!

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