GreatSQL Modify session variables using HINT syntax in SQL
GreatSQL supports a new optimized Hint, called SET_VAR. This feature supports users to modify some session variables of the GreatSQL database in the query statement. Of course, the modification only takes effect on the current query session and will not affect other sessions.
SET_VAR syntax
SET_VAR This hint is used to temporarily set the session value of system variables (valid for the duration of a single statement)
Usage of SET_VAR:SET_VAR(var_name=value)
var_name is the session variable name that has been temporarily modified, and value is the value of the session variable
greatsql> SELECT @@unique_checks;SELECT /*+ SET_VAR(unique_checks=OFF) */ @@unique_checks;SELECT @@unique_checks; SELECT /*+ SET_VAR(sort_buffer_size = 16M) */ name FROM people ORDER BY name; INSERT /*+ SET_VAR(foreign_key_checks=OFF) */ INTO t2 VALUES(2); SELECT /*+ SET_VAR(optimizer_switch = 'mrr_cost_based=off') */ 1;
How to operate before GreatSQL 8.0
Before GreatSQL 8.0, you need to modify session variables to a query. What should you do:
1. Query the system variables before
greatsql> SELECT @@optimizer_switch;
2. Backup system variables
greatsql> SET @old_optimizer_switch = @@optimizer_switch;
3. Set new variables
greatsql> SET optimizer_switch='index_merge=off';
4. Run the query statement
greatsql> SELECT empno,ename,deptno from emp limit 1;
5. Restore previous system variables
greatsql> SET optimizer_switch = @old_optimizer_switch;
Isn't it a bit cumbersome? Now we use the new feature of SET_VAR, which is very convenient to do this operation.
How to operate GreatSQL 8.0
greatsql>explain SELECT empno,ename,deptno FROM emp WHERE deptno=10 or ename='CLARK'; +----+-------------+-------+------------+-------------+------------------+------------------+---------+------+------+----------+--------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------------+------------------+------------------+---------+------+------+----------+--------------------------------------------+ | 1 | SIMPLE | emp | NULL | index_merge | deptno,idx_ename | deptno,idx_ename | 5,63 | NULL | 4 | 100.00 | Using union(deptno,idx_ename); Using where | +----+-------------+-------+------------+-------------+------------------+------------------+---------+------+------+----------+--------------------------------------------+ 1 row in set, 1 warning (0.01 sec)
From the perspective of execution plan, SQL statements use index merge (type=index_merge). If you do not want the SQL to use index merge, you can control it through SET_VAR.
greatsql>explain SELECT /*+ SET_VAR(optimizer_switch='index_merge=off') */ empno,ename,deptno FROM emp WHERE deptno=10 or ename='CLARK'; +----+-------------+-------+------------+------+------------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+------------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | emp | NULL | ALL | deptno,idx_ename | NULL | NULL | NULL | 14 | 38.10 | Using where | +----+-------------+-------+------------+------+------------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec
Is this new feature very convenient? Due to some settings of the optimizer, a small number of SQL statements chose the wrong execution plan, which led to the low performance of the query statement and the variables of the online database cannot be changed at will. With the new feature of SET_VAR, for this situation, you can consider using set_var to optimize the statement in the query statement.
We know that when using hash jion, the join buffer will be used. The size of the join buffer is controlled by join_buffer_size, and its default value is 256k. Hash connections cannot use more than this amount of memory. When the memory required for hash connection exceeds the available amount, GreatSQL will use files on disk to handle this problem. When using disk files, the performance will degrade. If you only want to set the join buffer for a single statement, you can use SET_VAR.
CREATE TABLE t1 (c1 INT, c2 INT); CREATE TABLE t2 (c1 INT, c2 INT); CREATE TABLE t3 (c1 INT, c2 INT);
Insert 1 million, 2 million, and 3 million data to t1, t2, and t3 respectively
greatsql> SET @@cte_max_recursion_depth = 99999999;
greatsql> INSERT INTO t1
WITH recursive t AS (
SELECT 1 AS c1 ,1 AS c2
UNION ALL
SELECT t.c1+1,t.c1*2
FROM t
WHERE t.c1 <1000000
)
SELECT * FROM t;
Query OK, 1000000 rows affected (10.63 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
greatsql> SELECT @@join_buffer_size;
+--------------------+
| @@join_buffer_size |
+--------------------+
| 262144 |
+--------------------+
1 row in set (0.00 sec)
greatsql> SELECT * FROM t1
JOIN t2 ON (t1.c1 = t2.c1 AND t1.c2 < t2.c2)
JOIN t3 ON (t2.c1 = t3.c1);
Empty set (6.91 sec)
greatsql> SELECT /*+ SET_VAR(join_buffer_size=16777216) */ * FROM t1
JOIN t2 ON (t1.c1 = t2.c1 AND t1.c2 < t2.c2)
JOIN t3 ON (t2.c1 = t3.c1);
Empty set (5.87 sec)Things to note
1. Not all session variables are allowed to be used with SET_VAR. A warning appears if the setting of system variables that do not support changing with SET_VAR.
greatsql> SELECT /*+ SET_VAR(collation_server = 'utf8mb4') */ 1; +---+ | 1 | +---+ | 1 | +---+ 1 row in set, 1 warning (0.01 sec) greatsql> SHOW WARNINGS\G *************************** 1. row *************************** Level: Warning Code: 3637 Message: Variable 'collation_server' cannot be set using SET_VAR hint. 1 row in set (0.00 sec)
2. The SET_VAR syntax only allows setting a single variable, but can give multiple prompts to set multiple variables:
SELECT /*+ SET_VAR(optimizer_switch = 'mrr_cost_based=off') SET_VAR(max_heap_table_size = 1G) */ 1;
3. If there is no system variable or the variable value is incorrect, ignore the SET_VAR prompt and issue a warning.
SELECT /*+ SET_VAR(max_size = 1G) */ 1; SELECT /*+ SET_VAR(optimizer_switch = 'mrr_cost_based=yes') */ 1;
Article 1 statement does not have the variable max_size. mrr_cost_based on or off in statement 2, and attempts to set it to yes is wrong. The hint of both statements will be ignored and a warning will be generated.
greatsql> SELECT /*+ SET_VAR(max_size = 1G) */ 1; +---+ | 1 | +---+ | 1 | +---+ 1 row in set, 1 warning (0.00 sec) greatsql> show warnings; +---------+------+---------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------+ | Warning | 3128 | Unresolved name 'max_size' for SET_VAR hint | +---------+------+---------------------------------------------+ 1 row in set (0.00 sec) greatsql> SELECT /*+ SET_VAR(optimizer_switch = 'mrr_cost_based=yes') */ 1; +---+ | 1 | +---+ | 1 | +---+ 1 row in set, 1 warning (0.00 sec) greatsql> show warnings; +---------+------+-------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------------------------------+ | Warning | 1231 | Variable 'optimizer_switch' can't be set to the value of 'mrr_cost_based=yes' | +---------+------+-------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
4. The SET_VAR prompt is only allowed to be used at the statement level. If used in a subquery, it is ignored and a warning is given.
Copy ignores SET_VAR in the copy statement to avoid potential security issues.
SET_VAR supports variables
SET_VAR is only available for some variables, and has sorted out the main supported variables of GreatSQL for reference:
- bulk_insert_buffer_size
- default_table_encryption
- default_tmp_storage_engine
- div_precision_increment
- end_markers_in_json
- eq_range_index_dive_limit
- foreign_key_checks
- group_concat_max_len
- internal_tmp_mem_storage_engine
- join_buffer_size
- lock_wait_timeout
- max_error_count
- max_execution_time
- max_heap_table_size
- max_join_size
- max_length_for_sort_data
- max_points_in_geometry
- max_seeks_for_key
- max_sort_length
- optimizer_prune_level
- optimizer_search_depth
- optimizer_switch
- optimizer_trace_max_mem_size
- range_alloc_block_size
- read_buffer_size
- read_rnd_buffer_size
- secondary_engine_cost_threshold
- select_into_buffer_size
- select_into_disk_sync
- select_into_disk_sync_delay
- show_create_table_skip_secondary_engine
- sort_buffer_size
- sql_auto_is_null
- sql_big_selects
- sql_buffer_result
- sql_mode
- sql_require_primary_key
- sql_safe_updates
- sql_select_limit
- time_zone (≥ 8.0.17)
- timestamp
- tmp_table_size
- unique_checks
- updatable_views_with_limit
- use_secondary_engine
- windowing_use_high_precision
Reference Documents
/doc/refman/8.0/en/#optimizer-hints-set-var
/doc/refman/8.0/en/
/doc/refman/8.0/en/
Enjoy GreatSQL 😃
About GreatSQL
GreatSQL is a domestic independent open source database suitable for financial-grade applications. It has multiple core features such as high performance, high reliability, high ease of use, and high security. It can be used as an optional replacement for MySQL or Percona Server. It is used in an online production environment and is completely free and compatible with MySQL or Percona Server.
This is the article about GreatSQL using HINT syntax to modify session variables in SQL. For more information about GreatSQL using HINT syntax to modify session variables, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!