4 Replies Latest reply on Nov 20, 2006 3:58 PM by doktora

    Slow remote database connection

    doktora

      I have always used postgresql on the localhost, which was also running JBoss.

      I have moved the DB to a separate computer on the LAN (gigabit), so now JBoss connects via port 5432 (default tcp/ip for postgresql).

      It is performing terribly slow when fetching data from the remote DB.

      If I connect from the localhost to the remote DB directly via psql -h , I can run the query faster by hand and get the results before JBoss comes around. So I believe the problem is in JBoss/Hibernate and not in my network.

      Has anyone experienced this? Where do I begin to look in order to find this bottleneck?

      I'm running the following config:

      Release ID: JBoss [Zion] 4.0.4.GA (build: CVSTag=JBoss_4_0_4_GA date=200605151000)
      Java VM: Java HotSpot(TM) Server VM 1.5.0_06-68,"Apple Computer, Inc."
      OS-System: Mac OS X 10.4.8,i386
      Postgresql 8.1.5 (built from source)

      I'm running a single (non-clustered) instance of JBoss with EJB 3.0.

      -- Dok

        • 1. Re: Slow remote database connection
          otaylor

          What I'd suggest doing is increasing the verbosity of logging from Hibernate
          to the point you can get it to log the SQL queries it is doing in check to make
          sure that the *number* of queries it is doing is reasonable. One possible reason
          that a large query might be slow remotely is that it is doing one query to
          fetch the set of rows and then an addititional query per-row. The per-query
          latency will be greater with a remote database, and even a millisecond or
          two per row can add up to a lot.

          (If you do find thousands of queries, it may be that some relationships on your entities
          need to be made lazy)

          • 2. Re: Slow remote database connection
            doktora

            I think you hit it on the nail. I've looked at the logs and there are a couple of hundred ones, but definitely not thousands, of 1-row queries.

            Also this issue has gone away with time, without me touching anything. That must be caching in action.

            I'll investigate further, add laziness and do a fresh start to see if this will improve things.

            This tip is very much appreciated!

            • 3. Re: Slow remote database connection
              ayyappan.ap

              Hi doktora,
              I am also having the same problem as you.
              I am using the Micrsoft Sql server 2000 and am getting the connection using data source.
              And i am calling a stored procedre from the client. When i run this stored procedure directly using the
              query analyzer i am getting the results much faster. But its taking much time to fetch the datas when i am calling the same stored procedure via jBoss. This is very much similar to the problem that you faced. Did you find any solution to the problem. If so kindly let me know what to do.
              And also people who come across such problems help me to find solution for the problem
              Thanks in advance,
              Ayyappan

              • 4. Re: Slow remote database connection
                doktora

                Hi,

                While I tried to dig deeper into this, a couple of days after it started happening, it stopped... without me doing anything. I was just going through the logs.

                I've restarted all servers and I cannot replicate the problem. It must have been some sort of caching problem.

                For my own assurance, I've consequently gone through the design of my application and removed unneeded EAGER joins. I saw that those could cause a lot of unnecessary table look-ups.

                It is indeed strange for the same to happen with a stored procedure. I would suggest going through the JBoss logs at the exact location/time when you call the stored procedure.

                For fetching one entity from my DB, JBoss dumped over 1MB of log data in its log file. It seems daunting to look at all of this stuff at first, but you get the hang of it.

                Good luck,
                let me know how it goes.

                doktora