A bunch of people do a bunch of stuff and something goes wrong with InterBase

Hello blog, I've been gone for a long time. Today I feel inspired...

Other than dealing with licensing and registration questions, the most common question I get from customer is something to the tune of "A bunch of users, did a bunch of stuff for a while and then something went wrong with my InterBase server". Something wrong is the server is hung, or it crashed, or it is performing really slowly. Naturally, as presented this is an unsolveable problem with a pleathura of things to look at. For me to assist you, I need a reproduceable test case. Without this, I am merely spinning my wheels. I understand how people are lost as to how to create a test case, but I really cannot help directly with this as presented.

Here is a list of everything I suggest to people with this problem. This list is in no particular order.

1. When the problem occurs, run iblockpr -h on the server and look at the size of used vs .length. If they are the same or nearly the same, double the size of V4_LOCK_MEM_SIZE in ibconfig and restart the server.

2. Use the InterBase Performance Monitor included with IBConsole. Peformance Monitor is just another connection to the database, so if your server has crashed or is hung, it isn't going to help you directly. What you are looking for with Performance Monitor are trends before the problem occurs. How many attachments are there? Is the same user always in when the problem occurs? (If so, find out what they doing!) How many statements are there? Are there any really long running statements? Are there any really long running transactions? To understand most of these answers, you first have to understand what your application(s) do. For example, if all your transactions should be within 1 second, a transaction with 5 seconds duration might be long. Otherwise, transactions of 2 hours in length might be legal for interactive users.

Also keep in mind that Performance Monitor is a snapshot. It is not possible for it to log every operation the server performs. However, you might consider the logging option of Performance Monitor to capture as much as you can.

3. Get InterBase 7.5.1 if you do not already have it. It is free to all 7.x users. If we find a problem and fix it, the fix is going to be based on this release anyway.

4. Create a test bed application that mimicks what your users are doing. Even if everything is rosy you should have this anyway.

5. Check to see if you database is corrupted with gfix -verify -mend.

6. If you have reporting users, create a reporting instance of InterBase and get those users to use it instead. This doesn't solve the problem, but it helps isolate the problem and makes sure reporting users do not effect interactive users.

7. Do client side monitoring. How you do this depends on the driver you are using. Drivers with client side monitoring include DBP, IBX, BDE/SQL Links and ODBC. The idea here is you have some users you suspect are causing the problem, so you log what they are doing, and look to see what they were doing when the problem occurs.

8. Create before update, before insert and before delete triggers on all tables, or at least ones that you suspect or are used frequently, and have the trigger write to some sort of logging table. You might make the logging table be a foreign file so that logging won't be lost if the problem happens between when the trigger fires and a commit happens. The disadvantage is transactions which are rolled back are still logged.

Using triggers is the closest you can come to logging everything the server does. It is not possible to log select statements.

9. Because all that performance monitor does is query System Temporary tables, you could invent your own process that does something similar to what Performance Monitor does and have more control. For example, if you suspect the problem is with the number of attachments, have a simple ap where all it does is select count(*) from attachments and run the application periodically. You could use isql to do this.

10. Most performance problems are bad application design. This is a whole topic in of itself. In short, you need to identify what operations in your application cause performance problem and deal with them.

11. If deadlocks are possible in your application and you have transactions that are long lived, make sure you have wait set to false when you connect to the database. Otherwise, your application will hang when deadlock occurs until the deadlock is resolved. Applications without deadlocks will be uneffected though.

12. People with performance problem frequently look to changing the ibconfig file to try to resolve their issues. This is NOT the way to go. Ibconfig is for tuning your application. If you have a performance problem you are not to the level of tuning yet. Ideal changes to ibconfig typically make improve performance 10%-25%.