File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Performance and the fly likes Performance issue while inserting transactions in DB when concurrent users are more Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Performance
Bookmark "Performance issue while inserting transactions in DB when concurrent users are more" Watch "Performance issue while inserting transactions in DB when concurrent users are more" New topic
Author

Performance issue while inserting transactions in DB when concurrent users are more

Sayali Chaudhari
Greenhorn

Joined: Mar 18, 2008
Posts: 5
Hi ,

My application has 2 components. One components (JEE 1.6_34, JBoss 6.1, MySQL 5.5, Struts 2, Hibernate 3.3) gets deployed on hundreds of end user computers (500 at present). The other component (JEE 1.6_34, JBoss 6.1, MS SQL Server 2008 R2, Struts 2, Hibernate 3.3) is deployed at central location. The end user computers are used for doing transactions - approximately 250 per day over 8-10 hours of duration. 50% of these transactions are first saved in local computer and then sent to central server asynchronously via JMS every 5-10 mins. Remaining 50% transactions are special transactions and are inserted directly in central server DB i.e. MS SQL Server using JDBC statements. During same time other operations (report generation using Jasper Reports) are also happening on central server DB. All these operations and transaction insertion accesses same set of tables. At present there are around 4.5 Million transactions (in 4 months). Each transaction has other related data which is spread across 7 other tables. We are facing performance issues while doing transactions which are directly saved in central server. The time taken to save each transaction varies from a few seconds to 15 minutes. Acceptable time limit is 10 seconds. Can someone provide some pointers on what should be looked at to improve performance while inserting transaction in central DB? During each insertion DB rearranges the indexes. Could having huge number of transactions (4.5 M) already in DB be one of the reason for overall slowdown? Will archiving older transactions to backup table help cause if less transaction are there in main table, every time transaction is inserted, there will be less re-indexing needed. Another question is can report generation, which is a daily activity and need actual daily data, be done in any different manner to avoid impact on transaction insertion. Is there anything around transaction isolation levels that should be looked at?

Regards
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30506
    
150

The first step is to find out where the problem lies. Is it the insert queries themselves? If so, talking about the indexes is a good idea. Is it the transactions waiting on each other? If so, talking about the indexes won't help.

You can try to recreate the problem in a test environment - and use a profiler. Or you can look at your database's statistics to rule it in/out as the problem.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Sayali Chaudhari
Greenhorn

Joined: Mar 18, 2008
Posts: 5
Thanks Jeanne for quick reply.

When transaction saving operation starts, the database connection is obtained within 15 seconds - connection timeout is set to 15 seconds and we don't see connection timeout exception. During morning hours when number of concurrent users are less, transactions get saved faster compared to later part of the day when more transactions are happening concurrently and report generation too is in progress . That time transactions are waiting and that must be the reason for slower operations. Can you please provide more details on what statistics should be looked at in profiler or at DB level.

Regards
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30506
    
150

On the database level, it depends on which database you are using. Here's how in postgres. This lets you see things like if queries are waiting for resources and what queries are being run. Once you know that, you can start looking at explain plans to see if any are suboptimal.

As far as smaller tables, yes. That generally helps.

With a profiler, you are looking at the Java code, to see what part of the code is taking the most time. It sounds like you already know that - the database part.
Darryl Burke
Bartender

Joined: May 03, 2008
Posts: 4540
    
    5

Rupali, please BeForthrightWhenCrossPostingToOtherSites
http://www.java-forums.org/advanced-java/80774-performance-issue-while-inserting-transactions-db-when-concurrent-users-more.html


luck, db
There are no new questions, but there may be new answers.
Sayali Chaudhari
Greenhorn

Joined: Mar 18, 2008
Posts: 5
Hi Darryl,

I am just trying to get as many inputs as possible to resolve my problem. As different people use different forums I have posted my question on two different forums. I am not sure if it's against the Forum rules .

Regards
Jayesh A Lalwani
Bartender

Joined: Jan 17, 2008
Posts: 2370
    
  28

If the performance degrades during periods of high load, there can be 2 possible causes
a) contention between transactions:- This can happen when multiple transactions try to lock the same record in database
b) the load on the database is too high:- If the database is taking too much CPU/memory or IO, the performance will degrade.


InnoDB monitors give you a lot of information that will be helpful for you but might be hard to parse. You can start off by using SHow processlist during periods of high load. This will atleast tell you which queries are running against the database during high load, and what they are doing. You might be able to figure out transaction contention by just looking at the queries.

8 times out of 10, I have diagnosed performance problems using Show processlist. Usually it's some queries that are very costly, and show processlist just tells you what the costliest queries are. Other times it's lock contention, and you can see right there that one query is waiting for another.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Performance issue while inserting transactions in DB when concurrent users are more