Database physical distribution Design (RPM)

Overview
Regardless of which database we use, no matter how we design the database, I would like to follow a principle: data security and performance efficiency of the two main aspects, but about these two aspects of the topic too much, here is not a statement, I just from the database of physical distribution design and we have a simple discussion. Because the database good physical distribution design is also to the data security and the performance high efficiency influence is big, just like we must first lay the foundation before the building construction.
In reality, when we apply a variety of different databases, often ignore the physical layout of the database, only when the database performance problems to consider, but this is not worth it, this will not only lead to design-related problems arise, but also affect the performance of the adjustment effect, So it is necessary to plan the physical layout of the database before we create the database, which is also in line with what people often call "ax". Let me take Oracle as an example from optimizing the operating system, disk layout optimization and configuration, selecting database initialization parameters, setting up and managing memory, setting up and managing CPUs, setting up and managing the tablespace, setting up and managing rollback segments, setting up and managing online redo logs, setting up and managing archive redo logs, Set up and manage control files and so on several aspects to make the statement.

I. Optimizing the operating system
Optimization of the operating system is also necessary for optimal server performance because operating system performance issues typically involve process management, memory management, scheduling, and so on, so users need to ensure that they have sufficient I/O bandwidth, CPU processing power, and swap space to minimize system time. If an application has too many "busy" waits in the buffer, the process of calling the system will increase, although the number of calls can be reduced by optimizing the SQL statements, but this is also a cure. The user can start the Oracle initialization parameter timed_statistics to increase the number of system calls, and conversely, if this parameter is turned off, the number of system calls will be reduced. The caching of the operating system does not conflict with Oracle's own cache management, although it can consume a certain amount of resources, but it has a certain benefit for performance, because the I/O of all databases generally requires access to the file memory through the system file cache.
Oracle's operations may use many processes (some are called threads), so users should ensure that all Oracle processes, background processes, and user processes have the same priority, or else it will cause deterioration. Causes high-priority processes to wait for low-priority processes to dispose of the CPU resources before processing, and not to bind the Oracle background process to the CPU, which can also cause the bound process to starve to the CPU resources.
The good is that some operating systems provide operating system Resource Manager (Operating System Resource Manager), through which it can prioritize system resource access to reduce the impact of peak load mode, to implement a variety of management policies and methods to control user resource access, Limit the amount of user resources that can be consumed.

Two Disk layout Optimization and configuration
In most product database applications, database files are generally placed on disk, so the good use and layout of the disks is also important. The goal of disk layout is: Disk performance can not hinder the implementation of database performance, the database disk must be dedicated to the database files, otherwise the non-database will affect the database, and this impact is unpredictable; System hardware and mirrors must meet recovery and performance requirements, data file size and I/O cannot exceed disk size and I/O, databases must be recoverable, and the competition between background processes must be minimized. Also note when planning your hard disk configuration: The disk capacity used first, sometimes with smaller disks than with a larger disk, because more advanced parallel I/O operations can be performed; Second, the speed of the disk, such as reaction time and seek time will affect the performance of I/O, you can consider using the appropriate file system as a data file; Furthermore, use the appropriate raid.
RAID (redundant Arrays of inexpensive Disks) inexpensive redundant arrays can improve data reliability, and I/O performance depends on how the raid is configured: RAID1 can provide better reliability and faster read speed, but the cost of writing is relatively large , so it is not suitable for frequently written applications; raid0+1 read faster on the basis of the original RAID1, so this is also the way that people often choose, RAID5 can provide better reliability, sequential read operations are better suited to this approach, but performance is affected, This is not suitable for applications where write operations are frequent. For the choice that way can not be generalize, according to the specific circumstances.
Some applications are congenital by disk I/O limits, so in design should try to make Oracle performance is not limited by I/O, so when designing an I/O system, consider the following database needs: Storage disk minimum bytes; availability, such as 24x7, 9X5; Performance such as output and response time for I/O. The I/O statistics that determine the Oracle file can be queried for the following: physical read quantity (v$filestat.phyrds), physical write Quantity (v$filestat.phywrites), average time, i/o= physical read + physical write. While the average number of I/o = (physical read + physical write)/total number of seconds), it is estimated that this data is useful for the new system and can be queried to see if the I/O requirements of the new application match the I/O capability of the system for timely adjustment.

