Skip to content

MySQL Performance Inspection

Background

For increasingly complex application architectures, the current trend is that more and more customers are adopting maintenance-free cloud databases. Therefore, inspecting the performance of MySQL is a top priority. Regular intelligent inspections of MySQL are performed to identify performance issues for anomaly alerts.

Prerequisites

  1. Offline deployment of self-built DataFlux Func
  2. Enable the [Script Market] in self-built DataFlux Func (../script-market-basic-usage/)
  3. Create an API Key in TrueWatch under 「Manage / API Key Management」 for operations.
  4. Install the 「Self-built Inspection Core Package」「Algorithm Library」「Self-built Inspection (MySQL Performance)」 via 「Script Market」 in your self-built DataFlux Func.
  5. Write self-built inspection processing functions in your self-built DataFlux Func.
  6. In your self-built DataFlux Func, use 「Manage / Scheduled Tasks (Old Version: Automatic Trigger Configuration)」 to create scheduled tasks (Old Version: Automatic Trigger Configuration) for the written functions.

If you consider using a cloud server for offline deployment of DataFlux Func, please ensure it is deployed with the currently used TrueWatch SaaS in the same operator and region

Configure Inspection

Create a new script set in self-built DataFlux Func to enable MySQL performance inspection configuration.

Python
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
from guance_monitor__register import self_hosted_monitor
from guance_monitor__runner import Runner
import guance_monitor_mysql_performance__main as main

# Workspace API_KEY configuration (configured by user)
API_KEY_ID  = 'wsak_xxxx'
API_KEY     = '3LTcYxxxxx'

# The function filters parameter has priority over configurations in studio monitoring\intelligent inspection. If the function filters parameter is configured, there is no need to change the detection settings in studio monitoring\intelligent inspection. If both are configured, the script's filters parameter takes precedence.

def filter_host(host):
    '''
    Filter host, define conditions for hosts that meet requirements, return True if matched, False otherwise.
    return True｜False
    '''
    if host in ['196.168.0.0']:
        return True

'''
Task configuration parameters should use:
@DFF.API('MySQL Performance Inspection', fixed_crontab='*/30 * * * *', timeout=900)

fixed_crontab: Fixed execution frequency 「every 30 minutes」
timeout: Task execution timeout duration, controlled within 15 minutes
'''
# Custom inspection configuration - no modification required by user
@self_hosted_monitor(API_KEY_ID, API_KEY)
@DFF.API('MySQL Performance Inspection', fixed_crontab='*/30 * * * *', timeout=900)
def run(configs=None):
    '''
    configs : List of hosts to be inspected (optional, default inspects all hosts in the current workspace if not configured)
    configs = {
        "host": ["192.168.0.1", "192.168.0.0"]    # Host list
    }

    '''
    checkers = [
        main.MysqlChecker(configs=configs, filters=[filter_host]),
    ]

    Runner(checkers, debug=False).run()

Start Inspection

After configuring the inspection in DataFlux Func, you can test it by selecting the run() method directly on the page. After publishing, you can view and configure it in DataFlux Func 「Manage / Scheduled Tasks」.

View Events

This inspection scans memory usage information from the last 6 hours. If it predicts that usage will exceed the warning threshold within the next 2 hours, the intelligent inspection generates corresponding events which can be viewed in the 「Event Center」.

Event Details

  • Event Overview: Describes the object and content of the abnormal inspection event.
  • CPU Utilization Line Chart: Shows the CPU utilization of the current abnormal host over the past 30 minutes.
  • Memory Utilization Line Chart: Shows the memory utilization of the current abnormal host over the past 30 minutes.
  • SQL Execution Count Line Chart: Displays the execution counts of replace, update, delete, insert, select over the past 30 minutes.
  • Slow SQL Line Chart: Displays the number of slow SQL queries discovered over the past 30 minutes.
  • SQL Time Ranking: Displays the top 5 slow SQL rankings and related information for abnormal MySQL instances. You can jump to view related logs via digest.

Common Issues

1. How to configure the detection frequency of MySQL Performance Inspection

  • When writing the self-built inspection processing function in DataFlux Func, add fixed_crontab='*/30 * * * *', timeout=900 in the decorator. Then configure it in 「Manage / Scheduled Tasks (Old Version: Automatic Trigger Configuration)」.

2. Why might there be no abnormal analysis when MySQL Performance Inspection triggers

If there is no abnormal analysis in the inspection report, please check the data collection status of the current datakit.

3. Under what circumstances does a MySQL Performance Inspection event occur

If the configured host experiences a CPU utilization exceeding 95% continuously for 10 minutes, or memory utilization exceeding 95% continuously for 10 minutes, or SQL execution count increases by 5 times compared to the previous period, or slow SQL occurrences increase by 5 times compared to the previous period, then an alert event is generated.

4. During the inspection process, previously normal scripts may encounter abnormal errors

Please update the referenced script sets in the Script Market of DataFlux Func. You can view the update records of the Script Market through the Change Log to facilitate timely updates of scripts.