Performance Optimization-mysql database and performance optimization mysql

Performance Optimization-mysql database and performance optimization mysql

I. mysql Common commands

1. Open the log

1) show global variables like "% genera % ";

2) set global general_log = on;

3) set global general_log = off;

2. If set autocommit = 0 is enabled for mysql, all statements must be in one transaction.

3. show engine innodb status

1) http://imysql.cn/2008_05_22_walk_through_show_innodb_status

Typical Problem Analysis

1. Database waiting Lock timeout Lock wait timeout exceeded; try restarting transaction

1 ). adjust the statement execution sequence to reduce lock wait time: There are many database operations involved, and the transaction is large. Tables with lock timeout (multiple transaction concurrency may take tables with the same row, there will be a lock wait situation) at the end of the transaction, the lock will be released after execution, reducing the lock wait time of other transactions
2). Split the transaction:
3) Statement merge to reduce network interaction between databases: multiple statements are combined, and multiQuery is used for one query update.

2. DeadLock Problems

3. Check that the transaction has not been committed.

Mysql> show engine innodb status \ G

Query the active transaction id

What skills can be used to optimize MySQL database performance?

1. Storage engine selection if the data table requires transaction processing, InnoDB should be considered because it fully complies with the ACID feature. If no transaction is needed, it is wise to use the default storage engine MyISAM. And do not try to use both storage engines. Think about it: in a transaction, some data tables use InnoDB, while others use MyISAM. What about the results? The entire subject will be canceled, and only those in the transaction processing will be taken back to the original state, and the rest of the committed data will be transferred to the memory, which will lead to conflicts throughout the database. However, there is a simple method to take advantage of the advantages of both storage engines. Currently, most MySQL suites include InnoDB, compiler, and linked list. If you choose MyISAM, you can still download InnoDB separately and use it as a plug-in. It's a simple method, isn't it?
2. Counting if the data table uses a storage engine that supports transaction processing (such as InnoDB), you should not use COUNT (*) to calculate the number of rows in the data table. This is because the product database uses COUNT (*) and returns an approximate value at most, because at a specific time, some transactions are always running. If you use COUNT (*), it will obviously produce a bug, and this error result will appear.
3. The most difficult problem of repeatedly testing the query is that no matter how careful the query is, errors will always occur and bugs will occur. On the contrary, the problem is that when a bug occurs in most cases, the application or database is online. There is indeed no practical solution to this issue unless you run the test sample on an application or database. Any database query can only be recognized after a large number of sample tests with thousands of records.
4. Avoid full table scan. Generally, full table scan is used if MySQL (or other relational database models) needs to search for or scan any specific records in the data table. In addition, the simplest method is to use an index table to solve the problem of low efficiency caused by full table scan. However, as we can see in subsequent problems, this has an error section.
5. Use "EXPLAIN" for query. When debugging is required, EXPLAIN is a good command. The following describes EXPLAIN in depth.
Reference: hi.baidu.com/...3.html

How can we optimize MySQL database performance? Example

In the mysql installation directory, for example, D: \ Program Files \ MySQL Server 5.1
There are several configuration files, as long as you change the name to my. ini, for example:
My-huge.ini giant servers
My-large.ini large
Medium my-medium.ini
Small my-small.ini
Back up the original one, rename it, and restart it. [Mysqld] is the configuration information of the mysql server.