Re: Input and Output data traffic

Well I finally figure it out (or somewhat).

The "problem" is indeed the 'sslmode' parameter of the psycopg2 connection.

If omitted, I think that's the default value is 'prefer' (enable ssl if the
server support). With that I got the very low value of 29014KB for traffic.

When i explicitly set it to 'disable', I got almost the same result of java
221222KB.

So here's the question, does the SSL also compress the traffic data? I
enabled the self-signed certificate mode in the Java test and got the same
result.

Note: I checked the package contents of the traffic with Wireshark to check
if it's indeed encrypted.

2011/5/13 Israel Ben Guilherme Fonseca <israel(dot)bgf(at)gmail(dot)com>

> Could you give some insights about how to check for the special parameters?
>
> I mean, the server was installed with the default options (apt-get
> install), if it's a server side configurations it's the same for both.
>
> From my readings:
>
> 1 - SSL need all those certified stuff, I didn't do any of this.
> 2 - Is binary mode only for the COPY statement? Well, I didn't do any of
> special configurations for this too, only if it's default on psycopg2.
>
>
> 2011/5/13 Nicolas Grilly <nicolas(at)gardentechno(dot)com>
>
>> Interesting. Just two random thoughts:
>> - Connections to a PostgreSQL server can use SSL or not. Have you checked
>> your Python and Java test cases are both in the same mode?
>> - Query results can be returned in text mode or binary mode. Have you
>> checked that too?
>>
>> These two settings can explain the big difference you noticed.
>>
>> Best,
>>
>> Nicolas
>>
>> On Fri, May 13, 2011 at 06:48, Israel Ben Guilherme Fonseca <
>> israel(dot)bgf(at)gmail(dot)com> wrote:
>>
>>> Hi again,
>>>
>>> I'm still doing my performance tests, but now psycopg2 is really kicking
>>> the bucket with an extremely low traffic usage in comparison to Java. My
>>> brand new test, shows a difference of 8 times, and again I think that it
>>> could be wrong, so that's why I'm here... again.
>>>
>>> A guy in the jdbc-mailist give me a tip of a program to measure traffic
>>> usage, it's called 'wireshark', very good indeed and I got this following
>>> results:
>>>
>>> 1 'Person' table, 2 columns (id, name), 7000++ registers.
>>>
>>> The traffic difference was:
>>>
>>> Java 220861 Bytes
>>> Python 29014 Bytes
>>>
>>> A difference of 8x. Now, I did the same test with my old proxy
>>> implementation and got similar results. So.. that's strange. Very strange.
>>> My question is, is there any specific implementation detail on psycopg?
>>> Maybe some voodoo magic? :)
>>>
>>> I think that's much probably that I'm doing something wrong, but I did
>>> and redid all the tests many times until now, that I'm starting to accept
>>> this.
>>>
>>> My ONLY track of explanation is this postgres log (debug5)
>>>
>>> JAVA
>>>
>>> simpletests DEPURAÇÃO: análise de <unnamed>: select * from "Person"
>>> simpletests DEPURAÇÃO: StartTransactionCommand
>>> simpletests DEPURAÇÃO: StartTransaction
>>> simpletests DEPURAÇÃO: name: unnamed; blockState: DEFAULT; state:
>>> INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
>>> simpletests DEPURAÇÃO: ligação de <unnamed> para <unnamed>
>>> simpletests LOG: executar <unnamed>: select * from "Person"
>>> simpletests DEPURAÇÃO: CommitTransactionCommand
>>> simpletests DEPURAÇÃO: CommitTransaction
>>> simpletests DEPURAÇÃO: name: unnamed; blockState: STARTED; state:
>>> INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
>>> simpletests DEPURAÇÃO: shmem_exit(0): 6 callbacks to make
>>> simpletests DEPURAÇÃO: proc_exit(0): 4 callbacks to make
>>> simpletests LOG: desconexão: tempo da sessão: 0:00:12.172
>>> usuário=postgres banco de dados=simpletests máquina=localhost port=56401
>>> simpletests DEPURAÇÃO: exit(0)
>>> simpletests DEPURAÇÃO: shmem_exit(-1): 0 callbacks to make
>>> simpletests DEPURAÇÃO: proc_exit(-1): 0 callbacks to make
>>>
>>> PYTHON
>>>
>>> simpletests DEPURAÇÃO: StartTransactionCommand
>>> simpletests DEPURAÇÃO: StartTransaction
>>> simpletests DEPURAÇÃO: name: unnamed; blockState: DEFAULT; state:
>>> INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
>>> simpletests LOG: comando: BEGIN; SET TRANSACTION ISOLATION LEVEL READ
>>> COMMITTED
>>> simpletests DEPURAÇÃO: ProcessUtility
>>> simpletests DEPURAÇÃO: CommitTransactionCommand
>>> simpletests DEPURAÇÃO: StartTransactionCommand
>>> simpletests DEPURAÇÃO: ProcessUtility
>>> simpletests DEPURAÇÃO: CommitTransactionCommand
>>> simpletests DEPURAÇÃO: StartTransactionCommand
>>> simpletests LOG: comando: select * from "Person"
>>> simpletests DEPURAÇÃO: CommitTransactionCommand
>>>
>>> The python log looks somewhat cleaner, and Java got this:
>>>
>>> simpletests DEPURAÇÃO: shmem_exit(0): 6 callbacks to make
>>> simpletests DEPURAÇÃO: proc_exit(0): 4 callbacks to make
>>>
>>> 10 extra callbacks? I dont have any idea about what is about, but maybe
>>> it could mean something.
>>>
>>> I hosted all the files and source code for this test-case on google code.
>>> If you are using ubuntu it probabbly take just 10 minutes to execute
>>> everthing and see this with your own eyes (and hopefully someone would say
>>> "your idiot, you did THAT <code> wrong"). Just read the readme to get the
>>> tests running.
>>>
>>> http://orm-native-comparative.googlecode.com/files/tests.zip
>>>
>>> If you got time to test it, thanks. If you don't, thanks anyway to at
>>> least read this huge email.
>>>
>>> Israel
>>>
>>
>>
>