From e1b5c612472b9d1acf47383c0bf75b555daff2e6 Mon Sep 17 00:00:00 2001
From: Master ScienceSim
Date: Thu, 4 Feb 2010 13:19:30 -0800
Subject: Updated MySQL connection management to use the MySQL connection
pooling. This should accommodate various timeout problems that exist with the
current connection pool code in a more general and standard way.
---
OpenSim/Data/MySQL/MySQLGridData.cs | 338 +++++++++++++-----------------------
1 file changed, 125 insertions(+), 213 deletions(-)
(limited to 'OpenSim/Data/MySQL/MySQLGridData.cs')
diff --git a/OpenSim/Data/MySQL/MySQLGridData.cs b/OpenSim/Data/MySQL/MySQLGridData.cs
index 1ec2609..f4e7b85 100644
--- a/OpenSim/Data/MySQL/MySQLGridData.cs
+++ b/OpenSim/Data/MySQL/MySQLGridData.cs
@@ -31,6 +31,7 @@ using System.Data;
using System.Reflection;
using System.Threading;
using log4net;
+using MySql.Data.MySqlClient;
using OpenMetaverse;
using OpenSim.Framework;
@@ -43,49 +44,9 @@ namespace OpenSim.Data.MySQL
{
private static readonly ILog m_log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
- ///
- /// MySQL Database Manager
- ///
- private MySQLManager database;
-
-
- ///
- /// Better DB manager. Swap-in replacement too.
- ///
- public Dictionary m_dbconnections = new Dictionary();
-
- public int m_maxConnections = 10;
- public int m_lastConnect;
-
- public MySQLSuperManager GetLockedConnection()
- {
- int lockedCons = 0;
- while (true)
- {
- m_lastConnect++;
-
- // Overflow protection
- if (m_lastConnect == int.MaxValue)
- m_lastConnect = 0;
-
- MySQLSuperManager x = m_dbconnections[m_lastConnect % m_maxConnections];
- if (!x.Locked)
- {
- x.GetLock();
- return x;
- }
-
- lockedCons++;
- if (lockedCons > m_maxConnections)
- {
- lockedCons = 0;
- Thread.Sleep(1000); // Wait some time before searching them again.
- m_log.Debug(
- "WARNING: All threads are in use. Probable cause: Something didnt release a mutex properly, or high volume of requests inbound.");
- }
- }
- }
-
+ private MySQLManager m_database;
+ private object m_dbLock = new object();
+ private string m_connectionString;
override public void Initialise()
{
@@ -106,49 +67,17 @@ namespace OpenSim.Data.MySQL
/// connect string.
override public void Initialise(string connect)
{
- if (connect != String.Empty)
- {
- database = new MySQLManager(connect);
+ m_connectionString = connect;
+ m_database = new MySQLManager(connect);
- m_log.Info("Creating " + m_maxConnections + " DB connections...");
- for (int i = 0; i < m_maxConnections; i++)
- {
- m_log.Info("Connecting to DB... [" + i + "]");
- MySQLSuperManager msm = new MySQLSuperManager();
- msm.Manager = new MySQLManager(connect);
- m_dbconnections.Add(i, msm);
- }
+ // This actually does the roll forward assembly stuff
+ Assembly assem = GetType().Assembly;
- }
- else
+ using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
{
- m_log.Warn("Using deprecated mysql_connection.ini. Please update database_connect in GridServer_Config.xml and we'll use that instead");
- IniFile GridDataMySqlFile = new IniFile("mysql_connection.ini");
- string settingHostname = GridDataMySqlFile.ParseFileReadValue("hostname");
- string settingDatabase = GridDataMySqlFile.ParseFileReadValue("database");
- string settingUsername = GridDataMySqlFile.ParseFileReadValue("username");
- string settingPassword = GridDataMySqlFile.ParseFileReadValue("password");
- string settingPooling = GridDataMySqlFile.ParseFileReadValue("pooling");
- string settingPort = GridDataMySqlFile.ParseFileReadValue("port");
-
- database = new MySQLManager(settingHostname, settingDatabase, settingUsername, settingPassword,
- settingPooling, settingPort);
-
- m_log.Info("Creating " + m_maxConnections + " DB connections...");
- for (int i = 0; i < m_maxConnections; i++)
- {
- m_log.Info("Connecting to DB... [" + i + "]");
- MySQLSuperManager msm = new MySQLSuperManager();
- msm.Manager = new MySQLManager(settingHostname, settingDatabase, settingUsername, settingPassword,
- settingPooling, settingPort);
- m_dbconnections.Add(i, msm);
- }
+ Migration m = new Migration(dbcon, assem, "GridStore");
+ m.Update();
}
-
- // This actually does the roll forward assembly stuff
- Assembly assem = GetType().Assembly;
- Migration m = new Migration(database.Connection, assem, "GridStore");
- m.Update();
}
///
@@ -156,7 +85,6 @@ namespace OpenSim.Data.MySQL
///
override public void Dispose()
{
- database.Close();
}
///
@@ -187,8 +115,6 @@ namespace OpenSim.Data.MySQL
/// Array of sim profiles
override public RegionProfileData[] GetProfilesInRange(uint xmin, uint ymin, uint xmax, uint ymax)
{
- MySQLSuperManager dbm = GetLockedConnection();
-
try
{
Dictionary param = new Dictionary();
@@ -197,35 +123,33 @@ namespace OpenSim.Data.MySQL
param["?xmax"] = xmax.ToString();
param["?ymax"] = ymax.ToString();
- IDbCommand result =
- dbm.Manager.Query(
- "SELECT * FROM regions WHERE locX >= ?xmin AND locX <= ?xmax AND locY >= ?ymin AND locY <= ?ymax",
- param);
- IDataReader reader = result.ExecuteReader();
+ using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
+ {
+ dbcon.Open();
+
+ using (IDbCommand result = m_database.Query(dbcon,
+ "SELECT * FROM regions WHERE locX >= ?xmin AND locX <= ?xmax AND locY >= ?ymin AND locY <= ?ymax",
+ param))
+ {
+ using (IDataReader reader = result.ExecuteReader())
+ {
+ RegionProfileData row;
- RegionProfileData row;
+ List rows = new List();
- List rows = new List();
+ while ((row = m_database.readSimRow(reader)) != null)
+ rows.Add(row);
- while ((row = dbm.Manager.readSimRow(reader)) != null)
- {
- rows.Add(row);
+ return rows.ToArray();
+ }
+ }
}
- reader.Close();
- result.Dispose();
-
- return rows.ToArray();
}
catch (Exception e)
{
- dbm.Manager.Reconnect();
- m_log.Error(e.ToString());
+ m_log.Error(e.Message, e);
return null;
}
- finally
- {
- dbm.Release();
- }
}
///
@@ -236,42 +160,38 @@ namespace OpenSim.Data.MySQL
/// A list of sim profiles
override public List GetRegionsByName(string namePrefix, uint maxNum)
{
- MySQLSuperManager dbm = GetLockedConnection();
-
try
{
Dictionary param = new Dictionary();
param["?name"] = namePrefix + "%";
- IDbCommand result =
- dbm.Manager.Query(
+ using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
+ {
+ dbcon.Open();
+
+ using (IDbCommand result = m_database.Query(dbcon,
"SELECT * FROM regions WHERE regionName LIKE ?name",
- param);
- IDataReader reader = result.ExecuteReader();
+ param))
+ {
+ using (IDataReader reader = result.ExecuteReader())
+ {
+ RegionProfileData row;
- RegionProfileData row;
+ List rows = new List();
- List rows = new List();
+ while (rows.Count < maxNum && (row = m_database.readSimRow(reader)) != null)
+ rows.Add(row);
- while (rows.Count < maxNum && (row = dbm.Manager.readSimRow(reader)) != null)
- {
- rows.Add(row);
+ return rows;
+ }
+ }
}
- reader.Close();
- result.Dispose();
-
- return rows;
}
catch (Exception e)
{
- dbm.Manager.Reconnect();
- m_log.Error(e.ToString());
+ m_log.Error(e.Message, e);
return null;
}
- finally
- {
- dbm.Release();
- }
}
///
@@ -281,32 +201,30 @@ namespace OpenSim.Data.MySQL
/// Sim profile
override public RegionProfileData GetProfileByHandle(ulong handle)
{
- MySQLSuperManager dbm = GetLockedConnection();
-
try
{
Dictionary param = new Dictionary();
- param["?handle"] = handle.ToString();
-
- IDbCommand result = dbm.Manager.Query("SELECT * FROM regions WHERE regionHandle = ?handle", param);
- IDataReader reader = result.ExecuteReader();
+ param["?handle"] = handle.ToString();
- RegionProfileData row = dbm.Manager.readSimRow(reader);
- reader.Close();
- result.Dispose();
+ using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
+ {
+ dbcon.Open();
- return row;
+ using (IDbCommand result = m_database.Query(dbcon, "SELECT * FROM regions WHERE regionHandle = ?handle", param))
+ {
+ using (IDataReader reader = result.ExecuteReader())
+ {
+ RegionProfileData row = m_database.readSimRow(reader);
+ return row;
+ }
+ }
}
+ }
catch (Exception e)
{
- dbm.Manager.Reconnect();
- m_log.Error(e.ToString());
+ m_log.Error(e.Message, e);
return null;
}
- finally
- {
- dbm.Release();
- }
}
///
@@ -316,30 +234,29 @@ namespace OpenSim.Data.MySQL
/// The sim profile
override public RegionProfileData GetProfileByUUID(UUID uuid)
{
- MySQLSuperManager dbm = GetLockedConnection();
-
try
{
Dictionary param = new Dictionary();
- param["?uuid"] = uuid.ToString();
+ param["?uuid"] = uuid.ToString();
- IDbCommand result = dbm.Manager.Query("SELECT * FROM regions WHERE uuid = ?uuid", param);
- IDataReader reader = result.ExecuteReader();
-
- RegionProfileData row = dbm.Manager.readSimRow(reader);
- reader.Close();
- result.Dispose();
+ using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
+ {
+ dbcon.Open();
- return row;
+ using (IDbCommand result = m_database.Query(dbcon, "SELECT * FROM regions WHERE uuid = ?uuid", param))
+ {
+ using (IDataReader reader = result.ExecuteReader())
+ {
+ RegionProfileData row = m_database.readSimRow(reader);
+ return row;
+ }
+ }
}
+ }
catch (Exception e)
{
- dbm.Manager.Reconnect();
- m_log.Error(e.ToString());
+ m_log.Error(e.Message, e);
return null;
- } finally
- {
- dbm.Release();
}
}
@@ -351,37 +268,36 @@ namespace OpenSim.Data.MySQL
{
if (regionName.Length > 2)
{
- MySQLSuperManager dbm = GetLockedConnection();
-
try
{
Dictionary param = new Dictionary();
// Add % because this is a like query.
param["?regionName"] = regionName + "%";
- // Order by statement will return shorter matches first. Only returns one record or no record.
- IDbCommand result =
- dbm.Manager.Query(
- "SELECT * FROM regions WHERE regionName like ?regionName order by LENGTH(regionName) asc LIMIT 1",
- param);
- IDataReader reader = result.ExecuteReader();
- RegionProfileData row = dbm.Manager.readSimRow(reader);
- reader.Close();
- result.Dispose();
+ using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
+ {
+ dbcon.Open();
- return row;
+ // Order by statement will return shorter matches first. Only returns one record or no record.
+ using (IDbCommand result = m_database.Query(dbcon,
+ "SELECT * FROM regions WHERE regionName like ?regionName order by LENGTH(regionName) asc LIMIT 1",
+ param))
+ {
+ using (IDataReader reader = result.ExecuteReader())
+ {
+ RegionProfileData row = m_database.readSimRow(reader);
+ return row;
+ }
+ }
+ }
}
catch (Exception e)
{
- dbm.Manager.Reconnect();
- m_log.Error(e.ToString());
+ m_log.Error(e.Message, e);
return null;
}
- finally
- {
- dbm.Release();
- }
}
+
m_log.Error("[GRID DB]: Searched for a Region Name shorter then 3 characters");
return null;
}
@@ -393,17 +309,16 @@ namespace OpenSim.Data.MySQL
/// Successful?
override public DataResponse StoreProfile(RegionProfileData profile)
{
- MySQLSuperManager dbm = GetLockedConnection();
- try {
- if (dbm.Manager.insertRegion(profile))
- {
+ try
+ {
+ if (m_database.insertRegion(profile))
return DataResponse.RESPONSE_OK;
- }
- return DataResponse.RESPONSE_ERROR;
+ else
+ return DataResponse.RESPONSE_ERROR;
}
- finally
+ catch
{
- dbm.Release();
+ return DataResponse.RESPONSE_ERROR;
}
}
@@ -415,18 +330,16 @@ namespace OpenSim.Data.MySQL
//public DataResponse DeleteProfile(RegionProfileData profile)
override public DataResponse DeleteProfile(string uuid)
{
- MySQLSuperManager dbm = GetLockedConnection();
-
-
- try {
- if (dbm.Manager.deleteRegion(uuid))
- {
+ try
+ {
+ if (m_database.deleteRegion(uuid))
return DataResponse.RESPONSE_OK;
- }
- return DataResponse.RESPONSE_ERROR;
- } finally
+ else
+ return DataResponse.RESPONSE_ERROR;
+ }
+ catch
{
- dbm.Release();
+ return DataResponse.RESPONSE_ERROR;
}
}
@@ -477,33 +390,32 @@ namespace OpenSim.Data.MySQL
///
override public ReservationData GetReservationAtPoint(uint x, uint y)
{
- MySQLSuperManager dbm = GetLockedConnection();
-
try
{
Dictionary param = new Dictionary();
- param["?x"] = x.ToString();
- param["?y"] = y.ToString();
- IDbCommand result =
- dbm.Manager.Query(
- "SELECT * FROM reservations WHERE resXMin <= ?x AND resXMax >= ?x AND resYMin <= ?y AND resYMax >= ?y",
- param);
- IDataReader reader = result.ExecuteReader();
-
- ReservationData row = dbm.Manager.readReservationRow(reader);
- reader.Close();
- result.Dispose();
-
- return row;
+ param["?x"] = x.ToString();
+ param["?y"] = y.ToString();
+
+ using (MySqlConnection dbcon = new MySqlConnection(m_connectionString))
+ {
+ dbcon.Open();
+
+ using (IDbCommand result = m_database.Query(dbcon,
+ "SELECT * FROM reservations WHERE resXMin <= ?x AND resXMax >= ?x AND resYMin <= ?y AND resYMax >= ?y",
+ param))
+ {
+ using (IDataReader reader = result.ExecuteReader())
+ {
+ ReservationData row = m_database.readReservationRow(reader);
+ return row;
+ }
+ }
+ }
}
catch (Exception e)
{
- dbm.Manager.Reconnect();
- m_log.Error(e.ToString());
+ m_log.Error(e.Message, e);
return null;
- } finally
- {
- dbm.Release();
}
}
}
--
cgit v1.1