webpy+DBUtils是如何将数据库连接数占满的
妙音
posted @ 2014年1月10日 11:41
in webpy
, 6478 阅读
webpy简单易用，而且非常轻量。这些特点很容易让人忽略它作为web服务器的主要目标，而把它当作一个小模块使用。
现在就把它当作小模块，只使用它操作数据库的api.
下面是个小程序，目的就是访问数据库，然后关闭连接。
import web
def main():
db = web.database(port=5432, host='localhost', dbn='postgres', db='tax',
user='postgres', pw='postgres')
rs = db.query("select count(*) as count from pg_stat_activity")
print list(rs)[0].count
db.ctx.db.close()
if __name__ == '__main__':
main()
在正常情况下，上面程序表现很正常。不会有任何问题.
如果变成周期程序，并且安装了DBUtils模块
import time
import web
import psycopg2
def main():
db = web.database(port=5432, host='localhost', dbn='postgres', db='tax',
user='postgres', pw='postgres')
rs = db.query("select count(*) as count from pg_stat_activity")
print list(rs)[0].count
db.ctx.db.close()
if __name__ == '__main__':
while True:
main()
time.sleep(1)
输出的count值不断增加，最终将连接数占满。
为什么close没有效果，数据库连接数不断增加？
第一个问题：close关闭的是什么？
在有DBUitls时，close关闭时的PooledDB.connection()产生的连接.
在无DBUtils时，close关闭的时psycopg2.connect()产生的连接.
第二个问题: 在有DBUtils时close为什么不会减少连接数？
写了段代码，来看看close是怎么回事
import psycopg2
from DBUtils import PooledDB
def main():
#mincached,maxconnections限制连接池中的连接数
pool = PooledDB.PooledDB(psycopg2, port=5432, host='localhost', dbname='postgres', database='tax',
user='postgres', password='postgres',
mincached=2, maxconnections=2)
# 从连接池中取一个连接
conn = pool.connection()
cur = conn.cursor()
cur.execute("select count(*) as count from pg_stat_activity")
rs = cur.fetchone()
print list(rs)
cur.close()
# 不会影响这个连接池的连接数量. pool.close()关闭连接池，会关闭所有连接.
conn.close()
if __name__ == '__main__':
while True:
main()
time.sleep(1)
发现连接数始终不是变，单个连接的close，不会影响到这个连接池。
试试将上面cur.close() conn.close()注释掉，会导致连接数增加吗？
答案也是不会. pool是局部变量，变量销毁，连接会自动关闭。
得到结论：
* 单个连接关闭，不会影响连接池
* 连接池变量释放，或者程序停止，连接会自动关闭
可推断webpy中定有全局变量，不释放而导致连接增加.
顺着这个思路阅读web源码，发现web.ctx是个TreadedDict类型
class ThreadedDict(threadlocal):
"""
Thread local storage.
>>> d = ThreadedDict()
>>> d.x = 1
>>> d.x
1
>>> import threading
>>> def f(): d.x = 2
...
>>> t = threading.Thread(target=f)
>>> t.start()
>>> t.join()
>>> d.x
1
"""
# 原因就在这里
_instances = set()
def __init__(self):
ThreadedDict._instances.add(self)
def __del__(self):
ThreadedDict._instances.remove(self)
def __hash__(self):
return id(self)
....
原来它有个静态属性ThreadedDict._instances保存每个实例，而每个实例中又保存web.database产生的PooledDB对象.
所以pool一直不释放。
所以原因就是: web.database每次新建一个PooledDB对象，而对象又保存在全局变量TreadedDict._instance中.
由于不断新建连接池，又不释放连接池，最终导致连接数不断增加。
webpy如何解决这个问题？
使用polling参数，禁止连接池
db = web.database(port=5432, host='localhost', dbn='postgres', db='tax',
user='postgres', pw='postgres', pooling=False)
这上面得出的结论对oracle和其它类型的数据库同样有效.
2016年7月22日 23:12
db = web.database(port=5432, host='localhost', dbn='postgres', db='tax',
user='postgres', pw='postgres')
不要建在循环里面。
2020年12月15日 13:57
thank you for a great post.
2023年7月31日 05:09
Hi, first of all, thank you for all this information. Personally I was able to vote on your form and I am very happy with it. I am from Imamia Quran Academy. Online Shia Quran Academy is using this radical idea of taking online classes so that any environmental factor could not disrupt the learning process. Students across the world can be benefited from this learning procedure. Join our Online Shia Quran Academy now, and Start your free 3 days Trial.
2023年10月14日 10:51
When it comes to home makeover projects, there's always something fun and exciting about exploring the wealth of decor options you can use to transform your space into something truly your own.
https://www.21newfurniture.com/
ALT Furniture, ALT Outdoor Furniture, ALT Garden - ALT
2024年1月08日 18:05
The Board model paper" typically refers to a sample or model question paper that is designed by educational boards or institutions for various exams. These papers serve as practice material for students preparing for exams, providing them with an idea of the question format, difficulty level, and the type of content that may be covered in the actual examination. boardmodelpaper.com Model papers are usually created for specific subjects or courses. They cover a range of topics and chapters that students are expected to have studied during the academic term. Students often use these educational board model papers as an integral part of their exam preparation strategy, helping them familiarize themselves with the exam pattern and refine their understanding of the subject matter.