Re: plpyhton

Hello,
Does calling a pl/python function from each database connection load the
python interpreter each time? what are the effects of using pl/python
function in a environment where no. of concurrent connections are more and
each user calls a pl/python function?

Please give the details about how pl/python functions are executed.
Thanks and regards,

CPK

Hello,
Does calling a pl/python function from each database connection load the
python interpreter each time? what are the effects of using pl/python
function in a environment where no. of concurrent connections are more and
each user calls a pl/python function?

Please give the details about how pl/python functions are executed.
Thanks and regards,

CPK

On Wed, Nov 24, 2010 at 11:56:16AM +0530, c k wrote:
> Hello,
> Does calling a pl/python function from each database connection load the
> python interpreter each time? what are the effects of using pl/python
> function in a environment where no. of concurrent connections are more and
> each user calls a pl/python function?
>
> Please give the details about how pl/python functions are executed.
> Thanks and regards,
>
> CPK

I don't know plpython terribly well, but for most PLs, calling them once in a
session loads any interpreter they require. That interpreter remains loaded
for the duration of the session. So each individual connection will load its
own interpreter, once, at the time of the first function call requiring that
interpreter. Most widely used languages also cache various bits of important
information about the functions you run, the first time you run them in a
session, to avoid needing to look up or calculate that information again when
you run the function next time.

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com

Thanks for your reply.
But if a database has 100+ connections then isn't loading any such
interpreter consumes more memory and requires more CPU? Does all PL
languages behave in the same fashion?

Regards,
CPK

On Thu, Nov 25, 2010 at 11:12 AM, Joshua Tolley <eggyknap(at)gmail(dot)com> wrote:

> On Wed, Nov 24, 2010 at 11:56:16AM +0530, c k wrote:
> > Hello,
> > Does calling a pl/python function from each database connection load the
> > python interpreter each time? what are the effects of using pl/python
> > function in a environment where no. of concurrent connections are more
> and
> > each user calls a pl/python function?
> >
> > Please give the details about how pl/python functions are executed.
> > Thanks and regards,
> >
> > CPK
>
> I don't know plpython terribly well, but for most PLs, calling them once in
> a
> session loads any interpreter they require. That interpreter remains loaded
> for the duration of the session. So each individual connection will load
> its
> own interpreter, once, at the time of the first function call requiring
> that
> interpreter. Most widely used languages also cache various bits of
> important
> information about the functions you run, the first time you run them in a
> session, to avoid needing to look up or calculate that information again
> when
> you run the function next time.
>
> --
> Joshua Tolley / eggyknap
> End Point Corporation
> http://www.endpoint.com
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.9 (GNU/Linux)
>
> iEYEARECAAYFAkzt918ACgkQRiRfCGf1UMOFvACdH2lXcrCzbOAgX803Ujvfvw0m
> LOUAoJykSFne3ybVsyabQKJQjlIm6iiQ
> =8rD2
> -----END PGP SIGNATURE-----
>
>

On Fri, Nov 26, 2010 at 05:28:52PM +0530, c k wrote:
> Thanks for your reply.
> But if a database has 100+ connections then isn't loading any such
> interpreter consumes more memory and requires more CPU? Does all PL
> languages behave in the same fashion?

If there are lots of connections, and each calls a plpython function (for
example), then each will load a python interpreter, and certainly that could
add up to serious memory usage. I can't speak for *every* PL; C functions
don't load any special interpreter, for instance, and I don't think there's
anything special you have to load to run SQL functions, beyond what gets
loaded anyway.

If you have problems with hundreds of connections using too much memory when
each loads an interpreter, you ought to consider getting more memory, using a
connection pooler, changing how you do things, or some combination of the
above.

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com

depends on the configuration implemented to enable caching capability for each type
*usually* fastest access can be achived by implementin a Procedure which loads into Procedure Cache
allowing consequent accesses to the Procedure 'in memory' (vs Disk I/O)

Martin Gainty
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.

Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.

