MySQL Pooling(with Caching) - The correct way.

Hi,

I think there was a thread a while back, where somebody asked which type of storage they should, either FlatFile(Binary), or MySQL(Said "My seequal"). I recommended MySQL, and there was many arguments ongoing about wether to use Binary, XML, or MySQL.

Binary - Static content that will be cached (items, mapdata, npcs, etc).
XML - Same as Binary, but smaller content
MySQL - Almost anything, but usually recommended for constantly changing data. If you're going to use it for cached data, it will use much more memory.

Why is MySQL good? MySQL has many upsides to it, but also some downsides. Firstly, sql is a storage database, used to keep data (Structured query language), and MySQL is a database server which uses sql snytax to store data upon it's connected server.

- Is mysql easy to use?
Yes, the snytax is very easy to understand. If you know how to work with Cookies (browser), then you are an expert in the language.

It is also very easy to edit content. Many software such as Navicat, and PhpMyAdmin help easily edit content, configure settings, and easily backup files.

- Is it safe to use?
Yes, mysql servers have "databases" which aquire a username, and a password. The usernames and passwords can be set by the user, and can have multiple users. Even if somebody knows your password or username, they require an absolute IP which you can setup in PhpMyAdmin/Navicat/etc

--------------------------------------
Now the caching and pooling part. You may be wondering, whats the point in pooling and caching? Pooling is useful, because say for instance, there is 300 users interacting with the database, all going through the same connection. If we split up the connections, it is much less stress on that 1 statement connection, which makes it alot faster.


Database:

Code:

---

/************************************************\
 * ############################################ *
 * ### Jolt Environment: RuneScape Emulator ### *
 * ### ------------------------------------ ### *
 * ###  Copyright (C) 2009 AJ Ravindiran  ### *
 * ###  HTTP://THEAJ.NET/ <[email protected]>  ### *
 * ############################################ *
\************************************************/
using System;

namespace AJRavindiran.Jolt.Storage
{
    /// <summary>
    /// Represents a storage database.
    /// </summary>
    public class Database
    {
        #region Fields
        private readonly string mName;
        private readonly uint mMinPoolSize;
        private readonly uint mMaxPoolSize;
        #endregion

        #region Properties
        /// <summary>
        /// The name of the database to connect to.
        /// </summary>
        public string Name
        {
            get { return mName; }
        }
        /// <summary>
        /// The minimum connection pool size for the database.
        /// </summary>
        public uint minPoolSize
        {
            get { return mMinPoolSize; }
        }
        /// <summary>
        /// The maximum connection pool size for the database.
        /// </summary>
        public uint maxPoolSize
        {
            get { return mMaxPoolSize; }
        }
        #endregion

        #region Constructor
        /// <summary>
        /// Constructs a Database instance with given details.
        /// </summary>
        /// <param name="sName">The name of the database.</param>
        /// <param name="minPoolSize">The minimum connection pool size for the database.</param>
        /// <param name="maxPoolSize"> The maximum connection pool size for the database.</param>
        public Database(string sName, uint minPoolSize, uint maxPoolSize)
        {
            if (sName == null || sName.Length == 0)
                throw new ArgumentException(sName);

            mName = sName;
            mMinPoolSize = minPoolSize;
            mMaxPoolSize = maxPoolSize;
        }
        #endregion
    }
}

---

DatabaseClient:

Code:

---

/************************************************\
 * ############################################ *
 * ### Jolt Environment: RuneScape Emulator ### *
 * ### ------------------------------------ ### *
 * ###  Copyright (C) 2009 AJ Ravindiran  ### *
 * ###  HTTP://THEAJ.NET/ <[email protected]>  ### *
 * ############################################ *
\************************************************/
using System;
using System.Data;

using MySql.Data.MySqlClient;

namespace AJRavindiran.Jolt.Storage
{
    /// <summary>
    /// Represents a client of a database,
    /// </summary>
    public class DatabaseClient : IDisposable
    {
        #region Fields
        private uint mHandle;
        private DateTime mLastActivity;

        private DatabaseManager mManager;
        private MySqlConnection mConnection;
        private MySqlCommand mCommand;
        #endregion

