If you have ever worked in an IT shop, you will know that the one thing you cannot escape from is issues related to Database performance. Now, I am no DBA in any way or fashion, but thought I should record some of the common issues and ways they have been overcome in my career so far. More for self records than trying to teach someone :).
- Network Related
- Check if the NIC (network interface) on the DB server has any speed mis match with the network device (most probably a switch) that it is connected to.
- Check the latency between the application and the DB (pertains to applications connecting to DB over WAN links)
- System Related
- Check if a rouge process is using all the system resources.
- Check if the disk sub system is performing optimally
- Recommend using RAID 10 for transactional systems.
- Don’t forget to check those batteries on the RAID controllers 🙂
- Check if the DB is just running out of gas.
- Long term capacity trending records come in handy here.
- Database Related
- Views are evil.. if created the wrong way. Esp, on data that is accessed frequently. Remember that you are now making two calls to the database. One to read the data, One to create the view.
- Ensure your logs are not being writted ot (thx Ray for pointing out the typo) written to the same disk subsystem as your data files.
- Indexes are good.. But only to an extent. If the size of your indexes is twice the size of your data.. you have an issue.
- Check for invalid objects. You will be surprised how many times, people overlook this.
- Sometimes, it helps to flush the SGA (Oracle specific). Be aware that it will slow down the response time for a while (until the cache gets populated again).
- Avoid excessive monitoring. Esp. with tools that query the system tables quite frequently. This has a negative impact on the database performance.
Did you run into any strange situations and figured out a solution? Please feel free to add your comments below…
Writted is not a word my friend 🙂
Couple things I’ve run into…
* Make sure your not out of disk space on either your data volume or your log volume!
* We ran into this little gem with Postgresql recently… http://blog.endpoint.com/2008/12/why-is-my-function-slow.html
* Triggers are the devil, from a design and maintenance perspective.
Great post Vinay!
Thx bud.. Typing at 1 in the night tends to make you do crazy things.. And thx for sharing your experiences.
Logical Views in OLTP environment are not good. (See http://www.ss64.com/orasyntax/3views.html) Materialized views are good for access but bad for maintenance. Regarding 2 calls to the database is not correct. You create a view only once. when you query a view you will not create anything except use the SQL in the view.
But doesn’t the view (unless it is an materialized view) get created on the fly?