Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need to aggregate incoming data and save the resulting aggregates to a table. A sample of the data is below.

id,time
1967,2014-04-12 00:42:55+00
1967,2014-04-12 00:42:51+00
1972,2014-04-12 00:42:51+00
1972,2014-04-12 00:42:49+00
1972,2014-04-12 00:42:40+00

I need to do a simple aggregation:

SELECT id, MIN(time),MAX(time),COUNT(id) FROM xx GROUP BY id

I've tried a few things:

Directly updating a table each time a piece of data comes in. Was too slow of course.

Saving the incoming data to a temporary table and periodically running an aggregation query similar to the one above and then saving the result to a table. This approach was better but proved too much for my disks as volume increased.

My current approach is a daemon program that reads incoming data, aggregates it in memory and then saves the results to a table based on some time criteria. This approach works much better but still cannot scale as much as I'd like.

I am using Postgres 9.1 on a 4-disk RAID 10 system with 15k SCSI drives and 32gb of RAM. There is no disk partition, WAL and everything is on one disk. I realize this is a large performance hit. Hardware changes are problematic at this point. This system cannot keep up with what I need to do. At the moment I need to write thousands of these aggregates per second and the solution I'll choose has to scale to much more.

I have been exploring distributed database options and I like the ideas behind Cassandra. However I am not up to speed on all the options as I'm primarily a software developer. Moving to a distributed database in the cloud seems like a good option for a next step. Being able to sort by the min,max and count columns would be ideal but I can do that in the application layer if I have to.

Another important note is that the result of these data aggregations are actually rarely needed and are only for human consumption. A tiny fraction of these will actually be viewed. I tried generating the aggregations realtime (by querying a table that has the raw data) but they ended up with sequential scans on large tables (100m+ rows). If aggregating this much data and having most of it not seen is nonsensical I'd be happy to hear of a way to generate them upon request. My SQL skills are basic it is possible I am missing techniques that will help.

Here's some relevant parts of my postgres config:

shared_buffers = 22000MB            
temp_buffers = 256MB            
work_mem = 896MB                
maintenance_work_mem = 16MB     
fsync = off             
wal_buffers = 64MB          
checkpoint_segments = 128       
checkpoint_timeout = 60min      
checkpoint_completion_target = 0.9  
random_page_cost = 2.0          
effective_cache_size = 8192MB
default_statistics_target = 200 
autovacuum_max_workers = 6      
autovacuum_naptime = 15s        
autovacuum_vacuum_threshold = 25    
autovacuum_analyze_threshold = 10   
autovacuum_vacuum_scale_factor = 0.1    
autovacuum_analyze_scale_factor = 0.05  
autovacuum_vacuum_cost_delay = 10ms 
autovacuum_vacuum_cost_limit = 1000 

So my questions:

How is my postgres config for these tasks? What kind of limits for this task am I looking at with my current hardware? Are there distributed databases well suited to my needs?

share|improve this question

2 Answers 2

up vote 1 down vote accepted

I would write the raw data to a very basic table without indexes or constraints, not even a primary key.

If you can, insert many rows at once, that's faster than single-row inserts.

If you can afford loosing some data in a catastrophic event, make that an unlogged table, that's faster.

If you can afford loosing some data in a catastrophic event and all inserts can run in a single session, make that a temporary table, that's even faster. Probably not possible, though.

Start a new partition every time unit of your choice (daily?) and aggregate the old partition into permanent storage at some opportune moment.

share|improve this answer
    
Thanks for the tips. I've tried these sorts of things and I couldn't get what I needed. I ended up going with a redis cache that's populated on demand and updated as new data comes in. –  jjames May 20 at 20:41
    
After redis was successful I tried unlogged tables in postgres and they worked as well. This data can actually be regenerated in case of server failure. –  jjames Jul 12 at 0:05

A few notes about the Postgres config :

Shared_buffers can be set higher on PostgreSQL 9.3 without having to recompile the kernel. Did you have to recompile the kernel to set your setting this high? Is it safe to take almost all of the RAM for shared_buffers if your work_mem is already set this high? The setting value gets multiplied depending on the number of users and tables being worked on. 8 tables 8 tables = 16X the amount specified or something close to that. With this set to 2/3 of your RAM, I'm worried this would baloon way beyond what it was intended to be. A wal_buffers of 1MB seems to be the standard on large systems. Are you sure 64MB isn't impacting perfs at this point? effective_cache_size should be set to 2/3 of your available RAM. 22000 MB. 8 GB limits the total amount of RAM Postgres can use.

No matter what the settings are, the limit will always be the speed of your disks and the intelligence of your software layer above. Aggregating in RAM as much as possible and dumping to an unlogged temp table as suggested is probably the fastest way. I'd dump the mechanical disks and switch to a PCI-e SSD card. Access times on newer SSDs are in the nanoseconds vs ms on mechanical disks. Transfer rates on RAID SSDs embedded on PCI-e cards can reach multi-gigabyte per second vs 100-300MB/s range on SCSI. I wouldn't consider anything less for such a demanding task.

As for distributed DBs, I doubt you'll be able to ever aggregate faster considering you seem to have a single source of data and a single destination. If you have multiple clients all sending thousands of IDs per second then the cloud could help mitigate geographic disparity and interconnect delays, but I wouldn't count on it. I'd contact Amazon directly and give them the baby. At this level of perfs nobody else will have enough backbone or tech resource to help you.

Just my 2c.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.