On Fri, Nov 6, 2009 at 9:57 AM, Michael Bayer <mike...@zzzcomputing.com> wrote:
>> Before I even posted I resorted to strace. strace immediately
>> confirmed my suspicion - when using psycopg2 I don't see one big fat
>> INSERT with lots of binds, I see one INSERT per bind, and it's this
>> that is ultimately killing the performance. You can easily observe
>> this via strace: as I'm sure you know, the communication between the
>> test program and postgresql takes place across a socket (unix domain
>> or tcp/ip). For every single set of bind params, the result is
>> essentially one sendto (INSERT INTO ....) and rt_sigprocmask, a poll,
>> and then a recvfrom and rt_sigprocmask pair. Profiling at the C level
>> shows that sendto accounts for *35%* of the total runtime and recvfrom
>> a healthy 15%. It's this enormous overhead for every single bind param
>> that's killing the performance.
>
> have you asked about this on the psycopg2 mailing list ? its at
> http://mail.python.org/mailman/listinfo/python-list
> . Let me know if you do, because I'll get out the popcorn... :)
That's the python list.
Anyway, I did some more testing. executemany performance is not any
better than looping over execute, because that's all that executemany
appears to do in any case.
However, I manually built a bit fat set of bind params (bypassing
sqlalchemy directly) and got a SUBSTANTIAL performance improvement.
Postgresql as of 8.2 supports /sets/ of bind params, it'd be nice if
pg8000 or psycopg2 (or both) supported that. Building 25000 bind
params by hand is not fun, but it got me to just shy of 50K
inserts/second.
> We also support the pg8000 DBAPI in 0.6. I doubt its doing something
> differently here but feel free to connect with postgresql+pg8000://
> and see what you get.
I tried pg8000 but I got an error:
...
return self.dbapi.connect(*cargs, **cparams)
sqlalchemy.exc.DBAPIError: (TypeError) connect() takes at least 1
non-keyword argument (0 given) None None
--
Jon
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
