Performance impact with logging ON/OFF
I would like to understand the performance impact on a unbuffered logging database for the following items: 1> I would like to create an Index on a very large table for an unffered logging database. Is it advisable to keep the logging ON/OFF? and create the index. 2> I would like to load the very large table(with 100s of mil rows) with HPL job. Is it advisable to keep the logging ON/OFF? while loading the table. In both scenario, Is there any I/O operation to logical log file?. If YES, How intensive these I/O operations are?. Thanks, Subra.
Subra, Good to see you again! My comments would be (and I've not worked with Informix for a while now, so don't take them too seriously): 1. Even if logging is turned off on the database, I believe that DDL operations are logged anyway, so for the index creation, I don't believe that having logging on or off has any effect at all on the speed of the index creation. There are a number of things that you can do to speed up index creation if you can take the engine down and tune parameters; I can look those up if you need me to. 2. Regarding the HPL load, if you're using a noconvert load from an existing table, and using the express mode, I believe that also bypasses logical logging. As a result, it shouldn't matter much whether you have the logging on the database on or off. If you're doing a deluxe mode load (to check integrity, or fire triggers, or some other operation), then it'll matter a great deal. I'm sure that others on the list will have opinions/comments, but those might get the discussion started. Good to see you again, and glad that you're working with Informix again... <G> Dan Michaelis Database Administrator/Developer eOriginal 351 West Camden Street Suite 800 Baltimore, MD 21201 410.625.5187 (phone) 410.659.9799 (fax) -----Original Message----- From: SENGODAN SU.... [mailto:sengodan.subramanian@verizon.com] Sent: Thursday, March 10, 2005 10:48 AM To: ids@iiug.org Subject: Performance impact with logging ON/OFF [4467] I would like to understand the performance impact on a unbuffered logging database for the following items: 1> I would like to create an Index on a very large table for an unffered logging database. Is it advisable to keep the logging ON/OFF? and create the index. 2> I would like to load the very large table(with 100s of mil rows) with HPL job. Is it advisable to keep the logging ON/OFF? while loading the table. In both scenario, Is there any I/O operation to logical log file?. If YES, How intensive these I/O operations are?. Thanks, Subra.
Thanks Michaelis, I am really happy and glad to see your mail. Yes, I am back into informix business. Pretty much taking care of Ketan's DBA work as Dev - DBA along with some development responsibility. A year back, I had moved to development team, and worked on few design and architecture items. Then we had x2 x 10 head count cuts due to funding issue. Ketan was victim of it. However, Ketan still working for Verizon, but different team. I am glad that he got the job on same day, when he lost it. Hope you are doing good. Good to hear from you. Subra, Sengodan Subramanian, iView - IPT Reporting service. Office Phone: (813) 978-6774 Cell: (813) 404-7331 => "PROPER PREPARATION PREVENTS POOR PERFORMANCE" => "WHY STAY ON EARTH? EXCEPT TO GROW" |---------+----------------------------> | | "Michaelis, | | | Daniel" | | | <dcmichaelis@eori| | | ginal.com> | | | | | | 03/10/2005 11:19 | | | AM | | | | |---------+----------------------------> >------------------------------------------------------------------------------- ----------------------------------------| | | | To: Sengodan G. Subramanian/EMPL/FL/Verizon@VZNotes, "'ids@iiug.org'" <ids@iiug.org> | | cc: | | Subject: RE: Performance impact with logging ON/OFF [4467] | >------------------------------------------------------------------------------- ----------------------------------------| Subra, Good to see you again! My comments would be (and I've not worked with Informix for a while now, so don't take them too seriously): 1. Even if logging is turned off on the database, I believe that DDL operations are logged anyway, so for the index creation, I don't believe that having logging on or off has any effect at all on the speed of the index creation. There are a number of things that you can do to speed up index creation if you can take the engine down and tune parameters; I can look those up if you need me to. 2. Regarding the HPL load, if you're using a noconvert load from an existing table, and using the express mode, I believe that also bypasses logical logging. As a result, it shouldn't matter much whether you have the logging on the database on or off. If you're doing a deluxe mode load (to check integrity, or fire triggers, or some other operation), then it'll matter a great deal. I'm sure that others on the list will have opinions/comments, but those might get the discussion started. Good to see you again, and glad that you're working with Informix again... <G> Dan Michaelis Database Administrator/Developer eOriginal 351 West Camden Street Suite 800 Baltimore, MD 21201 410.625.5187 (phone) 410.659.9799 (fax) -----Original Message----- From: SENGODAN SU.... [mailto:sengodan.subramanian@verizon.com] Sent: Thursday, March 10, 2005 10:48 AM To: ids@iiug.org Subject: Performance impact with logging ON/OFF [4467] I would like to understand the performance impact on a unbuffered logging database for the following items: 1> I would like to create an Index on a very large table for an unffered logging database. Is it advisable to keep the logging ON/OFF? and create the index. 2> I would like to load the very large table(with 100s of mil rows) with HPL job. Is it advisable to keep the logging ON/OFF? while loading the table. In both scenario, Is there any I/O operation to logical log file?. If YES, How intensive these I/O operations are?. Thanks, Subra.