Re: loading libraries on Postmaster startup

While using PL/R in a web based application, I noticed that the library
load and initialization time is significant enough to be annoying. So I
wrote a quick hack to load and initialize the library on postmaster
startup. This way, the backends get a fully initialized copy of the
interpreter when they are forked. The hack was to postmaster.c just
after the SSL initialization code at about line 650 (just remembered
this is 7.3.2 though):

if (true) /* later use startup GUC var */
{
char *fullname = "$libdir/plr.so";
char *funcname = "start_interp";
func_ptr initfunc;

initfunc = (func_ptr)
load_external_function(fullname, funcname, true, NULL);
(*initfunc)();
}

(I also had to add a #define for func_ptr)

This brings me to a couple questions:

1. Is there anything inherently dangerous with this approach?
My light testing seems to show that it works quite well for
my purpose.

2. It seems to me that other libraries such as those for PL/Tcl,
PL/Perl, etc may have the same issue. Is there any merit in
a GUC variable to allow libraries such as this to be loaded
and initialized at postmaster start? I'll generalize this and
send in a patch if there is interest.

Joe

Joe Conway <mail(at)joeconway(dot)com> writes:

> 2. It seems to me that other libraries such as those for PL/Tcl,
> PL/Perl, etc may have the same issue. Is there any merit in
> a GUC variable to allow libraries such as this to be loaded
> and initialized at postmaster start? I'll generalize this and
> send in a patch if there is interest.

A similar situation arises with mod_perl. Because perl is quite heavy-weight
and systems often need lots of packages with static data it's common to load a
startup.pl script that just loads lots of packages before the Apache server
forks. This reduces memory usage drastically.

The main gotcha is that you have to be careful about resources that you don't
want shared. The typical case is database handles which are sockets that
wouldn't be happy having two processes writing and reading on them.

At first blush it seemed unlikely you would have a database connection in an
embedded perl script. But then, hm, that would be a sly way of doing
interdatabase connections. In any case there are other situations where you
might want to have open file descriptors or sockets lying around.

So in short, not only is it useful, but it would be valuable to allow
mechanism to cause the language to load modules before forking. But there have
to be prominent caveats that no such shared packages should create resources
that can't be safely shared.

--
greg

Joe Conway <mail(at)joeconway(dot)com> writes:
> [ what about autoloading libraries into the postmaster? ]

I can see a couple possible downsides: (a) the library might have some
weird behavior across fork boundaries; (b) the additional memory space
that has to be duplicated into child processes will cost something per
child launch, even if the child never uses it. But these are only
arguments that it might not *always* be a prudent thing to do, not that
we shouldn't give the DBA the tool to do it if he wants. So fire away.

(I seem to recall Peter muttering about linking plperl, pltcl, etc
statically into the backend; which would reduce the need for this.
But it would not eliminate it ... and he hasn't done it anyway...)

regards, tom lane

Peter Eisentraut wrote:
> Joe Conway writes:
>
>>So I wrote a quick hack to load and initialize the library on postmaster
>>startup.
>
> On glibc systems you can probably do this using the environment variable
> LD_PRELOAD. I guess others have a similar mechanism.
>

Hmmm. I could try that. But I found during testing that the loading was
actually not the slow part, it was running the initialization function
for the interpreter that was. I wonder if there is there any way to get
an initialization function to automatically execute?

Joe

Joe Conway writes:

> So I wrote a quick hack to load and initialize the library on postmaster
> startup.

On glibc systems you can probably do this using the environment variable
LD_PRELOAD. I guess others have a similar mechanism.

--
Peter Eisentraut peter_e(at)gmx(dot)net

Tom Lane wrote:
> Joe Conway <mail(at)joeconway(dot)com> writes:
>>[ what about autoloading libraries into the postmaster? ]
>
> I can see a couple possible downsides: (a) the library might have some
> weird behavior across fork boundaries; (b) the additional memory space
> that has to be duplicated into child processes will cost something per
> child launch, even if the child never uses it. But these are only
> arguments that it might not *always* be a prudent thing to do, not that
> we shouldn't give the DBA the tool to do it if he wants. So fire away.

Here is a patch for the above, including a documentation update. It
creates a new GUC variable "preload_libraries", that accepts a list in
the form:

preload_libraries = '$libdir/mylib1:initfunc,$libdir/mylib2'

If ":initfunc" is omitted or not found, no initialization function is
executed, but the library is still preloaded. If "$libdir/mylib" isn't
found, the postmaster refuses to start.

In my testing with PL/R, it reduces the first call to a PL/R function
(after connecting) from almost 2 seconds, down to about 8 ms.

If there are no objections, please apply.

Thanks,

Joe

Joe Conway <mail(at)joeconway(dot)com> writes:
> In my testing with PL/R, it reduces the first call to a PL/R function
> (after connecting) from almost 2 seconds, down to about 8 ms.

Hm, pretty significant. Can you measure any per-fork cost (ie, the loss
incurred by children that don't use PL/R)? Is there any measurable
benefit for our other PLs (plperl etc)?

regards, tom lane

Tom Lane wrote:
> Joe Conway <mail(at)joeconway(dot)com> writes:
>
>>In my testing with PL/R, it reduces the first call to a PL/R function
>>(after connecting) from almost 2 seconds, down to about 8 ms.
>
> Hm, pretty significant. Can you measure any per-fork cost (ie, the loss
> incurred by children that don't use PL/R)? Is there any measurable
> benefit for our other PLs (plperl etc)?
>

Haven't done so yet, but will do and report back later today.

Joe

Joe Conway writes:

> Here is a patch for the above, including a documentation update. It
> creates a new GUC variable "preload_libraries", that accepts a list in
> the form:

Have you tried using existing dynamic linker functionality instead of
putting already existing functionality into PostgreSQL?

--
Peter Eisentraut peter_e(at)gmx(dot)net

Tom Lane wrote:
> Joe Conway <mail(at)joeconway(dot)com> writes:
>
>>In my testing with PL/R, it reduces the first call to a PL/R function
>>(after connecting) from almost 2 seconds, down to about 8 ms.
>
> Hm, pretty significant. Can you measure any per-fork cost (ie, the loss
> incurred by children that don't use PL/R)? Is there any measurable
> benefit for our other PLs (plperl etc)?

Here's what I got:

10000 connect/disconnect in tight loop
----------------------------------------------------------
condition time top
----------------------------------------------------------
with no preload 87 seconds ~10% CPU, ~2.2 MB
with plr preload 133 seconds ~10% CPU, ~13 MB
with plperl preload 92 seconds ~10% CPU, ~3.2 MB
with pltcl preload 88 seconds ~10% CPU, ~2.3 MB
with plpython preload 93 seconds ~10% CPU, ~2.3 MB

1000 connect/"select some_simple_func()"/disconnect in tight loop
------------------------------------------------------------------
condition time top
------------------------------------------------------------------
plr-func without preload 739 seconds ~60% CPU, ~13 MB
plr-func with preload 26 seconds ~10% CPU, ~13 MB
plperl-func without preload 46 seconds ~4% CPU, ~3.2 MB
plperl-func with preload 33 seconds ~3% CPU, ~3.2 MB
pltcl-func without preload 22 seconds ~5% CPU, ~2.3 MB
pltcl-func with preload 17 seconds ~4% CPU, ~2.3 MB
plpython-func without preload 33 seconds ~4% CPU, ~2.3 MB
plpython-func with preload 31 seconds ~4% CPU, ~2.3 MB

Joe

Peter Eisentraut wrote:
> Joe Conway writes:
>
>>Here is a patch for the above, including a documentation update. It
>>creates a new GUC variable "preload_libraries", that accepts a list in
>>the form:
>
> Have you tried using existing dynamic linker functionality instead of
> putting already existing functionality into PostgreSQL?
>

Sorry for my ignorance, but can you be more specific?

Are you referring to LD_PRELOAD? As I said before, it doesn't help
preloading the library if I can't also run an initialization function.
Do you know a way to do that? If so, is it portable?

Joe

Joe Conway writes:

> Are you referring to LD_PRELOAD? As I said before, it doesn't help
> preloading the library if I can't also run an initialization function.
> Do you know a way to do that? If so, is it portable?

With GCC you can use __attribute__((constructor)) after the initialization
function. It's pretty ugly to do right, admittedly.

--
Peter Eisentraut peter_e(at)gmx(dot)net

Joe Conway wrote:
> Tom Lane wrote:
>> Joe Conway <mail(at)joeconway(dot)com> writes:
>>> In my testing with PL/R, it reduces the first call to a PL/R function
>>> (after connecting) from almost 2 seconds, down to about 8 ms.
>>
>> Hm, pretty significant. Can you measure any per-fork cost (ie, the loss
>> incurred by children that don't use PL/R)? Is there any measurable
>> benefit for our other PLs (plperl etc)?
>
> Here's what I got:
>
> 10000 connect/disconnect in tight loop
> ----------------------------------------------------------
> condition time top
> ----------------------------------------------------------
> with no preload 87 seconds ~10% CPU, ~2.2 MB
> with plr preload 133 seconds ~10% CPU, ~13 MB
> with plperl preload 92 seconds ~10% CPU, ~3.2 MB
> with pltcl preload 88 seconds ~10% CPU, ~2.3 MB
> with plpython preload 93 seconds ~10% CPU, ~2.3 MB
>
>
> 1000 connect/"select some_simple_func()"/disconnect in tight loop
> ------------------------------------------------------------------
> condition time top
> ------------------------------------------------------------------
> plr-func without preload 739 seconds ~60% CPU, ~13 MB
> plr-func with preload 26 seconds ~10% CPU, ~13 MB
> plperl-func without preload 46 seconds ~4% CPU, ~3.2 MB
> plperl-func with preload 33 seconds ~3% CPU, ~3.2 MB
> pltcl-func without preload 22 seconds ~5% CPU, ~2.3 MB
> pltcl-func with preload 17 seconds ~4% CPU, ~2.3 MB
> plpython-func without preload 33 seconds ~4% CPU, ~2.3 MB
> plpython-func with preload 31 seconds ~4% CPU, ~2.3 MB
>

Here's an updated copy of the patch. The original patch was failing due
to code drift in cvs.

Peter's suggestion (__attribute__((constructor))) is interesting, but it
appears to be a gcc specific extension, and hence non-portable.

I'd still like to see this applied if there are no other objections.
From the above info it is apparent that the benefit outweighs the cost
for plperl and pltcl (although not as significantly as for plr).

Joe

Your patch has been added to the PostgreSQL unapplied patches list at:

http://momjian.postgresql.org/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

---------------------------------------------------------------------------

Joe Conway wrote:
> Joe Conway wrote:
> > Tom Lane wrote:
> >> Joe Conway <mail(at)joeconway(dot)com> writes:
> >>> In my testing with PL/R, it reduces the first call to a PL/R function
> >>> (after connecting) from almost 2 seconds, down to about 8 ms.
> >>
> >> Hm, pretty significant. Can you measure any per-fork cost (ie, the loss
> >> incurred by children that don't use PL/R)? Is there any measurable
> >> benefit for our other PLs (plperl etc)?
> >
> > Here's what I got:
> >
> > 10000 connect/disconnect in tight loop
> > ----------------------------------------------------------
> > condition time top
> > ----------------------------------------------------------
> > with no preload 87 seconds ~10% CPU, ~2.2 MB
> > with plr preload 133 seconds ~10% CPU, ~13 MB
> > with plperl preload 92 seconds ~10% CPU, ~3.2 MB
> > with pltcl preload 88 seconds ~10% CPU, ~2.3 MB
> > with plpython preload 93 seconds ~10% CPU, ~2.3 MB
> >
> >
> > 1000 connect/"select some_simple_func()"/disconnect in tight loop
> > ------------------------------------------------------------------
> > condition time top
> > ------------------------------------------------------------------
> > plr-func without preload 739 seconds ~60% CPU, ~13 MB
> > plr-func with preload 26 seconds ~10% CPU, ~13 MB
> > plperl-func without preload 46 seconds ~4% CPU, ~3.2 MB
> > plperl-func with preload 33 seconds ~3% CPU, ~3.2 MB
> > pltcl-func without preload 22 seconds ~5% CPU, ~2.3 MB
> > pltcl-func with preload 17 seconds ~4% CPU, ~2.3 MB
> > plpython-func without preload 33 seconds ~4% CPU, ~2.3 MB
> > plpython-func with preload 31 seconds ~4% CPU, ~2.3 MB
> >
>
> Here's an updated copy of the patch. The original patch was failing due
> to code drift in cvs.
>
> Peter's suggestion (__attribute__((constructor))) is interesting, but it
> appears to be a gcc specific extension, and hence non-portable.
>
> I'd still like to see this applied if there are no other objections.
> From the above info it is apparent that the benefit outweighs the cost
> for plperl and pltcl (although not as significantly as for plr).
>
> Joe

> Index: doc/src/sgml/runtime.sgml
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/doc/src/sgml/runtime.sgml,v
> retrieving revision 1.169
> diff -c -r1.169 runtime.sgml
> *** doc/src/sgml/runtime.sgml 19 Feb 2003 04:06:28 -0000 1.169
> --- doc/src/sgml/runtime.sgml 27 Feb 2003 18:46:39 -0000
> ***************
> *** 1781,1786 ****
> --- 1781,1815 ----
> </varlistentry>
>
> <varlistentry>
> + <term><varname>PRELOAD_LIBRARIES</varname> (<type>string</type>)</term>
> + <indexterm><primary>preload_libraries</></>
> + <listitem>
> + <para>
> + This variable specifies one or more shared libraries that are to be
> + preloaded at Postmaster start. An initialization function can also be
> + optionally specified by adding a colon followed by the name of the
> + initialization function after the library name. For example
> + <literal>'$libdir/mylib:init_mylib'</literal> would cause <literal>mylib</>
> + to be preloaded and <literal>init_mylib</> to be executed. If more than
> + one library is to be loaded, they must be delimited with a comma.
> + </para>
> +
> + <para>
> + If <literal>mylib</> is not found, the postmaster will fail to start.
> + However, if <literal>init_mylib</> is not found, <literal>mylib</> will
> + still be preloaded without executing the initialization function.
> + </para>
> +
> + <para>
> + By preloading a shared library (and initializing it if applicable),
> + the library startup time is avoided when the library is used later in a
> + specific backend. However there is a cost in terms of memory duplication
> + as every backend is forked, whether or not the library is used.
> + </para>
> + </listitem>
> + </varlistentry>
> +
> + <varlistentry>
> <term><varname>REGEX_FLAVOR</varname> (<type>string</type>)</term>
> <indexterm><primary>regular expressions</></>
> <listitem>
> Index: src/backend/postmaster/postmaster.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/backend/postmaster/postmaster.c,v
> retrieving revision 1.307
> diff -c -r1.307 postmaster.c
> *** src/backend/postmaster/postmaster.c 23 Feb 2003 04:48:19 -0000 1.307
> --- src/backend/postmaster/postmaster.c 27 Feb 2003 18:46:39 -0000
> ***************
> *** 205,210 ****
> --- 205,212 ----
> bool Log_connections = false;
> bool Db_user_namespace = false;
>
> + /* list of library:init-function to be preloaded */
> + char *preload_libraries_string = NULL;
>
> /* Startup/shutdown state */
> static pid_t StartupPID = 0,
> ***************
> *** 644,649 ****
> --- 646,658 ----
> if (EnableSSL)
> secure_initialize();
> #endif
> +
> + /*
> + * process any libraries that should be preloaded and
> + * optionally pre-initialized
> + */
> + if (preload_libraries_string)
> + process_preload_libraries(preload_libraries_string);
>
> /*
> * Fork away from controlling terminal, if -S specified.
> Index: src/backend/utils/init/miscinit.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/backend/utils/init/miscinit.c,v
> retrieving revision 1.100
> diff -c -r1.100 miscinit.c
> *** src/backend/utils/init/miscinit.c 27 Jan 2003 00:51:06 -0000 1.100
> --- src/backend/utils/init/miscinit.c 27 Feb 2003 18:46:39 -0000
> ***************
> *** 1044,1046 ****
> --- 1044,1135 ----
> "which is not compatible with this version %s.",
> file_major, file_minor, version_string);
> }
> +
> + /*-------------------------------------------------------------------------
> + * Library preload support
> + *-------------------------------------------------------------------------
> + */
> +
> + #if defined(__mc68000__) && defined(__ELF__)
> + typedef int32 ((*func_ptr) ());
> + #else
> + typedef char *((*func_ptr) ());
> + #endif
> +
> + /*
> + * process any libraries that should be preloaded and
> + * optionally pre-initialized
> + */
> + void
> + process_preload_libraries(char *preload_libraries_string)
> + {
> + char *rawstring;
> + List *elemlist;
> + List *l;
> +
> + if (preload_libraries_string == NULL)
> + return;
> +
> + /* Need a modifiable copy of string */
> + rawstring = pstrdup(preload_libraries_string);
> +
> + /* Parse string into list of identifiers */
> + if (!SplitIdentifierString(rawstring, ',', &elemlist))
> + {
> + /* syntax error in list */
> + pfree(rawstring);
> + freeList(elemlist);
> + elog(LOG, "invalid list syntax for preload_libraries configuration option");
> + }
> +
> + foreach(l, elemlist)
> + {
> + char *tok = (char *) lfirst(l);
> + char *sep = strstr(tok, ":");
> + char *filename = NULL;
> + char *funcname = NULL;
> + func_ptr initfunc;
> +
> + if (sep)
> + {
> + /*
> + * a colon separator implies there is an initialization function
> + * that we need to run in addition to loading the library
> + */
> + size_t filename_len = sep - tok;
> + size_t funcname_len = strlen(tok) - filename_len - 1;
> +
> + filename = (char *) palloc(filename_len + 1);
> + memset(filename, '\0', filename_len + 1);
> + snprintf(filename, filename_len + 1, "%s", tok);
> +
> + funcname = (char *) palloc(funcname_len + 1);
> + memset(funcname, '\0', funcname_len + 1);
> + snprintf(funcname, funcname_len + 1, "%s", sep + 1);
> + }
> + else
> + {
> + /*
> + * no separator -- just load the library
> + */
> + filename = pstrdup(tok);
> + funcname = NULL;
> + }
> +
> + initfunc = (func_ptr) load_external_function(filename, funcname, false, NULL);
> + if (initfunc)
> + (*initfunc)();
> +
> + elog(LOG, "preloaded library %s with initialization function %s", filename, funcname);
> +
> + if (filename != NULL)
> + pfree(filename);
> +
> + if (funcname != NULL)
> + pfree(funcname);
> + }
> +
> + pfree(rawstring);
> + freeList(elemlist);
> + }
> +
> Index: src/backend/utils/misc/guc.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/backend/utils/misc/guc.c,v
> retrieving revision 1.115
> diff -c -r1.115 guc.c
> *** src/backend/utils/misc/guc.c 23 Feb 2003 23:27:21 -0000 1.115
> --- src/backend/utils/misc/guc.c 27 Feb 2003 18:46:39 -0000
> ***************
> *** 60,65 ****
> --- 60,66 ----
> extern bool autocommit;
> extern int CommitDelay;
> extern int CommitSiblings;
> + extern char *preload_libraries_string;
>
> #ifdef HAVE_SYSLOG
> extern char *Syslog_facility;
> ***************
> *** 812,817 ****
> --- 813,824 ----
> {
> {"lc_time", PGC_USERSET}, &locale_time,
> "C", locale_time_assign, NULL
> + },
> +
> + {
> + {"preload_libraries", PGC_POSTMASTER, GUC_LIST_INPUT | GUC_LIST_QUOTE},
> + &preload_libraries_string,
> + "", NULL, NULL
> },
>
> {
> Index: src/backend/utils/misc/postgresql.conf.sample
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/backend/utils/misc/postgresql.conf.sample,v
> retrieving revision 1.70
> diff -c -r1.70 postgresql.conf.sample
> *** src/backend/utils/misc/postgresql.conf.sample 6 Feb 2003 20:25:33 -0000 1.70
> --- src/backend/utils/misc/postgresql.conf.sample 27 Feb 2003 18:46:39 -0000
> ***************
> *** 213,216 ****
> --- 213,217 ----
> #transform_null_equals = false
> #statement_timeout = 0 # 0 is disabled, in milliseconds
> #db_user_namespace = false
> + #preload_libraries = ''
>
> Index: src/include/miscadmin.h
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/include/miscadmin.h,v
> retrieving revision 1.116
> diff -c -r1.116 miscadmin.h
> *** src/include/miscadmin.h 22 Feb 2003 05:57:45 -0000 1.116
> --- src/include/miscadmin.h 27 Feb 2003 18:46:39 -0000
> ***************
> *** 288,293 ****
> --- 288,294 ----
> unsigned long id2);
>
> extern void ValidatePgVersion(const char *path);
> + extern void process_preload_libraries(char *preload_libraries_string);
>
> /* these externs do not belong here... */
> extern void IgnoreSystemIndexes(bool mode);

>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

More recent patch applied.

---------------------------------------------------------------------------

Joe Conway wrote:
> Tom Lane wrote:
> > Joe Conway <mail(at)joeconway(dot)com> writes:
> >>[ what about autoloading libraries into the postmaster? ]
> >
> > I can see a couple possible downsides: (a) the library might have some
> > weird behavior across fork boundaries; (b) the additional memory space
> > that has to be duplicated into child processes will cost something per
> > child launch, even if the child never uses it. But these are only
> > arguments that it might not *always* be a prudent thing to do, not that
> > we shouldn't give the DBA the tool to do it if he wants. So fire away.
>
> Here is a patch for the above, including a documentation update. It
> creates a new GUC variable "preload_libraries", that accepts a list in
> the form:
>
> preload_libraries = '$libdir/mylib1:initfunc,$libdir/mylib2'
>
> If ":initfunc" is omitted or not found, no initialization function is
> executed, but the library is still preloaded. If "$libdir/mylib" isn't
> found, the postmaster refuses to start.
>
> In my testing with PL/R, it reduces the first call to a PL/R function
> (after connecting) from almost 2 seconds, down to about 8 ms.
>
> If there are no objections, please apply.
>
> Thanks,
>
> Joe

> Index: doc/src/sgml/runtime.sgml
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/doc/src/sgml/runtime.sgml,v
> retrieving revision 1.169
> diff -c -r1.169 runtime.sgml
> *** doc/src/sgml/runtime.sgml 19 Feb 2003 04:06:28 -0000 1.169
> --- doc/src/sgml/runtime.sgml 22 Feb 2003 05:59:08 -0000
> ***************
> *** 1781,1786 ****
> --- 1781,1815 ----
> </varlistentry>
>
> <varlistentry>
> + <term><varname>PRELOAD_LIBRARIES</varname> (<type>string</type>)</term>
> + <indexterm><primary>preload_libraries</></>
> + <listitem>
> + <para>
> + This variable specifies one or more shared libraries that are to be
> + preloaded at Postmaster start. An initialization function can also be
> + optionally specified by adding a colon followed by the name of the
> + initialization function after the library name. For example
> + <literal>'$libdir/mylib:init_mylib'</literal> would cause <literal>mylib</>
> + to be preloaded and <literal>init_mylib</> to be executed. If more than
> + one library is to be loaded, they must be delimited with a comma.
> + </para>
> +
> + <para>
> + If <literal>mylib</> is not found, the postmaster will fail to start.
> + However, if <literal>init_mylib</> is not found, <literal>mylib</> will
> + still be preloaded without executing the initialization function.
> + </para>
> +
> + <para>
> + By preloading a shared library (and initializing it if applicable),
> + the library startup time is avoided when the library is used later in a
> + specific backend. However there is a cost in terms of memory duplication
> + as every backend is forked, whether or not the library is used.
> + </para>
> + </listitem>
> + </varlistentry>
> +
> + <varlistentry>
> <term><varname>REGEX_FLAVOR</varname> (<type>string</type>)</term>
> <indexterm><primary>regular expressions</></>
> <listitem>
> Index: src/backend/postmaster/postmaster.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/backend/postmaster/postmaster.c,v
> retrieving revision 1.306
> diff -c -r1.306 postmaster.c
> *** src/backend/postmaster/postmaster.c 25 Jan 2003 05:19:46 -0000 1.306
> --- src/backend/postmaster/postmaster.c 22 Feb 2003 04:29:44 -0000
> ***************
> *** 205,210 ****
> --- 205,212 ----
> bool Log_connections = false;
> bool Db_user_namespace = false;
>
> + /* list of library:init-function to be preloaded */
> + char *preload_libraries_string = NULL;
>
> /* Startup/shutdown state */
> static pid_t StartupPID = 0,
> ***************
> *** 644,649 ****
> --- 646,658 ----
> if (EnableSSL)
> secure_initialize();
> #endif
> +
> + /*
> + * process any libraries that should be preloaded and
> + * optionally pre-initialized
> + */
> + if (preload_libraries_string)
> + process_preload_libraries(preload_libraries_string);
>
> /*
> * Fork away from controlling terminal, if -S specified.
> Index: src/backend/utils/init/miscinit.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/backend/utils/init/miscinit.c,v
> retrieving revision 1.100
> diff -c -r1.100 miscinit.c
> *** src/backend/utils/init/miscinit.c 27 Jan 2003 00:51:06 -0000 1.100
> --- src/backend/utils/init/miscinit.c 22 Feb 2003 04:29:44 -0000
> ***************
> *** 1044,1046 ****
> --- 1044,1135 ----
> "which is not compatible with this version %s.",
> file_major, file_minor, version_string);
> }
> +
> + /*-------------------------------------------------------------------------
> + * Library preload support
> + *-------------------------------------------------------------------------
> + */
> +
> + #if defined(__mc68000__) && defined(__ELF__)
> + typedef int32 ((*func_ptr) ());
> + #else
> + typedef char *((*func_ptr) ());
> + #endif
> +
> + /*
> + * process any libraries that should be preloaded and
> + * optionally pre-initialized
> + */
> + void
> + process_preload_libraries(char *preload_libraries_string)
> + {
> + char *rawstring;
> + List *elemlist;
> + List *l;
> +
> + if (preload_libraries_string == NULL)
> + return;
> +
> + /* Need a modifiable copy of string */
> + rawstring = pstrdup(preload_libraries_string);
> +
> + /* Parse string into list of identifiers */
> + if (!SplitIdentifierString(rawstring, ',', &elemlist))
> + {
> + /* syntax error in list */
> + pfree(rawstring);
> + freeList(elemlist);
> + elog(LOG, "invalid list syntax for preload_libraries configuration option");
> + }
> +
> + foreach(l, elemlist)
> + {
> + char *tok = (char *) lfirst(l);
> + char *sep = strstr(tok, ":");
> + char *filename = NULL;
> + char *funcname = NULL;
> + func_ptr initfunc;
> +
> + if (sep)
> + {
> + /*
> + * a colon separator implies there is an initialization function
> + * that we need to run in addition to loading the library
> + */
> + size_t filename_len = sep - tok;
> + size_t funcname_len = strlen(tok) - filename_len - 1;
> +
> + filename = (char *) palloc(filename_len + 1);
> + memset(filename, '\0', filename_len + 1);
> + snprintf(filename, filename_len + 1, "%s", tok);
> +
> + funcname = (char *) palloc(funcname_len + 1);
> + memset(funcname, '\0', funcname_len + 1);
> + snprintf(funcname, funcname_len + 1, "%s", sep + 1);
> + }
> + else
> + {
> + /*
> + * no separator -- just load the library
> + */
> + filename = pstrdup(tok);
> + funcname = NULL;
> + }
> +
> + initfunc = (func_ptr) load_external_function(filename, funcname, false, NULL);
> + if (initfunc)
> + (*initfunc)();
> +
> + elog(LOG, "preloaded library %s with initialization function %s", filename, funcname);
> +
> + if (filename != NULL)
> + pfree(filename);
> +
> + if (funcname != NULL)
> + pfree(funcname);
> + }
> +
> + pfree(rawstring);
> + freeList(elemlist);
> + }
> +
> Index: src/backend/utils/misc/guc.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/backend/utils/misc/guc.c,v
> retrieving revision 1.114
> diff -c -r1.114 guc.c
> *** src/backend/utils/misc/guc.c 6 Feb 2003 20:25:33 -0000 1.114
> --- src/backend/utils/misc/guc.c 22 Feb 2003 06:01:33 -0000
> ***************
> *** 61,66 ****
> --- 61,67 ----
> extern int CommitDelay;
> extern int CommitSiblings;
> extern bool FixBTree;
> + extern char *preload_libraries_string;
>
> #ifdef HAVE_SYSLOG
> extern char *Syslog_facility;
> ***************
> *** 817,822 ****
> --- 818,829 ----
> {
> {"lc_time", PGC_USERSET}, &locale_time,
> "C", locale_time_assign, NULL
> + },
> +
> + {
> + {"preload_libraries", PGC_POSTMASTER, GUC_LIST_INPUT | GUC_LIST_QUOTE},
> + &preload_libraries_string,
> + "", NULL, NULL
> },
>
> {
> Index: src/backend/utils/misc/postgresql.conf.sample
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/backend/utils/misc/postgresql.conf.sample,v
> retrieving revision 1.70
> diff -c -r1.70 postgresql.conf.sample
> *** src/backend/utils/misc/postgresql.conf.sample 6 Feb 2003 20:25:33 -0000 1.70
> --- src/backend/utils/misc/postgresql.conf.sample 22 Feb 2003 04:29:44 -0000
> ***************
> *** 213,216 ****
> --- 213,217 ----
> #transform_null_equals = false
> #statement_timeout = 0 # 0 is disabled, in milliseconds
> #db_user_namespace = false
> + #preload_libraries = ''
>
> Index: src/include/miscadmin.h
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/include/miscadmin.h,v
> retrieving revision 1.115
> diff -c -r1.115 miscadmin.h
> *** src/include/miscadmin.h 9 Jan 2003 18:00:24 -0000 1.115
> --- src/include/miscadmin.h 22 Feb 2003 04:29:44 -0000
> ***************
> *** 292,297 ****
> --- 292,298 ----
> unsigned long id2);
>
> extern void ValidatePgVersion(const char *path);
> + extern void process_preload_libraries(char *preload_libraries_string);
>
> /* these externs do not belong here... */
> extern void IgnoreSystemIndexes(bool mode);

>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Newest patch applied. Thanks.

---------------------------------------------------------------------------

Joe Conway wrote:
> Joe Conway wrote:
> > Tom Lane wrote:
> >> Joe Conway <mail(at)joeconway(dot)com> writes:
> >>> In my testing with PL/R, it reduces the first call to a PL/R function
> >>> (after connecting) from almost 2 seconds, down to about 8 ms.
> >>
> >> Hm, pretty significant. Can you measure any per-fork cost (ie, the loss
> >> incurred by children that don't use PL/R)? Is there any measurable
> >> benefit for our other PLs (plperl etc)?
> >
> > Here's what I got:
> >
> > 10000 connect/disconnect in tight loop
> > ----------------------------------------------------------
> > condition time top
> > ----------------------------------------------------------
> > with no preload 87 seconds ~10% CPU, ~2.2 MB
> > with plr preload 133 seconds ~10% CPU, ~13 MB
> > with plperl preload 92 seconds ~10% CPU, ~3.2 MB
> > with pltcl preload 88 seconds ~10% CPU, ~2.3 MB
> > with plpython preload 93 seconds ~10% CPU, ~2.3 MB
> >
> >
> > 1000 connect/"select some_simple_func()"/disconnect in tight loop
> > ------------------------------------------------------------------
> > condition time top
> > ------------------------------------------------------------------
> > plr-func without preload 739 seconds ~60% CPU, ~13 MB
> > plr-func with preload 26 seconds ~10% CPU, ~13 MB
> > plperl-func without preload 46 seconds ~4% CPU, ~3.2 MB
> > plperl-func with preload 33 seconds ~3% CPU, ~3.2 MB
> > pltcl-func without preload 22 seconds ~5% CPU, ~2.3 MB
> > pltcl-func with preload 17 seconds ~4% CPU, ~2.3 MB
> > plpython-func without preload 33 seconds ~4% CPU, ~2.3 MB
> > plpython-func with preload 31 seconds ~4% CPU, ~2.3 MB
> >
>
> Here's an updated copy of the patch. The original patch was failing due
> to code drift in cvs.
>
> Peter's suggestion (__attribute__((constructor))) is interesting, but it
> appears to be a gcc specific extension, and hence non-portable.
>
> I'd still like to see this applied if there are no other objections.
> From the above info it is apparent that the benefit outweighs the cost
> for plperl and pltcl (although not as significantly as for plr).
>
> Joe

> Index: doc/src/sgml/runtime.sgml
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/doc/src/sgml/runtime.sgml,v
> retrieving revision 1.169
> diff -c -r1.169 runtime.sgml
> *** doc/src/sgml/runtime.sgml 19 Feb 2003 04:06:28 -0000 1.169
> --- doc/src/sgml/runtime.sgml 27 Feb 2003 18:46:39 -0000
> ***************
> *** 1781,1786 ****
> --- 1781,1815 ----
> </varlistentry>
>
> <varlistentry>
> + <term><varname>PRELOAD_LIBRARIES</varname> (<type>string</type>)</term>
> + <indexterm><primary>preload_libraries</></>
> + <listitem>
> + <para>
> + This variable specifies one or more shared libraries that are to be
> + preloaded at Postmaster start. An initialization function can also be
> + optionally specified by adding a colon followed by the name of the
> + initialization function after the library name. For example
> + <literal>'$libdir/mylib:init_mylib'</literal> would cause <literal>mylib</>
> + to be preloaded and <literal>init_mylib</> to be executed. If more than
> + one library is to be loaded, they must be delimited with a comma.
> + </para>
> +
> + <para>
> + If <literal>mylib</> is not found, the postmaster will fail to start.
> + However, if <literal>init_mylib</> is not found, <literal>mylib</> will
> + still be preloaded without executing the initialization function.
> + </para>
> +
> + <para>
> + By preloading a shared library (and initializing it if applicable),
> + the library startup time is avoided when the library is used later in a
> + specific backend. However there is a cost in terms of memory duplication
> + as every backend is forked, whether or not the library is used.
> + </para>
> + </listitem>
> + </varlistentry>
> +
> + <varlistentry>
> <term><varname>REGEX_FLAVOR</varname> (<type>string</type>)</term>
> <indexterm><primary>regular expressions</></>
> <listitem>
> Index: src/backend/postmaster/postmaster.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/backend/postmaster/postmaster.c,v
> retrieving revision 1.307
> diff -c -r1.307 postmaster.c
> *** src/backend/postmaster/postmaster.c 23 Feb 2003 04:48:19 -0000 1.307
> --- src/backend/postmaster/postmaster.c 27 Feb 2003 18:46:39 -0000
> ***************
> *** 205,210 ****
> --- 205,212 ----
> bool Log_connections = false;
> bool Db_user_namespace = false;
>
> + /* list of library:init-function to be preloaded */
> + char *preload_libraries_string = NULL;
>
> /* Startup/shutdown state */
> static pid_t StartupPID = 0,
> ***************
> *** 644,649 ****
> --- 646,658 ----
> if (EnableSSL)
> secure_initialize();
> #endif
> +
> + /*
> + * process any libraries that should be preloaded and
> + * optionally pre-initialized
> + */
> + if (preload_libraries_string)
> + process_preload_libraries(preload_libraries_string);
>
> /*
> * Fork away from controlling terminal, if -S specified.
> Index: src/backend/utils/init/miscinit.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/backend/utils/init/miscinit.c,v
> retrieving revision 1.100
> diff -c -r1.100 miscinit.c
> *** src/backend/utils/init/miscinit.c 27 Jan 2003 00:51:06 -0000 1.100
> --- src/backend/utils/init/miscinit.c 27 Feb 2003 18:46:39 -0000
> ***************
> *** 1044,1046 ****
> --- 1044,1135 ----
> "which is not compatible with this version %s.",
> file_major, file_minor, version_string);
> }
> +
> + /*-------------------------------------------------------------------------
> + * Library preload support
> + *-------------------------------------------------------------------------
> + */
> +
> + #if defined(__mc68000__) && defined(__ELF__)
> + typedef int32 ((*func_ptr) ());
> + #else
> + typedef char *((*func_ptr) ());
> + #endif
> +
> + /*
> + * process any libraries that should be preloaded and
> + * optionally pre-initialized
> + */
> + void
> + process_preload_libraries(char *preload_libraries_string)
> + {
> + char *rawstring;
> + List *elemlist;
> + List *l;
> +
> + if (preload_libraries_string == NULL)
> + return;
> +
> + /* Need a modifiable copy of string */
> + rawstring = pstrdup(preload_libraries_string);
> +
> + /* Parse string into list of identifiers */
> + if (!SplitIdentifierString(rawstring, ',', &elemlist))
> + {
> + /* syntax error in list */
> + pfree(rawstring);
> + freeList(elemlist);
> + elog(LOG, "invalid list syntax for preload_libraries configuration option");
> + }
> +
> + foreach(l, elemlist)
> + {
> + char *tok = (char *) lfirst(l);
> + char *sep = strstr(tok, ":");
> + char *filename = NULL;
> + char *funcname = NULL;
> + func_ptr initfunc;
> +
> + if (sep)
> + {
> + /*
> + * a colon separator implies there is an initialization function
> + * that we need to run in addition to loading the library
> + */
> + size_t filename_len = sep - tok;
> + size_t funcname_len = strlen(tok) - filename_len - 1;
> +
> + filename = (char *) palloc(filename_len + 1);
> + memset(filename, '\0', filename_len + 1);
> + snprintf(filename, filename_len + 1, "%s", tok);
> +
> + funcname = (char *) palloc(funcname_len + 1);
> + memset(funcname, '\0', funcname_len + 1);
> + snprintf(funcname, funcname_len + 1, "%s", sep + 1);
> + }
> + else
> + {
> + /*
> + * no separator -- just load the library
> + */
> + filename = pstrdup(tok);
> + funcname = NULL;
> + }
> +
> + initfunc = (func_ptr) load_external_function(filename, funcname, false, NULL);
> + if (initfunc)
> + (*initfunc)();
> +
> + elog(LOG, "preloaded library %s with initialization function %s", filename, funcname);
> +
> + if (filename != NULL)
> + pfree(filename);
> +
> + if (funcname != NULL)
> + pfree(funcname);
> + }
> +
> + pfree(rawstring);
> + freeList(elemlist);
> + }
> +
> Index: src/backend/utils/misc/guc.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/backend/utils/misc/guc.c,v
> retrieving revision 1.115
> diff -c -r1.115 guc.c
> *** src/backend/utils/misc/guc.c 23 Feb 2003 23:27:21 -0000 1.115
> --- src/backend/utils/misc/guc.c 27 Feb 2003 18:46:39 -0000
> ***************
> *** 60,65 ****
> --- 60,66 ----
> extern bool autocommit;
> extern int CommitDelay;
> extern int CommitSiblings;
> + extern char *preload_libraries_string;
>
> #ifdef HAVE_SYSLOG
> extern char *Syslog_facility;
> ***************
> *** 812,817 ****
> --- 813,824 ----
> {
> {"lc_time", PGC_USERSET}, &locale_time,
> "C", locale_time_assign, NULL
> + },
> +
> + {
> + {"preload_libraries", PGC_POSTMASTER, GUC_LIST_INPUT | GUC_LIST_QUOTE},
> + &preload_libraries_string,
> + "", NULL, NULL
> },
>
> {
> Index: src/backend/utils/misc/postgresql.conf.sample
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/backend/utils/misc/postgresql.conf.sample,v
> retrieving revision 1.70
> diff -c -r1.70 postgresql.conf.sample
> *** src/backend/utils/misc/postgresql.conf.sample 6 Feb 2003 20:25:33 -0000 1.70
> --- src/backend/utils/misc/postgresql.conf.sample 27 Feb 2003 18:46:39 -0000
> ***************
> *** 213,216 ****
> --- 213,217 ----
> #transform_null_equals = false
> #statement_timeout = 0 # 0 is disabled, in milliseconds
> #db_user_namespace = false
> + #preload_libraries = ''
>
> Index: src/include/miscadmin.h
> ===================================================================
> RCS file: /opt/src/cvs/pgsql-server/src/include/miscadmin.h,v
> retrieving revision 1.116
> diff -c -r1.116 miscadmin.h
> *** src/include/miscadmin.h 22 Feb 2003 05:57:45 -0000 1.116
> --- src/include/miscadmin.h 27 Feb 2003 18:46:39 -0000
> ***************
> *** 288,293 ****
> --- 288,294 ----
> unsigned long id2);
>
> extern void ValidatePgVersion(const char *path);
> + extern void process_preload_libraries(char *preload_libraries_string);
>
> /* these externs do not belong here... */
> extern void IgnoreSystemIndexes(bool mode);

>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Thursday 13 February 2003 22:24, Joe Conway wrote:
> Peter Eisentraut wrote:
> > Joe Conway writes:
> >>So I wrote a quick hack to load and initialize the library on postmaster
> >>startup.
> >
> > On glibc systems you can probably do this using the environment variable
> > LD_PRELOAD. I guess others have a similar mechanism.
>
> Hmmm. I could try that. But I found during testing that the loading was
> actually not the slow part, it was running the initialization function
> for the interpreter that was. I wonder if there is there any way to get
> an initialization function to automatically execute?
>

Joe, did You got the answer to this question ?
I would like to acomplish something like this:
execute some stored procedure on backend(not postmaster) start and exit.
So, it is not the same reason, but it is still the same question.

Regards !