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

The following mainly brings you how to achieve normal optimization of MySQL database, I hope these contents can bring you practical use, which is also the main purpose of this article on how to edit MySQL database to achieve normal optimization. All right, don't talk too much nonsense, let's just read the following.

MySQL database optimization:

Foreword:

In a website architecture, the first bottleneck must be the database, followed by storage.

1. Hard disk optimization: do not use virtual machines, use physical machines (because databases are IO-intensive applications)

A, CPU 64-bit CPU, Baidu for example: a machine 8-16 CPU. Ordinary company: 2-4 cpu.

B, memory (mem) Baidu as an example: 96Gmur128Gpen 3-4 instances. Ordinary company: 32G-64G, run 2 examples.

C. the more disks (disk), the better. Performance: SSD (high concurrency) > SAS (general business online) > SATA (offline)

Raid 4 disks: raid0 > raid10 > raid5 > raid1

D, network card multi-piece network card bond, and buffer,tcp optimization.

2. Software optimization

Operating system: x86x64 system

Software: mysql compilation optimization

3. Optimization of parameters in my.cnf

Note: the optimization of parameters in my.cnf is very small, most of the architecture and SQL statements are optimized.

Thoughts:

Monitoring: production parameters are general parameters.

Command monitoring: show global status\ G

Tuning tool: mysqlreport

Case study: some MySQL errors skip-name-resolve:

Http://blog.chinaunix.net/uid-7354272-id-2643611.html

4. Optimization of SQL statement.

A, index optimization

1), whitelist mechanism-Baidu, project development, DBA participation, reduce the number of slow SQL after launch.

Catch slow SQL and configure my.cnf

Long_query_time = 2

Log-slow-queries=/data/3306/slow-log.log

Polling by day: slow-log.log

2) slow log analysis tool-mysqlsla (recommended)

MySQLdumpslow,mysqlsla,myprofi,mysql-explain-slow-log,mysqllogfilter comparison

3) analyze the slow query regularly at 0: 00 every evening and send it to the mailbox of core development, DBA analysis and senior operation and maintenance, CTO.

DBA analysis gives optimization recommendations-- > Core development confirms changes-> DBA online operation processing.

B. large and complex SQL statements are split into several small SQL statements.

Subquery, join connected table query, 40 million records of a table.

C. The database is the place where the data is stored, but not where the data is calculated.

The data calculation and application class processing should be solved by the front-end application. Processing on the database is prohibited.

D, search function, like'% old boy%', generally do not use mysql database.

Detailed optimization details of the SQL statement:

① can use fixed-length char type, do not use varchar type.

Try not to use select for ② database queries unless you want to check all fields.

Mysql > select id,name from test; do not need mysql > select from test

When querying ③ select, the column type after the where condition should be in quotation marks if it is a string type, but not if it is a numeric type.

④ if the first n characters of a conditional column are close to a unique value, you can create an index on the first n characters of a column, instead of creating an index on the entire column.

⑤ can create federated indexes, but pay attention to the prefix feature. If you want to do a composite index, put the fields that are most commonly used as commonly used condition columns first.

⑥ can use explain to view the execution plan of select statements, and slow log or show full processlist statements can be seen for a long time.

⑦ can be inserted in batches as soon as it can be inserted, not one by one.

Mysql > insert into test values (1), (2) (2), (3), (4), (5)

⑧ uses set profiles to view the execution details of the SQL statement.

5. Optimization of architecture

1), business split: search function, like'% old boy%', generally do not use mysql database.

2), business split: some business applications use nosql for persistent storage, for example: memcahcedb,redis,ttserver

Fan attention, friend relationship and so on.

3), cache must be added to the front end of the database, for example: memcached, user login, product query.

4). Dynamic static data. The whole file is static, and the page fragment is static.

5). Database cluster is separated from reading and writing. One master, multiple slaves, cluster read and write separation through programs or dbproxy.

6), the single table exceeds 20 million. Dismantle the table in the library, dismantle the table manually (login, goods, orders)

7), Baidu, the former domestic × × company of Ali, will do this.

6. Process, system and safety optimization

Any update of a man-made database record has to follow a process:

A, human process: development-- > core development-- > operation and maintenance or DBA

B. Test process: intranet test-> IDC test-> online execution

C, client management, phpmyadmin

About the innodb_flush_log_at_trx_commit parameters of mysql:

Innodb_flush_log_at_trx_commit = 1

Parameter explanation:

0:log buffer will be written to log file once a second, and the flush (brush to disk) operation of log file will take place at the same time. In this mode, writing to disk is not actively triggered when the transaction commits.

1: every time a transaction commits, MySQL will write the data of log buffer to log file and flush (swipe to disk). This mode is the system default.

2: MySQL writes log buffer data to log file each time the transaction is committed, but the flush (flush to disk) operation does not occur at the same time. In this mode, MySQL performs a flush (flush to disk) operation once a second.

Parameter modification:

Locate the mysql configuration file mysql.cnf, modify it to the appropriate value, and restart mysql.

Note:

When set to 0, this mode is the fastest, but not full, and the crash of the mysqld process will result in the loss of all transaction data in the previous second.

When set to 1, this mode is the safest, but also the slowest. In the case of a mysqld service crash or a CVM host crash, binary log can only lose at most one statement or transaction.

When set to 2, this mode is faster and safer than 0, and all transaction data can be lost in the last second only if the operating system crashes or the system is powered off.

For the above about how to achieve normal optimization of MySQL database, 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.