FWIW, MySQL's InnoDB Storage Engine switched a few years ago from an LRU to something similar to what is described as the final solution here.
The LRU is split to a hot (5/8ths) and cold (3/8ths) chain. Scans can only evict contents of the cold chain, and optionally you can specify a minimum amount of time (5.6 default: 1000ms) before a page can be promoted to the hot chain.
2Q is clever, but certainly not the only improvement over LRU for page caching. Of interest, also, is LIRS, which uses the timing between the last two accesses to decide on a caching strategy.
For practical purposes, the difference between various non-LRU algorithms are workload dependent. They all work better than LRU generally. It is always possible to contrive a pathological access pattern for a given algorithm. These more sophisticated cache replace algorithms attempt to minimize the cross-section of realistic workloads that break the algorithm. 2Q style cache replacement is not the most robust of these but it is simple to implement and understand.
One thing to be aware of when selecting these algorithms is that many of them were designed for the assumptions of much older computer systems. For systems with large memory and a low cache miss rate (most modern ones), the CPU cache friendliness of the cache replacement algorithm can have a significant impact on practical performance. This is, for example, one of the advantages of clock-style algorithms.
In a lot of good database engines, these basic algorithms are extended so that individual execution contexts can annotate the cache with their opinion on the disposition of the page. One thread may think a page is stone cold, another may think it is hot. All of this metadata is reduced to an adaptive determination of whether or not a page should be evicted at a particular point in time. It is robust and fast across workloads but is relatively complicated to implement and partially exposes the underlying implementation to the rest of the system.
The LRU is split to a hot (5/8ths) and cold (3/8ths) chain. Scans can only evict contents of the cold chain, and optionally you can specify a minimum amount of time (5.6 default: 1000ms) before a page can be promoted to the hot chain.
More details: http://dev.mysql.com/doc/refman/5.6/en/innodb-performance-mi...