gfirefly或者gevent和django结合使用sqlalchemy的数据库连接池,非阻塞

一. 在gfirefly中和django结合使用数据库连接池

首先这个文档是为了解决gfirefly或者gevent和django结合,让django用sqlalchemy的数据库连接池的问题,我已经解决了
各个软件的版本号:
gevent==1.2.2 [gevent1.1正的版本需要修改gtwisted的源码,具体看“安装记录“文件夹下的内容]
pymysql==0.6.1
SQLAlchemy==1.0.0b5
django==1.8
gfirefly==我的安装包里的版本

0.为了避免意外,django的settings里面的CONN_MAX_AGE设为0,mysql的最大连接数设为500,每个连接数的时间设为最大864000000000

1.其中最重要的是
from gevent import monkey; monkey.patch_all()
如果是mokey.patch_os()和mokey.patch_socket(),那么一个进程只会开一个数据库连接,不会每一个协程都开一个数据库连接,这点很重要,就是是放在spwan里面执行,也不会重新创建数据库连接。
但如果是monkey.pach_all()的话,只要遇到数据库io操作,那么就会开一个新的协程,这里不用刻意防到spwan里面执行。
那么每一个协程都会开一次数据库连接,这样当成千上万个协程的时候,就会有成千上万个数据库连接,这样显然不行,所以要引进数据库连接池

2.为什么用pymysql
据说mysqldb这个包是C写的,用gevent的协程连接的话会阻塞,意思就是mysqldb中一个数据库查询如果死锁了,那么整个gevent进程都会卡在这里不会切换到其他协程
pymysql就不会出现这个问题

3.构建数据库连接池,我们用SQLAlchemy,下面是代码,这里要注意设置mysql的最大连接数和这里的数据库连接池数量匹配, 连接的最大时间设为最大即可

useSqlalchemyPool.py
################################################################################################################

# coding=utf-8
"""
@des: 运行在settings.configure之后
"""
from django.conf import settings
from gtwisted.utils import log
# 配置见这个url   http://docs.sqlalchemy.org/en/rel_0_9/core/engines.html
#Best match: SQLAlchemy 1.0.0b5
from sqlalchemy.pool import NullPool 
#pool_size线程池数量,max_overflow是超过pool_size后允许再开max_overflow个线程,但是马上关闭
POOL_SETTINGS = getattr(settings, 'SQLALCHEMY_POOL_OPTIONS',
                         { 'pool_size':5, 'max_overflow':0})  # {'poolclass':NullPool, 'pool_size':10}
class hashabledict(dict):
    def __hash__(self):
        return hash(tuple(sorted(self.items())))

class hashablelist(list):
    def __hash__(self):
        return hash(tuple(sorted(self)))

class ManagerProxy(object):
    def __init__(self, manager):
        self.manager = manager

    def __getattr__(self, key):
        return getattr(self.manager, key)

    def connect(self, *args, **kwargs):
        if 'conv' in kwargs:
            conv = kwargs['conv']
            if isinstance(conv, dict):
                items = []
                for k, v in conv.items():
                    if isinstance(v, list):
                        v = hashablelist(v)
                    items.append((k, v))
                kwargs['conv'] = hashabledict(items)
        if 'ssl' in kwargs:
            ssl = kwargs['ssl']
            if isinstance(ssl, dict):
                items = []
                for k, v in ssl.items():
                    if isinstance(v, list):
                        v = hashablelist(v)
                    items.append((k, v))
                kwargs['ssl'] = hashabledict(items)
        return self.manager.connect(*args, **kwargs)
