本地访问 oracle 和线上访问 oracle 速度差异原因排查及解决

# 测试脚本：

	if __name__ == '__main__':
	from sqlalchemy.engine import create_engine
	from sqlalchemy.pool import NullPool
	import pandas as pd
	import time
	import datetime

	db_url = 'xxx'
	engine = create_engine(db_url, poolclass=NullPool)

	sql = "xxx"
	start = datetime.datetime.now()
	df = pd.read_sql_query(sql, engine)
	end = datetime.datetime.now()
	print('start: %s' % start.strftime('%Y-%m-%d %H:%M:%S'))
	print('end: %s' % end.strftime('%Y-%m-%d %H:%M:%S'))
	print('End!')

线上执行状况测试

	(venv) [root@zsj2sat302 test]# python db-test.py
	start: 2021-11-29 02:08:07
	end: 2021-11-29 02:08:07
	End!

# 故障描述

线上生产环境访问某个指定页面速度大致为 2~3 秒
本地开发环境访问该指定页面速度为 20~30 秒
其他页面同样遇到这种情况，严重影响开发效率

# 原因排查

# 猜测一：物理机房位置原因，该 PC 机网段的实际网络链路效率问题

抓包测试，线上抓包，本地抓包。我们本地开发环境代码和线上环境代码是一样的，但是地理位置不一样，本地开发环境即个人 PC 在 china 境内，而产线机器在美国境内。所以首先猜测是因为跨太平洋远距离数据传输问题导致延迟。其他 team 也感觉和产线比有一些慢。

# 猜测二：中国 GFW 问题

因为 GFW 审查，公司内部网络走 HK 代理，猜测可能和代理有关，或者其他相关数据审查有关。有可能，但不好验证。

# 猜测三： Python ORM 框架版本问题

将 python 本地虚拟环境涉及到 db 的第三方 module 版本都调整和产线环境一样后发现速度还是很慢，排除！

cx-Oracle            8.1.0
Flask-SQLAlchemy     2.4.4
pandas               0.25.3
SQLAlchemy           1.3.22

# 猜测四： OS 相关环境问题

可能是 windows 环境或者本地 oracle instant client 的配置或版本问题

# 本地 vmware 安装 centos8.4，测试 centos8.4 上相同 python 脚本执行速度

	[root@localhost ~]$ cat /etc/redhat-release
	[root@localhost ~]# cd /opt/
	[root@localhost opt]# whereis python
	[root@localhost opt]# virtualenv -p /usr/bin/python3.6 venv
	[root@localhost opt]# mkdir test
	[root@localhost opt]# cd test/
	[root@localhost test]# touch db-test.py
	[root@localhost test]# vi db-test.py （赋值测试 code 内容）
	[root@localhost venv]# source bin/activate
	(venv) [root@localhost venv]# pip list
	(venv) [root@localhost venv]# pip install SQLAlchemy==1.3.22
	(venv) [root@localhost venv]# pip install Flask-SQLAlchemy==2.4.4
	(venv) [root@localhost venv]# pip install cx-Oracle==8.1.0
	(venv) [root@localhost test]# pip install pandas==0.25.3
	(venv) [root@localhost test]# python -V
	(venv) [root@localhost test]# python db-test.py （Cannot locate a 64-bit Oracle Client library 错误，下面装 oracle client）
	(venv) [root@localhost opt]# mkdir oracle
	(venv) [root@localhost opt]# cd oracle/
	(venv) [root@localhost oracle]# wget https://download.oracle.com/otn_software/linux/instantclient/185000/instantclient-basic-linux.x64-18.5.0.0.0dbru.zip
	(venv) [root@localhost oracle]# unzip instantclient-basic-linux.x64-18.5.0.0.0dbru.zip
	(venv) [root@localhost oracle]# sudo yum install libaio
	(venv) [root@localhost oracle]# sudo yum install libnsl
	(venv) [root@localhost oracle]# vi ~/.bash_profile （写入 export LD_LIBRARY_PATH=/opt/oracle/instantclient_18_5:$LD_LIBRARY_PATH）
	(venv) [root@localhost oracle]# source ~/.bash_profile
	(venv) [root@localhost test]# python db-test.py
	start: 2021-11-28 19:02:50
	end: 2021-11-28 19:03:00
	End!
	(venv) [root@localhost test]# python db-test.py
	start: 2021-11-28 19:12:51
	end: 2021-11-28 19:12:59
	End!

# Jones 从 mac os 帮忙测试的结果（WFH -> vpn）

	start: 2021-11-29 03:14:20
	end: 2021-11-29 03:14:28
	End!

