From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200606161603.k5GG3MS27858@candle.pha.pa.us>
Subject: Re: Test request for Stats collector performance improvement
In-Reply-To: <e6td7b$2oqb$1@news.hub.org>
To: Qingqing Zhou <zhouqq@cs.toronto.edu>
Date: Fri, 16 Jun 2006 12:03:22 -0400 (EDT)
CC: pgsql-hackers@postgresql.org
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=US-ASCII


OK, based on reports I have seen, generally stats_query_string adds 50%
to the total runtime of a "SELECT 1" query, and the patch reduces the
overhead to 25%.

However, that 25% is still much too large.  Consider that "SELECT 1" has
to travel from psql to the server, go through the
parser/optimizer/executor, and then return, it is clearly wrong that the
stats_query_string performance hit should be measurable.

I am actually surprised that so few people in the community are
concerned about this.  While we have lots of people studying large
queries, these small queries should also get attention from a
performance perspective.

I have created a new test that also turns off writing of the stats file.
This will not pass regression tests, but it will show the stats write
overhead.

Updated test to be run:

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

1)  Run this script and record the time reported:

	ftp://candle.pha.pa.us/pub/postgresql/mypatches/stat.script

    It should take only a few seconds.  

2)  Modify postgresql.conf:

	stats_command_string = on

    and reload the server.  Do "SELECT * FROM pg_stat_activity;" to verify
    the command string is enabled.  You should see your query in the
    "current query" column.

3)  Rerun the stat.script again and record the time.

4)  Apply this patch to CVS HEAD:

	ftp://candle.pha.pa.us/pub/postgresql/mypatches/stat.nobuffer

5)  Run the stat.script again and record the time.

6)  Revert the patch and apply this patch to CVS HEAD:

	ftp://candle.pha.pa.us/pub/postgresql/mypatches/stat.nobuffer_nowrite

7)  Run the stat.script again and record the time.

8)  Report the four results and your platform via email to
    pgman@candle.pha.pa.us.  Label times:

	stats_command_string = off
	stats_command_string = on
	stat.nobuffer patch
	stat.nobuffer_nowrite patch


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

Qingqing Zhou wrote:
> 
> "Bruce Momjian" <pgman@candle.pha.pa.us> wrote
> >
> > Any idea why there is such a variance in the result?  The second run
> > looks quite slow.
> >
> 
> No luck so far. It is quite repeatble in my machine -- runing times which
> show a long execution time: 2, 11, 14, 21 ... But when I do strace, the
> weiredness disappered totally.  Have we seen any strange things like this
> before?
> 
> Regards,
> Qingqing
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
> 
>                http://archives.postgresql.org
> 

-- 
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +