Performance Optimization-mysql database

Performance Optimization-mysql database

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