Обсуждение: Varchar and binary protocol
Hi, I do performance tests against orignal JDBC driver and my version in binary and in text mode. I saw strange results when I was reading varchar values. Here is some output from simple benchmark Plain strings speed Execution: 8316582 , local: 2116608 , all: 10433190 Binary strings speed Execution: 9354613 , local: 2755949 , all: 12110562 Text NG strings speed Execution: 8346902 , local: 2704242 , all: 11051144 Plain is standard JDBC driver, Binary is my version with binary transfer, Text is my version with normal transfer. 1st column, "Execution" is time spend on query execution this includes send, recivie proto message, store it, etc, no conversion to output format. Values are in nanoseconds. In new version I added some functionality, but routines to read parts in "Execution" block are almost same for binary and text. But as you see the binary version is 10-20% slower then orginal, and my text version, if I increase number of read records this proportion will not change. I done many checks, against even "skip proto message content" driver, end results was same 10-20% slower. Regards, Radek.
On Sat, Feb 05, 2011 at 10:59:45PM +0100, Rados??aw Smogura wrote: > I do performance tests against orignal JDBC driver and my version in binary > and in text mode. I saw strange results when I was reading varchar values. > Here is some output from simple benchmark > > Plain strings speed Execution: 8316582 , local: 2116608 , all: > 10433190 > Binary strings speed Execution: 9354613 , local: 2755949 , all: > 12110562 > Text NG strings speed Execution: 8346902 , local: 2704242 , all: > 11051144 > > Plain is standard JDBC driver, Binary is my version with binary transfer, Text > is my version with normal transfer. 1st column, "Execution" is time spend on > query execution this includes send, recivie proto message, store it, etc, no > conversion to output format. Values are in nanoseconds. > > In new version I added some functionality, but routines to read parts in > "Execution" block are almost same for binary and text. > > But as you see the binary version is 10-20% slower then orginal, and my text > version, if I increase number of read records this proportion will not change. > I done many checks, against even "skip proto message content" driver, end > results was same 10-20% slower. Comparing "COPY tbl(varchar_col) TO '/dev/null'" to "COPY tbl(varchar_col) TO '/dev/null' WITH BINARY" gives a better sense of the situation. Your data could have reflected a backend performance problem, but it could just as well have arisen from your client-side changes. (This thread also really belongs on pgsql-performance. See http://wiki.postgresql.org/wiki/SlowQueryQuestions) I can reproduce a 20% slowdown using the test case I mentioned above. I didn't investigate much further. Thanks, nm
On Sat, Feb 5, 2011 at 4:59 PM, Radosław Smogura <rsmogura@softperience.eu> wrote: > Hi, > > I do performance tests against orignal JDBC driver and my version in binary > and in text mode. I saw strange results when I was reading varchar values. > Here is some output from simple benchmark > > Plain strings speed Execution: 8316582 , local: 2116608 , all: > 10433190 > Binary strings speed Execution: 9354613 , local: 2755949 , all: > 12110562 > Text NG strings speed Execution: 8346902 , local: 2704242 , all: > 11051144 > > Plain is standard JDBC driver, Binary is my version with binary transfer, Text > is my version with normal transfer. 1st column, "Execution" is time spend on > query execution this includes send, recivie proto message, store it, etc, no > conversion to output format. Values are in nanoseconds. > > In new version I added some functionality, but routines to read parts in > "Execution" block are almost same for binary and text. > > But as you see the binary version is 10-20% slower then orginal, and my text > version, if I increase number of read records this proportion will not change. > I done many checks, against even "skip proto message content" driver, end > results was same 10-20% slower. Since there is basically zero difference in how *varchar* is handled in the database for the text or binary protocols (AFAIK, they use the same code), this is almost certainly an issue with the JDBC driver, or your benchmark application. merlin
Actually difference is http://archives.postgresql.org/pgsql-hackers/2011-02/msg00415.php Merlin Moncure <mmoncure@gmail.com> Thursday 10 February 2011 08:48:26 > On Sat, Feb 5, 2011 at 4:59 PM, Radosław Smogura > > <rsmogura@softperience.eu> wrote: > > Hi, > > > > I do performance tests against orignal JDBC driver and my version in > > binary and in text mode. I saw strange results when I was reading > > varchar values. Here is some output from simple benchmark > > > > Plain strings speed Execution: 8316582 , local: 2116608 , > > all: 10433190 > > Binary strings speed Execution: 9354613 , local: 2755949 , > > all: 12110562 > > Text NG strings speed Execution: 8346902 , local: 2704242 , > > all: 11051144 > > > > Plain is standard JDBC driver, Binary is my version with binary transfer, > > Text is my version with normal transfer. 1st column, "Execution" is time > > spend on query execution this includes send, recivie proto message, > > store it, etc, no conversion to output format. Values are in > > nanoseconds. > > > > In new version I added some functionality, but routines to read parts in > > "Execution" block are almost same for binary and text. > > > > But as you see the binary version is 10-20% slower then orginal, and my > > text version, if I increase number of read records this proportion will > > not change. I done many checks, against even "skip proto message > > content" driver, end results was same 10-20% slower. > > Since there is basically zero difference in how *varchar* is handled > in the database for the text or binary protocols (AFAIK, they use the > same code), this is almost certainly an issue with the JDBC driver, or > your benchmark application. > > merlin
On Thu, Feb 10, 2011 at 2:56 AM, Radosław Smogura <rsmogura@softperience.eu> wrote: > Merlin Moncure <mmoncure@gmail.com> Thursday 10 February 2011 08:48:26 >> On Sat, Feb 5, 2011 at 4:59 PM, Radosław Smogura >> >> Since there is basically zero difference in how *varchar* is handled >> in the database for the text or binary protocols (AFAIK, they use the >> same code), this is almost certainly an issue with the JDBC driver, or >> your benchmark application. >> >> merlin > > Actually difference is > http://archives.postgresql.org/pgsql-hackers/2011-02/msg00415.php > ah, I stand corrected -- interesting. merlin