Logical Log Sizing issues ~ Answered: amber (solid confidence) — David Williams' first reply directly and comprehensively answers all four of the original poster's sizing questions (doc reference, 20% guideline, OLTP vs. DSS, and correcting the '1024 max logs' belief to 32767); the thread then drifts into a different poster's (Bob Fontana's) unrelated -458 troubleshooting scenario, which Bob himself confirms was resolved by other responders, but the original asker (dbruce) never reappears to confirm.

Advisory only.

We are developing Standards for our Informix Instances. Our current discussion is how big do we make the logical logs for a specific instance. We currently us a standard of 10MB log files and keep enough logs to hold 24 hours worth of transactions. We have 7.24 and 7.30 on HPUX 10.20 and Solaris 2.5.1. I am interested in: What documentation have you seen that might address this issue. What standard sizes are recommended or used out there. Do we want a different size logical logs for OLTP and DSS type instances. What are the maximum number of logs that can be created? We heard 1024. Thank-you in advance for your input. -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

In article <768ke5$vr6$1@nnrp1.dejanews.com>, dbruce@us.dhl.com writes >We are developing Standards for our Informix Instances. >Our current discussion is how big do we make the logical logs for >a specific instance. We currently us a standard of 10MB log files and >keep enough logs to hold 24 hours worth of transactions. >We have 7.24 and 7.30 on HPUX 10.20 and Solaris 2.5.1. I am interested in: > > What documentation have you seen that might address this issue. Online Admin. Guide. > What standard sizes are recommended or used out there. Admin. Guide 7.1 says Total logs 20% of DB, split 3:1 ratio. I normally go for logical logs as small as possible so that they get written to tape quicker. After all they are safer on tape... > Do we want a different size logical logs for OLTP and DSS type instances. Probably since DSS environment will have less transactions. > What are the maximum number of logs that can be created? We heard 1024. > 32767 >Thank-you in advance for your input. > >-----------== Posted via Deja News, The Discussion Network ==---------- >http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own -- David Williams

Is that 20% of the entire database size? Or is it 20% of the ROOTDBS size? > Admin. Guide 7.1 says Total logs 20% of DB, split 3:1 ratio. Thanks for the info! Bob Fontana

Bob Fontana wrote in message <368A9382.D5282AC8@mapson.securitytechnologies.com>... >Is that 20% of the entire database size? Or is it 20% of the ROOTDBS size? It's 20% of the whole database server size. But really, it's just a starting point, and is influenced by many factors, including the level of update activity you have, your requirements for recoverability and other aspects of your operations. As David Williams said, it's quite adequately discussed in the Administrator's manual. Neil Truby Londis Holdings Hampton Hill SW London

Unfortunately, the company I'm working for is unwilling to upgrade to 7.x. They are running 5.0x on two different platforms -- AIX 4.2.1 and Unixware 2.1.2 which are deployed on about 1000 machines worldwide. They use I-Star heavily. The "DBA" who originally configured the system sized the root dbspace to 48 MB on AIX and 15 MB, with 3 MB of logical logs and 1 MB of physical logs. Archiving is directed to /dev/null. The database itself consists of 1 database with 109 tables. Some tables can grow as large as 4 million rows spread over 5 chunks of 2 GB each. I-Star is used to perform various joins between multiple machines whose databases are similar to what was described above. Over the years, disk drives have gotten larger and the database sizes on some machines have grown to 10 times their original size. The size of the rootdbs, however, has remained exactly the same. On machines running remote queries, we are now seeing SQL error -458 (long transaction timeout) occuring several times a day which results in the queries failing. The log indicates that at the time that the failures occur, the logical logs are filling up at a rate of once every 5-7 seconds. I contend that the number of logical logs (6 for Unixware and 16 for AIX) are probably okay and that the size needs to be increased from 500K to at least 4 MB. Everything I have read supports this but I can't seem to get the company's management to go along with this change. The 5.X administration guide on Page 1-26 says "physical and logical log files should equal about 20 percent of all dbspace dedicated to OnLine." Interpreted one way, the implication is that the physical and logical log size should be 1/5 of the database size. Interpreted another way, the physical and logical log size should equal 1/5 of the root dbspace, since the root dbspace is the only space that is dedicated soley to to OnLine, assuming the user tables have been created in a different dbspace. In the last paragraph of Page 1-24, however, there is a sentence that conflicts with the one on 1-26. It says "Total space devoted to teh physical and logical logs is 4,000 kilobytes. This value meets the first criterion of 20 percent of the root dbspace, which is 20,000 kilobytes" (referring to the default configuration). So, not having the 7.X Admin Guides, I'm left wondering which statement to believe. -Bob Neil Truby wrote: > Bob Fontana wrote in message > <368A9382.D5282AC8@mapson.securitytechnologies.com>... > >Is that 20% of the entire database size? Or is it 20% of the ROOTDBS size? > > It's 20% of the whole database server size. But really, it's just a > starting point, and is influenced by many factors, including the level of > update activity you have, your requirements for recoverability and other > aspects of your operations. > > As David Williams said, it's quite adequately discussed in the > Administrator's manual. > > Neil Truby > Londis Holdings > Hampton Hill > SW London

