C language to implement MySQL connection pool

MYSQL C API encapsulation and MYSQL connection pooling

Introduction: Database connection pooling is responsible for allocating, managing, and releasing database connections. It allows applications to reuse an existing database connection instead of creating another one. Connection pooling technology is mostly used behind high-concurrency servers. On the existing large high concurrency server, each thread with the database data exchange belongs to network connection, frequent start-stop connection is very reasonable, especially on large Web server delay too long time on the user experience is extremely bad, establishment and disconnect from the database and repeat the procedure of operation has considerable influence;

A connection pool is an abstract concept that contains a bunch of database connections, connection management threads, and externally provided interfaces. 1. The thread obtains a database connection by calling the connection pool interface. After use, it does not need to destroy the connection, but only needs to logically abandon the connection; It is still available when fetched by the next thread; 2. Connection pool management thread dynamically manages database connections, reasonably adds and deletes them, and ensures the reasonable utilization of the number of connections on the basis of meeting external calls. It is just like a miserly employer; Both additions and deletions change the current state of the connection pool, so the mutex in the database connection part of the pool below ensures that operations on the state are unique. 3. The database connection should add mutex on the thread using the database connection. Even if we have ten threads using the connection, we should ensure that only one thread is operating on the same data modification at the same time, which is realized in the thread part of the following figure.

This figure is a simple connection pool implementation model, we will use this figure to implement a basic connection pool, the database using MySQL

Abstract structure of database connection pool (circle 1)

#define IP_LEN 15 #define DBNAME_LEN 64 #define DBUSER_LEN 64 #define PASSWD_LEN 64 #define POOL_MAX_NUMBER 20 typedef struct _SQL_NODE SQL_NODE; /* Connection node */ typedef struct _SQL_CONN_POOL SQL_CONN_POOL; / / struct _SQL_NODE{MYSQL fd; /* MYSQL *mysql_sock; /* Pointer to connected MYSQL */ pthread_mutex_t lock; /* mutex; */ int used; /* use flag */ int index; /* subscript */ enum{/* Connection status */ DB_DISCONN, DB_CONN}sql_state; }SQL_NODE; /* connection pool */ typedef struct _SQL_CONN_POOL{int shutdown; /* Whether to disable */ SQL_NODE sql_pool[POOL_MAX_NUMBER]; /* a bunch of connections */ int pool_number; /* Number of connections */ int busy_number; /* Number of connections obtained */ char IP [IP_LEN+1]; /* Database IP */ int port; */ char db_name[DBNAME_LEN+1]; /* Database name */ char user[DBUSER_LEN+1]; /* User name */ char passwd[PASSWD_LEN+1]; /* Password */}SQL_CONN_POOL;Copy the code

2. Function overview (circle 3) : The server creates a connection pool (creates nodes), allocates connections to threads, processes and returns connections;

*/ SQL_CONN_POOL *sql_pool_create(int connect_pool_number, char IP [], int port, char db_name[], char user[], char passwd[]); Int create_db_connect(SQL_CONN_POOL *sp, SQL_NODE *node); /* destroy connection pool */ void sql_pool_destroy(SQL_CONN_POOL *sp); */ SQL_NODE *get_db_connect(SQL_CONN_POOL *sp); /* Return connection */ void release_node(SQL_CONN_POOL *sp, SQL_NODE *node); /* Add or delete connection */ SQL_CONN_POOL *changeNodeNum(SQL_CONN_POOL *sp, int op);Copy the code

3.1 Creating a connection pool function ==> Plan a memory space to store database connections and initialize the connection pool status information.

