Обсуждение: dependency between numbers keywords and parser speed
Hello, there was a discussion about impact of number of keyword for parser speed. I did some synthetic tests and I didn't see any slowness on pgbench when I increased a number of keywords. I added a 30 reserved keywords and 30 unreserved keywords. On my Intel(R) Core(TM)2 Duo CPU E8400 @ 3.00GHz wasn't a significant difference between patched and unpatched server. Tested read only queries. I am sure, so there will be any dependency, but it probably needs more keywords, then I tested. Regards Pavel Stehule
Pavel Stehule <pavel.stehule@gmail.com> writes:
> there was a discussion about impact of number of keyword for parser
> speed. I did some synthetic tests and I didn't see any slowness on
> pgbench when I increased a number of keywords.
I don't see any particular reason to suppose that pgbench would be a
good framework for stressing parsing speed. The queries it issues
are of trivial length.
regards, tom lane
On 3/14/11 1:34 PM, Tom Lane wrote:
> Pavel Stehule <pavel.stehule@gmail.com> writes:
>> there was a discussion about impact of number of keyword for parser
>> speed. I did some synthetic tests and I didn't see any slowness on
>> pgbench when I increased a number of keywords.
>
> I don't see any particular reason to suppose that pgbench would be a
> good framework for stressing parsing speed. The queries it issues
> are of trivial length.
TPC-H might work well. Mark, is DBT3 still in usable condition?
-- -- Josh Berkus PostgreSQL Experts Inc.
http://www.pgexperts.com
On Mon, Mar 14, 2011 at 4:34 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Pavel Stehule <pavel.stehule@gmail.com> writes: >> there was a discussion about impact of number of keyword for parser >> speed. I did some synthetic tests and I didn't see any slowness on >> pgbench when I increased a number of keywords. > > I don't see any particular reason to suppose that pgbench would be a > good framework for stressing parsing speed. The queries it issues > are of trivial length. I found that it was actually a fairly measurable component of the select-only test when running with shared_buffers cranked up to a reasonable value. But it'd probably be a lot easier to measure on a benchmark specifically targeted at the parser. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
2011/3/15 Robert Haas <robertmhaas@gmail.com>: > On Mon, Mar 14, 2011 at 4:34 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Pavel Stehule <pavel.stehule@gmail.com> writes: >>> there was a discussion about impact of number of keyword for parser >>> speed. I did some synthetic tests and I didn't see any slowness on >>> pgbench when I increased a number of keywords. >> >> I don't see any particular reason to suppose that pgbench would be a >> good framework for stressing parsing speed. The queries it issues >> are of trivial length. > > I found that it was actually a fairly measurable component of the > select-only test when running with shared_buffers cranked up to a > reasonable value. But it'd probably be a lot easier to measure on a > benchmark specifically targeted at the parser. > When I tested it - all data was in memory, there was a minimal (near zero IO) and I run read only test. It doesn't mean, so parser is gratis, but my numbers doesn't show any potential problem with 60 new keywords. Pavel > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company >
On Tue, Mar 15, 2011 at 2:19 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote: > 2011/3/15 Robert Haas <robertmhaas@gmail.com>: >> On Mon, Mar 14, 2011 at 4:34 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> Pavel Stehule <pavel.stehule@gmail.com> writes: >>>> there was a discussion about impact of number of keyword for parser >>>> speed. I did some synthetic tests and I didn't see any slowness on >>>> pgbench when I increased a number of keywords. >>> >>> I don't see any particular reason to suppose that pgbench would be a >>> good framework for stressing parsing speed. The queries it issues >>> are of trivial length. >> >> I found that it was actually a fairly measurable component of the >> select-only test when running with shared_buffers cranked up to a >> reasonable value. But it'd probably be a lot easier to measure on a >> benchmark specifically targeted at the parser. >> > > When I tested it - all data was in memory, there was a minimal (near > zero IO) and I run read only test. > > It doesn't mean, so parser is gratis, but my numbers doesn't show any > potential problem with 60 new keywords. That's an interesting result, although it would be more interesting if you posted the patch and benchmark methodology. It's important for us not to overestimate the cost of adding keywords, and I don't object to adding them where it adds meaningful clarity that is not otherwise available or where it is necessary to comply with the SQL spec. But I do think it is worth being disciplined about. We should think about wording commands in a way that won't require new keywords; if there's not a reasonable way to do it, then we add a keyword. Our preference should be not to add keywords where that's reasonably possible. It is particularly important for us to avoid keywords that are partially or fully reserved. In that case, the issue is not parser overhead but the fact that it breaks compatibility with previous releases. pg_dump files can't be loaded, PL/pgsql procedures break, and so on. I have been here and it isn't fun. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
2011/3/15 Robert Haas <robertmhaas@gmail.com>: > On Tue, Mar 15, 2011 at 2:19 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote: >> 2011/3/15 Robert Haas <robertmhaas@gmail.com>: >>> On Mon, Mar 14, 2011 at 4:34 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>>> Pavel Stehule <pavel.stehule@gmail.com> writes: >>>>> there was a discussion about impact of number of keyword for parser >>>>> speed. I did some synthetic tests and I didn't see any slowness on >>>>> pgbench when I increased a number of keywords. >>>> >>>> I don't see any particular reason to suppose that pgbench would be a >>>> good framework for stressing parsing speed. The queries it issues >>>> are of trivial length. >>> >>> I found that it was actually a fairly measurable component of the >>> select-only test when running with shared_buffers cranked up to a >>> reasonable value. But it'd probably be a lot easier to measure on a >>> benchmark specifically targeted at the parser. >>> >> >> When I tested it - all data was in memory, there was a minimal (near >> zero IO) and I run read only test. >> >> It doesn't mean, so parser is gratis, but my numbers doesn't show any >> potential problem with 60 new keywords. > > That's an interesting result, although it would be more interesting if > you posted the patch and benchmark methodology. It's important for us > not to overestimate the cost of adding keywords, and I don't object to > adding them where it adds meaningful clarity that is not otherwise > available or where it is necessary to comply with the SQL spec. But I > do think it is worth being disciplined about. We should think about > wording commands in a way that won't require new keywords; if there's > not a reasonable way to do it, then we add a keyword. Our preference > should be not to add keywords where that's reasonably possible. > > It is particularly important for us to avoid keywords that are > partially or fully reserved. In that case, the issue is not parser > overhead but the fact that it breaks compatibility with previous > releases. pg_dump files can't be loaded, PL/pgsql procedures break, > and so on. I have been here and it isn't fun. > I agree and I understand well a problems with keywords. Just I would to know a real limits of bison and I can say so 60 keywords are not a problem. Real test of parser's speed should be done on short and quick queries. It can be unexpected so parser should be a bottle neck on long OLAP queries. Patch is added Pavel p.s. I am sure so this test depends on platform. > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company >
Вложения
On Tue, Mar 15, 2011 at 05:09:47PM +0100, Pavel Stehule wrote: > Real test of parser's speed should be done on short and quick queries. > It can be unexpected so parser should be a bottle neck on long OLAP > queries. Surely parsing overhead could be measured by simply PREPAREing every query, rather than executing them. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patriotism is when love of your own people comes first; nationalism, > when hate for people other than your own comes first. > - Charles de Gaulle