What should I do if mysql stored procedure is too slow?

Solution to the problem that mysql stored procedure is too slow: First open the my.cnf configuration file; then add the configuration [long_query_time=1]; then monitor sql through the [tail -f /tmp/logs/mysqld.log] command ; Finally, targeted optimization can be carried out.

Solution:

(Recommended tutorial: mysql video tutorial)

First step : Modify the /etc/my.cnf file, find [mysqld] and add

#执行的sql
log=/tmp/logs/mysqld.log 
#记录sql执行超过下面设置时间的sql
log-slow-queries = /tmp/mysqlslowquery.log
#执行时间大于等于1秒
long_query_time = 1

. Then you can tail -f /tmp/logs/mysqld.log to monitor all executed sql. The same method can monitor mysqlslowquery. log is the sql statement whose execution time exceeds long_query_time = 1 (second)

For example, through the first step, we found that a certain mysql custom function executed slowly func_getDevice(); executed for 15s, but we did not know this method Which SQL statement affects performance? Then there is the second step.

Step 2: Enter the mysql command line and enter

mysql> set profiling=1;
mysql> select func_getDevice(1);
mysql> show profiles;
+----------+------------+-----------------------+
| Query_ID | Duration   | Query                 |
+----------+------------+-----------------------+
|        1 | 0.00250400 | select * from TDevice |
+----------+------------+-----------------------+
1 row in set (0.00 sec)

At this time you will see a detailed sql execution list, but only 15 sql records are recorded by default. If the sql in the method More, then you can set

mysql> set profiling_history_size=20;
mysql> show variables like 'profiling%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| profiling              | ON    |
| profiling_history_size | 15    |
+------------------------+-------+
2 rows in set (0.00 sec)
mysql> select func_getDevice(1);
mysql> show profiles;

At this time, you can accurately see which sql statement affects the performance, such as Query_ID=1 select * from TDevice affects the performance;

mysql> show profile for query 1;详细查看执行一条sql的耗时情况
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| (initialization)               | 0.000003 | 
| checking query cache for query | 0.000042 | 
| Opening tables                 | 0.00001 | 
| System lock                    | 0.000004 | 
| Table lock                     | 0.000025 | 
| init                           | 0.000009 | 
| optimizing                     | 0.000003 |

Check whether the indexes of the table are reasonable and improve efficiency through targeted optimization.

Related recommendations: php training

The above is the detailed content of What should I do if mysql stored procedure is too slow?. For more information, please follow other related articles on the PHP Chinese website!