How to plan and select a database server: CPU, memory, disk, network

Transferred from: http://blog.chinaunix.net/uid-5715-id-2734517.html

It is helpful to learn how to calculate TPCC values based on the business model.

When a new business system development is completed, it is necessary to promote the application in a region and even nationwide, how to choose the server configuration, internal and external disk size, and network bandwidth according to the size of the business is a complex matter.

One of the most realistic assessment is to establish a close to the real business application of the operating environment, to carry out a variety of stress testing, the number of different users, the system response time and throughput, and to obtain the utilization of various resources of the server at that time, the complete evaluation of hardware resources, need to consider the following three aspects:

• Evaluation of server performance

• Evaluation of client workstations or front-end desktops

• Evaluation of communication cards and network bandwidth

If an accurate stress test environment cannot be established, the server needs to be evaluated according to the industry's benchmark, and the server configuration that fits the size of the business is calculated, while the resources that are consumed in system administration, such as backup, recovery, problem diagnosis, performance analysis, Software Maintenance will bring additional consumption of resources, for important resources to consider to leave the future upgrade and scalability, the following are some common principles:

• Processor : Consider the power of the processor at peak time, and properly retain some buffering to ensure that the system has room for expansion when the business grows. If you want to maintain a fast response, you should reserve 20% to 40% of your CPU's headroom.

• memory : To consider memory for all applications running on this server, the memory required depends primarily on the number of users, the type of application, how the process is handled, and the amount of data processed by the application.

• disk : Estimate the amount of data for the actual user of the business to calculate the minimum number of disks, and do not forget to select a backup device (such as a tape drive).

• io Slots : Try to keep more IO slots to prevent future plugging of more PCI cards.

• Network : Select the appropriate network card, to ensure that networks are not the bottleneck of the system.

When evaluating database server performance, the most difficult thing is how to grasp the accuracy of the problem, exactly what factors to consider. Ideally, the following elements should be considered:

• Complexity of trading

• Trading rate

• Data read/write ratio

• Number of concurrent connections

• Number of concurrent transactions

• The size of the database maximum table

• Targets for performance metrics

Based on various benchmark test results and the detection of various production systems, the following table summarizes the utilization of CPU, disk, memory pages, network and virtual Storage page exchanges, and shows that a server is an ideal model if its utilization remains within the range indicated by good.

2. based on Rperf, evaluate the CPU of the database server

Rperf (Relative performance) is a business processing performance estimate derived from IBM's analytic model. This model simulates the operation of some systems, such as the central processing unit, cache, and memory, which do not simulate the input/output operation of the disk and the network. Although the parameters of the general database and operating system are used, the model does not reflect the specific database or AIX version. Unless otherwise stated separately, Rperf are estimated when the system is launched. The IBM pseries 640-b80 is the reference system and its value is this. Although Rperf can be used to compare commercial processing performance, the actual system energy may vary depending on a number of factors, including hardware configuration and software design and configuration.

Evaluating the performance of a database server requires understanding the type of transaction, peak times, number of users, and the number of transactions per user at peak times. If at peak time, there are three typical types of trading: light, general, heavy. Need to know the number of concurrent users for each transaction at peak time. The peak time is assumed to be: 10:00-11:00, and the number of transactions per user is as follows:

Light Trading =120 Trading/user

General Trading = 60 trades/user

Heavy trades = 15 trades/Users

2.1. CPU seconds per Exchange

After evaluating the type of transaction, it is necessary to evaluate the CPU seconds consumed by running each exchange, and if the B80 server is assumed to support 10 transactions per second, each transaction consumes 0.1 cpu seconds. If you do not know how to measure CPU seconds, the following tables are referenced according to the type of application.

2.2. Evaluate the Rperf value required by the server

Rperf value required by the server =sum (NU * TX * cs/pp)/MC

NU: Number of concurrent users at peak

TX: Number of trades per user at peak

CS: On rperf=1 servers, the CPU seconds required per exchange

PP: Peak duration of time

MC: Maximum CPU utilization (< 70% recommended)

The following is an example of how to calculate the required rperf value, assuming a company's situation is as follows:

peak Business hours : 10:00-11:00=1hour=3600 seconds

transaction type: Simple application with no complex queries

relative transaction type, number of users distribution : Light = 2000, General = 50, heavy =5

At peak hours, the number of trades per User:

Light =120 Trading/user

General =60 Trading/user

Heavy =15 Trading/user

for rperf=1 servers, CPU seconds per transaction response

The Light =1

General =3

Heavy =15.

the largest CPU Utilization : 60%

According to the above formula, the Rperf value corresponding to the different transaction types can be deduced.

Light trades: nu*tx*cs/pp=2000*120*1/3600=66.0

General trade: nu*tx*cs/pp=50*60*3/3600=2.5

Heavy trades: nu*tx*cs/pp=5*15*15/3600=0.3

