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



Wed, 12 Feb 2003 20:15:24 -0800
Joe Conway <mail@joeconway.com>
loading libraries on Postmaster startup
Joe Conway <mail@joeconway.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


12 Feb 2003 23:29:39 -0500
Greg Stark <gsstark@mit.edu>
Re: loading libraries on Postmaster startup
Joe Conway <mail@joeconway.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

Wed, 12 Feb 2003 23:55:14 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: loading libraries on Postmaster startup 
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


Thu, 13 Feb 2003 13:24:02 -0800
Joe Conway <mail@joeconway.com>
Re: loading libraries on Postmaster startup
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@gmx.net


Thu, 13 Feb 2003 22:25:24 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: loading libraries on Postmaster startup
Tom Lane wrote:
> Joe Conway <mail@joeconway.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
Sat, 22 Feb 2003 11:09:34 -0800
Joe Conway <mail@joeconway.com>
Re: [HACKERS] loading libraries on Postmaster startup
Joe Conway <mail@joeconway.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

Sat, 22 Feb 2003 14:22:03 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] loading libraries on Postmaster startup 
Tom Lane wrote:
> Joe Conway <mail@joeconway.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


Sat, 22 Feb 2003 12:40:44 -0800
Joe Conway <mail@joeconway.com>
Re: [HACKERS] loading libraries on Postmaster startup
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@gmx.net


Sun, 23 Feb 2003 02:40:21 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: [HACKERS] loading libraries on Postmaster startup
Tom Lane wrote:
> Joe Conway <mail@joeconway.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


Sat, 22 Feb 2003 19:13:07 -0800
Joe Conway <mail@joeconway.com>
Re: [HACKERS] loading libraries on Postmaster startup
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


Sun, 23 Feb 2003 10:10:19 -0800
Joe Conway <mail@joeconway.com>
Re: [HACKERS] loading libraries on Postmaster startup
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@gmx.net


Sun, 23 Feb 2003 21:00:12 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: [HACKERS] loading libraries on Postmaster startup
Joe Conway wrote:
> Tom Lane wrote:
>> Joe Conway <mail@joeconway.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
Thu, 27 Feb 2003 11:33:21 -0800
Joe Conway <mail@joeconway.com>
Re: [HACKERS] loading libraries on Postmaster startup

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@joeconway.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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Mon, 17 Mar 2003 12:28:52 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] loading libraries on Postmaster startup

More recent patch applied.

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

Joe Conway wrote:
> Tom Lane wrote:
> > Joe Conway <mail@joeconway.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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Wed, 19 Mar 2003 23:51:48 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] loading libraries on Postmaster startup

Newest patch applied.  Thanks.

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


Joe Conway wrote:
> Joe Conway wrote:
> > Tom Lane wrote:
> >> Joe Conway <mail@joeconway.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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Wed, 19 Mar 2003 23:51:59 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] loading libraries on Postmaster startup
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 !

Wed, 28 May 2003 19:19:25 +0200
Darko Prenosil <darko.prenosil@finteh.hr>
Re: loading libraries on Postmaster startup