2019年8月

秋天到了

秋天到了,河边的花已是晚期了,早晚外面凉快多了,但是室内还是那么闷热;

立秋以后,北方已经秋高气爽了,魔都估计得到十月份才能彻底凉快下来;

秋天是个收获的季节,不过前提是得种下才有收获;

---阅读剩余部分---

MySQL网络超时错误Got timeout reading communication packets的解决方法

MySQL大量报错Got timeout reading communication packets,error显示日志如下:

2018-11-14T23:27:01.130760Z 2876756 [Note] Aborted connection 2876756 to db: 'zabbix_db' user: 'zabbix_u' host: '192.168.255.209' (Got timeout reading communication packets)
2018-11-14T23:27:03.437908Z 2874970 [Note] Aborted connection 2874970 to db: 'zabbix_db' user: 'zabbix_u' host: '192.168.255.209' (Got timeout reading communication packets)

当前MySQL主要timeout相关的参数如下:

show session variables like '%timeout%';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 10       |
| delayed_insert_timeout      | 300      |
| have_statement_timeout      | YES      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 10       |
| innodb_rollback_on_timeout  | ON       |
| interactive_timeout         | 60       |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 60      |
| net_write_timeout           | 30      |
| rpl_stop_slave_timeout      | 31536000 |
| slave_net_timeout           | 60       |
| wait_timeout                | 900      |
+-----------------------------+----------+
13 rows in set (0.01 sec)

connect_timeout:默认为10S
wait_timeout:默认是8小时,即28800秒
interactive_timeout:默认是8小时,即28800秒
net_read_timeout:默认是30S
net_write_timeout:默认是60S
如果是认证过程中超时,不管是读还是,都是 connect_timeout;
对于读网络超时,一般是 wait_timeout/interactive_timeout,基本不会是 net_read_timeout(特例是业务用到 LOAD DATA LOCAL FILE);
对于写网络超时,都是 net_write_timeout。
解决方法如下:

set global interactive_timeout=900;
set global net_read_timeout=900;
set global net_write_timeout=900;

再次tail日志,已经无该信息报错了,可以把以上参数加入到my.cnf里面,900根据实际场景设置;

参考文档:
http://mysql.taobao.org/monthly/2017/05/04/
http://cenalulu.github.io/mysql/mysql-timeout/
https://www.cnblogs.com/xiaoboluo768/p/6222862.html

CentOS7启用ena网络增强模块

CentOS7.4.1708及以上版本内核中已经集成了亚马逊的ena驱动程序已被加进内核,参考https://wiki.centos.org/zh/Manuals/ReleaseNotes/CentOS7.1708 官方页面;
maxresdefault.jpg
如果是7.4以上版本直接使用下面的方法即可加载ena模块:

[root@monitor ~]# yum update -y
[root@monitor ~]# cat /etc/redhat-release
CentOS Linux release 7.6.1810 (Core)

[root@monitor ~]# modinfo ena
filename:       /lib/modules/3.10.0-957.12.2.el7.x86_64/kernel/drivers/net/ethernet/amazon/ena/ena.ko.xz
version:        1.5.0K
license:        GPL
description:    Elastic Network Adapter (ENA)
author:         Amazon.com, Inc. or its affiliates
retpoline:      Y
rhelversion:    7.6
srcversion:     1B9931F07C26733BA8D4F94
alias:          pci:v00001D0Fd0000EC21sv*sd*bc*sc*i*
alias:          pci:v00001D0Fd0000EC20sv*sd*bc*sc*i*
alias:          pci:v00001D0Fd00001EC2sv*sd*bc*sc*i*
alias:          pci:v00001D0Fd00000EC2sv*sd*bc*sc*i*
depends:
intree:         Y
vermagic:       3.10.0-957.12.2.el7.x86_64 SMP mod_unload modversions
signer:         CentOS Linux kernel signing key
sig_key:        8D:E6:4F:B5:96:9B:55:7E:DC:7F:03:2E:EA:BC:BE:4F:37:17:7F:4E
sig_hashalgo:   sha256
parm:           debug:Debug level (0=none,...,16=all) (int)

查看内核版本:

[root@monitor ~]# uname -r
3.10.0-957.12.2.el7.x86_64

添加模块:

dracut -f --add-drivers nvme /boot/initramfs-$(uname -r).img $(uname -r)

查看内核镜像内容并过滤nvme:

[root@monitor ~]# lsinitrd /boot/initramfs-3.10.0-957.12.2.el7.x86_64.img | grep -i nvme
Arguments: -f --add-drivers 'nvme'
drwxr-xr-x   3 root     root            0 Aug 14 19:43 usr/lib/modules/3.10.0-957.12.2.el7.x86_64/kernel/drivers/nvme
drwxr-xr-x   2 root     root            0 Aug 14 19:43 usr/lib/modules/3.10.0-957.12.2.el7.x86_64/kernel/drivers/nvme/host
-rw-r--r--   1 root     root        28480 May 15 05:48 usr/lib/modules/3.10.0-957.12.2.el7.x86_64/kernel/drivers/nvme/host/nvme-core.ko.xz
-rw-r--r--   1 root     root        16908 May 15 05:48 usr/lib/modules/3.10.0-957.12.2.el7.x86_64/kernel/drivers/nvme/host/nvme.ko.xz

