On 11/28/2012 06:57 AM, Niels Kristian Schjødt wrote: Before I go crazy, here... you really need to tell us what "not enough" means. You didn't provide an explain analyze, so we don't know what your actual performance is. But I have my suspicions.
So as you can see, it's already pretty optimized, it's just not enough :-) So what can I do? the two columns last_observed_at and data_source_id has an index, and it is needed elsewhere, so I can't delete those.
Ok, so part of your problem is that you're tying an advertising system directly to the database for direct updates. That's a big no-no. Any time you got a huge influx of views, there would be a logjam. You need to decouple this so you can use a second tool to load the database in larger batches. You'll get much higher throughput this way. If you absolutely must use this approach, you're going to have to beef up your hardware.
PS. I'm on postgres 9.2 on a server with 32gb ram, 8 cores and two 3T disks in a software raid 1 setup.
This is not sufficient for a high-bandwidth stream of updates. Not even close. Even if those 3T disks are 7200 RPM, and even in RAID-1, you're going to have major problems with concurrent reads and writes. You need to do several things: 1. Move your transaction logs (pg_xlog) to another pair of disks entirely. Do not put these on the same disks as your data if you need high write throughput. 2. Get a better disk architecture. You need 10k, or 15k RPM disks. Starting with 6 or more of them in a RAID-10 would be a good beginning. You never told us your postgresql.conf settings, so I'm just going with very generic advice. Essentially, you're expecting too much for too little. That machine would have been low-spec three years ago, and unsuited to database use simply due to the 2-disk RAID.
Is the only way out of this really a SSD disk?
No. There are many, many steps you can and should take before going this route. You need to know the problem you're solving before making potentially expensive hardware decisions. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 sthomas@xxxxxxxxxxxxxxxx ______________________________________________ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
