[pypy-dev] PyPQ: a dbapi 2 PostgreSQL driver working with pypy

[Maciej Fijalkowski]

On Wed, Oct 5, 2011 at 12:59 PM, Igor Katson <igor.katson at gmail.com> wrote:
> So I've launched a few tests for all PostgreSQL python drivers
> out there, and optimized PyPq a bit along the way.
>
> *IntergerInsert* test does this 30000 times (i wanted more, but pg8000
> bloats
> postgres memory usage to 1,5 gigabytes on this somewhy, so i lowered the
> amount
> of queries to 30000)
>
>    cursor.executemany('insert into test_table values(%s, %s, %s, %s)',
> [(1,2,3,4)] * 30000)
>
> *IntegerSelect* selects this data back into python
>
> *VariableDataInsert* does the same as IntegerInsert, but inserts a string, a
> datetime, a date and a timestamp into the database (except for pg8000, which
> told
> me that it did not support timestamps)
>
> *VariableDataSelect* selects this data back into python
>
> cPython 2.7.2 (32-bit, archlinux latest build), 30000 inserts
>
> Psycopg2IntegerInsert.test_insert took 1.78s
> .Psycopg2IntegerSelect.test_select took 0.06s
> .Psycopg2VariableDataInsert.test_insert took 2.57s
> .Psycopg2VariableDataSelect.test_select took 0.25s
>
> .Psycopg2ctIntegerInsert.test_insert took 4.46s
> .Psycopg2ctIntegerSelect.test_select took 1.62s
> .Psycopg2ctVariableDataInsert.test_insert took 6.00s
> .Psycopg2ctVariableDataSelect.test_select took 3.31s
>
> .PyPQIntegerInsertTest.test_insert took 3.41s
> .PyPQIntegerSelectTest.test_select took 0.84s
> .PyPQVariableDataInsertTest.test_insert took 4.07s
> .PyPQVariableDataSelectTest.test_select took 3.70s
>
> pg8000IntegerInsert.test_insert took 16.20s
> .pg8000IntegerSelect.test_select took 1.43s
> .pg8000VariableDataInsert.test_insert took 18.00s
> .pg8000VariableDataSelect.test_select took 2.17s
>
> PyPy 1.6.0 (32-bit, archlinux latest build), 30000 inserts
>
> Psycopg2ctIntegerInsert.test_insert took 2.69s
> .Psycopg2ctIntegerSelect.test_select took 0.63s
> .Psycopg2ctVariableDataInsert.test_insert took 4.53s
> .Psycopg2ctVariableDataSelect.test_select took 1.36s
>
> .PyPQIntegerInsertTest.test_insert took 4.61s
> .PyPQIntegerSelectTest.test_select took 0.37s
> .PyPQVariableDataInsertTest.test_insert took 4.48s
> .PyPQVariableDataSelectTest.test_select took 1.58s
>
> pg8000IntegerInsert.test_insert took 8.34s
> .pg8000IntegerSelect.test_select took 0.60s
> .pg8000VariableDataInsert.test_insert took 9.15s
> .pg8000VariableDataSelect.test_select took 1.64s
>
> As we can see, pg8000 is slow on inserts, and as i've said, it does some
> strange
> things to my postgres, bloating the postgres memory usage to 1.5 gigabytes
> (i tried
> to insert 100000 records with executemany)
>
> On cPython, pypq is faster than psycopg2ct and pg8000, except for
> VariableDataSelect
> test.
> On PyPy, all of them get faster, except pypq, though it is still a bit
> faster
> than psycopg2ct in 2 tests.
>
> Next, I tested pypq side by side to see the difference more clearly.
>
> Here are the results.
>
> cPython 2.7.2 (32-bit, archlinux latest build), 200000 inserts
>
> Psycopg2IntegerInsert.test_insert took 12.22s
> .Psycopg2IntegerSelect.test_select took 0.39s
> .Psycopg2VariableDataInsert.test_insert took 17.30s
> .Psycopg2VariableDataSelect.test_select took 1.71s
>
> .Psycopg2ctIntegerInsert.test_insert took 28.56s
> .Psycopg2ctIntegerSelect.test_select took 10.48s
> .Psycopg2ctVariableDataInsert.test_insert took 38.53s
> .Psycopg2ctVariableDataSelect.test_select took 21.67s
>
> .PyPQIntegerInsertTest.test_insert took 22.53s
> .PyPQIntegerSelectTest.test_select took 5.59s
> .PyPQVariableDataInsertTest.test_insert took 26.86s
> .PyPQVariableDataSelectTest.test_select took 24.84s
>
> PyPy 1.6.0 (32-bit, archlinux latest build), 200000 inserts
>
> Psycopg2ctIntegerInsert.test_insert took 14.11s
> .Psycopg2ctIntegerSelect.test_select took 3.18s
> .Psycopg2ctVariableDataInsert.test_insert took 29.36s
> .Psycopg2ctVariableDataSelect.test_select took 7.78s
>
> .PyPQIntegerInsertTest.test_insert took 25.91s
> .PyPQIntegerSelectTest.test_select took 1.92s
> .PyPQVariableDataInsertTest.test_insert took 30.31s
> .PyPQVariableDataSelectTest.test_select took 8.73s
>
> On 10/05/2011 01:38 AM, Maciej Fijalkowski wrote:
>>
>> On Tue, Oct 4, 2011 at 4:51 PM, Igor Katson<igor.katson at gmail.com>  wrote:
>>>
>>> Hi, Dan,
>>> before answering I'll describe the situation a bit.
>>>
>>> there was a question today, if I know about pg8000 or psycopg2ct.
>>>
>>> As for pg8000, pure python should be slower than ctypes, anyway, so I
>>> don't
>>> think these two should be compared.
>>
>> [citation needed]
>
>

Great data!

It probably does make sense to run each of pypy tests twice to see how
much time is spent warming up the JIT, although definitely the data is
very interesting already.

Cheers,
fijal