Re: 8.3devel slower than 8.2 under read-only load

On Nov 21, 2007 10:09 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> Please do tests of at least 2 minutes duration. A 1.25 second test isn't
> enough.

I already run far longer tests. It's not a matter of running a test
for long, it's just that each unique query is a little bit slower.

Moreover it's not a pgbench artifact, I have the same results inside
the real application (it's a PHP app).

> Please confirm you have VACUUM ANALYZED each db beforehand.

Yes of course. The dump was just loaded in both databases. Both
databases are identical (the 8.3 db is smaller in size on the disk as
expected).
They are both analyzed.
FYI, I also have the very same database running on a 8.1.x branch
(just loaded and analyzed) and the results are more like the 8.2 ones
than like the 8.3 ones.

> Have you checked that the EXPLAIN ANALYZEs are essentially identical
> also?

I did the test before. Every plan of every query involved in the test
is identical. I removed from the test the one where a different index
was chosen (8.2 chooses a larger index and 8.3 chooses the good one -
Tom fixed something about that not so long ago and it works fine for
us too).

> Is the data identical on both systems?

Freshly loaded from a dump.

> How do the postgresql.conf files differ?

They don't differ at all, except for the new parameters introduced in
8.3 (I let them the default).

> Also, do a run with SELECT 1 FROM table where col = constant; so we can
> assess differences in path without cache or data being relevant.

I don't think the cache is relevant as they are all index scans and
queries don't return a lot of rows. The indexes fit in RAM and I run
each pgbench test several times.

And to answer a question Joshua asked on IRC, pgbench is the same in
both tests. I use the system wide one (8.1.9 from the RH package).

To be sure, here are more information:
** 8.2 **
cityvox=# show shared_buffers;
shared_buffers
----------------
128MB
(1 row)

cityvox=# show work_mem;
work_mem
----------
32MB
(1 row)

cityvox=# show lc_collate;
lc_collate
-------------
fr_FR.UTF-8
(1 row)

cityvox=# select version();
version
----------------------------------------------------------------------------------------------------
PostgreSQL 8.2.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
4.1.1 20070105 (Red Hat 4.1.1-52)
(1 row)

** 8.3 **
cityvox=# ANALYZE;
ANALYZE
cityvox=# show shared_buffers;
shared_buffers
----------------
128MB
(1 row)

cityvox=# show work_mem;
work_mem
----------
32MB
(1 row)

cityvox=# show lc_collate;
lc_collate
-------------
fr_FR.UTF-8
(1 row)

cityvox=# select version();
version
-------------------------------------------------------------------------------------------------------
PostgreSQL 8.3beta2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
4.1.1 20070105 (Red Hat 4.1.1-52)
(1 row)

Then I run the test longer (I run it with 1000 transactions before to
have the data in cache):
** 8.2 **
[ip-dyn151 postgresql]# pgbench -h localhost -p 5482 -U cityvox -f
bench.cityvox.simple.sql -t 100000 -c 1 cityvox
starting vacuum...end.
transaction type: Custom query
scaling factor: 1
number of clients: 1
number of transactions per client: 100000
number of transactions actually processed: 100000/100000
tps = 853.100511 (including connections establishing)
tps = 853.124776 (excluding connections establishing)

** 8.3 **
[ip-dyn151 postgresql]# pgbench -h localhost -p 5483 -U cityvox -f
bench.cityvox.simple.sql -t 100000 -c 1 cityvox
starting vacuum...end.
transaction type: Custom query
scaling factor: 1
number of clients: 1
number of transactions per client: 100000
number of transactions actually processed: 100000/100000
tps = 791.244011 (including connections establishing)
tps = 791.268316 (excluding connections establishing)

Then let's simplify the test a bit with only one query:

[root(at)ip-dyn151 postgresql]# cat bench.cityvox.really.simple.sql
select libvil from vilsitelang where codelang='FRA' and codevil='LYO'

** 8.2 **
cityvox=> EXPLAIN ANALYZE select libvil from vilsitelang where
codelang='FRA' and codevil='LYO';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Index Scan using pk_vilsitelang on vilsitelang (cost=0.00..4.27
rows=1 width=12) (actual time=0.028..0.031 rows=1 loops=1)
Index Cond: (((codevil)::text = 'LYO'::text) AND ((codelang)::text
= 'FRA'::text))
Total runtime: 0.071 ms
(3 rows)

[ip-dyn151 postgresql]# pgbench -h localhost -p 5482 -U cityvox -f
bench.cityvox.really.simple.sql -t 100000 -c 1 cityvox
starting vacuum...end.
transaction type: Custom query
scaling factor: 1
number of clients: 1
number of transactions per client: 100000
number of transactions actually processed: 100000/100000
tps = 3468.220041 (including connections establishing)
tps = 3468.630504 (excluding connections establishing)

** 8.3 **
cityvox=> EXPLAIN ANALYZE select libvil from vilsitelang where
codelang='FRA' and codevil='LYO';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Index Scan using pk_vilsitelang on vilsitelang (cost=0.00..4.27
rows=1 width=9) (actual time=0.029..0.031 rows=1 loops=1)
Index Cond: (((codevil)::text = 'LYO'::text) AND ((codelang)::text
= 'FRA'::text))
Total runtime: 0.057 ms
(3 rows)

[ip-dyn151 postgresql]# pgbench -h localhost -p 5483 -U cityvox -f
bench.cityvox.really.simple.sql -t 100000 -c 1 cityvox
starting vacuum...end.
transaction type: Custom query
scaling factor: 1
number of clients: 1
number of transactions per client: 100000
number of transactions actually processed: 100000/100000
tps = 3272.416750 (including connections establishing)
tps = 3272.820625 (excluding connections establishing)

Except for the prefix, I didn't give any options to configure. The CVS
tip doesn't have any particular options compared to 8.2?

--
Guillaume