Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Tom Lane wrote: > Stefan Kaltenbrunner writes: > > Tom Lane wrote: > >> Nobody suggested dismissing it. The point was that it hasn't been > >> tested adequately to justify applying it now. > > > not sure what testing people want to get done though (there are a fair > > amount of results and profiles in the thread)? > > Robert was complaining that the worst case hadn't been characterized > adequately, which I agree with. We know it helps a lot on certain > cases, but what's the downside? Added to TODO list so we don't forget about it: Considering improving performance of computing CHAR() value lengths -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Stefan Kaltenbrunner writes: > Tom Lane wrote: >> Nobody suggested dismissing it. The point was that it hasn't been >> tested adequately to justify applying it now. > not sure what testing people want to get done though (there are a fair > amount of results and profiles in the thread)? Robert was complaining that the worst case hadn't been characterized adequately, which I agree with. We know it helps a lot on certain cases, but what's the downside? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Tom Lane wrote: Stefan Kaltenbrunner writes: hmm I tend to disagree, this patch was specifically done to address a hotspot I noticed under a given workload and it helped a lot for that workload(like getting 6000qps more is pretty neat imho). While people might not use fixed width chars that often(which especially for migrated database is imho not true) it is an issue that can be seen with a rather popular database related benchmarking tool so we should not really dismiss it easily. Nobody suggested dismissing it. The point was that it hasn't been tested adequately to justify applying it now. not sure what testing people want to get done though (there are a fair amount of results and profiles in the thread)? Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Stefan Kaltenbrunner writes: > hmm I tend to disagree, this patch was specifically done to address a > hotspot I noticed under a given workload and it helped a lot for that > workload(like getting 6000qps more is pretty neat imho). > While people might not use fixed width chars that often(which especially > for migrated database is imho not true) it is an issue that can be seen > with a rather popular database related benchmarking tool so we should > not really dismiss it easily. Nobody suggested dismissing it. The point was that it hasn't been tested adequately to justify applying it now. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Alvaro Herrera wrote: Robert Haas escribió: On Mon, Feb 22, 2010 at 9:35 PM, Bruce Momjian wrote: What ever happened to this patch? I think it's unclear that all of the best and worst cases have been sufficiently tested and that the results are satisfactory. We have everything from massive performance gains to no obvious benefit at all, and it's very unclear that anyone has made a serious effort to find a benchmark the worst-case scenarios. I think we should drop this for now. *If* someone wants to put some work into more thorough analysis for 9.1, we can revisit it then. +1 -- it's not like it hasn't been a problem all along. hmm I tend to disagree, this patch was specifically done to address a hotspot I noticed under a given workload and it helped a lot for that workload(like getting 6000qps more is pretty neat imho). While people might not use fixed width chars that often(which especially for migrated database is imho not true) it is an issue that can be seen with a rather popular database related benchmarking tool so we should not really dismiss it easily. Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Robert Haas escribió: > On Mon, Feb 22, 2010 at 9:35 PM, Bruce Momjian wrote: > > What ever happened to this patch? > > I think it's unclear that all of the best and worst cases have been > sufficiently tested and that the results are satisfactory. We have > everything from massive performance gains to no obvious benefit at > all, and it's very unclear that anyone has made a serious effort to > find a benchmark the worst-case scenarios. I think we should drop > this for now. *If* someone wants to put some work into more thorough > analysis for 9.1, we can revisit it then. +1 -- it's not like it hasn't been a problem all along. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
On Mon, Feb 22, 2010 at 9:35 PM, Bruce Momjian wrote: > Tom Lane wrote: >> Jeremy Kerr writes: >> > Stephen, >> >> If the updated function is always faster when the overall string is at >> >> least, say, 16 characters long, >> >> > But that's not the case - the cost of the function (and the speedup from >> > the previous version) depends on the number of spaces that there are at >> > the end. >> >> Right, but there are certainly not more spaces than there are string >> characters ;-) >> >> I think Dimitri's idea is eminently worth trying. In a string of less >> than, say, 16 bytes, the prospects of being able to win anything get >> much smaller compared to the prospects of wasting the extra loop >> overhead. There is also a DBA psychology angle to it. If you've got >> CHAR(n) for very small n, it's likely that the type is being used in the >> "canonical" fashion and there won't be many trailing blanks. The case >> where we can hope to win is where we have CHAR(255) or some other >> plucked-from-the-air limit. > > What ever happened to this patch? I think it's unclear that all of the best and worst cases have been sufficiently tested and that the results are satisfactory. We have everything from massive performance gains to no obvious benefit at all, and it's very unclear that anyone has made a serious effort to find a benchmark the worst-case scenarios. I think we should drop this for now. *If* someone wants to put some work into more thorough analysis for 9.1, we can revisit it then. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Tom Lane wrote: > Jeremy Kerr writes: > > Stephen, > >> If the updated function is always faster when the overall string is at > >> least, say, 16 characters long, > > > But that's not the case - the cost of the function (and the speedup from > > the previous version) depends on the number of spaces that there are at > > the end. > > Right, but there are certainly not more spaces than there are string > characters ;-) > > I think Dimitri's idea is eminently worth trying. In a string of less > than, say, 16 bytes, the prospects of being able to win anything get > much smaller compared to the prospects of wasting the extra loop > overhead. There is also a DBA psychology angle to it. If you've got > CHAR(n) for very small n, it's likely that the type is being used in the > "canonical" fashion and there won't be many trailing blanks. The case > where we can hope to win is where we have CHAR(255) or some other > plucked-from-the-air limit. What ever happened to this patch? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
On Fri, June 26, 2009 11:39 am, Tom Lane wrote: > to...@tuxteam.de writes: >> On Fri, Jun 26, 2009 at 05:03:11PM +0200, Dimitri Fontaine wrote: >>> It's becoming somewhat tricky, but maybe the test to do for the >>> optimisation to get used is n >= threshold && str[n-6] == 0x20, Ã la >>> Boyer/Moore? > >> That's cute. What about comparing the last aligned word which completely >> fits in the buffer? Something along the lines of (assuming four-byte >> words) >>* (int*) (4 * ((int) &buf[0]) / 4) > > We're trying to avoid adding cycles to the optimization-is-useless case. > The more expensive this test gets, the slower the unoptimizable case > becomes. > Yeah. Like you, I like the idea of a switch based on string length. I would suggest a cutoff of something like 36 (length of the string representation of a UUID). But maybe that will miss lots of optimisable cases like address fields. I guess those people should really be using varchar(n) anyway. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
to...@tuxteam.de writes: > On Fri, Jun 26, 2009 at 05:03:11PM +0200, Dimitri Fontaine wrote: >> It's becoming somewhat tricky, but maybe the test to do for the >> optimisation to get used is n >= threshold && str[n-6] == 0x20, Ã la >> Boyer/Moore? > That's cute. What about comparing the last aligned word which completely > fits in the buffer? Something along the lines of (assuming four-byte > words) >* (int*) (4 * ((int) &buf[0]) / 4) We're trying to avoid adding cycles to the optimization-is-useless case. The more expensive this test gets, the slower the unoptimizable case becomes. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Fri, Jun 26, 2009 at 05:03:11PM +0200, Dimitri Fontaine wrote: > Le 26 juin 09 à 14:47, Jeremy Kerr a écrit : >> For the new function to be faster, we need to know that there are more >> than 6 (on average, depending on alignment) trailing spaces. > > It's becoming somewhat tricky, but maybe the test to do for the > optimisation to get used is n >= threshold && str[n-6] == 0x20, àla > Boyer/Moore? That's cute. What about comparing the last aligned word which completely fits in the buffer? Something along the lines of (assuming four-byte words) * (int*) (4 * ((int) &buf[0]) / 4) (now that's an ugly one, but you know what I mean?) Regards - -- tomás -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFKROlCBcgs9XrR2kYRArHPAJ9VhT+RfK5/5BxwA0nxaOmK4nfuWACdFtFL iKtvPaZY/KhDJMOf4hyzmQI= =yd05 -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Le 26 juin 09 à 14:47, Jeremy Kerr a écrit : For the new function to be faster, we need to know that there are more than 6 (on average, depending on alignment) trailing spaces. It's becoming somewhat tricky, but maybe the test to do for the optimisation to get used is n >= threshold && str[n-6] == 0x20, àla Boyer/Moore? I call it tricky because you could have a space here which isn't followed by spaces, but still, if it's not a space here, you're saying we should not even try the optimisation. -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Jeremy Kerr writes: > Stephen, >> If the updated function is always faster when the overall string is at >> least, say, 16 characters long, > But that's not the case - the cost of the function (and the speedup from > the previous version) depends on the number of spaces that there are at > the end. Right, but there are certainly not more spaces than there are string characters ;-) I think Dimitri's idea is eminently worth trying. In a string of less than, say, 16 bytes, the prospects of being able to win anything get much smaller compared to the prospects of wasting the extra loop overhead. There is also a DBA psychology angle to it. If you've got CHAR(n) for very small n, it's likely that the type is being used in the "canonical" fashion and there won't be many trailing blanks. The case where we can hope to win is where we have CHAR(255) or some other plucked-from-the-air limit. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Stephen, > If the updated function is always faster when the overall string is at > least, say, 16 characters long, But that's not the case - the cost of the function (and the speedup from the previous version) depends on the number of spaces that there are at the end. For the new function to be faster, we need to know that there are more than 6 (on average, depending on alignment) trailing spaces. The number of non-space characters in the string won't affect performance (other than that there is probably a correlation between total string length and number of spaces, but we can't be certain of that). > If the new function is always slower, regardless of overall string > length, when there's only 1 extra space at the end Yes, that is correct. Cheers, Jeremy -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
* Jeremy Kerr (j...@ozlabs.org) wrote: > > Is it just the size that matters, or is it when there are few spaces > > at the end? > > It's the number of spaces at the end. If we knew this number, then we > wouldn't have to do any comparisons at all :) I meant in terms of affecting the performance of this function.. We know the total length of the string, including spaces, coming into the function. If the updated function is always faster when the overall string is at least, say, 16 characters long, then Dimitri's suggestion to just check the overall length coming in and decide which approach to use might make sense. If the new function is always slower, regardless of overall string length, when there's only 1 extra space at the end, then chances are it's not worth it. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Stephen, > Is it just the size that matters, or is it when there are few spaces > at the end? It's the number of spaces at the end. If we knew this number, then we wouldn't have to do any comparisons at all :) Cheers, Jeremy -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
* Dimitri Fontaine (dfonta...@hi-media.com) wrote: > Le 26 juin 09 à 05:20, Jeremy Kerr a écrit : >>> Unfortunately, the cases with lots of padding spaces are probably >>> much less probable than the cases with fewer. It would be unpleasant >>> for example if this patch resulted in a severe performance >>> degradation for a "canonical" example of char(n) being used properly, >>> such as char(2) for US state abbreviations. >> >> Yep, makes sense. The other consideration is stock-ticker symbols, I >> assume they may also be stored in CHAR(small n) columns. > > Could this optimisation only kicks in when n is "big enough"? > I'm don't know if this part of the code knows the typmod... Is it just the size that matters, or is it when there are few spaces at the end? We do know the overall length, but I didn't see a definite that if it's larger than X words, doing the by-word comparison is a win regardless of how many actual spaces are at the end (apologies to Jeremy if it's in his more detailed report, I havn't had a chance to look yet). Thanks, Stpehen signature.asc Description: Digital signature
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Hi, Le 26 juin 09 à 05:20, Jeremy Kerr a écrit : Unfortunately, the cases with lots of padding spaces are probably much less probable than the cases with fewer. It would be unpleasant for example if this patch resulted in a severe performance degradation for a "canonical" example of char(n) being used properly, such as char(2) for US state abbreviations. Yep, makes sense. The other consideration is stock-ticker symbols, I assume they may also be stored in CHAR(small n) columns. Could this optimisation only kicks in when n is "big enough"? I'm don't know if this part of the code knows the typmod... Regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Tom, > > I've put together some data from a microbenchmark of the bcTrulen > > function, patched and unpatched. > > Uh, where's the data? If you're after the raw data for a run, I've put it up: http://ozlabs.org/~jk/projects/db/data/bctruelen.csv I've also packaged up the quick-and-dirty benchmark I've been using: http://ozlabs.org/~jk/projects/db/bctrulen.tar.gz to recreate as-needed. ./benchmark.sh will run tests for varying-length strings (so changing the alignment) and varying numbers of trailing spaces. ./benchmark-bctruelen will perform an individual old/new comparison. > Unfortunately, the cases with lots of padding spaces are probably > much less probable than the cases with fewer. It would be unpleasant > for example if this patch resulted in a severe performance > degradation for a "canonical" example of char(n) being used properly, > such as char(2) for US state abbreviations. Yep, makes sense. The other consideration is stock-ticker symbols, I assume they may also be stored in CHAR(small n) columns. Cheers, Jeremy -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Jeremy Kerr writes:
> I've put together some data from a microbenchmark of the bcTrulen
> function, patched and unpatched.
Uh, where's the data?
> In the worst cases, I see a 53% cost increase on x86 (with the string
> 'aaa ') and a 97% increase on PowerPC ('a ').
> So, it all depends on the number of padding spaces we'd expect to see on
> workload data. Fortunately, we see the larger reductions on the more
> expensive operations (ie, longer strings).
Unfortunately, the cases with lots of padding spaces are probably much
less probable than the cases with fewer. It would be unpleasant for
example if this patch resulted in a severe performance degradation for
a "canonical" example of char(n) being used properly, such as char(2)
for US state abbreviations.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Hi Stephen,
> What would be really useful would be "best case" and "worst case"
> scenarios.
I've put together some data from a microbenchmark of the bcTrulen
function, patched and unpatched.
As for best-case, if you have a long string of trailing spaces, we can
go through them at theoretically one quarter of cost (a test benchmark
on x86 shows an actual reduction of 11 to 3 sec with a string of 100
spaces).
Worst-case behaviour is with smaller numbers of spaces. Here are the
transition points (ie, where doing the word-wise comparison is faster
than byte-wise) that I see from my benchmark:
align spaces
3 7
2 6
1 5
0 4
- where 'align' is the alignment of the first byte to compare (ie, at
the end of the string). This is pretty much as-expected, as these
transition points are the first opportunity that the new function has to
do a word compare.
In the worst cases, I see a 53% cost increase on x86 (with the string
'aaa ') and a 97% increase on PowerPC ('a ').
So, it all depends on the number of padding spaces we'd expect to see on
workload data. Fortunately, we see the larger reductions on the more
expensive operations (ie, longer strings).
Cheers,
Jeremy
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Stefan, * Stefan Kaltenbrunner (ste...@kaltenbrunner.cc) wrote: > Stephen Frost wrote: >> What would be really useful would be "best case" and "worst case" >> scenarios. Ideally, with profile information for this specific function >> (in addition to full benchmark runs since those can show minimal >> performance improvments due to other constraints, locking, etc). > > not sure what you are after here - my testcase is one specific query run > in parallel on 16 processes (running it in a single process yields > similiar improvements our scaling is pretty good here). What I'm suggesting is to test what happens when strings sent to bcTruelen are at different memory addresses (there's only 4 or 8 different possibilities here, then different lengths should be tested of less-than-1-word, 1-word, >1-word, and then different ending points at different memory addresses) and compare the new function to the old. The main point here being to try and find out if there are any pathological cases where the new function is much worse, or even somewhat worse, so we can weigh that against the places where it performs better. This isn't something you can do from in PG. :) > I was simply testing the patch Jeremy provided on that workload(the > upthread mentioned query is mostly affected by that on others there is > no measurable difference) Certainly, your performance numbers are good justification if there's no downside. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Stephen Frost wrote: * Stefan Kaltenbrunner (ste...@kaltenbrunner.cc) wrote: FWIW: I'm able to measure an even more significant improvement of around 10%: What would be really useful would be "best case" and "worst case" scenarios. Ideally, with profile information for this specific function (in addition to full benchmark runs since those can show minimal performance improvments due to other constraints, locking, etc). not sure what you are after here - my testcase is one specific query run in parallel on 16 processes (running it in a single process yields similiar improvements our scaling is pretty good here). Have you tried pulling this function out and running tests with all alignment possibilities to see how it compares to the original? There's only so many options and showing that this change always improves performance, or at least doesn't degrade it in the worst case, would certainly go a long way towards getting it accepted. again I'm not exactly sure on what you want to get tested specifically - the original problem report was because I noticed a significant performance improvement from using varchar() instead of char(n) and that showed bcTruelen() on the very top of the profile. I was simply testing the patch Jeremy provided on that workload(the upthread mentioned query is mostly affected by that on others there is no measurable difference) Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
* Stefan Kaltenbrunner (ste...@kaltenbrunner.cc) wrote: > FWIW: I'm able to measure an even more significant improvement of around > 10%: What would be really useful would be "best case" and "worst case" scenarios. Ideally, with profile information for this specific function (in addition to full benchmark runs since those can show minimal performance improvments due to other constraints, locking, etc). Have you tried pulling this function out and running tests with all alignment possibilities to see how it compares to the original? There's only so many options and showing that this change always improves performance, or at least doesn't degrade it in the worst case, would certainly go a long way towards getting it accepted.. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Robert Haas wrote: On Tue, Jun 23, 2009 at 10:46 PM, Jeremy Kerr wrote: Robert, I'd still like to know the workload and exact numbers. From up-thread: http://ozlabs.org/~jk/projects/db/data/postgres.bcTruelen/ - or were there other details you were looking for? Oh! Very nice, sorry, missed the original message. You probably want to add your patch here: http://wiki.postgresql.org/wiki/CommitFestOpen FWIW: I'm able to measure an even more significant improvement of around 10%: http://archives.postgresql.org/pgsql-hackers/2009-06/msg01041.php on a specific query. Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
On Tue, Jun 23, 2009 at 10:46 PM, Jeremy Kerr wrote: > Robert, > >> I'd still like to know the workload and exact numbers. > > From up-thread: > > http://ozlabs.org/~jk/projects/db/data/postgres.bcTruelen/ > > - or were there other details you were looking for? Oh! Very nice, sorry, missed the original message. You probably want to add your patch here: http://wiki.postgresql.org/wiki/CommitFestOpen Thanks, ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Robert, > I'd still like to know the workload and exact numbers. From up-thread: http://ozlabs.org/~jk/projects/db/data/postgres.bcTruelen/ - or were there other details you were looking for? Cheers, Jeremy -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
On Tue, Jun 23, 2009 at 9:05 PM, Jeremy Kerr wrote: > Results in a small performance increase; around 1-2% on my POWER6 test > box. I'd still like to know the workload and exact numbers. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
The current bcTruelen function uses a simple reverse array scan to
find the legth of a space-padded string. On some workloads (it shows
in sysbench), this can result in a lot of time spent in this function.
This change introduces a word-at-a-time comparison in bcTruelen, aiming
to reduce the number of compares where possible. Word-size compares
are performed on aligned sections of the string.
Results in a small performance increase; around 1-2% on my POWER6 test
box.
Signed-off-by: Jeremy Kerr
---
Resend: context diff this time
---
src/backend/utils/adt/varchar.c | 24 +---
1 file changed, 21 insertions(+), 3 deletions(-)
*** a/src/backend/utils/adt/varchar.c
--- b/src/backend/utils/adt/varchar.c
***
*** 624,639 varchartypmodout(PG_FUNCTION_ARGS)
static int
bcTruelen(BpChar *arg)
{
char *s = VARDATA_ANY(arg);
int i;
- int len;
! len = VARSIZE_ANY_EXHDR(arg);
! for (i = len - 1; i >= 0; i--)
{
if (s[i] != ' ')
break;
}
return i + 1;
}
--- 624,657
static int
bcTruelen(BpChar *arg)
{
+ const uint32_t spaces = 0x20202020;
+ const int wordsize = sizeof(spaces);
char *s = VARDATA_ANY(arg);
int i;
! i = VARSIZE_ANY_EXHDR(arg) - 1;
!
! /* compare down to an aligned boundary */
! for (; i > 0 && !PointerIsAligned(s + i - (wordsize - 1), uint32_t);
i--)
{
if (s[i] != ' ')
+ return i + 1;
+ }
+
+ /* now that we're aligned, compare word at a time */
+ for (; i >= wordsize - 1; i -= wordsize)
+ {
+ if (*(uint32_t *)(s + i - (wordsize - 1)) != spaces)
break;
}
+
+ /* check within the last non-matching word */
+ for (; i >= 0; i--)
+ {
+ if (s[i] != ' ')
+ break;
+ }
+
return i + 1;
}
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Greg Stark writes: > We could add an integer prefix to the char() datatype > with the "total" length and then just not include the spaces. But that > would make it not binary compatible with text -- which would mean > implementing a whole bunch of casts and operators. Um, it's already not binary compatible with text. The space hit would be a bigger issue ... at least for cases where you don't save much of anything by eliminating the spaces, which would be exactly the cases where sane Postgres users use char(n) today. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
On Thu, Jun 18, 2009 at 10:03 PM, Simon Riggs wrote: > > The overall problem is that we expect the Datum's of a datatype to know > how to display themselves without any access to metadata. Yes > Another way of looking at this might be that we need a default FORMAT > specifier associated with a column. Teradata used the FORMAT specifier > on a column to allow you to specify a default format. That allowed you > to specify leading/trailing zeros/spaces, decimal points and other > characters. I've suggested this in the past in the context of getting rid of the "cash" data type. However I'm not sure it solves this problem. In the case where we're just outputing a column we could arrange to have the typmod pretty easily. but what would you do if you had some complex expression which happened to result in a char(). Say something like: coalesce(nickname, name) where both nickname and name are, say, char(50). Or for that matter where they're two different length chars, like char(50) and char(100). We could add an integer prefix to the char() datatype with the "total" length and then just not include the spaces. But that would make it not binary compatible with text -- which would mean implementing a whole bunch of casts and operators. Perhaps. I think we already have rtrim as the cast in one direction and I think we already have operators which is the whole trigger for the bcTrueLen thing which started this thread. maybe it wouldn't really be that much of a pain. -- greg http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
On Thu, 2009-06-18 at 12:58 -0400, Tom Lane wrote: > "Kevin Grittner" writes: > > Tom Lane wrote: > >> It would be way nicer if we could strip trailing blanks on storage, > >> and then figure a way to either reconstitute them on output > > > How about pushing it even farther back -- always keep them with > > trimmed trailing spaces and add trailing spaces as required in > > operator functions? > > I think that's what I said. AFAIK there isn't any place where we'd > need to add back the spaces except the output function. All the > operators would be just as happy if the spaces weren't there. The overall problem is that we expect the Datum's of a datatype to know how to display themselves without any access to metadata. Another way of looking at this might be that we need a default FORMAT specifier associated with a column. Teradata used the FORMAT specifier on a column to allow you to specify a default format. That allowed you to specify leading/trailing zeros/spaces, decimal points and other characters. It would be good to have a generic way to specify formatting to a data type at the point you are manipulating it, rather than globally. That would be helpful because we currently have some very ugly things like the DateStyle parameter. That effects the Input and Output of data into Time/Date columns, forcing you to switch parameter values constantly as you load/select data. The current way makes it impossible to load data that has different date formats in different columns, for example. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Tom Lane wrote: > "Kevin Grittner" writes: >> Tom Lane wrote: >>> It would be way nicer if we could strip trailing blanks on >>> storage, and then figure a way to either reconstitute them on >>> output > >> How about pushing it even farther back -- always keep them with >> trimmed trailing spaces and add trailing spaces as required in >> operator functions? > > I think that's what I said. AFAIK there isn't any place where we'd > need to add back the spaces except the output function. All the > operators would be just as happy if the spaces weren't there. Sorry, I misread it. If this can be done, it might ease the transition from other products for some people. In Sybase there were significant performance benefits to using char() instead of varchar() in certain circumstances. In our first test conversion from Sybase we mapped all domains and columns which were char(n) in Sybase to the same in PostgreSQL, which caused some pain. It was no big deal to remap those to varchar() and re-run, but on the face of it, it seems like there wouldn't be a significant performance hit for char() with this change, so it would be one less thing for people to stub their toes on during migration. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
"Kevin Grittner" writes: > Tom Lane wrote: >> It would be way nicer if we could strip trailing blanks on storage, >> and then figure a way to either reconstitute them on output > How about pushing it even farther back -- always keep them with > trimmed trailing spaces and add trailing spaces as required in > operator functions? I think that's what I said. AFAIK there isn't any place where we'd need to add back the spaces except the output function. All the operators would be just as happy if the spaces weren't there. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Simon Riggs escribió: > I notice we lose on tuple access also. CHAR(n) is fixed length, but is > treated as variable length for offsets. Fixed character length != fixed byte length -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
On Thu, 2009-06-18 at 09:59 -0400, Tom Lane wrote: > Simon Riggs writes: > > Why is bcTruelen being called so many *more* times? > > I think you have misunderstood the context. err, no, I just misread the original text. Possibly a worse error :-? > It would be way nicer if we could strip trailing blanks on storage, > and then figure a way to either reconstitute them on output (problem > here is the output function doesn't have access to typmod) or > language-lawyer our way to deciding we don't have to. Is there a spare bit on the varhdr that can be set by datatypes? It would be useful to have a bit meaning "there is a typmod stored on this Datum", that would allow the output function to do some special processing. I notice we lose on tuple access also. CHAR(n) is fixed length, but is treated as variable length for offsets. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Tom Lane wrote: > It would be way nicer if we could strip trailing blanks on storage, > and then figure a way to either reconstitute them on output How about pushing it even farther back -- always keep them with trimmed trailing spaces and add trailing spaces as required in operator functions? -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Simon Riggs writes: > Why is bcTruelen being called so many *more* times? I think you have misunderstood the context. The char(n) code is defined to store trailing blanks (up to n) but to disregard the trailing blanks during comparisons. bcTrueLen is invoked during comparisons (not during storage) to figure out what the "valid" string length is for comparing. varchar considers any trailing blanks to be real, comparable data, so it simply hasn't got any equivalent code. It would be way nicer if we could strip trailing blanks on storage, and then figure a way to either reconstitute them on output (problem here is the output function doesn't have access to typmod) or language-lawyer our way to deciding we don't have to. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
* Simon Riggs: > On Thu, 2009-06-18 at 15:09 +0200, Stefan Kaltenbrunner wrote: > >> the testcase discusses here is indeed CHAR(n) vs. VARCHAR. > > OK, thanks for pointing out my error. But I think your point still makes sense. Is it really necessary to determine the unpadded length for a query such as this one? "SELECT c from sbtest where id between $1 and $2 order by c" (See the start of the thread.) -- Florian Weimer BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
On Thu, 2009-06-18 at 15:09 +0200, Stefan Kaltenbrunner wrote: > the testcase discusses here is indeed CHAR(n) vs. VARCHAR. OK, thanks for pointing out my error. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Simon Riggs wrote: On Thu, 2009-06-18 at 15:14 +0300, Marko Kreen wrote: On 6/18/09, Simon Riggs wrote: On Tue, 2009-06-16 at 10:23 -0400, Tom Lane wrote: > Speaking of which, what about some performance numbers? Like Heikki, > I'm quite suspicious of whether there is any real-world gain to be had > from this approach. It has been "lore" for some time that VARCHAR is cheaper than VARCHAR(n), so I'm looking forward to this improvement as a real-world gain. (If done right). I've looked at the code and the thing that bothers me is that I can't see where or why bcTruelen would be called more often for VARCHAR(n) than it would be for VARCHAR, on a Select/Sort only workload. I'd guess plain VARCHAR simply does not have blanks at the end, so Truelen is cheap. If we were comparing CHAR(n) with VARCHAR then I could agree. But we are comparing VARCHAR(n) and VARCHAR. There is no blank padding with VARCHAR, and the two have identical on-disk representations so the cost of bcTruelen looks like it should be identical in each case. the testcase discusses here is indeed CHAR(n) vs. VARCHAR. To reiterate - my numbers(8core/16 thread Nehalem xeon with 16 processes) are 46000qps for CHAR(n), 52000 qps for CHAR(n) with Jeremys patch(thout bcTrueLen is still on top in the profile) and 67000 qps for VARCHAR. Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
On Thu, 2009-06-18 at 15:14 +0300, Marko Kreen wrote: > On 6/18/09, Simon Riggs wrote: > > On Tue, 2009-06-16 at 10:23 -0400, Tom Lane wrote: > > > Speaking of which, what about some performance numbers? Like Heikki, > > > I'm quite suspicious of whether there is any real-world gain to be had > > > from this approach. > > > > > > It has been "lore" for some time that VARCHAR is cheaper than > > VARCHAR(n), so I'm looking forward to this improvement as a real-world > > gain. (If done right). > > > > I've looked at the code and the thing that bothers me is that I can't > > see where or why bcTruelen would be called more often for VARCHAR(n) > > than it would be for VARCHAR, on a Select/Sort only workload. > > I'd guess plain VARCHAR simply does not have blanks at the end, > so Truelen is cheap. If we were comparing CHAR(n) with VARCHAR then I could agree. But we are comparing VARCHAR(n) and VARCHAR. There is no blank padding with VARCHAR, and the two have identical on-disk representations so the cost of bcTruelen looks like it should be identical in each case. Is bcTruelen being called too many times, or is there, as Marko suggests, an explanation as to why calling bcTruelen costs more when we have a typmod set? -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
On 6/18/09, Simon Riggs wrote: > On Tue, 2009-06-16 at 10:23 -0400, Tom Lane wrote: > > Speaking of which, what about some performance numbers? Like Heikki, > > I'm quite suspicious of whether there is any real-world gain to be had > > from this approach. > > > It has been "lore" for some time that VARCHAR is cheaper than > VARCHAR(n), so I'm looking forward to this improvement as a real-world > gain. (If done right). > > I've looked at the code and the thing that bothers me is that I can't > see where or why bcTruelen would be called more often for VARCHAR(n) > than it would be for VARCHAR, on a Select/Sort only workload. I'd guess plain VARCHAR simply does not have blanks at the end, so Truelen is cheap. > Are we tuning the right thing? Is there some code we can completely > avoid? > > If not, does this mean it is a generic effect? Does this imply that > NUMERIC(n) is somehow worse than NUMERIC? etc.. Probably not. For numeric the (n) seems to be only checked input time. -- marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
On Tue, 2009-06-16 at 10:23 -0400, Tom Lane wrote: > Speaking of which, what about some performance numbers? Like Heikki, > I'm quite suspicious of whether there is any real-world gain to be had > from this approach. It has been "lore" for some time that VARCHAR is cheaper than VARCHAR(n), so I'm looking forward to this improvement as a real-world gain. (If done right). I've looked at the code and the thing that bothers me is that I can't see where or why bcTruelen would be called more often for VARCHAR(n) than it would be for VARCHAR, on a Select/Sort only workload. Are we tuning the right thing? Is there some code we can completely avoid? If not, does this mean it is a generic effect? Does this imply that NUMERIC(n) is somehow worse than NUMERIC? etc.. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
On Tue, 2009-06-16 at 00:24 -0400, Robert Haas wrote: > There's not much point taking the length of the word Not sure why we need to be calculating the length here anyway. ISTM that there is no need to reconfirm the length of the data, since it is already checked to be that length at insert. Why is bcTruelen being called so many *more* times? -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Jeremy Kerr wrote: Hi all, Speaking of which, what about some performance numbers? OK, benchmarks done: http://ozlabs.org/~jk/projects/db/data/postgres.bcTruelen/ Summary: small increase in performance (~1-2% on my machine), at about 1.5 standard deviations from the mean. Profiles show a decent drop in hits within bcTruelen. However: Sysbench seems to be quite heavy with the fixed-width char types, so may end up calling bcTruelen more than most workloads. Would be nice to get some x86 numbers too, but I don't have a suitable machine here. This improves my numbers from 46000 queries/s to 52000 queries/s for: --num-threads=16 --test=oltp --db-ps-mode=auto --pgsql-user=postgres --test=oltp --oltp-read-only=on --oltp-sum-ranges=0 --oltp-simple-ranges=0 --oltp-order-ranges=10 --oltp-point-selects=0 --oltp-distinct-ranges=0 --oltp-skip-trx=off run with a profile 16 connections like: samples %symbol name 1925976 23.7136 bcTruelen 3294534.0564 AllocSetAlloc 2921763.5974 slot_deform_tuple 2789963.4351 index_getnext 1653972.0365 _bt_checkkeys 1556211.9161 appendBinaryStringInfo 1412951.7397 LWLockAcquire 1361971.6769 internal_putbytes 1304741.6065 AllocSetFree 1213411.4940 printtup 1164131.4333 hash_search_with_hash_value 1095731.3491 FunctionCall2 95101 1.1709 heap_form_minimal_tuple 91475 1.1263 enlargeStringInfo 90841 1.1185 heap_fill_tuple 89926 1.1072 ExecProcNode 86112 1.0603 varstr_cmp and after the patch applied: samples %symbol name 2225769 7.8378 bcTruelen 1335050 4.7013 index_getnext 1296272 4.5647 AllocSetAlloc 1132026 3.9863 slot_deform_tuple 7022192.4728 _bt_checkkeys 6406752.2561 appendBinaryStringInfo 6117832.1543 LWLockAcquire 5994022.1107 AllocSetFree 5905572.0796 internal_putbytes 5255261.8506 printtup 4760771.6765 hash_search_with_hash_value 4596601.6186 FunctionCall2 4235691.4916 ExecProcNode 3914761.3785 heap_form_minimal_tuple 3856891.3582 heap_fill_tuple 3832311.3495 enlargeStringInfo 3683901.2973 comparetup_heap 3634811.2800 varstr_cmp 3602611.2686 ExecProject 3566961.2561 pq_putmessage 3541931.2473 MemoryContextAlloc 3412621.2017 LWLockRelease 3379701.1901 pq_begintypsend 3210691.1306 pfree 3108061.0945 tuplesort_gettuple_common Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
On Wed, Jun 17, 2009 at 3:26 PM, Jeremy Kerr wrote:
> Hurjeet,
>
> > http://wiki.postgresql.org/wiki/Working_with_Git#Context_diffs_with_G
> >it
>
> Awesome, thanks.
>
> I'm going to wait for a decision before reformatting and sending the
> diff.
>
You might want to submit the patch (if modified from previous submission,
which I think it is), if you want others to benchmark on other archs.
Best regards,
--
Lets call it Postgres
EnterpriseDB http://www.enterprisedb.com
gurjeet[.sin...@enterprisedb.com
singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com
Mail sent from my BlackLaptop device
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Heikki Linnakangas wrote: Jeremy Kerr wrote: Speaking of which, what about some performance numbers? OK, benchmarks done: http://ozlabs.org/~jk/projects/db/data/postgres.bcTruelen/ Summary: small increase in performance (~1-2% on my machine), at about 1.5 standard deviations from the mean. Profiles show a decent drop in hits within . However: Sysbench seems to be quite heavy with the fixed-width char types, so may end up calling bcTruelen more than most workloads. Would be nice to get some x86 numbers too, but I don't have a suitable machine here. So: The increase in performance is positive on this workload, albeit fairly minor. Downside is increased code complexity. Based on this benchmark, I don't think this patch is worth it.. what exactly is getting graphed here - is the what sysbench reports as transactions/s or the operations/queries per second? if yes it is important to notice that sysnbench by default executes multiple queries per transaction and and several different kind of queries. Only some of those queries have bcTruelen() showing up on top of the profile. Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Jeremy Kerr wrote: Speaking of which, what about some performance numbers? OK, benchmarks done: http://ozlabs.org/~jk/projects/db/data/postgres.bcTruelen/ Summary: small increase in performance (~1-2% on my machine), at about 1.5 standard deviations from the mean. Profiles show a decent drop in hits within bcTruelen. However: Sysbench seems to be quite heavy with the fixed-width char types, so may end up calling bcTruelen more than most workloads. Would be nice to get some x86 numbers too, but I don't have a suitable machine here. So: The increase in performance is positive on this workload, albeit fairly minor. Downside is increased code complexity. Based on this benchmark, I don't think this patch is worth it.. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
On Wed, Jun 17, 2009 at 3:01 PM, Jeremy Kerr wrote:
> Will re-send the patch once I work out how to get git to create a
> context diff...
>
http://wiki.postgresql.org/wiki/Working_with_Git#Context_diffs_with_Git
--
Lets call it Postgres
EnterpriseDB http://www.enterprisedb.com
gurjeet[.sin...@enterprisedb.com
singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com
Mail sent from my BlackLaptop device
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Hi all, > Speaking of which, what about some performance numbers? OK, benchmarks done: http://ozlabs.org/~jk/projects/db/data/postgres.bcTruelen/ Summary: small increase in performance (~1-2% on my machine), at about 1.5 standard deviations from the mean. Profiles show a decent drop in hits within bcTruelen. However: Sysbench seems to be quite heavy with the fixed-width char types, so may end up calling bcTruelen more than most workloads. Would be nice to get some x86 numbers too, but I don't have a suitable machine here. So: The increase in performance is positive on this workload, albeit fairly minor. Downside is increased code complexity. Will re-send the patch once I work out how to get git to create a context diff... Cheers, Jeremy -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
> -Original Message- > From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers- > ow...@postgresql.org] On Behalf Of Stephen Frost > Sent: Tuesday, June 16, 2009 5:47 AM > To: Greg Stark > Cc: Robert Haas; Jeremy Kerr; ; Alvaro > Herrera; Stefan Kaltenbrunner; Gurjeet Singh > Subject: Re: [HACKERS] [PATCH] backend: compare word-at-a-time in > bcTruelen > On 64-bit machines, the native word size is 64-bits (obviously), and comparing 32 bits at a time is much slower than comparing 64 bits at a time. You might want to consider this. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Jeremy Kerr wrote: Hi Tom, Speaking of which, what about some performance numbers? Like Heikki, I'm quite suspicious of whether there is any real-world gain to be had from this approach. Will send numbers tomorrow, with the reworked patch. I can easily redo my testing as well if required. Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Hi Tom, > Speaking of which, what about some performance numbers? Like Heikki, > I'm quite suspicious of whether there is any real-world gain to be > had from this approach. Will send numbers tomorrow, with the reworked patch. Cheers, Jeremy -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Greg Stark writes: > On some architectures like intel accessing unaligned ints is just > slow. On others (Alpha and PPC iirc?) it is an immediate bus error. To a first approximation, Intel is the *only* popular architecture that doesn't bus-error on unaligned accesses. (And I'm sure their chip designers rue the day that their predecessors chose to allow that.) There are some systems where the kernel trap handler then proceeds to emulate the unaligned access for you, but that gives new meaning to the word "slow". You definitely don't want to be doing it in a patch that's alleged to give a performance improvement. Speaking of which, what about some performance numbers? Like Heikki, I'm quite suspicious of whether there is any real-world gain to be had from this approach. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Hi all,
> That's correct. To check the alignment you would have to look at the
> actual pointer. I would suggest using the existing macros to handle
> alignment. Hm, though the only one I see offhand which is relevant is
> the moderately silly PointerIsAligned(). Still it would make the code
> clearer even if it's pretty simple.
Yes, the code (incorrectly) assumes that any multiple-of-4 index into
the char array is aligned, and so the array itself must be aligned for
this to work.
I'll rework the patch, testing the pointer alignment directly instead.
> Incidentally, the char foo[4] = {' ',' ',' ',' '} suggestion is, I
> think, bogus. There would be no alignment guarantee on that array.
> Personally I'm find with 0x20202020 with a comment explaining what it
> is.
The variable is called 'spaces', but I can add extra comments if
preferred.
Cheers,
Jeremy
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Robert Haas wrote: Ooh, good point. I still don't like the 0x20 thing, but using uint32 instead of int or long is the main point, unless we support any platforms where 0x20 != ' '. All our server encodings are strictly ASCII supersets. So 0x20 is always the space character. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
On Tue, Jun 16, 2009 at 8:38 AM, Greg Stark wrote:
> On Tue, Jun 16, 2009 at 1:03 PM, Robert Haas wrote:
>> I see that... but I don't think the test in the first loop is correct.
>> It's based on the value of i % 4, but I'm not convinced that you know
>> anything about the alignment at the point where i == 0.
>
> That's correct. To check the alignment you would have to look at the
> actual pointer. I would suggest using the existing macros to handle
> alignment. Hm, though the only one I see offhand which is relevant is
> the moderately silly PointerIsAligned(). Still it would make the code
> clearer even if it's pretty simple.
>
> Incidentally, the char foo[4] = {' ',' ',' ',' '} suggestion is, I
> think, bogus. There would be no alignment guarantee on that array.
> Personally I'm find with 0x20202020 with a comment explaining what it
> is.
Ooh, good point. I still don't like the 0x20 thing, but using uint32
instead of int or long is the main point, unless we support any
platforms where 0x20 != ' '.
...Robert
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
On Tue, Jun 16, 2009 at 1:41 PM, Stephen Frost wrote: > > Ah, you may be half right there (see below). It does appear to be > assuming that char *s (or s[i == 0]) is aligned, which isn't a > guarentee (in fact, it might never be right..). If having it actually > aligned is an important bit (as opposed to just doing the comparisons in > larger but possibly unaligned blocks) then that'd make a difference. On some architectures like intel accessing unaligned ints is just slow. On others (Alpha and PPC iirc?) it is an immediate bus error. I would actually be more curious whether we can do th e comparison without having to pre-scan for the spaces at the end than trying to opimize that prescan. -- greg http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
* Greg Stark (gsst...@mit.edu) wrote: > There are two points here that kind of cancel each other out :) Thanks for the insight. :) Stephen signature.asc Description: Digital signature
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
On Tue, Jun 16, 2009 at 1:03 PM, Robert Haas wrote: > >> On the flip side, I am curious as to if the arguments to a stored >> procedure are always aligned or not. Never had a case to care before, >> but if palloc() is always going to return an aligned chunk of memory >> (per MemSetAligned in c.h) it makes me wonder. > > Well, if it's char(n) for n <~ 126, it's going to have a 1-byte > varlena header... There are two points here that kind of cancel each other out :) If the data is in fact returned from a palloc because it was the result of some other function call then it will almost certainly have a 4-byte header and that'll be aligned. There are some exceptions where functions are just returning copies and copy the whole datum though, but the point is we normally don't toast or pack varlenas unless they're being stored on disk. However that's all irrelevant because there's no guarantee the data being passed will have been palloced at all. You could get a pointer to data in a shared buffer. Ie, data on disk. That will be aligned based on how tuples are packed on disk which is precisely where we go out of our way to avoid wasting space on alignment. -- greg http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
* Robert Haas (robertmh...@gmail.com) wrote: > I see that... but I don't think the test in the first loop is correct. > It's based on the value of i % 4, but I'm not convinced that you know > anything about the alignment at the point where i == 0. Ah, you may be half right there (see below). It does appear to be assuming that char *s (or s[i == 0]) is aligned, which isn't a guarentee (in fact, it might never be right..). If having it actually aligned is an important bit (as opposed to just doing the comparisons in larger but possibly unaligned blocks) then that'd make a difference. If the code as-is showed performance improvment even when it's working on less-than-aligned blocks, I'd be curious what would happen if it was actually aligned. Unfortunately, the results of such would probably be heavily architecture dependent.. > > On the flip side, I am curious as to if the arguments to a stored > > procedure are always aligned or not. Never had a case to care before, > > but if palloc() is always going to return an aligned chunk of memory > > (per MemSetAligned in c.h) it makes me wonder. > > Well, if it's char(n) for n <~ 126, it's going to have a 1-byte > varlena header... Right, but I'm talking about the base of the argument itself, not the start of the data. If every variable length argument to a stored procedure is palloc()'d independently, and palloc()'s always return aligned memory, we'd at least know that the base of the argument is aligned and could figure out the header size and then do the comparisons accordingly. This would also mean, of course, that we'd almost(?) never have s[i == 0] on an aligned boundary due to the header. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
On Tue, Jun 16, 2009 at 1:03 PM, Robert Haas wrote:
> I see that... but I don't think the test in the first loop is correct.
> It's based on the value of i % 4, but I'm not convinced that you know
> anything about the alignment at the point where i == 0.
That's correct. To check the alignment you would have to look at the
actual pointer. I would suggest using the existing macros to handle
alignment. Hm, though the only one I see offhand which is relevant is
the moderately silly PointerIsAligned(). Still it would make the code
clearer even if it's pretty simple.
Incidentally, the char foo[4] = {' ',' ',' ',' '} suggestion is, I
think, bogus. There would be no alignment guarantee on that array.
Personally I'm find with 0x20202020 with a comment explaining what it
is.
--
greg
http://mit.edu/~gsstark/resume.pdf
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
On Tue, Jun 16, 2009 at 6:30 AM, Stephen Frost wrote: > * Robert Haas (robertmh...@gmail.com) wrote: >> As I look at this, another problem is that it seems to me that you're >> assuming that VARDATA_ANY() will return an aligned pointer, which >> isn't necessarily the case (see src/include/postgres.h). > > I believe you need to look at it more carefully. I don't think it's > making any such assumption. Specifically, it has three loops; an "until > we're aligned" loop, then a "while we're aligned", and a "when we've > done all the aligned we could do". I see that... but I don't think the test in the first loop is correct. It's based on the value of i % 4, but I'm not convinced that you know anything about the alignment at the point where i == 0. I might be all wet here, I haven't looked at this area of the code in detail. > On the flip side, I am curious as to if the arguments to a stored > procedure are always aligned or not. Never had a case to care before, > but if palloc() is always going to return an aligned chunk of memory > (per MemSetAligned in c.h) it makes me wonder. Well, if it's char(n) for n <~ 126, it's going to have a 1-byte varlena header... ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
* Robert Haas (robertmh...@gmail.com) wrote: > As I look at this, another problem is that it seems to me that you're > assuming that VARDATA_ANY() will return an aligned pointer, which > isn't necessarily the case (see src/include/postgres.h). I believe you need to look at it more carefully. I don't think it's making any such assumption. Specifically, it has three loops; an "until we're aligned" loop, then a "while we're aligned", and a "when we've done all the aligned we could do". On the flip side, I am curious as to if the arguments to a stored procedure are always aligned or not. Never had a case to care before, but if palloc() is always going to return an aligned chunk of memory (per MemSetAligned in c.h) it makes me wonder. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Robert Haas wrote: The advice in Stephen's email is also very good - in particular, whatever you come up with, you should submit performance results. Note that while --enable-profiling is very useful and profiling numbers are good to submit, you'll also want to make sure you do a build that is optimized for speed (i.e. no profiling, no casserts, no debug) and do timings on that. I'm particularly interested to see how much the patch hurts performance in the cases where it's not helping. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
On Mon, Jun 15, 2009 at 9:51 PM, Jeremy Kerr wrote:
> I was considering something like:
>
> unsigned int spaces;
> const unsigned int wordsize = sizeof(unsigned int);
>
> memset(&spaces, ' ', wordsize);
>
> In most cases, the compiler should be able to optimise the memset out,
> but it may introduce overhead where this is not possible.
What about just:
static char spaces[4] = { ' ', ' ', ' ', ' ' };
and then ... * (uint32 *) spaces?
There's not much point taking the length of the word when you've
initialized it to contain exactly 4 bytes. What you want to do is
pick the flavor of integer that will be the same length as what you've
initialized, and it turns out we already have that (see
src/include/c.h).
As I look at this, another problem is that it seems to me that you're
assuming that VARDATA_ANY() will return an aligned pointer, which
isn't necessarily the case (see src/include/postgres.h).
The advice in Stephen's email is also very good - in particular,
whatever you come up with, you should submit performance results.
Note that while --enable-profiling is very useful and profiling
numbers are good to submit, you'll also want to make sure you do a
build that is optimized for speed (i.e. no profiling, no casserts, no
debug) and do timings on that.
...Robert
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Jeremy, * Jeremy Kerr (j...@ozlabs.org) wrote: > Signed-off-by: Jeremy Kerr > > --- > src/backend/utils/adt/varchar.c | 24 +--- > 1 file changed, 21 insertions(+), 3 deletions(-) Thanks for the contribution. A couple of comments: The documentation for submitting a patch to PostgreSQL is here: http://wiki.postgresql.org/wiki/Submitting_a_Patch There is also a Developer FAQ available here: http://wiki.postgresql.org/wiki/Developer_FAQ The PostgreSQL core folks prefer context diffs (it's not my preference, but I'm not part of core, nor am I a committer :). There are a number of things requested to be included with a patch, but in particular I would point out: Which CVS branch the patch is against (ordinarily this will be HEAD). Whether it compiles and tests successfully, so we know nothing obvious is broken. Whether it contains any platform-specific items and if so, has it been tested on other platforms. Describe the effect your patch has on performance, if any. If the patch is intended to improve performance, it's a good idea to include some reproducible tests to demonstrate the improvement. You might check out sections 3 & 6 of src/include/c.h. Section 3 defines standard system types, while section 6 defines some widely useful macros; in particular our custom MemSet and MemSetAligned, which work on aligned memory structures for improved performance. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Robert, > This looks very non-portable to me. Unsurprisingly, I'm new to postgres hacking and the large number of supported platforms :) I was considering something like: unsigned int spaces; const unsigned int wordsize = sizeof(unsigned int); memset(&spaces, ' ', wordsize); In most cases, the compiler should be able to optimise the memset out, but it may introduce overhead where this is not possible. However, are there any supported platforms where sizeof(unsigned int) != 4 ? Cheers, Jeremy -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
On Jun 15, 2009, at 9:04 PM, Jeremy Kerr wrote:
Signed-off-by: Jeremy Kerr
---
src/backend/utils/adt/varchar.c | 24 +---
1 file changed, 21 insertions(+), 3 deletions(-)
diff --git a/src/backend/utils/adt/varchar.c b/src/backend/utils/adt/
varchar.c
index 5f3c658..6889dff 100644
--- a/src/backend/utils/adt/varchar.c
+++ b/src/backend/utils/adt/varchar.c
@@ -624,16 +624,34 @@ varchartypmodout(PG_FUNCTION_ARGS)
static int
bcTruelen(BpChar *arg)
{
+const unsigned int spaces = 0x20202020;
+const intwordsize = sizeof(spaces);
This looks very non-portable to me.
...Robert
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [PATCH] backend: compare word-at-a-time in bcTruelen
Signed-off-by: Jeremy Kerr
---
src/backend/utils/adt/varchar.c | 24 +---
1 file changed, 21 insertions(+), 3 deletions(-)
diff --git a/src/backend/utils/adt/varchar.c b/src/backend/utils/adt/varchar.c
index 5f3c658..6889dff 100644
--- a/src/backend/utils/adt/varchar.c
+++ b/src/backend/utils/adt/varchar.c
@@ -624,16 +624,34 @@ varchartypmodout(PG_FUNCTION_ARGS)
static int
bcTruelen(BpChar *arg)
{
+ const unsigned int spaces = 0x20202020;
+ const int wordsize = sizeof(spaces);
char *s = VARDATA_ANY(arg);
int i;
- int len;
- len = VARSIZE_ANY_EXHDR(arg);
- for (i = len - 1; i >= 0; i--)
+ i = VARSIZE_ANY_EXHDR(arg) - 1;
+
+ /* compare down to an aligned boundary */
+ for (; i >= 0 && i % wordsize != wordsize - 1; i--)
{
if (s[i] != ' ')
+ return i + 1;
+ }
+
+ /* now that we're aligned, compare word at a time */
+ for (; i >= wordsize - 1; i -= wordsize)
+ {
+ if (*(unsigned int *)(s + i - (wordsize - 1)) != spaces)
break;
}
+
+ /* check within the last non-matching word */
+ for (; i >= 0; i--)
+ {
+ if (s[i] != ' ')
+ break;
+ }
+
return i + 1;
}
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
