Summary of the methods for rapidly increasing transactions in the IBM small machine + ORACLE Database TPS

Summary: Recently, we have been conducting business stress tests to improve the system's transaction processing capabilities. The transaction processing volume has accumulated some experience from three thousand at the beginning to nearly 10 thousand today.

Summary: Recently, we have been conducting business stress tests to improve the system's transaction processing capabilities. The transaction processing volume has accumulated some experience from three thousand at the beginning to nearly 10 thousand today.

Summary: Recently, we have been conducting business stress tests to improve the system's transaction processing capabilities. The transaction processing volume is nearly three thousand from the first 10 thousand to today, and some optimization methods have been accumulated to share with you. First of all, of course, your system needs to handle large concurrent transactions. If your system TPS is only 100 or 200 per second, Hong Kong servers may not be the main objective of transaction optimization. There are two optimization methods in total. The first part is the conventional optimization method, and the second part is the optimization method with a slight "evil" point.

Conventional Optimization Methods for large concurrent transactions:

1) redo log Disk Optimization: If it is stored, you can take out a few more disks for the LOG disk, the most basic knowledge disk should be made into RAID 10, not RAID 5. Depending on your disk, you can use 6-12 disks. These disks are exclusively provided for REDO logs, and the remaining space cannot be provided for other disks to avoid affecting the IO of LOG disks. Because the storage space generally carries a CACHE, the CACHE size varies depending on the high and low ends, and the website space and storage model are different, the stored CACHE can be said to be the soul of storage, the write buffer effect is very obvious, greatly reducing the time for writing LOG disks.

2) the I/O of the data disk depends on the situation of your system. According to the Operating System Tool topas-D or iostat-DlRa 1, there is no bottleneck in data inventory. If busy is large, you need to add hard disks to increase IOPS. The busy data disk of transaction systems is mainly caused by dirty Data Writing (maybe your situation is different from mine). Generally, IOPS is not high. This depends on the system. If IOPS is not enough, you can consider using SSD to increase IOPS, but the storage does not support SSD well. It is best to scatter ssd io in multiple storage fiber loops.

3) ORACLE's group commit is an automatic and default action. In fact, it cannot be optimized without optimization.

4) set the CACHE attribute for key business tables to ensure that the transaction data is in the memory. The optimization is also very important, and the optimization effect is also very obvious. You can set CACHE tables in ORACLE.

At that time, the pressure test was based on a small P740 machine + a mid-range storage of V7000. After the above optimization, the TPS could reach 6000.

Optimization of unconventional means:

1) Since P740 only has a physical 16-core cpu, the CPU usage reaches 65%, and the LOAD is close to 40. To enable LGWR to obtain CUP resources at any time, the _ high_priority_processes parameter and website space are set to ensure that LGWR can obtain CPU resources at any time without queuing.

2) Set dscrctl-n-B-s 1 memory pre-read at the operating system level. The effect is very obvious and the TPS can be increased by more than 1000 immediately.

3) if you are a 10-gb Nic, chdev-l defaults 0-a cdli_queues = 4-P, and increase the NIC processing queue, the effect is also very obvious, and the TPS can be increased by more than 1500. For systems with a large number of things, NICs are often bottlenecks and necessary for optimization. In a timely manner, the processing capacity of your 3 M NICs has not reached the bottleneck yet. However, with this optimization, you can still see the immediate effect.

4) when the redo log block size of ORACLE is set to 4 K. 11GR2, you can specify the block size of the redo log, which is generally 512 bytes of the disk sector size. In my version 11.2.0.3, an error is reported, indicating that the modification value does not match the actual slice size. The implicit parameter _ disk_sector_size_override can be modified to true. The modification method is to add log file xxxx blocksize 4096 in alter database. For PL/SQL stress tests, the commit write immediate wait method is used for submission. The gap between the two methods is close to 4 times, which is amazing. However, our business stress testing only improved the TPS by more than 1500, which is also very good.

After the optimization in the above four steps, the TPS can be nearly 1 W. I would like to share with you my hope to help you. I am not talking about it in detail in many places. If you need it, please contact me for further discussion.

P740, 16 core 3.55GHZ memory, 32g cpu, 3 M Nic BOND.

V7000 80g cache. 12 LOG disks. Enough data disks.

This article is from the "Wei Xinghua's DBA life" blog. Please keep this source