Release History
Requirements

Introduction

Quick onboard

Deployment

Data Modeling

Connecting

Write data

Migration

Query

Operations and Maintenance

Performance Tuning

Troubleshooting

Reference Guide

SQL Reference

FAQ

Performance Tuning Overview

This document introduces performance tuning methodologies in YMatrix, covering system resource optimization, database resource tuning, and SQL optimization.

1 Why Perform Performance Tuning?

Performance tuning is a highly comprehensive and critical task.

There are generally two reasons for performance tuning:

  • Performance is significantly below normal levels, requiring resolution of performance bottlenecks;
  • A specific SQL statement performs adequately under current conditions but fails to meet the requirements of the target business scenario, necessitating further optimization for improved performance.

Prerequisite:

  • Without affecting the correct operation of the database management system.

2 How to Effectively Perform Performance Tuning?

2.1 Describe the Problem

Use performance monitoring data and other requirement information to describe the current performance bottleneck or performance needs.

A good problem description should include context and clearly articulate the observed issue. It should be concise and typically contain, but not be limited to, the following information:

  • YMatrix version
  • Affected component (or cluster-wide issue)
  • Quantified symptoms of the problem

Better example: Using the built-in data ingestion tool in the YMatrix 5.0 GUI to load 2.8 million rows, after 30 test runs, latency increased from 30 seconds to 1 minute — nearly a 2x degradation in performance.

Poorer example: Generating test data via the GUI is getting slower.

2.2 Define Objectives

Optimization goals vary across business scenarios. For example, in a time-series use case such as connected vehicles, optimization objectives may include improving performance for aggregation queries and join operations in detailed data queries. In contrast, for a financial core OLTP system, the goal might be reducing long-tail transaction latency.

An effective optimization objective should be quantifiable, for example:

  • During peak business hours from 9:00 AM to 10:00 AM, p50 transfer transaction latency must remain under 200 milliseconds.
  • Dashboard 1 must respond in real time within 10 seconds.

Not acceptable:

  • The system is too slow and unresponsive; optimization is needed.
  • Improve real-time responsiveness of the dashboard.

2.3 Collect Information

YMatrix recommends collecting, but not limited to, the following information for analysis:

  • Server resource metrics:
    • CPU utilization
    • Memory utilization
    • Disk I/O
    • Network throughput
  • Software environment:
    • Operating system version
    • YMatrix version
  • Cluster information:
    • Cluster deployment topology
  • Database information (collect as appropriate based on actual situation):

2.4 Analyze Root Cause / Identify Performance Bottleneck

Based on collected information, identify or hypothesize the root cause of the performance bottleneck. Multiple rounds of data collection may be required to pinpoint the issue. Refer to Performance Tuning for detailed methodology.

Note!
Effective communication and collaboration are key to quickly identifying root causes.

2.5 Propose Solutions

After identifying the performance bottleneck, propose an optimization plan that is low-cost, low-risk, and delivers maximum benefit.

Even if a solution offers the highest potential gain for the most significant bottleneck, always assess its associated risks and implementation cost. Typically, YMatrix recommends the following tuning order, listed from lowest to highest cost/risk:

  1. Hardware
    Conduct configuration testing and benchmark testing to quickly evaluate the impact of hardware upgrades.
  2. SQL Queries
    Use ANALYZE to update table statistics; or analyze query plans and rewrite SQL statements to reduce logical complexity and execution cost.
  3. System Configuration Parameters
    Improve performance by adjusting system-level configuration parameters related to the query. Exercise caution when modifying system-level (System) parameters, as changes may have adverse global effects.
  4. Data Model
    Consider modifying the table DDL only when previous methods fail to achieve desired tuning results. Altering table structures carries high risk and broad impact—use this approach only as a last resort.

Note!
To avoid irreparable performance bottlenecks during production operation, place greater emphasis on initial design. Scientifically plan, analyze, and test the data model for your use case to ensure correctness.

2.6 Implement Tuning

The tuning implementation process typically includes, but is not limited to, the following steps:

  • Hold multiple solution review meetings with detailed documentation of each discussion
  • Iterate and refine until a complete and robust tuning plan is finalized
  • Replicate the production environment as closely as possible to validate changes and perform full regression testing, minimizing risks during production deployment
  • Execute changes on the production system, with meticulous documentation of every step

2.7 Evaluate Results

Performance tuning is rarely a one-time effort. For a given performance issue, you may need to repeat steps 2.3 through 2.7 multiple times before achieving satisfactory results.

Therefore, it's essential to evaluate tuning outcomes clearly and take the following actions:

  • If objectives are met: Deliver the project and schedule a post-tuning review meeting. To accommodate future business growth, consider conducting capacity planning for the system.
  • If objectives are not met: Repeat steps 2.3 through 2.7 until the goals are achieved.
← Previous
Grafana Cluster Alerts
Next →
Approach