# Icon 从 windows 虚拟的 ubuntu 帮忙测试的结果

	start: 2021-11-29 10:23:43
	end: 2021-11-29 10:23:53
	End!
	start: 2021-11-29 10:41:27 （换另外一种连接oracle的方式后同样慢）
	end: 2021-11-29 10:41:32

排除！

# 解决办法

# 尝试使用 iptables + NAT 配置跳板机

以三台机器 ip 为例

A: 192.168.0.1 （类比本地 ip）
B: 192.168.0.2 （类比跳板机 ip）
C: 192.168.0.3 （类比 oracle 服务器 ip）

	[root@localhost ~]# vi /etc/sysctl.conf
	# Controls IP packet forwarding
	# net.ipv4.ip_forward = 1 （设为 1 开启 Forword）
	[root@localhost ~]# /sbin/sysctl -p （生效）
	[root@localhost ~]# service iptables status
	[root@localhost ~]# systemctl start iptables.service
	[root@localhost ~]# service iptables status
	[root@localhost ~]# iptables -L -n
	[root@localhost ~]# iptables -X
	[root@localhost ~]# iptables -F
	[root@localhost ~]# iptables -Z

	[root@localhost ~]# iptables -t nat -A PREROUTING -p tcp -m tcp --dport 11521 -j DNAT --to-destination 192.168.0.3:1701
	[root@localhost ~]# iptables -t nat -A POSTROUTING -p tcp -m tcp --dport 1521 -j SNAT --to-source 192.168.0.2
	[root@localhost ~]# iptables-save

	[root@localhost ~]# iptables -A PREROUTING -p tcp -m tcp -d 192.168.0.2 -j DNAT --to-destination 192.168.0.3:1701
	[root@localhost ~]# iptables -A POSTROUTING -p tcp -m tcp -d 192.168.0.1 -j SNAT --to-source 192.168.0.2
	[root@localhost ~]# iptables-save

	[root@localhost ~]# iptables -L -n
	[root@localhost ~]# systemctl restart iptables.service

参考

https://cloud.tencent.com/developer/article/1115750
https://blog.51cto.com/quliren/2047191

# 使用 socat 转发数据包

[root@localhost ~]# yum install -y socat

[root@localhost ~]# #下面是监听 192.168.1.252 网卡的 15672 端口，并将请求转发至 172.17.0.15 的 15672 端口。

[root@localhost ~]#  socat  -d -d -lf /var/log/socat.log TCP4-LISTEN:15672,bind=192.168.1.252,reuseaddr,fork TCP4:172.17.0.15:15672

-d -d 前面两个连续的 - d -d 代表调试信息的输出级别。
-lf /var/log/socat.log 指定输出信息的文件保存位置。
TCP4-LISTEN:15672 在本地建立一个 TCP IPv4 协议的监听端口，也就是转发端口。这里是 15672，请根据实际情况改成你自己需要转发的端口。
bind 指定监听绑定的 IP 地址，不绑定的话将监听服务器上可用的全部 IP。
reuseaddr 绑定一个本地端口。
fork TCP4:172.17.0.15:15672 指的是要转发到的服务器 IP 和端口，这里是 172.17.0.15 的 15672 端口。

	if __name__ == '__main__':
	from sqlalchemy.engine import create_engine
	from sqlalchemy.pool import NullPool
	import pandas as pd
	import time
	import datetime

	db_url = 'oracle://username:password@192.168.1.252:15672/?service_name=xxx'
	engine = create_engine(db_url, poolclass=NullPool)

	sql = "xxx"
	start = datetime.datetime.now()
	df = pd.read_sql_query(sql, engine)
	end = datetime.datetime.now()
	print('start: %s' % start.strftime('%Y-%m-%d %H:%M:%S'))
	print('end: %s' % end.strftime('%Y-%m-%d %H:%M:%S'))
	print('End!')

start: 2021-11-29 05:50:28
end: 2021-11-29 05:50:35
End!

还是慢
效果不行！

参考：

https://blog.chaos.run/dreams/nat-vps-port-forwarding/
https://www.hi-linux.com/posts/61543.html

# VM 环境内调试

这个是可行的，不过 sftp 代码开启自动同步会有一些耗时，并且每次远程连接服务器上的虚拟环境时也会有一些耗时，不过这些耗时相对较短，如果 db 查询实在是太慢的话可以暂时使用这种方法。否则，除非给网络 team 提 ticket 反应这个问题，让他们 tracking 一下链路，否则真的不好处理。

pycharm 配置：

Tools -> Deployment -> Configuration
Tools -> Deployment -> Automatic Uploads(Always)
File -> Settings -> SSH Configurations
File -> Settings -> Python Interpreter -> Add -> SSH Interpreter

参考：

https://zhuanlan.zhihu.com/p/36843200

oracle