>> I contend that the number of logical logs (6 for Unixware and 16 for AIX) are probably okay and that the size needs to be increased from 500K to at least 4 MB. I simply can't understand why you would be considering a change from 500K to 4MB. This is peanuts. We're in an age when a 10GByte drive (10 THOUSAND MBytes!!!!!!!!!) costs a couple of hundred pounds/dollars. Ramp the total size of your logs up to, say, 100MBytes and stop fannying around! >> Interpreted one way, the implication is that the physical and logical log size should be 1/5 of the database size. Interpreted another way, the physical and logical log size should equal 1/5 of the root dbspace, since the root dbspace is the only space that is dedicated soley to to OnLine, assuming the user tables have been created in a different dbspace. That's a misunderstansding. The statement means of the total disk space allocated to the database server. But it's just the roughest of rough, initial guidelines, it's not supposed to be a hard-and-fast rule. In any case, you're missing the point, which is not to slavishly follow guidelines, but to optimise your system. Your updates are failing with a long transaction error, and you've correctly identified the cause, i.e. insufficient disk space. If I were you I would STRONGLY advise you to stop wasting further time on pondering the issue, and add a large amount of logical logs, say 100MBytes. If your cause with your management would be strengthened by the considered written (and expensive!) opinion of a reputable consultant, please drop me a line! Neil Truby aracnet Limited Weybridge, UK

In article <76gvhe$bji$1@taliesin.netcom.net.uk>, Neil Truby <ntruby@netcomuk.co.uk> writes >insufficient disk space. If I were you I would STRONGLY advise you to stop >wasting further time on pondering the issue, and add a large amount of >logical logs, say 100MBytes. > Agreed, I normally go for 1000 250K logical logs + physical log of 200Mb. Remember logical logs get written to disk once full and not longer needed for rollback, hence smaller logs = less on vulnerable disk storage. Remember online allows for up to 32767 logical logs!! >If your cause with your management would be strengthened by the considered >written (and expensive!) opinion of a reputable consultant, please drop me a >line! > And me! >Neil Truby > >aracnet Limited > >Weybridge, UK > > > > > -- David Williams

Thanks Neil and David. You both gave me the answer I was looking for. -Bob

Bob Fontana wrote: > > Unfortunately, the company I'm working for is unwilling to upgrade to > 7.x. They are running 5.0x on two different platforms -- AIX 4.2.1 and > Unixware 2.1.2 which are deployed on about 1000 machines worldwide. > They use I-Star heavily. The "DBA" who originally configured the > system sized the root dbspace to 48 MB on AIX and 15 MB, with 3 MB of > logical logs and 1 MB of physical logs. Archiving is directed to > /dev/null. > > The database itself consists of 1 database with 109 tables. Some > tables can grow as large as 4 million rows spread over 5 chunks of 2 > GB each. I-Star is used to perform various joins between multiple > machines whose databases are similar to what was described above. > Over the years, disk drives have gotten larger and the database sizes > on some machines have grown to 10 times their original size. The size > of the rootdbs, however, has remained exactly the same. On machines > running remote queries, we are now seeing SQL error -458 (long > transaction timeout) occuring several times a day which results in the > queries failing. The log indicates that at the time that the failures > occur, the logical logs are filling up at a rate of once every 5-7 > seconds. > > I contend that the number of logical logs (6 for Unixware and 16 for > AIX) are probably okay and that the size needs to be increased from > 500K to at least 4 MB. Everything I have read supports this but I > can't seem to get the company's management to go along with this > change. The 5.X administration guide on Page 1-26 says "physical and > logical log files should equal about 20 percent of all dbspace > dedicated to OnLine." > > Interpreted one way, the implication is that the physical and logical > log size should be 1/5 of the database size. Interpreted another way, > the physical and logical log size should equal 1/5 of the root > dbspace, since the root dbspace is the only space that is dedicated > soley to to OnLine, assuming the user tables have been created in a > different dbspace. > > In the last paragraph of Page 1-24, however, there is a sentence that > conflicts with the one on 1-26. It says "Total space devoted to teh > physical and logical logs is 4,000 kilobytes. This value meets the > first criterion of 20 percent of the root dbspace, which is 20,000 > kilobytes" (referring to the default configuration). > > So, not having the 7.X Admin Guides, I'm left wondering which > statement to believe. Not to just 'me too' with David and Neil, but I like to run 70-80 100MB logical logs. I tend to use a smaller physical log but that is because I checkpoint every 10 minutes and our update rate does not warrant a larger one. Sizing the physical log BTW is pretty straight forward. It must be large enough to not force early checkpointing, except under unusual conditions like a sudden update of the entire database, and is wasted if less than about 50% is typically used just before a scheduled checkpoint. Adjust this by adding space if the used % rises above about 85-90% before the checkpoint completes. Anyway back to your problem. You say you get -458 during distributed queries. Assuming these are not actually remote updates, the only thing that could be logging is the creation of the implicit temp tables needed for sorting. You can remove this problem in two ways: 1) reduce the need to sort when possible and 2) use PSORT_NPROCS and DBTEMP to get those temp tables and sort-work files out of rootdbs where they are obviously being created now. This will reduce the logging requirements of these queries. This is your real problem not how little logical log space you have, and I agree BTW that you do not have nearly enough logical log space. And I agree with David and Neil, bite the bullet and add a disk drive dedicated to logical logs and add more log space. Even if you have to do that on ALL 1000 machines worldwide that's about $400,000 US a very small price for the peace of mind of knowing that queries will no longer fail. The extra salaries saved by not having to repeat queries will pay for the disks. BTW start backing up your logs. If you cannot dedicate a terminal and tape drive to continuous backups then work out a periodic backup plan. It is not as easy as it is in 7.xx with the ALARMPROGRAM to handle it for you but doable. For example you can have a cron job wake hourly and run ontape -a with the logs going to a disk file that is subsequently renamed. Then the log backup files will be picked up by the system backups (please say your sysadmin backs up those 1000 machines) and another cron can delete backup files older than say 14 days after which the files will be on at least 2 system backups (an incremental and a full). BTW I believe that in 5.x all of the logical logfiles had to be the same size. Someone recently reported trying to use differing log file sizes, apparently tbparams allows you to add these, and reported odd behavior. Try to talk management into upgrading to IDS 7.3x it is a vastly superior product and so much faster than 7.[12]x that any original objections to upgrading from 5.xx due to 5.xx being faster on simple queries is no longer valid. OH ALSO VERY IMPORTANT! I hope the x in 5.0x above is an 8 or 9 because the ontape for versions before 5.08 CANNOT SAFELY ARCHIVE A DATABASE while the database is online and actively being updated. It misses pages and so cannot be successfully restored except to the exact same disks where the missing pages may be already there. If you are using 5.00 -> 5.07 UPGRADE NOW to OL 5.10 or IDS 7.30. Art S. Kagel PS: I'd also take some of those consulting fees to convince your bosses. But I refuse to deal with pointy haired people.

There is another reason to go to 5.10+: Year 2000 Compliant. I believe the latest version of the 5 family is 5.11+. =============================================== Clifton M. Bean cbean@informix.com INFORMIX Support Engineer 16011 College Blvd Lenexa, KS 66219 INFORMIX Phone: 800-274-8184 Fax: 913-588-8590 =============================================== NOTICE TO BULK E-MAILERS: Please read the following before bulk e-mailing this address: Pursuant to US Code, Title 47, Chapter 5, Subchapter II, p.227, any and all non-solicited commercial e-mail sent to this address is subject to a download and archival fee in the amount of $500.00 US. Anyone who sends unsolicited commercial e-mail to this account will be charged a $500.00 US proof- reading fee. Consider this an official notification. Failure to abide by this will result in legal action. For a complete summary of this Legislation see the following URL: http://thomas.loc.gov/cgi-bin/bdquery/z?d105:SN01618:@@@D