0

I have activated slow logs in mysql 5.7.30. I notice some SP's calls are not highlighting in slow logs even its taking more then time to my threshold value which is 2-sec. On investigation, I found the calls which are using temporary tables are not highlighting into slow logs eg below query taking 30-sec but this SP is not highlighting into slow logs:

INSERT INTO temp_media(pacs_media_id, pacs_users_id)
    SELECT p.id, pu.id
    FROM media p
    INNER JOIN users pu ON pu.token_client_id=p.token_client_id
                AND pu.token_location_id=p.token_location_id
                AND pu.user_ref_id=p.entity_ref_id
                -- AND pu.token_client_id IN (812,525, 141,44,69) -- 1104
    INNER JOIN clients pc ON pc.token_client_id=pu.token_client_id
                AND pc.is_active=1
                -- AND pc.process_id=p_process_id
                AND FIND_IN_SET(pu.user_type, pc.user_types)
                AND pu.user_type IS NOT NULL
    WHERE   p.entity_name_id=1 -- 1: users
        AND p.is_processed=0
        -- AND p.id % p_thread_total=p_thread_no
    LIMIT 100
Irfi
  • 49
  • 2
  • 6
  • Additional information request, please. RAM size, # cores, any SSD or NVME devices on MySQL Host server? Post TEXT data on justpaste.it and share the links. From your SSH login root, Text results of: A) SELECT COUNT(*) FROM information_schema.tables; B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) STATUS; not SHOW STATUS, just STATUS; G) SHOW ENGINE INNODB STATUS; for server in-depth analysis of all Slow Log settings to provide suggestions. – Wilson Hauck Aug 10 '22 at 11:41
  • we are on cloud with 256G and 32cores. – Irfi Aug 15 '22 at 16:59
  • Please post other requested information to allow in-depth analysis of all Slow Log settings to determine why you have nothing in the slow query data. – Wilson Hauck Aug 15 '22 at 17:27

1 Answers1

0

(Not what you asked about, but...) These might speed it up some:

p:  INDEX(entity_name_id, is_processed)
pu:  INDEX(token_client_id)
pc:  INDEX(token_client_id,  is_active, process_id, user_types)

Meanwhile, it "worked for me" on Version 8.0.30.

slow log (cf Comment)

mysql> create temporary table tt select *, sleep(1) from abc limit 5;
Query OK, 5 rows affected (5.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 0.600000 |
+-----------------+----------+
1 row in set (0.02 sec)

mysql> select *, cast(sql_text as char) from mysql.slow_log where start_time = '2022-08-15 21:45:17.743652'\G
*************************** 1. row ***************************
            start_time: 2022-08-15 21:45:17.743652
             user_host: root[root] @ localhost []
            query_time: 00:00:05.010076
             lock_time: 00:00:00.000023
             rows_sent: 0
         rows_examined: 5
                    db: try
        last_insert_id: 0
             insert_id: 0
             server_id: 1
              sql_text: 0x6372656174652074656D706F72617279207461626C652074742073656C656374202A2C20736C6565702831292066726F6D20616263206C696D69742035
             thread_id: 5655
cast(sql_text as char): create temporary table tt select *, sleep(1) from abc limit 5
Rick James
  • 135,179
  • 13
  • 127
  • 222
  • My question simple. If i use temporary table and its take more than 2 seconds (as per slow query settings) then its not trapping into slow log table of mysql schema. Hopefully you would understand my concern. – Irfi Aug 15 '22 at 17:01
  • 1
    @Irfi - Hmmm... "Works for me". See what I added. That was run on MySQL 8.0.30 with the slowlog captured into TABLE. Note that the 5.01 seconds is longer than long_query_time (0.60). OK, maybe the CALL to a Stored Proc is part of the issue. I suggest you build a trivial test case like mine and write another Question with all the info to build a test case. (`SLEEP()` is a trivial way to stretch the time without needing a huge table.) – Rick James Aug 16 '22 at 04:54