Category: Uncategorized

Posted on

InnoDB: High Performance vs. Reliability with dbWatch

If you are a developer that deals with MySQL or a MySQL database administrator, you probably know what MySQL database engines are. One of the most popular database engines as far as MySQL or MariaDB is concerned is InnoDB. – This storage engine is very widely regarded as a high-performance storage engine that also balances high performance with high reliability.

———————————————————————————————————-

This storage engine replaced MyISAM since generally used in  MySQL 5.5 which was  – MyISAM was released in 2010. This blog post will go through what MySQL can offer in this space and how dbWatch can help monitor performance and reliability issues. 

How does InnoDB Ensure High Performance and Reliability?

If you ask a MySQL database administrator or a developer who deals with databases, how does InnoDB ensure high performance and reliability? You will probably hear the term “ACID” being mentioned. As it deals with databases, the term ACID is an acronym for four words: Atomicity, Consistency, Isolation, and Durability. Here’s how InnoDB ensures that the ACID parameters are being followed: 

– InnoDB can ensure that statements in a transaction operate as an indivisible unit, and their effects are either seen collectively or not seen at all.

– InnoDB has logging mechanisms that record all of the changes to the database.

– InnoDB has row-level locking.

– InnoDB also tracks all of the changes to the system by maintaining a log file.

It is worth noting that not all InnoDB engines are ACID-compliant “out of the box”. – ACID-compliance for InnoDB is controlled by the innodb_flush_log_at_trx_commit variable in my.cnf. This variable has three possible options: zero (0), one (1), and two (2). The default value is 1 – this value makes InnoDB ACID compliant. The other two values, 0 and 2, can be used to achieve faster write speeds, but then InnoDB will no longer be ACID-compliant, and so the engine can lose up to one second’s worth of transactions.


In general, the innodb_flush_log_at_trx_commit parameter controls how to perform fsync operations. – fsync() is a Linux function that transfers (“flushes”) all modified data in such a way that forces a physical write of data from the buffer cache. – iIt also ensures that all of the data up to the time that the when fsync() call was invoked is will be recorded on the disk after a system crash, power outage or any other hiccup.


How can dbWatch Help Ensure InnoDB High Performance and Reliability? 

If you want to ensure that your MySQL InnoDB instances follow high performance and reliability principles, keep an eye on dbWatch. dbWatch has quite a few jobs that are aimed to ensure that the performance of your InnoDB instances will follows the high performance and high-reliability principles. Here’s how that looks like at the time this blog post is written: 

Image 1 - dbWatch Performance jobs

Simply expand the Performance job section and you will see a couple of database-based jobs that can help you monitor the binlog cache, monitor your database load, and your lock statistics. It can show you your memory setup, your query cache hit rate, session load, temporary table status, etc. But we are interested in one job – that’s the InnoDB buffer pool checking job. 

Right-click the job, click Details, and you should see this screen which explains what the job does in detail: 

Image 2 - InnoDB buffer pool hit ratio details

This graph depicts the hit ratio for the InnoDB buffer pool. In order to ensure that your InnoDB instances follow high-performance principles, aim for: 

The hit ratio to be as high as possible – when InnoDB cannot read from the buffer pool, the disk is accessed. Queries hitting the disk are usually slower.

A large InnoDB buffer pool value – the larger it is, the less disk I/O is needed to access data in tables.

To set these parameters up, you might want to make use of the free –h command (this command displays how much RAM is free in your system in a human-readable format) – to make a good decision, evaluate your project needs upfront and account the RAM usage for the applications that will run on your server. 

dbWatch also shows you a graph that depicts the actual usage of the InnoDB buffer pool by your database instances. – it shows the total number of the buffer pool read requests and how many of them accessed the disk: 

Image 3 - the usage of the InnoDB buffer pool

dbWatch also allows you to configure this job easily –right-click and click Configure, and you should see this screen: 

