pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPGSQL 9.3 - Materialized View - multithreading
15+ messages / 5 participants
[nested] [flat]
* PGSQL 9.3 - Materialized View - multithreading
@ 2014-04-04 16:29 Nicolas Paris <niparisco@gmail.com>
0 siblings, 2 replies; 15+ messages in thread
From: Nicolas Paris @ 2014-04-04 16:29 UTC (permalink / raw)
To: pgsql-performance
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)
Thanks a lot,
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: PGSQL 9.3 - Materialized View - multithreading
@ 2014-04-04 16:54 Thom Brown <thom@linux.com>
parent: Nicolas Paris <niparisco@gmail.com>
1 sibling, 1 reply; 15+ messages in thread
From: Thom Brown @ 2014-04-04 16:54 UTC (permalink / raw)
To: Nicolas Paris <niparisco@gmail.com>; +Cc: pgsql-performance
On 4 April 2014 17:29, Nicolas Paris <niparisco@gmail.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)
The only thing that immediately comes to mind would be running a
rather hacky DO function in 4 separate sessions:
DO $$
DECLARE
session CONSTANT BIGINT := 0;
rec RECORD;
BEGIN
FOR rec IN SELECT quote_ident(nspname) || '.' ||
quote_ident(relname) AS mv FROM pg_class c INNER JOIN pg_namespace n
ON c.relnamespace = n.oid WHERE relkind = 'm' AND c.oid::bigint % 8 =
session LOOP
RAISE NOTICE 'Refreshing materialized view: %', rec.mv;
EXECUTE 'REFRESH MATERIALIZED VIEW ' || rec.mv || ';';
END LOOP;
END$$ language plpgsql;
Where you would set session to 0 for the first session, 1 for the
next, 2 for the next and 3 for the next, and so on until you reach 7
for the last. These would each be run in a separate parallel session,
although someone may come up with a better solution.
--
Thom
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: PGSQL 9.3 - Materialized View - multithreading
@ 2014-04-04 19:49 PARIS Nicolas <niparisco@gmail.com>
parent: Thom Brown <thom@linux.com>
0 siblings, 1 reply; 15+ messages in thread
From: PARIS Nicolas @ 2014-04-04 19:49 UTC (permalink / raw)
To: Thom Brown <thom@linux.com>; +Cc: pgsql-performance
Thanks,
"The only thing that immediately comes to mind would be running a
rather hacky DO function in 4 separate sessions:"
You mean 8 sessions I guess.
8 separate sessions ?
Have you any idea how to manage sessions ? Is it possible to create
separate session internaly ?
Do I have to make 8 external connection to database, to get 8 process.
It would be great if I could manage session internaly, in a pl/sql by
example.
Le 04/04/2014 18:54, Thom Brown a écrit :
> On 4 April 2014 17:29, Nicolas Paris <niparisco@gmail.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)
>
> The only thing that immediately comes to mind would be running a
> rather hacky DO function in 4 separate sessions:
>
> DO $$
> DECLARE
> session CONSTANT BIGINT := 0;
> rec RECORD;
> BEGIN
> FOR rec IN SELECT quote_ident(nspname) || '.' ||
> quote_ident(relname) AS mv FROM pg_class c INNER JOIN pg_namespace n
> ON c.relnamespace = n.oid WHERE relkind = 'm' AND c.oid::bigint % 8 =
> session LOOP
> RAISE NOTICE 'Refreshing materialized view: %', rec.mv;
> EXECUTE 'REFRESH MATERIALIZED VIEW ' || rec.mv || ';';
> END LOOP;
> END$$ language plpgsql;
>
> Where you would set session to 0 for the first session, 1 for the
> next, 2 for the next and 3 for the next, and so on until you reach 7
> for the last. These would each be run in a separate parallel session,
> although someone may come up with a better solution.
>
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: PGSQL 9.3 - Materialized View - multithreading
@ 2014-04-04 19:57 Thom Brown <thom@linux.com>
parent: PARIS Nicolas <niparisco@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Thom Brown @ 2014-04-04 19:57 UTC (permalink / raw)
To: PARIS Nicolas <niparisco@gmail.com>; +Cc: pgsql-performance
On 4 April 2014 20:49, PARIS Nicolas <niparisco@gmail.com> wrote:
> Thanks,
>
> "The only thing that immediately comes to mind would be running a
> rather hacky DO function in 4 separate sessions:"
> You mean 8 sessions I guess.
Yes, typo.
> 8 separate sessions ?
> Have you any idea how to manage sessions ? Is it possible to create
> separate session internaly ?
> Do I have to make 8 external connection to database, to get 8 process.
> It would be great if I could manage session internaly, in a pl/sql by
> example.
Well you can't have multiple sessions per connection, so yes, you'd
need to issue each of them in separate connections.
I can't think of a more convenient way of doing it, but the solution
I've proposed isn't particularly elegant anyway.
--
Thom
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: PGSQL 9.3 - Materialized View - multithreading
@ 2014-04-04 20:07 PARIS Nicolas <niparisco@gmail.com>
parent: Thom Brown <thom@linux.com>
0 siblings, 1 reply; 15+ messages in thread
From: PARIS Nicolas @ 2014-04-04 20:07 UTC (permalink / raw)
To: Thom Brown <thom@linux.com>; +Cc: pgsql-performance
Ok thanks,
And what about triggers. 8 triggers based on the same event won't be
multithreaded ?
Le 04/04/2014 21:57, Thom Brown a écrit :
> On 4 April 2014 20:49, PARIS Nicolas <niparisco@gmail.com> wrote:
>> Thanks,
>>
>> "The only thing that immediately comes to mind would be running a
>> rather hacky DO function in 4 separate sessions:"
>> You mean 8 sessions I guess.
>
> Yes, typo.
>
>> 8 separate sessions ?
>> Have you any idea how to manage sessions ? Is it possible to create
>> separate session internaly ?
>> Do I have to make 8 external connection to database, to get 8 process.
>> It would be great if I could manage session internaly, in a pl/sql by
>> example.
>
> Well you can't have multiple sessions per connection, so yes, you'd
> need to issue each of them in separate connections.
>
> I can't think of a more convenient way of doing it, but the solution
> I've proposed isn't particularly elegant anyway.
>
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: PGSQL 9.3 - Materialized View - multithreading
@ 2014-04-04 20:14 Thom Brown <thom@linux.com>
parent: PARIS Nicolas <niparisco@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Thom Brown @ 2014-04-04 20:14 UTC (permalink / raw)
To: PARIS Nicolas <niparisco@gmail.com>; +Cc: pgsql-performance
On 4 April 2014 21:07, PARIS Nicolas <niparisco@gmail.com> wrote:
> Ok thanks,
>
> And what about triggers. 8 triggers based on the same event won't be
> multithreaded ?
I'm not clear on how triggers come into this. You can't have triggers
on materialized views, and they don't fire triggers on tables or views
that they are based on.
--
Thom
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: PGSQL 9.3 - Materialized View - multithreading
@ 2014-04-04 20:26 PARIS Nicolas <niparisco@gmail.com>
parent: Thom Brown <thom@linux.com>
0 siblings, 2 replies; 15+ messages in thread
From: PARIS Nicolas @ 2014-04-04 20:26 UTC (permalink / raw)
To: Thom Brown <thom@linux.com>; +Cc: pgsql-performance
this postgres documentation :
http://www.postgresql.org/docs/9.3/static/ecpg-connect.html
says it is actually possible to manage connection in C stored procedure.
I may be wrong...
Le 04/04/2014 22:14, Thom Brown a écrit :
> lear on how triggers come into this. You can't have triggers
> on materialized views, and they don't fire triggers on tables or views
> that they are based o
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: PGSQL 9.3 - Materialized View - multithreading
@ 2014-04-04 20:34 ktm@rice.edu <ktm@rice.edu>
parent: PARIS Nicolas <niparisco@gmail.com>
1 sibling, 0 replies; 15+ messages in thread
From: ktm@rice.edu @ 2014-04-04 20:34 UTC (permalink / raw)
To: PARIS Nicolas <niparisco@gmail.com>; +Cc: Thom Brown <thom@linux.com>; pgsql-performance
On Fri, Apr 04, 2014 at 10:26:22PM +0200, PARIS Nicolas wrote:
> this postgres documentation :
> http://www.postgresql.org/docs/9.3/static/ecpg-connect.html
> says it is actually possible to manage connection in C stored procedure.
>
> I may be wrong...
>
>
> Le 04/04/2014 22:14, Thom Brown a écrit :
> > lear on how triggers come into this. You can't have triggers
> > on materialized views, and they don't fire triggers on tables or views
> > that they are based o
>
Hi,
I do not know if it can be used in this fashion, but could pl/proxy be
used by defining a cluster to be the same server and use a partitioned
remote call? Someone with pl/proxy experience may have more information.
Regards,
Ken
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: PGSQL 9.3 - Materialized View - multithreading
@ 2014-04-06 19:07 Thom Brown <thom@linux.com>
parent: PARIS Nicolas <niparisco@gmail.com>
1 sibling, 1 reply; 15+ messages in thread
From: Thom Brown @ 2014-04-06 19:07 UTC (permalink / raw)
To: PARIS Nicolas <niparisco@gmail.com>; +Cc: pgsql-performance
On 4 April 2014 21:26, PARIS Nicolas <niparisco@gmail.com> wrote:
> this postgres documentation :
> http://www.postgresql.org/docs/9.3/static/ecpg-connect.html
> says it is actually possible to manage connection in C stored procedure.
>
> I may be wrong...
That page doesn't refer to triggers at all, so I'm still not sure what you mean.
--
Thom
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: PGSQL 9.3 - Materialized View - multithreading
@ 2014-04-06 22:21 PARIS Nicolas <niparisco@gmail.com>
parent: Thom Brown <thom@linux.com>
0 siblings, 0 replies; 15+ messages in thread
From: PARIS Nicolas @ 2014-04-06 22:21 UTC (permalink / raw)
To: Thom Brown <thom@linux.com>; +Cc: pgsql-performance
Right, not refering triggers, seems to be kind of mix C/sql compiled (=
external).
To conclude :
- pl/proxy, it appears difficult, and not designed to.
- pgAgent (supposed to apply jobs in a multithreaded way)
- bash (xargs does the job)
- external scripts (R, python, perl...)
So I will test pgAgent and feedback it
Thanks
Le 06/04/2014 21:07, Thom Brown a écrit :
> On 4 April 2014 21:26, PARIS Nicolas <niparisco@gmail.com> wrote:
>> this postgres documentation :
>> http://www.postgresql.org/docs/9.3/static/ecpg-connect.html
>> says it is actually possible to manage connection in C stored procedure.
>>
>> I may be wrong...
>
> That page doesn't refer to triggers at all, so I'm still not sure what you mean.
>
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: PGSQL 9.3 - Materialized View - multithreading
@ 2014-04-07 10:29 Graeme B. Bell <grb@skogoglandskap.no>
parent: Nicolas Paris <niparisco@gmail.com>
1 sibling, 1 reply; 15+ messages in thread
From: Graeme B. Bell @ 2014-04-07 10:29 UTC (permalink / raw)
To: Nicolas Paris <niparisco@gmail.com>; pgsql-performance
On 04 Apr 2014, at 18:29, Nicolas Paris <niparisco@gmail.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.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: PGSQL 9.3 - Materialized View - multithreading
@ 2014-04-07 12:49 Nicolas Paris <niparisco@gmail.com>
parent: Graeme B. Bell <grb@skogoglandskap.no>
0 siblings, 1 reply; 15+ messages in thread
From: Nicolas Paris @ 2014-04-07 12:49 UTC (permalink / raw)
To: Graeme B. Bell <grb@skogoglandskap.no>; +Cc: pgsql-performance
Hello,
Thanks for this clear explanation !
Then I have a sub-question :
Supposed I have 3600 materialised views say 600 mat views from 6 main
table. (A,B,C,D,E,F are repetead 600 times with some differences)
Is it faster to :
1) parallel refresh 600 time A, then 600 time B etc,
OR
2) parallel refresh 600 time A,B,C,D,E,F
I guess 1) is faster because they are 600 access to same table loaded in
memory ? But do parallel access to the same table implies concurency
and bad performance ?
Thanks
Nicolas PARIS
2014-04-07 12:29 GMT+02:00 Graeme B. Bell <grb@skogoglandskap.no>:
> On 04 Apr 2014, at 18:29, Nicolas Paris <niparisco@gmail.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.
>
>
>
>
>
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: PGSQL 9.3 - Materialized View - multithreading
@ 2014-04-07 12:59 Graeme B. Bell <grb@skogoglandskap.no>
parent: Nicolas Paris <niparisco@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Graeme B. Bell @ 2014-04-07 12:59 UTC (permalink / raw)
To: Nicolas Paris <niparisco@gmail.com>; +Cc: pgsql-performance
Hi again Nick.
Glad it helped.
Generally, I would expect that doing all the A's first, then all the B's, and so on, would be fastest since you can re-use the data from cache.
Concurrency when reading isn't generally a problem. Lots of things can read at the same time and it will be nice and fast.
It's concurrent writes or concurrent read/write of the same data item that causes problems with locking. That shouldn't be happening here, judging by your description.
If possible, try to make sure nothing is modifying those source tables A/B/C/D/E/F when you are doing your view refresh.
Graeme.
On 07 Apr 2014, at 14:49, Nicolas Paris <niparisco@gmail.com> wrote:
> Hello,
> Thanks for this clear explanation !
>
> Then I have a sub-question :
> Supposed I have 3600 materialised views say 600 mat views from 6 main table. (A,B,C,D,E,F are repetead 600 times with some differences)
> Is it faster to :
> 1) parallel refresh 600 time A, then 600 time B etc,
> OR
> 2) parallel refresh 600 time A,B,C,D,E,F
>
> I guess 1) is faster because they are 600 access to same table loaded in memory ? But do parallel access to the same table implies concurency
> and bad performance ?
>
> Thanks
>
> Nicolas PARIS
>
>
> 2014-04-07 12:29 GMT+02:00 Graeme B. Bell <grb@skogoglandskap.no>:
> On 04 Apr 2014, at 18:29, Nicolas Paris <niparisco@gmail.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.
>
>
>
>
>
>
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: PGSQL 9.3 - Materialized View - multithreading
@ 2014-04-07 13:56 Nicolas Paris <niparisco@gmail.com>
parent: Graeme B. Bell <grb@skogoglandskap.no>
0 siblings, 1 reply; 15+ messages in thread
From: Nicolas Paris @ 2014-04-07 13:56 UTC (permalink / raw)
To: Graeme B. Bell <grb@skogoglandskap.no>; +Cc: pgsql-performance
Excellent.
Maybe the last sub-question :
Those 3600 mat views do have *indexes*.
I guess I will get better performances in *dropping indexes* first, then
refresh, then *re-creating indexes*.
Are there other way to improve performances (like mat views storage
parameters<http://www.postgresql.org/docs/9.3/static/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS;),
because this routines will be at night, and need to be finished quickly.
Thanks
Nicolas PARIS
2014-04-07 14:59 GMT+02:00 Graeme B. Bell <grb@skogoglandskap.no>:
>
> Hi again Nick.
>
> Glad it helped.
>
> Generally, I would expect that doing all the A's first, then all the B's,
> and so on, would be fastest since you can re-use the data from cache.
>
> Concurrency when reading isn't generally a problem. Lots of things can
> read at the same time and it will be nice and fast.
> It's concurrent writes or concurrent read/write of the same data item that
> causes problems with locking. That shouldn't be happening here, judging by
> your description.
>
> If possible, try to make sure nothing is modifying those source tables
> A/B/C/D/E/F when you are doing your view refresh.
>
> Graeme.
>
> On 07 Apr 2014, at 14:49, Nicolas Paris <niparisco@gmail.com> wrote:
>
> > Hello,
> > Thanks for this clear explanation !
> >
> > Then I have a sub-question :
> > Supposed I have 3600 materialised views say 600 mat views from 6 main
> table. (A,B,C,D,E,F are repetead 600 times with some differences)
> > Is it faster to :
> > 1) parallel refresh 600 time A, then 600 time B etc,
> > OR
> > 2) parallel refresh 600 time A,B,C,D,E,F
> >
> > I guess 1) is faster because they are 600 access to same table loaded in
> memory ? But do parallel access to the same table implies concurency
> > and bad performance ?
> >
> > Thanks
> >
> > Nicolas PARIS
> >
> >
> > 2014-04-07 12:29 GMT+02:00 Graeme B. Bell <grb@skogoglandskap.no>:
> > On 04 Apr 2014, at 18:29, Nicolas Paris <niparisco@gmail.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.
> >
> >
> >
> >
> >
> >
>
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: PGSQL 9.3 - Materialized View - multithreading
@ 2014-04-07 14:05 Graeme B. Bell <grb@skogoglandskap.no>
parent: Nicolas Paris <niparisco@gmail.com>
0 siblings, 0 replies; 15+ messages in thread
From: Graeme B. Bell @ 2014-04-07 14:05 UTC (permalink / raw)
To: Nicolas Paris <niparisco@gmail.com>; +Cc: pgsql-performance
- http://wiki.postgresql.org/wiki/Performance_Optimization
- run it on the most powerful machine you can find
- get some more memory
- get a big (512-1TB) SSD drive
- avoid recalculating the same things over and over. if your views have many similar elements, then calculate those first into a partial result, then build the final views from the partial result.
- make sure your source tables are fully indexed and have good statistics
- run all the views once with \timing and keep track of how long they took. Fix the slow ones.
G
On 07 Apr 2014, at 15:56, Nicolas Paris <niparisco@gmail.com> wrote:
> Excellent.
>
> Maybe the last sub-question :
>
> Those 3600 mat views do have indexes.
> I guess I will get better performances in dropping indexes first, then refresh, then re-creating indexes.
>
> Are there other way to improve performances (like mat views storage parameters), because this routines will be at night, and need to be finished quickly.
>
> Thanks
>
> Nicolas PARIS
>
>
> 2014-04-07 14:59 GMT+02:00 Graeme B. Bell <grb@skogoglandskap.no>:
>
> Hi again Nick.
>
> Glad it helped.
>
> Generally, I would expect that doing all the A's first, then all the B's, and so on, would be fastest since you can re-use the data from cache.
>
> Concurrency when reading isn't generally a problem. Lots of things can read at the same time and it will be nice and fast.
> It's concurrent writes or concurrent read/write of the same data item that causes problems with locking. That shouldn't be happening here, judging by your description.
>
> If possible, try to make sure nothing is modifying those source tables A/B/C/D/E/F when you are doing your view refresh.
>
> Graeme.
>
> On 07 Apr 2014, at 14:49, Nicolas Paris <niparisco@gmail.com> wrote:
>
> > Hello,
> > Thanks for this clear explanation !
> >
> > Then I have a sub-question :
> > Supposed I have 3600 materialised views say 600 mat views from 6 main table. (A,B,C,D,E,F are repetead 600 times with some differences)
> > Is it faster to :
> > 1) parallel refresh 600 time A, then 600 time B etc,
> > OR
> > 2) parallel refresh 600 time A,B,C,D,E,F
> >
> > I guess 1) is faster because they are 600 access to same table loaded in memory ? But do parallel access to the same table implies concurency
> > and bad performance ?
> >
> > Thanks
> >
> > Nicolas PARIS
> >
> >
> > 2014-04-07 12:29 GMT+02:00 Graeme B. Bell <grb@skogoglandskap.no>:
> > On 04 Apr 2014, at 18:29, Nicolas Paris <niparisco@gmail.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.
> >
> >
> >
> >
> >
> >
>
>
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2014-04-07 14:05 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-04-04 16:29 PGSQL 9.3 - Materialized View - multithreading Nicolas Paris <niparisco@gmail.com>
2014-04-04 16:54 ` Thom Brown <thom@linux.com>
2014-04-04 19:49 ` PARIS Nicolas <niparisco@gmail.com>
2014-04-04 19:57 ` Thom Brown <thom@linux.com>
2014-04-04 20:07 ` PARIS Nicolas <niparisco@gmail.com>
2014-04-04 20:14 ` Thom Brown <thom@linux.com>
2014-04-04 20:26 ` PARIS Nicolas <niparisco@gmail.com>
2014-04-04 20:34 ` ktm@rice.edu <ktm@rice.edu>
2014-04-06 19:07 ` Thom Brown <thom@linux.com>
2014-04-06 22:21 ` PARIS Nicolas <niparisco@gmail.com>
2014-04-07 10:29 ` Graeme B. Bell <grb@skogoglandskap.no>
2014-04-07 12:49 ` Nicolas Paris <niparisco@gmail.com>
2014-04-07 12:59 ` Graeme B. Bell <grb@skogoglandskap.no>
2014-04-07 13:56 ` Nicolas Paris <niparisco@gmail.com>
2014-04-07 14:05 ` Graeme B. Bell <grb@skogoglandskap.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