pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to run in parallel in Postgres
7+ messages / 4 participants
[nested] [flat]
* How to run in parallel in Postgres
@ 2019-12-05 12:10 Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
2019-12-05 16:42 ` Re: How to run in parallel in Postgres Laurenz Albe <laurenz.albe@cybertec.at>
2019-12-07 01:23 ` Re: How to run in parallel in Postgres Ondrej Ivanič <ondrej.ivanic@gmail.com>
2019-12-07 01:25 ` Re: How to run in parallel in Postgres Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 3 replies; 7+ messages in thread
From: Lars Aksel Opsahl @ 2019-12-05 12:10 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Hi
I have a function that prepares data, so the big job can be run it in parallel.
Today I have solved this by using "Gnu parallel" like this.
psql testdb -c"\! psql -t -q -o /tmp/run_cmd.sql testdb -c\"SELECT find_overlap_gap_make_run_cmd('sl_lop.overlap_gap_input_t1','geom',4258,'sl_lop.overlap_gap_input_t1_res',50);\"; parallel -j 4 psql testdb -c :::: /tmp/run_cmd.sql" 2>> /tmp/analyze.log;
The problem here is that I depend on external code which may not be installed.
Since Postgres now supports parallel I was wondering if it's easy to trigger parallel dynamically created SQL calls.
If you look at https://github.com/larsop/find-overlap-and-gap/blob/master/src/test/sql/regress/find_overlap_and_gap... you see that
find_overlap_gap_make_run_cmd generates as set of 28 sql calls.
So is it in a simple way possible to use Postgres parallel functionality to call this 28 functions i parallel so I don't have dependent on externally install programs ?
When this 28 sql calls are done, the find_overlap_gap_make_run_cmd may continue to the next step of work. So the function that triggers parallel calls wait for them complete and then may start on the next step of work.
Thanks .
Lars
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to run in parallel in Postgres
2019-12-05 12:10 How to run in parallel in Postgres Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
@ 2019-12-05 16:42 ` Laurenz Albe <laurenz.albe@cybertec.at>
2019-12-06 08:39 ` Re: How to run in parallel in Postgres Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
2 siblings, 1 reply; 7+ messages in thread
From: Laurenz Albe @ 2019-12-05 16:42 UTC (permalink / raw)
To: Lars Aksel Opsahl <Lars.Opsahl@nibio.no>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
On Thu, 2019-12-05 at 12:10 +0000, Lars Aksel Opsahl wrote:
> have a function that prepares data, so the big job can be run it in parallel.
>
> Today I have solved this by using "Gnu parallel" like this.
> psql testdb -c"\! psql -t -q -o /tmp/run_cmd.sql testdb -c\"SELECT find_overlap_gap_make_run_cmd('sl_lop.overlap_gap_input_t1','geom',4258,'sl_lop.overlap_gap_input_t1_res',50);\"; parallel -j 4
> psql testdb -c :::: /tmp/run_cmd.sql" 2>> /tmp/analyze.log;
>
> The problem here is that I depend on external code which may not be installed.
>
> Since Postgres now supports parallel I was wondering if it's easy to trigger parallel dynamically created SQL calls.
>
> If you look at https://github.com/larsop/find-overlap-and-gap/blob/master/src/test/sql/regress/find_overlap_and_gap... you see that
> find_overlap_gap_make_run_cmd generates as set of 28 sql calls.
>
> So is it in a simple way possible to use Postgres parallel functionality to call this 28 functions i parallel so I don't have dependent on externally install programs ?
>
> When this 28 sql calls are done, the find_overlap_gap_make_run_cmd may continue to the next step of work. So the function that triggers parallel calls wait for them complete and then may start on
> the next step of work.
You cannot run several queries in parallel in a PostgreSQL function.
You may want to have a look at PL/Proxy which might be used for things like that.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to run in parallel in Postgres
2019-12-05 12:10 How to run in parallel in Postgres Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
2019-12-05 16:42 ` Re: How to run in parallel in Postgres Laurenz Albe <laurenz.albe@cybertec.at>
@ 2019-12-06 08:39 ` Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
0 siblings, 0 replies; 7+ messages in thread
From: Lars Aksel Opsahl @ 2019-12-06 08:39 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
>From: Laurenz Albe <laurenz.albe@cybertec.at>
>Sent: Thursday, December 5, 2019 5:42 PM
>To: Lars Aksel Opsahl <Lars.Opsahl@nibio.no>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
>Subject: Re: How to run in parallel in Postgres
>
>On Thu, 2019-12-05 at 12:10 +0000, Lars Aksel Opsahl wrote:
>> have a function that prepares data, so the big job can be run it in parallel.
>>
>> Today I have solved this by using "Gnu parallel" like this.
>> psql testdb -c"\! psql -t -q -o /tmp/run_cmd.sql testdb -c\"SELECT find_overlap_gap_make_run_cmd('sl_lop.overlap_gap_input_t1','geom',4258,'sl_lop.overlap_gap_input_t1_res',50);\"; parallel -j 4
>> psql testdb -c :::: /tmp/run_cmd.sql" 2>> /tmp/analyze.log;
>>
>> The problem here is that I depend on external code which may not be installed.
>>
>> Since Postgres now supports parallel I was wondering if it's easy to trigger parallel dynamically created SQL calls.
>>
>> If you look at https://github.com/larsop/find-overlap-and-gap/blob/master/src/test/sql/regress/find_overlap_and_gap... you see that
>> find_overlap_gap_make_run_cmd generates as set of 28 sql calls.
>>
>> So is it in a simple way possible to use Postgres parallel functionality to call this 28 functions i parallel so I don't have dependent on externally install programs ?
>>
>> When this 28 sql calls are done, the find_overlap_gap_make_run_cmd may continue to the next step of work. So the function that triggers parallel calls wait for them complete and then may start on
>> the next step of work.
>
>You cannot run several queries in parallel in a PostgreSQL function.
>
>You may want to have a look at PL/Proxy which might be used for things like that.
>
>Yours,
>Laurenz Albe
>--
>Cybertec | https://www.cybertec-postgresql.com
Hi
Thanks, I checked it out.
If I understand it correct I have to write the code using plproxy syntax and this means if plproxy is not installed the code will fail.
So the only way now to use built in parallel functionality in Postgres is to use C ?
Do you believe it will possible in the future to run parallel calls from a PostgresSQL function (or is impossible/difficult because of design) ?
Lars
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to run in parallel in Postgres
2019-12-05 12:10 How to run in parallel in Postgres Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
@ 2019-12-07 01:23 ` Ondrej Ivanič <ondrej.ivanic@gmail.com>
2019-12-07 11:17 ` Re: How to run in parallel in Postgres Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
2 siblings, 1 reply; 7+ messages in thread
From: Ondrej Ivanič @ 2019-12-07 01:23 UTC (permalink / raw)
To: ; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Hi Lars,
I have two suggestions:
- `xargs` almost always present and it can run in parallel (-P) but script
needs to be changed:
for((i=1;i<=28;i++)); do echo "SELECT
find_overlap_gap_single_cell('test_data.overlap_gap_input_t1','geom',4258,'test_data.overlap_gap_input_t1_res',${I},28);";
done | xargs -n1 -P 10 psql ...
- `UNION ALL` might trigger parallel execution (you need to mess with the
cost of the function and perhaps other settings):
SELECT find_overlap_gap_single_cell('test_data.overlap_gap_input_t1','geom',
4258,'test_data.overlap_gap_input_t1_res',1,28) UNION ALL
SELECT find_overlap_gap_single_cell('test_data.overlap_gap_input_t1','geom',
4258,'test_data.overlap_gap_input_t1_res',2,28)
...
Cheers,
On Thu, 5 Dec 2019 at 23:11, Lars Aksel Opsahl <Lars.Opsahl@nibio.no> wrote:
> Hi
>
> I have a function that prepares data, so the big job can be run it in
> parallel.
>
> Today I have solved this by using "Gnu parallel" like this.
> psql testdb -c"\! psql -t -q -o /tmp/run_cmd.sql testdb -c\"SELECT
> find_overlap_gap_make_run_cmd('sl_lop.overlap_gap_input_t1','geom',4258,'sl_lop.overlap_gap_input_t1_res',50);\";
> parallel -j 4 psql testdb -c :::: /tmp/run_cmd.sql" 2>> /tmp/analyze.log;
>
> The problem here is that I depend on external code which may not be
> installed.
>
> Since Postgres now supports parallel I was wondering if it's easy to
> trigger parallel dynamically created SQL calls.
>
> If you look at
> https://github.com/larsop/find-overlap-and-gap/blob/master/src/test/sql/regress/find_overlap_and_gap...
> you see that
>
> find_overlap_gap_make_run_cmd generates as set of 28 sql calls.
>
>
> So is it in a simple way possible to use Postgres parallel functionality
> to call this 28 functions i parallel so I don't have dependent
> on externally install programs ?
>
>
> When this 28 sql calls are done, the find_overlap_gap_make_run_cmd may continue
> to the next step of work. So the function that triggers parallel calls wait
> for them complete and then may start on the next step of work.
>
>
> Thanks .
>
>
> Lars
>
>
>
>
>
>
--
Ondrej
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to run in parallel in Postgres
2019-12-05 12:10 How to run in parallel in Postgres Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
2019-12-07 01:23 ` Re: How to run in parallel in Postgres Ondrej Ivanič <ondrej.ivanic@gmail.com>
@ 2019-12-07 11:17 ` Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
0 siblings, 0 replies; 7+ messages in thread
From: Lars Aksel Opsahl @ 2019-12-07 11:17 UTC (permalink / raw)
To: Ondrej Ivanič <ondrej.ivanic@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
>From: Ondrej Ivanič <ondrej.ivanic@gmail.com>
>Sent: Saturday, December 7, 2019 2:23 AM
>Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
>Subject: Re: How to run in parallel in Postgres
>
>Hi Lars,
>
>I have two suggestions:
>
>- `xargs` almost always present and it can run in parallel (-P) but script needs to be changed:
>for((i=1;i<=28;i++)); do echo "SELECT find_overlap_gap_single_cell('test_data.overlap_gap_input_t1','geom',4258,'test_data.overlap_gap_input_t1_res',${I},28);"; done | xargs -n1 -P 10 psql ...
>
>- `UNION ALL` might trigger parallel execution (you need to mess with the cost of the function and perhaps other settings):
>SELECT
> find_overlap_gap_single_cell('test_data.overlap_gap_input_t1','geom',4258,'test_data.overlap_gap_input_t1_res',1,28)
>UNION ALL
>SELECT
> find_overlap_gap_single_cell('test_data.overlap_gap_input_t1','geom',4258,'test_data.overlap_gap_input_t1_res',2,28)
>...
>
>
>Cheers,
>
Hi Ondrej
* Yes using xargs seems be an alternative to GNU parallel and I will have that in mind.
* I did a test using UNION ALL in the branch https://github.com/larsop/find-overlap-and-gap/tree/union_all_parallel but I was not able to trigger Postgres parallel . That may be related to what Justin say about create tables.
Thanks.
Lars
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to run in parallel in Postgres
2019-12-05 12:10 How to run in parallel in Postgres Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
@ 2019-12-07 01:25 ` Justin Pryzby <pryzby@telsasoft.com>
2019-12-07 11:27 ` Re: How to run in parallel in Postgres Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
2 siblings, 1 reply; 7+ messages in thread
From: Justin Pryzby @ 2019-12-07 01:25 UTC (permalink / raw)
To: Lars Aksel Opsahl <Lars.Opsahl@nibio.no>; +Cc: pgsql-performance@lists.postgresql.org
On Thu, Dec 05, 2019 at 12:10:42PM +0000, Lars Aksel Opsahl wrote:
> I have a function that prepares data, so the big job can be run it in parallel.
>
> Since Postgres now supports parallel I was wondering if it's easy to trigger parallel dynamically created SQL calls.
>
> If you look at https://github.com/larsop/find-overlap-and-gap/blob/master/src/test/sql/regress/find_overlap_and_gap... you see that
>
> find_overlap_gap_make_run_cmd generates as set of 28 sql calls.
>
> So is it in a simple way possible to use Postgres parallel functionality to call this 28 functions i parallel so I don't have dependent on externally install programs ?
SELECT find_overlap_gap_single_cell('test_data.overlap_gap_input_t1','geom',4258,'test_data.overlap_gap_input_t1_res',1,28);
SELECT find_overlap_gap_single_cell('test_data.overlap_gap_input_t1','geom',4258,'test_data.overlap_gap_input_t1_res',2,28);
SELECT find_overlap_gap_single_cell('test_data.overlap_gap_input_t1','geom',4258,'test_data.overlap_gap_input_t1_res',3,28);
...
I see that find_overlap_gap_single_cell creates tables, so cannot be run in parallel.
Maybe you could consider rewriting it to return data to its caller instead.
You'd also need to mark it as PARALLEL SAFE, of course.
Your other functions involved should be PARALLEL SAFE too.
Justin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: How to run in parallel in Postgres
2019-12-05 12:10 How to run in parallel in Postgres Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
2019-12-07 01:25 ` Re: How to run in parallel in Postgres Justin Pryzby <pryzby@telsasoft.com>
@ 2019-12-07 11:27 ` Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
0 siblings, 0 replies; 7+ messages in thread
From: Lars Aksel Opsahl @ 2019-12-07 11:27 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
> From: Justin Pryzby <pryzby@telsasoft.com>
> Sent: Saturday, December 7, 2019 2:25 AM
> To: Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
> Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
> Subject: Re: How to run in parallel in Postgres
>
> On Thu, Dec 05, 2019 at 12:10:42PM +0000, Lars Aksel Opsahl wrote:
> > I have a function that prepares data, so the big job can be run it in parallel.
> >
> > Since Postgres now supports parallel I was wondering if it's easy to trigger parallel dynamically created SQL calls.
> >
> > If you look at https://github.com/larsop/find-overlap-and-gap/blob/master/src/test/sql/regress/find_overlap_and_gap... you see that
> >
> > find_overlap_gap_make_run_cmd generates as set of 28 sql calls.
> >
> > So is it in a simple way possible to use Postgres parallel functionality to call this 28 functions i parallel so I don't have dependent on externally install programs ?
>
> SELECT find_overlap_gap_single_cell('test_data.overlap_gap_input_t1','geom',4258,'test_data.overlap_gap_input_t1_res',1,28);
> SELECT find_overlap_gap_single_cell('test_data.overlap_gap_input_t1','geom',4258,'test_data.overlap_gap_input_t1_res',2,28);
> SELECT find_overlap_gap_single_cell('test_data.overlap_gap_input_t1','geom',4258,'test_data.overlap_gap_input_t1_res',3,28);
> ...
>
> I see that find_overlap_gap_single_cell creates tables, so cannot be run in parallel.
> Maybe you could consider rewriting it to return data to its caller instead.
> You'd also need to mark it as PARALLEL SAFE, of course.
> Your other functions involved should be PARALLEL SAFE too.
>
> Justin
Hi Justin
The reason why I don't return the results Is that on very bug tables I usually get memory problems if I return all the results to the master function. So I usually break thing up into small unlogged tables. Then I work on each table separately or in groups. When all steps are done i merge all the small tables together. I this case we only single step, but usually I work many more steps.
But I will keep mind that it may work i parallel if I don't create any child tables but returns the result.
Thanks.
Lars
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2019-12-07 11:27 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-12-05 12:10 How to run in parallel in Postgres Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
2019-12-05 16:42 ` Laurenz Albe <laurenz.albe@cybertec.at>
2019-12-06 08:39 ` Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
2019-12-07 01:23 ` Ondrej Ivanič <ondrej.ivanic@gmail.com>
2019-12-07 11:17 ` Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
2019-12-07 01:25 ` Justin Pryzby <pryzby@telsasoft.com>
2019-12-07 11:27 ` Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox