Re: big transaction slows down over time - but disk seems

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

 



Ben wrote:
My transaction calls the same stored procedure many times over. Over the lifetime of the transaction, that stored procedure slows down by roughly 2 orders of magnitude. The procedure itself tries to look up several strings in dictionary tables, and if the strings aren't there (most of them will be) it inserts them. All those dictionary tables have indexes. After it has converted most of the strings into ids, it does another lookup on a table and if it finds a matching row (should be the common case) it updates a timestamp column of that row; otherwise, it inserts a new row.

Which would suggest Heikki's guess was pretty much right and it's dead rows that are causing the problem.

Assuming most updates are to this timestamp, could you try a test case that does everything *except* update the timestamp. If that runs blazingly fast then we've found the problem.

If that is the problem, there's two areas to look at:
1. Avoid updating the same timestamp more than once (if that's happening)
2. Update timestamps in one go at the end of the transaction (perhaps by loading updates into a temp table).
3. Split the transaction in smaller chunks of activity.

So.... there isn't much table size changing, but there are a lot of updates. Based on pg_stat_user_tables I suspect that the procedure is using indexes more than table scans. Is there a better way to know?

Not really. You can check the plans of queries within the function, but there's no way to capture query plans of running functions.

--
  Richard Huxton
  Archonet Ltd


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux