Bret,
And then, even if the support is there, you'd need to outline exactly
how you're planning on pushing this button.
Specifically, what's your usage pattern that would make this a
win for you?
Let me explain. I have a very busy application generating thousands
of SQLs per second.
There is an application level cache built into the application already.
The important part is that once per hour the application writes
collected data to huge historical
tables (100M up-to billions of records, partitioned). Since it
happens every hour database buffer
cache is already overwritten by data and indexes of other tables, so
the write operation is very
slow and requires huge amount of disk seeks causing 50-100x drop of
performance.
The disk seeks will happen regardless of what Postgres does, as the
OS pulls in new disk blocks to perform the write. If your OS' buffer
cache is large enough to hold all the data you need, then your
best bet is likely partitioning data across multiple disks, so that
queuing the archive reads doesn't get in the way of production reads.
As I'm a unix admin mostly, I'm not qualified to give advice on whether
or not that's possible, or how to do it if it is ;)
I was talking about read seeks obviously caused by index-related
searches. Write operations
do not cause latency issues as they are handled quite well by OS,
controller, HDD, whatever
write cache.
So, my idea is to assign a separate buffer cache for the historical
tables. It would guarantee that
index data is always cached, so the write operation will be very fast.
Is it possible? Is there any other techniques available?
If it were at all possible, I'd actually set up a secondary archiving
server (unless you need the historical data on tap for the production
system as well), either on another port on the same machine, or on
another machine which won't impact your production system if it has to
suddenly do a bunch of disk I/O, and log the history to that.
I agree. Two separate servers would be a nice solution as usage patterns
are absolutely different,
so the servers can be tuned differently. Lack of transactional integrity
is an obvious drawback of
such approach.
Kind regards,
Alexei
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
