From a46964ad11a85effa833decb81384b478a0cf75d Mon Sep 17 00:00:00 2001 From: Brian Lamar Date: Wed, 10 Aug 2011 13:21:36 -0400 Subject: Allows multiple MySQL connections to be maintained using eventlet's db_pool. --- nova/db/sqlalchemy/session.py | 92 ++++++++++++++++++++++++++++++++----------- nova/flags.py | 6 +++ 2 files changed, 74 insertions(+), 24 deletions(-) diff --git a/nova/db/sqlalchemy/session.py b/nova/db/sqlalchemy/session.py index 4a9a28f43..726a801af 100644 --- a/nova/db/sqlalchemy/session.py +++ b/nova/db/sqlalchemy/session.py @@ -19,37 +19,81 @@ Session Handling for SQLAlchemy backend """ -from sqlalchemy import create_engine -from sqlalchemy import pool -from sqlalchemy.orm import sessionmaker +import eventlet.patcher +eventlet.patcher.monkey_patch() -from nova import exception -from nova import flags +import eventlet.db_pool +import sqlalchemy.orm +import sqlalchemy.pool + +import nova.exception +import nova.flags +import nova.log + + +FLAGS = nova.flags.FLAGS +LOG = nova.log.getLogger("nova.db.sqlalchemy") + + +try: + import MySQLdb +except ImportError: + LOG.debug(_("Unable to load MySQLdb module.")) -FLAGS = flags.FLAGS _ENGINE = None _MAKER = None def get_session(autocommit=True, expire_on_commit=False): - """Helper method to grab session""" - global _ENGINE - global _MAKER - if not _MAKER: - if not _ENGINE: - kwargs = {'pool_recycle': FLAGS.sql_idle_timeout, - 'echo': False} - - if FLAGS.sql_connection.startswith('sqlite'): - kwargs['poolclass'] = pool.NullPool - - _ENGINE = create_engine(FLAGS.sql_connection, - **kwargs) - _MAKER = (sessionmaker(bind=_ENGINE, - autocommit=autocommit, - expire_on_commit=expire_on_commit)) + """Return a SQLAlchemy session.""" + global _ENGINE, _MAKER + + if _MAKER is None or _ENGINE is None: + _ENGINE = get_engine() + _MAKER = get_maker(_ENGINE, autocommit, expire_on_commit) + session = _MAKER() - session.query = exception.wrap_db_error(session.query) - session.flush = exception.wrap_db_error(session.flush) + session.query = nova.exception.wrap_db_error(session.query) + session.flush = nova.exception.wrap_db_error(session.flush) return session + + +def get_engine(): + """Return a SQLAlchemy engine.""" + connection_dict = sqlalchemy.engine.url.make_url(FLAGS.sql_connection) + engine_args = { + "pool_recycle": FLAGS.sql_idle_timeout, + "echo": False, + } + + LOG.info(_("SQL connection: %s") % FLAGS.sql_connection) + + if "sqlite" in connection_dict.drivername: + engine_args["poolclass"] = sqlalchemy.pool.NullPool + + if "mysql" in connection_dict.drivername: + LOG.info(_("Using MySQL/eventlet DB connection pool.")) + pool_args = { + "db": connection_dict.database, + "user": connection_dict.username, + "passwd": connection_dict.password, + "host": connection_dict.host, + "min_size": FLAGS.sql_min_pool_size, + "max_size": FLAGS.sql_max_pool_size, + "max_idle": FLAGS.sql_idle_timeout, + "max_age": FLAGS.sql_idle_timeout, + } + creator = eventlet.db_pool.ConnectionPool(MySQLdb, **pool_args) + engine_args["pool_size"] = FLAGS.sql_max_pool_size + engine_args["pool_timeout"] = FLAGS.sql_pool_timeout + engine_args["creator"] = creator.create + + return sqlalchemy.create_engine(FLAGS.sql_connection, **engine_args) + + +def get_maker(engine, autocommit=True, expire_on_commit=False): + """Return a SQLAlchemy sessionmaker using the given engine.""" + return sqlalchemy.orm.sessionmaker(bind=engine, + autocommit=autocommit, + expire_on_commit=expire_on_commit) diff --git a/nova/flags.py b/nova/flags.py index eb6366ed9..fa5528c8c 100644 --- a/nova/flags.py +++ b/nova/flags.py @@ -345,6 +345,12 @@ DEFINE_string('logdir', None, 'output to a per-service log file in named ' 'directory') DEFINE_integer('logfile_mode', 0644, 'Default file mode of the logs.') DEFINE_string('sqlite_db', 'nova.sqlite', 'file name for sqlite') +DEFINE_integer('sql_pool_timeout', 30, + 'seconds to wait for connection from pool before erroring') +DEFINE_integer('sql_min_pool_size', 10, + 'minimum number of SQL connections to pool') +DEFINE_integer('sql_max_pool_size', 10, + 'maximum number of SQL connections to pool') DEFINE_string('sql_connection', 'sqlite:///$state_path/$sqlite_db', 'connection string for sql database') -- cgit From 93c4a691c28668d62103b2ae2f90b284950cd95f Mon Sep 17 00:00:00 2001 From: Brian Lamar Date: Wed, 10 Aug 2011 14:01:18 -0400 Subject: Make sure to not use MySQLdb if you don't have it. --- nova/db/sqlalchemy/session.py | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) diff --git a/nova/db/sqlalchemy/session.py b/nova/db/sqlalchemy/session.py index 726a801af..6ef2d7b2c 100644 --- a/nova/db/sqlalchemy/session.py +++ b/nova/db/sqlalchemy/session.py @@ -38,7 +38,7 @@ LOG = nova.log.getLogger("nova.db.sqlalchemy") try: import MySQLdb except ImportError: - LOG.debug(_("Unable to load MySQLdb module.")) + MySQLdb = None _ENGINE = None @@ -72,8 +72,8 @@ def get_engine(): if "sqlite" in connection_dict.drivername: engine_args["poolclass"] = sqlalchemy.pool.NullPool - if "mysql" in connection_dict.drivername: - LOG.info(_("Using MySQL/eventlet DB connection pool.")) + if MySQLdb and "mysql" in connection_dict.drivername: + LOG.info(_("Using MySQLdb/eventlet DB connection pool.")) pool_args = { "db": connection_dict.database, "user": connection_dict.username, -- cgit From ad3ccef3e86220b480a114bb70eaa9def2abd430 Mon Sep 17 00:00:00 2001 From: Brian Lamar Date: Wed, 10 Aug 2011 14:03:16 -0400 Subject: Removed un-needed log line. --- nova/db/sqlalchemy/session.py | 2 -- 1 file changed, 2 deletions(-) diff --git a/nova/db/sqlalchemy/session.py b/nova/db/sqlalchemy/session.py index 6ef2d7b2c..7ebe000e8 100644 --- a/nova/db/sqlalchemy/session.py +++ b/nova/db/sqlalchemy/session.py @@ -67,8 +67,6 @@ def get_engine(): "echo": False, } - LOG.info(_("SQL connection: %s") % FLAGS.sql_connection) - if "sqlite" in connection_dict.drivername: engine_args["poolclass"] = sqlalchemy.pool.NullPool -- cgit From 8331fee7695a40824ae1bd24c52b22987b5f3507 Mon Sep 17 00:00:00 2001 From: Brian Lamar Date: Wed, 10 Aug 2011 15:06:04 -0400 Subject: elif and FLAG feedback --- nova/db/sqlalchemy/session.py | 3 +-- 1 file changed, 1 insertion(+), 2 deletions(-) diff --git a/nova/db/sqlalchemy/session.py b/nova/db/sqlalchemy/session.py index 7ebe000e8..81cb9fad0 100644 --- a/nova/db/sqlalchemy/session.py +++ b/nova/db/sqlalchemy/session.py @@ -70,7 +70,7 @@ def get_engine(): if "sqlite" in connection_dict.drivername: engine_args["poolclass"] = sqlalchemy.pool.NullPool - if MySQLdb and "mysql" in connection_dict.drivername: + elif MySQLdb and "mysql" in connection_dict.drivername: LOG.info(_("Using MySQLdb/eventlet DB connection pool.")) pool_args = { "db": connection_dict.database, @@ -80,7 +80,6 @@ def get_engine(): "min_size": FLAGS.sql_min_pool_size, "max_size": FLAGS.sql_max_pool_size, "max_idle": FLAGS.sql_idle_timeout, - "max_age": FLAGS.sql_idle_timeout, } creator = eventlet.db_pool.ConnectionPool(MySQLdb, **pool_args) engine_args["pool_size"] = FLAGS.sql_max_pool_size -- cgit From 4d2d064e9da37ce72010408bc1aad8ca67708462 Mon Sep 17 00:00:00 2001 From: Brian Lamar Date: Wed, 10 Aug 2011 16:20:31 -0400 Subject: Support for postgresql. --- nova/db/sqlalchemy/session.py | 37 +++++++++++++++++++++++++------------ 1 file changed, 25 insertions(+), 12 deletions(-) diff --git a/nova/db/sqlalchemy/session.py b/nova/db/sqlalchemy/session.py index 81cb9fad0..07ca27bab 100644 --- a/nova/db/sqlalchemy/session.py +++ b/nova/db/sqlalchemy/session.py @@ -41,6 +41,12 @@ except ImportError: MySQLdb = None +try: + import psycopg2 +except ImportError: + psycopg2 = None + + _ENGINE = None _MAKER = None @@ -62,28 +68,35 @@ def get_session(autocommit=True, expire_on_commit=False): def get_engine(): """Return a SQLAlchemy engine.""" connection_dict = sqlalchemy.engine.url.make_url(FLAGS.sql_connection) + engine_args = { "pool_recycle": FLAGS.sql_idle_timeout, + "pool_size": FLAGS.sql_max_pool_size, + "pool_timeout": FLAGS.sql_pool_timeout, "echo": False, } + pool_args = { + "db": connection_dict.database, + "user": connection_dict.username, + "passwd": connection_dict.password, + "host": connection_dict.host, + "min_size": FLAGS.sql_min_pool_size, + "max_size": FLAGS.sql_max_pool_size, + "max_idle": FLAGS.sql_idle_timeout, + } + if "sqlite" in connection_dict.drivername: + del engine_args["pool_size"] + del engine_args["pool_timeout"] engine_args["poolclass"] = sqlalchemy.pool.NullPool elif MySQLdb and "mysql" in connection_dict.drivername: - LOG.info(_("Using MySQLdb/eventlet DB connection pool.")) - pool_args = { - "db": connection_dict.database, - "user": connection_dict.username, - "passwd": connection_dict.password, - "host": connection_dict.host, - "min_size": FLAGS.sql_min_pool_size, - "max_size": FLAGS.sql_max_pool_size, - "max_idle": FLAGS.sql_idle_timeout, - } creator = eventlet.db_pool.ConnectionPool(MySQLdb, **pool_args) - engine_args["pool_size"] = FLAGS.sql_max_pool_size - engine_args["pool_timeout"] = FLAGS.sql_pool_timeout + engine_args["creator"] = creator.create + + elif psycopg2 and "postgresql" in connection_dict.drivername: + creator = eventlet.db_pool.ConnectionPool(psycopg2, **pool_args) engine_args["creator"] = creator.create return sqlalchemy.create_engine(FLAGS.sql_connection, **engine_args) -- cgit From b121cd266d3d5e1719e644d6bd82d6402f13d2e2 Mon Sep 17 00:00:00 2001 From: Brian Lamar Date: Thu, 11 Aug 2011 11:55:02 -0400 Subject: Logging for SQLAlchemy type. --- nova/db/sqlalchemy/session.py | 14 +++++++++++--- 1 file changed, 11 insertions(+), 3 deletions(-) diff --git a/nova/db/sqlalchemy/session.py b/nova/db/sqlalchemy/session.py index 07ca27bab..073e4ae49 100644 --- a/nova/db/sqlalchemy/session.py +++ b/nova/db/sqlalchemy/session.py @@ -77,10 +77,8 @@ def get_engine(): } pool_args = { - "db": connection_dict.database, - "user": connection_dict.username, - "passwd": connection_dict.password, "host": connection_dict.host, + "user": connection_dict.username, "min_size": FLAGS.sql_min_pool_size, "max_size": FLAGS.sql_max_pool_size, "max_idle": FLAGS.sql_idle_timeout, @@ -92,10 +90,20 @@ def get_engine(): engine_args["poolclass"] = sqlalchemy.pool.NullPool elif MySQLdb and "mysql" in connection_dict.drivername: + LOG.info(_("Using mysql/eventlet db_pool.")) + pool_args.update({ + "db": connection_dict.database, + "passwd": connection_dict.password, + }) creator = eventlet.db_pool.ConnectionPool(MySQLdb, **pool_args) engine_args["creator"] = creator.create elif psycopg2 and "postgresql" in connection_dict.drivername: + LOG.info(_("Using postgresql/eventlet db_pool.")) + pool_args.update({ + "database": connection_dict.database, + "password": connection_dict.password, + }) creator = eventlet.db_pool.ConnectionPool(psycopg2, **pool_args) engine_args["creator"] = creator.create -- cgit From 253d75e71beb1ce9c65e84233a3178f95f82d77d Mon Sep 17 00:00:00 2001 From: Brian Lamar Date: Thu, 11 Aug 2011 12:02:05 -0400 Subject: More logging. --- nova/db/sqlalchemy/session.py | 3 +++ 1 file changed, 3 insertions(+) diff --git a/nova/db/sqlalchemy/session.py b/nova/db/sqlalchemy/session.py index 073e4ae49..141b7bf37 100644 --- a/nova/db/sqlalchemy/session.py +++ b/nova/db/sqlalchemy/session.py @@ -107,6 +107,9 @@ def get_engine(): creator = eventlet.db_pool.ConnectionPool(psycopg2, **pool_args) engine_args["creator"] = creator.create + LOG.debug(_("SQLAlchemy Engine Arguments: %(engine_args)s") % locals()) + LOG.debug(_("Eventlet Pool Arguments: %(pool_args)s") % locals()) + return sqlalchemy.create_engine(FLAGS.sql_connection, **engine_args) -- cgit From 49da55f7952f8daecf6df9498769b336af95ce6d Mon Sep 17 00:00:00 2001 From: Brian Lamar Date: Thu, 11 Aug 2011 14:27:14 -0400 Subject: Removed postgres, bug in current ubuntu package which won't allow it to work easily. Will add a bug in LP. --- nova/db/sqlalchemy/session.py | 31 +++++-------------------------- 1 file changed, 5 insertions(+), 26 deletions(-) diff --git a/nova/db/sqlalchemy/session.py b/nova/db/sqlalchemy/session.py index 141b7bf37..ffa3c747c 100644 --- a/nova/db/sqlalchemy/session.py +++ b/nova/db/sqlalchemy/session.py @@ -41,12 +41,6 @@ except ImportError: MySQLdb = None -try: - import psycopg2 -except ImportError: - psycopg2 = None - - _ENGINE = None _MAKER = None @@ -76,14 +70,6 @@ def get_engine(): "echo": False, } - pool_args = { - "host": connection_dict.host, - "user": connection_dict.username, - "min_size": FLAGS.sql_min_pool_size, - "max_size": FLAGS.sql_max_pool_size, - "max_idle": FLAGS.sql_idle_timeout, - } - if "sqlite" in connection_dict.drivername: del engine_args["pool_size"] del engine_args["pool_timeout"] @@ -94,22 +80,15 @@ def get_engine(): pool_args.update({ "db": connection_dict.database, "passwd": connection_dict.password, + "host": connection_dict.host, + "user": connection_dict.username, + "min_size": FLAGS.sql_min_pool_size, + "max_size": FLAGS.sql_max_pool_size, + "max_idle": FLAGS.sql_idle_timeout, }) creator = eventlet.db_pool.ConnectionPool(MySQLdb, **pool_args) engine_args["creator"] = creator.create - elif psycopg2 and "postgresql" in connection_dict.drivername: - LOG.info(_("Using postgresql/eventlet db_pool.")) - pool_args.update({ - "database": connection_dict.database, - "password": connection_dict.password, - }) - creator = eventlet.db_pool.ConnectionPool(psycopg2, **pool_args) - engine_args["creator"] = creator.create - - LOG.debug(_("SQLAlchemy Engine Arguments: %(engine_args)s") % locals()) - LOG.debug(_("Eventlet Pool Arguments: %(pool_args)s") % locals()) - return sqlalchemy.create_engine(FLAGS.sql_connection, **engine_args) -- cgit From 3017d3a7cd9cd4928a5e5247054b877e63fac095 Mon Sep 17 00:00:00 2001 From: Brian Lamar Date: Thu, 11 Aug 2011 14:36:29 -0400 Subject: Silly fixes. --- nova/db/sqlalchemy/session.py | 10 ++++------ 1 file changed, 4 insertions(+), 6 deletions(-) diff --git a/nova/db/sqlalchemy/session.py b/nova/db/sqlalchemy/session.py index ffa3c747c..07f281938 100644 --- a/nova/db/sqlalchemy/session.py +++ b/nova/db/sqlalchemy/session.py @@ -65,19 +65,15 @@ def get_engine(): engine_args = { "pool_recycle": FLAGS.sql_idle_timeout, - "pool_size": FLAGS.sql_max_pool_size, - "pool_timeout": FLAGS.sql_pool_timeout, "echo": False, } if "sqlite" in connection_dict.drivername: - del engine_args["pool_size"] - del engine_args["pool_timeout"] engine_args["poolclass"] = sqlalchemy.pool.NullPool elif MySQLdb and "mysql" in connection_dict.drivername: LOG.info(_("Using mysql/eventlet db_pool.")) - pool_args.update({ + pool_args = { "db": connection_dict.database, "passwd": connection_dict.password, "host": connection_dict.host, @@ -85,8 +81,10 @@ def get_engine(): "min_size": FLAGS.sql_min_pool_size, "max_size": FLAGS.sql_max_pool_size, "max_idle": FLAGS.sql_idle_timeout, - }) + } creator = eventlet.db_pool.ConnectionPool(MySQLdb, **pool_args) + engine_args["pool_size"] = FLAGS.sql_max_pool_size + engine_args["pool_timeout"] = FLAGS.sql_pool_timeout engine_args["creator"] = creator.create return sqlalchemy.create_engine(FLAGS.sql_connection, **engine_args) -- cgit