import sqlalchemy.pool as pool
pool_initialized = False
#from django.db import load_backend
from django.db.utils import load_backend
def init_pool():
    """
    Init connection pooler
    """
    if not globals().get('pool_initialized', False):
        global pool_initialized
        pool_initialized = True
        for nn in ['default','game']:
            try:
                backendname = settings.DATABASES[nn]['ENGINE']
                backend = load_backend(backendname)
                # replace the database object with a proxy.
                backend.Database = ManagerProxy(pool.manage(backend.Database, **POOL_SETTINGS))
                #backend.Database = pool.manage(backend.Database, **POOL_SETTINGS)
                backend.DatabaseError = backend.Database.DatabaseError
                backend.IntegrityError = backend.Database.IntegrityError
                # logging.info("Connection Pool initialized")
                log.err('pool success')
            except Exception, e:
                log.msg(e)
                log.err('pool error')
            # logging.exception("Connection Pool initialization error")
# Now call init_pool function to initialize the connection pool.

 ################################################################################################################

 

4.如何调用这个数据库连接池呢,这样用appmain.py启动的gfirefly节点就会使用数据库连接池了
其中需要注意的是,注意下面这三段代码的位置顺序

必须是patch_all,我以前是patch_os所以一个进程只一个数据库连接,不管你开多少个连接池
############

from gevent import monkey; monkey.patch_all()

############

用pymysql代替mysqldb

############

import pymysql
pymysql.install_as_MySQLdb()

############

使用数据库连接池
####################

from useSqlalchemyPool import init_pool
init_pool()

####################

 

gfierfly工程里面的
appmain.py

################################################################################################################

#coding:utf8
#这一段是因为部署到supervisord的时候会重置编码,不能print中文
import sys
default_encoding = 'utf-8'
if sys.getdefaultencoding() != default_encoding:
    reload(sys)
    sys.setdefaultencoding(default_encoding)
#end--这一段是因为部署到supervisord的时候会重置编码,不能print中文
#日志设置
# from twisted.python import context
# from twisted.python.log import ILogContext
# context.setDefault(ILogContext,
#                    {"isError": 1,
#                     "system": "-"})
#end--日志设置
# django>=1.6
#from gevent import monkey; monkey.patch_os();monkey.patch_socket();monkey.patch_select();
from gevent import monkey; monkey.patch_all()

import json,sys,logging
from twisted.python import log
from gfirefly.server.server import FFServer
from twisted.logger import globalLogBeginner

class LevelFileLogObserver(log.FileLogObserver):
    def __init__(self, f, level=logging.INFO):
        log.FileLogObserver.__init__(self, f)
        self.logLevel = level

    def emit(self, eventDict):
        if eventDict['isError']:
            level = logging.ERROR
        elif 'level' in eventDict:
            level = eventDict['level']
        else:
            level = logging.INFO
        if level >= self.logLevel:
            log.FileLogObserver.emit(self, eventDict)
    

#!!!!!上线把日志级别改成error--以后检测是否有内存泄露
logger = LevelFileLogObserver(sys.stderr, logging.ERROR) #logging.NOTSET, logging.ERROR
globalLogBeginner.beginLoggingTo([logger.emit])
#log.addObserver(logger.emit)
#日志系统支持日志级别---start#日志系统支持日志级别---end

try:
    from django.conf import settings
    import sys, os
    sys.stdout = sys.stderr  # 调试supervisor的时候需要显示print的日志就加这一句
    sys.path.append('../game')
    from game import settings as project_settings
except Exception,e:
    log.err("=============")
    log.err(e)

# 用pymysql代替mysqldb--支持pypy
import pymysql
pymysql.install_as_MySQLdb()
# end--用pymysql代替mysqldb--支持pypy
settings.configure(DEBUG=False,  # 要想在supervisor中显示出djanog的print信息,这里要设置为True,还没实践
                   DATABASES=project_settings.DATABASES,
                   INSTALLED_APPS=project_settings.INSTALLED_APPS,
                   CACHES=project_settings.CACHES,
                   DATABASE_ROUTERS=project_settings.DATABASE_ROUTERS,
                   #
                   MIDDLEWARE_CLASSES = project_settings.MIDDLEWARE_CLASSES,
                   LANGUAGES = project_settings.LANGUAGES,
                   LANGUAGE_CODE = project_settings.LANGUAGE_CODE,
                   LOCALE_PATHS = project_settings.LOCALE_PATHS,
                   TIME_ZONE = project_settings.TIME_ZONE,
                   USE_I18N = project_settings.USE_I18N,
                   USE_L10N = project_settings.USE_L10N,
                   USE_TZ = project_settings.USE_TZ,
                   TEMPLATES = project_settings.TEMPLATES
                   )


