Month: May 2021

Posted on

The 5 R’s in Server Migration Planning

About the Author: 

Chad Pabalan is a Pre-Sales Engineer for dbWatch and a DBA specializing in SQLServer high availability setups and disaster recovery planning and configurations. He is an AWS Certified Solutions Architect Professional, a cloud enthusiast specializing in architecture and designing scalable, high available, and fault-tolerant systems on AWS Cloud. 

As a DBA, infrastructure admin, or manager, you may have encountered a scenario where you are tasked to lead, plan and execute a server migration project for reasons such as: improving the application performance by using new or upgraded hardware or optimize the enterprise architecture.

Planning for a server migration can be quite complex, especially in large enterprises with many different servers used for various web applications, internal applications, databases, and many more. Today I will discuss some of the issues you need to consider when planning a server migration.

Planning for a server migration can be quite complex, especially in large enterprises with many different servers used for various web applications, internal applications, databases, and many more. Today I will discuss some of the issues you need to consider when planning a server migration.

The 5 R’s to consider when planning a Server Migration are the following:

1. Rehosting – (also known as Lift and Shift)

This approach involves moving your current infrastructure, applications, or databases to another server, another data center, or moving it to the cloud as it is.

Advantages:

– Simple migration by re-hosting all applications and databases as it is

– No optimizations, no architecture changes, no code changes, moving to other servers or in the cloud without doing additional changes

– No optimizations, no architecture changes, no code changes, moving to other servers or in the cloud without doing additional changes

Disadvantages:

– Can cause security risks if users and roles are not applied effectively

– Can cause application failure or services unavailability if processes, security roles, jobs are not synchronized correctly

 2. Replatform

Replatform involves migrating to a new platform or infrastructure, for example, moving your on-premise databases to an Azure Managed instance in the cloud or moving your on-premise web application to AWS ElasticBeanstalk.

Advantages:

– Not changing the core architecture but may require some code changes for optimization

Disadvantages:
– It will most likely require extra time and effort to apply code changes
– Use different tool kits and packages only available to the new platform

3. Retarget

Moving to a different product or database platform (Oracle to MariaDB). An example is you are trying to migrate your Oracle databases to a MariaDB to save substantial licensing costs. The move from one database platform to another requires changes in your stored procedures and packages when moving from Oracle to a MariaDB database.

Advantages

– Migrate the database to a better solution for reasons such as cost-benefit, feature/function availability.

Disadvantages:

– Migrate the database to a better solution for reasons such as cos

– It may consume time migrating to a new database platform as you need to map out every process happening within the database.

t-benefit, feature/function availability.

4. Refactor

Restructuring the enterprise architecture (databases and applications)

Advantages:

– Overhaul the application, driven by the need of business to add new features and functions
– Optimizing the overall application and usage rate of resources by query optimization and rewriting queries
– Long term cost savings as your applications/database are optimally running and properly provisioned.

Disadvantages:

– It may require a lot of time and effort on the part of DBAs/Developers to work on the project to refactor the whole application/database architecture

– May introduce new bugs or issues

– It will require extensive planning and testing before stable and ready

5. Retire

Turn off things that are no longer being used or running may be due to refactoring the whole environment. Consolidate database instances that are infrequently used to other servers that have extra capacity and resources.

Advantages:

– Save costs up to 10 – 30%
– It helps reduce security vulnerabilities
– Fewer servers to maintain, monitor, and manage
– Simplify environment
– Remove old legacy platforms and apps
– Better overview
– Cleanup

Disadvantages

– Hard to verify instances or databases no longer in use without extensive activity logs going back a long time or extensive manual checks
– Moving databases may introduce unexpected side effect

Conclusion

When planning your server migration, always remember the 5 R’s, which are:

– May introduce new bugs or issues

– Replatform

– Retarget

– Refactor

– Retire

 

Before you migrate your servers, you should put a monitoring and management solution in place to keep track of your applications or databases’ health.

dbWatch Control Center allows you to track your database health and performance using different database monitoring jobs for every performance metric.

Control Center has built-in reporting capabilities for the management to have a quick overview of their database farms’ status, capacity, and resource usage.

Try dbWatch Control Center today – https://www.dbwatch.com/download-dbwatch-controlcenter/
For more information, visit https://www.dbwatch.com/get-started-with-dbwatch or https://wiki.dbwatch.com/ControlCenter/
Posted on

Monitoring The Performance Of Database Indexes With Dbwatch

About the Author:

Lukas Vileikis is an ethical hacker and a frequent conference speaker.

Since 2014, Lukas has found and responsibly disclosed security flaws in some of the most visited websites in Lithuania.

He runs one of the biggest & fastest data breach search engines in the world – BreachDirectory.com, frequently speaks at conferences and blogs in multiple places including his blog over at lukasvileikis.com.

In general, most database administrators sometimes face a few common problems. One of those issues is optimizing the performance of queries – whenever query optimization is mentioned, the chances are that you will often see some advice regarding indexes. Today we will try to see why indexes are so essential and dive into how to monitor your database indexes’ performance with dbWatch. 

What are Indexes?

In the database world, indexes are data structures that are frequently used to improve the speed of data retrieval operations. Indexes make data retrieval operations faster because when indexes are in use, databases can quickly locate data without having to scan through every row in a database table 
every time it‘s accessed. The usage of indexes, of course, has both its upsides and downsides – we will start from the good things, then go into the minuses, and finally, we will tell you how to monitor the performance of your database indexes using dbWatch. 

Advantages and Disadvantages of Using Indexes

