Running SQL Queries with Tools in dbWatch Control Center

If you find yourself developing any kinds of applications focused more towards the web or even mobile applications, it is inevitable that sometimes you might find yourself running a couple of SQL queries – you need to interact with your database systems anyway, don’t you? 

1-Sep-28-2021-07-53-02-71-AM

Don’t fret – all database users run SQL queries. It’s important to run them in a proper medium though – while most, say, MySQL database administrators, find themselves running their queries inside of phpMyAdmin, some database administrators might find themselves searching for different alternatives.

Indeed, there a quite a couple – if you find yourself deeply immersed inside of the database management world, you might have already noticed that you can run your SQL queries together with some database management tools. Most database management tools that allow you to run SQL queries inside of your database instances also allow you to do a couple of different things – for example, run a couple of database jobs that let you observe the full power of your database instances or let you see the amount of logical reads (writes) going on in your database instance: 

Today we are going to be focusing on another one for this blog – we are going to be telling you why it’s important to get assistance while running SQL queries inside of your database instance together with tools like the one provided by dbWatch. 

Running SQL Queries with dbWatch Control Center 

You see, while dbWatch isn’t a SQL client, it does have an ability to help you craft your SQL queries – simply observe the left-hand side of dbWatch and choose the fourth option from the top: 

Once you’re done, make sure dbWatch is actually connected to the database instance you want to run queries on – click on the Reload Auths button: 

Once the authentication is reloaded, you should be able to see the username next to your database instance: 

Tick the checkbox next to your database instance and you should be more than ready to run queries inside of a given database instance (do note that if you do not, dbWatch will provide you with an error): 

Now it’s time to write your queries that interact with your database instance – for that, dbWatch will provide you with a very nice user interface and underneath – the ability to see the results of a given database query – input your query inside of the SQL field, and click “Execute” if you want to run it – if not, you can also clear all of the input, output (also save them.)

While your query is running, you will be able to see the amount of time your database query is running – if you don’t see any results, but keep seeing that the query continues executing (see example below), you might want to add a couple of partitions or indexes on top of your table – that should fix the issue: 

If your query ran successfully, you will be able to observe the output (in this case, our column is called “column”, so we needed to escape it with backticks (note the `column` inside of the query)): 

See underneath the SQL window displayed by dbWatch – you will be able to observe what queries failed to run and what queries ran successfully: 

See the “!” in the triangle-shaped box? Those queries failed. See the tick next to the last query in the list? That query ran successfully! 

The ability to simply run queries is not everything that dbWatch comes with though – as already noted, you will also be able to “load input” to run (e.g. import SQL files that contain SQL queries for dbWatch to run), clear input (the SQL queries you just ran) or output (the results of the SQL queries), if you want, you will be able to save them as well. For example, click “Clear Input” and you will be able to see that your SQL queries disappeared:

See? Easy as that! No more deletion – who needs that? dbWatch will delete all of the things for you. 

Output Panels 

In case you didn’t know that, dbWatch will also provide you with one output panel per every result set returned. Here’s how that looks like: 

Summary 

It doesn’t matter whether you are a high-end developer that deals with databases on-and-off or a full-fledged DBA of Oracle, MySQL, MariaDB, or even Sybase – you will need to be running queries focused at a specific database instance sooner or later. dbWatch comes with a SQL query feature – underneath the query you will be able to observe the results of it. You will also be able to see how many queries of yours have run successfully and how many failed – do so and your database instances should be headed towards a more performant future! 

If you find yourself running into any kinds of issues when using the tool, keep in mind that dbWatch has a support team consisting of top-notch database administrators – they can solve all of the problems you face within no time, so don’t be afraid to reach out and you will receive the help you need very quickly. We hope you enjoyed reading this blog and will stick around – we have a lot more content coming up. 

Job Statuses in dbWatch – the How, Why and When

Monitoring the status of database instances is the daily task of every database administrator or even a developer. Database monitoring these days can be incredibly complex and time-consuming if performed manually: thankfully, there are tools like dbWatch that can help you in time of need. If you are a user of dbWatch, you have noticed that it has a thing called “job statuses”: this blog post should provide some insight into it. 

businessman hand using tablet computer and server room background

 

What are Job Statuses? 

 

Job statuses in dbWatch, simply put, depicts how did a certain job in dbWatch execute – was it successful? Did it produce a warning? An alarm? There are three categories of job statuses in dbWatch and here is how everything works: 

 

  • A job status of “success” means that the job ran successfully and can provide some interesting details on your database availability, capacity or performance if instructed to do so (simply right click the job and click Details to observe them in all their glory); 
  • A job status of “warning” means that the job encountered some errors on the way (it might also mean that your database farms or instances are not up to par and can perform better – check the Details section as instructed above to learn more) 
  • A job status of “alarm” means that something is (probably) terribly wrong – seeing such a job status means that something very bad is happening in your database farms and (or) database instances and needs immediate further attention. If left unresolved, this job can negatively impact your database availability, capacity, or performance. 

 

Statuses and Your Database Instances

In order to check the status of your database jobs with dbWatch, you might first want to check what database instances you are running. To do that, click on the Database Farm icon (that’s the third icon from the top on the left-hand side), then expand per platform in the inventory overview section:

 

That way you will get a nice animated view of what database instances your servers are running! 

With dbWatch, you can also go back to the main page and observe the statuses of your database instances themselves (statuses are not specific to jobs, they can also be applied to database instances  – keep that in mind!

 

 

 

Once you have ensured that your database instances are running smoothly, it’s time to keep an eye out on your database jobs. Right click your database instance, click configure jobs and you should see an output similar to this one (black checkmarks mean that the job is installed, green checkmarks mean that it’s enabled):

 

 

Once you have installed the jobs you wish to use on your database instance, it’s time to put them to the test! Choose a job you like, afterwards you can configure it if you wish: 

 

Finally, run it by right clicking on it and clicking Run now:

 

Once your job has been run, you will be able to see its status along with a comment on how well it was executed – a green setting icon with a tick mean that the job executed well, an orange setting icon means that the job produces some errors (check the details section to know what they are) and a red setting icon means that the job found something wrong with your instances: 

 

 

Summary

In general, job statuses in dbWatch are like small advisors for your database instance that are always keeping an eye out for its availability, capacity or performance – once either of those do not satisfy you, check the details section of the jobs to see what can be improved. You would want to keep an eye out on the job statuses in dbWatch since it’s really easy to deploy and it enables you to make sure your database instances are always performing in a tip-top shape.

Monitoring MyISAM Performance with dbWatch, a Guide

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.