Three To create a selection of database initialization parameters
The first stage of managing a database is initializing the creation of the database, although you can adjust the performance after the database is created, but some parameters cannot be modified or are difficult to modify, such as: Db_block_size, Db_name, Db_domain, Compatible, Nls_ Language, Nls_characterset, Nls_nchar_characterset.
Db_block_size parameters determine the size of the Oracle database block, the general can choose the range is 2K, 4K, 8K, 16K, 32K, the use of the next large database block size effect can generally be centralized query performance increased by 50%. But generally for the general server does not advocate the value of the large, except minicomputer, because there will be more rows in the database block, the possibility of block-level competition during the maintenance of the database is relatively large, the way to avoid this competition is to increase the table-level and index-level freelists, Setting values for Maxtrans and Initrans, usually freelists set to greater than 4, will bring more benefits.
Db_name This parameter specifies a database identifier, typically the name specified in create databases, the parameter is optional (required when oracle9i real-time application of a cluster, multiple instances have the same parameter values), but is recommended in the Create Database before setting it, if not specified, to appear in the startup or alter Database mount command.
Db_domain This parameter specifies the extended portion of the global database name, which is required when oracle9i applies the cluster in real time, and multiple instances have the same parameter values.
Compatible This parameter specifies the compatibility of the Oracle Server maintenance version, which allows users to use the new version when backward compatibility with earlier versions, is required when oracle9i is applied in real time, and multiple instances have the same parameter values.
Nls_language and Nls_characterset and nls_nchar_characterset three parameters are the character set parameters of the database, after the creation of the database is generally not changed or difficult to change, so in the creation of the database should be set up first.

Four Setting up and managing memory
Oracle uses shared memory to manage its memory and file structure, and Oracle often uses the following memory structures:
System global AREA,SGA, the SGA varies from one environment to another, without a common best practice, before we set it straight, consider the following: How large is physical memory, and how much memory the operating system is, Whether the database system is a file system or a bare device; The SGA includes: Fixed size, Variable size, Database buffers, Redo buffers. The amount of physical memory that the SGA occupies is not strictly regulated and can only be followed by general rules: The SGA occupies about 40%--60% of physical memory. If it is expressed through an intuitive formulation: The OS uses memory +sga+ concurrent processes * (SORT_AREA_SIZE+HASH_AREA_SIZE+2M) <0.7ram, this formula is only a reference, not to be detained in this, the actual situation can be free to play. Some parameters in the initialization parameter file have a decisive effect on the size of the SGA. The parameter db_block_buffers (the number of buffers in the SGA cache), the parameter shared_pool_size (the number of bytes allocated to the shared SQL area), is the primary influence of the SGA size. The database buffers parameter is the most important determinant of the SGA size and database performance. This high value increases the system's hit ratio and reduces I/O. The size of each buffer is equal to the size of the parameter db_block_size. The Oracle database block represents the size in bytes. The shared pool portion of the Oracle SGA is comprised of the library cache, the dictionary cache, and other user and server session information, which is the largest consumption component. Adjusting the size of each structure in the SGA area can greatly improve the performance of the system.
The data block buffer cache (buffers cache), which is used by the buffers in 8i, is replaced by Db_cache_size in db_block_buffers*db_block_size,9i. After the other parameters are set in the memory configuration, the data buffers should be given. Oracle reads and writes data to the database cache during run time, the cache hit indicates that the information is in memory, and the cache failure means that Oracle must have disk I/O. The key to keeping the cache failure rate to a minimum is to ensure the cache size. The initialization parameter in Oracle8i db_block_buffers controls the size of the database buffer cache. You can query the V$sysstat hit rate to determine if the value of db_block_buffers should be increased.
SELECT Name,value from V$sysstat
WHERE name in (' Dbblock gets ', ' consistent gets ', ' physical reads ');
Hit =1-physical reads/(Dbblock gets+consistent) by query results if the hit rate is <0.6~0.7, you should increase db_block_buffers.
Dictionary buffer (Dictionary cache), the size of the data dictionary buffer is managed internally by the database, and the size is set by the parameter shared_pool_size. The data dictionary cache includes the structure of the database, user, entity information, and so on. The hit ratio of the data dictionary has a great impact on the system. In the calculation of hit rate, getmisses indicates the number of failures, and gets indicates the number of successes. Query V$rowcache table:
SELECT (SUM (getmisses)/(SUM (gets) +sum (getmisses))) *100
From V$rowcache;
If the value is >90%, the hit ratio is appropriate. Otherwise, you should increase the size of the shared pool.
Redo the log buffers (Read log buffer), there will be a statement below, not described here.
The SQL Shared pool (shared pool size), which includes the execution plan and parsing of SQL statements against the database, can be used to speed up execution by parsing the same SQL statement the next time you run it. If it is too small, the statements are continuously loaded into the library buffers, which can affect performance. This parameter can be modified by the ALTER system command, and the later version of 9I can be dynamically modified to its size.
Candidates (Large pool size) is an optional memory area. If you choose, you can improve performance for database-like backup/restore of these large operations. If you do not select this parameter, the system uses a shared pool.
Java pools (Java pool size), known by their names, are required to satisfy the parsing of java command syntax. In UNIX systems, if the size of the zone group is 4MB, the default size should be 24M, and if the zone group size is 16MB, the default size is 32M. If the database is not using Java, keep it 10m-20m enough.
Multiple buffer pools (multiple buffer pools), you can use a multi-buffer pool to separate large datasets from the rest of the application to reduce their likelihood of competing for the same resources within the cache, and to set their size in the initialization parameters when they are created.
Program Global AREA,PGA is a private memory area of Oracle, and in later versions of 9i, if workarea_size_policy=auto, all sessions share a piece of memory that has parameters Pga_ Aggregate_target setting, a good initial setting for it is: for an OLTP system pga_aggregate_target= (totall_mem*80%) *20%; for a DSS system Pga_aggregate_ target= (total_mem*80%) *50%. The total_mem here is physical memory. The following dynamic views are helpful when adjusting the Pga_aggregate_target parameter: V$sysstat and V$sesstat;v$sql_workarea_active;v$pgastat;v$sql_workarea; V $process.

