In earlier versions of MySQL 5.1, the innodb_file_io_threads parameter defaulted to 4, which was unchangeable on linux systems but adjustable on windows systems. The function of this parameter is that InnoDB uses background threads to process the number of read/write I/O (input/output) requests on data pages.
In MySQL 5.5 and later versions, two new parameters innodb_read_io_threads and innodb_write_io_threads are used instead of innodb_file_io_threads. After this adjustment, the corresponding parameter values can be changed according to the cpu core number on the linux platform by default of 4.
mysql> show variables like "%io%"; +----------------------------------------------------------+------------------------------------------------------------------------------------------+ | Variable_name | Value | +----------------------------------------------------------+------------------------------------------------------------------------------------------+ | binlog_direct_non_transactional_updates | OFF | | binlog_error_action | ABORT_SERVER | | binlog_transaction_dependency_history_size | 25000 | | binlog_transaction_dependency_tracking | COMMIT_ORDER | | block_encryption_mode | aes-128-ecb | | character_set_connection | utf8 | | collation_connection | utf8_general_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | | completion_type | NO_CHAIN | | default_authentication_plugin | mysql_native_password | | div_precision_increment | 4 | | ft_query_expansion_limit | 20 | | gtid_executed_compression_period | 1000 | | innodb_compression_failure_threshold_pct | 5 | | innodb_compression_level | 6 | | innodb_compression_pad_pct_max | 50 | | innodb_io_capacity | 200 | | innodb_io_capacity_max | 2000 | | innodb_read_io_threads | 4 | | innodb_replication_delay | 0 | | innodb_use_native_aio | ON | | innodb_version | 5.7.22 | | innodb_write_io_threads | 4 | | key_cache_division_limit | 100 | | keyring_operations | ON | | log_bin_trust_function_creators | OFF | | low_priority_updates | OFF | | max_connections | 151 | | max_execution_time | 0 | | max_sp_recursion_depth | 0 | | max_user_connections | 0 | | myisam_recover_options | OFF | | performance_schema_events_transactions_history_long_size | 10000 | | performance_schema_events_transactions_history_size | 10 | | performance_schema_session_connect_attrs_size | 512 | | protocol_version | 10 | | session_track_gtids | OFF | | session_track_schema | ON | | session_track_state_change | OFF | | session_track_system_variables | time_zone,autocommit,character_set_client,character_set_results,character_set_connection | | session_track_transaction_info | OFF | | slave_checkpoint_period | 300 | | slave_transaction_retries | 10 | | slave_type_conversions | | | table_definition_cache | 1400 | | tls_version | TLSv1,TLSv1.1 | | transaction_alloc_block_size | 8192 | | transaction_allow_batching | OFF | | transaction_isolation | REPEATABLE-READ | | transaction_prealloc_size | 4096 | | transaction_read_only | OFF | | transaction_write_set_extraction | OFF | | tx_isolation | REPEATABLE-READ | | validate_password_dictionary_file | | | version | 5.7.22 | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | Linux | +----------------------------------------------------------+------------------------------------------------------------------------------------------+ 59 rows in set (0.00 sec)
Show engine InnoDB status G; commands can also be viewed
mysql> show engine innodb status\G; *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 2019-08-14 15:50:29 0x7f2240ef5700 INNODB MONITOR OUTPUT ...... ...... ------------ TRANSACTIONS ------------ Trx id counter 10901 Purge done for trx's n:o < 0 undo n:o < 0 state: running but idle History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421260600866640, not started 0 lock struct(s), heap size 1136, 0 row lock(s) -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (write thread) I/O thread 7 state: waiting for completed aio requests (write thread) I/O thread 8 state: waiting for completed aio requests (write thread) I/O thread 9 state: waiting for completed aio requests (write thread) Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] , ibuf aio reads:, log i/o's:, sync i/o's: Pending flushes (fsync) log: 0; buffer pool: 0 1055 OS file reads, 59 OS file writes, 7 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- ......
If the cpu has two 8 cores, it can be set up:
innodb_read_io_threads=8 innodb_write_io_threads=8
If the database has more read operations than write operations, you can set up:
innodb_read_io_threads=10 innodb_write_io_threads=8
That is, you can set it up according to the situation.
Be careful:
These two parameters do not support dynamic modification, input read-only variables, need to add this parameter to / etc/my.cnf, after modification restart mysql service, the allowable range is 1-64.
After the adjustment is completed, you can use the command "show engine InnoDB status G;" to view the adjustment results, as follows:
mysql> show engine innodb status\G; ...... -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (read thread) I/O thread 7 state: waiting for completed aio requests (read thread) I/O thread 8 state: waiting for completed aio requests (read thread) I/O thread 9 state: waiting for completed aio requests (read thread) I/O thread 10 state: waiting for completed aio requests (write thread) I/O thread 11 state: waiting for completed aio requests (write thread) I/O thread 12 state: waiting for completed aio requests (write thread) I/O thread 13 state: waiting for completed aio requests (write thread) I/O thread 14 state: waiting for completed aio requests (write thread) I/O thread 15 state: waiting for completed aio requests (write thread) I/O thread 16 state: waiting for completed aio requests (write thread) I/O thread 17 state: waiting for completed aio requests (write thread) Pending normal aio reads: [0, 0, 0, 0, 0, 0, 0, 0] , aio writes: [0, 0, 0, 0, 0, 0, 0, 0] , ibuf aio reads:, log i/o's:, sync i/o's: Pending flushes (fsync) log: 0; buffer pool: 0 532 OS file reads, 53 OS file writes, 7 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ......
View mysql or pxc default storage engine
mysql> show variables like "%default%"; +---------------------------------+-----------------------+ | Variable_name | Value | +---------------------------------+-----------------------+ | default_authentication_plugin | mysql_native_password | | default_password_lifetime | 0 | | default_storage_engine | InnoDB | | default_tmp_storage_engine | InnoDB | | default_week_format | 0 | | explicit_defaults_for_timestamp | OFF | | innodb_default_row_format | dynamic | +---------------------------------+-----------------------+ 7 rows in set (0.01 sec)
View the maximum number of connections for mysql or pxc
mysql:
mysql> show variables like "%max_connections%"; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 151 | +-----------------+-------+ 1 row in set (0.00 sec)
pxc:
View Character Sets
View the mysql character set
mysql> show variables like "%char%"; +--------------------------------------+----------------------------+ | Variable_name | Value | +--------------------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | | validate_password_special_char_count | 1 | +--------------------------------------+----------------------------+ 9 rows in set (0.00 sec)
View the pxc character set
mysql> show variables like "%char%"; +---------------------------+---------------------------------------------+ | Variable_name | Value | +---------------------------+---------------------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | /usr/share/percona-xtradb-cluster/charsets/ | | ft_query_extra_word_chars | OFF | +---------------------------+---------------------------------------------+ 9 rows in set (0.01 sec)
Finally, the general optimization of mysql and pxc is shown in the section of the coil in red below.
The configuration has been changed and the mysql service needs to be restarted to take effect.
After optimization,
Details are shown as follows:
mysql> show variables like "%io%"; +----------------------------------------------------------+------------------------------------------------------------------------------------------+ | Variable_name | Value | +----------------------------------------------------------+------------------------------------------------------------------------------------------+ | binlog_direct_non_transactional_updates | OFF | | binlog_error_action | ABORT_SERVER | | binlog_transaction_dependency_history_size | 25000 | | binlog_transaction_dependency_tracking | COMMIT_ORDER | | block_encryption_mode | aes-128-ecb | | character_set_connection | utf8 | | collation_connection | utf8_general_ci | | collation_database | utf8_general_ci | | collation_server | utf8_general_ci | | completion_type | NO_CHAIN | | default_authentication_plugin | mysql_native_password | | div_precision_increment | 4 | | ft_query_expansion_limit | 20 | | gtid_executed_compression_period | 1000 | | innodb_compression_failure_threshold_pct | 5 | | innodb_compression_level | 6 | | innodb_compression_pad_pct_max | 50 | | innodb_io_capacity | 200 | | innodb_io_capacity_max | 2000 | | innodb_read_io_threads | 8 | | innodb_replication_delay | 0 | | innodb_use_native_aio | ON | | innodb_version | 5.7.22 | | innodb_write_io_threads | 8 | | key_cache_division_limit | 100 | | keyring_operations | ON | | log_bin_trust_function_creators | OFF | | low_priority_updates | OFF | | max_connections | 4190 | | max_execution_time | 0 | | max_sp_recursion_depth | 0 | | max_user_connections | 0 | | myisam_recover_options | OFF | | performance_schema_events_transactions_history_long_size | 10000 | | performance_schema_events_transactions_history_size | 10 | | performance_schema_session_connect_attrs_size | 512 | | protocol_version | 10 | | session_track_gtids | OFF | | session_track_schema | ON | | session_track_state_change | OFF | | session_track_system_variables | time_zone,autocommit,character_set_client,character_set_results,character_set_connection | | session_track_transaction_info | OFF | | slave_checkpoint_period | 300 | | slave_transaction_retries | 10 | | slave_type_conversions | | | table_definition_cache | 600 | | tls_version | TLSv1,TLSv1.1 | | transaction_alloc_block_size | 8192 | | transaction_allow_batching | OFF | | transaction_isolation | REPEATABLE-READ | | transaction_prealloc_size | 4096 | | transaction_read_only | OFF | | transaction_write_set_extraction | OFF | | tx_isolation | REPEATABLE-READ | | validate_password_dictionary_file | | | version | 5.7.22 | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | Linux | +----------------------------------------------------------+------------------------------------------------------------------------------------------+ 59 rows in set (0.00 sec)