Обсуждение: Time to read pg_hba.conf (Re: [PATCHES] [PATCH] Patch to make...)

Tom Lane writes:

> Since pg_hba.conf is re-read on every connection, I've always thought
> it was pretty bogus to bulk it up with that much internal documentation.
> I've not tried to measure how much time it takes the postmaster to skip
> over those 200 comment lines, but it can't be completely negligible.

I've run a simplistic test for this.  I've let psql start 10000 times
sequentially and timed the backend startup.  All times are wall clock
(gettimeofday).  The first checkpoint is after the accept(), the second
before the backend loop begins.  The machine had a load average of 1.00 to
1.50 and wasn't running anything else besides "infrastructure".

default pg_hba.conf
count |   min    |   max    |     avg      |       stddev
-------+----------+----------+--------------+---------------------10000 | 0.024667 | 0.060208 | 0.0298723081 |
0.00746411804719077

pg_hba.conf, all comments removed
count |   min    |   max   |        avg         |       stddev
-------+----------+---------+--------------------+---------------------10000 | 0.022364 | 0.05946 | 0.0262477744000001
|0.00570493964559965
 

So we're looking at a possible 12% win.  I suggest we remove the comments
and direct the user to the Admin Guide.

Btw., in case someone wants to go optimizing, more than 75% of the backend
startup time is spent in InitPostgres():
count |   min   |   max    |     avg      |       stddev
-------+---------+----------+--------------+---------------------10000 | 0.01953 | 0.368216 | 0.0222271679 |
0.00629838985852663

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

Peter Eisentraut <peter_e@gmx.net> writes:
> So we're looking at a possible 12% win.

Many thanks for doing this legwork.

The possible win from not having to read the file at all is probably
somewhat higher than that, but not vastly higher.  Accordingly, I'd
say that pre-parsing the file is not worth the development time needed
to make it happen.  However, moving the comments out is clearly worth
the (very small) amount of effort needed to make that happen.  Any
objections?

> Btw., in case someone wants to go optimizing, more than 75% of the backend
> startup time is spent in InitPostgres():

No surprise, that's where all the initial database access happens.  We'd
need to break it down more to learn anything useful, but I'd bet that
the initial loading of required catalog cache entries is a big chunk.
        regards, tom lane

> Peter Eisentraut <peter_e@gmx.net> writes:
> > So we're looking at a possible 12% win.
> 
> Many thanks for doing this legwork.
> 
> The possible win from not having to read the file at all is probably
> somewhat higher than that, but not vastly higher.  Accordingly, I'd
> say that pre-parsing the file is not worth the development time needed
> to make it happen.  However, moving the comments out is clearly worth
> the (very small) amount of effort needed to make that happen.  Any
> objections?

Let me see if I can cache the contents.  I hate to make things harder to
set us up, even if it is a small thing.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026