May 2021

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 Database 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 Database 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:

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 in MySQL:

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 in MySQL – 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 of Database Performance Monitoring with dbWatch

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 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.

Understand more about database performance monitoring, book a demo today.

Checking the status of database servers is a daily task of nearly every database administrator – checking the status of your database servers is like monitoring the health of your database servers: by utilizing proper monitoring techniques, you can make sure that your databases always perform at the very best of their ability no matter what happens. In this blog, we are going to explain how to do that with dbWatch.

Why Should You Check the Status of Your Database Servers?

As far as the database world is concerned, checking your database servers’ status can prove to be an essential tool in the shed to improve your database performance or ensure that your database performance stays in shape no matter what happens. Checking the status of your database servers allows you to identify the key areas where potential issues with the configuration of your database instances can interfere with their performance, identify slow running queries, misplaced or missing indexes, monitor the growth and capacity of your database servers or decide when it’s time to switch hosting providers and move to a new server. There are quite a few tools that help you check your database servers’ status – we are going to be focusing on one of them. That’s dbWatch.

Why Should You Use dbWatch?

Before we tell you how you should check your database servers’ status with dbWatch, we should probably tell you what dbWatch is. In general, dbWatch is a highly scalable software solution that helps enterprise customers monitor and manage both small and large numbers of database servers efficiently by providing total control over all aspects of their operation, performance, and resource usage. dbWatch is highly effective across many platforms; it doesn’t matter what your databases are based on – it supports almost every platform you can think of, including MSSQL, Oracle, PostgreSQL, Sybase, or MySQL. Since the dbWatch team comprises leading database experts in Norway, the software can help you solve your database management and monitoring issues in no time. Here’s how you should check your database servers’ status using the software.

Checking the Status of Your Database Servers with dbWatch

dbWatch can also help you check the status of your database servers. There are multiple ways to do that

– for example, the simplest one is to open up dbWatch and take a glance at the database status at the monitoring module:

The index page lists the database instances which have lost connection are not monitored, or have other status types. The page lists the number of database instances, their names, their groups, and the status time, which depicts when the databases were last checked for errors. For example, those database instances that did not have any issues at the time they have been checked will be listed under “Ok”:

Database instances that do have issues, on the other hand, will be listed under the “Warning” category:

Similarly, database instances that have significant issues and need immediate looking into will be listed on the alarm category, etc. Issues can be observed at the database instances tab (the orange status gear means that there was a warning, as you can see from the example above):

There is also another way – simply head over to the “Server” tab and click on “Server States”:

In the window that opens up, you will be able to see your server name; you will be able to perform a trace route and access a menu that looks a little something like this:

This menu can be your savior when checking the status of your database servers with dbWatch. You can connect to or disconnect from your database instance in question or do other things. For example, you can configure the connection to the instance (in this case, the server name is blurred out):

It also gives you the ability to configure your connection parameters:

Need to optimize query speed further to try out different things? Did not yet have the time to add or remove an index on a particular table, so you need to do it tomorrow or next week? Take notes!

Need to take a backup of the dbWatch logs to glance at them now or any time in the future? dbWatch has you covered in this area too, select “Get logs” and download the zip file:

Finally, extract the zip file to gain access to the log files and see them:

Then you can observe what went wrong with the server, the output, and the server logs. The error log file logs all of the errors, the server.log file logs everything related to the dbWatch server, you also have an output.log file that might contain some juicy information too. For example, here’s how the server.log file looks like from the inside:

You can easily see the memory information, the virtual machine properties, where the ControlCenter is installed, your user directory, your java runtime version, etc. Not all of this can be helpful – that’s why you also have the error and output logs which can help you observe what possibly went wrong with dbWatch at what stage so you can try and correct the errors,

Summary

Checking the status of database servers is a near-daily task of nearly every database administrator. Checking the health of your database server instances is critical if you want to push them to their limit – dbWatch can be of great assistance when doing that. Keep in mind that dbWatch can not only be used to check your database servers’ status: as already previously mentioned, the tool can be used to solve issues regarding MSSQL, Oracle, PostgreSQL, Sybase, MySQL or Azure SQL database instances. For example, dbWatch can be used to solve problems pertaining to MySQL engines, including InnoDB and MyISAM, you can use dbWatch to monitor the performance of database indexes and other things. dbWatch can also provide you with a set of logs – the logs, depending on what they are (there are three categories: error logs, server logs or output logs) can help you find out what went wrong with dbWatch when executing certain things. Error logs log all errors, server logs log when dbWatch was started, the memory information about the server dbWatch was running on and similar information and the output logs log warnings and similar things related to dbWatch. To read more about dbWatch, consider reading other articles on the dbWatch blog.

Month: May 2021

Monitoring the Performance of Database Indexes with dbWatch