FAQ
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. +

Search Discussions

Discussion Posts

Previous

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 17 of 21 | next ›
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedJun 15, '06 at 4:05a
activeJun 17, '06 at 5:43p
posts21
users6
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2017 Grokbase