        #region Properties
        /// <summary>
        /// Gets the handle of this database client.
        /// </summary>
        public uint Handle
        {
            get { return mHandle; }
        }
        /// <summary>
        /// Gets whether this database client is anonymous and does not recycle in the database manager.
        /// </summary>
        public bool Anonymous
        {
            get { return (mHandle == 0); }
        }
        /// <summary>
        /// Gets the DateTime object representing the date and time this client has been used for the last time.
        /// </summary>
        public DateTime LastActivity
        {
            get { return mLastActivity; }
        }
        /// <summary>
        /// Gets the amount of seconds that this client has been inactive.
        /// </summary>
        public int Inactivity
        {
            get { return (int)(DateTime.Now - mLastActivity).TotalSeconds; }
        }
        /// <summary>
        /// Gets the state of the connection instance.
        /// </summary>
        public ConnectionState State
        {
            get { return (mConnection != null) ? mConnection.State : ConnectionState.Broken; }
        }
        #endregion

        #region Constructor
        /// <summary>
        /// Constructs a new database client with a given handle to a given database proxy.
        /// </summary>
        /// <param name="Handle">The identifier of this database client as an unsigned 32 bit integer.</param>
        /// <param name="pManager">The instance of the DatabaseManager that manages the database proxy of this database client.</param>
        public DatabaseClient(uint Handle, DatabaseManager pManager)
        {
            if (pManager == null)
                throw new ArgumentNullException("pManager");

            mHandle = Handle;
            mManager = pManager;

            mConnection = new MySqlConnection(mManager.CreateConnectionString());
            mCommand = mConnection.CreateCommand();

            UpdateLastActivity();
        }
        #endregion

        #region Methods
        /// <summary>
        /// Attempts to open the database connection.
        /// </summary>
        public void Connect()
        {
            if (mConnection == null)
                throw new DatabaseException("Connection instance of database client " + mHandle + " holds no value.");
            if (mConnection.State != ConnectionState.Closed)
                throw new DatabaseException("Connection instance of database client " + mHandle + " requires to be closed before it can open again.");

            try
            {
                mConnection.Open();
            }
            catch (MySqlException mex)
            {
                throw new DatabaseException("DatabaseClient[" + mHandle + "] - " + mex.Message);
            }
        }
        /// <summary>
        /// Attempts to close the database connection.
        /// </summary>
        public void Disconnect()
        {
            try
            {
                mConnection.Close();
            }
            catch { }
        }
        /// <summary>
        /// Closes the database connection (if open) and disposes all resources.
        /// </summary>
        public void Destroy()
        {
            Disconnect();

            mConnection.Dispose();
            mConnection = null;

            mCommand.Dispose();
            mCommand = null;

            mManager = null;
        }
        /// <summary>
        /// Updates the last activity timestamp to the current date and time.
        /// </summary>
        public void UpdateLastActivity()
        {
            mLastActivity = DateTime.Now;
        }

        /// <summary>
        /// Returns the DatabaseManager of this database client.
        /// </summary>
        public DatabaseManager GetManager()
        {
            return mManager;
        }

        public void AddParamWithValue(string sParam, object val)
        {
            mCommand.Parameters.AddWithValue(sParam, val);
        }
        public void ExecuteQuery(string sQuery)
        {
            mCommand.CommandText = sQuery;
            mCommand.ExecuteScalar();
            mCommand.CommandText = null;
        }

        public DataSet ReadDataSet(string sQuery)
        {
            DataSet pDataSet = new DataSet();
            mCommand.CommandText = sQuery;

            using (MySqlDataAdapter pAdapter = new MySqlDataAdapter(mCommand))
            {
                pAdapter.Fill(pDataSet);
            }
            mCommand.CommandText = null;

            return pDataSet;
        }
        public DataTable ReadDataTable(string sQuery)
        {
            DataTable pDataTable = new DataTable();
            mCommand.CommandText = sQuery;

            using (MySqlDataAdapter pAdapter = new MySqlDataAdapter(mCommand))
            {
                pAdapter.Fill(pDataTable);
            }
            mCommand.CommandText = null;

            return pDataTable;
        }
        public DataRow ReadDataRow(string sQuery)
        {
            DataTable pDataTable = ReadDataTable(sQuery);
            if (pDataTable != null && pDataTable.Rows.Count > 0)
                return pDataTable.Rows[0];

            return null;
        }
        public String ReadString(string sQuery)
        {
            mCommand.CommandText = sQuery;
            String result = mCommand.ExecuteScalar().ToString();
            mCommand.CommandText = null;

            return result;
        }
        public Int32 ReadInt32(string sQuery)
        {
            return 0;
        }

        #region IDisposable members
        /// <summary>
        /// Returns the DatabaseClient to the DatabaseManager, where the connection will stay alive for 30 seconds of inactivity.
        /// </summary>
        public void Dispose()
        {
            if (this.Anonymous == false) // No disposing for this client yet! Return to the manager!
            {
                // Reset this!
                mCommand.CommandText = null;
                mCommand.Parameters.Clear();

                mManager.ReleaseClient(mHandle);
            }
            else // Anonymous client, dispose this right away!
            {
                Destroy();
            }
        }
        #endregion
        #endregion
    }
}

