GreenPlum优化参数之数据库参数gp_interconnect_tcp_listener_backlog和操作系统内核参数net.core.somaxconn调优
Tags： gp_interconnect_tcp_listener_backlogGreenPlumnet.core.somaxconn内核参数参数
👉 本文共约479个字，系统预计阅读时间或需2分钟。
现象
使用Navicat做了一个比较大的SQL查询，由多个union all组成，结果报警告：
WARNING: SetupTCPInterconnect: too many expected incoming connections(352), Interconnect setup might possibly fail (seg4 slice41 192.10.26.101:6000 pid=72434)
HINT: Try enlarging the gp_interconnect_tcp_listener_backlog GUC value and OS net.core.somaxconn parameter
但是，并不影响执行结果，可以正常获取到结果。
分析
HINT提示很明显了，涉及OS内核参数net.core.somaxconn和数据库参数gp_interconnect_tcp_listener_backlog
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | [gpadmin@mdw2 ~]$ gpconfig -s gp_interconnect_type Values on all segments are consistent GUC : gp_interconnect_type Master value: tcp Segment value: tcp [gpadmin@mdw2 ~]$ gpconfig -s gp_interconnect_tcp_listener_backlog Values on all segments are consistent GUC : gp_interconnect_tcp_listener_backlog Master value: 128 Segment value: 128 [gpadmin@mdw2 ~]$ exit logout You have new mail in /var/spool/mail/root [root@mdw2 ~]# sysctl -a | grep net.core.somaxconn net.core.somaxconn = 128 |
解决
1、在master和segment上修改OS内核参数net.core.somaxconn的到10240，有关该参数请参考：https://www.xmmup.com/linuxneihecanshuzhinet-core-somaxconn.html
本人提供Oracle、MySQL、PG等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
1 2 3 | sysctl -w net.core.somaxconn=10240 echo "net.core.somaxconn = 10240" >>/etc/sysctl.conf sysctl -p |
2、修改gp_interconnect_tcp_listener_backlog到10240
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | [gpadmin@mdw2 ~]$ gpconfig -c gp_interconnect_tcp_listener_backlog -v 10240 20230206:10:46:16:120117 gpconfig:mdw2:gpadmin-[INFO]:-completed successfully with parameters '-c gp_interconnect_tcp_listener_backlog -v 10240' [gpadmin@mdw2 ~]$ gpconfig -s gp_interconnect_tcp_listener_backlog Values on all segments are consistent GUC : gp_interconnect_tcp_listener_backlog Master value: 128 Segment value: 128 [gpadmin@mdw2 ~]$ gpstop -u 20230206:10:46:37:121220 gpstop:mdw2:gpadmin-[INFO]:-Starting gpstop with args: -u 20230206:10:46:37:121220 gpstop:mdw2:gpadmin-[INFO]:-Gathering information and validating the environment... 20230206:10:46:37:121220 gpstop:mdw2:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information 20230206:10:46:37:121220 gpstop:mdw2:gpadmin-[INFO]:-Obtaining Segment details from master... 20230206:10:46:37:121220 gpstop:mdw2:gpadmin-[INFO]:-Greenplum Version: 'postgres (Greenplum Database) 6.13.0 build commit:4f1adf8e247a9685c19ea02bcaddfdc200937ecd Open Source' 20230206:10:46:37:121220 gpstop:mdw2:gpadmin-[INFO]:-Signalling all postmaster processes to reload [gpadmin@mdw2 ~]$ gpconfig -s gp_interconnect_tcp_listener_backlog Values on all segments are consistent GUC : gp_interconnect_tcp_listener_backlog Master value: 10240 Segment value: 10240 |
重新执行，不再报警告。
