Hi Jeff, thanks for the insight.
<< And then the next question would be, once they are in the cache, why don't they stay there? For that you would have to know what other types of activities are going on that might be driving the data out of the cache.
>>
To give you an idea of the activity level, each physical machine hosts multiple DB’s with the same structure – one DB per client.
We run automated ETL processes which digests client feeds (E) normalizes them (T) and then stores them in our DB (L).
Looking at the stats from our audit log, the average feed load is 4 hours, divided up into 14 client sessions. Each session averages about 50 write (update, insert, no deletes) operations per second, representing 700 write operations per second.
Is each of these write operations just covering a single row? Does this description apply to just one of the many (how many?) databases, so that there are really 14*N concurrent sessions?
The ratio of reads per write is pretty high as the system goes through the transformation process.
Since I don’t know how this compares to other PG installations, the question of using periodic REINDEX and CLUSTER brings up these questions:
1) Because we are hosting multiple DB’s, what is the impact on OS and disk caches?
They have to share the RAM. One strategy would be run ETL processes only one at a time, rather than trying to run several concurrently, if that is what you are doing. That way you can concentrate one customers data in RAM, and then another's, to reduce the competition.
2) Is there an automated CLUSTER and REINDEX strategy that will not interfere with normal operations?
3) By PG standards, is this a busy DB - and does explain why the general caches expire?
You really need to know whether those reads and writes are concentrated in a small region (relative to the amount of your RAM), or widely scattered. If you are reading and writing intensively (which you do seem to be doing) but only within a compact region, then it should not drive other data out of the cache. But, since you do seem to have IO problems from cache misses, and you do have a high level of activity, the easy conclusion is that you have too little RAM to hold the working size of your data.
Cheers,
Jeff
