多线程 - 为何python子线程会等待很长时间

Question

背景：运行一个爬虫，开了10个线程，每个线程先去爬取指定数量的代理作为自己的代理池，然后开始工作。

问题：下面是爬虫日志的两行，可以看到在第一行任务处等待了45秒，而这里不过是输出一条信息，十分不理解为什么等了这么长时间？日志中像这样动辄十几秒什么一两分钟的情形基本都发生在爬取代理的过程中，是否意味着这个任务的代码有问题？

15:57:50    INFO    Thread-2    the proxy already in list, skip
15:58:35    INFO    Thread-10       {'https': '117.170.28.178:8123'} download 2111 bytes in 0.75 seconds(average in 1 tries), need 10, available count: 7    

思考：我理解python的多线程调度机制是完成了一条指令后，就可以调用其他线程了，并不是一定要等着这个指令得到了预期的结果，那么如果我的代码写的有问题也不至于影响他的调度吧。这个线程没有进展又不将CPU的使用权让渡出来，GIL为什么不剥夺这个线程的运行时间，总不至于是在等待某个程序块或者函数运行完毕吧。

Answer 1

It is found that the described problem is mainly caused by the improper use of SQLite. The previous design was to open a connection until the verification of all agents in the agent pool is completed and a certain number of agents are captured before closing the connection. Whenever there is an agent The addition, modification, and deletion of information are all written to the data file, which causes coarse-grained SQLite to be locked for a long time.

After discovering this problem, we optimized it. When starting a new connection, we will close the connection immediately after reading the inventory agent. After that, all agent new, updated, and deleted data will be temporarily stored in class variables until all required agents are obtained. Open a new connection, use executemany to update the data, then close the connection, complete the scheduled task, and the speed will increase.

But I still can’t understand why in the original situation, the thread scheduling mechanism allows the thread blocked by the database to always occupy resources instead of switching in time?

Answer 2

So your thread is blocked at the level of writing to the database. Since you are using sqlite, I will give you another ancient power to speed up the database writing operation:

import sqlite3

...
conn = sqlite3.connect('xxx.db')
cur = conn.cursor()
cur.execute("CREATE TABLE xxx")  # 建个表
cur.execute("PRAGMA synchronous = OFF")  # 关闭磁盘同步
cur.execute("BEGIN TRANSACTION")  # 开始事务处理
cur.executemany("INSERT INTO names VALUES (?,?)", lst)  # 批量插入爬到的数据
conn.commit()
conn.close()
...

Three methods are used to speed upsqlitewriting speed

1. Turn off disk synchronization

2. SQLite Transactions

3. executemany batch insert

PS: In addition, if you have ample memory, you can throw the database file into the tmpfs directory, which will greatly eliminate the impact of disk I/O (equivalent to writing directly in memory)