%Cached using onstat -p vs %Cached using onstat -g buf

I am on Informix 11.70, and I am grabbing metrics to record. One of them is the buffer write% for each server. When running onstat -p, it shows the %cached as 90.60 . However when actually calculating the %cached, it yields 80.20. The 80.20 value aligns with the value from onstat -g buf. I was wondering why these are different, and how I can actually capture the 90.60 value? My current sql statement to grab this metric is through this pseudocode: dskwrites = select value from sysprofile where name = "dskwrites"; bufwrites = select value from sysprofile where name = "bufwrites"; then I calculate these values as: %cached = (bufwrites - dskwrites) / bufwrites I would like to capture the %cached as 90.60, but I am not sure how the system calculates this value. ------------------------------ Connor Theising ------------------------------ #Informix

Connor: Your formula for calculating off the sysprofile numbers is correct, either: (bufwrites - dskwrites)/bufwrites * 100 or (1.0-(dskwrites/bufwrites)) * 100 You may be running into an old v11 bug in onstat. Your release has been out-of-support for several years, and v12.10 may be going OOS soon as well. You really should upgrade to Informix v14.10FC8. The numbers look right on my v14.10 system. Note that v14.10 is noticeably faster than 11.70, improves replication by 5X over v12.10, and adds many new features including getting compression included (it was an extra cost option in v11.xx and 12.10) and support for JSON/BSON data working seamlessly with relational data and the MongoDB client API (which will make your developers happy and save you the effort of having to convert everything to MongoDB). Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

I'm yet having to understand what a write cache rate would or could be or mean - in Informix. If I'd consider a file system or disk cache, it makes sense to say X% of my writes, to a file or a device, went to that cache vs. (100-X)% I had to wait on real disk i/o for (because e.g. there was no space left in the cache?) But with Informix buffer cache, we'd first have to determine what a write even is: a modification of a page in a buffer. So, from this perspective, it always is 'cached'. These 'writes' are counted in bufwrites. Then there's also writing of modified pages down to disk: in a foreground write, so by a user sessions, yet not during a write/modify operation, but during a read in need of a free buffer in an LRU write, so in background by a flusher in a chunk write, by the checkpoint (so a flusher too) All those writes would be counted in those dskwrites counters. So any ratio you'd construct between any of these would never mean anything like 'so many of my inserts/updates/deletes/other modifications had (not) to go to disk'. In fact the only 'having to go to disk' during such operation might be for the initial read access, so blurring the picture even more. There are so called physical write operations where a page gets modified, in bufferpool, and then immediately written to disk, in the course of this operation, so there such 'cached' calculation would at least make a little sense (with cache rate always being 0). But those operations don't occur during regular SQL user activity, a good example would be a physical restore. What a "(bufwrites - dskwrites) / bufwrites" cache rate would indicate, I'm not able to see, but that's indeed what's displayed. What might make some sense is its complement, the plain bufwrites/dskwrites ratio, as a vague indication of "my instance needed so many (or few) disk writes for this many (buffer) modifications", yet this, contrary to read cache rates, has no implication on session/SQL performance nor can it be influenced through configuration (apart maybe through checkpoint frequency and LRU configuration.) Maybe not exactly what you were hoping for, sorry ;-) And yes, I'd love to stand corrected and understand the true value of this metric. BR, Andreas ------------------------------ Andreas Legner ------------------------------

Andreas: The write cache percent has little effect on server performance. It is a holdover from Informix v4 & v5 where writes went almost immediately to disk. You are correct that it does not represent, as it would for a filesystem cache, the percent of writes that targeted data already in the cache. In Informix, as in most RDBMS and indeed most well built database systems, all data is written to data already in the buffer cache. What write cache means now is, as you inferred when you propose an inverse write cache hit to calculate the average pages per disk write, how efficient the writes and the cache are. A low write cache percentage, and the corresponding low pages per disk write, can indicate that flushes are happening too frequently so that dirty pages are being cleaned and dirtied again quickly. A low percentage of writes are to already dirty pages so too much write IO. This can result from setting the lru_min/max_dirty sub-parameters too low for the server's transaction rate. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Hi Connor, Your formula is: %cached = (bufwrites - dskwrites) / bufwrites I would use: %cached = bufwrites / (bufwrites + dskwrites) and the same with reads: %cached = bufreads / (bufreads + dskreads) Try it. -- Cordialement, Regards, Khaled Bentebal Directeur G??n??ral - ConsultiX T??l: 33 (0) 1 39 12 18 00 Mobile: 33 (0) 6 07 78 41 97 Email: khaled.bentebal@consult-ix.fr Site Web: www.consult-ix.fr