Re: Overhead for stats_command_string et al, take 2

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Overhead for stats_command_string et al, take 2
Date: 2006-06-26 16:40:53
Message-ID: 200606261640.k5QGera27354@momjian.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-patches


I ran your test with all defaults in 8.1 and CVS HEAD on a BSD/OS dual
Xeon and got:

8.1.X 1.946
HEAD 1.986

I ran the test ten times on three runs and took the middle value.

It is a slowdown of 2%. I used these configure options:

configure \
--with-tcl \
--with-perl \
--with-tclconfig=/u/lib \
--with-includes="/usr/local/include/readline /usr/contrib/include" \
--with-libraries="/usr/local/lib /usr/contrib/lib" \
--with-openssl \
--enable-thread-safety \
--enable-nls

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

Tom Lane wrote:
> I redid my previous measurements after finishing up the weekend's
> hacking. The numbers shown below are elapsed time in seconds for
>
> time psql -f testfile.sql postgres >/dev/null
>
> using CVS HEAD and REL8_1_STABLE branch tip, compiled --enable-debug
> --disable-cassert, no nondefault options except for turning fsync off
> (which doesn't particularly affect read-only tests like these anyway).
> The machines are both running current Fedora Core 5. The older x86
> machine is known to have the slow-gettimeofday problem from previous
> experimentation with EXPLAIN ANALYZE. Each number is the median of 3
> or more tests, rounded off to 0.1 second (I wouldn't put a lot of faith
> in differences of 0.1 sec or so, because of the variance I saw in the
> tests).
>
> x86 x86_64
>
> 8.1 HEAD 8.1 HEAD
>
> 100000 "SELECT 1;" 25.9 27.0 9.2 9.1
> with stats_command_string=1 63.5 27.6 18.7 9.2
> with log_min_duration_statement=100 26.9 27.8 9.6 9.2
> with statement_timeout=100 27.5 28.6 9.6 9.8
> with all 3 features 66.1 29.3 19.5 9.7
>
> BEGIN, 100000 "SELECT 1;", COMMIT 21.2 23.1 8.3 8.4
> with stats_command_string=1 52.3 23.5 15.4 8.5
> with log_min_duration_statement=100 22.1 23.4 8.4 8.4
> with statement_timeout=100 23.7 24.3 8.6 8.8
> with all 3 features 55.2 25.5 16.0 8.8
>
> I chose the log_min_duration_statement and statement_timeout settings
> high enough so that no actual logging or timeout would happen --- the
> point is to measure the measurement overhead.
>
> The good news is that we've pretty much licked the problem of
> stats_command_string costing an unreasonable amount.
>
> The bad news is that except in the stats_command_string cases, HEAD
> is noticeably slower than 8.1 on the machine with slow gettimeofday.
> In the single-transaction test this might be blamed on the addition
> of statement_timestamp support (which requires a gettimeofday per
> statement that wasn't there in 8.1) ... but in the one-transaction-
> per-statement tests that doesn't hold water, because each branch is
> doing a gettimeofday per statement, just in different places.
>
> Can anyone else reproduce this slowdown? It might be only an artifact
> of these particular builds, but it's a bit too consistent in my x86 data
> to just ignore.
>
> BTW, according to "top" the CPU usage percentages in these tests are
> on the order of 55% backend, 45% psql. Methinks psql needs a round
> of performance tuning ...
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

--
Bruce Momjian bruce(at)momjian(dot)us
EnterpriseDB http://www.enterprisedb.com

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

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Jim C. Nasby 2006-06-26 16:46:45 Re: vacuum, performance, and MVCC
Previous Message Greg Stark 2006-06-26 16:31:24 Inheritance, CREATE TABLE LIKE, and partitioned tables

Browse pgsql-patches by date

  From Date Subject
Next Message Bruce Momjian 2006-06-26 17:24:39 Re: [HACKERS] Overhead for stats_command_string et al, take 2
Previous Message Magnus Hagander 2006-06-26 16:31:05 Further patch for VS2005