Enterprise databases are often built using popular database software like MySQL, Oracle DB, Percona DB, etc. Though they seem similar, there are vast differences in terms of their function and their goals. Legacy approaches that have been applied across databases in the same way are moving out of trend. As of 2020, we are witnessing personalization being widely incorporated in database optimization processes. Personalization involves understanding the current needs, the infrastructure, and working closely with the administrative teams to achieve the database performance goals. Application performance is a high visibility metric in a given organization. An individualistic approach from DBAs allows this metric to be maintained transparently.
In this article, we will talk about how we are successfully implementing personalization towards the performance tuning of databases. We will also talk about our experience of what works best and what should be avoided.
Database Optimization Processes
Database optimization is a broad term encompassing multiple sub-processes each having their life cycle. While some processes are executed once, others are performed regularly. For example, analysis of database design is one such process that is performed at the beginning. Query optimization to improve performance is an example of an ongoing process.
Database Optimization is a set of processes that lasts a long time. Where there is an application, there needs to be performance tuning performed frequently. Databases don’t remain the same and the volume of data increasing is enough to hamper performance. We have been partners with over a hundred clients over many years where optimization processes have lasted for five years on average.
With the advancement in technology, a lot of processes were designed to scale up. However, the scaling of optimization tasks has led to the loss of detailed programs. We were seeing how a standard checklist was being applied irrespective of the performance goals or the current state of the database. This is where we decided to reinvent ourselves and bring about a much-specialized approach. We have refined and executed approaches tailored to specific needs and achieved tremendous results.
Personalization for Performance Tuning
Image Credit : Processes for Effective Tuning of Databases
There are many approaches to database performance tuning but not all are necessary for a given Optimization scenario. Two mistakes need to be avoided here.
- Trying to apply all the tuning approaches to a given enterprise database without analysing the requirements first.
- Implementing approaches without considering the performance gains thereby spending time on tasks that don’t give a boost to overall efficiency.
Both these mistakes can cost money and time, the two most critical resources being wasted. Thus, what we need is an exploratory approach that builds a personalized plan for a given application database. The following steps can be incorporated to achieve performance gains without wasting resources.
Step #1: Analyzing the Database Design and Performance Goals
Some organizations might want to ensure that their existing database performance remains stable while others might want to improve it. The goal of a DBA is to first analyze the design to evaluate if it’s capable to meet the performance requirement.
The knowledge of various databases like the Oracle DB, MySQL, etc is a bonus. For example, based on the database, the query optimization techniques vary. The variance can point towards hardware optimizations to help with performance rather than the DB design itself. Overall, the DBA is navigating the current infrastructure and aligning it with the main goal.
Step #2: Setting up Routines and Processes Proactively
The previous step enables us to identify the most important processes to achieve the goal. For example, let’s say that slow queries have been causing performance issues. Now, eliminating slow queries will become one of the processes we set up. However, we do this slightly differently.
Image Credit: Monitoring Slow Queries – RareMile Blog
Instead of waiting for a slow query to cause the performance to degrade noticeably, we take proactive measures to find the slow queries and fix the problems. For example, we set up a process where every week, a database administrator will run a check to identify slow queries and immediately send recommendations to fix it. This ensures that the database performance is maintained and no issues have arisen.
Step #3: Reduce Response Times and Improve Expertise
A database is the heart of a business and must be treated with the utmost care. Database optimization includes solving issues as soon as possible and one major influencer for this is the response time. The ability of an expert DBA to quickly respond to a crisis has always helped to reduce the loss of data, money, and time.
Let’s talk about our experience helping organizations with their database optimization. There are times when our clients find themselves in a problematic situation. Some of the application pages take a long time to load because of slow queries and they are counting on DBAs like us to solve it as quickly as possible. At a time like this, our senior DBAs respond quickly to the situation and use the appropriate monitoring tools to identify the problem and come up with a solution. Compared to the industry standard of 30 minutes to 60 minutes response time, we have refined our process to bring the response time to only 6 minutes assuring the clients 24/7 support.
Improving expertise is critical to stay updated with technology. As a database administrator, there are a few areas where expertise boosts the result in performance:
- Database Security – The security of data is paramount to businesses. It not only has to protect it from external threats but internal ones too. As databases evolve, they provide a lot more functionality in terms of data access, authentication, and authorization mechanisms. Having expertise ensures that better-informed recommendations can be made.
- Capacity Planning – Smooth running databases have capacity planning done well behind them. Experienced DBAs will have a better understanding of the seasonal load on servers and anticipate the type of scaling required to keep the database at optimal efficiency.
- ETL – Extraction, Transformation, and Load ( commonly known as ETL) is an area of expertise that helps to refine some areas of the database. It involves working directly on data with an intent to either move it to another place or ensure that it conforms to the latest standards. We also implement these techniques to better represent the data which in turn improves performance by improving data access speed.
Step #4: Sharing Personalized Reports Transparently
Based on the goals set earlier, the type of reports that will be generated will differ from one optimization process to another. Personalization of reports implies that the information it contains will be the most important set of recommendations and metrics that helps the business gauge the progress of the optimization process.
Image Credit: Report Generation from DB Analyzer tools
As DBAs we use many tools to monitor the various aspects of a database. Some databases like the Oracle DB provide a dashboard while others like MySQL have a host of third-party tools that helps us to track and generate various reports. The idea here is to extract the essential aspects from each of these tools and compile a single report that will give an overall perspective of the database’s health and performance.
Transparency helps the business team to monitor the progress easily . At OptimizDBA, we personalize the reports by communicating the key metrics and then sharing the reports with them on a daily or weekly basis. For example, we use the monitoring systems set up as a part of database optimization to generate specific reports such as “daily backup status”, “database uptime”, and more. The generated reports will then be sent to the monitoring team regularly.
Start Personalizing Database Optimization
Databases are extremely valuable to organizations and optimization is a necessary action to keep it functioning at an optimal level. Personalization helps with keeping costs down, efficiency up, and everyone happy. Transparency allows the business team to see what is being worked on and to easily track progress.
Expertise will develop over time but it doesn’t mean that it shouldn’t be pursued. Expertise helps DBAs to solve problems quicker and recommend the best approaches. We have completed over nine thousand optimization routines over the years and personalization processes have helped us reach the desired goals in a streamlined manner. We recommend adopting personalization methods we have discussed here to ensure high database performance and rapid resolution times.
