I'm trying to use C3P0 library to handle connection pooling. These are my C3P0 settings:
minPoolSize=3
maxPoolSize=20
acquireIncrement=1
maxIdleTime=240
maxStatements=20
In the log I can see that C3P0 seems to be correctly initialized by reading
INFO com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource - Initializing c3p0 pool...
But when I try so see how many connection there are on my MySQL db with
SHOW STATUS WHERE `variable_name` = 'Threads_connected'
I can see that the result is 48, 46, 49 ecc.
I can't understand if is not correct the way I try to see how many connections there are on the db or I did't understand the way C3P0 works
I also faced such confusion in the MySQL for threads & connections. I will explain what I have learned and grasp while studying the same, if anything that I have misunderstood then or still in confusion then please make me correct.
Some basics in MySQL:
- MySQL server is a single process application.
- It is multithreaded.
- It accepts connections like TCP/IP server.
- Each connection gets a thread.
- These threads are sometimes named processes, and sometimes they're referred to as connections.
Last and second last point makes so much confusion, In our mind we think that there is a 1-1 mapping between connections and active threads. It true also but then, there is a thread pool, which means there can be threads which are not associated with any connection.
Every new connection gets its own thread. Every new connection makes new thread, and a disconnect calls for thread's destruction. So, there is a 1-1 mapping between connections and active threads. After destruction of threads it may go into the thread pool. So, the number of threads is greater than or equal to the number of connections.
Also If you run below query
SELECT t.PROCESSLIST_ID,IF (NAME = 'thread/sql/event_scheduler','event_scheduler',t.PROCESSLIST_USER) PROCESSLIST_USER,t.PROCESSLIST_HOST,t.PROCESSLIST_DB,t.PROCESSLIST_COMMAND,t.PROCESSLIST_TIME,t.PROCESSLIST_STATE,t.THREAD_ID,t.TYPE,t.NAME,t.PARENT_THREAD_ID,t.INSTRUMENTED,t.PROCESSLIST_INFO,a.ATTR_VALUE FROM performance_schema.threads t LEFT OUTER JOIN performance_schema.session_connect_attrs a ON t.processlist_id = a.processlist_id AND (a.attr_name IS NULL OR a.attr_name = 'program_name') WHERE 1=1
Then you will see column TYPE in that values are either FOREGROUND or BACKGROUND so this tells there can be some threads which are connected with DB to do some (background) work (eg. event thread, monitor thread etc.).
generally, c3p0 concerns about connection and not for threads, so you should check for SHOW FULL PROCESSLIST for connections with DB server.
I hope I have cleared the confusion which you are having with MySQL threads & connections.