Re: Utilizing multiple cores for one query - Mailing list pgsql-performance

From Jean-David Beyer
Subject Re: Utilizing multiple cores for one query
Date
Msg-id 475193FF.1080803@verizon.net
Whole thread Raw
In response to Re: Utilizing multiple cores for one query  (henk de wit <henk53602@hotmail.com>)
List pgsql-performance
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

henk de wit wrote:
>> > I wonder whether the current versions of postgres (i.e. either 8.2
> or 8.3)
>> > are able to utilize multiple cores for the execution of a single query?
>> Nope.
>
> I see, thanks for the clarification.
>
> Btw, in this thread:
> http://archives.postgresql.org/pgsql-performance/2007-10/msg00159.php
>
> the following is said:
>
>>You can determine what runs in parellel based on the
>>indentation of the output.
>>Items at the same indentation level under the same
>>"parent" line will run in parallel
>
> Wouldn't this offer some opportunities for running things on multiple
> cores? Based on the above, many people already seem to think that PG is
> able to utilize multiple cores for 1 query.

Of course, it depends on just what you mean. Since postgresql is a
client-server system, the client can run on one processor and the server on
another. And that _is_ parallelism in a way. For me in one application, my
client uses about 20% of a processor and the server uses around 80%. But in
more detail,

VIRT   RES  SHR SWAP %MEM %CPU     TIME+ P COMMAND

2019m  94m  93m 1.9g  1.2   79   2:29.97 3 postgres: jdbeyer stock [local]
INSERT
2019m 813m 813m 1.2g 10.2    2  23:38.67 0 postgres: writer process

2018m  29m  29m 1.9g  0.4    0   4:07.59 3 /usr/bin/postmaster -p 5432 -D ...
 8624  652  264 7972  0.0    0   0:00.10 2 postgres: logger process

 9624 1596  204 8028  0.0    0   0:01.07 2 postgres: stats buffer process

 8892  840  280 8052  0.0    0   0:00.74 1 postgres: stats collector process
 6608 2320 1980 4288  0.0   22   1:56.27 0 /home/jdbeyer/bin/enter

The P column shows the processor the process last ran on. In this case, I
might get away with using one processor, it is clearly using all four.

Now this is not processing a single query on multiple cores (in this case,
the "query" is running on core #3 only), but the ancillary stuff is running
on multiple cores and some of it should be charged to the query. And the OS
kernel takes time for IO and stuff as well.

> Of course, it can be easily
> "proved" that this does not happen by simply watching at the CPU
> utilization graphs when executing a query. Nevertheless, those people
> may wonder why (some of) those items that already run in parallel not
> actually run in parallel using multiple cores?
>
>
- --
  .~.  Jean-David Beyer          Registered Linux User 85642.
  /V\  PGP-Key: 9A2FC99A         Registered Machine   241939.
 /( )\ Shrewsbury, New Jersey    http://counter.li.org
 ^^-^^ 11:40:01 up 1 day, 2:02, 5 users, load average: 4.15, 4.14, 4.15
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)
Comment: Using GnuPG with CentOS - http://enigmail.mozdev.org

iD8DBQFHUZP/Ptu2XpovyZoRAn2BAKDLCyDrRiSo40u15M5GwY4OkxGlngCfbNHI
7hjIcP1ozr+KYPr43Pck9TA=
=Fawa
-----END PGP SIGNATURE-----

pgsql-performance by date:

Previous
From: "Jonah H. Harris"
Date:
Subject: Re: Utilizing multiple cores for one query
Next
From: "Mindaugas"
Date:
Subject: Dealing with big tables