For archives, Tom commited the patch yesterday:
http://archives.postgresql.org/pgsql-committers/2007-11/msg00552.php
--
Guillaume
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.
Tom,
On Nov 27, 2007 3:58 AM, Tom Lane <[EMAIL PROTECTED]> wrote:
> Please try this patch on your real app (not the dumbed-down test case)
> and see what it does for you ...
If I disable the cache in the application, the most visited page
generates 175 SQL queries, mix of simple and more complica
Tom Lane wrote:
> Simon Riggs <[EMAIL PROTECTED]> writes:
> > But I think there must be an action that we can take for 8.3 and that
> > much runtime should not be given away easily. ISTM that we can win back
> > the losses Guillaume has identified, plus gain a little more even.
>
> Perhaps some sa
On Mon, 2007-11-26 at 18:18 -0500, Tom Lane wrote:
> Simon Riggs <[EMAIL PROTECTED]> writes:
> > Here's where I am:
>
> > Basic test was to replace call to oper_select_candidate with a single
> > item that was fed by a hardcoded value for varchar equality operator.
>
> Well, that confirms what we
Simon Riggs <[EMAIL PROTECTED]> writes:
> Here's where I am:
> Basic test was to replace call to oper_select_candidate with a single
> item that was fed by a hardcoded value for varchar equality operator.
Well, that confirms what we knew from gprof, but surely you aren't
proposing that as a usabl
On Mon, 2007-11-26 at 09:55 -0500, Tom Lane wrote:
> Simon Riggs <[EMAIL PROTECTED]> writes:
> > But I think there must be an action that we can take for 8.3 and that
> > much runtime should not be given away easily. ISTM that we can win back
> > the losses Guillaume has identified, plus gain a lit
"Kevin Grittner" <[EMAIL PROTECTED]> writes:
> Are we likely to see the 3% or the 7% performance degradation with
> version 8.3?
Probably not, since it sounds like your queries are typically not as
trivial as the ones in Guillame's test case. IOW there will be some
slowdown but it's likely to be
>>> On Mon, Nov 26, 2007 at 1:04 PM, in message
<[EMAIL PROTECTED]>, "Guillaume Smet"
<[EMAIL PROTECTED]> wrote:
> On Nov 26, 2007 6:48 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
>> In the test case Guillame provided, every single WHERE clause happens
>> to be of the form
>> varchar_column =
On Nov 26, 2007 6:48 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
> In the test case Guillame provided, every single WHERE clause happens
> to be of the form
> varchar_column = 'unknown-type literal'
> and there are no other operators used in the SELECT lists; but I can
> hardly believe that thi
Gregory Stark <[EMAIL PROTECTED]> writes:
> A 5-line patch which improves performance by 40% for any case sounds amazing,
> but how fragile is that gain? The kind of thing which would be worryign is if
> runing a query which uses both varchar and some other ambiguous operator
> causes it to lose al
"Simon Riggs" <[EMAIL PROTECTED]> writes:
> I've written up my suggestion as a 5 line patch, with measured
> performance improvement of about 40% for the varchar case.
A 5-line patch which improves performance by 40% for any case sounds amazing,
but how fragile is that gain? The kind of thing w
On Nov 26, 2007 5:58 PM, Simon Riggs <[EMAIL PROTECTED]> wrote:
> I've written up my suggestion as a 5 line patch, with measured
> performance improvement of about 40% for the varchar case.
>
> It isn't a great long term fix, but I don't want to delay 8.3 either
> with the full fix.
Can we see the
On Mon, 2007-11-26 at 09:55 -0500, Tom Lane wrote:
> Simon Riggs <[EMAIL PROTECTED]> writes:
> > But I think there must be an action that we can take for 8.3 and that
> > much runtime should not be given away easily. ISTM that we can win back
> > the losses Guillaume has identified, plus gain a lit
Simon Riggs <[EMAIL PROTECTED]> writes:
> But I think there must be an action that we can take for 8.3 and that
> much runtime should not be given away easily. ISTM that we can win back
> the losses Guillaume has identified, plus gain a little more even.
Perhaps some sanity could be restored to th
On Nov 26, 2007 11:59 AM, Peter Eisentraut <[EMAIL PROTECTED]> wrote:
> I have seen many applications where prepared queries caused stale plans and
> poor performance. We have in many cases achieved great performance gains by
> turning off prepared queries globally, for example in the driver layer
Am Samstag, 24. November 2007 schrieb Simon Riggs:
> In many cases, 100% of queries are unprepared.
I have seen many applications where prepared queries caused stale plans and
poor performance. We have in many cases achieved great performance gains by
turning off prepared queries globally, for
On Sun, 2007-11-25 at 19:35 -0500, Tom Lane wrote:
> The cost of resolving ambiguous operators has been an issue for a long
> time, of course, but it seems particularly bad in this case --- gprof
> blames 37% of the runtime on oper_select_candidate(). It might be time
> to think about caching the
Gregory Stark <[EMAIL PROTECTED]> writes:
> "Tom Lane" <[EMAIL PROTECTED]> writes:
>> But since your columns are varchar, which doesn't have any operators of its
>> own, we have to go through oper_select_candidate()
> I wonder whether at some point we shouldn't just eliminate this distinction
> en
"Tom Lane" <[EMAIL PROTECTED]> writes:
> But since your columns are varchar, which doesn't have any operators of its
> own, we have to go through oper_select_candidate()
I wonder whether at some point we shouldn't just eliminate this distinction
entirely. Just make "text" and "varchar" the same
On Nov 26, 2007 1:54 AM, Guillaume Smet <[EMAIL PROTECTED]> wrote:
> From what you say, I understand we can't even find a workaround for
> 8.3 to improve the situation while waiting for a cleaner solution in
> 8.4+?
To explain the reasons why I'm so worried, I should have explained
that all the pr
On Nov 26, 2007 1:35 AM, Tom Lane <[EMAIL PROTECTED]> wrote:
> The cost of resolving ambiguous operators has been an issue for a long
> time, of course, but it seems particularly bad in this case --- gprof
> blames 37% of the runtime on oper_select_candidate(). It might be time
> to think about ca
"Guillaume Smet" <[EMAIL PROTECTED]> writes:
> Sure, it's the same queries I posted earlier. My pgbench script is the
> following:
> BEGIN
> select libvil from vilsitelang where codelang='FRA' and codevil='LYO'
> select TL.motsclesmetatags, TL.descriptifmeta, TL.motcleoverture_l,
> TL.motcleovertu
On Nov 25, 2007 7:35 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
> What about the table schemas?
The schema is in the dump. That's the main reason why I've sent the
dump to both of you.
Anyway, here it is:
db=# \d themelang
Table "public.themelang"
Column | Type
"Guillaume Smet" <[EMAIL PROTECTED]> writes:
> On Nov 25, 2007 6:41 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
>> Would you show us the test case you're using?
> Sure, it's the same queries I posted earlier.
What about the table schemas?
> I send a link to the data to both of you in private.
I dou
On Nov 25, 2007 6:41 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
> Would you show us the test case you're using?
Sure, it's the same queries I posted earlier. My pgbench script is the
following:
BEGIN
select libvil from vilsitelang where codelang='FRA' and codevil='LYO'
select TL.motsclesmetatags, TL
"Guillaume Smet" <[EMAIL PROTECTED]> writes:
> Using pgbench -n -S -c 10 -t 10, I also have CVS tip as fast as
> CVS from january. But using my set of queries, it's not.
Would you show us the test case you're using?
regards, tom lane
---(end of
"Guillaume Smet" <[EMAIL PROTECTED]> writes:
> Using pgbench -n -S -c 10 -t 10, I also have CVS tip as fast as
> CVS from january. But using my set of queries, it's not.
Were you ever able to send your queries?
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me
On Nov 24, 2007 5:50 PM, Gregory Stark <[EMAIL PROTECTED]> wrote:
> It would be nice to have infrastructure similar to the buldfarm running a
> standard set of benchmarks every day. It would be fascinating to see the
> graphs day-by-day of performance. Hopefully we wouldn't see too many dips and
>
On Nov 25, 2007 8:06 AM, Tom Lane <[EMAIL PROTECTED]> wrote:
> The TPS numbers bounce around by 1% or so on repeated trials, so I
> wouldn't put too much faith in small differences. What it looks like
> to me is that it's all about the stats collection overhead. The drop on
> 01-17 corresponds to
"Guillaume Smet" <[EMAIL PROTECTED]> writes:
> Here are some rough results:
> http://people.openwide.fr/~gsmet/postgresql/postgresql_8.3_development_cycle_1.png
I repeated this experiment using the "pgbench -n -S -c 10 -t 10 bench"
test case that I've been looking at. The attached graph shows
On Sat, 24 Nov 2007, Guillaume Smet wrote:
Here are some rough results:
http://people.openwide.fr/~gsmet/postgresql/postgresql_8.3_development_cycle_1.png
So the big dips were Jan->Feb and April->May. I've still got the text of
the commit log Tom assembled sitting at
http://developer.postgr
"Guillaume Smet" <[EMAIL PROTECTED]> writes:
> On Nov 24, 2007 5:16 PM, Gregory Stark <[EMAIL PROTECTED]> wrote:
>> Several of the major changes in 8.3 are I/O vs CPU tradeoffs which could be
>> causing a slowdown if you're measuring primarily CPU resources. I'm thinking
>> of both HOT and packed
On 11/24/07, Gregory Stark <[EMAIL PROTECTED]> wrote:
>
> This is a conflict which will affect Postgres in the future as well.
> Generally
> I/O costs win over cpu costs in databases since only relatively small
> systems
> are cpu-bound. Large systems are typically I/O-bound.
>
That really depends
On Nov 24, 2007 5:16 PM, Gregory Stark <[EMAIL PROTECTED]> wrote:
> Several of the major changes in 8.3 are I/O vs CPU tradeoffs which could be
> causing a slowdown if you're measuring primarily CPU resources. I'm thinking
> of both HOT and packed varlenas. I don't know if either of these are causi
"Guillaume Smet" <[EMAIL PROTECTED]> writes:
> And the most important point IMHO is that we must be aware of the
> trade-offs we make. We might have some cases where the CPU trade-off
> is not worth the I/O improvement (and probably the other case too).
> We really need a test framework to be abl
On Nov 24, 2007 5:16 PM, Gregory Stark <[EMAIL PROTECTED]> wrote:
> This is a conflict which will affect Postgres in the future as well. Generally
> I/O costs win over cpu costs in databases since only relatively small systems
> are cpu-bound. Large systems are typically I/O-bound.
It really depen
"Guillaume Smet" <[EMAIL PROTECTED]> writes:
> Tom, from my tests, the slow down goes down from 8% to 4% but it's
> still there and measurable. It's pretty consistent with the fact that
> you only saw a 3% slow down in your tests.
> The fact that you had only 3% overhead is still bugging me thoug
On Nov 22, 2007 7:01 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
> "Guillaume Smet" <[EMAIL PROTECTED]> writes:
> > I thought I could also perform a test on CVS head every month from
> > December 2006 to now to see if it can give us a better idea of when
> > the overhead first appeared. Ping me if you'
On Nov 24, 2007 11:35 AM, Simon Riggs <[EMAIL PROTECTED]> wrote:
> On the plus side, there are many very savvy people out there too and all
> the performance features we put in are being used in serious ways. But
> we must cater for both the top end and bottom end of the application
> spectrum.
To
On Sat, 2007-11-24 at 00:04 -0500, Tom Lane wrote:
> I didn't intend to say that select-only transactions aren't interesting;
> rather that there should be some minimal effort on the application side.
> The cases we are testing here involve:
>
> 1. One query per transaction. Even with the 8.3 im
On Nov 24, 2007 12:39 AM, Gregory Stark <[EMAIL PROTECTED]> wrote:
> Surely such applications would be using prepare queries though?
Surely the good ones. I know you can only save people from themselves
to a certain point... I just didn't want to dismiss it completely.
--
Jonah H. Harris, Sr. S
"Jonah H. Harris" <[EMAIL PROTECTED]> writes:
> On Nov 23, 2007 8:52 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
>> So I'm satisfied with these results, particularly in view of the
>> fact that what we're measuring is certainly the stupidest, least
>> efficient way to use Postgres.
>
> Given the emerg
On Nov 24, 2007 12:04 AM, Tom Lane <[EMAIL PROTECTED]> wrote:
> I didn't intend to say that select-only transactions aren't interesting;
> rather that there should be some minimal effort on the application side.
> The cases we are testing here involve:
Agreed, thanks for clarifying.
--
Jonah H.
"Jonah H. Harris" <[EMAIL PROTECTED]> writes:
> On Nov 23, 2007 8:52 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
>> So I'm satisfied with these results, particularly in view of the
>> fact that what we're measuring is certainly the stupidest, least
>> efficient way to use Postgres.
> Given the emergin
On Nov 23, 2007 8:52 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
> So I'm satisfied with these results, particularly in view of the
> fact that what we're measuring is certainly the stupidest, least
> efficient way to use Postgres.
Given the emerging world of data-driven content management systems and
I wrote:
> 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 cas
"Guillaume Smet" <[EMAIL PROTECTED]> writes:
> I thought I could also perform a test on CVS head every month from
> December 2006 to now to see if it can give us a better idea of when
> the overhead first appeared. Ping me if you're interested in it.
If you feel like doing that, it might be intere
On Nov 22, 2007 12:45 PM, Guillaume Smet <[EMAIL PROTECTED]> wrote:
> I thought I could also perform a test on CVS head every month from
> December 2006 to now to see if it can give us a better idea of when
> the overhead first appeared. Ping me if you're interested in it.
If I recall correctly, I
Tom,
On Nov 22, 2007 10:29 AM, Simon Riggs <[EMAIL PROTECTED]> wrote:
> Sounds comprehensive, thanks for double checking.
>
> Would it be possible to do these tests?
>
>
Do you want me to perform additional tests or are you pretty sure of
what the problem is?
I thought I could also perform a te
On Nov 22, 2007 5:00 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
> Gregory Stark <[EMAIL PROTECTED]> 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 cas
Gregory Stark <[EMAIL PROTECTED]> writes:
> "Tom Lane" <[EMAIL PROTECTED]> 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
>>
"Guillaume Smet" <[EMAIL PROTECTED]> writes:
> On Nov 22, 2007 6:44 AM, Tom Lane <[EMAIL PROTECTED]> 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 t
"Tom Lane" <[EMAIL PROTECTED]> 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 cu
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
resul
On Nov 22, 2007 6:44 AM, Tom Lane <[EMAIL PROTECTED]> 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 Nov 22, 2007 2:17 AM, Tom Lane <[EMAIL PROTECTED]> 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 tra
"Guillaume Smet" <[EMAIL PROTECTED]> 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
On Nov 21, 2007 7:22 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
> 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
I wrote:
> 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.
However, some comparisons with gprof show
On Nov 22, 2007 12:49 AM, Greg Smith <[EMAIL PROTECTED]> wrote:
> Hard drives deliver a higher transfer rate at their inner
> portions, typically the start of the disk from the operating system's
> perspective.
It could have been a good point if it was seq scans of large tables.
It's only index sc
On Nov 22, 2007 12:59 AM, Tom Lane <[EMAIL PROTECTED]> 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 ha
Greg Smith <[EMAIL PROTECTED]> 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 bet
"Guillaume Smet" <[EMAIL PROTECTED]> writes:
> I took a couple of very simple read only queries executed in the pages
> to create a pgbench script and I have the following results:
Hmm ... I can reproduce a consistent difference of about three percent
between 8.2 and HEAD. Using pgbench's built-i
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
On Nov 22, 2007 12:07 AM, Gregory Williamson
<[EMAIL PROTECTED]> wrote:
> I've been running some fairly heavy read-only tests (5 minutes duration)
> against 8.3beta2 and 8.2.5 and 8.1.10 and are getting slightly faster
> numbers for 8.2.5 over 8.1 and 8.3beta2 looks consistently faster by a few
>
On Nov 21, 2007 10:09 PM, Simon Riggs <[EMAIL PROTECTED]> 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'
FWIW,
>
> Please do tests of at least 2 minutes duration. A 1.25 second test isn't
> enough. Please confirm you have VACUUM ANALYZED each db beforehand.
>
> Have you checked that the EXPLAIN ANALYZEs are essentially identical
> also? Is the data identical on both systems?
>
I've been running s
On Wed, 2007-11-21 at 20:04 +0100, Guillaume Smet wrote:
> number of clients: 1
> number of transactions per client: 1000
> number of transactions actually processed: 1000/1000
> tps = 784.819087 (including connections establishing)
> tps = 786.884214 (excluding connections establishing)
>
> All
68 matches
Mail list logo
