One ,I/O tuning
In progress I/O Many decisions have to be made when tuning . Is the original device or file system used ? Whether to use direct I/O? What block size should be selected for the database ?
If online transactions are being strictly executed ( It is characterized by small random reading / Write operation ) Workload , You should choose a smaller block size such as 2KB. about
DSS For medium and long-term query operations , In the implementation of complex query optimizer and complex memory ( classification / Hash area ) Parameter controlled database , Larger block sizes increase the speed of database scanning , for example
8KB( If the database supports , Or a larger size is available ). Include in workload OLTP and DSS How to deal with it ? At this time, the optimization of database parameters should be carefully considered .
In some cases it is necessary to make a compromise , perhaps 4KB The size of the block is appropriate .
Two , Queue length and response time
stay Linux In the system , vmstat It's good I/O Bandwidth measurement tool . The tool's “ I/O section”
In the end bi and bo The two columns originally represent the number of blocks input to and output from the block device , as vmstat Of man Help command . however , In a variety of Linux These columns in the distribution are actually
KBps Reports character or block devices for units ( file system ) Transmission rate during measurement . For both workloads , If the queue length is greater than 1, There is the possibility of some kind of conflict . about OLTP Come on ,
exceed 50ms Response time is a problem to be solved .
Three , load balance
Linux Multiple systems provide load balancing tools to determine whether or not a load balancing tool is required . An easy way to do this is to use the iostat.
It is shown below iostat –x An example of the output of ：
If software striping is not used (striping) ability , You should ensure that all tables in the database are evenly distributed across all disks . In this read-only Operations Section of the benchmark , disk
sdi Writing is actually in progress , Because the log is obviously stored on this disk . The log should be on a separate tape volume (stripe volume) upper , Even on a separate disk if possible , So that the disk
sdi Is not affected by other aspects of the benchmark .
Four , Global memory
about OLTP In terms of workload , Generally, the global cache of the database should be used as much as possible
I/O Operation moved to memory . Most databases provide tools to see if user transactions are cached , Including about dirty (dirty) Statistics of buffers and used buffers . stay Oracle
To estimate properly in memory , Parameters need to be set database_block_
buffers. This only needs to determine the amount of free memory dedicated to the database , Then divide the value by database_block_size that will do , As shown below ：
4GB In memory allocation for database 2.5GB, therefore database_block_buffers The value is 2684354560 /4096= 655360
Here's one db_block_buffers Examples of formulas ：
That depends on the primary key index query
OLTP/WEB The workload benefits from caching results through a large buffer pool and reducing it I/O Subsystem I/O Throughput ( Per second I/O workload ) bottleneck .DSS A workload often requires a large table scan operation and returns a large number of table row results .
For this kind of workload , Through a large number of sort and join Operation allocates memory , It can avoid high damage on temporary disk space I/O bandwidth / Throughput (MBps) Spillover phenomenon of . This is done through configuration
hash and sort Size these database parameters to complete . The global cache capacity of these workloads does not need to be large —— Can compare OLTP The global cache required by the workload is several orders of magnitude smaller .
Here is an example vmstat To determine an example of free memory and used memory , The relevant columns are then described . Note that this example contains vmstat Multiple columns of data that can be returned under normal conditions .
* free Columns are displayed in kilobytes . If free memory is still available , So it may not be a resource constraint .
* swpd Columns are displayed in kilobytes , Reports the virtual stock used or the amount of memory swapped out to disk .
* si Column gives the amount of memory swapped in from disk during the reporting period .
* so Column gives the swap out to disk during the reporting period ( virtual memory ) Amount of memory on .
If swpd The value is larger and the si and
so A large number of exchange activities can be found in the column , You may need to add more memory or reduce the amount of memory allocated to the database , This saves more memory for the application . Make sure there is memory available to allocate to the database . in addition , It also assumes that Linux The global cache region for locking the database has been considered in .
It can also be used Linux Of top Command to get more detailed information about the memory intensive process . Running top Input on command h, You can get a list of options ; input
m Sort by resident memory usage , To determine which processes consume the most memory . Linux Of /usr/bin/top Tool ratio vmstat With greater interference , It also takes up more
CPU time . Therefore, it should be used first vmstat, Use when additional information is needed top tool .
It should be remembered , stay 32 position Linux On the system , The memory capacity may exceed the addressing capacity of the database software . under these circumstances , If it appears I/O problem , We should look for new ways to use free memory .
In order to reduce I/O operation , Use memory as much as possible . In some cases , The temporary space area of the database can be utilized , Especially for the use of sort or hash The specific process of the region ( Typical exists in
DSS In workload ). Make sure that the database parameters that control these areas are set to the maximum ( Divided by the number of database agents ), At the same time, it still does not exceed the memory of the system ( Including kernel ) Range .
Five , Log device
When all the other bottlenecks are resolved , The optimization of logging devices is often the final decision OLTP Database performance . It is important to try to separate the log file from all other database files .
The next step is to decide whether to use the raw device or the file system device to run the log file . historically , Raw devices are the preferred logging device for supporting databases . Some databases use direct
I/O file system , Its performance can reach that of the original equipment 5%. Others ( Usually non-commercial ) The database uses
Linux Provides a buffer mechanism for file system caching . It is suggested that these schemes should be directly compared in specific settings .