Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog How to Optimize Slow SQL in PolarDB-X

How to Optimize Slow SQL in PolarDB-X

This tutorial will guide you through optimizing slow SQL in PolarDB-X.

By Ximing

1. Prerequisites

Make sure you have successfully deployed PolarDB-X as per the previous tutorial and can connect to the PolarDB-X database.

2. Launch the Service

This step will guide you on how to simulate service traffic using Sysbench Select.

2.1 Prepare Stress Test Data

  • Create the sysbench_test database by executing the following SQL command:
CREATE DATABASE sysbench_test;
  • Use the sysbench_test database by executing the following SQL command:
USE sysbench_test;
  • On the experiment page, click the icon 1 in the top-right corner to create a new terminal: Terminal 3.

2

  • Switch to the galaxykube account by executing the following command:
su galaxykube
  • Go to the /home/galaxykube directory by executing the following command:
cd
  • Create the sysbench-prepare.yaml file by executing the following command:
vim sysbench-prepare.yaml
  • Press i to enter insert mode, copy the following code into the file, then press ESC, type :wq, and press Enter to save and exit the file.
apiVersion: batch/v1
kind: Job
metadata:
  name: sysbench-prepare-data-test
  namespace: default
spec:
  backoffLimit: 0
  template:
    spec:
      restartPolicy: Never
      containers:
        - name: sysbench-prepare
          image: severalnines/sysbench
          env:
            - name: POLARDB_X_USER
              value: polardbx_root
            - name: POLARDB_X_PASSWD
              valueFrom:
                secretKeyRef:
                  name: polardb-x
                  key: polardbx_root
          command: [ 'sysbench' ]
          args:
            - --db-driver=mysql
            - --mysql-host=$(POLARDB_X_SERVICE_HOST)
            - --mysql-port=$(POLARDB_X_SERVICE_PORT)
            - --mysql-user=$(POLARDB_X_USER)
            - --mysql_password=$(POLARDB_X_PASSWD)
            - --mysql-db=sysbench_test
            - --mysql-table-engine=innodb
            - --rand-init=on
            - --max-requests=1
            - --oltp-tables-count=1
            - --report-interval=5
            - --oltp-table-size=160000
            - --oltp_skip_trx=on
            - --oltp_auto_inc=off
            - --oltp_secondary
            - --oltp_range_size=5
            - --mysql_table_options=dbpartition by hash(`id`)
            - --num-threads=1
            - --time=3600
            - /usr/share/sysbench/tests/include/oltp_legacy/parallel_prepare.lua
            - run
  • Run the sysbench-prepare.yaml file and initialize the test data by executing the following command:
kubectl apply -f sysbench-prepare.yaml
  • Check the job status by executing the following command:
kubectl get jobs

The following result is returned. Wait about 1 minute. When COMPLETIONS shows 1/1, the data initialization is complete.

3

2.2 Start the Stress Test

  • Create the sysbench-select.yaml file by executing the following command:
vim sysbench-select.yaml
  • Press i to enter insert mode, copy the following code into the file, then press ESC, type :wq, and press Enter to save and exit the file.
apiVersion: batch/v1
kind: Job
metadata:
  name: sysbench-point-select-k-test
  namespace: default
spec:
  backoffLimit: 0
  template:
    spec:
      restartPolicy: Never
      containers:
        - name: sysbench-point-select-k
          image: severalnines/sysbench
          env:
            - name: POLARDB_X_USER
              value: polardbx_root
            - name: POLARDB_X_PASSWD
              valueFrom:
                secretKeyRef:
                  name: polardb-x
                  key: polardbx_root
          command: [ 'sysbench' ]
          args:
            - --db-driver=mysql
            - --mysql-host=$(POLARDB_X_SERVICE_HOST)
            - --mysql-port=$(POLARDB_X_SERVICE_PORT)
            - --mysql-user=$(POLARDB_X_USER)
            - --mysql_password=$(POLARDB_X_PASSWD)
            - --mysql-db=sysbench_test
            - --mysql-table-engine=innodb
            - --rand-init=on
            - --max-requests=0
            - --oltp-tables-count=1
            - --report-interval=5
            - --oltp-table-size=32000000
            - --oltp_skip_trx=on
            - --oltp_auto_inc=off
            - --oltp_secondary
            - --oltp_range_size=5
            - --mysql-ignore-errors=all
            - --num-threads=8
            - --time=3600
            - --random_points=1
            - /usr/share/sysbench/tests/include/oltp_legacy/select_random_points.lua
            - run
  • Run the sysbench-select.yaml file to start the stress test by executing the following command:
kubectl apply -f sysbench-select.yaml
  • Find the pod running the stress test script by executing the following command:
kubectl get pods

The following result is returned. The pod name starting with sysbench-point-select-k-test- is the target pod.

4

  • View QPS and other information by executing the following command:

Note: You need to replace "target pod" in the command with the actual pod name starting with sysbench-point-select-k-test-.

kubectl logs -f target pod

2.3 SQL Throttling and SQL Advisor

SQL Throttling

SQL throttling in PolarDB-X restricts SQL queries that meet specific criteria. Assuming the Sysbench Select traffic severely impacts other services, use SQL throttling to limit Select SQL.

  • View currently running requests by executing the following SQL command:
show full processlist where info is not null

The following result is returned:

5

  • Create a throttling rule for this SQL by executing the following SQL command:
create ccl_rule block_select on sysbench_test.* to 'polardbx_root'@'%' for select filter by keyword('pad') with max_concurrency=0;

After executing the SQL command for intercepting Select SQL queries in Terminal 2, you will see many SQL errors in Terminal 3.

6

  • View the specific interception status by executing the following SQL command:
show ccl_rules;

The following result is returned:

7

Using SQL Advisor to Optimize Slow SQL Queries

After slow SQL queries are restricted, the system returns to normal. Next, optimize SQL. The built-in SQL Advisor of PolarDB-X provides optimization suggestions for a given SQL statement.

  • Analyze the SQL statement using SQL Advisor by executing the following command:
explain advisor SELECT id, k, c, pad from sbtest1 where k in(10)\G

The following result is returned. In the ADVISE_INDEX section, you will find the suggestions provided by SQL Advisor.

8

  • Execute the SQL statement suggested by SQL Advisor.
ALTER TABLE `sysbench_test`.`sbtest1` ADD GLOBAL INDEX `__advise_index_gsi_sbtest1_k`(`k`) DBPARTITION BY HASH(`k`);
  • Remove the SQL rate limit by executing the following SQL command:
drop ccl_rule block_select;

After removing the rate limit in Terminal 2, you will see a significant increase in QPS in Terminal 3.

9

Try out database products for free:

lQLPJw7V5gCNgtfNBITNCvSwSh_pHTRWM4UGiQoky9W4AA_2804_1156

SQL Database Developers POLARDB Database Optimization Tutorials PolarDB-X Slow SQL Stress Test SQL Advisor
1 2 0
Share on

Read previous post:

PolarDB Brings Alibaba Cloud SIGMOD Best Paper Award

Read next post:

PolarDB-X, X-DB, and PolarDB

ApsaraDB

439 posts | 93 followers

You may also like

Comments

Santhakumar Munuswamy August 4, 2024 at 8:14 am

Thanks for Sharing and good write-up

ApsaraDB

439 posts | 93 followers

Related Products

More Posts by ApsaraDB

See All