Total rperf/mc= required (66.0+2.5+0.3)/0.7=98.3 Rperf

3. Estimate the CPU of the database server based on the TPC-C calculation

The TPC-C benchmark is a performance benchmark established by the transaction board to demonstrate online transaction processing performance (OLTP), which is measured to enable customers to evaluate the performance of different online transaction processing systems that run in a standard database in a controlled state.

The TPC-C test consists of 5 typical OLTP transactions, which are:

New Order: One user submits a new order

Payment: Update the user's account balance to reflect a payment

Delivery: Delivery of orders (implemented through a batch transaction process)

Order Status: Returns the status of the user's latest order

Inventory level: Monitor Current warehouse Inventory

TPC-C transaction processing is the transaction process implemented on a database of 9 tables including: UPDATE, INSERT, delete, terminate, and access to primary and secondary keys, each transaction 90% response time should be less than or equal to 5 seconds, where the inventory level response time can be within 20 seconds.

The throughput value of the tpc-c is a direct result of terminal activity level, such as 10 terminals per warehouse, 5 transactions are available at each terminal, and a remote terminal emulator is used to perform the necessary transaction blending during the performance testing process. This hybrid represents a complete business process for order Processing: entry, payment, inspection, delivery. More professionally, this necessary blending is defined as generating an equal number of new orders and payment transactions, as well as generating a delivery transaction in each of the 10 new order transactions, an order status check transaction, and an inventory level inspection transaction

The remote terminal emulator is also used to measure the response time of each transaction, as well as to simulate the typing time and think time, which is the time spent in data entry on the terminal, and the time taken by the operator to read the result of the transaction in the terminal before making the next transaction request. Every thing has a minimum of typing time and minimum think time. In addition, this response time must be under a given limit value.

The result of the TPC-C benchmark--tpc-c throughput (TPMC), which represents the maximum continuous performance of the system, is defined as how many new order transactions can be processed per minute by the system, while the system is also dealing with four other types of transactions (payment, order status, delivery, inventory level). All 5 tpc-c transactions have a limited user response time requirement, where the response time for a new order transaction is less than 5 seconds. So if the tpc-c value of a system is 100tpmc/min, it means that the system can generate 100 new order transactions at the same time that it handles the work of other mixed tpc-c transactions per minute.

3.1. How to use TPC-C for server evaluation

From the above, the TPC-C test benchmark is primarily used to test the number of online transactions that the host server can handle per minute, and the unit result that the test produces is the TPM value (Transaction per Minute, which is the number of transactions processed per minute).

Although the tpc-c objectively reflects the system processing performance of each computer manufacturer, and the test benchmark is constantly perfected to be closer to the real-world trading environment, it still cannot match the various practical applications, and the TPC Test host system has been optimized for the proper degree of system. Therefore, in the actual business application system select Host server by carrier, must take into account the various factors, in order to maximize the application system to achieve the production requirements.

The following formula is the empirical methodology summarized by IBM in the practical application of the financial integrated Business system, which basically reflects the requirements of the financial business characteristic to the host processing capability:

Tpm=task x 80% x S x F/(T x C)

which

TASK: Peak volume for daily business statistics

T: for daily peak trading hours, assume that 80% volumes per day are concentrated in 4 hours per day, or within 240 minutes:t=240.

S : The ratio of the complexity of the test benchmark environment transaction to the actual banking transaction operation relative to the standard tpc-c. Due to the complexity of the actual financial business transactions with TPC? There are significant differences in the trading of C standard tests, and a reasonable corresponding value should be set. In the case of ordinary savings transactions, a transaction often requires opening a large number of database tables at the same time, taking out its related data to operate, which is much more complex than the complexity of TPC-C standard transactions, and according to scientific statistics, This value can be set to 10~20 per trade operation compared to the complexity of each transaction in the TPC standard test.

C: handle the headroom for the host CPU. Practical experience shows that the CPU utilization of one host server is higher than 80%, which indicates that high CPU utilization will produce system bottleneck, while utilization rate is at 75%, it is in the best utilization state. Therefore, when calculating the host performance indicators, you must consider the CPU redundancy, set c=75%.

F: development of redundant reservations for the system's business volume over the next 3-5 years.

To sum up, in order to protect the online business processing performance requirements, we can calculate the host's required processing capacity, based on the corresponding model and configuration.

4, example, using TPC-C for database server evaluation

The following for the XYZ line of online banking business needs, we conduct database server selection analysis.

As the current XYZ line only 17 branches opened online banking business, according to our estimate, according to the current number of customers, all branches have opened online banking business, the total number of customers can reach 100,000. Considering the rapid development of the Internet in our country, the annual growth rate of the number of customers is calculated according to 50%, then the number of customers after 3 years will reach 100,000 x (1+50%) 3≈34 million.

