Search Postgresql Archives

Re: UPDATE on two large datasets is very slow

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Mon, 2007-04-02 at 22:24, Steve Gerhardt wrote:
> I've been working for the past few weeks on porting a closed source
> BitTorrent tracker to use PostgreSQL instead of MySQL for storing
> statistical data, but I've run in to a rather large snag. The tracker in
> question buffers its updates to the database, then makes them all at
> once, sending anywhere from 1-3 MiB of query data. With MySQL, this is
> accomplished using the INSERT INTO...ON DUPLICATE KEY UPDATE query,
> which seems to handle the insert/update very quickly; generally it only
> takes about a second for the entire set of new data to be merged.
> 
> The problem I am encountering is that when I attempt to duplicate this
> functionality in Postgres, it is terrifically slow to a point of utter
> unusability. The tracker currently handles around 10,000-40,000 client
> updates per minute, which translates roughly to the same number of rows
> in the database. Part of the issue is that some of those rows cannot be
> updated because they do not yet exist in the database, but there is
> likely around a 100:1 ratio on updates to inserts.
> 
> After consulting with some of the folks on the PostgreSQL IRC channel on
> freenode.net, I was left with this idea to try:

I can't help but think that the way this application writes data is
optimized for MySQL's transactionless table type, where lots of
simultaneous input streams writing at the same time to the same table
would be death.

Can you step back and work on how the app writes out data, so that it
opens a persistent connection, and then sends in the updates one at a
time, committing every couple of seconds while doing so?


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux