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?
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.
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.
author & internet detective
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.
There are no new questions, but there may be new answers.
Joined: Mar 18, 2008
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 .
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.