Announcement Announcement Module
Collapse
No announcement yet.
Spring MySql hangs Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Spring MySql hangs

    Hi everyone.

    Well i have strange problem.
    i can't reproduce this error that i'll tallk about but it happens time to time.

    The case is this:

    After some time my application hangs. i can't connect to web gui.
    well all i have seen is that links that don't interact with database works and if i click on some link that is doing some query on MySql database it hangs.

    Then i added some diagnostic page that where i can test is working JMX, MySql and Mongo connection if that happens again.

    And today happened again.
    on my diagnostic page only Mongo database connection is working . MySql connection hangs and after 5minutes i get
    Error 504 Gateway timeout (because request timeout on server is 5min).

    Does anyone know where to start looking. i can't reproduce that error. it's just happens.

    my datasource configuration in application-contex.xml:
    Code:
    <bean id="DataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
            <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
            <property name="url" value="COMMONS.SQL.HOST_URI"/>
            <property name="username" value="COMMONS.SQL.USER_NAME"/>
            <property name="password" value="COMMONS.SQL.PASSWORD"/>
            <property name="defaultAutoCommit" value="true"/>
            <property name="poolPreparedStatements" value="true"/>
            <property name="validationQuery" value="SELECT 1"/>
            <property name="testOnBorrow" value="true"/>        
        </bean>
    db_url =
    Code:
    jdbc:mysql://localhost/some_database?relaxAutoCommit=true&autoReconnect=true&useUnicode=true&characterEncoding=UTF8
    thanks in advance.
    Last edited by cromorrissey; Apr 23rd, 2011, 10:22 AM.

  • #2
    I guess there is some point in your application that borrow a connection but never return it... (missing .close() at some point...) so your connection pool is empty...

    You can check opened connection with some mysql admin tool...

    Comment


    • #3
      I would tend to agree with gwa.

      The most likely problem is that you are using up connections in the pool and you are eventually getting to a point where there are none left for your application.

      Comment


      • #4
        thank you for answers.

        isn't dbcp responssible for closing connections or i need to do that manually?
        do you have some example how to close connection properly?

        thank you .

        Comment


        • #5
          Hello

          I suggest strongly use C3PO instead of BasicDataSource

          Your configuration is very basic, consider include pool, I think you are running out of connections

          BTW, let Spring manage the life cicly of your connections!, it is very useful and work excellent

          More information in the forums and of course in Spring documentation

          HTH

          Comment


          • #6
            I suggest using BoneCP as that is a faster (more recent) connection pool. Although that doesn't solve your problem .

            The problem in general is wrong or absent transaction management, using connections yourself and not closing them. I general I suggest use spring to manage your transactions and resources, that saves you a ton of work.

            Comment


            • #7
              I suggest using BoneCP as that is a faster (more recent) connection pool.
              Thanks for the advice Marten

              Comment


              • #8
                thank you all on your answers.
                i have fixed it.
                default setting of DBCP are :

                Code:
                maxActive 	8
                maxIdle            8
                maxWait 	indefinitely
                so when my application runs out of connections then there is a dead lock waiting indefinitely for connection.

                Comment


                • #9
                  Which still doesn't solve your problem, you only prolonged the life... You have a serious issue with your tx setup and connection management, which is what you should fix...

                  Comment


                  • #10
                    well... i've changed settings fro maxActive and maxIdle.
                    8 was not enough for my application...

                    Comment


                    • #11
                      The only thing you did was increase the connection pool, you didn't solve the problem of the depletion of your connection pool. You only fixed symptoms not the actual issue...

                      Comment


                      • #12
                        so what do you suggest me?
                        where to start?
                        is it problem in dbcp or in my code?

                        Thanks.

                        Comment


                        • #13
                          As Marten asked previously, are you using Spring for your connection and transaction management?

                          Comment


                          • #14
                            I'm using spring NamedJdbcTemplate if is that what are you asking and for data source using apache DBCP.

                            Thanks.

                            Comment


                            • #15
                              can't connect to web GUI

                              Hi,

                              Connection.close will return the connection to the pool.
                              After setting up JDBC yout code can basically stay the same.
                              To close the real connection to the database at application shutdown do:
                              connectionPool.close()

                              Use standard JDBC cleanup to release connections in finally, because request multiple connections within single transaction.



                              Regards
                              Kevin Thomas
                              Attune Infocom Inc
                              http://www.attuneinfocom.com
                              Email : [email protected]
                              Liferay portlet Development

                              Comment

                              Working...
                              X