MySQL Quick Analysis

mysql_quick_analysis.sql

  SELECT
      ROW_NUMBER() OVER (ORDER BY section_order, priority) AS row_num,
      section, metric, user_info, value, details, status,
      query_text, problem_indicator
  FROM (
      -- SECTION 1: BLOCKING CHAINS AND LOCK ISSUES
      (SELECT
          1 as section_order, 1 as priority,
          'BLOCKING' as section,
          'Lock Chain' as metric,
          CONCAT(p.USER, '@', p.HOST) as user_info,
          CONCAT('PID ', p.ID, ' -> ', IFNULL(CAST(p2.ID AS CHAR), 'None')) as value,
          CONCAT('Time: ', p.TIME, 's | DB: ', IFNULL(p.DB, 'none')) as details,
          CASE WHEN p.TIME > 30 THEN 'CRITICAL' WHEN p.TIME > 10 THEN 'WARNING' ELSE 'INFO' END as status,
          LEFT(p.INFO, 200) as query_text,
          CASE
              WHEN p.INFO LIKE '%LOCK TABLES%' THEN 'LOCK TABLES statement holding lock'
              WHEN p.INFO LIKE '%FOR UPDATE%' THEN 'FOR UPDATE clause causing lock'
              WHEN p.INFO LIKE '%LOCK IN SHARE MODE%' THEN 'LOCK IN SHARE MODE holding shared lock'
              ELSE 'Blocking transaction'
          END as problem_indicator
      FROM INFORMATION_SCHEMA.PROCESSLIST p
      LEFT JOIN INFORMATION_SCHEMA.PROCESSLIST p2 ON p.ID = p2.ID AND p2.STATE = 'Waiting for table metadata lock'
      WHERE p.COMMAND != 'Sleep' AND EXISTS (
          SELECT 1 FROM INFORMATION_SCHEMA.PROCESSLIST p3
          WHERE p3.STATE LIKE '%lock%' AND p3.ID != p.ID
      )
      LIMIT 5)

      UNION ALL

      -- SECTION 2: SLOW QUERIES WITH PROBLEM ANALYSIS
      (SELECT
          2 as section_order, ROW_NUMBER() OVER (ORDER BY p.TIME DESC) as priority,
          'SLOW QUERY' as section,
          CONCAT('PID ', p.ID) as metric,
          CONCAT(p.USER, '@', p.HOST) as user_info,
          CONCAT(p.TIME, 's runtime') as value,
          CONCAT('State: ', p.STATE, ' | DB: ', IFNULL(p.DB, 'none')) as details,
          CASE WHEN p.TIME > 60 THEN 'CRITICAL' WHEN p.TIME > 30 THEN 'WARNING' ELSE 'INFO' END as status,
          LEFT(p.INFO, 300) as query_text,
          CASE
              WHEN p.INFO LIKE '%SELECT%' AND p.INFO NOT LIKE '%WHERE%' THEN 'Missing WHERE clause - full table scan'
              WHEN p.INFO LIKE '%JOIN%' AND p.INFO NOT LIKE '%ON%' THEN 'JOIN without ON clause - cartesian product'
              WHEN p.INFO LIKE '%SELECT * %' THEN 'SELECT * - fetching all columns unnecessarily'
              WHEN p.INFO LIKE '%NOT IN%' THEN 'NOT IN clause - consider using NOT EXISTS'
              WHEN p.INFO LIKE '%LIKE ''%%' OR p.INFO LIKE '%LIKE "%"%' THEN 'Leading wildcard in LIKE - cannot use index'
              WHEN p.INFO LIKE '%OR%' AND p.INFO LIKE '%WHERE%' THEN 'OR in WHERE clause - may prevent index usage'
              WHEN p.INFO LIKE '%GROUP BY%' AND p.INFO LIKE '%ORDER BY%' THEN 'GROUP BY with ORDER BY - may cause filesort'
              WHEN p.INFO LIKE '%DISTINCT%' THEN 'DISTINCT - verify if necessary, may cause temp table'
              WHEN p.STATE LIKE '%Sending data%' AND p.TIME > 10 THEN 'Long "Sending data" - large result set or missing index'
              WHEN p.STATE LIKE '%Creating sort index%' THEN 'Creating sort index - missing index for ORDER BY'
              WHEN p.STATE LIKE '%Copying to tmp table%' THEN 'Creating temp table - query needs optimization'
              ELSE 'Long running query - check execution plan'
          END as problem_indicator
      FROM INFORMATION_SCHEMA.PROCESSLIST p
      WHERE p.COMMAND = 'Query'
          AND p.TIME > 5
          AND p.INFO IS NOT NULL
          AND p.INFO NOT LIKE '%INFORMATION_SCHEMA%'
      ORDER BY p.TIME DESC
      LIMIT 10)

      UNION ALL

      -- SECTION 3: PROBLEMATIC QUERY PATTERNS
      (SELECT
          3 as section_order, ROW_NUMBER() OVER (ORDER BY p.TIME DESC) as priority,
          'QUERY PATTERN' as section,
          CONCAT('PID ', p.ID) as metric,
          CONCAT(p.USER, '@', p.HOST) as user_info,
          CONCAT('Time: ', p.TIME, 's') as value,
          CONCAT('State: ', p.STATE, ' | DB: ', IFNULL(p.DB, 'none')) as details,
          'WARNING' as status,
          LEFT(p.INFO, 300) as query_text,
          CASE
              WHEN p.INFO LIKE '%JOIN%JOIN%JOIN%' THEN 'Multiple JOINs - check join order and indexes'
              WHEN p.INFO LIKE '%UNION%' AND p.INFO NOT LIKE '%UNION ALL%' THEN 'UNION without ALL - adds deduplication overhead'
              WHEN p.INFO LIKE '%HAVING%' AND p.INFO NOT LIKE '%GROUP BY%' THEN 'HAVING without GROUP BY - use WHERE instead'
              WHEN p.INFO LIKE '%SUBSTRING%WHERE%' OR p.INFO LIKE '%CONCAT%WHERE%'
                  THEN 'Functions in WHERE - prevents index usage'
              WHEN p.INFO LIKE '%DATE(%' AND p.INFO LIKE '%WHERE%'
                  THEN 'DATE() function in WHERE - use date range instead'
              WHEN p.INFO LIKE '%YEAR(%' AND p.INFO LIKE '%WHERE%'
                  THEN 'YEAR() function in WHERE - use date range instead'
              WHEN p.INFO REGEXP 'COUNT\\(\\*\\)' AND p.INFO NOT LIKE '%GROUP BY%'
                  THEN 'COUNT(*) without GROUP BY - consider COUNT(column)'
              WHEN p.INFO LIKE '%ORDER BY RAND()%' THEN 'ORDER BY RAND() - very inefficient for large tables'
              WHEN p.INFO LIKE '%<%>%' OR p.INFO LIKE '%!=%' THEN 'Inequality operator - may not use index efficiently'
              WHEN p.INFO LIKE '%IN (%' AND p.INFO LIKE '%,%,%,%,%'
                  THEN 'Large IN clause - consider temp table or JOIN'
              ELSE 'Query may need optimization'
          END as problem_indicator
      FROM INFORMATION_SCHEMA.PROCESSLIST p
      WHERE p.COMMAND = 'Query'
          AND p.TIME > 3
          AND p.INFO IS NOT NULL
          AND p.INFO NOT LIKE '%INFORMATION_SCHEMA%'
          AND p.INFO NOT LIKE '%performance_schema%'
      ORDER BY p.TIME DESC
      LIMIT 10)

      UNION ALL

      -- SECTION 4: TRANSACTION ISSUES WITH USER INFO
      (SELECT
          4 as section_order, ROW_NUMBER() OVER (ORDER BY t.trx_started) as priority,
          'TRANSACTION' as section,
          CONCAT('TRX ', LEFT(t.trx_id, 10)) as metric,
          CONCAT(IFNULL(p.USER, 'Unknown'), '@', IFNULL(p.HOST, 'Unknown')) as user_info,
          CONCAT(TIMESTAMPDIFF(SECOND, t.trx_started, NOW()), 's old') as value,
          CONCAT('Rows locked: ', t.trx_rows_locked, ' | Modified: ', t.trx_rows_modified) as details,
          CASE
              WHEN TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) > 300 THEN 'CRITICAL'
              WHEN TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) > 60 THEN 'WARNING'
              ELSE 'INFO'
          END as status,
          IFNULL(LEFT(t.trx_query, 300), 'No current query - idle transaction') as query_text,
          CASE
              WHEN t.trx_query IS NULL THEN 'Idle transaction - no active query, holding locks'
              WHEN t.trx_rows_locked > 5000 THEN 'Locking many rows - potential for deadlock'
              WHEN t.trx_autocommit_non_locking = 1 THEN 'Non-locking read transaction'
              WHEN TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) > 300 THEN 'Very long transaction - risk of blocking'
              WHEN t.trx_rows_modified > 1000 THEN 'Large transaction - many rows modified'
              ELSE 'Long running transaction'
          END as problem_indicator
      FROM INFORMATION_SCHEMA.INNODB_TRX t
      LEFT JOIN INFORMATION_SCHEMA.PROCESSLIST p ON t.trx_mysql_thread_id = p.ID
      WHERE TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) > 10
      ORDER BY t.trx_started
      LIMIT 10)

      UNION ALL

      -- SECTION 5: WAITING AND BLOCKED QUERIES
      (SELECT
          5 as section_order, ROW_NUMBER() OVER (ORDER BY p.TIME DESC) as priority,
          'WAITING' as section,
          CONCAT('PID ', p.ID) as metric,
          CONCAT(p.USER, '@', p.HOST) as user_info,
          p.STATE as value,
          CONCAT('Time: ', p.TIME, 's | DB: ', IFNULL(p.DB, 'none')) as details,
          CASE
              WHEN p.TIME > 30 THEN 'WARNING'
              ELSE 'INFO'
          END as status,
          LEFT(p.INFO, 300) as query_text,
          CASE
              WHEN p.STATE LIKE '%metadata lock%' THEN 'Waiting for metadata lock - DDL blocking'
              WHEN p.STATE LIKE '%table lock%' THEN 'Waiting for table lock'
              WHEN p.STATE LIKE '%Waiting for%' THEN CONCAT('Waiting: ', p.STATE)
              WHEN p.STATE LIKE '%Creating sort index%' THEN 'Creating sort index - missing index for ORDER BY'
              WHEN p.STATE LIKE '%Copying to tmp table%' THEN 'Large result set requiring temp table'
              WHEN p.STATE LIKE '%Sending data%' THEN 'Sending large result set to client'
              WHEN p.STATE LIKE '%statistics%' THEN 'Updating table statistics'
              ELSE p.STATE
          END as problem_indicator
      FROM INFORMATION_SCHEMA.PROCESSLIST p
      WHERE p.COMMAND = 'Query'
          AND p.INFO IS NOT NULL
          AND p.INFO NOT LIKE '%INFORMATION_SCHEMA%'
          AND (
              p.STATE LIKE '%lock%'
              OR p.STATE LIKE '%Creating%'
              OR p.STATE LIKE '%Copying%'
              OR p.STATE LIKE '%Waiting%'
              OR p.STATE LIKE '%Sending data%' AND p.TIME > 10
          )
      ORDER BY p.TIME DESC
      LIMIT 10)

      UNION ALL

      -- SECTION 6: TOP USERS BY ACTIVITY
      (SELECT
          6 as section_order, ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) as priority,
          'TOP USERS' as section,
          CONCAT('User: ', p.USER) as metric,
          CONCAT(p.USER, '@', SUBSTRING_INDEX(GROUP_CONCAT(DISTINCT p.HOST), ',', 1)) as user_info,
          CONCAT(COUNT(*), ' connections') as value,
          CONCAT(
              'Active: ', SUM(CASE WHEN p.COMMAND = 'Query' THEN 1 ELSE 0 END),
              ' | Sleep: ', SUM(CASE WHEN p.COMMAND = 'Sleep' THEN 1 ELSE 0 END)
          ) as details,
          CASE
              WHEN COUNT(*) > 50 THEN 'WARNING'
              WHEN COUNT(*) > 30 THEN 'INFO'
              ELSE 'OK'
          END as status,
          CONCAT('Most recent: ', LEFT(MAX(p.INFO), 100)) as query_text,
          CASE
              WHEN COUNT(*) > 50 THEN 'High connection count from this user'
              WHEN SUM(CASE WHEN p.COMMAND = 'Sleep' THEN 1 ELSE 0 END) > 20
                  THEN 'Many idle connections - check connection pooling'
              ELSE 'Normal activity'
          END as problem_indicator
      FROM INFORMATION_SCHEMA.PROCESSLIST p
      WHERE p.USER NOT IN ('event_scheduler', 'system user')
      GROUP BY p.USER
      ORDER BY COUNT(*) DESC
      LIMIT 5)

      UNION ALL

      -- SECTION 7: CONNECTION AND SYSTEM STATUS
      (SELECT
          7 as section_order, 1 as priority,
          'SYSTEM' as section,
          'Connections' as metric,
          'ALL USERS' as user_info,
          CONCAT(
              (SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND != 'Sleep'),
              '/',
              (SELECT @@max_connections)
          ) as value,
          CONCAT(
              'Active: ', (SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND = 'Query'),
              ' | Sleep: ', (SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND = 'Sleep')
          ) as details,
          CASE
              WHEN (SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST) / (SELECT @@max_connections) > 0.9 THEN 'CRITICAL'
              WHEN (SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST) / (SELECT @@max_connections) > 0.7 THEN 'WARNING'
              ELSE 'OK'
          END as status,
          'System Status' as query_text,
          CASE
              WHEN (SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST) / (SELECT @@max_connections) > 0.9
                  THEN 'Near connection limit - increase max_connections'
              WHEN (SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND = 'Sleep') > 50
                  THEN 'Many idle connections - check connection pool settings'
              ELSE 'Connection pool usage normal'
          END as problem_indicator
      LIMIT 1)

  ) AS monitoring_data
  ORDER BY row_num;