X-Tuner: Parameter Tuning and Diagnosis ​

Overview ​

X-Tuner is a parameter tuning tool integrated into databases. It uses AI technologies such as deep reinforcement learning and global search algorithm to obtain the optimal database parameter settings without manual intervention. This function is not necessarily deployed with the database environment. It can be independently deployed and run without the database installation environment.

Preparations ​

Prerequisites and Precautions ​

• The database status is normal; the client can be properly connected; and data can be imported to the database. As a result, the optimization program can perform the benchmark test for optimization effect.
• To use this tool, you need to specify the user who logs in to the database. The user who logs in to the database must have sufficient permissions to obtain sufficient database status information.
• If you log in to the database host as a Linux user, add $GAUSSHOME/bin to the _PATH_environment variable so that you can directly run database O&M tools, such as gsql, gs_guc, and gs_ctl.
• This tool can run in three modes. In tune or train mode, you must configure the benchmark running environment and import data. This tool will iteratively run the benchmark to check whether the performance is improved after the parameters are modified.
• In recommend mode, you are advised to run the command when the database is executing the workload to obtain more accurate real-time workload information.
• By default, this tool provides benchmark running script samples of TPC-C, TPC-H, TPC-DS, and sysbench. If you use the benchmarks to perform pressure tests on the database system, you can modify or configure the preceding configuration files. To adapt to your own service scenarios, you need to compile the script file that drives your customized benchmark based on the template.py file in the benchmark directory.

Principles ​

The tuning program is a tool independent of the database kernel. The usernames and passwords for the database and instances are required to control the benchmark performance test of the database. Before starting the tuning program, ensure that the interaction in the test environment is normal, the benchmark test script can be run properly, and the database can be connected properly.

X-Tuner can run in any of the following modes:

• recommend: Log in to the database using the specified username, obtain the feature information about the running workload, and generate a parameter recommendation report based on the feature information. Report improper parameter settings and potential risks in the current database. Output the currently running workload behavior and characteristics. Output the recommended parameter settings. In this mode, the database does not need to be restarted. In other modes, the database may need to be restarted repeatedly.
• train: Modify parameters and execute the benchmark based on the benchmark information provided by users. The reinforcement learning model is trained through repeated iteration so that you can load the model in tune mode for optimization.
• tune: Use an optimization algorithm to tune database parameters. Currently, two types of algorithms are supported: deep reinforcement learning and global search algorithm (global optimization algorithm). The deep reinforcement learning mode requires train mode to generate the optimized model after training. However, the global search algorithm does not need to be trained in advance and can be directly used for search and optimization.

Figure 1 X-Tuner structure

Figure 1 shows the overall architecture of the X-Tuner. The X-Tuner system can be divided into the following parts:

• DB: The DB_Agent module is used to abstract database instances. It can be used to obtain the internal database status information and current database parameters and set database parameters. The SSH connection used for logging in to the database environment is included on the database side.
• Algorithm: algorithm package used for tuning, including global search algorithms (such as Bayesian optimization and particle swarm optimization) and deep reinforcement learning (such as DDPG).
• X-Tuner: The main logic module is encapsulated by the environment module. Each step is a tuning process. The entire tuning process is iterated through multiple steps.
• Benchmark: a user-specified benchmark performance test script, which is used to run benchmark jobs. The benchmark result reflects the performance of the database system.

Installing and Running X-Tuner ​

Run the following command to obtain the help information about the X-Tuner function:

gs_dbmind component xtuner --help

You can specify different commands to obtain the corresponding help information.

Description of the X-Tuner Configuration File ​

Before running the X-Tuner, you need to load the configuration file. You can run the --help command to view the absolute path of the configuration file that is loaded by default.

...  
 -x TUNER_CONFIG_FILE, --tuner-config-file TUNER_CONFIG_FILE
                        This is the path of the core configuration file of the
                        X-Tuner. You can specify the path of the new
                        configuration file. The default path is /path/to/xtuner/xtuner.conf.
                        You can modify the configuration file to control the
                        tuning process.
...

You can modify the configuration items in the configuration file as required to instruct the X-Tuner to perform different actions. For details about the configuration items in the configuration file, see Table 2. If you need to change the loading path of the configuration file, you can specify the path through the -x command line option.

Benchmark Selection and Configuration ​

The benchmark driver script is stored in the benchmark subdirectory of the X-Tuner directory ($GAUSSHOME/bin/dbmind/components/xtuner). X-Tuner provides common benchmark driver scripts, such as time-based detection script (default), TPC-C, and TPC-H. The X-Tuner invokes the get_benchmark_instance() command in the benchmark/__init__.py file to load different benchmark driver scripts and obtain benchmark driver instances. The format of the benchmark driver script is described as follows:

