Logical Logs/Physical logs

IDS 12.10.FC12 Solaris 10 1/13 If you had to choose having a separate dbspace for logical logs or physical logs, but not both, which would you choose? I'm uncertain which is likely to generate more I/O. Thank you. Regards, DG ------------------------------ David Grove ------------------------------ #Informix

David: By definition, the logical logs will tend to get more IO than the physical logs. There are exceptions such as when many smallish rows live on a page but only one row on each of many pages are being frequently updated. Then there will be one PL page for each LL entry and the former will be larger. But that's an edge condition that is rare in practice. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.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 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.

Thank you, Art. I'm setting up a small test platform, and I was uncertain. I have room for one more disk slice, and thinking maybe I had the right thinking, I actually already set it up as logical log space. But, I was uncertain enough to check with the "big guns" first, and I had left things in an incomplete state so I could change, if I got feedback to the contrary. I think Andrew Ford has also posted some similar thinking in his blog at his site. Thank you. DG ------------------------------ David Grove ------------------------------

David, Definitely Logical logs. Logical logs contains all of the transactions, checkpoint information, and any chunk allocations if needed. If your database or databases are?? not logged, the Logical logs will not generate much IO since there are not transactions logged; in this case the logical logs dbspace will not be very important as far as IO. Most of the time the databases are logged, in this case you need to look at the logging mode (Buffered, unbuffered). The buffered mode generates far less IO in the logs since the IO are done when the log buffer is full and not at commit time (less secure in case of a crash). Small transactions generate a lot of IO. Large transactions generate less logs. I would say: - no logging databases ==> Physical logs should have a priority - buffered logging databases => it depends but I give the priority to the logical logs - unbuffered or ansi mode databases => logical logs should have a priority However, you should be aware that you can have a mixture of databases in the same instance and you can also have differents logging modes (1 database not logged, 1 database in buffered mode , 1 database in unbuffered mode, etc). Since there is one central logical log (spread in several logs),?? in this case the unbuffered database(s) will win since the commits provoke an IO in the log no matter what was in the log buffer. If you have several databases with differents logging mode, you could may be have different instances?? and group databases by logging mode. Overall, most OLTP applications should have a priority for the logical logs instead of the physical logs. Note: DSS databases that do not have transactions do not write in the logical logs if they are only SELECTS or mostly SELECTS. THese databases should be in a separate instance that do not give a priority to the logical logs. So the decision depends on several things that need to be analyzed. -- Khaled Bentebal Mobile: 33 (0) 6 07 78 41 97 Email: khaled.bentebal@consult-ix.fr Site Web: www.consult-ix.fr

why not just monitor the chunk writes to the phys & logi chunks with onstat -D for a definitive period of time ... should know very quickly.

Thank you Khaled and Mark, too. 1) All our databases use unbuffered logging. 2) Indeed, I am curious, so I'll run onstat -D David Grove ------------------------------ David Grove ------------------------------

David, 1/?? onstat -D will only work for you if you?? already have a setup running with a physical log totally by itself?? in a dbspace and you will have to add up the writes for all of the chunks of that dbspace AND another dbspace or dbspaces that include all of the logical logs and no other information shares these dbspaces ( such as other tables). If that is the case, onstat -D will be give you information that will allow you to compare and make your decision. 2/ Otherwise, it will be difficult to?? know which?? consumes mores IO since your physical log could be in the rootdbs or elsewhere and the same for the logical logs that could also be in the rootdbs or spread on different dbspaces. I think that for your case, having all of the databases in unbuffered mode, you will most likely have more IO for the logical logs. So I would give the priority to the logical logs. -- Khaled Bentebal Mobile: 33 (0) 6 07 78 41 97 Email: khaled.bentebal@consult-ix.fr Site Web: www.consult-ix.fr

Thank you, Khaled, for the follow-up. I had not yet refreshed myself on the documentation regarding onstat -D before I responded. I do not have the physical logs in a separate (since that was the essence of my question). Guess that will teach me (yet again!) to read first, before posting. Regards, DG ------------------------------ David Grove ------------------------------

David, The second part of my earlier reply was somehow omitted : onstat -l can also be used. numwrites are number of writes to disk regardless where their respective dbspaces reside.at the end of they day the logical log will be on top of the leader board most likely. onstat -l | head -20 IBM Informix Dynamic Server Version 12.10.FC8 -- On-Line -- Up 17 days 21:47:24 -- 12729152 KbytesPhysical Logging Buffer bufused bufsize numpages numwrits pages/io P-2 28 32 66413 2098 31.66 phybegin physize phypos phyused %used 2:53 2097099 323694 1779 0.08 Logical Logging Buffer bufused bufsize numrecs numpages numwrits recs/pages pages/io L-2 0 16 1029758 76568 59184 13.4 1.3 Subsystem numrecs Log Space used OLDRSAM 1014875 100174364 SBLOB 13406 43302328 CDR 1457 78112 HA 20 880

Thank you, Mark. This is something I should have thought of, as I use onstat -l frequently. A quick check verifies that there are about 3 orders of magnitude difference in writes between logical and physical logs! DG ------------------------------ David Grove ------------------------------