Regression test for stats collector

Started by Manfred Koizaralmost 23 years ago6 messagespatches

mkoi-pg@aon.at

almost 23 years ago

With pg_autovacuum becoming increasingly popular it's important to
have a working stats collector. This test is able to discover the
problem that was present in 7.4 Beta 2.

Servus
Manfred

Bruce Momjian

bruce@momjian.us

almost 23 years ago

In reply to: Manfred Koizar (#1)

Re: Regression test for stats collector

Uh, how do you force the stats collector to 'on' before the test is
run?

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

Manfred Koizar wrote:

With pg_autovacuum becoming increasingly popular it's important to
have a working stats collector. This test is able to discover the
problem that was present in 7.4 Beta 2.

Servus
Manfred

diff -ruN ../base/src/test/regress/expected/stats.out src/test/regress/expected/stats.out
--- ../base/src/test/regress/expected/stats.out	1970-01-01 01:00:00.000000000 +0100
+++ src/test/regress/expected/stats.out	2003-09-10 21:01:49.000000000 +0200
@@ -0,0 +1,79 @@
+--
+-- Test Statistics Collector
+--
+-- Must be run after tenk2 has been created (by create_table),
+-- populated (by create_misc) and indexed (by create_index).
+--
+-- conditio sine qua non
+SHOW stats_start_collector;  -- must be on
+ stats_start_collector
+-----------------------
+ on
+(1 row)
+
+-- save counters
+CREATE TEMP TABLE prevstats AS
+SELECT t.seq_scan, t.seq_tup_read, t.idx_scan, t.idx_tup_fetch,
+       (b.heap_blks_read + b.heap_blks_hit) AS heap_blks,
+       (b.idx_blks_read + b.idx_blks_hit) AS idx_blks
+  FROM pg_catalog.pg_stat_user_tables AS t,
+       pg_catalog.pg_statio_user_tables AS b
+ WHERE t.relname='tenk2' AND b.relname='tenk2';
+-- enable statistics
+SET stats_block_level = on;
+SET stats_row_level = on;
+-- helper function
+CREATE FUNCTION sleep(interval) RETURNS integer AS '
+DECLARE
+  endtime timestamp;
+BEGIN
+  endtime := timeofday()::timestamp + $1;
+  WHILE timeofday()::timestamp < endtime LOOP
+  END LOOP;
+  RETURN 0;
+END;
+' LANGUAGE 'plpgsql';
+-- do something
+SELECT count(*) FROM tenk2;
+ count
+-------
+ 10000
+(1 row)
+
+SELECT count(*) FROM tenk2 WHERE unique1 = 1;
+ count
+-------
+     1
+(1 row)
+
+-- let stats collector catch up
+SELECT sleep('0:0:2'::interval);
+ sleep
+-------
+     0
+(1 row)
+
+-- check effects
+SELECT st.seq_scan >= pr.seq_scan + 1,
+       st.seq_tup_read >= pr.seq_tup_read + cl.reltuples,
+       st.idx_scan >= pr.idx_scan + 1,
+       st.idx_tup_fetch >= pr.idx_tup_fetch + 1
+  FROM pg_stat_user_tables AS st, pg_class AS cl, prevstats AS pr
+ WHERE st.relname='tenk2' AND cl.relname='tenk2';
+ ?column? | ?column? | ?column? | ?column?
+----------+----------+----------+----------
+ t        | t        | t        | t
+(1 row)
+
+SELECT st.heap_blks_read + st.heap_blks_hit >= pr.heap_blks + cl.relpages,
+       st.idx_blks_read + st.idx_blks_hit >= pr.idx_blks + 1
+  FROM pg_statio_user_tables AS st, pg_class AS cl, prevstats AS pr
+ WHERE st.relname='tenk2' AND cl.relname='tenk2';
+ ?column? | ?column?
+----------+----------
+ t        | t
+(1 row)
+
+-- clean up
+DROP FUNCTION sleep(interval);
+-- End of Stats Test
diff -ruN ../base/src/test/regress/parallel_schedule src/test/regress/parallel_schedule
--- ../base/src/test/regress/parallel_schedule	2003-09-02 20:48:55.000000000 +0200
+++ src/test/regress/parallel_schedule	2003-09-10 21:02:40.000000000 +0200
@@ -74,4 +74,4 @@
# The sixth group of parallel test
# ----------
# "plpgsql" cannot run concurrently with "rules"
-test: limit plpgsql copy2 temp domain rangefuncs prepare without_oid conversion truncate alter_table sequence polymorphism
+test: limit plpgsql copy2 temp domain rangefuncs prepare without_oid conversion truncate alter_table sequence polymorphism stats
diff -ruN ../base/src/test/regress/serial_schedule src/test/regress/serial_schedule
--- ../base/src/test/regress/serial_schedule	2003-09-02 20:48:55.000000000 +0200
+++ src/test/regress/serial_schedule	2003-09-10 21:03:16.000000000 +0200
@@ -94,3 +94,4 @@
test: alter_table
test: sequence
test: polymorphism
+test: stats
diff -ruN ../base/src/test/regress/sql/stats.sql src/test/regress/sql/stats.sql
--- ../base/src/test/regress/sql/stats.sql	1970-01-01 01:00:00.000000000 +0100
+++ src/test/regress/sql/stats.sql	2003-09-10 21:01:49.000000000 +0200
@@ -0,0 +1,58 @@
+--
+-- Test Statistics Collector
+--
+-- Must be run after tenk2 has been created (by create_table),
+-- populated (by create_misc) and indexed (by create_index).
+--
+
+-- conditio sine qua non
+SHOW stats_start_collector;  -- must be on
+
+-- save counters
+CREATE TEMP TABLE prevstats AS
+SELECT t.seq_scan, t.seq_tup_read, t.idx_scan, t.idx_tup_fetch,
+       (b.heap_blks_read + b.heap_blks_hit) AS heap_blks,
+       (b.idx_blks_read + b.idx_blks_hit) AS idx_blks
+  FROM pg_catalog.pg_stat_user_tables AS t,
+       pg_catalog.pg_statio_user_tables AS b
+ WHERE t.relname='tenk2' AND b.relname='tenk2';
+
+-- enable statistics
+SET stats_block_level = on;
+SET stats_row_level = on;
+
+-- helper function
+CREATE FUNCTION sleep(interval) RETURNS integer AS '
+DECLARE
+  endtime timestamp;
+BEGIN
+  endtime := timeofday()::timestamp + $1;
+  WHILE timeofday()::timestamp < endtime LOOP
+  END LOOP;
+  RETURN 0;
+END;
+' LANGUAGE 'plpgsql';
+
+-- do something
+SELECT count(*) FROM tenk2;
+SELECT count(*) FROM tenk2 WHERE unique1 = 1;
+
+-- let stats collector catch up
+SELECT sleep('0:0:2'::interval);
+
+-- check effects
+SELECT st.seq_scan >= pr.seq_scan + 1,
+       st.seq_tup_read >= pr.seq_tup_read + cl.reltuples,
+       st.idx_scan >= pr.idx_scan + 1,
+       st.idx_tup_fetch >= pr.idx_tup_fetch + 1
+  FROM pg_stat_user_tables AS st, pg_class AS cl, prevstats AS pr
+ WHERE st.relname='tenk2' AND cl.relname='tenk2';
+SELECT st.heap_blks_read + st.heap_blks_hit >= pr.heap_blks + cl.relpages,
+       st.idx_blks_read + st.idx_blks_hit >= pr.idx_blks + 1
+  FROM pg_statio_user_tables AS st, pg_class AS cl, prevstats AS pr
+ WHERE st.relname='tenk2' AND cl.relname='tenk2';
+
+-- clean up
+DROP FUNCTION sleep(interval);
+
+-- End of Stats Test

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

-- 
  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

Neil Conway

neilc@samurai.com

almost 23 years ago

In reply to: Bruce Momjian (#2)

Re: Regression test for stats collector

On Thu, 2003-09-11 at 11:58, Bruce Momjian wrote:

Uh, how do you force the stats collector to 'on' before the test is
run?

The stats collector is on by default (of course, that doesn't apply to
"make installcheck"...)

But this reminds me: I think it would be cool to extend the language we
use for writing regression tests to be more than mere SQL. We could do
with boolean expressions ("IF stats_collector_is_on THEN run_test ELSE
skip_test"), better control over parallel regression tests ("Run all
possible interleavings of tests X, Y, and Z"; "Run X and Y
simultaneously"; etc.), and more.

-Neil

Bruce Momjian

bruce@momjian.us

almost 23 years ago

In reply to: Neil Conway (#3)

Re: Regression test for stats collector

Neil Conway wrote:

On Thu, 2003-09-11 at 11:58, Bruce Momjian wrote:

Uh, how do you force the stats collector to 'on' before the test is
run?

The stats collector is on by default (of course, that doesn't apply to
"make installcheck"...)

Oh I see now, nice. I didn't realize you could turn it on per backend.

-- 
  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

Manfred Koizar

mkoi-pg@aon.at

almost 23 years ago

In reply to: Bruce Momjian (#4)

Re: Regression test for stats collector

On Thu, 11 Sep 2003 23:17:13 -0400 (EDT), Bruce Momjian
<pgman@candle.pha.pa.us> wrote:

The stats collector is on by default (of course, that doesn't apply to
"make installcheck"...)

By doing "appropriate" changes to your installation I guess you can
break almost every test ;-)

Oh I see now, nice. I didn't realize you could turn it on per backend.

stats_start_collector and stats_reset_on_server_start "cannot be
changed after server start".

stats_command_string, stats_block_level, and stats_row_level can be
set per backend.

The test starts with
+SHOW stats_start_collector;  -- must be on
+ stats_start_collector
+-----------------------
+ on
+(1 row)
so that, if the test fails, you can look at the diff and have a chance
to tell whether the stats collector is not activated or not working
properly.

BTW, the test succeeds on 7.3.4, but the patch won't apply cleanly.
If anybody is interested, the patch for 7.3.4 can be fetched from
http://www.pivot.at/pg/25-TestStats_734.diff .

Servus
Manfred

Bruce Momjian

bruce@momjian.us

almost 23 years ago

In reply to: Manfred Koizar (#1)

Re: Regression test for stats collector

Patch applied. Thanks.

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

Manfred Koizar wrote:

With pg_autovacuum becoming increasingly popular it's important to
have a working stats collector. This test is able to discover the
problem that was present in 7.4 Beta 2.

Servus
Manfred

diff -ruN ../base/src/test/regress/expected/stats.out src/test/regress/expected/stats.out
--- ../base/src/test/regress/expected/stats.out	1970-01-01 01:00:00.000000000 +0100
+++ src/test/regress/expected/stats.out	2003-09-10 21:01:49.000000000 +0200
@@ -0,0 +1,79 @@
+--
+-- Test Statistics Collector
+--
+-- Must be run after tenk2 has been created (by create_table),
+-- populated (by create_misc) and indexed (by create_index).
+--
+-- conditio sine qua non
+SHOW stats_start_collector;  -- must be on
+ stats_start_collector
+-----------------------
+ on
+(1 row)
+
+-- save counters
+CREATE TEMP TABLE prevstats AS
+SELECT t.seq_scan, t.seq_tup_read, t.idx_scan, t.idx_tup_fetch,
+       (b.heap_blks_read + b.heap_blks_hit) AS heap_blks,
+       (b.idx_blks_read + b.idx_blks_hit) AS idx_blks
+  FROM pg_catalog.pg_stat_user_tables AS t,
+       pg_catalog.pg_statio_user_tables AS b
+ WHERE t.relname='tenk2' AND b.relname='tenk2';
+-- enable statistics
+SET stats_block_level = on;
+SET stats_row_level = on;
+-- helper function
+CREATE FUNCTION sleep(interval) RETURNS integer AS '
+DECLARE
+  endtime timestamp;
+BEGIN
+  endtime := timeofday()::timestamp + $1;
+  WHILE timeofday()::timestamp < endtime LOOP
+  END LOOP;
+  RETURN 0;
+END;
+' LANGUAGE 'plpgsql';
+-- do something
+SELECT count(*) FROM tenk2;
+ count
+-------
+ 10000
+(1 row)
+
+SELECT count(*) FROM tenk2 WHERE unique1 = 1;
+ count
+-------
+     1
+(1 row)
+
+-- let stats collector catch up
+SELECT sleep('0:0:2'::interval);
+ sleep
+-------
+     0
+(1 row)
+
+-- check effects
+SELECT st.seq_scan >= pr.seq_scan + 1,
+       st.seq_tup_read >= pr.seq_tup_read + cl.reltuples,
+       st.idx_scan >= pr.idx_scan + 1,
+       st.idx_tup_fetch >= pr.idx_tup_fetch + 1
+  FROM pg_stat_user_tables AS st, pg_class AS cl, prevstats AS pr
+ WHERE st.relname='tenk2' AND cl.relname='tenk2';
+ ?column? | ?column? | ?column? | ?column?
+----------+----------+----------+----------
+ t        | t        | t        | t
+(1 row)
+
+SELECT st.heap_blks_read + st.heap_blks_hit >= pr.heap_blks + cl.relpages,
+       st.idx_blks_read + st.idx_blks_hit >= pr.idx_blks + 1
+  FROM pg_statio_user_tables AS st, pg_class AS cl, prevstats AS pr
+ WHERE st.relname='tenk2' AND cl.relname='tenk2';
+ ?column? | ?column?
+----------+----------
+ t        | t
+(1 row)
+
+-- clean up
+DROP FUNCTION sleep(interval);
+-- End of Stats Test
diff -ruN ../base/src/test/regress/parallel_schedule src/test/regress/parallel_schedule
--- ../base/src/test/regress/parallel_schedule	2003-09-02 20:48:55.000000000 +0200
+++ src/test/regress/parallel_schedule	2003-09-10 21:02:40.000000000 +0200
@@ -74,4 +74,4 @@
# The sixth group of parallel test
# ----------
# "plpgsql" cannot run concurrently with "rules"
-test: limit plpgsql copy2 temp domain rangefuncs prepare without_oid conversion truncate alter_table sequence polymorphism
+test: limit plpgsql copy2 temp domain rangefuncs prepare without_oid conversion truncate alter_table sequence polymorphism stats
diff -ruN ../base/src/test/regress/serial_schedule src/test/regress/serial_schedule
--- ../base/src/test/regress/serial_schedule	2003-09-02 20:48:55.000000000 +0200
+++ src/test/regress/serial_schedule	2003-09-10 21:03:16.000000000 +0200
@@ -94,3 +94,4 @@
test: alter_table
test: sequence
test: polymorphism
+test: stats
diff -ruN ../base/src/test/regress/sql/stats.sql src/test/regress/sql/stats.sql
--- ../base/src/test/regress/sql/stats.sql	1970-01-01 01:00:00.000000000 +0100
+++ src/test/regress/sql/stats.sql	2003-09-10 21:01:49.000000000 +0200
@@ -0,0 +1,58 @@
+--
+-- Test Statistics Collector
+--
+-- Must be run after tenk2 has been created (by create_table),
+-- populated (by create_misc) and indexed (by create_index).
+--
+
+-- conditio sine qua non
+SHOW stats_start_collector;  -- must be on
+
+-- save counters
+CREATE TEMP TABLE prevstats AS
+SELECT t.seq_scan, t.seq_tup_read, t.idx_scan, t.idx_tup_fetch,
+       (b.heap_blks_read + b.heap_blks_hit) AS heap_blks,
+       (b.idx_blks_read + b.idx_blks_hit) AS idx_blks
+  FROM pg_catalog.pg_stat_user_tables AS t,
+       pg_catalog.pg_statio_user_tables AS b
+ WHERE t.relname='tenk2' AND b.relname='tenk2';
+
+-- enable statistics
+SET stats_block_level = on;
+SET stats_row_level = on;
+
+-- helper function
+CREATE FUNCTION sleep(interval) RETURNS integer AS '
+DECLARE
+  endtime timestamp;
+BEGIN
+  endtime := timeofday()::timestamp + $1;
+  WHILE timeofday()::timestamp < endtime LOOP
+  END LOOP;
+  RETURN 0;
+END;
+' LANGUAGE 'plpgsql';
+
+-- do something
+SELECT count(*) FROM tenk2;
+SELECT count(*) FROM tenk2 WHERE unique1 = 1;
+
+-- let stats collector catch up
+SELECT sleep('0:0:2'::interval);
+
+-- check effects
+SELECT st.seq_scan >= pr.seq_scan + 1,
+       st.seq_tup_read >= pr.seq_tup_read + cl.reltuples,
+       st.idx_scan >= pr.idx_scan + 1,
+       st.idx_tup_fetch >= pr.idx_tup_fetch + 1
+  FROM pg_stat_user_tables AS st, pg_class AS cl, prevstats AS pr
+ WHERE st.relname='tenk2' AND cl.relname='tenk2';
+SELECT st.heap_blks_read + st.heap_blks_hit >= pr.heap_blks + cl.relpages,
+       st.idx_blks_read + st.idx_blks_hit >= pr.idx_blks + 1
+  FROM pg_statio_user_tables AS st, pg_class AS cl, prevstats AS pr
+ WHERE st.relname='tenk2' AND cl.relname='tenk2';
+
+-- clean up
+DROP FUNCTION sleep(interval);
+
+-- End of Stats Test

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

-- 
  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