Skip to content

MySQL Performance Tuning #38

@dyrnq

Description

@dyrnq
I have no name!@65c9f8462559:/$ mysqladmin variables -uroot --password=666666 |grep -E "pool|connect|heap|buffer|cache|size|time" | sort
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
| binlog_cache_size                                        | 32768                                                                                                                                                                                                                                                                                                                                                                                                                                     |
| binlog_max_flush_queue_time                              | 0                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| binlog_stmt_cache_size                                   | 32768                                                                                                                                                                                                                                                                                                                                                                                                                                     |
| binlog_transaction_dependency_history_size               | 25000                                                                                                                                                                                                                                                                                                                                                                                                                                     |
| bulk_insert_buffer_size                                  | 8388608                                                                                                                                                                                                                                                                                                                                                                                                                                   |
| character_set_connection                                 | utf8mb4                                                                                                                                                                                                                                                                                                                                                                                                                                   |
| collation_connection                                     | utf8mb4_unicode_ci                                                                                                                                                                                                                                                                                                                                                                                                                        |
| connect_timeout                                          | 10                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| datetime_format                                          | %Y-%m-%d %H:%i:%s                                                                                                                                                                                                                                                                                                                                                                                                                         |
| default_password_lifetime                                | 0                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| delayed_insert_timeout                                   | 300                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| delayed_queue_size                                       | 1000                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| disconnect_on_expired_password                           | ON                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| explicit_defaults_for_timestamp                          | ON                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| flush_time                                               | 0                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| have_query_cache                                         | YES                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| have_statement_timeout                                   | YES                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| host_cache_size                                          | 279                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| init_connect                                             |                                                                                                                                                                                                                                                                                                                                                                                                                                           |
| innodb_buffer_pool_chunk_size                            | 134217728                                                                                                                                                                                                                                                                                                                                                                                                                                 |
| innodb_buffer_pool_dump_at_shutdown                      | ON                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| innodb_buffer_pool_dump_now                              | OFF                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| innodb_buffer_pool_dump_pct                              | 25                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| innodb_buffer_pool_filename                              | ib_buffer_pool                                                                                                                                                                                                                                                                                                                                                                                                                            |
| innodb_buffer_pool_instances                             | 1                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| innodb_buffer_pool_load_abort                            | OFF                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| innodb_buffer_pool_load_at_startup                       | ON                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| innodb_buffer_pool_load_now                              | OFF                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| innodb_buffer_pool_size                                  | 134217728                                                                                                                                                                                                                                                                                                                                                                                                                                 |
| innodb_change_buffer_max_size                            | 25                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| innodb_change_buffering                                  | all                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| innodb_disable_sort_file_cache                           | OFF                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| innodb_flush_log_at_timeout                              | 1                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| innodb_ft_cache_size                                     | 8000000                                                                                                                                                                                                                                                                                                                                                                                                                                   |
| innodb_ft_max_token_size                                 | 84                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| innodb_ft_min_token_size                                 | 3                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| innodb_ft_result_cache_limit                             | 2000000000                                                                                                                                                                                                                                                                                                                                                                                                                                |
| innodb_ft_total_cache_size                               | 640000000                                                                                                                                                                                                                                                                                                                                                                                                                                 |
| innodb_lock_wait_timeout                                 | 50                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| innodb_log_buffer_size                                   | 16777216                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| innodb_log_file_size                                     | 50331648                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| innodb_log_write_ahead_size                              | 8192                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| innodb_max_undo_log_size                                 | 1073741824                                                                                                                                                                                                                                                                                                                                                                                                                                |
| innodb_old_blocks_time                                   | 1000                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| innodb_online_alter_log_max_size                         | 134217728                                                                                                                                                                                                                                                                                                                                                                                                                                 |
| innodb_page_size                                         | 16384                                                                                                                                                                                                                                                                                                                                                                                                                                     |
| innodb_purge_batch_size                                  | 300                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| innodb_rollback_on_timeout                               | OFF                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| innodb_sort_buffer_size                                  | 1048576                                                                                                                                                                                                                                                                                                                                                                                                                                   |
| innodb_sync_array_size                                   | 1                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| interactive_timeout                                      | 28800                                                                                                                                                                                                                                                                                                                                                                                                                                     |
| join_buffer_size                                         | 262144                                                                                                                                                                                                                                                                                                                                                                                                                                    |
| key_buffer_size                                          | 8388608                                                                                                                                                                                                                                                                                                                                                                                                                                   |
| key_cache_age_threshold                                  | 300                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| key_cache_block_size                                     | 1024                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| key_cache_division_limit                                 | 100                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| large_page_size                                          | 0                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| lc_time_names                                            | en_US                                                                                                                                                                                                                                                                                                                                                                                                                                     |
| lock_wait_timeout                                        | 31536000                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| log_timestamps                                           | UTC                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| long_query_time                                          | 10.000000                                                                                                                                                                                                                                                                                                                                                                                                                                 |
| max_binlog_cache_size                                    | 18446744073709547520                                                                                                                                                                                                                                                                                                                                                                                                                      |
| max_binlog_size                                          | 1073741824                                                                                                                                                                                                                                                                                                                                                                                                                                |
| max_binlog_stmt_cache_size                               | 18446744073709547520                                                                                                                                                                                                                                                                                                                                                                                                                      |
| max_connect_errors                                       | 100                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| max_connections                                          | 151                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| max_execution_time                                       | 0                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| max_heap_table_size                                      | 16777216                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| max_join_size                                            | 18446744073709551615                                                                                                                                                                                                                                                                                                                                                                                                                      |
| max_relay_log_size                                       | 0                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| max_user_connections                                     | 0                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| metadata_locks_cache_size                                | 1024                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| myisam_data_pointer_size                                 | 6                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| myisam_max_sort_file_size                                | 9223372036853727232                                                                                                                                                                                                                                                                                                                                                                                                                       |
| myisam_mmap_size                                         | 18446744073709551615                                                                                                                                                                                                                                                                                                                                                                                                                      |
| myisam_sort_buffer_size                                  | 8388608                                                                                                                                                                                                                                                                                                                                                                                                                                   |
| net_buffer_length                                        | 16384                                                                                                                                                                                                                                                                                                                                                                                                                                     |
| net_read_timeout                                         | 30                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| net_write_timeout                                        | 60                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| ngram_token_size                                         | 2                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| optimizer_trace_max_mem_size                             | 16384                                                                                                                                                                                                                                                                                                                                                                                                                                     |
| parser_max_mem_size                                      | 18446744073709551615                                                                                                                                                                                                                                                                                                                                                                                                                      |
| performance_schema_accounts_size                         | -1                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| performance_schema_digests_size                          | 10000                                                                                                                                                                                                                                                                                                                                                                                                                                     |
| performance_schema_events_stages_history_long_size       | 10000                                                                                                                                                                                                                                                                                                                                                                                                                                     |
| performance_schema_events_stages_history_size            | 10                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| performance_schema_events_statements_history_long_size   | 10000                                                                                                                                                                                                                                                                                                                                                                                                                                     |
| performance_schema_events_statements_history_size        | 10                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| performance_schema_events_transactions_history_long_size | 10000                                                                                                                                                                                                                                                                                                                                                                                                                                     |
| performance_schema_events_transactions_history_size      | 10                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| performance_schema_events_waits_history_long_size        | 10000                                                                                                                                                                                                                                                                                                                                                                                                                                     |
| performance_schema_events_waits_history_size             | 10                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| performance_schema_hosts_size                            | -1                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| performance_schema_session_connect_attrs_size            | 512                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| performance_schema_setup_actors_size                     | -1                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| performance_schema_setup_objects_size                    | -1                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| performance_schema_users_size                            | -1                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| preload_buffer_size                                      | 32768                                                                                                                                                                                                                                                                                                                                                                                                                                     |
| profiling_history_size                                   | 15                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| query_alloc_block_size                                   | 8192                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| query_cache_limit                                        | 1048576                                                                                                                                                                                                                                                                                                                                                                                                                                   |
| query_cache_min_res_unit                                 | 4096                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| query_cache_size                                         | 1048576                                                                                                                                                                                                                                                                                                                                                                                                                                   |
| query_cache_type                                         | OFF                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| query_cache_wlock_invalidate                             | OFF                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| query_prealloc_size                                      | 8192                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| range_alloc_block_size                                   | 4096                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| range_optimizer_max_mem_size                             | 8388608                                                                                                                                                                                                                                                                                                                                                                                                                                   |
| read_buffer_size                                         | 131072                                                                                                                                                                                                                                                                                                                                                                                                                                    |
| read_rnd_buffer_size                                     | 262144                                                                                                                                                                                                                                                                                                                                                                                                                                    |
| rpl_stop_slave_timeout                                   | 31536000                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| session_track_system_variables                           | time_zone,autocommit,character_set_client,character_set_results,character_set_connection                                                                                                                                                                                                                                                                                                                                                  |
| slave_net_timeout                                        | 60                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| slave_pending_jobs_size_max                              | 16777216                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| slow_launch_time                                         | 2                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| sort_buffer_size                                         | 262144                                                                                                                                                                                                                                                                                                                                                                                                                                    |
| sql_buffer_result                                        | OFF                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| stored_program_cache                                     | 256                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| system_time_zone                                         | UTC                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| table_definition_cache                                   | 1400                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| table_open_cache                                         | 2000                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| table_open_cache_instances                               | 16                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| thread_cache_size                                        | 9                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| thread_handling                                          | one-thread-per-connection                                                                                                                                                                                                                                                                                                                                                                                                                 |
| time_format                                              | %H:%i:%s                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| time_zone                                                | +08:00                                                                                                                                                                                                                                                                                                                                                                                                                                    |
| tmp_table_size                                           | 16777216                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| transaction_alloc_block_size                             | 8192                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| transaction_prealloc_size                                | 4096                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| wait_timeout                                             | 28800                                                                                                                                                                                                                                                                                                                                                                                                                                     |

Metadata

Metadata

Assignees

No one assigned

    Labels

    documentationImprovements or additions to documentation

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions