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

The following mainly brings you the main optimization of enterprise production mysql. I hope these contents can bring you practical use. This is also the main purpose of this article that I edit enterprise production mysql. All right, don't talk too much nonsense, let's just read the following.

Reference answer:

A: hardware optimization:

1. Use at least 4 64-bit cpu,cpu. The bigger the L2 cache, the better.

2. Large memory, running 1-2 instances in 32-64G and 3-4 instances in 96-128g

3. Choose SAS disk for mechanical disk, rotate speed above 15000, and use ssd if possible.

4. Raid10 is used for raid cards

5. Multiple network cards, more than gigabytes

6. Do not use virtualization for database. Slave hardware is better than master.

B: operating system optimization

1. The operating system chooses x86164-bit and uses xfs file system as far as possible.

2. Optimize disk storage parameters

3. Optimize kernel parameters

4. Optimize the network, etc.

Optimization of c:mysql framework

1. Configure the CVM to run multiple instances according to the memory size

2. Master-slave replication adopts mixed mode. Try not to synchronize across computer rooms. If you want to cross computer rooms, try to write remotely and read locally.

3. Check and repair the data differences between master and slave replication regularly.

4. Split the business, and the search function is not performed by MySQL database; some services with high concurrency and general security use nosql, such as memcache, redis, etc.

5. Add cache to the front end of the database, such as memcache, for user login and product query

6. Static dynamic data, the whole file and page fragments.

7. Database cluster read-write separation, one master and multiple slaves, and cluster read-write separation through dbproxy

8. When a single table exceeds 8 million, dismantle the library, such as manually dismantling (login, goods, orders).

9. Choose the backup from the database, and back up the database by table and database.

Optimization at d:MySQL database level

1. Optimize my.cnf parameters.

2. Optimize the design of database table, including character set, string length, creating short index and multi-purpose compound index.

3. Optimize SQL statements to reduce the number of slow sentences.

E: database management process and system optimization

1. Human process: development-> Core Operation and maintenance / DBA

2. Test process: intranet IDC test is executed online.

3. Client management, PHPMYADMIN

F:MySQL database security optimization

1. Database forbids setting public network

2. Optimization of database file permissions

3. Limit the permissions of authorized users, and try their best to use dedicated users of dedicated libraries.

4. Restrict the operation authority of the developer to the production library.

5. Prevent SQL statements from being injected

For the above about the main optimization of enterprise production mysql, we do not think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.