mysql简单查询优化

最近爬Boss直聘数据,因为没有使用scrapy这种框架,自建轮子写的整个框架,构造的url和数据存储都在阿里云国际的vps上(1G内存,CPU也是弱鸡),到了40多万时mysql查询就成了瓶颈。

研究了下,主要从数据库和查询语句两方面下手优化:

  1. innodb_buffer_pool_size = 256M (这个最重要,但是我内存很小,设置过高后容易把机器卡死,没办法)
  2. max_allowed_packet = 512M(这里对我没有意义,因为内存很小)
  3. 开启慢查询,针对性的优化了查询语句, 加索引 参考 https://www.mysqltutorial.org/mysql-index/mysql-create-index/ ,还有就是避免 like %s, NULL,所有查询都要加字段where避免select *:

slow_query_log = ON
slow_query_log_file = /var/lib/mysql/tmp_slow.log
long_query_time = 10

(boss) [chen@iZt4n8k2vy63cytrd03m3xZ boss]$ sudo mysqldumpslow -t 10  /var/lib/mysql/tmp_slow.log

Reading mysql slow query log from /var/lib/mysql/tmp_slow.log
Count: 3  Time=20.94s (62s)  Lock=0.00s (0s)  Rows=590255.0 (1770765), root[root]@[47.56.250.177]
  SELECT `job_url` FROM jobs

Count: 68  Time=16.41s (1116s)  Lock=0.00s (0s)  Rows=589155.1 (40062550), root[root]@25hosts
  SELECT `job_url`, `city_code` FROM jobs

Count: 11  Time=16.38s (180s)  Lock=0.00s (0s)  Rows=110793.7 (1218731), root[root]@10hosts
  select `main_url` from start_url_cp LIMIT N, N

Died at /bin/mysqldumpslow line 167, <> chunk 82.

因为脚本框架的原因,需要查询已经存在的url防止重复爬取,所以数据量大时进行全表查询确实费时,加上网络延迟,各个爬虫host都在谷歌GCE上,这个效果目前能接受:

mysql> select count(job_url) from jobs;
+—————-+
| count(job_url) |
+—————-+
| 592501 |
+—————-+
1 row in set (2.35 sec)

之前这个查询非常慢了,效率指数级下降,几十秒甚至十几分钟。参考:

https://blog.csdn.net/u011277123/article/details/72627011

查询历史命令: cat  ~/.mysql_history

百万boss直聘职位数据分析

自从前几天爬了boss直聘7000+职位信息后,一直停滞不前,遇到了几个坑,今天算是重新启动了,感谢亚马逊!
https://geoseis.cn/CoV_tab.html
自适应各种平台显示,新版脚本也格式化成了标签形式。
根据我的爬虫脚本实时监控数据可视化,美国新增病历继续高歌猛进,闭着眼睛都能想到美股或者原油又崩盘了。目前为止161872累计确诊病例,但是不得不说,这个数据应该经得住敲打。
今天我的boss直聘脚本已经突破了1000行,主要还是因为脚本考虑的太周全,超时处理,没有找到相关职位的空页面处理,遇到机器人反爬处理,各种代理设置,反爬策略,多种日志并进(sql和csv),不知不觉就几百上千行了。如果用scrapy这种框架会少很多,不用自己造轮子。

疫情监控脚本可视化BI大屏:

疫情监控BI大屏
一小部分boss直聘数据

废寝忘食地搞了两天,终于搞明白了几个服务。最近天天想方设法寻找合适的ip代理了,现在发现脚本写的越来越长不仅不方便维护,效率也下来了,有时间得整改下,套用下成熟的框架试试。
昨天发现新大陆,昨天晚上连夜研究,今天终于把脚本写好了,利用亚马逊服务器可以虽然更换ip的功能,调用它的api,用python写了个脚本每个两分钟更换一次ip,其实挺简单,就是release, allocate, attach,核心就三步,扔掉原来ip,分配新ip,新ip分配给服务器。当然,其中还有从远程服务器删除旧ip等过程。
美国服务器的ip更新后自动存到远在日本的服务器(上面架设了redis服务),笔记本配置好redis客户端后python脚本连接日本的服务器redis,获取ip。
看似简单,实际操作中还是挺繁琐,很多坑要踩。当然,最重要的是过程,国外的服务器还是不适合,gfw的过滤明显减慢速度。
这两天还配置了squid服务器,但是不知怎么不太好使,可能跟阿布云代理有关。数据没采集多少,💰 没少花,时间更是大把浪费了。