Re: Not using Physical Log, why?

First, I don't know why you did not see any physical log pages being written during the updates. My own understanding is that you should have seen activity in the physical log. Is it possible that a checkpoint ran between the beginning of the transaction and the time that you checked the physical log? In that case the checkpoint would have cleared out the physical log since its pages are no longer needed once a checkpoint completes. Next, know that Informix does not use the physical log for rollback operations, only the logical log is used for the rollbck itself. However, as you have seen, during the rollback, preimages of pages being modified by the rollback are copied to the physical log in case the server crashes in the middle of the rollback. Physical log pages are used during fast recovery, after a crash, to slap down a clean page in case there are pages that were partially written during the crash. This gives the logical log replay a clean slate to write to. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) 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 Sat, Dec 4, 2010 at 7:05 PM, Cesar Inacio Martins < cesar_inacio_martins@yahoo.com.br> wrote: > Hi , > > I already identify this situation on v11.10 xC1 and since then I not > found any answer. > I just forgot about this and today the situation "popup" and again I not > found the reason.... > > v11.70 UC1DE - OpenSuse 11.2 > > This is a very simple test environment. > - Database with log > - 1 table with +- 520.000 records , rowsize 1320, 1 PK > - update executed over 1 field ( NCHAR(100) ), single transaction (begin > work) , changing 100.000 records > - No physical log is used > - Only logical log is filled > > If the transaction is rolled back, then the physical log is used.. > appear to be filled by the recover from logical log, and monitoring with > onstat -D , I can see the pages writes on the physical log and the> dbspace used by table grow up with exactly same values... > > Looking the Logical Log content (onlog -n # -l ) they used HUPDAT > record, what have the before/after image. > I already looking into the manual about this situation and not found. > Can someone explain why the engine choose this way ? > > Regards > Cesar > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636c5a28c13fa2804969edc27