There are a few main benefits of using database indexes as long as databases are concerned. We will use MySQL as an example. In this relational database management system, among other things, indexes can be used to:
There are a few main benefits of using database indexes as long as databases are concerned. We will use MySQL as an example. In this relational database management system, among other things, indexes can be used to:
– Quickly and efficiently find rows matching a WHERE clause.
– Retrieve rows from other tables in JOIN operations.
– Save disk I/O when values are retrieved straight from the index structure.
However, we mustn’t forget that what has advantages probably has disadvantages too. Here are the disadvantages of using indexes inMySQL
– One of the main drawbacks of using indexes in MySQL is that your data will consume more space than usual.
– Indexes degrade the performance of certain types of queries inMySQL  – INSERT, UPDATE and DELETE queries can be significantly slower on indexed columns. When data is updated, the index needs to be updated together with it.
– You may use redundant indexes in MySQL (e.g., you might index the same column two or three times by adding an ordinary INDEX, a FULLTEXT index, and a PRIMARY KEY or a UNIQUE INDEX, etc.).In this case, it’s helpful to remember that MySQL does not error out when you use multiple types of indexes on the same column, so it never hurts to be careful.
We also must not forget that there also are multiple indexes in MySQL. You can use a PRIMARY KEY (this type of index allows you to use automatically incrementing values), An ordinary INDEX accepts NULL values and is frequently used to speed up SELECT operations (while slowing down INSERT, UPDATE and DELETE queries), a UNIQUE INDEX can be used to remove duplicate rows, you can also use full-text search capabilities while using a FULLTEXT index, or if you want to store rows in a descending format, you can also use a DESCENDING INDEX.

Monitoring the Performance of Indexes Using dbWatch

To monitor the performance of your database indexes using dbWatch, you can utilize a couple of methods outlined below:
– dbWatch allows you to see your database growth rates. For that, dbWatch has two specific jobs letting you see the aggregated and detailed growth rates of your databases regardless of the platform you use. Here’s how the aggregated growth rates look like:
The red line depicts the data size, the orange is for index size and the green one is reserved for the total size.
By observing aggregated growth rates of your database you can easily see the data and index size derived from your database server, letting you decide whether your indexes are starting to be redundant or not.
Here’s how the detailed growth rates look like:
Detailed growth rates show a chart detailing the growth rate for the most extensive databases on the server. Both of the jobs also display dates letting you observe how your database grew over time. 
If your indexes’ size is very small, it might be time to look into a different optimization method. On the other hand, if the size of your indexes is a bit bigger, indexes can become the primary reason your
queries run efficiently. It all depends on the index – indexes are critical for good performance, but people often misunderstand them, so indexing can cause more hassle than it’s worth too. To get the best out of the indexes that are in use in your database management system, you can also utilize the
InnoDB buffer pool checking job or the MyISAM key buffer checking job – these jobs can give you an excellent indication of the buffer pool utilization in InnoDB or the key buffer utilization in MyISAM.
The InnoDB buffer pool check job can be configured to give an alarm or a warning if the buffer utilization exceeds a certain value in percent, allowing you to keep an eye on the buffer pool at all times – since the buffer pool is maintained primarily for caching data and indexes in memory, monitoring its performance can be a crucial aspect of monitoring the performance of your database indexes with dbWatch:
The same can be said about the MyISAM key buffer check job. Once again, this job can be found by simply looking to the dbWatch Control Center’s left side. All that’s left to do is to configure and enable it for it to work:
When configuring the job, keep in mind that there are a couple more parameters that you can use:
– You can choose the number of days you want to keep the data for – after a specified amount of days has passed, data will be discarded.
– The job can give you an alarm or a warning if the buffer utilization exceeds certain specified values in percent.
– The job can give you an alarm or a warning if the read ratio exceeds certain specified values in percent.
The key buffer utilization alarms can be beneficial not only if you want to know whether the indexes you use in MyISAM are effective or not but also if you want to know when to upgrade your database instances or servers that you usually use to run the database instances on (e.g if a buffer utilization threshold constantly exceeds, say, 90% it might be time to look for how you can push your resources further to accommodate the data and indexes that you use).
Summary
Monitoring the indexes’ performance in your database with dbWatch can be a substantial step if you want to ensure that some of your database queries (e.g., search queries) stay fast and efficient. Do keep in mind that indexes usually slow down the performance of certain types of queries too (e.g INSERT and UPDATE queries), but if you have a lot of data, indexes can be handy. When using indexes, keep in mind that there are separate types of them (for example, B-Tree indexes and FULLTEXT indexes, PRIMARY KEYs are also indexes) and that you have multiple types of indexes on the same column at once.
Software developed by dbWatch does have a couple of jobs that can help you monitor the performance of the database indexes that you use – the database growth rate job can help you check the size of your indexes helping you decide whether they’re efficient or not, the InnoDB buffer pool checking job can help you monitor the data and indexes of your InnoDB tables, and the key buffer checking job can help you monitor your MyISAM table performance. As always, if you need any further information, consider having a read through the documentation of the dbWatch ControlCenter and always contact the team if you need any assistance– they will be happy to help.

Other Blogs:

Managing your Database Instances with dbWatch The hidden Gems of dbWatch – Understanding dbWatch internals
Standard-Logo (17)

Complete database Monitoring and Management solutions

CONTACT INFORMATION

+47 22 33 14 20 (Oslo)

+44 020 7692 8971 (UK)

sales@dbwatch.com

dbWatch AS

Nedre Slottsgate 4M
0153 Oslo, Norway

 

 

© 2023 dbWatch AS