#解决apps.get_model的问题
try:
    import django
    django.setup()
except Exception,e:
    pass
#django启动要做的事情

# # 使用sqlalchemy的连接池
from useSqlalchemyPool import init_pool
init_pool()
# # end--使用sqlalchemy的连接池

try:
    from common.defines import BMOBJ
    BMOBJ.initConfigG()
except Exception,e:
    pass
#end--django启动要做的事
if __name__=="__main__":
    try:
        args = sys.argv
        try:#设置进程名字
            from setproctitle import setproctitle, getproctitle
            setproctitle("firefly: %s" % args[1])
        except Exception, e:
            pass
        servername = None
        config = None
        if len(args)>2:
            servername = args[1]
            config = json.load(open(args[2],'r'))
        else:
            raise ValueError
        dbconf = config.get('db')
        memconf = config.get('memcached')
        sersconf = config.get('servers',{})
        masterconf = config.get('master',{})
        serconfig = sersconf.get(servername)
        ser = FFServer()
        ser.config(serconfig, servername=servername, dbconfig=dbconf, memconfig=memconf, masterconf=masterconf)
        #from common import mylog
        #reload(mylog)
        ser.start()
    except BaseException,e:
        BMOBJ.logerr(e)
        log.err("--------------------------")
        log.err(e)

################################################################################################################

 

5.最重要的一点,在每个数据库操作的方法之前加上这个,这个也是实现数据库连接池的关键

 

###############################

from functools import wraps
def auto_close_db(f):
    "Ensures the database connection is closed when the function returns."
    from django.db import connections
    @wraps(f)
    def wrapper(*args, **kwargs):
        try:
            return f(*args, **kwargs)
        finally:
            for connection in connections.all():
                connection.close()
    return wrapper


@auto_close_db
def test():
    try:
        #获取登录角色信息
        Player = apps.get_model('player', 'Player')
        obj = Player.objects.all()
        lll = obj.count()-1
        obj[random.randint(0,lll)].id
        log.err("login__%s"%gevent.getcurrent())
    except Exception,e:
        log.err("errr__%s"%e)
        pass
test() #这里test放在gevent.spawn里面也行,不放也行,都会开新的协程

###############################

 

二. 在uwsgi+django中使用数据库连接池--这个是独立的和上面没关系

注意在这个就不需要用@auto_close_db了,这个是gevent中独有的用法

settings.py

############################################################################

#coding=utf-8
"""
Django settings for udserver project.

Generated by 'django-admin startproject' using Django 1.8.3.

For more information on this file, see
https://docs.djangoproject.com/en/1.8/topics/settings/

For the full list of settings and their values, see
https://docs.djangoproject.com/en/1.8/ref/settings/
"""

# Build paths inside the project like this: os.path.join(BASE_DIR, ...)
import os, sys
sys.path.insert(0, "../..")
from lzserver.gconfigs import dbconfig_game, caches_game
#modify by yx
if 1:#如果要使用pypy或者数据库连接池的话,就要加这里
    import pymysql
    pymysql.install_as_MySQLdb()
#end--modify by yx

BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))

# Quick-start development settings - unsuitable for production
# See https://docs.djangoproject.com/en/1.8/howto/deployment/checklist/

# SECURITY WARNING: keep the secret key used in production secret!
SECRET_KEY = '14%4&t&aq83i34lw(789451fds)_6z#y-2qeio4ds1+&v8q2y_7j'

# SECURITY WARNING: don't run with debug turned on in production!
DEBUG = False

ALLOWED_HOSTS = ["*"]


# Application definition