---

DatabaseException:

Code:

---

/************************************************\
 * ############################################ *
 * ### Jolt Environment: RuneScape Emulator ### *
 * ### ------------------------------------ ### *
 * ###  Copyright (C) 2009 AJ Ravindiran  ### *
 * ###  HTTP://THEAJ.NET/ <[email protected]>  ### *
 * ############################################ *
\************************************************/
using System;

namespace AJRavindiran.Jolt.Storage
{
    public class DatabaseException : Exception
    {
        public DatabaseException(string sMessage) : base(sMessage) { }
    }
}

---

DatabaseManager:

Code:

---

/************************************************\
 * ############################################ *
 * ### Jolt Environment: RuneScape Emulator ### *
 * ### ------------------------------------ ### *
 * ###  Copyright (C) 2009 AJ Ravindiran  ### *
 * ###  HTTP://THEAJ.NET/ <[email protected]>  ### *
 * ############################################ *
\************************************************/
using System;
using System.Data;
using System.Threading;

using MySql.Data.MySqlClient;

namespace AJRavindiran.Jolt.Storage
{
    /// <summary>
    /// DatabaseManager acts as a proxy towards an encapsulated Database at a DatabaseServer.
    /// </summary>
    public class DatabaseManager
    {
        #region Fields
        private DatabaseServer mServer;
        private Database mDatabase;

        private DatabaseClient[] mClients = new DatabaseClient[0];
        private bool[] mClientAvailable = new bool[0];
        private int mClientStarvationCounter;

        private Thread mClientMonitor;
        #endregion

        #region Properties

        #endregion

        #region Constructor
        /// <summary>
        /// Constructs a DatabaseManager for a given DatabaseServer and Database.
        /// </summary>
        /// <param name="pServer">The DatabaseServer for this database proxy.</param>
        /// <param name="pDatabase">The Database for this database proxy.</param>
        public DatabaseManager(DatabaseServer pServer, Database pDatabase)
        {
            mServer = pServer;
            mDatabase = pDatabase;
        }
        /// <summary>
        /// Constructs a DatabaseManager for given database server and database details.
        /// </summary>
        /// <param name="sServer">The network host of the database server, eg 'localhost' or '127.0.0.1'.</param>
        /// <param name="Port">The network port of the database server as an unsigned 32 bit integer.</param>
        /// <param name="sUser">The username to use when connecting to the database.</param>
        /// <param name="sPassword">The password to use in combination with the username when connecting to the database.</param>
        /// <param name="sDatabase">The name of the database to connect to.</param>
        /// <param name="minPoolSize">The minimum connection pool size for the database.</param>
        /// <param name="maxPoolSize">The maximum connection pool size for the database.</param>
        public DatabaseManager(string sServer, uint Port, string sUser, string sPassword, string sDatabase, uint minPoolSize, uint maxPoolSize)
        {
            mServer = new DatabaseServer(sServer, Port, sUser, sPassword);
            mDatabase = new Database(sDatabase, minPoolSize, maxPoolSize);

            mClientMonitor = new Thread(MonitorClientsLoop);
            mClientMonitor.Priority = ThreadPriority.Lowest;

            mClientMonitor.Start();
        }
        #endregion

        #region Methods
        /// <summary>
        /// Starts the client monitor thread. The client monitor disconnects inactive clients etc.
        /// </summary>
        public void StartMonitor()
        {
            mClientMonitor = new Thread(MonitorClientsLoop);
            mClientMonitor.Priority = ThreadPriority.Lowest;

            mClientMonitor.Start();
        }
        /// <summary>
        /// Stops the client monitor thread.
        /// </summary>
        public void StopMonitor()
        {
            if (mClientMonitor != null)
            {
                mClientMonitor.Abort();
            }
        }

        /// <summary>
        /// Disconnects and destroys all database clients.
        /// </summary>
        public void DestroyClients()
        {
            lock (this)
            {
                for (int i = 0; i < mClients.Length; i++)
                {
                    mClients[i].Destroy();
                    mClients[i] = null;
                }
            }
        }
        /// <summary>
        /// Nulls all instance fields of the database manager.
        /// </summary>
        public void DestroyManager()
        {
            mServer = null;
            mDatabase = null;
            mClients = null;
            mClientAvailable = null;

            mClientMonitor = null;
        }