Five Setting up and managing CPUs
In the process of setting up and installing the database, basically do not configure the CPU what to do, the system will automatically default, but in the management process we can use the operating system monitoring tools to monitor the status of the CPU. For example, in a UNIX system, you can run Sar–u tools to check the level of CPU used by the entire system. The statistical information includes: User time, System time, idle time, I/O wait time. In the case of normal workloads, if the idle time and I/O wait time are close to 0 or less than 5%, then there is a problem with CPU usage.
For Windows systems to check CPU usage through Performance Monitor (Performance Monitor), you can provide the following information: Processor Time, User time, privileged time, interrupt time, DPC time.
If there is a problem with the use of the CPU, it can be solved by optimizing the system and database, increasing the hardware capacity, prioritizing the CPU resource allocation, and the Oracle Database resource Manager (Resource Manager) is responsible for allocating and managing CPU resources between users and applications.

Six Set up and manage table spaces
The I/O competition between database files is a big taboo of database, so before the database planning to the data file I/O preliminary evaluation, usually, the application of the Product database table is located in the table space is very active, index table space and data dictionary and other table space is also very active, for things more frequent application , the Redo table space is also very active, so the data file for different types of database I/O competition will be slightly different, but basically follow the following principle is better: applied tables and indexes should usually be allocated or partitioned into multiple table spaces to reduce the I/O of a single data file, It is best to set up separate table spaces for each area object with the same function; There is no reason to put anything other than the data dictionary table and the system fallback segment into the system table space, to remove objects that can move out of the system table space; The index segment should not be placed in the same table space as the related tables because they generate a lot of concurrent I/O in data management and querying; Temporary table spaces are used to store a large number of sorts, so other application objects cannot be placed in temporary table spaces.
The above is the principle of database file distribution, principle of the principle, the application of facts, we still have experience to set distribution is better, of course, before the experience or reference principle so that will not detours.
A database and tablespace can be a one-to-many relationship, and a table space and data file can also be a one-to-many relationship, and data files and data objects can also be one-to-many relationships. When you create a data object, such as a table or index, you can assign it to a table space by default or special commands, which creates a segment (Segment) in that tablespace to store data about that object. A segment consists of sections called intervals (Extent, a contiguous set of Oracle blocks), but when an existing section cannot store data, the segment is given another interval to support inserting the data into the object. Therefore, the space used by this segment is determined by its parameters, which can be specified at creation time, or can be changed later. If you do not specify a storage parameter in the Create table,create index,create cluster,create rollback segment command, the database defaults to the parameters of the tablespace in which it is stored, with the parameters Initial,next , Pctincrease,maxextents,minextents and so on. The initial and minextents values cannot be modified after creation, and the default values for the storage parameters for each table space can be queried in the Dba_tablespaces view.
Disk I/O is a bottleneck for system performance, which solves disk I/O and can significantly improve performance. Query V$filestat to know how often each physical file is used (Phyrds indicates the number of reads per data file, Phywrts indicates the number of times each data file was written)
SELECT name,phyrds,phywrts from V$datafile Df,v$filestat FS
WHERE df.file# =fs.file#;
For high-frequency physical files, you can use the following strategy: Distribute I/O as evenly as possible on as many disks as possible, create different tablespaces for tables and indexes, separate data files from redo log files on separate disks, and reduce disk I/O without Oracle server.
If you do not have experience in the case of improper planning of data files, resulting in a large number of I/O competition, it is necessary to adjust the distribution of data files according to the above principles, in order to balance the I/O competition between data files, how to move the data files, various database methods are different, But the basic principle is the same, here is an example of two ways to oracle8i how to move a data file (9i slightly different):
The first method: (Alter database)
Close database-Move database file-mount and rename-start

Database physical distribution Design (RPM)