Your system database queries seem to work fine. Why worry about monitoring and maintaining your database? But slowly, people notice that queries and programs are taking longer. Then, all of the sudden, you see that your company’s database applications have slowed to a crawl. Fixing the problem in crisis mode, leaving your business and customers in limbo, means lost business and lost productivity. With simple monitoring, this scenario could have been avoided.
Think of your car. You know all those little indicators on the dashboard? Hopefully you pay attention when they light up. Yes, your car still runs, but perhaps not for long. And low and behold, when you do get it tuned up, perhaps your mileage just jumped from 25 miles/gallon to 35 miles/gallon. Who knew? Maybe you should have had that weird sound you heard last week checked!
The takeaway? Database monitoring needs to be done on a regular, if not full-time basis. Database platforms are reasonably resilient, and will find their way around minor database flaws, until they are overwhelmed.
There are four levels of operation that must be watched: the SQL level, the instance/database level, the infrastructure level, and the user/session level. Each of these can cause their own issues, some more troublesome than others, but unattended to, they can all bring your processing to a crawl. When you see any change in behavior, it’s probably time to do some professional tuning.
So what do I need to monitor?
Five major metrics can indicate a growing problem or inefficiency.
Database throughput: One of the most important and obvious metrics. If throughput is less than the number of incoming queries, they will back up and slow all processes down while the server thrashes. Time to upgrade processing power/memory, or optimize the queries.
Database Response: Simply stated, how long does the server work before you get query results? If you see this dropping you need to optimize your queries, or increase concurrency limits.
Database Connections: If you have more open connections than active queries, then there may be a flaw in your query code that is not shutting connections after results are obtained. This creates unnecessary strain that is easy to fix in your code. But unless you monitor it, you may find hundreds of open connections, sucking you processing power and memory.
Number of Errors: Errors could be caused by access problems, lack of memory, too many connections, or blocked users. If errors are occurring, and queries aren’t working, you probably have a lot of frustrated users. Analysis is needed to find the bottleneck and fix it.
Most Frequent Query Optimization: Chances are that there are about a dozen or so queries that users run frequently. It behooves you to optimize these queries to ensure that they run effectively. It will go a long way to improving performance without upgrading hardware.
Can you monitor all of this yourself? The answer is “Maybe”. There are many automated tools that will catch issues in a timely manner. However, having the capabilities to repair them and optimize the server in a timely manner are not that common. It pays to have a certified DBA available who can find their way through your system, and fix what ails it. Talk to someone like AnswerPoint about your needs. You will surely find that the increased speed, happy users, and security of a highly tuned systems is well worth investing in.
Ed Weisberg is VP of Sales and Marketing at AnswerPoint, LLC. He can be reached at Ed.Weisberg@AnswerPoint.com