Hi Art, Thanks for your answer. No, unfortunately don't occur any checkpoint... About using the physical log on rollback , ok! thanks for the answer... Now, my doubt about not using the physical log during the transaction activity... still the question. Check bellow: ## Before run any statement , I run: $ onmode -c $ onstat -z IBM Informix Dynamic Server Version 11.70.UC1DE -- On-Line -- Up 04:40:40 -- 224892 Kbytes ## I open a connection and execute the update ## without include any commit/rollback... keeping the transaction open. $ sqlcmd -vBH -d myfs_db 2010-12-04 23:30:29+ CONNECT TO 'myfs_db' WITH CONCURRENT TRANSACTIONS (Warnings: database has transactions, database is not SE) Time: 0.036272 SQL[292]: SQL[292]: begin work; 2010-12-04 23:31:12 + begin work; BEGIN WORK: Rows processed = 0 Time: 0.001978 SQL[293]: update fs_full set nome_arquivo = upper(nome_arquivo) where sequence <= 100000 ; 2010-12-04 23:31:22 + update fs_full set nome_arquivo = upper(nome_arquivo) where sequence <= 100000 ; UPDATE...WHERE: Rows processed = 100000, Last ROWID = 8538881 Time: 128.892188 SQL[294]: ## Other session, I keep running the command bellow ## there is only a inexpressive modification into physical log over all transaction. $ onstat -l -r |egrep -A1 "phyused|Dynamic" .... IBM Informix Dynamic Server Version 11.70.UC1DE -- On-Line -- Up 04:42:12 -- 224892 Kbytes -- phybegin physize phypos phyused %used 3:53 100000 32159 16 0.02 -- ... ## When the update finish, this is the output of the pg reads/writes $ onstat -D ....Chunks address chunk/dbs offset page Rd page Wr pathname 48747988 1 1 0 0 0 /ifmxdados/L_rootdbs.ch1 4a4d02f8 2 2 0 0 5627 /ifmxdados/L_llog.ch1 4a4d04e0 3 3 0 0 18 /ifmxdados/L_plog.ch1 4a4d06c8 4 4 0 0 0 /ifmxdados/L_temp1.ch1 4a4d08b0 5 5 0 0 0 /ifmxdados/L_temp2.ch1 4a4d0a98 6 6 0 5 0 /ifmxdados/L_admin.ch1 4a4d0c80 7 7 0 0 0 /ifmxdados/L_er_cdr.ch1 4a4d1018 8 8 0 0 0 /ifmxdados/L_smart_er1.ch1 4a4d1200 9 9 0 0 0 /ifmxdados/L_smart1.ch1 4a4d13e8 10 10 0 0 0 /ifmxdados/L_smart_def1.ch1 4a4d15d0 11 11 0 0 0 /ifmxdados/L_smart_temp1.ch1 4a4d17b8 12 12 0 0 0 /ifmxdados/L_smart_bts1.ch1 4a4d19a0 13 13 0 0 0 /ifmxdados/L_simple1.ch1 4a4d1b88 14 14 0 347200 64542 /ifmxdados/L_dados1_4k.ch1 4a4d1d70 15 15 0 0 0 /ifmxdados/L_dados2_16k.ch1 15 active, 32766 maximum ## No checkpoints $ onstat -m ....12/04/10 23:30:47 Maximum server connections 3 12/04/10 23:30:47 Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 0, Plog used 7, Llog used 2 12/04/10 23:31:27 Logical Log 413 Complete, timestamp: 0x10040f0. 12/04/10 23:32:01 Logical Log 414 Complete, timestamp: 0x100ee91. 12/04/10 23:32:39 Logical Log 415 Complete, timestamp: 0x101a95a. ## This is my transaction: $ onstat -xaddress flags userthread locks begin_logpos current logpos isol rb_time retrys coord ... 48808f60 A-B-- 487dd160 100002 413:0x816018 416:0xc6450 COMMIT 0:05 0 ## Now, cleaning again the statistics and starting the rollback: $ onstat -z IBM Informix Dynamic Server Version 11.70.UC1DE -- On-Line -- Up 04:49:22 -- 224892 Kbytes ## Starting rolling back: SQL[294]: rollback; 2010-12-04 23:40:03 + rollback; ROLLBACK WORK: Rows processed = 0 Time: 150.358115 ## This is what I see into Physical Log during the rollback: IBM Informix Dynamic Server Version 11.70.UC1DE -- On-Line -- Up 04:50:15 -- 224892 Kbytes -- phybegin physize phypos phyused %used 3:53 100000 43293 11149 11.15 -- IBM Informix Dynamic Server Version 11.70.UC1DE -- On-Line -- Up 04:50:20 -- 224892 Kbytes -- phybegin physize phypos phyused %used 3:53 100000 45315 13175 13.18 -- IBM Informix Dynamic Server Version 11.70.UC1DE -- On-Line -- Up 04:50:25 -- 224892 Kbytes -- phybegin physize phypos phyused %used 3:53 100000 47337 15231 15.23 ## The transaction rolling back: $ onstat -x IBM Informix Dynamic Server Version 11.70.UC1DE -- On-Line -- Up 04:51:43 -- 224892 KbytesTransactions est. address flags userthread locks begin_logpos current logpos isol rb_time retrys coord ... 48808f60 A-R-- 487dd160 100002 413:0x816018 414:0x3dd044 COMMIT 0:01 0 23 active, 128 total, 0 maximum concurrent ## And finally the read/write statistics $ onstat -D -r ...Chunks address chunk/dbs offset page Rd page Wr pathname 48747988 1 1 0 1 7 /ifmxdados/L_rootdbs.ch1 4a4d02f8 2 2 0 5623 1810 /ifmxdados/L_llog.ch1 4a4d04e0 3 3 0 0 66617 /ifmxdados/L_plog.ch1 4a4d06c8 4 4 0 0 0 /ifmxdados/L_temp1.ch1 4a4d08b0 5 5 0 0 0 /ifmxdados/L_temp2.ch1 4a4d0a98 6 6 0 0 0 /ifmxdados/L_admin.ch1 4a4d0c80 7 7 0 0 0 /ifmxdados/L_er_cdr.ch1 4a4d1018 8 8 0 0 0 /ifmxdados/L_smart_er1.ch1 4a4d1200 9 9 0 0 0 /ifmxdados/L_smart1.ch1 4a4d13e8 10 10 0 0 0 /ifmxdados/L_smart_def1.ch1 4a4d15d0 11 11 0 0 0 /ifmxdados/L_smart_temp1.ch1 4a4d17b8 12 12 0 0 0 /ifmxdados/L_smart_bts1.ch1 4a4d19a0 13 13 0 0 0 /ifmxdados/L_simple1.ch1 4a4d1b88 14 14 0 64538 64614 /ifmxdados/L_dados1_4k.ch1 4a4d1d70 15 15 0 0 0 /ifmxdados/L_dados2_16k.ch1 15 active, 32766 maximum On 12/04/2010 10:29 PM, Art Kagel wrote: > First, I don't know why you did not see any physical log pages being written > during the updates. My own understanding is that you should have seen > activity in the physical log. Is it possible that a checkpoint ran between > the beginning of the transaction and the time that you checked the physical > log? In that case the checkpoint would have cleared out the physical log > since its pages are no longer needed once a checkpoint completes. > > Next, know that Informix does not use the physical log for rollback > operations, only the logical log is used for the rollbck itself. However, > as you have seen, during the rollback, preimages of pages being modified by > the rollback are copied to the physical log in case the server crashes in > the middle of the rollback. > > Physical log pages are used during fast recovery, after a crash, to slap > down a clean page in case there are pages that were partially written during > the crash. This gives the logical log replay a clean slate to write to. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > IIUG Board of Directors (art@iiug.org) > 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 Sat, Dec 4, 2010 at 7:05 PM, Cesar Inacio Martins< > cesar_inacio_martins@yahoo.com.br> wrote: > >> Hi , >> >> I already identify this situation on v11.10 xC1 and si