        /// <summary>
        /// Closes the connections of database clients that have been inactive for too long. Connections can be opened again when needed.
        /// </summary>
        private void MonitorClientsLoop()
        {
            while (true)
            {
                try
                {
                    lock (this)
                    {
                        DateTime dtNow = DateTime.Now;
                        for (int i = 0; i < mClients.Length; i++)
                        {
                            if (mClients[i].State != ConnectionState.Closed)
                            {
                                if (mClients[i].Inactivity >= 60) // Not used in the last %x% seconds
                                {
                                    mClients[i].Disconnect(); // Temporarily close connection

                                    // JoltEnvironment.GetLogger().WriteInfo("Disconnected database client #" + mClients[i].Handle);
                                    JoltEnvironment.GetLogger().WriteInfo("Database client[" + mClients[i].Handle + "] - Dissconnected");
                                }
                            }
                        }
                    }

                    Thread.Sleep(10000); // 10 seconds
                }
                catch (ThreadAbortException) { } // Nothing special
                catch (Exception ex)
                {
                    JoltEnvironment.GetLogger().WriteError(ex.Message);
                }
            }
        }
        /// <summary>
        /// Creates the connection string for this database proxy.
        /// </summary>
        public string CreateConnectionString()
        {
            MySqlConnectionStringBuilder pCSB = new MySqlConnectionStringBuilder();

            // Server
            pCSB.Server = mServer.Host;
            pCSB.Port = mServer.Port;
            pCSB.UserID = mServer.User;
            pCSB.Password = mServer.Password;

            // Database
            pCSB.Database = mDatabase.Name;
            pCSB.MinimumPoolSize = mDatabase.minPoolSize;
            pCSB.MaximumPoolSize = mDatabase.maxPoolSize;

            return pCSB.ToString();
        }

        public DatabaseClient GetClient()
        {
            // Let other threads wait if they contact this DatabaseManager while we're busy with it
            lock (this)
            {
                // Try to find an available client
                for (uint i = 0; i < mClients.Length; i++)
                {
                    // Somebody here?
                    if (mClientAvailable[i] == true)
                    {
                        // No starvation anymore
                        mClientStarvationCounter = 0;

                        // Is this connection broken?
                        //if (mClients[i].State == ConnectionState.Broken)
                        //{
                        //    mClients[i] = new DatabaseClient((i + 1), this); // Create new client
                        //}

                        // Is this connection closed?
                        if (mClients[i].State == ConnectionState.Closed)
                        {
                            // TODO: exception handling
                            mClients[i].Connect();

                            JoltEnvironment.GetLogger().WriteInfo("DatabaseClient[" + mClients[i].Handle + "] -- Opening connection");
                        }

                        // Is this client ready?
                        if (mClients[i].State == ConnectionState.Open)
                        {
                            JoltEnvironment.GetLogger().WriteInfo("DatabaseClient[" + mClients[i].Handle + "] -- Handed out client");

                            mClientAvailable[i] = false; // BRB

                            mClients[i].UpdateLastActivity();
                            return mClients[i];
                        }
                    }
                }

                // No clients?
                mClientStarvationCounter++;

                // Are we having a structural lack of clients?
                if (mClientStarvationCounter >= ((mClients.Length + 1) / 2)) // Database hungry much?
                {
                    // Heal starvation
                    mClientStarvationCounter = 0;

                    // Increase client amount by 0.3
                    SetClientAmount((uint)(mClients.Length + 1 * 1.3f));

                    // Re-enter this method
                    return GetClient();
                }

                DatabaseClient pAnonymous = new DatabaseClient(0, this);
                pAnonymous.Connect();

                JoltEnvironment.GetLogger().WriteInfo("DatabaseClient[anonymous] -- Handed out client");

                return pAnonymous;
            }
        }
        public void ReleaseClient(uint Handle)
        {
            if (mClients.Length >= (Handle - 1)) // Ensure client exists
            {
                mClientAvailable[Handle - 1] = true;
                JoltEnvironment.GetLogger().WriteInfo("DatabaseClient[" + Handle + "] -- Released client");
            }
        }

        /// <summary>
        /// Sets the amount of clients that will be available to requesting methods. If the new amount is lower than the current amount, the 'excluded' connections are destroyed. If the new connection amount is higher than the current amount, new clients are prepared. Already existing clients and their state will be maintained.
        /// </summary>
        /// <param name="Amount">The new amount of clients.</param>
        public void SetClientAmount(uint Amount)
        {
            lock (this)
            {
                if (mClients.Length == Amount)
                    return;

                if (Amount < mClients.Length) // Client amount shrinks, dispose clients that will die
                {
                    for (uint i = Amount; i < mClients.Length; i++)
                    {
                        mClients[i].Destroy();
                        mClients[i] = null;
                    }
                }

                DatabaseClient[] pClients = new DatabaseClient[Amount];
                bool[] pClientAvailable = new bool[Amount];
                for (uint i = 0; i < Amount; i++)
                {
                    if (i < mClients.Length) // Keep the existing client and it's available state
                    {
                        pClients[i] = mClients[i];
                        pClientAvailable[i] = mClientAvailable[i];
                    }
                    else // We are in need of more clients, so make another one
                    {
                        pClients[i] = new DatabaseClient((i + 1), this);
                        pClientAvailable[i] = true; // Elegant?
                    }
                }

                // Update the instance fields
                mClients = pClients;
                mClientAvailable = pClientAvailable;
            }
        }