At least half of these customers are individual customers, and the other half are corporate customers. Business customers trading frequency is relatively high, we average per enterprise customers to do 1.5 transactions per day calculation; The most common transactions for individual customers are inquiries, withdrawals, deposits, and monthly pay electricity, so we assume that the average individual client trades 4 times per month, so the daily volume is:

340,000 x50%x1.5+34 million x50%x (4÷30) ≈ 280,000 pen

Assuming that the transaction complexity of online banking reaches 15, the number of database operations per day reaches:

280,000 x15=420 million times

High Law litigation Fee Payment:

As the cost of litigation is not increasing, we calculate the annual increment rate by 5%. According to the statistics of the XYZ head office, a total of 37 branches nationwide, the amount of contributions to a larger branch can reach 25000 monthly, accounting for 20% of the total number of branches, the medium-paying province can reach 15000 monthly, accounting for 30% of the total number of branches, the province can reach a small contribution of 7000 per month, accounting for 50% of the total branch One months, 20 business days. In this way, the number of transactions per day after three years can be achieved:

(25000x20%+15000x30%+7000x50%) x37÷20x (1+5%) 3≈28740 pen

We assume that the transaction complexity of high-law litigation payment reaches 13, so the daily database operation achieves:

28740*13=373620 Times

4.1, the overall performance requirements:

The total number of database operations is: 4200000+373620=4573620

Assuming that 80% of daily transactions occur within 4 hours, the number of online transactions per minute in the peak trading hours is: 4573620x80%÷ (4x60) ≈15250

To reserve 40% of processing power for applications that will be added in the future, in addition, the performance of the system is better given the CPU's busy time of less than 70%, we will set this ratio at 65%. So the system tpc-c value should reach: 15250÷ (1-40%) ÷65%≈39000

4.2. Memory Capacity Requirement Analysis

First, according to the database capacity to calculate the required database cache size, and then estimate the operating system, system software and other required memory, the total is the required memory capacity.

NET Silver Data Volume Analysis:

The database of the XYZ head Office Online banking system consists of the CIF information, the transaction log, and the trading flow three parts.

Among them: CIF information, including enterprise customers and personal customer information, enterprise customer information average size of about 20K, personal customer information average size of about 5 K; each transaction must be recorded in the transaction log, the average size of the journal is about 4K; Each transfer transaction must be recorded in the transaction flow, the size of the transaction water is about 2K.

At least half of these customers are individual customers, and the other half are corporate customers. Business customers trading frequency is relatively high, we average per enterprise customers to do 1.5 transactions per day calculation; The most common transactions for individual customers are inquiries, withdrawals, deposits, and monthly pay electricity, so we assume that the average individual client trades 4 times per month, so the daily volume is:

All transaction logs and trade flows are retained for three months. Since the transfer transactions of individual clients are very small and negligible, it is assumed that the transfer transactions of corporate customers account for 70% of the total volume. We can calculate the requirements for the storage system capacity of online Banking:

CIF Information Capacity =20kx (340,000 x50%) +5kx (340,000 x50%) =3.25GB+421MB≈4GB

Transaction log capacity =[34 million X50%X1.5+34 million x50%x (4÷30)]x4kx30x3 =277667X4KX30X3≈95GB

Volume of trade flow = (340,000 x50%x1.5) X70%X2KX30X3≈30GB

XYZ Online Banking Overall data capacity requirements: =4GB+95GB+30GB=129GB

High law litigation Fee data Volume Analysis:

The high-law transaction data is retained for three years on request, with a size of 512 bytes per transaction and a total capacity of: (25000x20%+15000x30%+7000x50%) X37X12X3X0.5K≈8.2GB

Therefore, the total amount of data for the database is: 129GB+8.2GB=137.2GB

The database system has a good performance when the cache capacity reaches 5% of the total database capacity, so the database cache size is:6.86GB.

In order to calculate the system memory requirements are:

1.	Memory occupied by the AIX operating system	128MB
2.	The memory that the database management system occupies	256MB
3.	Memory for system software such as dual standby hot spares	128MB
4.	Memory occupied by the application	256MB
5.	Database Cache	6.86GB
6.	Reasonable Memory utilization	75%
	Total	10GB

4.3. Storage capacity Requirement Analysis

In addition to the storage capacity requirements for the above XYZ online banking system and the high-law litigation fee payment system, there are storage requirements for the asynchronous query download service.

Asynchronous query download service generates a download packet every 1 hours, the size of each packet is 3MB, the packet to be downloaded is a 10 o'clock in the morning generated packet, the packet needs to be saved for 2 years, and the other packets are saved for 3 months. Therefore, the storage capacity is:

23x3mx30x3+1x3mx365*2=6gb+2gb=8gb

In order to avoid the bottleneck of storage system performance, the utilization rate of system storage system should be less than 40%, it is recommended to store the data in mirror mode, so the total storage capacity is:

(137.2GB+8GB) ÷40%x2= 766GB

How to plan and select a database server: CPU, memory, disk, network (RPM)