Introduction to Enterprise Production MySQL Optimization

Compared with other large databases such as Oracle, DB2, SQL Server, etc., MySQL has its own shortcomings, but this does not reduce its popularity at all. For general individual users and small and medium-sized enterprises, the functions provided by MySQL are more than enough, and because MySQL is open source software, it can greatly reduce the total cost of ownership.

How does an enterprise optimize MySQL in production?

Reference answer:

a: Hardware optimization:

1. Use 64-bit CPU, at least 4 CPUs, and the larger the L2 cache, the better
2. The memory should be large. 32-64G can run 1-2 instances, and 96-128G can run 3-4 instances.
3. Use sas disk for mechanical disk, with a rotation speed of more than 15000, and use ssd if possible
4. Use raid10 for raid card
5. Multiple network cards, Gigabit or above
6. Do not use virtualization for databases, and the slave hardware should be better than the master

b: Operating system optimization

1. Choose x86_64 as the operating system and try to use xfs file system
2. Optimize disk storage parameters
3. Optimize kernel parameters
4. Optimize network, etc.

c: mysql architecture optimization

1. Configure the server to run multiple instances based on memory size
2. Master-slave replication uses mixed mode. Try not to synchronize across data centers. If you need to synchronize across data centers, try to use remote writing and local reading.
3. Regularly check and repair data differences between master and slave replication
4. Business splitting, search function does not use MySQL database to execute; some high-concurrency and general security businesses use nosql, such as memcache, redis, etc.
5. Add cache to the database front end, such as memcache, for user login and product query
6. Staticize dynamic data, staticize the entire file, and staticize page fragments
7. Database cluster read-write separation, one master and multiple slaves, cluster read-write separation through dbproxy
8. If a single table exceeds 8 million, the database and table will be split, such as manually splitting (login, product, order) into tables and databases.
9. Select backup from the database and perform backup of the database in separate tables and databases

d: MySQL database level optimization

1. Optimize my.cnf parameters
2. Optimize the design of database tables, including character sets, string length, creation of short indexes, and multiple use of composite indexes;
3. Optimize SQL statements and reduce the number of slow statements;

e: Database management process and system optimization

1. Human process: Development -> Core operation and maintenance/DBA
2. Test process: Intranet IDC test is performed online
3. Client management, PHPMYADMIN

f: MySQL database security optimization

1. The database is prohibited from setting up an external network
2. Optimize database file permissions;
3. Limit the permissions of authorized users, and try to use dedicated databases and dedicated users
4. Limit the operating rights of developers to the production database
5. Prevent SQL statement injection

Summarize

This is the end of this article on enterprise production MySQL optimization introduction. I hope it will be helpful to everyone. Interested friends can refer to: Detailed explanation of Oracle paging query performance optimization code, analysis of key points of ORACLE SQL statement optimization technology, introduction to C/C++ compiler optimization, etc. If you have any questions, you can leave a message at any time and the editor will reply to you in time.