Performance Optimization--mysql Database

A MySQL common command

1. Open the Log

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

2)set global general_log=on;

3)set global general_log=off;

2. MySQL If you open set autocommit=0, then all the statements must be in one transaction

3. Show Engine InnoDB status

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

Analysis of two typical problems

1. Database waits for lock timeout lock wait timeout exceeded; Try restarting transaction

1). Adjust the order of execution of the statements, reduce the lock wait time: More database operations involved, the transaction is relatively large, these locks time-out table (multiple transactions may take the same row of table, there will be a lock wait situation) on the last side of the transaction, the completion of the release of locks, reduce the lock wait time for other transactions
2). Slicing transactions:
3). Statement merging, reducing database network interaction: Multiple statements, and together, using Multiquery, one query update

2. Deadlock issues

3. View issues that have not been submitted by the transaction

Mysql> Show engine InnoDB status \g

Querying the transaction ID for active