On Friday, February 22, 2013, Carlo Stonebanks wrote:
Hi Jeff, thanks for the reply.
<<
What is going on during the interregnum? Whatever it is, it seems to be driving the log_2013_01_session_idx index out of the cache, but not the log_2013_01 table. (Or perhaps the table visit is getting the benefit of effective_io_concurrency?)
…
Rebuilding the index might help, as it would put all the leaf pages holding values for session_id=27 adjacent to each other, so they would read from disk faster. But with a name like "session_id", I don't know how long such clustering would last though.
>>
Technically, nothing should be happening. We used to keep one massive audit log, and was impossible to manage due to its size. We then changed to a strategy where every month a new audit log would be spawned, and since log_2013_01 represents January, the log should be closed and nothing should have changed (it is technically possible that a long-running process would spill over into February, but not by this much). So, assuming that it’s stable, it should be a very good candidate for reindexing, no?
Yes, assuming the problem is reading the index data from disk, that sounds like a good candidate for reindexing (and maybe clustering as well).
Our effective_io_concurrency is 1, and last I heard the PG host was a LINUX 4 drive RAID10, so I don’t know if there is any benefit to raising this number – and if there was any benfit, it would be to the Bitmap Scan, and the problem is the data building before the fact.
>> the bitmap itself doesn't get cached. But the data needed to construct the bitmap does get cached. It gets cached by the generic caching methods of PG and the OS, not through something specific to bitmaps.
<<
This has always been a problem for me. I spend hours trying different strategies and think I’ve solved the problem, when in fact it seems like a cache has spun up, and then something else expires it and the problem is back. Is there a way around this problem, can I force the expiration of a cache?
You can clear the PG cache by restarting the instance. To clear the OS cache as well you can do this (Linux)
<stop postgres>
sync
sudo echo 3 > /proc/sys/vm/drop_caches
<start postgres>
But I think it would be better just not to execute the same query repeatedly. For example, each time you execute it during testing, pick a different session_id rather than using 27 repeatedly. (It might also be a good idea to change up the hard-coded in-list values you have, but with the plans you are currently seeing that isn't important as those are being used in a filter not a look-up)
Cheers,
Jeff