Wednesday, March 20, 2013

Compairing MySQL Enterprise and MySQL Community Edition

MySQL Community Edition have been used by many organization in production and as well as in QA/Development and staging setup. Reason is very simple: it is simple to install, configure and monitor, it is scalable when required and provide high availability if configured in that way. These feature makes it worlds first widely industry accepted open source database server.

Then why Enterprise Edition is available and what additional features enterprise edition have???

YES, Enterprise Edition has additional feature, what are those features???

  1. Support : support for mission critical issues and hot fixes.
  2. Backup : MEB ( MySQL Enterprise Backup ), online InnoDB,MyISAM backups.
  3. Monitoring Tools : MEM ( MySQL Enterprise Monitor ), Query Analyzer, Web Based configuration
  4. Plugins : Additional plugins for performance improvement i.e. thread pool, PAM authentication etc

Do we really need above features depends upon company financial policies, however in startups Community Edition is used and monitoring or analysis is carried out using shell/perl scripts.

The most important feature of Enterprise Edition is its thread pool plugin which boost the performance at optimal configuration and traffic on database server.

What is Thread Pool in MySQL and how it enhance the performance??

The MySQL Thread Pool is a MySQL server plugin that extends the default connection-handling capabilities of the MySQL server to limit the number of concurrently executing statements/queries and transactions to ensure that each has sufficient CPU and memory resources to fulfill its task.
The default thread-handling model in MySQL Server executes statements using one thread per client connection. As more clients connect to the server and execute statements, overall performance degrades. The Thread Pool plugin provides an alternative thread-handling model designed to reduce overhead and improve performance. The Thread Pool plugin increases server performance by efficiently managing statement execution threads for large numbers of client connections, especially on modern multi-CPU/Core systems
The Thread Pool uses a divide and conquer approach to limiting and balancing concurrency. Unlike the default connection handling of the MySQL Server, the Thread Pool separates connections and threads, so there is no fixed relationship between connections and the threads that execute statements received from those connections. The Thread Pool then manages client connections within configurable thread groups, where they are prioritized and queued based on the nature of the work they were submitted to accomplish. 

With the new thread pool plugin, there is now an alternative way to handle connection threads in MySQL Enterprise Edition.  With the plugin, MySQL connection threads are shared like an extraordinarily well managed timeshare in Hawaii.  When one connection is “idle”, asking nothing of and expecting nothing from the database, another connection can use that same thread for its database requests.  Threads are released by each connection as soon as the request is completed and  go back into the pool for re-use – just like the theoretical timeshare is up for grabs on the weeks you are not there.
In the older, and still default connection thread model, threads are dedicated to a single client  for the life of the connection and there are as many threads as there are clients currently connected to the database.  This has some disadvantages when the server workload must scale to handle large numbers of connections, and the overhead can be signficant. This occurs for several reasons:

  • Lots of threads use lots of memory and can make the CPU cache ineffective
  • Too many active threads trying to execute in parallel may cause a high level of resource contention and be inappropriate for the amount of parallelism available
The new thread pool plugin offers an alternative thread pool implementation, and focuses on limiting the number of concurrent, short running statements to mazimize performance and reduce overhead.  By limiting the number of concurrent, short running statements and sharing threads, we can control the number of active threads at any one time.  Thread management has been revamped and by managing these threads in a highly efficient manner, we end up reducing overhead and often increasing performance. 
Here are the mechanics:  In the new plugin, threads are organized into groups (16 by default but configurable up to 64 on server startup).  Each group starts with one thread and can increase to a maximum of 4096 threads.  Additional threads are created only when necessary.  Each incoming connection request is assigned to a group by round robin. Each group has one listener thread that listens for incoming statement requests.
When a statement request comes in, it is executed immediately by the group’s listener thread if it is not busy and there are no other statement requests waiting.  If the statement request finishes quickly, the listener thread then efficiently returns to listening and is available to execute the next incoming request, preventing the need for a new thread to be created.   If the request does not finish quickly, it runs to completion but another thread is  created as the new listener.
If the listener thread is busy, the request is queued.  There will be a very brief time (configurable with the thread_pool_stall_limit system variable which defaults to 60 ms) while we wait to see if the currently executing statement will finish quickly or not. If it finishes quickly (under thread_pool_stall_limit), we can re-use this thread for the next request in the queue, eliminating the overhead of creating a new thread or having too many short statement trying to execute in parallel .
You can see how this thread pool design strives to have one thread executing per group at any time . The number of groups (thread_pool_size_variable) is very important, because it approximates the number of short running statements that will be executing concurrently at any one time.  Long running statements are prevented from causing other statements to wait, since if they go beyond the thread_pool_stall_limit, another thread will be started and the next request in the queue will execute on it.
Your predominant storage engine will help determine the number of groups you should have.  For InnoDB, between 16 and 36 groups seems to work well in many cases, but for MyISAM set it much lower (4-8).
There are two queues for waiting statements, low and high priority.  The low priority queue contains:

  • all statements for non-transactional storage engines
  • all statements if autocommit is enabled
  • the first statement in  an InnoDB transaction
These statements do not languish in the low priority queue forever since they will get kicked over to the high priority queue when the thread_pool_kickup_timer times them out. However, there is a maximum number of statements that can be moved per time period to keep things under control.
The high priority queue contains

  • any subsequent statements in InnoDB transactions, and
  • any statements kicked up from the low priority queue.
 From all benchmarks we've seen that the performance of the thread pool
when operated with less than the optimal number of active connections is
about 1-3% slower than without thread pool since the behaviour is the same
and the thread pool adds a little bit more overhead. More or less all of
this overhead is to handle KILL query correctly.

When operated in the region of the optimal number of active connections
the performance is very similar. We have seen though that the thread pool
benefits very much from locking the MySQL Server to a number of CPUs
equal to the setting of the thread_pool_size configuration parameter.
When not locked to CPUs the performance is similar, when locked to CPUs
the thread pool gives 10-15% higher performance when using the optimal
number of active connections. The MySQL Server operated without thread
pool and locked to CPUs have no significant change of throughput compared
to not locking to CPUs.

When operating above optimal number of connections the thread pool
provides a great benefit, we've seen numbers all the way up to 100x
better performance when operating with a few thousand concurrently
active connections.


  1. What is the different between community edition & standard edition.

    1. Support : support for mission critical issues and hot fixes.
      Backup : MEB ( MySQL Enterprise Backup ), online InnoDB,MyISAM backups.
      Monitoring Tools : MEM ( MySQL Enterprise Monitor ), Query Analyzer, Web Based configuration
      Plugins : Additional plugins for performance improvement i.e. thread pool, PAM authentication etc

  2. Can MySQL Community Edition be used for Enterprise purposes i.e we are about to setup a data center with web hosting facility and also would be using it as our DR.
    Pl suggest, how to go about the same with your valuable inputs.

    *IBM BladeCenter HS 22 Servers would be used for the purpose.
    And, if not why and what and how.

    Looking forward to your reply at the earliest.
    Thankig you.

  3. Looking forward to receive more articles