*/ SQL_CONN_POOL *sql_pool_create(int connect_pool_number, char IP [], int port, char db_name[], char user[], char passwd[]) { SQL_CONN_POOL *sp = NULL; /* Error input detection */ if (connect_pool_number < 1) {printf("connect_pool_number < 1.defalut 1 \n"); connect_pool_number = 1; } /* Allocate memory for connection pool */ if ((sp=(SQL_CONN_POOL *)malloc(sizeof(SQL_CONN_POOL)) == NULL) {printf("malloc SQL_CONN_POOL error.\n"); return NULL; } sp->shutdown = 0; Sp ->pool_number = 0; Sp ->busy_number = 0; Strcpy (sp-> IP, IP); // database IP sp->port = port; // database Port strcpy(sp->db_name, db_name); Strcpy (sp->user, user); Strcpy (sp->passwd, passwd); // password /* Create a connection */ if (connect_pool_number > POOL_MAX_NUMBER) connect_pool_number = POOL_MAX_NUMBER; for (int index=0; index < connect_pool_number; Index++) {// failed to create a custom function, create a node function; if (0 ! = create_db_connect(sp, &sp->sql_pool[index]); sql_pool_destroy(sp); return NULL; } sp->sql_pool[index]. Index = index; sp->pool_number++; printf("create database pool connect:-%d-.\n",sp->sql_pool[index].index); } return sp; }Copy the code

3.2 Creating a connection node ==> Thread: Lock the node before creating a connection, and set the automatic connection and timeout limit for the node after the connection is successfully created. Special attention should be paid when a connection is not used for a certain period of time, it will be disconnected, so we need to set up automatic connection, when the connection is disconnected automatically reconnect;

int opt=1; Int res=0; //0 Normal -1 Initialization failure 1 Connection failure do {if (shutdown == 1) return-1; Pthread_mutex_init (&node->lock, NULL); If (NULL == mysql_init(&node->fd)) {printf("mysql init error. \n"); res = -1; break; } if (! (node->mysql_sock = mysql_real_connect( &node->fd, sp->ip, sp->user, sp->passwd, sp->db_name, sp->port, NULL, 0))) { printf("can not connect to mysql.\n"); node->sql_state = DB_DISCONN; res = 1; break; } // node->used = 0; node->sql_state = DB_CONN; Mysql_options (&node->fd, MYSQL_OPT_RECONNECT, &opt); opt = 3; Mysql_options (&node->fd, MYSQL_OPT_CONNECT_TIMEOUT, &opt); res = 0; }while(0); return res;Copy the code

3.3 Connection Pool Destruction

void sql_pool_destroy(SQL_CONN_POOL *sp) { printf("destroy sql pool ... . \n"); sp->shutdown = 1; For (int index=0; index < sp->pool_number; index++) { if (NULL ! = sp->sql_pool[index].mysql_sock) { mysql_close(sp->sql_pool[index].mysql_sock); sp->sql_pool[index].mysql_sock = NULL; } sp->sql_pool[index].sql_state = DB_DISCONN; sp->pool_number--; }}Copy the code

3.4 Retrieving a Usable connection from the connection pool To ensure that the probability of fetching each connection is roughly equal, here will start accessing the address subscript with a random number; When taking out, the connection status is also tested. If the connection is disconnected, the connection will be re-established

SQL_NODE *get_db_connect(SQL_CONN_POOL *sp) { Int start_index = 0, index = 0, I; int ping_res; if (shutdown == 1) return NULL; srand((int)time(0)); // Generate a random number based on the current time start_index = rand() % sp->pool_number; For (I =0; i < sp->pool_number; i++) { index = (start_index + i) % sp->pool_number; if (! Pthread_mutex_trylock (&sp-> SQL_pool [index].lock)) {if (DB_DISCONN == sp->sql_pool[index].sql_state) {// reconnect if (0! = create_db_connect (sp, & (sp - > sql_pool [index]))) {/ / reconnect the failure release_node (sp, & (sp - > sql_pool [index])); continue; }} ping_res = mysql_ping(sp->sql_pool[index].mysql_sock); if (0 ! = ping_res) { printf("mysql ping error.\n"); sp->sql_pool[index].sql_state = DB_DISCONN; release_node(sp, &(sp->sql_pool[index])); } else {sp->sql_pool[index]. Used = 1; sp->busy_number++; // The number of fetches is increased by 1 break; If (I == sp->pool_number) return NULL; else return &(sp->sql_pool[index]); }Copy the code

3.5 Regression connection

/* Return connection */ void release_node(SQL_CONN_POOL *sp, SQL_NODE *node) {node->used = 0; sp->busy_number--; pthread_mutex_unlock(&node->lock); }Copy the code

Connection pool management (Circle 2) My implementation here does not adopt an automatic solution, but provides an interface; Friends who have needs can achieve it by themselves; I propose a solution: create a manager thread during the initial connection pool phase, which checks the connection pool at regular intervals, using a set of logical code to decide whether to add or remove connections;

/* Add or delete connections */ SQL_CONN_POOL *changeNodeNum(SQL_CONN_POOL *sp, int op) */ int index; */ int index; int endindex; If (op == 1) {endindex = sp->pool_number; /* create a connection */ for (index=sp->pool_number; index < endindex; Index++) {// failed to create if (0! = create_db_connect(sp, &sp->sql_pool[index]); return NULL; } sp->sql_pool[index]. Index = index; sp->pool_number++; printf("create database pool connect:-%d-.\n",sp->sql_pool[index].index); } } else if (op == 0) { endindex = sp->pool_number - Num -1; For (index=sp->pool_number-1; index>endindex && index>=0; index--) { if (NULL ! = sp->sql_pool[index].mysql_sock) { mysql_close(sp->sql_pool[index].mysql_sock); sp->sql_pool[index].mysql_sock = NULL; } sp->sql_pool[index].sql_state = DB_DISCONN; sp->pool_number--; printf("delete database pool connect:-%d-.\n",sp->sql_pool[index].index); } } return sp; }Copy the code

 

= = = = = = = = = = = =

The complete code is as follows:

sqlpool.h

#ifndef __SQLPOOL_H_ #define __SQLPOOL_H_ #define IP_LEN 15 #define DBNAME_LEN 64 #define DBUSER_LEN 64 #define PASSWD_LEN 64 #define POOL_MAX_NUMBER 20 typedef struct _SQL_NODE SQL_NODE; /* Connection node */ typedef struct _SQL_CONN_POOL SQL_CONN_POOL; / / struct _SQL_NODE{MYSQL fd; /* mysql *mysql_sock; /* Pointer to connected MYSQL */ pthread_mutex_t lock; /* mutex */ int used; /* use flag */ int index; /* subscript */ enum{DB_CONN, DB_CONN}sql_state; }SQL_NODE; /* connection pool */ typedef struct _SQL_CONN_POOL{int shutdown; /* Whether to disable */ SQL_NODE sql_pool[POOL_MAX_NUMBER]; /* a bunch of connections */ int pool_number; /* Number of connections */ int busy_number; /* Number of connections obtained */ char IP [IP_LEN+1]; /* Database IP */ int port; */ char db_name[DBNAME_LEN+1]; /* Database name */ char user[DBUSER_LEN+1]; /* User name */ char passwd[PASSWD_LEN+1]; /* Password */}SQL_CONN_POOL; */ SQL_CONN_POOL *sql_pool_create(int connect_pool_number, char IP [], int port, char db_name[], char user[], char passwd[]); Int create_db_connect(SQL_CONN_POOL *sp, SQL_NODE *node); /* destroy connection pool */ void sql_pool_destroy(SQL_CONN_POOL *sp); */ SQL_NODE *get_db_connect(SQL_CONN_POOL *sp); /* Return connection */ void release_node(SQL_CONN_POOL *sp, SQL_NODE *node); /* Add or delete connection */ SQL_CONN_POOL *changeNodeNum(SQL_CONN_POOL *sp, int op); #endifCopy the code

sqlpool.c

#include <stdio.h> #include <stdlib.h> #include <string.h> #include <sys/socket.h> #include </usr/include/mysql/mysql.h>  #include <pthread.h> #include "sqlpool.h" //sql_pool_create(POOL_MAX_NUMBER, "localhost", 3306, "ceshi", "root", "qc123456"); */ SQL_CONN_POOL *sql_pool_create(int connect_pool_number, char IP [], int port, char db_name[], char user[], char passwd[]) { SQL_CONN_POOL *sp = NULL; if (connect_pool_number < 1) { printf("connect_pool_number < 1. defalut 1 \n"); connect_pool_number = 1; } if ((sp=(SQL_CONN_POOL *)malloc(sizeof(SQL_CONN_POOL))) == NULL) { printf("malloc SQL_CONN_POOL error.\n"); return NULL; } sp->shutdown = 0; Sp ->pool_number = 0; sp->busy_number = 0; strcpy(sp->ip, ip); sp->port = port; strcpy(sp->db_name, db_name); strcpy(sp->user, user); strcpy(sp->passwd, passwd); If (connect_pool_number > POOL_MAX_NUMBER) connect_pool_number = POOL_MAX_NUMBER; for (int index=0; index < connect_pool_number; Index++) {// failed to create if (0! = create_db_connect(sp, &sp->sql_pool[index]); return NULL; } sp->sql_pool[index]. Index = index; sp->pool_number++; printf("create database pool connect:-%d-.\n",sp->sql_pool[index].index); } return sp; } /* Create a connection */ int create_db_connect(SQL_CONN_POOL *sp, SQL_NODE *node) {int opt=1; int res=0; //0 Normal -1 Initialization failure 1 Connection failure do {if (shutdown == 1) return-1; /* lock */ pthread_mutex_init(&node->lock, NULL); If (NULL == mysql_init(&node->fd)) {printf("mysql init error. \n"); res = -1; break; } if (! (node->mysql_sock = mysql_real_connect( &node->fd, sp->ip, sp->user, sp->passwd, sp->db_name, sp->port, NULL, 0))) { printf("can not connect to mysql.\n"); node->sql_state = DB_DISCONN; res = 1; break; } node->used = 0; node->sql_state = DB_CONN; Mysql_options (&node->fd, MYSQL_OPT_RECONNECT, &opt); opt = 3; Mysql_options (&node->fd, MYSQL_OPT_CONNECT_TIMEOUT, &opt); res = 0; }while(0); return res; Void sql_pool_destroy(SQL_CONN_POOL *sp) {printf("destroy SQL pool... . \n"); sp->shutdown = 1; For (int index=0; index < sp->pool_number; index++) { if (NULL ! = sp->sql_pool[index].mysql_sock) { mysql_close(sp->sql_pool[index].mysql_sock); sp->sql_pool[index].mysql_sock = NULL; } sp->sql_pool[index].sql_state = DB_DISCONN; sp->pool_number--; SQL_NODE *get_db_connect(SQL_CONN_POOL *sp) { Int start_index = 0, index = 0, I; int ping_res; if (shutdown == 1) return NULL; srand((int)time(0)); // Generate a random number based on the current time start_index = rand() % sp->pool_number; For (I =0; i < sp->pool_number; i++) { index = (start_index + i) % sp->pool_number; if (! Pthread_mutex_trylock (&sp-> SQL_pool [index].lock)) {if (DB_DISCONN == sp->sql_pool[index].sql_state) {// reconnect if (0! = create_db_connect (sp, & (sp - > sql_pool [index]))) {/ / reconnect the failure release_node (sp, & (sp - > sql_pool [index])); continue; }} ping_res = mysql_ping(sp->sql_pool[index].mysql_sock); if (0 ! = ping_res) { printf("mysql ping error.\n"); sp->sql_pool[index].sql_state = DB_DISCONN; release_node(sp, &(sp->sql_pool[index])); } else { sp->sql_pool[index].used = 1; sp->busy_number++; // The number of fetches is increased by 1 break; If (I == sp->pool_number) {return NULL; } else { return &(sp->sql_pool[index]); Void release_node(SQL_CONN_POOL *sp, SQL_NODE *node) {node->used = 0; sp->busy_number--; pthread_mutex_unlock(&node->lock); } /* Add or delete connections */ SQL_CONN_POOL *changeNodeNum(SQL_CONN_POOL *sp, int op) int index; int endindex; If (op == 1) {endindex = sp->pool_number; /* create a connection */ for (index=sp->pool_number; index < endindex; Index++) {// failed to create if (0! = create_db_connect(sp, &sp->sql_pool[index]); return NULL; } sp->sql_pool[index]. Index = index; sp->pool_number++; printf("create database pool connect:-%d-.\n",sp->sql_pool[index].index); } } else if (op == 0) { endindex = sp->pool_number - Num -1; For (index=sp->pool_number-1; index>endindex && index>=0; index--) { if (NULL ! = sp->sql_pool[index].mysql_sock) { mysql_close(sp->sql_pool[index].mysql_sock); sp->sql_pool[index].mysql_sock = NULL; } sp->sql_pool[index].sql_state = DB_DISCONN; sp->pool_number--; printf("delete database pool connect:-%d-.\n",sp->sql_pool[index].index); } } return sp; } /* int main() { //MYSQL_FIELD *fd; SQL_CONN_POOL *sp = sql_pool_create(10, "localhost", 3306, "ceshi", "root", "qc123456"); SQL_NODE *node = get_db_connect(sp); SQL_NODE *node2 = get_db_connect(sp); if (NULL == node) { printf("get sql pool node error.\n"); return -1; } printf("--%d-- \n", node->index); printf("busy--%d--\n", sp->busy_number); if (mysql_query(&(node->fd), "select * from c1")) { printf("query error.\n"); return -1; } else { printf("succeed! \n"); } changeNodeNum(sp, 0); // Reduce changeNodeNum(sp, 1); / / add sql_pool_destroy (sp); return 0; } * /Copy the code

 

Thank you for reading my article and I hope your doubts can be answered

Copyright notice: This article is the blogger’s original article, shall not be reproduced without the permission of the blogger. Blog.csdn.net/qq_36359022…