Shulou(Shulou.com)06/01 Report--

Let's learn about some solutions to the high concurrency of MySQL. I believe you will benefit a lot after reading it. The text is not in the essence. I hope to solve some of the solutions of high concurrency in MySQL. This short article is what you want.

First, the criteria for judging large websites:

1.pv (page views) page views: the total number of visits to all pages of a site within 24 hours. Ten million, one million.

2. UV value (unique vistor) unique visitors: a website, how many users visit our website in 24 hours. Reach 100000

3. Independent ip (key content): a website, how many ip come to visit our website in 24 hours.

The uv value is about equal to the independent ip. If you want to consider a local area network, the UV value is slightly greater than the stand-alone ip

Second, some problems brought about by large websites

1. Large concurrency.

Concurrency: how many users visit our website at the same time (within 1 second). For the same URL, refresh the browser at the same time. When it reaches 500, it is very big.

If the concurrency is 500, what is the PV value? 500 million 3600 million 10 million 18000000

2. Large flow.

The large bandwidth required by the website. 10g.

3. Large storage.

The database in the website, the capacity of the table is a massive trend, gt level, how to quickly find out the desired data.

Third, the solution to large concurrency:

1. Load balancer:

Hardware: f5-bigip performance is relatively good, immediate results, expensive, generally suitable for large website companies, online game companies.

Software:

Lvs (linux virtual server) linux virtual service, added to the kernel of linux.

Nginx: can be a web server (apache), can also do load balancing.

3. Cluster:

The main purpose is to solve the single point of failure of the computer. in a cluster, only one computer is working, and the other computers are in a dormant state, monitoring the working computer. When there is a problem with the working computer, the dormant computer takes over the work immediately.

Extend:

A single point of failure (English: single point of failure, abbreviated spof) refers to a component in the system that fails at one point, which will make the whole system unable to operate. In other words, a single point of failure will lead to an overall failure.

4. High-traffic solutions:

1. Prevent the resources of our website from being stolen: we can use some non-technical measures to prevent it from being stolen, and add watermarks to the pictures.

2, reduce http requests: the main means is to merge js files, css files, background image files. Merge the style file or js file needed by the browser into a style file or js file. For example, taking a background image as an example, you can put some icons icons on a large background image and intercept an icons icon by position.

3. Enable compression: reduce the amount of data transferred. The common compression format is: gzip,deflate.

4, cache data content through the browser: there are some resources in the website, such as js files, css files, some picture files, which are updated less frequently. By setting the cache-control expires property of http to set the cache, you can set the file type and cache cycle of the cache.

5. You can set up a separate server for some resources that take up traffic, such as a picture server, a video server and so on.

5. Large storage solution:

1. Caching technology:

Through the cache technology, we can achieve the goal of not querying the database or querying the database less.

Computer access speed, memory "hard disk file" database

The main caching technologies are:

Disk cache (page static), which turns a page that queries a database into a page that does not query the database.

Memory cache: save frequently queried data in memory and query directly in memory the next time you query the data.

(memcache/redis/mysql 's memory engine)

2. When designing a table, it is necessary to meet the 3 paradigms:

The first paradigm is: atomicity, fields can no longer be divided. As long as it is a relational database, it automatically satisfies the first paradigm:

Classification of databases:

Relational database: the concept of rows and columns, two-dimensional tables. A common relational database: mysql,sql server,oracle,db2

Non-relational database (nosql) is oriented to collections and documents without the concept of rows and columns, such as redis/mongodb and so on.

The second paradigm: you cannot have exactly the same records in a table. You can set a primary key

The third normal form: the fields in the table cannot be stored redundant

3. To add an appropriate index to the table: the index is very important and can improve the query speed:

Common indexes are: primary key index, unique index, general index, full-text index.

4. To create appropriate stored procedures, functions, triggers, etc.

5. Read-write separation (master-slave server)

6. Sub-table technology (vertical segmentation and horizontal segmentation)

7. Partitioning technology: storing the data contents of a table in different areas

8. Upgrade mysql server (add configuration: increase content, 64-bit)

9 query *, to tune the sql statement * *: select * from tablename do not use this statement, query as needed. Query the data of which field you need.

10. Optimize the configuration file

For example, configure the concurrency of the mysql database: find the value of max_connections=100 in the mysql configuration file my.ini to control the concurrency of mysql.

After reading this article to solve some solutions for high concurrency in MySQL, many readers will want to know more about it. If you need more industry information, you can follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.