In my current assignment we collect queries that take more than a second to execute in our production environment We then parse these logs using a perl script to populate unique queries a database table. A charting tool is used to generate top 10 slow schema's and their top ten slow queries to keep our focus.
As a next step we tune the queries on following priority basis:
1. repeatedly run slow
2. examine lot of records
3. fetch lot of records
4. take more time to execute
5. take up locks for long time
We spend some time every day on the reports and many a times we learn new patterns which if attached would help to get rid of many slow queries.
For e.g. we had a release of a common component that several applications use. We found that the slow query count increased one fine day. The common component was actually firing these queries impacting performance of the applications using them. We of course tuned all of them and witnessed the sky scrappers become huts in 2 days :)
No comments:
Post a Comment