Greg Smith <gsmith@gregsmith.com> writes:
> On Wed, 21 Nov 2007, Guillaume Smet wrote:
>> *** 8.2 ***
>> tps = 853.360277 (including connections establishing)
>>
>> *** 8.3 ***
>> tps = 784.819087 (including connections establishing)

> This is an 8% drop. I've seen a larger difference than that between two
> identical installations of the same version when the database is many GB
> large. Hard drives deliver a higher transfer rate at their inner
> portions, typically the start of the disk from the operating system's
> perspective. It's not unusual for the slow parts of the disk to be 30-40%
> slower than the fast ones.

FWIW, the test cases I was just comparing are entirely CPU-bound ---
vmstat says there are no disk reads happening at all. Now I only got a
3% drop, so that may not be the same effect Guillaume is seeing. But
the whole thing is a bit upsetting seeing that we thought we'd reduced
the overhead for short read-only transactions ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Nov 22, 2007 12:59 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> It's not that autovacuum is now
> on by default --- turning it off made no particular difference.

Tested that also a few hours ago. No difference.

> It's not that stats collection is now on by default --- ditto.

Same here. My 8.2 has stats collection enabled in the same way that 8.3 does.

--
Guillaume

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

"Guillaume Smet" <guillaume.smet@gmail.com> writes:
> I'm not saying my benchmark is perfectly relevant: I made it
> excessively simple on purpose. I just see a general slow down which is
> quite consistent accross all the tests I did (with pgbench or the
> application) and I'd really like to know if it's just my case on this
> particular box or something more general.

Are you examining only "trivial" queries? I've been able to identify a
couple of new planner hotspots that could explain some slowdown if the
planning time is material compared to the execution time. If you're
seeing a slowdown on queries that run for awhile, that would be
something else ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

On Nov 22, 2007 2:17 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Another issue is that on read-only transactions there's an extra
> gettimeofday() call caused by pgstat_report_tabstats, which could be a
> problem on machines with slow gettimeofday(). However that shouldn't
> happen if you've got track_counts turned off, so if you don't see any
> difference with or without stats then it's not the problem for you.

The box is a Core2 duo box so I don't think it's the case.

track_counts on/off doesn't change anything to the results.

--
Guillaume

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Nov 22, 2007 6:44 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Are you examining only "trivial" queries? I've been able to identify a
> couple of new planner hotspots that could explain some slowdown if the
> planning time is material compared to the execution time. If you're
> seeing a slowdown on queries that run for awhile, that would be
> something else ...

Yes, I kept only queries with no join and a couple of where
conditions. As I explained previously, I can reproduce the behavior
with a single index scan on only one table (plan posted previously).
If anyone is interested I can post the content of this table (there's
nothing confidential in it so I should have the customer permission)
and a couple of instructions to reproduce the test case.

The case in which I used a few differents queries executes the
following ones directly extracted from the application (all are index
scans - and they use the exact same index on 8.2 and 8.3):
select libvil from vilsitelang where codelang='FRA' and codevil='LYO'
select TL.motsclesmetatags, TL.descriptifmeta, TL.motcleoverture_l,
TL.motcleoverture_c, TL.baselinetheme from themelang TL where
TL.codeth = 'ASS' and TL.codelang = 'FRA'
SELECT libvilpubwoo, codelang, codepays, petiteville FROM vilsite
WHERE codevil = 'LYO'
select libvil from vilsitelang where codelang='FRA' and codevil='LYO'

So as you can see, queries can't be simpler.

--
Guillaume

---------------------------(end of broadcast)---------------------------
TIP 1: 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

On Thu, 2007-11-22 at 00:30 +0100, Guillaume Smet wrote:

> > Is the data identical on both systems?

Guillaume,

Sounds comprehensive, thanks for double checking.

Would it be possible to do these tests?

1. Compare SELECT 1;
This will allow us to remove planner and indexscan overheads from
results, though will still include protocol and tcop stuff.

2. Compare SELECT ... WHERE values are constants
This will cause the clients to reuse the plan already made, so should
effectively remove planner, but not indexscan overheads from the test.

3. Change the test to look at Integers columns only for the WHERE
clause, so we can remove any thought it has anything to do with text
data, collation etc..

>From those tests we should be able to narrow things down to planner,
executor or indexscan related.

Thanks,

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

"Tom Lane" <tgl@sss.pgh.pa.us> writes:

> The weird thing is that after a couple of hours of poking at it with
> oprofile and other sharp objects, I have no idea *why* it's slower.
> oprofile shows just about the same relative percentages for all the
> hot-spot functions in the backend.

Out of curiosity have you recompiled 8.2.5 recently? That is, are they
compiled with the same version of gcc?

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

"Guillaume Smet" <guillaume.smet@gmail.com> writes:
> On Nov 22, 2007 6:44 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Are you examining only "trivial" queries? I've been able to identify a
>> couple of new planner hotspots that could explain some slowdown if the
>> planning time is material compared to the execution time. If you're
>> seeing a slowdown on queries that run for awhile, that would be
>> something else ...

> Yes, I kept only queries with no join and a couple of where
> conditions. As I explained previously, I can reproduce the behavior
> with a single index scan on only one table (plan posted previously).
> If anyone is interested I can post the content of this table (there's
> nothing confidential in it so I should have the customer permission)
> and a couple of instructions to reproduce the test case.

I don't think you need to --- the "read-only transaction" case built
into pgbench is probably an equivalent test. What it looks like to
me is that the EquivalenceClass mechanism has added a little bit of
overhead, which isn't actually buying much of anything in these
trivial cases. I'll look at whether it can be short-circuited.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Gregory Stark <stark@enterprisedb.com> writes:
> "Tom Lane" <tgl@sss.pgh.pa.us> writes:
>> The weird thing is that after a couple of hours of poking at it with
>> oprofile and other sharp objects, I have no idea *why* it's slower.
>> oprofile shows just about the same relative percentages for all the
>> hot-spot functions in the backend.

> Out of curiosity have you recompiled 8.2.5 recently? That is, are they
> compiled with the same version of gcc?

CVS tip of both branches, freshly compiled for this test.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

On Nov 22, 2007 5:00 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Gregory Stark <stark@enterprisedb.com> writes:
> > Out of curiosity have you recompiled 8.2.5 recently? That is, are they
> > compiled with the same version of gcc?
>
> CVS tip of both branches, freshly compiled for this test.

And in my case, a vanilla 8.2.5 compiled on the same box with the same compiler.

--
Guillaume

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org