MySQL database too many connections error resolution process record

Source: Internet
Author: User

When doing a registration page, to use the AJAX asynchronous check user input is correct, but also to do the user name check to work, so need to frequent access to the database. I use the c3p0 of an operational database utils is my own after watching the study video I do, I do utils and video do the difference is: The video does the utils is registered driver, Set Combopooleddatasource and get connection separate, what I do is a getconnection method one step. The usual use of the database frequency is not high also found no problem, until now unmasked ...

Yesterday there was an error too many connections; I also wonder why my connection will be closed every time I run out, why too many connections ...

Use the command show full processlist; Found that there were 20,000 sleep connections,

One stop program runs and only one link is left (that is, the native connection),

Check the code and find that the "Create Connection pool" and "Fetch connection" and "close connection" codes are put together, resulting in such a workflow:

A user accesses-----> Creates a link pool (20 connections)---> Takes a connection-----> closes a link;

So multiple access (multiple user access) created a number of connection pools, but each user only closed one of the links, leaving a bunch of links;

For example, 30 user access, each user access to create a 20 connected pool, a total of 600 links created, but 30 users at the end of all access, a total of 30 links are closed, leaving 570 links.

More and more users, causing useless links more and more, so there is too many connections

The correct way to use the database is to set the Combopooleddatasource first, then call a link, and each call is closed;

Refer to the following procedure (test procedure, not registration program)

 Public classc3p0 {combopooleddatasource DataSource=NewCombopooleddatasource (); @Test Public voidName ()throwsException {//1000-Time testsetconnection ();  for(inti=0;i<1000;i++) {Thread.Sleep (200);            Operatedatabase ();        System.out.println (i); }    }            /*** Demo C3P0 How to use * @project_name Day11 * @class_name C3p0demo *@authorDovinya * @data 2014-8-27 pm 07:57:42 *@version1 * @notes*/        Public voidSetConnection () {//Set Combopooleddatasource        Try{Datasource.setdriverclass ("Com.mysql.jdbc.Driver"); Datasource.setjdbcurl ("Jdbc:mysql://localhost:3306/kxpro"); Datasource.setuser ("Root"); Datasource.setpassword ("514079"); Datasource.setminpoolsize (5);//Connection Pool Minimum number of connectionsDatasource.setinitialpoolsize (20);//Connection Pool creation time number of connectionsDatasource.setmaxpoolsize (80);//Connection Pool Maximum number of connectionsDatasource.setacquireincrement (5);//the number of connections created when the link runs outDatasource.setnumhelperthreads (5);//multi-threaded execution for improved performance}Catch(Exception e) {//TODO auto-generated Catch blockE.printstacktrace (); System.out.println ("No driver found"); }} @Test Public voidOperatedatabase () {//using linksConnection conn =NULL; PreparedStatement PS=NULL; ResultSet RS=NULL; Try {  //class.forname ("Com.mysql.jdbc.Driver"); //conn = Drivermanager.getconnection ("Jdbc:mysql://Localhost:3306/day11 "," root "," 123 "); //PS = conn.preparestatement ("SELECT * from Account"); Conn=datasource.getconnection (); PS= Conn.preparestatement ("SELECT * FROM Login"); RS=Ps.executequery ();  while(Rs.next ()) {System.out.println (rs.getstring ("Username")); }                                   while(Rs.next ()) {String name= rs.getstring ("name");                  SYSTEM.OUT.PRINTLN (name); }                                } Catch(Exception e) {e.printstacktrace (); }finally{                  if(rs!=NULL){                      Try{rs.close (); } Catch(SQLException e) {e.printstacktrace (); }finally{RS=NULL; }                  }                                    if(ps!=NULL){                      Try{ps.close (); } Catch(SQLException e) {e.printstacktrace (); }finally{PS=NULL; }                  }                                    if(conn!=NULL){                      Try{conn.close (); System.out.println ("Conn Yiguanbi"); } Catch(SQLException e) {e.printstacktrace (); }finally{conn=NULL; }                  }                                       }          }                }

The most important thing is that Combopooleddatasource is going to be a member variable, only once, creating the connection pool, and then the user just needs to take it out.

MySQL database too many connections error resolution process record

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.