MySQL connection pooling and how it can be used for parallelization

I want to share with all readers an interesting topic had faced recently and I liked it. The development of this theme gave me pleasure and added a bit of experience in the piggy bank. Probably many or may not have encountered the DB connection pooling. After reading this interesting option I wanted to write an article and share it with you. Perhaps the article will turn out a little long, but I think this post someone will still be interesting to read, and it will be interested in the topic. Maybe work with the article someone is using in your project, in any case, I’ll be interested to write it and tell it to you.
I am a freelancer. Somehow brought me one of my friend with the owners of the school, and there I was asked to make a program for the collection of tickets. Something like his book tickets SDA. All questions are stored in a MySQL database in three tables, where a collection of issues in the different subjects of the questions and answers in the latter. As a result of 800 issues with or without picture, including replies. The original building was a usual method of sequential formation of questions inside. I am quite interested in the theme of a multithreaded program so after creating quite a work method, I decided to make things more convenient, and at the same time added the sampling rate.
To begin with, to form its own version of TRAFFIC RULES must take into account the subject of ticket issues. The total 12 themes. The first 8 topics consist of 40 issues remaining from 120. Each topic has a corresponding number on the ticket, the first 8 topics is 1 question from the ticket, other 3 issue of the ticket. To store the generated version of a Dictionary where each key keeps a list of questions on a certain topic. The order of the questions should always be different, i.e. a method that will generate a sequence of numbers from 0 to 40 without repetition, so you get to choose any ticket and issue out of it. In view of all this is the algorithm of all questions:
get all the themes that we have;
of the form list of questions for each of the topics;
generating random numbers;
on the basis of the list of issues and the sequence of random numbers do sample and form a list of questions on the subject;
assign each list in the dictionary.
Implementation and operation of the first two steps depends on the logic and in my opinion can be run one time when a select query. The third step depends on how much we want to do the same, each subject to a new generation, or once for every order. I selected Generate once only, in my view, this is sufficient. The fourth step will work more often and this is where you would take most time when fetching. The last step is a simple and easy to miss.


Connection to DB
Consider the connection to the database. To do this, we use the abstract class that will store a connection string, the Connection and the DataReader:


I like abstract classes so that they can create and hide a convenient logic, allowing the other to fully focus on specifics and not worry about other details, entrusting to the base class. In this case, creating an instance of the derived class, we automatically receive the necessary conditions for work with databases and we have to implement the logic of the class to create a selection of issues:



It is the most simple and synchronous version, which runs for about 2 seconds and the 200400 milliseconds, thus blocking the UI on it all the time. It’s already pretty good working version, as the first implementation worked a long time, for about 6 seconds. The improvement came only about 2 seconds.
Creating an asynchronous version of the sample
All is well and everything is already running, but is, as it should be? We have synchronous (blocking) and not a console application. A correct and complete operating program, which will not be blocked by even half a second, and will correctly handle any load. The first thing we will rewrite the method GetQuestion (). Make it asynchronous in accordance with pattern TAP (Task-based Asynchronous Pattern). You read on the Internet or is there a good book that I really like is asynchronous programming in C # 5.0» Alex Davis, where very well describes the subject, libozaglânite here. Rewrite it and it will look like this:


Let’s look at the most interesting thing in this method: Task.Factory.StartNew (). Starting with version 4.5, you can use the version of .NET Task. Run (), which is different from the previous more simple announcement with fewer parameters when you create. In fact the Task. Run() it is simply more convenient wrapper over Task.Factory.StartNew () and very suitable to easily create asynchronous tasks, but it has a bit less flexible control. If you need more precise control over which thread performs calculations or how he planned to use Task.Factory.StartNew (). If interested click here. In this case, I used this version because I also pointed out this option as the TaskCreationOptions. LongRunning, that mark this task as long and indicates that the work item will be performed over a long period of time and can block other work items. This option also provides information to the TaskScheduler that oversubscription and this will create more threads than the number of available hardware threads. With this option you can completely avoid the ThreadPool, including global and local queues. For more information, see Task Scheduler.
So we get are asynchronous execution of data collection and not block the main thread.
Threading sample
Then turn to the paralleling that will give us a boost in speed data sampling. But it will have to redo a little primary class, modify the base and add another to ensure parallelism of work cycle to avoid data races, critical sections and problems with variables to different threads.
First, we create a new nested class in the class SqlPerceptionQuestionsSqlQuestions. It‘ll move methods to get questions and answers, as well as make it a derivative of SqlBase, and leave the outer class method to get the order, we will call him once and generate a sequence of numbers.
Class code: SqlQuestions



For loop parallelization will use parallel.for (). It’s a pretty convenient way to load data into multiple streams. But it is also a fact that we will need to create at least a few database connections because one Connection can handle one DataReader. Rewrite method.Request ():



And after opening the connection, it must be close. All of this will occur in the cycle. For all this I decided to create a separate class SqlQuestions. The connection will be.Dispose (), in which we register that we need to do when closing. To do this, you first declare the method.The Dispose () method in the base class:



And we will implement it differently in derived classes. Why so? To begin with, what happens when you create a connection to the database and its closure? If you open the connection, and then you close it for a while (about 3 minutes) is placed in a connection pool, MySQL, and if you open a new, then the connection is taken out of the pool, which means that the time and resources to reopen is not used. Let’s run our new methods and see how long does it take to open a database connection for this insert Stopwatch in the base class in code, where opening a connection and see what we have on the output. Code:



Opening time
The first link is a long, here is the link for klassaSqlPerceptionQuestions, which will be open for the duration of the method. Subsequent connections are those that were open when the cycle when you create instances of the class SqlQuestions. In view of the number of processors on the computer, I have 4 get that in a maximum of 4 open connections. Just get that initially will be open 5 connections, the cycle will be as open and close. So the first 5 connections require time for opening, and after when in the cycle will be closed old links and open up new, they will not go away of time and resources, since there is a connection in the pool and they just are issued whenever required. Because of this and is a little bit different cleaning classes. In the class SqlPerceptionQuestions method will look like this:


And in the SqlQuestions Similarly, except the row MySqlConnection. ClearAllPools (); After all, if we leave, we get a kind of situation:
Opening time
As we can see, the constant cleaning of the thread pool leads to permanent open connection with the consequences.