Uploaded image for project: 'Percona Server'
  1. Percona Server
  2. PS-3966

Re-implement SET STATEMENT ... FOR ... using 8.0 hint syntax

    XMLWordPrintable

    Details

    • Type: Admin & Maintenance Task
    • Status: Done
    • Priority: Medium
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 8.0.12-2rc1
    • Component/s: None
    • Labels:
      None

      Description

      In 8.0 we are keeping the per-statement variable setting feature but dropping our/MariaDB syntax in favor of upstream statement hint syntax, which, in upstream, only works for a subset of variables on SELECTs. This task is to use this syntax for all statements, virtually all variables.

      This can be done in parallel with main 8.0 porting, on a clean upstream 8.0.11 branch as base.

       

      High Level Description

      SET STATEMENT FOR provides a way to set multiple session variables for a single statement. The goal of the task is replace percona’s implementation(syntax) but provide same functionality using MySQL 8.0 SET_VAR().

      High Level Design

      1. SET_VAR hints works will all DML statements. ie. INSERT, UPDATE, DELETE
      2. SET_VAR hints works will all SELECTs
      3. SET_VAR works with PREPARE STATEMENT
      4. SET_VAR hints are not replicated whereas SET STATEMENT FOR variables are replicated. In 8.0, Percona server will use upstream behaviour. i.e. not to replicate variables set using SET_VAR hints
      5. SET_VAR works with stored procedure. SET_VAR hints  can be at statement level inside procedure or an hint to the CALL statement. The hint cannot be written at the definition of the complete stored procedure. Example:

                         DELIMITER |;

                        SET STATEMENT myisam_sort_buffer_size=400000,

                                                myisam_repair_threads=2,

                                                 sort_buffer_size=200000,

                                                  binlog_format=row,

                                                 keep_files_on_create=OFF,

                                                max_join_size=4444440000000 FOR

                                                CREATE PROCEDURE p1() BEGIN

                                                SELECT @@myisam_sort_buffer_size,

                                                       @@myisam_repair_threads,

                                                       @@sort_buffer_size,

                                                       @@binlog_format,

                                                       @@keep_files_on_create,

                                                       @@max_join_size;

                                                 END|

                        DELIMITER ;|

                        We cannot have  /*+ SET_VAR(sort_buffer_size=20000) */ CREATE PROCEDURE

                       p1() .   

                       SET_VAR hints should be written at STATEMENT. (after SELECT/UPDATE/DELETE..

                       ) or  CALL /*+SET_VAR() */ p1(). The hints at CALL statement will be applicable to all

                       statements inside stored procedure.

                      The hints at statement within stored procedure will have higher preference compared to

                      hints at CALL statement.

            6. SET_VAR hints do not work in subqueries

            7. Recursive SET STATEMENT FOR  are possible but recursive SET_VAR are not.

                Percona Server will use upstream behaviour. For example, the equivalent of the below

                 statement using SET_VAR will not be possible.

                          SET STATEMENT myisam_sort_buffer_size = 700000, sort_buffer_size = 3000000

                          FOR SET STATEMENT myisam_sort_buffer_size=200000

                            FOR SELECT @@myisam_sort_buffer_size, @@sort_buffer_size;

           8.  Hints are not supported in view (CREATE or ALTER VIEW)

           9.  Setting from global values is not allowed

                 Example: SELECT /*+ SET_VAR(sort_buffer_size=@@global.sort_buffer_size) */ * FROM

                 t1;

      Percona Server changes (extends SET_VAR to):

      1. OPTIMIZE TABLE statement
      2.  MyISAM session variables
      3.  Plugin or Storage Engine variables
      4. InnoDB Session variables. innodb_lockwait_timeout and innodb_tmpdir
      5. ALTER TABLE. Works with variables: innodb_tmpdir , innodb_ft_user_stopword_table
      6. CALL stored_proc() statement
      7. ANALYZE TABLE statement. Works with session variables "histogram_generation_max_mem_size" and "myisam_stats_method"
      8. CHECK TABLE statement. Works with  session variable "myisam_stats_method"
      9. LOAD INDEX statement(used for MyiSAM). Works with session variable "preload_buffer_size"
      10. CREATE TABLE statement. Works with session variable  "innodb_ft_user_stopword_table"

      List of variables that are extended to use with SET_VAR

      1. innodb_lock_wait_timeout
      2. innodb_tmpdir
      3. innodb_ft_user_stopword_table
      4. block_encryption_mode
      5. histogram_generation_max_mem_size
      6. myisam_sort_buffer_size
      7. myisam_repair_threads
      8. myisam_stats_method
      9. preload_buffer_size(used by MyISAM only)

        Smart Checklist

          Attachments

            Issue Links

              Activity

                People

                • Assignee:
                  satya.bodapati Satya Bodapati
                  Reporter:
                  laurynas.biveinis Laurynas Biveinis
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  4 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved:

                    Time Tracking

                    Estimated:
                    Original Estimate - Not Specified
                    Not Specified
                    Remaining:
                    Remaining Estimate - 0 minutes
                    0m
                    Logged:
                    Time Spent - 1 week, 1 hour, 43 minutes
                    1w 1h 43m