Is Your Database Application Armed? was originally posted on the main page of LDS Tech. It was written by Lynn Conrad
When debugging an application that is not performing well, finding the real issue can be very hard. Is it The LAN? The WAN? Is it the application server? Load balances? DNS? LDAP? The database server? Maybe it is the SAN or a disk storage device. We can do things to make it easier. You can arm your application and build it so that it is ready to do battle in the complete operation arena that it will be required to run in.
Arming into the DB Layer
Most databases have a procedural language and the ability to trigger audit transactions, and drive functionality. You can also build in debugging triggers that can track what is happening in the app. You can design your procedures and functions to do the same. If you track how long each type of transaction or call takes to complete, and what the parameters were when a function or package is called, then you have a great tool for debugging development, as well as identifying production issues. If you see that the “create new” function doesn’t take any longer to complete than it did before the slowdown, then the database is not the issue; it must be further upstream. If they are taking substantially longer, then it must be downstream (disk, server, DB configuration, DSS queries in an OLTP database, etc.).
Arming into the App Layer
Vendors sell costly systems to perform user transactions through the app. Often these are not worth the investment as they usually can’t perform the full breadth of transactions needed. There are some transactions that cannot be simulated, as they will cause problems with the app because they are not real. Most people find that they use very little of the tool, and they require a lot of custom code. Some simulate the transactions into different tables.This works, but query plan issues are not seen by it, as they are different tables and have different stats. Thus you should build your own. For example, suppose you build an app robot that creates transactions and then removes them. You can set it up to function like your own app without a user connected, and with tagged transactions that test the infrastructure without breaking application functionality. You simply need to add it to the application requirements. When a user does x, then (…) , and when the robot does x, then (…). You should be able to set the mix of transactions and how often they are performed. I encountered an application written in VB a long time ago that did just that. It had eight different example transactions that it could run and then delete out of the application. Twenty of these robots were then put around the WAN and LAN. These robots recorded the time it took to perform these transactions. It was a great debugging tool. Stats were averaged by ten minutes, hours, and days and stored in a repository. Comparing the time to complete transactions from the robot in the data center to the one at the far end of the WAN helped the network team diagnose problems.
Using these robots on new hardware in a dev and test environment helped management decide if new was worth the cost.
You may say, “Oh, I don’t have time for this, or this app must run as fast as possible and can’t handle the overhead.” I have found that it will help you get done sooner, and you can always set it up to be turned off. Triggers can be enabled and disabled. Functions in them or procedures can be enabled and disabled on the fly. You don’t need to have the full debugging on all the time in production. However, your operators will love you every time they turn it on to find a problem.
On one currently in development, we found that the ORM layer was making two calls to the database every time a user clicked on a search button. At first we could not understand why, but after looking at the detail provided by the procedure logging all of the calls to it with start and stop times and parameters, we found fundamental design flaw, and so we changed the table structure long before the app went to QA or to users. Developers should build in the debugging from the very beginning, arming applications to do battle in the operation world.
Lynn Conrad is a database engineer for the Church