Month: February 2021

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