How to solve the problem that mysql stored procedures are too slow

Editor to share with you how to solve the problem of mysql stored procedure is too slow, I believe that most people do not understand, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

The solution to the slow mysql stored procedure is: first open the my.cnf configuration file; then add the configuration [long_query_time=1]; then monitor the sql; through the [tail-f / tmp/logs/mysqld.log] command and finally optimize it.

Solution:

Step 1: modify the / etc/my.cnf file, find [mysqld] and add it.

# sqllog=/tmp/logs/mysqld.log executed # record the sql execution time exceeding the following set time sqllog-slow-queries = / tmp/mysqlslowquery.log# execution time greater than or equal to 1 second long_query_time = 1

Then you can tail-f / tmp/logs/mysqld.log to monitor all executed sql, and the same method can monitor mysqlslowquery.log for sql statements whose execution time exceeds long_query_time = 1 (seconds).

For example, through the first step, we find a mysql custom function that executes slow func_getDevice (); it executes for 15 seconds, but we don't know which sql in this method affects performance, so there is the second step.

Step 2: enter the mysql command line and type

Mysql > set profiling=1;mysql > select func_getDevice (1); mysql > show profiles +-- + | Query_ID | Duration | Query | +-- + | 1 | 0 .00 | select * from TDevice | +-+ 1 row in set (00250400 sec)

At this point, you will see a detailed list of sql execution, but only 15 sql are recorded by default. If there are more sql in the method, you can set the

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

It's time to see exactly which sql statement affects performance, such as Query_ID=1 select * from TDevice.

Mysql > show profile for query 1 Take a detailed look at the time spent executing a 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 index of the table is reasonable, through targeted optimization to improve efficiency.

The above is all the contents of how to solve the problem that the mysql stored procedure is too slow. Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!