• Benchmark driver script name uniquely identifies the driver script. You can specify the benchmark driver script to be loaded by setting benchmark_script in the configuration file of the X-Tuner.
• The driver script contains the path variable, cmd variable, and the run function.

The following describes the three elements of the driver script:

1. path: path for storing the benchmark driver script. You can modify the path in the driver script or specify the path by setting the benchmark_path configuration item in the configuration file.

2. cmd: command for executing the benchmark driver script. You can modify the command in the driver script or specify the command by setting the benchmark_cmd configuration item in the configuration file. Placeholders can be used in the text of cmd to obtain necessary information for running cmd commands. For details, see the TPC-H driver script example. These placeholders include:

   • {host}: IP address of the database host
   • {port}: listening port number of the database instance
   • {user}: username for logging in to the database
   • {password}: password of the user who logs in to the database system
   • {db}: name of the database that is being optimized

3. run: The signature of this function is as follows:

   def run(remote_server, local_host) -> float:

   The returned data type is float, indicating the evaluation score after the benchmark is executed. A larger value indicates better performance. For example, the TPC-C test result tpmC can be used as the returned value, the inverse number of the total execution time of all SQL statements in TPC-H can also be used as the return value. A larger return value indicates better performance.

   The remote_server variable is the shell command line interface transferred by the X-Tuner program to the remote host (database host machine) used by the script. The local_host variable is the shell command line interface of the local host (host where the X-Tuner script is executed) transferred by the X-Tuner program. Methods provided by the preceding shell command interface include:

   exec_command_sync(command, timeout)
   Function: This method is used to run the shell command on the host.
   Parameter list:
   command: The data type can be str, and the element can be a list or tuple of the str type. This parameter is mandatory.
   timeout: The timeout interval for command execution in seconds. This parameter is optional.
   Return value:
   Returns 2-tuple (stdout and stderr). stdout indicates the standard output stream result, and stderr indicates the standard error stream result. The data type is str.

   exit_status
   Function: This attribute indicates the exit status code after the latest shell command is executed.
   Note: Generally, if the exit status code is 0, the execution is normal. If the exit status code is not 0, an error occurs.

Benchmark driver script example:

1. TPC-C driver script

   from tuner.exceptions import ExecutionError
   
   # WARN: You need to download the benchmark-sql test tool to the system,
   # replace the PostgreSQL JDBC driver with the openGauss driver,
   # and configure the benchmark-sql configuration file.
   # The program starts the test by running the following command:
   path = '/path/to/benchmarksql/run' # Path for storing the TPC-C test script benchmark-sql
   cmd = "./runBenchmark.sh props.gs"  # Customize a benchmark-sql test configuration file named props.gs.
   
   
   def run(remote_server, local_host):
       # Switch to the TPC-C script directory, clear historical error logs, and run the test command.
        # You are advised to wait for several seconds because the benchmark-sql test script generates the final test report through a shell script. The entire process may be delayed.
       # To ensure that the final tpmC value report can be obtained, wait for 3 seconds.
       stdout, stderr = remote_server.exec_command_sync(['cd %s' % path, 'rm -rf benchmarksql-error.log', cmd, 'sleep 3'])
       # If there is data in the standard error stream, an exception is reported and the system exits abnormally.
       if len(stderr) > 0:
           raise ExecutionError(stderr)
   
       # Find the final tpmC result.
       tpmC = None
       split_string = stdout.split()  # Split the standard output stream result.
       for i, st in enumerate(split_string):
            # In the benchmark-sql of version 5.0, the value of tpmC is the last two digits of the keyword (NewOrders). In normal cases, the value of tpmC is returned after the keyword is found.
           if "(NewOrders)" in st:
               tpmC = split_string[i + 2]
               break
       stdout, stderr = remote_server.exec_command_sync(
           "cat %s/benchmarksql-error.log" % path)
       nb_err = stdout.count("ERROR:")  # Check whether errors occur during the benchmark running and record the number of errors.
       return float(tpmC) - 10 * nb_err  # The number of errors is used as a penalty item, and the penalty coefficient is 10. A higher penalty coefficient indicates a larger number of errors.

2. TPC-H driver script

   import time
   
   from tuner.exceptions import ExecutionError
   
   # WARN: You need to import data into the database and SQL statements in the following path will be executed.
   # The program automatically collects the total execution duration of these SQL statements.
   path = '/path/to/tpch/queries'  # Directory for storing SQL scripts used for the TPC-H test
   cmd = "gsql -U {user} -W {password} -d {db} -p {port} -f {file}"  # The command for running the TPC-H test script. Generally, gsql -f script file is used.
   
   
   def run(remote_server, local_host):
       # Traverse all test case file names in the current directory.
       find_file_cmd = "find . -type f -name '*.sql'"
       stdout, stderr = remote_server.exec_command_sync(['cd %s' % path, find_file_cmd])
       if len(stderr) > 0:
           raise ExecutionError(stderr)
       files = stdout.strip().split('\n')
       time_start = time.time()
       for file in files:
           # Replace {file} with the file variable and run the command.
           perform_cmd = cmd.format(file=file)
           stdout, stderr = remote_server.exec_command_sync(['cd %s' % path, perform_cmd])
           if len(stderr) > 0:
               print(stderr)
       # The cost is the total execution duration of all test cases.
       cost = time.time() - time_start
       # Use the inverse number to adapt to the definition of the run function. The larger the returned result is, the better the performance is.
       return - cost

Examples ​

X-Tuner supports three modes: recommend for obtaining parameter diagnosis reports, train for training reinforcement learning models, and tune for using an optimization algorithm. The preceding three modes are distinguished by command line parameters, and the details are specified in the configuration file.

Configuring the Database Connection Information ​

Configuration items for connecting to a database in the three modes are the same. You can enter the detailed connection information in the command line or in the JSON configuration file. Both methods are described as follows:

1. Entering the connection information in the command line

   Input the following options: --db-name --db-user --port --host --host-user. The --host-ssh-port is optional. The following is an example:

   gs_dbmind component xtuner recommend --db-name postgres --db-user omm --port 5678 --host 192.168.1.100 --host-user omm

2. Entering the connection information in the JSON configuration file

   Assume that the file name is connection.json. The following is an example of the JSON configuration file:

   {
     "db_name": "postgres",  # Database name
     "db_user": "dba",       # Username for logging in to the database
     "host": "127.0.0.1",    # IP address of the database host
     "host_user": "dba",     # Username for logging in to the database host
     "port": 5432,           # Listening port number of the database
     "ssh_port": 22          # SSH listening port number of the database host
   }

   Input -f connection.json.

Example of Using the recommend Mode ​

The configuration item scenario takes effect for the recommend mode. If the value is auto, the workload type is automatically detected.

Run the following command to obtain the diagnosis result:

gs_dbmind component xtuner recommend -f connection.json

The diagnosis report is generated as follows:

Figure 1 Report generated in recommend mode

In the preceding report, the database parameter configurations in the environment are recommended, and a risk warning is provided. The report also generates the current workload features. The following features are for reference:

• temp_file_size: number of generated temporary files. If the value is greater than 0, the system uses temporary files. If too many temporary files are used, the performance is poor. If possible, increase the value of work_mem.
• cache_hit_rate: cache hit ratio of shared_buffer, indicating the cache efficiency of the current workload.
• read_write_ratio: read/write ratio of database jobs.
• search_modify_ratio: ratio of data query to data modification of a database job.
• ap_index: AP index of the current workload. The value ranges from 0 to 10. A larger value indicates a higher preference for data analysis and retrieval.
• workload_type: workload type, which can be AP, TP, or HTAP based on database statistics.
• checkpoint_avg_sync_time: average duration for refreshing data to the disk each time when the database is at the checkpoint, in milliseconds.
• load_average: average load of each CPU core in 1 minute, 5 minutes, and 15 minutes. Generally, if the value is about 1, the current hardware matches the workload. If the value is about 3, the current workload is heavy. If the value is greater than 5, the current workload is too heavy. In this case, you are advised to reduce the load or upgrade the hardware.

select pg_stat_reset_shared('bgwriter');
select pg_stat_reset();

Example of Using the train Mode ​

This mode is used to train the deep reinforcement learning model. The configuration items related to this mode are as follows:

• rl_algorithm: algorithm used to train the reinforcement learning model. Currently, this parameter can be set to ddpg.

• rl_model_path: path for storing the reinforcement learning model generated after training.

• rl_steps: maximum number of training steps in the training process.

• max_episode_steps: maximum number of steps in each episode.

• scenario: specifies the workload type. If the value is auto, the system automatically determines the workload type. The recommended parameter tuning list varies according to the mode.

• tuning_list: specifies the parameters to be invoked. If no parameter is specified, the parameters to be invoked are automatically recommended based on the workload type. If parameters are specified, tuning_list indicates the path of the tuning list file. The following is an example of the content of a tuning list configuration file:

  {
    "work_mem": {
      "default": 65536,
      "min": 65536,
      "max": 655360,
      "type": "int",
      "restart": false
    },
    "shared_buffers": {
      "default": 32000,
      "min": 16000,
      "max": 64000,
      "type": "int",
      "restart": true
    },
    "random_page_cost": {
      "default": 4.0,
      "min": 1.0,
      "max": 4.0,
      "type": "float",
      "restart": false
    },
    "enable_nestloop": {
      "default": true,
      "type": "bool",
      "restart": false
    }
  }