CentOS7.4以下版本需要重新安装,操作步骤如下:
1、先更新升级Linux内核:

yum --enablerepo=extras install epel-release
yum -y install patch dkms kernel-devel perl
yum update
yum --enablerepo=elrepo-kernel install kernel-lt -y

vi /etc/default/grub
将GRUB_DEFAULT=saved 改成 GRUB_DEFAULT=0
grub2-mkconfig -o /boot/grub2/grub.cfg 
reboot 重启后生效

2、安装ena驱动

cd /opt
curl -o ena_linux_2.1.1.tar.gz https://codeload.github.com/amzn/amzn-drivers/tar.gz/ena_linux_2.1.1
tar zxvf ena_linux_2.1.1.tar.gz
mv amzn-drivers-ena_linux_2.1.1 /usr/src/ena-2.1.1
cat <<EOF > /usr/src/ena-2.1.1/dkms.conf
PACKAGE_NAME="ena"
PACKAGE_VERSION="2.1.1"
AUTOINSTALL="yes"
REMAKE_INITRD="yes"
BUILT_MODULE_LOCATION[0]="kernel/linux/ena"
BUILT_MODULE_NAME[0]="ena"
DEST_MODULE_LOCATION[0]="/updates"
DEST_MODULE_NAME[0]="ena"
CLEAN="cd kernel/linux/ena; make clean"
MAKE="cd kernel/linux/ena; make BUILD_KERNEL=\${kernelver}"
EOF

dkms add -m ena -v 2.1.1
dkms build -m ena -v 2.1.1
dkms install -m ena -v 2.1.1

dracut -f --add-drivers ena

#安装完毕查看
[root@devops ~]# modinfo ena
filename:       /lib/modules/3.10.0-957.27.2.el7.x86_64/extra/ena.ko.xz
version:        2.1.1g
license:        GPL
description:    Elastic Network Adapter (ENA)
author:         Amazon.com, Inc. or its affiliates
retpoline:      Y
rhelversion:    7.6
srcversion:     F342F1BEE707726508E3DF8
alias:          pci:v00001D0Fd0000EC21sv*sd*bc*sc*i*
alias:          pci:v00001D0Fd0000EC20sv*sd*bc*sc*i*
alias:          pci:v00001D0Fd00001EC2sv*sd*bc*sc*i*
alias:          pci:v00001D0Fd00000EC2sv*sd*bc*sc*i*
depends:
vermagic:       3.10.0-957.27.2.el7.x86_64 SMP mod_unload modversions
parm:           debug:Debug level (0=none,...,16=all) (int)
parm:           rx_queue_size:Rx queue size. The size should be a power of 2. Max value is 8K
 (int)
parm:           force_large_llq_header:Increases maximum supported header size in LLQ mode to 224 bytes, while reducing the maximum TX queue size by half.
 (int)

3、添加驱动

查看内核版本:uname -r

添加模块:dracut -f --add-drivers nvme /boot/initramfs-$(uname -r).img $(uname -r)

查看内核镜像内容并过滤nvme:
lsinitrd /boot/initramfs-3.10.0-957.1.3.el7.x86_64.img | grep -i nvme

4、最后关机,并启用ena模块

aws ec2 modify-instance-attribute --instance-id i-07xxxxxxxxxxxxx --ena-support  #使i-07xxxxxxxxxxxxx虚机支持ena
aws ec2 describe-instances --instance-id i-07xxxxxxxxxxxxx --query "Reservations[].Instances[].EnaSupport"  #查询是否启用了ena模块

最后启动虚机即可生效,查看ena信息:

[root@monitor ~]# modinfo ena
filename:       /lib/modules/3.10.0-957.27.2.el7.x86_64/extra/ena.ko.xz
version:        2.1.1g
license:        GPL
description:    Elastic Network Adapter (ENA)
author:         Amazon.com, Inc. or its affiliates
retpoline:      Y
rhelversion:    7.6
srcversion:     F342F1BEE707726508E3DF8
alias:          pci:v00001D0Fd0000EC21sv*sd*bc*sc*i*
alias:          pci:v00001D0Fd0000EC20sv*sd*bc*sc*i*
alias:          pci:v00001D0Fd00001EC2sv*sd*bc*sc*i*
alias:          pci:v00001D0Fd00000EC2sv*sd*bc*sc*i*
depends:        
vermagic:       3.10.0-957.27.2.el7.x86_64 SMP mod_unload modversions 
parm:           debug:Debug level (0=none,...,16=all) (int)
parm:           rx_queue_size:Rx queue size. The size should be a power of 2. Max value is 8K
 (int)
parm:           force_large_llq_header:Increases maximum supported header size in LLQ mode to 224 bytes, while reducing the maximum TX queue size by half.
 (int)

