Re: BUG #13493: pl/pgsql doesn't scale with cpus (PG9.3, 9.4)
От | Graeme |
---|---|
Тема | Re: BUG #13493: pl/pgsql doesn't scale with cpus (PG9.3, 9.4) |
Дата | |
Msg-id | 681C25CA-EC03-4849-B88C-3C7C804A52A8@gmail.com обсуждение исходный текст |
Ответ на | Re: BUG #13493: pl/pgsql doesn't scale with cpus (PG9.3, 9.4) (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-bugs |
> On 8 Jul 2015, at 03:13, Tom Lane <tgl@sss.pgh.pa.us> wrote: >=20 > grb@skogoglandskap.no writes: >> pl/pgsql doesn't scale properly on postgres 9.3/9.4 with multiple = processors >> beyond 2-3 processors for me, regardless of the machine I use or the >> benchmark/project.=20 >=20 >> The benchmark results & source code are here: >> https://github.com/gbb/ppppt >=20 > First off, thanks for providing a concrete test case! It's always a = lot > easier to investigate when a problem can be reproduced locally. >=20 > Having said that ... >=20 > plpgsql is really designed as a glue language for SQL queries, not for > heavy-duty computation, so these examples aren't exactly showing it at > its best. It would be worth your while to consider using some other > convenient programming language, perhaps plperl or plpython or plv8, > if you want to do self-contained calculations on the server side. I'm very interested in trying out plv8, but it is a problem for projects = when you have a team of people who know plpgsql and a lot of plpgsql = legacy code. It would be interesting to know what happens when these functions are = ported into those other languages.=20 > But I think that the main problem you are seeing here is from snapshot > acquisition contention. By default, plpgsql acquires a new snapshot > for each statement inside a function, and that results in a lot of > contention for the ProcArray if you're maxing out a multicore machine. > Depending on what you're actually doing inside the function, you might > well be able to mark it stable or even immutable, which would suppress > the per-statement snapshot acquisitions. On my machine (admittedly = only > 8 cores), the scalability problems in this example pretty much vanish > when I attach "stable" to the function definitions. Thanks for testing it on your own machine and looking into a possible = cause. Do you think that explains both problems (one occurs with table data; = one occurs with work)? Several of the functions that were killing me last year were dynamic SQL = with some internal state, so declaring them harmless and predictable = probably isn't possible. I will need to go back and check.=20 It sounds like marking up functions may help (at least as far as 8 = cores), but there must be a lot of people out there running plpgsql they = have ported over from oracle, (postgis? pgrouting? not sure about their = internals) etc, and various libraries where the functions aren't marked = up and can't easily be marked up. A broader solution in the far off = future would be awesome, if it is ever possible. I am not in a position = currently to provide it, I regret.=20 > There is some discussion going on about improving the scalability of > snapshot acquisition, but nothing will happen in that line before 9.6 > at the earliest. Thanks, this is interesting to know. Thanks again for your time looking into this and for the = stable/immutable tip. Graeme. >=20 > regards, tom lane >=20 >=20 > --=20 > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs
В списке pgsql-bugs по дате отправления:
Предыдущее
От: GraemeДата:
Сообщение: Re: BUG #13493: pl/pgsql doesn't scale with cpus (PG9.3, 9.4)
Следующее
От: Michael PaquierДата:
Сообщение: Re: BUG #13490: Segmentation fault on pg_stat_activity