INSTALLED_APPS = (
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    'django.contrib.staticfiles',
    #'common',
    'gm',
    'player',
    'mgr'
)

MIDDLEWARE_CLASSES = (
    'django.contrib.sessions.middleware.SessionMiddleware',
    'django.middleware.locale.LocaleMiddleware',
    'django.middleware.common.CommonMiddleware',
    #'django.middleware.csrf.CsrfViewMiddleware',
    'django.contrib.auth.middleware.AuthenticationMiddleware',
    'django.contrib.auth.middleware.SessionAuthenticationMiddleware',
    'django.contrib.messages.middleware.MessageMiddleware',
    #'django.middleware.clickjacking.XFrameOptionsMiddleware',
    'django.middleware.security.SecurityMiddleware',
)

ROOT_URLCONF = 'game.urls'

TEMPLATES = [
    {
        'BACKEND': 'django.template.backends.django.DjangoTemplates',
        'DIRS': [os.path.join(BASE_DIR, "templates")],  #],
        'APP_DIRS': True,
        'OPTIONS': {
            'context_processors': [
                'django.template.context_processors.debug',
                'django.template.context_processors.request',
                'django.core.context_processors.i18n',
                'django.contrib.auth.context_processors.auth',
                'django.contrib.messages.context_processors.messages',
            ],
        },
    },
]

WSGI_APPLICATION = 'game.wsgi.application'


# Database
# https://docs.djangoproject.com/en/1.8/ref/settings/#databases

DATABASES = dbconfig_game


#使用sqlalchemy的连接池---用第一步中第3个文件
from useSqlalchemyPool import init_pool
init_pool()
# end--使用sqlalchemy的连接池

#DATABASE_ROUTERS = ['common.config.AdminRouter','common.config.UdRouter']

CACHES = caches_game

# Internationalization
# https://docs.djangoproject.com/en/1.8/topics/i18n/
LANGUAGES = (
    ('zh_cn', (u'中文简体')),
    ('zh_tw', (u'中文繁體')),
)

#翻译文件所在目录,需要手工创建
LOCALE_PATHS = (
    os.path.join(BASE_DIR, 'locale'),
)

LANGUAGE_CODE = 'zh_cn'

TIME_ZONE = 'Asia/Shanghai'

USE_I18N = True

USE_L10N = True

USE_TZ = False


# Static files (CSS, JavaScript, Images)
# https://docs.djangoproject.com/en/1.8/howto/static-files/



# add by yx

STATIC_URL = '/static/'
STATIC_ROOT = os.path.join(BASE_DIR, 'static_root')
STATICFILES_DIRS = (
                    os.path.join(BASE_DIR, "static"),
)

MEDIA_URL = '/media/'
MEDIA_ROOT = os.path.join(BASE_DIR, 'media')

SESSION_COOKIE_NAME = "gameserversessionlzid"
SESSION_EXPIRE_AT_BROWSER_CLOSE = True
SESSION_ENGINE = "django.contrib.sessions.backends.cache" #如果在twisted里用,就必须在安装选项里面写出来

AUTHENTICATION_BACKENDS = (  
    'django.contrib.auth.backends.ModelBackend',
    #'business.auth.BusinessBackend',   
)  

LOGGING = {
    'version': 1,
    'disable_existing_loggers': False,
    'filters': {
        'require_debug_false': {
            '()': 'django.utils.log.RequireDebugFalse'
        }
    },
    'handlers': {
        'mail_admins': {
            'level': 'ERROR',
            'filters': ['require_debug_false'],
            'class': 'django.utils.log.AdminEmailHandler'
        }
    },
    'loggers': {
        'django.request': {
            'handlers': ['mail_admins'],
            'level': 'ERROR',
            'propagate': True,
        },
    }
}

DATETIME_FORMAT = 'Y-m-d H:i:sO'
DATE_FORMAT = 'Y-m-d'

DATABASE_ROUTERS = ['common.databasesrouters.DatabaseAppsRouter']

############################################################################


分享到: 微信 更多