[root@monitor ~]# ethtool -i ens5
driver: ena
version: 2.1.1g
firmware-version: 
expansion-rom-version: 
bus-info: 0000:00:05.0
supports-statistics: yes
supports-test: no
supports-eeprom-access: no
supports-register-dump: no
supports-priv-flags: no

via:https://gist.github.com/Ray33/ba189a729d81babc99d7cef0fb6fbcd8

MySQL使用存储过程插入100万条记录

业务上为了测试DB写入性能,需测试大量数据写入速度,数据库部署在aws上,4核8G内存100G硬盘,MySQL5.7.27
mysql.png

--创建测试数据库
create database a1 default charset utf8mb4;
--创建测试表
CREATE TABLE `test110` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `comp` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `cid` int(11) DEFAULT NULL,
  `aid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--创建插入存储过程
DELIMITER $$
CREATE PROCEDURE test_insert()
BEGIN
declare i int;
set i=0;
while i<1000000 do
INSERT INTO `test110` VALUES (null,'TMP01','user11','会员中台','2019-08-15 00:00:02','11','22');
set i=i+1;
end while;
end $$
DELIMITER ;

测试如下,速度很快:

mysql> call test_insert();
Query OK, 1 row affected (44.71 sec)

mysql> call test_insert();
Query OK, 1 row affected (44.74 sec)

mysql> call test_insert();
Query OK, 1 row affected (44.71 sec)

mysql> select count(1) from test110;
+----------+
| count(1) |
+----------+
|  3000000 |
+----------+
1 row in set (0.44 sec)

MySQL配置如下my.cnf

[client]
default-character-set = utf8mb4
port        = 3306
socket        = /data/mysql/mysql.sock

[mysql]
default-character-set = utf8mb4
prompt="\u@oatestdb \R:\m:\s [\d]> "
no-auto-rehash

[mysqld]

secure_file_priv = ''
#validate-password = off
slow_query_log = On
log_output = FILE
slow-query-log-file=/data/mysql/slow.log
long_query_time = 30 
log_queries_not_using_indexes = ON

lower_case_table_names=1
auto-increment-increment = 1
auto-increment-offset = 1

log_warnings=1
log_error_verbosity=1

default-storage-engine=INNODB
group_concat_max_len = 18446744073709551615
# generic configuration options
port        = 3306
socket        = /data/mysql/mysql.sock 
pid-file        = /data/mysql/mysqld.pid
basedir        = /usr/local/mysql
datadir         = /data/mysql/db
language    = /usr/local/mysql/share/english 
user        = mysql
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
skip-external-locking
skip-name-resolve
skip-character-set-client-handshake
character-set-server = utf8mb4
explicit_defaults_for_timestamp = true
init_connect='SET NAMES utf8mb4'
character-set-client-handshake = FALSE
collation-server = utf8mb4_unicode_ci
explicit_defaults_for_timestamp

#replication
expire_logs_days = 30
sync-binlog = 1000
server_id=1
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=/data/mysql/binlog/binlog
binlog_format=ROW


relay_log_recovery = 1
skip_slave_start = 0
slave-skip-errors = all
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G

#group replication
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="e4668cea-d7ca-11e6-86b5-18a99b76310d"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "db1:24901"
loose-group_replication_group_seeds= "db1:24901,db2:24902,db3:24903"
loose-group_replication_bootstrap_group= off
loose-group_replication_single_primary_mode=FALSE
loose-group_replication_enforce_update_everywhere_checks= TRUE

skip_name_resolve = 1
open_files_limit    = 65535
back_log = 1024
max_connections = 5000
max_connect_errors = 100000
max_prepared_stmt_count=1048576
table_open_cache = 10240
table_definition_cache = 10240
table_open_cache_instances = 64
thread_stack = 512K
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 16M
join_buffer_size = 16M
thread_cache_size = 7680
query_cache_size = 0
query_cache_type = 0
interactive_timeout = 900
wait_timeout = 900
tmp_table_size = 256M
max_heap_table_size = 256M

# innodb
innodb_buffer_pool_size = 12G 
innodb_data_file_path = IBdata1:1024M;IBdata2:1024M:autoextend
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 32M
innodb_log_file_size = 2G
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_flush_method=O_DSYNC

innodb_io_capacity = 50000
innodb_io_capacity_max = 80000
innodb_flush_neighbors = 0
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_purge_threads = 4
innodb_page_cleaners = 4
innodb_open_files = 65535
innodb_max_dirty_pages_pct = 50
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 4000
innodb_checksum_algorithm = crc32
#innodb_file_format = Barracuda
#innodb_file_format_max = Barracuda
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_file_per_table = 1
innodb_online_alter_log_max_size = 4G
internal_tmp_disk_storage_engine = InnoDB
innodb_stats_on_metadata = 0

[mysqld_safe]
open-files-limit = 8192
log-error=/data/mysql/error.log
pid-file=/data/mysql/mysqld.pid

[mysqldump]
quick
max_allowed_packet = 32M

最新

分类

归档

评论

其它