How do CPU resources and available memory size affect database performance?

What this article brings to you is about the impact of CPU resources and available memory size on database performance? It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

Preface

There are several points that may affect database performance. One of them is the server hardware, which is also the CPU and available memory discussed in this section.

Introduction

When hot data exceeds the available memory size, the MemCache storage engine cache layer is prone to failure (when a large number of cache failures occur, a large amount of network transmission is likely to occur), thus affecting the performance of the server.

When this type of I/O system bottleneck occurs, we need to upgrade the I/O subsystem to add more memory. Network and I/O resources are the second impact on the performance of our database. Hardware factors.

CPU Selection

Purchasers often ask us what parameters of CPU hardware they need to buy, etc.

I will buy the best whatever I want! But for things like cost, you can only choose one.

So, do we need frequency or quantity of CPU?

First we should know whether our application is CPU-intensive?

In fact, most of the time we have to choose better rather than more
For the current version of MYSQL, multiple CPUs are not supported for concurrent processing of the same SQL

Of course, we also have to How about the concurrency of the system? (Concurrency is at the nanosecond level)

QPS, an indicator that measures database processing capabilities: the number of SQLs processed simultaneously (here is per second)

MYSQL is currently used in a large number of Web applications , the concurrency of this type of application is also very large, so the number of CPU cores is more important than the frequency

At the same time, the version of MYSQL used must also be considered (higher versions have better support for multi-core CPUs, at least you You can safely use a 16-core or 32-core CPU)

Of course, if you want to use a multi-core CPU, you also need to use the latest version of MYSQL to achieve the best performance.

About memory

MYSQL itself is single-threaded

• The size of memory directly affects the performance of MYSQL

• Storing data into the cache can greatly improve the performance of the data

Commonly used MYSQL storage engine

MyISAM stores the index in memory and the data in In the operating system,
InnoDB stores indexes and data in memory at the same time, thereby improving the efficiency of database operation.

Memory configuration tips

1. Although the more memory, the better, but it is not good for performance. The impact is limited and cannot increase performance infinitely
2. Of course, excess memory can increase the performance of other services such as the operating system

Cache is beneficial to reading, and it is also beneficial to writing operations.

CPU -》Memory-》Disk

The cache can delay write operations and turn multiple writes into one write (Eg: View Counter

The above is the detailed content of How do CPU resources and available memory size affect database performance?. For more information, please follow other related articles on the PHP Chinese website!