> Date: Fri, 26 Nov 2010 09:04:42 -0700
> From: eggyknap(at)gmail(dot)com
> To: shreeseva(dot)learning(at)gmail(dot)com
> CC: pgsql-admin(at)postgresql(dot)org; pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] plpyhton
>
> On Fri, Nov 26, 2010 at 05:28:52PM +0530, c k wrote:
> > Thanks for your reply.
> > But if a database has 100+ connections then isn't loading any such
> > interpreter consumes more memory and requires more CPU? Does all PL
> > languages behave in the same fashion?
>
> If there are lots of connections, and each calls a plpython function (for
> example), then each will load a python interpreter, and certainly that could
> add up to serious memory usage. I can't speak for *every* PL; C functions
> don't load any special interpreter, for instance, and I don't think there's
> anything special you have to load to run SQL functions, beyond what gets
> loaded anyway.
>
> If you have problems with hundreds of connections using too much memory when
> each loads an interpreter, you ought to consider getting more memory, using a
> connection pooler, changing how you do things, or some combination of the
> above.
>
> --
> Joshua Tolley / eggyknap
> End Point Corporation
> http://www.endpoint.com

How can we implement procedure cache? Cane you please give details regarding
this?
It will be helpful for me.

Thanks and regards,
CPK

On Fri, Nov 26, 2010 at 11:53 PM, Martin Gainty <mgainty(at)hotmail(dot)com> wrote:

> depends on the configuration implemented to enable caching
> capability for each type
> *usually* fastest access can be achived by implementin a Procedure which
> loads into Procedure Cache
> allowing consequent accesses to the Procedure 'in memory' (vs Disk I/O)
>
> Martin Gainty
> ______________________________________________
> Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
>
> Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene
> Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte
> Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht
> dient lediglich dem Austausch von Informationen und entfaltet keine
> rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von
> E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
>
> Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.
>
>
>
>
>
>
> > Date: Fri, 26 Nov 2010 09:04:42 -0700
> > From: eggyknap(at)gmail(dot)com
> > To: shreeseva(dot)learning(at)gmail(dot)com
> > CC: pgsql-admin(at)postgresql(dot)org; pgsql-general(at)postgresql(dot)org
> > Subject: Re: [GENERAL] plpyhton
>
> >
> > On Fri, Nov 26, 2010 at 05:28:52PM +0530, c k wrote:
> > > Thanks for your reply.
> > > But if a database has 100+ connections then isn't loading any such
> > > interpreter consumes more memory and requires more CPU? Does all PL
> > > languages behave in the same fashion?
> >
> > If there are lots of connections, and each calls a plpython function (for
> > example), then each will load a python interpreter, and certainly that
> could
> > add up to serious memory usage. I can't speak for *every* PL; C functions
> > don't load any special interpreter, for instance, and I don't think
> there's
> > anything special you have to load to run SQL functions, beyond what gets
> > loaded anyway.
> >
> > If you have problems with hundreds of connections using too much memory
> when
> > each loads an interpreter, you ought to consider getting more memory,
> using a
> > connection pooler, changing how you do things, or some combination of the
> > above.
> >
> > --
> > Joshua Tolley / eggyknap
> > End Point Corporation
> > http://www.endpoint.com
>

On Sat, Nov 27, 2010 at 04:38:27PM +0530, c k wrote:
> How can we implement procedure cache? Cane you please give details regarding
> this?
> It will be helpful for me.

This is something the PL implementation needs to do for you. I finally looked
at the plpython code, and found that it does indeed do this for you. PLPython
caches a procedure's input and return types, and a compiled representation of
the procedure's source.

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com

Joshua Tolley wrote:
-- Start of PGP signed section.
> On Sat, Nov 27, 2010 at 04:38:27PM +0530, c k wrote:
> > How can we implement procedure cache? Cane you please give details regarding
> > this?
> > It will be helpful for me.
>
> This is something the PL implementation needs to do for you. I finally looked
> at the plpython code, and found that it does indeed do this for you. PLPython
> caches a procedure's input and return types, and a compiled representation of
> the procedure's source.

He should also consider using shared_preload_libraries to reduce the
dynamic load time for plpython.so.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +