One problem with this default installation is that Informix places its logical log files and physical log files in the rootdbs. And one of the most common mistakes that rookie Informix DBAs make is to leave the logical and physical log files in the rootdbs—or even create entire databases there. For performance and maintainability reasons, those log files should really occupy their own dbspaces. This 10-Minute Solution shows you how to avoid this problem. Keeping Your Informix rootdbs Lean and Clean You can confirm the contents of your rootdbs by running the following command:
When Informix is installed with default options, all of the elements of the database are placed in the initial dbspace, which is called the rootdbs. The Informix installation process generates the minimum configuration that works and does not attempt to optimize any database elements.
The Problem
Informix's default installation places the logical and physical log files in the rootdbs. This can cause severe performance and maintenance issues for DBAs if not dealt with early on.
The Solution
There are several things you need to do in order to keep your rootdbs lean and clean:
Confirming the Contents of rootdbs
When you see a rootdbs larger than 50–75MB, you can be pretty sure that the logical and physical log files have been left in the rootdbs. The only database objects that need to be in the rootdbs are the system tables and other internal data structures that are managed by IDS.
oncheck –pe > tempfile
The only database objects that you should find in chunk 1 (the rootdbs) are the following:
- Objects in the sysmaster database
- Objects in the sysutils database
- TABLESPACE TABLESPACE
- ROOT DBspace RESERVED Pages
- CHUNK FREE LIST PAGE
- DATABASE TABLESPACE
- FREE space (chunk space that is not currently allocated to tables)
If you have found physical or logical log files in the rootdbs or if you have found other database objects, you should consider moving them out of the rootdbs to improve performance and maintainability.
Choosing the Right Disk Spaces for Log Files
One factor affecting performance is access speed. The high write loads that are placed on the logical and physical log files means there is an excessive disk head movement as the head tries to read from many non-adjacent areas of the disk. To avoid this disk thrashing, you should place the logical and physical log files only on your fastest-writing disk spaces.
It's best not to put them in RAID 5 spaces because write speeds are slower on RAID 5 systems. Instead, place them either on individual disks or mirrored disks.
Keeping Your Informix rootdbs Lean and Clean (cont.)
Placing Logical and Physical Logs
in Their Own dbspaces
Informix installs with a small number of small logical logs in the rootdbs. As the DBA, you most often need to add log files in order to avoid problems with long transactions. When a transaction runs out of logical log space, it is rolled back. If there is not enough log space available for the rollback, the database may have to be restored from an archive. This is often known as the "Informix death penalty."
To alleviate this problem, place your logical and physical logs in their own dbspaces. A good rule of thumb for creating logical logs is to create enough of them to equal about 10 to 20 percent of the size of the total database.
Here's how:
- Change the LOGSMAX parameter in the onconfig file from its default to some randomly large number. I usually use anything from 50 to 500. This parameter only changes the maximum allowable number of log files; it does not actually create the log files.
- Change the TAPEDEV parameter in the onconfig file to /dev/null. Remember to change it back when you're finished. An easy way to do this is to duplicate the TAPEDEV line in the onconfig file and change the device to /dev/null on UNIX systems or to NUL on Windows NT systems. Comment out the one you don't need by placing a "#" in front of the unwanted line.
- Note the number of the last original logfile by checking its "number" column in the output of "onstat –l".
- Create separate dbspaces for the logical and physical log files. Give the spaces a descriptive name so that you can identify their purposes from their name. I use "logspace" and "physlogspace" for the names in my systems. Remember that a dbspace cannot exceed 2GB. If you need bigger logspaces, enlarge them by adding additional 2GB storage areas to the existing logspace. I generally create a physical logspace equal to about two to three times the size of one logical logfile. You can modify the amount of the physical logs later as you tune the system.
- Take the database completely offline and restart it, so that the changes you made to the onconfig file take effect. It will come back up in quiescent mode. Leave it there. Do not take it to online mode.
- Add the new logical log files. For each one, use the following command:
onparams –a –d <logspace name> -s <size in K> -y
- Make the log files active by executing a "fake archive" to /dev/null:
ontape –s
This archive is almost instantaneous.
- Use the following command repeatedly until the current logfile (indicated by a "C" in the flags field of the "onstat –l" output) is one of the new log files rather than one of the original ones. You can differentiate the old from the new log files by noting the "number" column of the "onstat –l" command:
onmode –l
- Delete the old log files by using the following command repeatedly, changing the "logid" value until it increments up to the last logfile number that you noted in Step 3:
onparams –d –l <logid> -y
- Move the physical log into its own dbspace by using the following command:
onparams –p –s <size> -d <physdbspace name> -y
- Do another fake archive to /dev/null in order to recover the space freed up from the deletion of the old log files.
- Take the database offline again and bring it back into online mode.
If you automate Steps 6 and 9 by creating a script file with the appropriate onparams commands, you'll make the process of properly setting up the log files a simple, fool-resistant task.
Note that I didn't say "foolproof." Nothing is foolproof because fools are crafty and can outwit most normal precautions!
Keeping Your Informix rootdbs Lean and Clean (cont.)
Moving User Databases Outside rootdbs
Unless you specifically requests otherwise, the default Informix installation will create the user databases in the rootdbs. Do not do this! If you do, you will never be able to regain the space if you drop them, because you cannot drop the rootdbs or reduce its size after it has been created.
If the databases are small and not frequently used, you can probably afford to leave them in place. If they are junk databases, simply drop them by using this simple SQL command:
drop database <dbname>
If your databases are worth keeping, however, you can recreate them in their own dbspaces by using the dbexport and dbimport commands.
First, export the database to ASCII files:
dbexport <dbname> -ss
This command creates a directory called <dbname>.exp and places the ASCII export files for each table in the directory.
To reimport these databases into the correct database, use the dbimport command:
dbimport <dbname> -d <spacename> -l buffered
This assumes that you are using buffered logs. If not, check the manual for the correct syntax for the import.
When you are creating new databases, use the following syntax to avoid putting the database in the rootdbs:
create database <dbname> in <spacename> with buffered log
As I stated at the outset of this article, cramming everything into the rootdbs is a sure sign that the system has not been designed for maximum performance. As you fill up the rootdbs with log files and other extraneous databases, you run the chance of either filling up the rootdbs or deteriorating disk performance.
Keeping a clean rootdbs makes it easier to tune such things as physical log size as you learn more about the performance needs of any particular database. Keeping the rootdbs clean and lean is a sign of a careful, attentive DBA.
Summary
1)create dbspaces : onspaces –c –d logdbs –p <path> -o –s
2)edit onconfig : setting PHYSDBS to logdbs
3)down to quiescent mode : onmode –u
4)run onmonitor to used new dbspace: onmonitor > parameter > physical log
5)drop logical log that used rootdbs : onparams –d –l <log number>
6)add new logical log to new dbspaces: onparams –a –d logdbs
7)run onmode –l until it used the new dbspaces
8)make level 0 archieve
0 comments:
Post a Comment