1

I have a several process that aggregate some statistics on items (how much was shown, clicked ... around 30 factors). Once in a 5 minutes i flush the aggregate data to postgres (9.1). I have 250K aggregate items with statistics. tabe key: item_id,process_id (in order to avoid locking process-id is part of the key),channel,(more 2 fields).

The process is like this: open transaction try to update (most of time this item already exist in db, so update success). update is done like this - update statistics set counter_a= counter_a+1... where id = x and channel = y and ...; insert in case update failed. commit

This take 15 minutes for 250K updates. Any advices? I saw this question : Optimizing bulk update performance in PostgreSQL But the case there is not relevant.

I cant drop indexes because i need them for the update. Any way to avoid the overhead of the mvvc when i know that this is the only process that will write the data and no need in isolation or even transaction in case of failure.

db machine run on "hi1.4xlarge" amazon instance (64G RAM , 8 cores).

Community
  • 1
Avihai Marchiano
  • 231
  • 2
  • 6
  • No, you can't disable MVCC even if you think you don't need it, because it's required for transactional updates. Without it, if there was an error/crash/disconnect partway through an update the system couldn't rollback the transaction and you'd have a partially completed update. – Craig Ringer Aug 10 '14 at 00:31
  • Re the High I/O instance: your database is on the instance store right? With replication for backup? Because the high I/O instances offer no benefit (just cost more) if you're using EBS. – Craig Ringer Aug 10 '14 at 00:46
  • Anyway ... you're doing tons of small upserts, which is a known horrible case for performance. Consider seeing if you can do batch upserts with table locking instead: http://stackoverflow.com/questions/17267417/how-do-i-do-an-upsert-merge-insert-on-duplicate-update-in-postgresql – Craig Ringer Aug 10 '14 at 00:52
  • Does it improve if you set synchronous_commit to off? (But be aware of the consequences of doing so) – jjanes Aug 19 '14 at 15:03

0 Answers0