As you can see, dbWatch also lets you configure the hit ratio alarm and warning thresholds., mMeaning that you will be presented with a warning or a notice if the InnoDB buffer pool hit ratio falls below specific values in percent (%). 

Summary 

InnoDB is widely known as a high-performance and high-reliability storage engine for most developers that deal with MySQL and MySQL DBAs. To push your InnoDB instances to the next level and help ensure they stay highly performant and reliable, use the jobs provided by dbWatch  – doing so should help your database instances run smoothly and keep you sleeping soundly at night. If you need any further assistance, feel free to contact the team  – they will be glad to help you out. 

Other Blogs:

Dealing with DataOps in the Modern Age – the dbWatch Approach

Managing your Database Instances with dbWatch

Posted on

Challenges of Scaling MySQL: dbWatch to the Rescue

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.

If you have ever worked with MySQL or if you are a MySQL DBA, you have probably faced the challenge of scaling your MySQL instances. This blog post will outline the challenges of scaling MySQL and tell you how can dbWatch help you to scale, monitor and manage your MySQL (and other) database servers efficiently while also providing you with total control over all aspects of operation, performance and resource usage.

When Would You Need to Scale MySQL?

In general, the challenge of scaling MySQL would frequently appear in a situation when you start seeting your MySQL-backed application’s traffic increase by tens, hundreds or even thousands of times and you start seeing query timeouts more and more often.

What can You Do to Scale MySQL?

As far as the actual scaling of MySQL is concerned, you have a few options you can choose from. You can apply application level optimizations and, of course, you can apply some optimizations on the MySQL level and you can also scale through infrastructure. As we’re talking about the challenges of scaling MySQL in this blog post, we will go through some of the things that you can do to optimize the performance at the MySQL level yourself and tell you how can dbWatch help you to scale your database instances too.

If you want to optimize your database instances at the MySQL level, here’s some basic things you have to keep in mind:

– Your queries should only fetch the data that is absolutely needed meaning that you should avoid using queries like SELECT * if it’s not absolutely needed. A query working with too much data is one of the most basic reasons a query might not perform well – optimize the data your queries access and optimize your queries such that they access less data. The less data your queries access, the faster they will be – do not ask the database for data you don’t need.

– The EXPLAIN clause can be very useful if you want to optimize your query performance – the EXPLAIN clause is able to give you an access type that MySQL is using to find results (the access types range from a full table scan to index scans, range scans, unique index lookups and constants) – understanding the general concepts of scanning a table, scanning an index, range accesses and single value accesses could prove to be very valueable if you want to optimize your database instances at the MySQL level. The type ALL means that MySQL has ran a full table scan while ref means that MySQL used the ref access type on some sort of an index.

– Keep in mind that there are some basic things related to query execution. First, the client sends the SQL statement to the server, then the server checks the query cache. If there’s a hit in the query cache, it returns the stored result from the cache, otherwise the server parses, preprocesses and optimizes the SQL into a query execution plan. Afterwards, the query execution engine executes the plan by making calls to the storage engine API and the server sends the result to the client. Keeping these things in mind could put you on a good path when scaling your MySQL instances.

– If you are using wildcard searches (for example if you use LIKE queries) do not start your search with a wildcard. Only use a wildcard at the end. If a wildcard is used MySQL won’t know what the search term begins with and thus an index might not be used even if it exists on the column you are querying.

The advice above should set you on a good path regarding fixing query timeouts, deadlocks and the like, but if you need to scale MySQL seriously, chances are that you are going to need certain tools suitable for the task.

Solving MySQL Scaling Challenges with dbWatch

If you want to be able to solve your MySQL scaling challenges using tools, dbWatch can help you. dbWatch is a highly scalable software solution that helps enterprises monitor and manage your database server instances efficiently while also providing you with total control over all aspects of operation, performance and resource usage. dbWatch can help you scale all kinds of database management systems – it does matter if you are using MSSQL, Oracle, PostgreSQL, Sybase, MySQL or Azure SQL. Here’s how the user interface of dbWatch looks like:

dbWatch offers a few options to scale your MySQL (or any kind of database management system) instances:

1. You can (and should) make use of the dbWatch jobs that are available – the dbWatch jobs are split accross three categories (Availability, Capacity and Performance) and each of those categories contain certain jobs that perform specific tasks. For example, the Availability category for MySQL consists of database monitoring jobs that check the uptime of your DBMS and give you alerts, the Capacity category provides you with aggregated and detailed database growth rates and the Performance category consists of database jobs that check the effectiveness of the InnoDB buffer pool, the MyISAM key buffer, it can provide you with some database lock statistics, it can analyze the memory setup of your database server, it can also provide you with some information regarding your session load and the query cache hitrate and so on. Keep in mind that jobs can be configured (or even disabled if you so desire) and they also have a details section meaning that you can see some more information. Simply right click on a job and click on “Details”, “Configure” or “Set Schedule”:

For example, here’s the Details section on one of MySQL jobs in all of its beauty:

2. Make use of job scheduling – if you have a lot of database instances (and you can do that with dbWatch), job scheduling can be an invalueable resource. To schedule your jobs with dbWatch, simply click Set Schedule after you right click a job. Then you will be able to set a schedule for the job to run every minute, hour, day of a week or week of a year:

 

3. When you’re using dbWatch, you can also connect to or shutdown all servers at once – that might help with scaling your database instances too.

4. You can also make use of FDL (Farm Data Language) – now this one deserves an entire book alone (take a look into the documentation), but in short, FDL can help you with a multitude of different things ranging from filtering instances by database name to sorting the results of a column by an ascending or descending order. For example if you use use #sort your instances would be sorted in a certain order (use asc for an ascending order and desc for a descending order)

