Why not record slow queries ?, Slow recording

Why not record slow queries ?, Slow recording
(I) Background:

At on May 18, I received a message from the front-end about player disconnection. I checked the CPU, slow query, and DB Request volume. No exception was found. The DB performance was as usual.

(Ii) Identify the cause:

INSERT INTO t (col1, col2, col3, col4, col5, col6, col7) VALUES ('3532082239485507011_130_99', '130_99', 130, 99, 3532082239485507011, 2172353000317425008, 29078) 

This long transaction did not exit after being executed for more than one hour, Kill the corresponding process, and the program frontend returns to normal.


(Iii) Handling ideas:

(1) query view

select trx_id,trx_state,trx_started,trx_requested_lock_id,trx_weight,trx_mysql_thread_id from information_schema.innodb_trx where trx_state='RUNNING';

(2) show engine innodb status \ G; follow Transactions to extract SQL and transaction information

(Iv) My questions:


Why is the SQL statement executed for more than one hour not recorded in the slow query log ??


(V) Original:


Query_time-Lock_time> long_query_time <= record
Query_time-Lock_time <long_query_time <= not recorded


(Vi) Simulation Scenario:


(1) Query_time-Lock_time> long_query_time

Session_A：mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select emp_no,hire_date from employees where emp_no=10170 for update;+--------+------------+| emp_no | hire_date  |+--------+------------+|  10170 | 1986-01-02 |+--------+------------+1 row in set (0.00 sec)Session_B:mysql> select emp_no,hire_date,sleep(3) from employees where emp_no=10170 for update;

After A period of time, commit is performed on A and B is executed and recorded in the slow log:

# Time: 140818 22:37:31# User@Host: root[root] @ localhost []  Id:     1# Query_time: 3.049016  Lock_time: 0.018891 Rows_sent: 1  Rows_examined: 1use employees;SET timestamp=1408372651;select emp_no,hire_date,sleep(3) from employees where emp_no=10170 for update;

(2) Query_time-Lock_time <long_query_time

Session_A：mysql> begin;Query OK, 0 rows affected (0.00 sec)mysql> select emp_no,hire_date from employees where emp_no=10170 for update;+--------+------------+| emp_no | hire_date  |+--------+------------+|  10170 | 1986-01-02 |+--------+------------+1 row in set (0.00 sec)Session_B:mysql> select emp_no,hire_date from employees where emp_no=10170 for update;

After A period of time, commit will be performed on A, and B will execute but will not be recorded in slow log.

(Vii) My gains:

Our daily performance analysis should actually include two aspects:

1) execution time-based Analysis

2) wait time-based Analysis

By water

Good Luck!

How to Set slow query logs? By default, mysql does not record slow query logs. How can I set it to record slow queries?

Linux:

Add

Log-slow-queries =/var/lib/mysql/slowquery. log (specify the location where the log file is stored, which can be empty and the system will give a default file host_name-slow.log)
Long_query_time = 2 (the time when the record exceeds, the default value is 10 s)
Log-queries-not-using-indexes)
Log-long-format (if it is set, all queries without indexes will be recorded)

Windows:

Add the following statement in [mysqld] Of my. ini:
Log-slow-queries = E: \ web \ mysql \ log \ mysqlslowquery. log
Long_query_time = 2 (other parameters above)

Why is my network speed suddenly slow? How can I check if my network is hacked?

The slow speed may not be caused by network attacks because of many reasons.

First, are you using a single line or sharing bandwidth with others? Sharing may be caused by vro settings.

If it is a single line that may be related to the instability of the local line, it is recommended to call for consultation.

If it is not a line problem, it is possible that viruses or Trojans are in the computer, and antivirus software is used for antivirus. Or use similar software such as 360 security guard and optimization master to clear system spam. If it is still slow, you can reinstall the system.