mysql5.7（或 pxc5.7）充分利用cpu 多核的处理能力 及 mysql/pxc的通用优化
在早期的mysql5.1 版本中，innodb_file_io_threads 参数默认是4，该参数在linux系统上是不可更改的，但windows系统上可以调整。这个参数的作用是：InnoDB使用后台线程处理数据页上读写I/O（输入/输出）请求的数量。在mysql5.5及其后的版本中，使用两个新的参数 innodb_read_io_threads 和 innodb_write_io_thre..
·
在早期的mysql5.1 版本中，innodb_file_io_threads 参数默认是4，该参数在linux系统上是不可更改的，但windows系统上可以调整。这个参数的作用是：InnoDB使用后台线程处理数据页上读写I/O（输入/输出）请求的数量。
在mysql5.5及其后的版本中，使用两个新的参数 innodb_read_io_threads 和 innodb_write_io_threads 取代了innodb_file_io_threads ，如此调整后，在linux平台上就可以根据cpu核数来更改相应的参数值了，默认是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; 命令也可以查看
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
-------------------------------------
......假如 cpu 是 2颗8核的，那么可以设置：
innodb_read_io_threads=8
innodb_write_io_threads=8如果数据库的读操作比写操作多，那么可以设置：
innodb_read_io_threads=10
innodb_write_io_threads=8也就是，你可以根据情况加以设置。
注意：
这两个参数不支持动态修改，输入只读变量，需要把该参数加入/etc/my.cnf 里，修改完后重启mysql服务，允许值的范围是1~64.
调整完成后，你可以用命令"show engine innodb status\G;" 来查看调整结果，如下所示：
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
......查看 mysql或pxc 默认存储引擎
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)查看mysql 或 pxc 最大连接数
mysql:
mysql> show variables like "%max_connections%";
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+
1 row in set (0.00 sec)pxc:
查看字符集
查看mysql字符集
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)查看pxc字符集
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)
最后的最后，mysql与pxc 的通用优化见下图红线圈起来的部分：
改了配置，需要重启mysql服务使其生效。
优化后，
详细如图：
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)
更多推荐
0
0- 0
已为社区贡献8条内容
所有评论(0)