Re: Q: Space allocation

RVASUDEV wrote: > > > Hi, > > Our platform : HP 9000 / HP-UX 10.20 / Informix Online Ver. 7.x > (x = 23, 24, 30). > > Query: > > (We searched the online CD-Docs). > > 1. Is there any guideline for calculating : > > a) space to allocate for TEMP DB space : > is there any default as a percantage of the total database size, > OR > is it application-specific and dependent on things like number and > complexity ofqueries and sizes of result sets ? You will need enough tempdb space for explicit and implicit temp tables, hash-join hash tables, sort work files (unless you set PSORT_DBTEMP), and the temp tables created during archive processing to hold preimages of modified pages (one per dbspace). Unless you do large numbers of complex joins requiring large sort space the archive needs are usually the greatest and depend on the level update activity during the archive. In versions prior to 7.3 each dbspace got a temp table created in one of the temp dbspaces selected round-robin, if any of the temp dbspaces becomes full during the backup the archive will abort so you must have enough temp dbspace to accommodate the possibility that all of the busiest dbspaces will get their temp tables created in the same temp dbspace. For versions before 7.3 therefore you want to have fewer larger temp spaces for archive needs while sorting efficiency favors many smaller temp spaces in separate disks. Version 7.30+ solves the archive problem by fragmenting ALL temp tables not created with an IN clause across all temp dbspaces as a round-robin fragmented table. So in 7.3 it is simpler. Create at least 3 temp dbspaces large enough to hold any temp tables and sort-work files and all of the page pre-images needed during an archive. > b) the size of the logical and physical logs ? Logical logs, enough total space that your longest reasonable and normal transactions are unlikely to cause a long transaction condition. Remember to allow for several of these to coexist if that can happen. As to the balance between number of logs and size of each log, fewer larger logs are easier to manage. More smaller logs are safer since they are backed up more frequently using continuous or event driven log archiving. Also if you use buffered logging keep in mind that the log is not written to disk until it is completed so larger log files are more at risk. Unbuffered logs are flushed when the logical log I/O buffer is filled or the transaction commits, whichever comes first. Physical log, enough to permit the CKPTINTVL you want. If you set the CKPTINTVL to 900 (15 minutes) and onstat -m shows checkpoints every 8 minutes then the physical log reaching 75% full is triggering early checkpoints. This may be OK during peak load but should not be relied on during normal load (also there is a bug in version before 7.23 which can cause the engine to crash if the checkpoint does not complete before the physical log reaches 100% full). > c) the ratio to maintain between the number of logical and physical > logs ? Not related at all. > d) the ratio to maintain between the total size of the database and > the total size of all logs Not related. Art S. Kagel