After the preceding configuration items are configured, run the following command to start the training:

gs_dbmind component xtuner train -f connection.json

After the training is complete, a model file is generated in the directory specified by the rl_model_path configuration item.

Example of Using the tune Mode ​

The tune mode supports a plurality of algorithms, including a DDPG algorithm based on reinforcement learning (RL), and a Bayesian optimization algorithm and a particle swarm algorithm (PSO) which are both based on a global optimization algorithm (GOP).

The configuration items related to the tune mode are as follows:

• tune_strategy: specifies the algorithm to be used for tuning. The value can be rl(using the reinforcement learning model), gop(using the global optimization algorithm), or auto(selected automatically). If this parameter is set to rl, RL-related configuration items take effect. In addition to the preceding configuration items that take effect in train mode, the test_episode configuration item also takes effect. This configuration item indicates the maximum number of episodes in the tuning process. This parameter directly affects the execution time of the tuning process. Generally, a larger value indicates longer time consumption.
• gop_algorithm: specifies a global optimization algorithm. The value can be bayes or pso.
• max_iterations: specifies the maximum number of iterations. A larger value indicates a longer search time and better search effect.
• particle_nums: specifies the number of particles. This parameter is valid only for the PSO algorithm.
• For details about scenario and tuning_list, see the description of train mode.

After the preceding items are configured, run the following command to start tuning:

gs_dbmind component xtuner tune -f connection.json

Obtaining Help Information ​

Before starting the tuning program, run the following command to obtain help information:

gs_dbmind component xtuner --help

The command output is as follows:

usage:  [-h] [--database DATABASE] [--db-user DB_USER] [--db-port DB_PORT] [--db-host DB_HOST] [--host-user HOST_USER] [--host-ssh-port HOST_SSH_PORT] [-f DB_CONFIG_FILE] [-x TUNER_CONFIG_FILE] [-v]
        {train,tune,recommend}

X-Tuner: a self-tuning tool integrated by openGauss.

positional arguments:
  {train,tune,recommend}
                        Train a reinforcement learning model or tune database by model. And also can recommend best_knobs according to your workload.


optional arguments:
  -h, --help            show this help message and exit
  -f DB_CONFIG_FILE, --db-config-file DB_CONFIG_FILE
                        You can pass a path of configuration file otherwise you should enter database information by command arguments manually. Please see the template file share/server.json.template.
  -x TUNER_CONFIG_FILE, --tuner-config-file TUNER_CONFIG_FILE
                        This is the path of the core configuration file of the X-Tuner. You can specify the path of the new configuration file. The default path is /path/to/config/file. You can modify the configuration file to control the tuning process.
  -v, --version         show program's version number and exit

Database Connection Information:
  --database DATABASE, --db-name DATABASE
                        The name of database where your workload running on.
  --db-user DB_USER     Use this user to login your database. Note that the user must have sufficient permissions.
  --db-port DB_PORT, --port DB_PORT
                        Use this port to connect with the database.
  --db-host DB_HOST, --host DB_HOST
                        The IP address of your database installation host.
  --host-user HOST_USER
                        The login user of your database installation host.
  --host-ssh-port HOST_SSH_PORT
                        The SSH port of your database installation host.

Command Reference ​

Table 1 Command-line parameters

Table 2 Parameters in the configuration file

Troubleshooting ​

• Failure of connection to the database instance: Check whether the database instance is faulty or the security permissions of configuration items in the pg_hba.conf file are incorrectly configured.
• Restart failure: Check the health status of the database instance and ensure that the database instance is running properly.
• Poor performance of TPC-C jobs: In high-concurrency scenarios such as TPC-C, a large amount of data is modified during pressure tests. Each test is not idempotent, for example, the data volume in the TPC-C database increases, invalid tuples are not cleared using VACUUM FULL, checkpoints are not triggered in the database, and drop cache is not performed. Therefore, it is recommended that the benchmark data that is written with a large amount of data, such as TPC-C, be imported again at intervals (depending on the number of concurrent tasks and execution duration). A simple method is to back up the $PGDATA directory.
• When the TPC-C job is running, the TPC-C driver script reports the error "TypeError: float() argument must be a string or a number, not 'NoneType'" (none cannot be converted to the float type). This is because the TPC-C pressure test result is not obtained. There are many causes for this problem, manually check whether TPC-C can be successfully executed and whether the returned result can be obtained. If the preceding problem does not occur, you are advised to set the delay time of the sleep command in the command list in the TPC-C driver script to a larger value.