Physical log size

IDS 11.50.fc6 HP-UX 11.31 PA-RISC For one of my instances, the online log file includes the following messages at startup: 19:38:04 Performance Advisory: The physical log size is smaller than the recommended size for a server configured with RTO_SERVER_RESTART. 19:38:04 Results: Fast recovery performance might not be optimal. 19:38:04 Action: For best fast recovery performance when RTO_SERVER_RESTART is enabled, increase the physical log size to at least 8800000 KB. For servers configured with a large buffer pool, this might not be necessary. These messages cause the following questions to come to mind. First, would changing the instance to not use RTO_SERVER_RESTART cause these messages to disappear? If so, then am I correct in assuming that the server would revert to using CKPTINTVL for its checkpointing? Second, fast recovery performance "might not be optimal", but this implies that at least it will not fail. Is that correct? How bad would the performance be (I know - it depends)? Will the server perform checkpoints more frequently because of the smaller-than-optimal physical log? I know that it will start a checkpoint if the physical log crosses a threshhold (75%?), so is this simply telling me that the smaller-than-recommended physical log will cause the server to do checkpoints more frequently than would normally be required to meet a given RTO target? If so, that seems like it would lead to a restart that would take even less time than the RTO target. If that is the case, it seems like fast recovery performance would be even better than what was set in RTO_SERVER_RESTART, which doesn't sound like "might not be optimal". Finally, the last sentence in the last message - "For servers configured with a large buffer pool, this might not be necessary" - what qualifies as a large buffer pool? I know, it depends. But on what does it depend? Large relative to physical log space? Large relative to logical log space (that doesn't seem to make sense)? Large compared to some static value (e.g., anything over 6 GB of buffers)? I know from the note in onconfig.std that PHYSFILE should be (size of buffers) * 1.1, but that note also says "up to about 1 GB)". So, if we have a PHYSFILE of 3 GB, is that OK?

Mark: So, this one is complex and difficult to explain, but I'll do my best to lay out what I understand. The physical log message with RTO_SERVER_RESTART set is telling you that you asked for an N second recovery time and the small physical log is causing more frequent checkpoints than are required to guarantee that recovery time. Yes, that means that actual recovery time is likely to be shorted than you set. The downside is that the more frequent checkpoints might adversely affect server performance, especially if the buffer cache is undersized. That's why the cache size was mentioned. If the cache is large enough that the vast bulk of your active working set of data is already in memory, then the write IO of the checkpoints will not be likely to affect query performance because read IO rates will be very low. So if you have a sufficiently large cache, the smaller physical log and more frequent checkpoints are not a problem. It's the possibility of affecting query performance that prompts the not "optimal" note. Yes, turning off RTO_SERVER_RESTART will get rid of that message and revert to controlling checkpoints using CKPTINTVL, however, you may find a different message if short term transaction rate spikes cause the server to guess that the physical log may reach 75% full before the next scheduled checkpoint. So, what's a large buffer cache? Big enough to keep the entire active working set of data rows and index nodes in memory at all times is the only answer I can give. If you know or can guess how big that is, great. If not, monitoring your server's cache characteristics with the BTR metric, cache hit percentages, and other indicators will let you figure it out, but there are elements of trial and error and of experience involved. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Dec 19, 2012 at 6:49 PM, MARK COLLINS <markc@myfastmail.com> wrote: > IDS 11.50.fc6 > HP-UX 11.31 PA-RISC > > For one of my instances, the online log file includes the following > messages > at startup: > > 19:38:04 Performance Advisory: The physical log size is smaller than the > recommended size for a server configured with RTO_SERVER_RESTART. > 19:38:04 Results: Fast recovery performance might not be optimal. > 19:38:04 Action: For best fast recovery performance when > RTO_SERVER_RESTART is> enabled, increase the physical log size to at least 8800000 KB. For servers > configured with a large buffer pool, this might not be necessary. > > These messages cause the following questions to come to mind. First, would > changing the instance to not use RTO_SERVER_RESTART cause these messages to > disappear? If so, then am I correct in assuming that the server would > revert > to using CKPTINTVL for its checkpointing? > > Second, fast recovery performance "might not be optimal", but this implies > that at least it will not fail. Is that correct? How bad would the > performance > be (I know - it depends)? > > Will the server perform checkpoints more frequently because of the > smaller-than-optimal physical log? I know that it will start a checkpoint > if > the physical log crosses a threshhold (75%?), so is this simply telling me > that the smaller-than-recommended physical log will cause the server to do > checkpoints more frequently than would normally be required to meet a given > RTO target? If so, that seems like it would lead to a restart that would > take > even less time than the RTO target. If that is the case, it seems like fast > recovery performance would be even better than what was set in > RTO_SERVER_RESTART, which doesn't sound like "might not be optimal". > > Finally, the last sentence in the last message - "For servers configured > with > a large buffer pool, this might not be necessary" - what qualifies as a > large > buffer pool? I know, it depends. But on what does it depend? Large > relative to > physical log space? Large relative to logical log space (that doesn't seem > to > make sense)? Large compared to some static value (e.g., anything over 6 GB > of > buffers)? > > I know from the note in onconfig.std that PHYSFILE should be (size of > buffers) > * 1.1, but that note also says "up to about 1 GB)". So, if we have a > PHYSFILE> of 3 GB, is that OK? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8ff1c69842ecf304d13f54a6

Art, Thanks for the info. We do have enough buffer cache to effectively buffer most of what will be active at any point, so it really does come down to monitoring the various checkpoint-related stats. It was just really confusing that the message indicated that it would be fast recovery that might not be optimal. I would have been able to understand if instead it had said that query performance might not be optimal.