:instance{#sort(asc)}

FDL can be really useful if you want to scale MySQL further and push your MySQL instances to its limits.

Summary

If you’re a MySQL DBA, chances are that sometime in the future you will face issues in relation to scaling MySQL. Your queries might become slower and slower, your traffic might increase more and more etc. – when using dbWatch you can make sure these kinds of problems will be solved quickly, securely and effectively no matter what kind of database instance you use, so be sure to try dbWatch today – if you encounter any kinds of problems, be sure to give the team behind dbWatch a quote – they will be more than glad to help you out and solve your challenges of scaling MySQL or other kinds of database instances in no-time.

Other Blogs:

Automating your database processes with dbWatch The end-user product Architecture of dbWatch – an overview
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
Posted on

Managing Your Database Instances 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.

If you’re a database administrator dealing with a lot of database instances, you have probably noticed just how hard it is to keep track of all of your database instances at once, not even taking into account the fact that you have to keep up with the count of your database instances per platform and similar things. We have discussed database administration issues previously – we have discussed howcan dbWatch assist you in solving the MySQL issues you are facing;we have discussed how you shouldmonitor your InnoDB performance with dbWatch we have covered MyISAM too. This blog post will be a little bit different – now we will talk about how you should go about managing your database instances using dbWatch.
How do I Manage My Database Instances with dbWatch?
To start managing your database instances with dbWatch, first launch dbWatch, then add a database instance you want to monitor. Once you have done that, hover over the Monitor text at the top once again, then click on Management: 
Once you have clicked on Management, you will see a screen. Look to the left, select your database instance (in this example, we have used MySQL 5.7.26, but database management works with any other database management system, too), then select what you would want to glance at. Here are your options: 
You can see that you can look into your database instance configuration, performance, sessions, databases, or security. We will now go through each of those things. 
– The configuration part of the management part of dbWatch can help you see valuable information related to your database instance. It can help you see your database management system version, what server architecture it’s built on, what port it’s running on; it can help you see where the configuration file is located, and on top of other things, it lets you know the uptime of the database instance in question: 

– The management part of dbWatch can also help you see how your database instances perform, meaning that you can easily observe the running queries in your database instance, what database they are running on, etc. You can also see how long your queries are running and their state (running etc.) – that might help you determine whether you need to index your tables, what indexes you need to use etc. – you can also see how much of your CPU is used, what sessions are active or inactive, you can observe the buffer cache hit ratio, etc.: 

– You can also observe the performance reports. This part of dbWatch can tell you what database platforms your database instances use, help you observe what database instances use the most database space, what editions of database instances you use, etc. – you can also see how many hosts, instances, and databases are related to a specific dbWatch server. This list can also be filtered: 

– As far as performance is concerned, when using dbWatch you can also see an overview of the top 20 databases your database instance is running, what contents does your database have, how many logical reads per second your database is currently running, etc.: 
– You can also observe the contents of each of your databases – this part of dbWatch can be especially useful if you don’t want to or don’t have the ability to access, for example, phpMyAdmin to observe the contents of your databases: 
– You can also observe certain types of data (programmability, tables, and views) of a certain database:
– Finally, dbWatch can help you observe your databases’ security status, letting you observe how your database users look like from a security standpoint, how frequently they change passwords, what plugin is used to change their password, what host they have access to, etc. –hover over the Security section: 
– You can also use the management section of dbWatch to run SQL queries on your database instances if you want to. Again, this part of dbWatch can be helpful if you don’t have access to phpMyAdmin or if you don’t have the ability to SSH into your server and you need to run SQL queries: 
Summary
It’s pretty easy to manage your database instances with dbWatch – in that regard, dbWatch can help you if you need to observe the configuration of any of your database instances (e.g., it can be helpful if you need to see the specific directories in which the files related to the database are stored, the versions of your database management systems, etc.), dbWatch can help you observe the performance of your database instances letting you decide if you need to use indexes on any of your tables or not too. Since the security of your database instances isn’t out of the question too, dbWatch can also help you observe what parts of your server the database users can access; you can see what method is used to store passwords, when did the passwords get changed, the account lock status, etc. Suppose you don’t have the ability to SSH into your server or don’t have the ability to access, for example, phpMyAdmin. In that case, dbWatch also provides you with the ability to run SQL queries on any of your database instances.

Other Blogs:

Backing up your data with dbWatch Control Center: A practical guide Database monitoring: Customizing your views using Farm Data Language 
Posted on

Create A Customized Database Performance View In Dbwatch

About the Author:

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

As a database administrator managing many different database instances on various platforms, it can be difficult for you to track various aspects of your performance within your database farm. You need help from specialized tools to monitor your database key performance metrics, a database monitoring solution that offers the functionality for customization and allows you to view the most important metrics you need in a single window.   

Creating a view is not that complicated, like creating and modifying views in SQL Server and Oracle databases. With dbWatch, you can easily create, customize, or modify existing views. You can also incorporate your existing performance scripts and display it on a single view in dbWatch.  

This section will show you how you can create a custom view using dbWatch properties and your existing monitoring script.   

In this example, we will be creating a dbWatch property and sub-properties (using dbWatch EM12), which will be used to create a custom view where we could monitor all sessions running in our SQLServer and Oracle instances.  

  1. Download and update the XML Template you will use and modify depending on the performance counter you would like to create as a view and Monitor.
    (Note: read the REPLACE comments on the XML file)
  2. After modifying the document, save it as an XML file
  3. Go to your dbWatch Monitor. At the top3, choose Configure and click Upload Resource.

4. A new window will appear; choose the xml file we have modified then click Upload. 

   5. After modifying the document, save it as an XML file 

          6. A new window will appear which helps you create a new tab and a view.   

Please specify the name of the view in this example: I will name it as Oslo-Cluster Session Info.  Click OK. 

7. A new window will appear, where you input the FDL(Farm Data Language) query to retrieve the columns from the XML file we’ve modified. (FDL is a powerful language dbWatch has developed to help you query and retrieve data across database platforms. Learn more about FDL by clicking here)  

Earlier, we have configured our select query for both Oracle and SQLServer within the XML file template. We will select all the columns and data from SQLServer and Oracle session tables in the question below.  

FDL Query:  

$instance->i/session_id{“Session ID” #hide}->sid  

/$I/name{“Instance”}  

/$sid/username{“username”}  

/$sid/logon_time{“logon time”}  

/$sid/db_name{“database”}  

/$sid/status{“status”}  

/$sid/host{“host”}  

/$sid/program{“program”} 

    8. At the top, choose File and click Save. 

The new view contains the different columns which we would like to display has been created.   

By following these steps, you can easily create dbWatch properties and customize your performance view based on your existing performance script.  

If you have any questions or would like to know more on how dbWatch can assist you in your current enterprise database monitoring and management situation.  

Please contact us through the email: chadwick@dbwatch.com  

For more information, visit www.dbWatch.com or wiki.dbWatch.com  

Other Blogs:

Backing up your data with dbWatch Control Center: a practical guide on how you can monitor and manage your backups Monitoring MyISAM Performance with dbWatch – a Guide
Posted on

dbWatch Control Center for managing Database Farms

About the Author:

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

Introduction to Database Farms 

Companies use different database management systems to store large amounts of data for processing and analysis; some companies have massive data warehouses (OLAP) for analytics and huge database farms that cater to different Online Transaction Processes (OLTP) in the enterprise. 

In small businesses, they start with a few database servers that a database administrator is managing. The DBA monitors these servers manually or by using his customized scripts as he investigates the servers one by one to check for the health and performance of their databases, to make sure that every server is running efficiently and serving its purpose.   

And as years pass by, the company grows, and from a small group of database servers, it becomes a vast database farm that shares resources to form a cluster. The DBA doesn’t have the spare time to remote into each of the servers manually one by one to check each database’s health status.  

The DBA needs a dedicated solution to support him in his day-to-day work monitoring and managing the whole database farm. His customized scripts for a few servers that he was using back then might not be as effective in tracking or handling the current database farm he manages currently. 

A database farm comprises multiple database servers, instances of various platforms, and versions used in an enterprise to support their day-to-day business operations or functions.    

As a DBA managing a colossal database farm, one of the most important things is to have a complete overview of your database farm.

You should be able to answer questions such as:

– What is the health status of all databases/instances? Which of the databases needs attention right now, and which should I prioritize? – How many disks, memory, and CPU cores been allocated to the database farm?
– What is the resource usage rate for each instance?
– How many instances exist in my database farm?
– What are the database platforms and versions running in the database farm?

In large virtualized environments, resource allocation and utilization is an issue. As a DBA,
you should carefully analyze all the resources allocated within your database farm; you should have a clear overview of how the various resources like your disk, memory, and CPU are utilized in your database farm. You need to determine if your servers have been over or under-provisioned with resources.

Documenting a huge database farm is also a big issue as the business grows your database farm grows. With proper documentation, you have up-to-date information on your databases in the database farm. It makes maintenance easier, helps with management or DBAs decision-making, and diminishes risk. It’s more straightforward to make an assessment using your database documentation as a guide. As a DBA managing a database farm, you don’t have time to create your database farm’s complete documentation manually. It would be best if you had tools to automate the documentation process.

In your database farm, you need to ensure that all your backup jobs are running optimally and running as scheduled. You need to have complete control over your databases’ backup and recovery plan; it is essential if an unexpected situation like corrupted databases, sudden server failure, or other forms of data loss occurs. As a DBA, you will use existing backups to restore lost information to the system. 

Imagine having a database farm with hundreds or up to thousands of instances. As a DBA, do you still have enough time to remote into each database server to know their health status? 

Whether you are a  DBA or a manager, you need a top-level view of what’s happening in your database farm and an option to drill down into the specific instance when manual intervention is necessary.  

dbWatch Control Center offers, in addition to the traditional database instance monitoring functionality, a feature-rich database farm solution to monitor, manage and control your database farm. 

dbWatch Control Center offers the latest database farm management functionality, enabling DBAs to view all your instances in single views. The image below shows that Control Center displays total instances and databases with their corresponding platform and versions, providing you a top-level overview of your database farm. 

dbWatch Control Center helps users with their capacity and resource planning projects.   

dbWatch Control Center has built-in views that display the capacity overview like your database resource consumption in the database farm.   

This overview provides DBAs and managers information to determine which instances consume the most or least memory resource in the database farm.   

 dbWatch Control Center gives you clear information on which instances have excess resources allocated to them, which you can reallocate to instances that have more need for them. 

This view displays the total disk usage in the database farm for you to determine which instances have the most significant number of disks or volumes allocated in the instance; it also shows the usage rate per volume.  

dbWatch Control Center is a complete database farm monitoring and management solution, which offers automation, consolidated views, and performance reports giving you the key information you need at your fingertips. Automating your routine tasks help you save time and effort on manually managing databases in your farm by focusing on more critical tasks on hand. At the same time, Control Center does all the proactive monitoring of your database farm, allows you to have complete control over your database farm. 

Control Center offers the following: 

– Farm overview – consolidated views for performance, capacity, and maintenance for your entire database farm
– Monitor in-depth all your instances and platforms
– Performance – identify performance bottlenecks within the farm
– Scalability – ability to adapt with your growing database farm
– Alerting – alerting and third-party extensions tailored to your business requirements
– Security – role-based access controls, AD and Kerberos integration encrypts connections and supports security certificates

Summary 

With dbWatch Control Center as your partner in monitoring and managing your database farm helps you achieve the following: 

– Proactive monitoring displays a health overview of all databases within your database farm. Provides you the necessary information for you to take proactive action against possible issues.

– Provides complete information of your database farms’ total allocated hardware and resource utilization like your CPU, Memory, and Disk usage.

– Displays information on all instances in the database farm.

– Auto-discover instances on specified network ranges.

– Delivers information for performance metrics and identify which database platforms and versions are running in your database farm.

Try Control Center today! https://info.dbwatch.com/download-dbwatch-controlcenter 

 

For more information, visit www.dbWatch.com or https://wiki.dbwatch.com/ControlCenter/ 

Other Blogs:

Monitoring MyISAM Performance with dbWatch – a Guide

From SQL Instance Management to Database Farm Management
Posted on

Monitoring MyIsam Performance With Dbwatch – a Guide

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.

Why MyISAM?

MyISAM was the default MySQL storage engine for MySQL versions prior to 5.5 released in 2009. The MyISAM engine is based on older ISAM code. One of the key differences between InnoDB and MyISAM is that MyISAM is significantly faster when using COUNT(*) queries because MyISAM stores the number in the table metadata, InnoDB does not.

What do You Need to Monitor in MyISAM?

Monitoring your MyISAM-based table performance is crucial if you use MyISAM to store data. One of the most crucial things related to this engine is the MyISAM key buffer (also called the key cache). MyISAM employs a cache mechanism to keep the most frequently accessed table blocks in memory to minimize disk I/O.

The key cache (or key buffer) structure contains a number of block buffers where the most-used index blocks are placed. When the MyISAM key cache is in use, multiple sessions can access the cache concurrently. To control the size of the key cache in MyISAM, use the key_buffer_size variable. If this variable is equal to zero, no key cache is used.

How to Monitor the Performance of MyISAM with dbWatch?

Thankfully, if you want to monitor your MyISAM-based tables with dbWatch, doing so is pretty simple. Open up the dbWatch Control Center, navigate to your MySQL instance and expand the performance section as shown. You should see a “Key buffer check” job underneath:

The next thing you should probably do is configure the job. Here are your options:

This task can be configured to:

– Change the buffer utilization alarm threshold – dbWatch will alert you with a status of ALARM if this value exceeds the specified value in percent.

– The buffer utilization warning threshold means that dbWatch will present a warning when the buffer utilization of the MyISAM engine exceeds a value specified, which can be useful if you want to monitor your MyISAM performance or even know when to migrate to a new server.

– The read ratio alarm threshold will give you an alarm if the reads exceed a specified value in percent.

– The read ratio warning threshold will present you with a warning if the reads exceed a specified value in percent.

In general, this job can be beneficial if you want to ensure that you use your server resources with MyISAM engine well. If the values don’t satisfy your desires, you are free to change them.

Summary

To summarize, the MyISAM key buffer cache checking job in the dbWatch Control Center can be very useful if your MySQL data is stored using the MyISAM engine. The job can help you ensure that your server resources are utilized to ensure the MyISAM engine’s best performance.

 

Other Blogs:

Monitoring InnoDB Performance with dbWatch – a Guide Database Locks – how to monitor and manage it
Posted on

Monitoring InnoDB Performance with dbwatch – a Guide

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.

Why InnoDB?

InnoDB is one of the most widely used storage engines in MySQL. This storage engine is known as a highreliability and a high-performance storage engine and some of its advantages include supporting foreign keys and row-level locking. This engine is especially widely known for following the ACID (Atomicity Consistency Isolation Durability) model – in general, ACID ensures that database transactions are processed reliably and warrant data validity despite errors, power outages etc.:

– Atomicity means that statements in a transaction must operate as an indivisible unit meaning that their results must be seen collectively or not seen at all.

– Consistency is handled by MySQL’s logging mechanisms.

– Isolation – row-level locking provided by InnoDB.

– Durability means that InnoDB maintains a log file that tracks all changes.

The explanation above should give you a pretty good idea of how powerful InnoDB really is, but in order to keep the engine running as smooth as butter, you need to understand how is it monitored.

Why Monitor?

Before we actually tell you how you should monitor the performance of your InnoDB engine, we should probably answer the question “why do you need to monitor it in the first place?”. In general, monitoring the performance of all of your engines (not only InnoDB) can come in handy if you want to make sure your database always is in tip-top shape and also to avoid issues that might arise in the future due to mismanagement of data or other things.

What do You Need to Monitor?

When it comes to InnoDB, there’s a couple of things that you need to keep in mind (we won’t list everything here, but this list should give you an idea of just how powerful InnoDB really is):

– The data residing in InnoDB engines is situated around the ibdata1 file which stores all data relevant to InnoDB including the data and indexes of InnoDB tables, table metadata, MVCC data, the doublewrite buffer and the insert buffer.

– One of the most important parameters in InnoDB is the innodb_buffer_pool_size parameter – this parameter acts as a memory buffer that InnoDB uses to cache the data and indexes of its tables. For the best performance, the value of this parameter should consist of 50- 80% of the available RAM on your system (keep in mind that you need space for the processes running in the OS aswell)

– The innodb_log_file_size parameter defines the size of InnoDB log files.

– The innodb_log_buffer_size is used to write to the log files on disk.

As you can probably tell, you should try to monitor at least some of those things because they are absolutely crucial for InnoDB to function properly – now we will look into how you can monitor one of the most important parameters of InnoDB – the innodb_log_buffer_size – in dbWatch.

 

How do You Monitor the InnoDB Engine in dbWatch?

In general, to monitor the InnoDB engine in dbWatch, run the InnoDB buffer pool check job:

This job can be configured with a hit ratio alarm threshold and a hit ratio warning threshold. The hit ratio alarm threshold will give an alarm if the hit ratio falls behind a certain specified percentage that you specify and the hit ratio warning threshold will give a warning if the hit ratio falls below a certain
percentage specified:

This job is very useful when monitoring InnoDB because by using it, you can verify that tables running InnoDB in your MySQL instance are actually doing an effective job and InnoDB is used properly. If the hit ratio falls behind a certain low percentage, it might be time to check your my.cnf configuration. 

Summary
Monitoring InnoDB performance is one of the primary ways to ensure that your MySQL instances stay in the best shape possible. Tools developed by dbWatch can help you ensure that your InnoDB engine is used effectively and help you push InnoDB to its limits. Contact dbWatch now!

Other Blogs:

Monitoring MyISAM Performance with dbWatch – a Guide From SQL Instance Management to Database Farm Management
Posted on

From SQL Instance Management to Database Farm Management

How is database farm management different from instance management? Why do you need it and when? That is what I will try to shed some light on in this blog. If you are responsible for a database farm, read on.

Managing instances – watching and tuning performance, handling incidents, and generally maintaining them has always been the DBA domain. DBAs are focused on the database server performance now. As the number of instances grows, you will need more DBAs to handle the job of keeping all instances ship-shape daily. This is when you need to consider farm management as well.

As an analogy, think of the difference between database farm management and instance management as the difference between managing public transport in a large city with managing a formula one race car team. The former is concerned with moving as many people as possible on buses, trams, and trains in a cost-efficient manner, while the latter is concerned with making one or two cars win the race at almost any cost.

Database Farm Management is different from instance management. If you are to do this efficiently, you will need more comprehensive tools than those usually used by a DBA.

The first task in database farm management is to get the total overview of all the server instances under your responsibility. A complete overview is crucial since you cannot manage what you cannot see or do not know. This may seem trivial, but I have seen too many sites that do not have a complete overview of all their database servers. Sometimes, departments or outside 3rd party solution vendors will install new servers without informing IT, or someone will deploy a new temporary cloud server and forget to decommission it. In most cases, it will come back to haunt you – whether deserved or not. Ensure you have the complete overview. Install tools to auto-scan your networks for new instances and keep a close eye on your cloud services bill for new servers popping up. 

 

So, now you know what database servers you are responsible for, you have the overview. While you are at it, collect as much relevant data and properties as you can, such as platform, version, location, resources and licenses. You will need it for later.

The next step is to monitor status and health.

Are they ok, or do you need to take corrective or preventive action? There are lots of tools to help you with monitoring. Make sure they monitor all your instances on your list, so you are not caught out when somebody complains about some server you somehow forgot to include in your monitoring scheme. So, monitor them all – all the time. It is also a sign of professionalism to show and document to any manager who wants to know what you are doing and control.

The goal of database operations is to have everything available with acceptable performance whenever needed. If you fail to monitor, you can only react to service complaints since you have no forewarning to let you take preventive action.

When you can monitor the whole farm as a whole and see the bigger picture, it should also be easier to know where you should direct your DBA expertise to work with the most impact on overall system performance and health.

Inventory Management

If you have set up this appropriately so far, you should be in a position to quickly produce any report on all your servers, required for internal reporting, budgeting, or audit.

Another use for this is to see what versions you are running and use it for planning upgrade and patch cycles.

Resource Management

One of the critical areas and benefits of database farm management is in optimizing resource utilization.

Your database farm consists of large amounts of expensive and limited resources: memory, disk, CPU cores, and software licenses. These resources represent a large financial investment and cost, and your job is to ensure the farm is utilized optimally. Here are some typical questions you should ask yourself:

– Do I have servers that are not being used, and can be decommissioned and the resources returned to the free pool?
– Do I have underutilized servers that we possibly could consolidate to free resources?
– Do all the instances require and use all the memory that has been allocated?
– Do they need and use all the cores they have been allocated?
– Do I have servers that are starved of CPU or memory, that can better use these resources?
– Do all servers with enterprise licenses need enterprise licenses, or is there scope for reducing licenses and cost?

I have seen examples of sites where they  auto scale/auto configure the memory allocated vs used on 1000+ servers every night. They then automatically reduce or increase memory on each instance to maximize performance by shifting memory to where it is most needed. It sounds like a big job – but it can be done completely automatically. The result was better overall performance and delayed the need for a new VM cluster. Maximizing resource usage in an elegant manner

When you have convinced yourself that you have taken out all the slack resources in your farm, you can start planning for expansion. If you have trend charts for how the whole database farm is growing in resource usage, you have a good starting point for planning and budgeting for growth. When you also can document that there are no more slack or extra resources than necessary, it should be easier to argue for more resources.

View Farm Management video
Database Farm Management resources
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