AWS - RDS - mysql instances

http://serverfault.com/questions/599421/highest-value-of-max-connections-in-aws-rds-micro-instance

MODEL      max_connections innodb_buffer_pool_size
---------  --------------- -----------------------
t1.micro   34                326107136 (  311M)
m1-small   125              1179648000 ( 1125M,  1.097G)
m1-large   623              5882511360 ( 5610M,  5.479G)
m1-xlarge  1263            11922309120 (11370M, 11.103G)
m2-xlarge  1441            13605273600 (12975M, 12.671G)
m2-2xlarge 2900            27367833600 (26100M, 25.488G)
m2-4xlarge 5816            54892953600 (52350M, 51.123G)
show variables like 'max_connect_errors' 


Blocked because of many connection errors







many connection error













org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.exception.GenericJDBCException: Could not open connection



    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:932)



    org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:816)







org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.exception.GenericJDBCException: Could not open connection



    org.springframework.orm.hibernate4.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:428)



    org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:372)







org.hibernate.exception.GenericJDBCException: Could not open connection



    org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54)



    org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)



    org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)







java.sql.SQLException: null,  message from server: "Host 'ip-10-248-46-25.ap-southeast-2.compute.internal' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'"



    com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)



    com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)



    com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1104)



    com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2137)

















Once this error happens can't access the database from same URL, but can be done from different URL.



Quick Fix



mysqladmin -h ssssss.ffffffff.ap-ffffff-2.rds.amazonaws.com -u root -p flush-hosts



Solution





By default, mysqld blocks a host after 10 connection errors. For more details on the same please refer to http://dev.mysql.com/doc/refman/5.0/en/blocked-host.html.



Can you try increasing the value of max_connect_errors to see if it resolves your issue? You can find more details for the same at http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_max_connect_errors. You can use the CLI for modifying the parameters as documentedathttp://docs.amazonwebservices.com/AmazonRDS/latest/CommandLineReference/CLIReference-cmd-ModifyDBParameterGroup.html





Follow these steps to increase the MySQL limits and avoid locking the database:


    

  1. Download the RDS Command Line Toolkit here
    2. 

  2. Unzip the toolkit and add the following environment variables
      

    1. Add <unzipped_folder>/bin to the PATH variable
      2. 

    2. AWS_RDS_HOME=<unzipped_folder>
      3. 

    3. AWS_CREDENTIAL_FILE=<unzipped_folder>\credential-file-path.template
      4. 

    4. Optionally, override the default region with EC2_REGION=<region> (e.g. us-west-1)
      5. 

    5. JAVA_HOME=<JDK location>
      6. 

    

    3. 

  3. Enter a valid access key into the credental-file-path.template file 
      

    1. You can get the access key from the AWS console under "Security Credentials"
      2. 

    

    4. 

  4. In AWS Console, create a "DB Parameter Group" with the database parameters required
      

    1. Created one called "mysql-custom"
      2. 

    2. Changed max_connect_errors=1000
      3. 

    3. Changed max_connections=1000
      4. 

    

    5. 

  5. Open a command prompt
      

    1. Change the parameter group of a database with the command: rds-modify-db-instance finance101test --db-parameter-group-name=mysql-custom --region ap-southeast-2
      2. 

    

    6. 

  6. Restart(Reboot) the database
    7. 

  7. Verify the changes by opening a MySQL command line (i.e. with Navicat) and typing: show variables like 'max_connect_errors';
    8. 




References

Comments

Post a Comment

Popular posts from this blog

API design best practices

API Design summary 1. Use two base URLs per resource 2. Keep verbs out of your base URLs 3. Use HTTP verbs to operate on the collections and elements 4. Be consistent either use plural nouns or singular nouns 5. Use concreate names rather than abstract names     An intuitive API uses plural rather than singular nouns, and concrete rather than abstract names 6. keep your API intuitive by simplifying the associations between resources, and sweeping parameters and other complexities under the rug of the HTTP question mark. Handling errors Errors boil down to Everything worked - ok The application did something wrong – client error The API did something wrong – server error In summary, be verbose and use plain language descriptions. Add as many hints as your API team can think of about what's causing an error. eg: HTTP Status Code: 401 {"status" : "401", "message":"Authenticate","code": 20003, "...
Read more

DB Connection Issues

1. Application gives "1c86bdf0 SharedPool I J2CA0086W: Shareable connection MCWrapper id 686bbdf9 Managed connection com.ibm.ws.rsadapter.spi.WSRdbManagedConnectionImpl@6156bdf9 State:STATE_TRAN_WRAPPER_INUSE" message when trying to do a db update within loop which is querying a view and carrying out some operations. In IBM this problem is documented under the topic " Application hangs with prior J2CA0086W warning messages" The solutions are  1. The application must be modified to use serial reuse or 2. The connection pool can be changed to unshareable. Shareable connections are not allowed within a local transaction containment boundary. The above scenario suggests a possible problem in the application logic and how it is using the connection code. The J2CA0086 message is telling you that your application is using a shared connection in a LocalTransaction. Since the connection is enlisted in a Local Transaction, and not a Global Transaction, di...
Read more

Reading Excel Sheet on client side and processing it

Image
If you ever want to process excel sheets on the client side (on the browser) then this will be useful for you.  This uses  http://oss.sheetjs.com  coed and the source files are in ( https://github.com/SheetJS/js-xls ,  https://github.com/SheetJS/js-xlsx ) it is licensed under Apache Licence, Version 2.0 (refer attached licence in the libraries in github) Steps to Follow, JS Files required: Copy the code for following files and make corresponding files locally, or download them from github,                 https://raw.github.com/SheetJS/js-xlsx/master/xlsx.js                 https://raw.github.com/SheetJS/js-xls/master/xls.js                 http://stuk.github.io/jszip/ We will be taking some javascript already in oss.sheetjs.com for our example particularly on the main page of oss.sheetjs.com. Sample Excel Sheet: Our exce...
Read more