        public bool INSERT(IDataObject obj)
        {
            using (DatabaseClient dbClient = GetClient())
            {
                return obj.INSERT(dbClient);
            }
        }

        public bool DELETE(IDataObject obj)
        {
            using (DatabaseClient dbClient = GetClient())
            {
                return obj.DELETE(dbClient);
            }
        }

        public bool UPDATE(IDataObject obj)
        {
            using (DatabaseClient dbClient = GetClient())
            {
                return obj.UPDATE(dbClient);
            }
        }

        public override string ToString()
        {
            return mServer.ToString() + ":" + mDatabase.Name;
        }
        #endregion
    }
}

---

DatabaseServer:

Code:

---

/************************************************\
 * ############################################ *
 * ### Jolt Environment: RuneScape Emulator ### *
 * ### ------------------------------------ ### *
 * ###  Copyright (C) 2009 AJ Ravindiran  ### *
 * ###  HTTP://THEAJ.NET/ <[email protected]>  ### *
 * ############################################ *
\************************************************/
using System;
using System.Collections.Generic;
using System.Text;

namespace AJRavindiran.Jolt.Storage
{
    /// <summary>
    /// Represents a database server and holds information about the database host, port and access credentials.
    /// </summary>
    public class DatabaseServer
    {
        #region Fields
        private readonly string mHost;
        private readonly uint mPort;

        private readonly string mUser;
        private readonly string mPassword;
        #endregion

        #region Properties
        /// <summary>
        /// The network host of the database server, eg 'localhost' or '127.0.0.1'.
        /// </summary>
        public string Host
        {
            get { return mHost; }
        }
        /// <summary>
        /// The network port of the database server as an unsigned 32 bit integer.
        /// </summary>
        public uint Port
        {
            get { return mPort; }
        }
        /// <summary>
        /// The username to use when connecting to the database.
        /// </summary>
        public string User
        {
            get { return mUser; }
        }
        /// <summary>
        /// The password to use in combination with the username when connecting to the database.
        /// </summary>
        public string Password
        {
            get { return mPassword; }
        }
        #endregion

        #region Constructor
        /// <summary>
        /// Constructs a DatabaseServer object with given details.
        /// </summary>
        /// <param name="sHost">The network host of the database server, eg 'localhost' or '127.0.0.1'.</param>
        /// <param name="Port">The network port of the database server as an unsigned 32 bit integer.</param>
        /// <param name="sUser">The username to use when connecting to the database.</param>
        /// <param name="sPassword">The password to use in combination with the username when connecting to the database.</param>
        public DatabaseServer(string sHost, uint Port, string sUser, string sPassword)
        {
            if (sHost == null || sHost.Length == 0)
                throw new ArgumentException("sHost");
            if (sUser == null || sUser.Length == 0)
                throw new ArgumentException("sUser");

            mHost = sHost;
            mPort = Port;

            mUser = sUser;
            mPassword = (sPassword != null) ? sPassword : "";
        }
        #endregion

        #region Methods
        public override string ToString()
        {
            return mUser + "@" + mHost;
        }
        #endregion
    }
}

---

IDataObject:

Code:

---

/************************************************\
 * ############################################ *
 * ### Jolt Environment: RuneScape Emulator ### *
 * ### ------------------------------------ ### *
 * ###  Copyright (C) 2009 AJ Ravindiran  ### *
 * ###  HTTP://THEAJ.NET/ <[email protected]>  ### *
 * ############################################ *
\************************************************/
using System;

namespace AJRavindiran.Jolt.Storage
{
    public interface IDataObject
    {
        bool INSERT(DatabaseClient dbClient);
        bool DELETE(DatabaseClient dbClient);
        bool UPDATE(DatabaseClient dbClient);
    }
}

---

I know this is C#, but if you guys want, I've been working on a C# server.
MySQL pooling(w/ caching) in action:

[Only registered and activated users can see links. Click Here To Register...]

Project: [Only registered and activated users can see links. Click Here To Register...]

Thanks for the information Aj.