Shulou(Shulou.com)06/01 Report--

This article mainly introduces how mysql starts the slow check log, what it involves, from the theoretical knowledge to learn, there are many books, literature for everyone to refer to, from the practical point of view, accumulated years of practical experience can be shared with you.

SQL and index optimization

Optimization of database table structure

system configuration

hardware

mysql> show variables like 'slow_query_log';+----------------+-------+| Variable_name | Value |+----------------+-------+| slow_query_log | OFF |+----------------+-------+1 row in set (0.00 sec)

Slow log query is not open, you can open it.

Find my.cnf and add sudo vim /usr/local/mysql/my.cnf

Add Slow Log Query

log_output=fileslow_query_log=onslow_query_log_file = /tmp/mysql-slow.loglog_queries_not_using_indexes=onlong_query_time = 1

Restart MySQL.

Determine if the modification was successful Start Slow Log Query

mysql> show variables like 'slow_query_log';+----------------+-------+| Variable_name | Value |+----------------+-------+| slow_query_log | ON |+----------------+-------+1 row in set (0.00 sec)mysql> show variables like '%quer%';+----------------------------------------+------------------------------------+| Variable_name | Value |+----------------------------------------+------------------------------------+| binlog_rows_query_log_events | OFF || ft_query_expansion_limit | 20 || have_query_cache | YES || log_queries_not_using_indexes | ON || log_throttle_queries_not_using_indexes | 0 || long_query_time | 1.000000 || query_alloc_block_size | 8192 || query_cache_limit | 1048576 || query_cache_min_res_unit | 4096 || query_cache_size | 8388608 || query_cache_type | OFF || query_cache_wlock_invalidate | OFF || query_prealloc_size | 8192 || slow_query_log | ON || slow_query_log_file | /tmp/mysql-slow.log |

+----------------------------------------+------------------------------------+

Execute the following SQL statement to view the status of mysql slow queries

show variables like '%slow%';

In fact, it is difficult to execute a slow query with practical significance, because when you test yourself, even if you query a massive table with 200,000 data, it only takes 0.9 seconds. We can replace it by the following statement:

SELECT SLEEP(10);

Check the number of slow queries by using the following sql statement to see how many slow queries have been executed:

show global status like '%slow%';

At this time, look again, has opened the slow query log.

$ sudo cat /tmp/mysql-slow.log /usr/local/mysql/bin/mysqld, Version: 5.6.40-log (Source distribution). started with:Tcp port: 3306 Unix socket: /tmp/mysql.sockTime Id Command Argument

If you change the log directory location

For example, change to/home/msyql/log/mysql-slow.log

At a minimum, you need to set the log directory owner to mysql:mysql.

When you restart mysql in this way, the mysql-slow.log file can be automatically generated.

jiqing@jiqing-pad:/home/mysql/log$ ll Total Usage 12drwxr-xr-x 2 mysql mysql 4096 May 31 01:46 ./ drwxr-xr-x 3 jiqing jiqing 4096 May 31 01:26 ../- rw-rw---- 1 mysql mysql 175 May 31 01:46 mysql-slow.logjiqing@jiqing-pad:/home/mysql/log$ pwd/home/mysql/log

Log files cannot be generated if the log directory is not mysql user privileges, mysql user does not have permission to write data.

Read the above introduction mysql how to start slow check log, hope to give you some help in the actual application. Due to the limited space in this article, it is inevitable that there will be deficiencies and needs to be supplemented. You can continue to pay attention to the industry information section and regularly update industry news and knowledge. If you need more professional answers, you can contact us at the official website for 24 hours before and after sales.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.