MySQL connector C/C ++ multi-thread Encapsulation

MySQL connector C/C ++ multi-thread Encapsulation

I have been searching for it online for a long time and there are many packages, but I don't think it is ideal to handle many threads. The first encapsulated version has a simple idea. It uses a singleton mode and corresponds to a connection. The multi-threaded query is completed on this link.Code

 
ClassDbclass {Public: Open (...) {mysql_real_connect (MDB);} querysql (....){Mysql_real_query (MDB );}Protected: MySQL*MDB ;}
 

However, in actual use, it is found that an error is reported as long as multiple threads are enabled, which is generally a lost connect error. In the beginning, it is thought that the connection is a simple disconnection, so some connection problems have been made.ArticleUse ping to check whether the connection is normal during each query. I will not go into details about how to write the ping command here. There are many articles on the Internet, and Ping is completed, the problem still cannot be solved when a timeout value is set for the connection.

I checked the document and found that when multithreading is usedMysql_real_queryThe function and the mysql_store_result function must be locked. Because Ping may cause connection reconnection, a lock is added to ping. The following code:

 
ClassDbclass {Public: Open (...) {mysql_real_connect (MDB );}
Ping ()
{
Mutex.Lock();
Mysql_ping ();
Mutex. Unlock ();
}
Querysql (...) {mutex.Lock(); Mysql_real_query (MDB); mutex. Unlock ();}Protected: MySQL*MDB ;}
 

After this attempt, I finally won't report an error. After a long time, I'm glad that this piece of code will not be looked at again. At the end of the Code test, I found the problem by adding performance measurements such as timing, multithreading is the same as a single thread .... Obviously, this is actually a serial access to the database, but it is actually a single thread. In addition, even if a single thread has many locks, the speed is not fast.
After thinking about it, the single-connection multithreading seems to be useless. One connection can only process one thread at a time. If you want to understand it, you can do it. If you change it to a connection for each thread, it will be OK, in addition, connection does not share resources and mutual exclusion is not required, which greatly improves the efficiency.

 Class  Dbclass {  Public  :MySQL* Open (....){
MySQL*PDB; Mysql_real_connect (PDB);
ReturnPDB;} Ping (MySQL*PDB){ Mysql_ping (PDB);} Querysql (MySQL*PDB,.....){  Mysql_real_query (PDB);}

Close (MySQL*PDB)
{
Mysql_close (PDB);
} }

Work_thread ()
{
 MySQL*PDB = Dbclass ::Open(); // It should be a singleton. Here, use static to indicate 

.....
 Dbclass ::Ping(PDB);
Dbclass :: Querysql(PDB);
 .....
 Dbclass ::Close(PDB); 
}

 

After the change, the effect is obvious. It takes about 30 seconds for a single thread to process 8000 pieces of data (and various computations), and more than three seconds for 10 threads to process. In addition, since the lock was too many, it would take about 20 seconds for a single thread to process 1000 pieces of data, and now 8000 takes 30 seconds, which has improved a lot. There are also some optimizations, but it already meets your needs.

To sum up, pay attention to the following points for the multi-thread encapsulation of MySQL:

1. Ping before querying to ensure the connection is normal.

2. Each thread corresponds to a connection

3. If the thread is deleted and established frequently, you need to use the connection pool when getting the connection.