All:
We have a web-application which is growing ... fast. We're currently
running on (1) quad-core Xeon 2.0Ghz with a RAID-1 setup, and 8GB of RAM.
Our application collects a lot of sensor data, which means that we have 1
table which has about 8 million rows, and we're adding about 2.5 million
rows per month.
The problem is, this next year we're anticipating significant growth,
where we may be adding more like 20 million rows per month (roughly 15GB
of data).
A row of data might have:
The system identifier (int)
Date/Time read (timestamp)
Sensor identifier (int)
Data Type (int)
Data Value (double)
The nasty part of this problem is that the data needs to be "readily"
available for reports, and we cannot consolidate the data for reporting
purposes.
We generate real time graphs from this data, usually running reports
across multiple date/time ranges for any given system. Reports and graphs
do not span more than 1 system, and we have indexes on the applicable
columns.
I know we need a LOT of RAM (as much as we can afford), and we're looking
at a couple of Nehalem systems w/ a large, and fast, RAID-10 disk set up.
So far, we're seeing some slowness in reading from our table - queries are
in the "seconds" range. No issues, yet, with inserting volumes of data.
Two questions:
1. Other than partitioning (by system, and/or date), and splitting up the
data into multiple tables (by data type), what could be done within
Postgresql to help with this type of set up (1 large table)?
2. Before going out and buying a beast of a system, we'd like to get some
idea of performance on a "high-end" system. We may need to split this up,
or move into some other type of architecture. Do you know of anyone who
would let us "play" with a couple of systems to see what would be an
applicable purchase?
Thanks!
--
Anthony
