Home >Database >Mysql Tutorial >What should I do if mysql stored procedure is too slow?

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

王林
王林Original
2020-09-27 14:48:323571browse

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.

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

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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn