Re: Best Use of Free Memory
On 29 Oct, 16:04, "Art Kagel" <art.ka...@gmail.com> wrote: > On Tue, Oct 28, 2008 at 7:14 PM, da...@smooth1.co.uk <da...@smooth1.co.uk>wrote: > <SNIP> > > > > > As the number of buffers go up the lru chains get longer and so the > > time to find a buffer can increase (if the buffer is further down an > > LRU queue > > at the time). > > David, > > If the engine needs to access a specific page that is already in the buffer > cache, it is not searched for in the LRU queues, there is a hash table that > is used to locate the buffer page that contains the specific disk page so > the number of buffers does NOT affect the time needed to locate data in > memory. The LRU queues are primarily used to decide which existing page in > the cache to overwrite when another page needs to be read in from disk or a > new page created and to quickly locate dirty pages that require cleaning. > > Your points about LRU latching are certainly valid, as is Kevin's reply that > you can sometimes increase (or simply change) the number of LRU queues to > reduce contention for the LRU latches. However, Kevin misses the point that > the number of LRU queues is limited to 128 in 32bit releases and 512 in > 64bit releases which limits ones ability to make that adjustment in busier > environments. > > Art > -- > Art S. Kagel > Oninit (www.oninit.com) > IIUG Board of Directors (a...@iiug.org) > > Disclaimer: Please keep in mind that my own opinions are my own opinions and > do not reflect on my employer, Oninit, the IIUG, nor any other organization > with which I am associated either explicitly or implicitly. Neither do > those opinions reflect those of other individuals affiliated with any entity > with which I am affiliated nor those of the entities themselves. The manual clearly states: ""When a user thread needs to acquire a buffer, the database server randomly selects one of the FLRU queues and uses the oldest or least- recently used entry in the list. If the least-recently used page can be latched, that page is removed from the queue. " so the question then becomes what does "when a user thread needs to acquire a buffer". The buffer has to not be dirty as it is on the FLRU queue. I wish this was better documented. Where is this hidden buffer hash table and how can we give it on contention on it, are there latches for the buffer hash table?
David, I see the confusion now. The English phrase, "Needs to acquire a buffer," is too vague. There are really two major cases, but the manual is only addressing one (the rarer one). The cases are: 1. The required data is already in the buffer pool. In this case the thread needs to get access to the particular buffer that already contains the data. 2. The required data is not already in the buffer pool. In this case the thread needs to get access to a buffer into which that data can be read. This case has two subcases: a. A clean buffer is available. b. No clean buffers are available. The manual entry is really only talking about case 2a. If the working set fits into the buffer pool (which it should if you care about performance), 95% of the time or more the activity of "acquiring a buffer" falls into case 1, i.e. there already exists a buffer that contains the needed data. This is the case that uses hash lookup to hash from the page number directly to the hash chain that contains the needed buffer. I believe the hash chain needs to be latched while searching the chain, but there are normally very few buffers on a hash chain so this should not generate a lot of contention because most concurrent queries will be accessing other hash chains. Case 2 is a page fault. These should happen less than 5% of the time. When a page fault occurs, it starts off in case 2a. It picks a random clean LRU chain and tries to latch it. If the chain latch is not immediately available, it goes to the next chain, etc. When it succeeds in latching a clean chain, it then searches from the least recently used end of that chain for a buffer it can latch. If it finds one, it unlatches the chain, reads the data into that buffer, and updates the corresponding hash chain. If after enough tries it can't acquire a clean buffer (usually because the buffer pool is very dirty), it goes to case 2b and uses a similar approach to acquire a dirty buffer. This search will continue until it succeeds. Then it unlatches the dirty chain, writes the dirty buffer to disk (called a "foreground write"), move the buffer from dirty to clean chain (requiring latching both dirty and clean chains), reads the needed data into the buffer, and finally updates the corresponding hash chain (requiring the hash chain latch). Thus as you can imagine, case 2b is very expensive. Normally you should be able to tune IDS so this case almost never occurs (near-zero foreground writes). There are other cases that require LRU chain latches, at least the following: -- Dirtying a previously clean buffer needs to move it from the clean to the dirty chain (two chain latches). -- Accessing a low-priority buffer needs to relink it higher in the chain to maintain LRUness of the least-recently-used portion of the chain (one chain latch). -- Upgrading a low-priority buffer to high priority requires relinking it to the head of the chain (one chain latch). -- Background flushing of dirty buffers moves buffers from dirty to clean chains (two chain latches). Thus if you have a large number of buffers with a lot of concurrent threads, it helps to increase the number of LRU chains to reduce contention on the chain latches. The default is only 8, which won't scale well to large systems and workloads. If you have millions of buffers and lots of concurrent activity, going to 128 LRUs can help, and is very unlikely to hurt. The performance gain will usually not be as noticeable as adding more buffers when working set does not fit in the buffer pool, though, because the latter reduces disk I/Os. However, unlike the cost of adding more buffers, the cost of adding more chains is very tiny, so you might as well try it. -- Kevin Cherkauer Software Engineer IBM Informix Dynamic Server -- Database Kernel <david@smooth1.co.uk> wrote: The manual clearly states: ""When a user thread needs to acquire a buffer, the database server randomly selects one of the FLRU queues and uses the oldest or least- recently used entry in the list. If the least-recently used page can be latched, that page is removed from the queue. " so the question then becomes what does "when a user thread needs to acquire a buffer". The buffer has to not be dirty as it is on the FLRU queue. I wish this was better documented. Where is this hidden buffer hash table and how can we give it on contention on it, are there latches for the buffer hash table?
On 31 Oct, 00:49, "Kevin Cherkauer" <invalid_addr...@nowhere.com> wrote: > David, > > I see the confusion now. The English phrase, "Needs to acquire a buffer," is > too vague. There are really two major cases, but the manual is only > addressing one (the rarer one). The cases are: > > 1. The required data is already in the buffer pool. In this case the thread > needs to get access to the particular buffer that already contains the data. > > 2. The required data is not already in the buffer pool. In this case the > thread needs to get access to a buffer into which that data can be read. > This case has two subcases: > a. A clean buffer is available. > b. No clean buffers are available. > > The manual entry is really only talking about case 2a. > > If the working set fits into the buffer pool (which it should if you care > about performance), 95% of the time or more the activity of "acquiring a > buffer" falls into case 1, i.e. there already exists a buffer that contains > the needed data. This is the case that uses hash lookup to hash from the > page number directly to the hash chain that contains the needed buffer. I > believe the hash chain needs to be latched while searching the chain, but > there are normally very few buffers on a hash chain so this should not > generate a lot of contention because most concurrent queries will be > accessing other hash chains. > > Case 2 is a page fault. These should happen less than 5% of the time. When a > page fault occurs, it starts off in case 2a. It picks a random clean LRU > chain and tries to latch it. If the chain latch is not immediately > available, it goes to the next chain, etc. When it succeeds in latching a > clean chain, it then searches from the least recently used end of that chain > for a buffer it can latch. If it finds one, it unlatches the chain, reads > the data into that buffer, and updates the corresponding hash chain. > > If after enough tries it can't acquire a clean buffer (usually because the > buffer pool is very dirty), it goes to case 2b and uses a similar approach > to acquire a dirty buffer. This search will continue until it succeeds. Then > it unlatches the dirty chain, writes the dirty buffer to disk (called a > "foreground write"), move the buffer from dirty to clean chain (requiring > latching both dirty and clean chains), reads the needed data into the > buffer, and finally updates the corresponding hash chain (requiring the hash > chain latch). Thus as you can imagine, case 2b is very expensive. Normally > you should be able to tune IDS so this case almost never occurs (near-zero > foreground writes). > > There are other cases that require LRU chain latches, at least the > following: > -- Dirtying a previously clean buffer needs to move it from the clean to the > dirty chain (two chain latches). > -- Accessing a low-priority buffer needs to relink it higher in the chain to > maintain LRUness of the least-recently-used portion of the chain (one chain > latch). > -- Upgrading a low-priority buffer to high priority requires relinking it to > the head of the chain (one chain latch). > -- Background flushing of dirty buffers moves buffers from dirty to clean > chains (two chain latches). > > Thus if you have a large number of buffers with a lot of concurrent threads, > it helps to increase the number of LRU chains to reduce contention on the > chain latches. The default is only 8, which won't scale well to large > systems and workloads. If you have millions of buffers and lots of > concurrent activity, going to 128 LRUs can help, and is very unlikely to > hurt. The performance gain will usually not be as noticeable as adding more > buffers when working set does not fit in the buffer pool, though, because > the latter reduces disk I/Os. However, unlike the cost of adding more > buffers, the cost of adding more chains is very tiny, so you might as well > try it. > > -- > Kevin Cherkauer > Software Engineer > IBM Informix Dynamic Server -- Database Kernel > > <da...@smooth1.co.uk> wrote: > > The manual clearly states: > > ""When a user thread needs to acquire a buffer, the database server > randomly selects one of the FLRU queues and uses the oldest or least- > recently used entry in the list. If the least-recently used page can > be latched, that page is removed from the queue. " > > so the question then becomes what does "when a user thread needs to > acquire a buffer". The buffer has to not be dirty as it is on the FLRU > queue. > > I wish this was better documented. Where is this hidden buffer hash > table and how can we give it on contention on it, are there latches > for the buffer hash table? Well use 127 as IDS 7 has a bug where it reported a message to the online.log if you use 128. With Dell Blades http://configure.us.dell.com/dellstore/config.aspx?c=us&cs=555&l=en&oc=MLB1390&s=biz appearing with 96GB of RAM and 4 socket quad cores as well for 5 grand GB (excluding discounts) this will get interesting. I recommend using DS_NONPDQ_QUERY_MEM to increase the default sort memory per session to more than 128GB (query symaster:syssesprof and try to eliminate disk sorts). Also VP_MEMORY_CACHE_KB to give each VP a private memory cache. Beyond that I guess statement cache, distributions cache, data dictionary cache, UDR cache (all the *SIZE* and *HASH* params) but they will not be large. Then SQLTRACE to get all the statement level states for the last N sql statements. After than I still struggle to see how to use 96GB! I guess PDQ and DS_TOTAL_MEMORY but that can be tricky as you cannot tell in advance how many threads will be spawned, IDS makes up it's own mind for that (think 80 threads for one piece of sql...)!!