Skip to content

Instantly share code, notes, and snippets.

@knutov

knutov/mysql-performance-tuning.sql

Forked from mttjohnson/mysql-performance-tuning.sql
Created August 11, 2020 22:47
Show Gist options
  • Save knutov/9cc2bde268c9e0355a7d7b2b5da2c328 to your computer and use it in GitHub Desktop.
Save knutov/9cc2bde268c9e0355a7d7b2b5da2c328 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. Matt Johnson revised this gist Jul 12, 2018. 1 changed file with 23 additions and 1 deletion.
    24 changes: 23 additions & 1 deletion mysql-performance-tuning.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -23,7 +23,29 @@ FROM
    ) AA
    ) A;



    -- Analysis by table
    SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    CONCAT(CEILING(RIBPS/POWER(1024,pw)),SUBSTR(' KMGT',pw+1,1))
    Recommended_InnoDB_Buffer_Pool_Size,
    (
    SELECT CONCAT(CEILING(variable_value/POWER(1024,FLOOR(LOG(variable_value)/LOG(1024)))),SUBSTR(' KMGT',FLOOR(LOG(variable_value)/LOG(1024))+1,1))
    FROM information_schema.global_variables
    WHERE variable_name = 'innodb_buffer_pool_size'
    ) AS 'innodb_buffer_pool_size'
    FROM
    (
    SELECT TABLE_SCHEMA, TABLE_NAME, RIBPS,FLOOR(LOG(RIBPS)/LOG(1024)) pw
    FROM
    (
    SELECT TABLE_SCHEMA, TABLE_NAME, SUM(data_length+index_length)*1.1*growth RIBPS
    FROM information_schema.tables AAA,
    (SELECT 1 growth) BBB
    WHERE ENGINE='InnoDB' GROUP BY TABLE_SCHEMA, TABLE_NAME WITH ROLLUP
    ) AA
    ) A ORDER BY RIBPS DESC


    # System memory usage + connection memory usage
  2. Matt Johnson revised this gist Jul 12, 2018. 1 changed file with 6 additions and 4 deletions.
    10 changes: 6 additions & 4 deletions mysql-performance-tuning.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,23 +1,25 @@
    -- Query the database to calculate a recommended innodb_buffer_pool_size
    -- and get the currently configured value
    -- The rollup as the bottom row gives the total for all DBs on the server, where each other row is recommendations per DB.

    SELECT
    TABLE_SCHEMA,
    CONCAT(CEILING(RIBPS/POWER(1024,pw)),SUBSTR(' KMGT',pw+1,1))
    Recommended_InnoDB_Buffer_Pool_Size,
    (
    SELECT CONCAT(CEILING(variable_value/POWER(1024,FLOOR(LOG(variable_value)/LOG(1024)))),SUBSTR(' KMGT',FLOOR(LOG(variable_value)/LOG(1024))+1,1))
    FROM information_schema.global_variables
    WHERE variable_name = 'innodb_buffer_pool_size'
    ) as 'innodb_buffer_pool_size'
    ) AS 'innodb_buffer_pool_size'
    FROM
    (
    SELECT RIBPS,FLOOR(LOG(RIBPS)/LOG(1024)) pw
    SELECT TABLE_SCHEMA, RIBPS,FLOOR(LOG(RIBPS)/LOG(1024)) pw
    FROM
    (
    SELECT SUM(data_length+index_length)*1.1*growth RIBPS
    SELECT TABLE_SCHEMA, SUM(data_length+index_length)*1.1*growth RIBPS
    FROM information_schema.tables AAA,
    (SELECT 1 growth) BBB
    WHERE ENGINE='InnoDB'
    WHERE ENGINE='InnoDB' GROUP BY TABLE_SCHEMA WITH ROLLUP
    ) AA
    ) A;

  3. Matt Johnson revised this gist May 7, 2018. 1 changed file with 25 additions and 0 deletions.
    25 changes: 25 additions & 0 deletions mysql-performance-tuning.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -54,4 +54,29 @@ select
    (@@binlog_cache_size/1024/1024) binlog_cache_size;
    "

    # Estimated Max Memory Usage
    mysql -e "
    select
    (
    (@@key_buffer_size/1024/1024) + -- key_buffer_size,
    (@@query_cache_size/1024/1024) + -- query_cache_size,
    (@@tmp_table_size/1024/1024) + -- tmp_table_size,
    (@@innodb_buffer_pool_size/1024/1024) + -- innodb_buffer_pool_size,
    (@@innodb_additional_mem_pool_size/1024/1024) + -- innodb_additional_mem_pool_size,
    (@@innodb_log_buffer_size/1024/1024) -- innodb_log_buffer_size
    ) +
    (
    @@max_connections * -- max_connections,
    (
    (@@sort_buffer_size/1024/1024) + -- sort_buffer_size,
    (@@read_buffer_size/1024/1024) + -- read_buffer_size,
    (@@read_rnd_buffer_size/1024/1024) + -- read_rnd_buffer_size,
    (@@join_buffer_size/1024/1024) + -- join_buffer_size,
    (@@thread_stack/1024/1024) + -- thread_stack,
    (@@binlog_cache_size/1024/1024) -- binlog_cache_size
    )
    ) MAX_MEMORY_USAGE_MB
    ;
    "


  4. Matt Johnson revised this gist May 6, 2018. 1 changed file with 2 additions and 0 deletions.
    2 changes: 2 additions & 0 deletions mysql-performance-tuning.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -25,6 +25,8 @@ FROM


    # System memory usage + connection memory usage
    # http://www.mysqlcalculator.com/
    # https://dba.stackexchange.com/questions/1229/how-do-you-calculate-mysql-max-connections-variable


    # System Memory Limits
  5. Matt Johnson revised this gist May 6, 2018. 1 changed file with 34 additions and 1 deletion.
    35 changes: 34 additions & 1 deletion mysql-performance-tuning.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -19,4 +19,37 @@ FROM
    (SELECT 1 growth) BBB
    WHERE ENGINE='InnoDB'
    ) AA
    ) A;
    ) A;




    # System memory usage + connection memory usage


    # System Memory Limits
    mysql -e "
    select
    (@@key_buffer_size/1024/1024) key_buffer_size,
    (@@query_cache_size/1024/1024) query_cache_size,
    (@@tmp_table_size/1024/1024) tmp_table_size,
    (@@innodb_buffer_pool_size/1024/1024) innodb_buffer_pool_size,
    (@@innodb_additional_mem_pool_size/1024/1024) innodb_additional_mem_pool_size,
    (@@innodb_log_buffer_size/1024/1024) innodb_log_buffer_size;
    "


    # Connection Memory usage
    # max_connections * (per connection memory limits)
    mysql -e "
    select
    (@@max_connections) max_connections,
    (@@sort_buffer_size/1024/1024) sort_buffer_size,
    (@@read_buffer_size/1024/1024) read_buffer_size,
    (@@read_rnd_buffer_size/1024/1024) read_rnd_buffer_size,
    (@@join_buffer_size/1024/1024) join_buffer_size,
    (@@thread_stack/1024/1024) thread_stack,
    (@@binlog_cache_size/1024/1024) binlog_cache_size;
    "


  6. Matt Johnson created this gist Nov 22, 2016.
    22 changes: 22 additions & 0 deletions mysql-performance-tuning.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,22 @@
    -- Query the database to calculate a recommended innodb_buffer_pool_size
    -- and get the currently configured value

    SELECT
    CONCAT(CEILING(RIBPS/POWER(1024,pw)),SUBSTR(' KMGT',pw+1,1))
    Recommended_InnoDB_Buffer_Pool_Size,
    (
    SELECT CONCAT(CEILING(variable_value/POWER(1024,FLOOR(LOG(variable_value)/LOG(1024)))),SUBSTR(' KMGT',FLOOR(LOG(variable_value)/LOG(1024))+1,1))
    FROM information_schema.global_variables
    WHERE variable_name = 'innodb_buffer_pool_size'
    ) as 'innodb_buffer_pool_size'
    FROM
    (
    SELECT RIBPS,FLOOR(LOG(RIBPS)/LOG(1024)) pw
    FROM
    (
    SELECT SUM(data_length+index_length)*1.1*growth RIBPS
    FROM information_schema.tables AAA,
    (SELECT 1 growth) BBB
    WHERE ENGINE='InnoDB'
    ) AA
    ) A;