Re: PGSQL 9.3 - Materialized View - multithreading

From: "Graeme B(dot) Bell" <grb(at)skogoglandskap(dot)no>
To: Nicolas Paris <niparisco(at)gmail(dot)com>, postgres performance list <pgsql-performance(at)postgresql(dot)org>
Subject: Re: PGSQL 9.3 - Materialized View - multithreading
Date: 2014-04-07 10:29:34
Message-ID: 7A0C759C-AE7C-4097-94E3-27F14C20DA48@skogoglandskap.no
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

On 04 Apr 2014, at 18:29, Nicolas Paris <niparisco(at)gmail(dot)com> wrote:

> Hello,
>
> My question is about multiprocess and materialized View.
> http://www.postgresql.org/docs/9.3/static/sql-creatematerializedview.html
> I (will) have something like 3600 materialised views, and I would like to know the way to refresh them in a multithread way
> (anderstand 8 cpu cores -> 8 refresh process in the same time)

Hi Nick,

out of DB solution:

1. Produce a text file which contains the 3600 refresh commands you want to run in parallel. You can do that with select and format() if you don't have a list already.

2. I'm going to simulate your 3600 'refresh' commands here with some select and sleep statements that finish at unknown times.

(In BASH):
for i in {1..3600} ; do echo "echo \"select pg_sleep(1+random()::int*10); select $i\" | psql mydb" ; done > 3600commands

3. Install Gnu Parallel and type:

parallel < 3600commands

4. Parallel will automatically work out the appropriate number of cores/threads for your CPUs, or you can control it manually with -j.
It will also give you a live progress report if you use --progress.
e.g. this command balances 8 jobs at a time, prints a dynamic progress report and dumps stdout to /dev/null

parallel -j 8 --progress < 3600commands > /dev/null

5. If you want to make debugging easier use the parameter --tag to tag output for each command.

Of course it would be much more elegant if someone implemented something like Gnu Parallel inside postgres or psql ... :-)

Hope this helps & have a nice day,

Graeme.

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Ryan Johnson 2014-04-07 10:52:54 Re: SSI slows down over time
Previous Message Johann Spies 2014-04-07 10:25:52 The same query - much different runtimes