Category: SQL

Blogs about performance monitoring, tuning and analytics on all SQL servers

Posted on

What is Horizontal and Vertical Database Scalability?

Buildings represent Horizontal and Vertical Database Scalability

The total amount of data in the universe is always growing. A lot of it ends up in our databases. DBAs need database scalability for database instances to cater to both the growth in data volumes and the increased traffic and workload.

 

This strains our database engines as we push ever-increasing demands on indexes, searches, references, updates, reliability and availability.

Planning Database Scalability

As data and workload grow, the number of server instances in use by businesses grows accordingly, and the importance of stable and reliable IT systems increase, it’s no longer possible for businesses to cope with rigid, non-scalable systems and tools.

 

Making your databases and applications scalable is not necessarily a simple task. There are two main variations of server scalability to consider: vertical and horizontal scaling.

Working With Vertical Scaling

Vertical scaling, or “scaling up”, involves adding more resources to a server instance. By increasing CPU resources, memory, and storage or network bandwidth, the performance of every individual node can be improved, scaling small servers to handle large databases.

 

This is the “old school” way of scaling up.  You either procure the largest machine you can afford or give your existing server as many resources as you can to fit the workload demand.

Buildings represent Horizontal and Vertical Database Scalability

Advantages of Vertical Scaling

Vertical scaling is typically easy for application developers as they do not have to do much to support a larger machine. Same for DBAs – usually quite easy to manage. But at some point, you may reach the capacity ceiling for the hardware you have, and you will need to spend an ever-increasing amount of time optimizing the application, SQL, and server instance to wring out every last bit of performance. This will bring you ever-diminishing returns as you get closer and closer to the maximum size you can take a single server. At some point, the cost and effort to scale vertically hit the ceiling, where you no longer can scale up.

Disadvantages of Vertical Scaling

Vertically scaled systems do come with some disadvantages, though. Not only can initial hardware costs be high due to the need for high-end hardware and virtualization, but upgrades can be both expensive and limited – there is, after all, only so much you can add to one machine before it is still outgrown by your database. Normally clustering like Always On or RAC is applied to these large servers to make them reliable and with enough capacity to handle the load.

 

You may also find yourself quickly ‘locked-in’ to a particular database vendor by following this strategy, and moving away from that vendor later could mean very expensive server upgrades.

 

In summary, vertical scaling is much easier to establish and administer – as it is just a small number of machines, or even just one. Vertical systems can also offer advantages in terms of stability, reliability and development, and cost savings through being suitable for smaller data centers, and license costs might be lower.

Using Horizontal Scaling

Horizontal Scaling, or “scaling out,” is adding capacity by adding more database server instances. This is done by spreading out databases on more machines to answer the call for increased demand and capacity.

 

This is now often the preferred way of scaling databases and is often referred to as the “cloud native” way of doing things.

 

When more capacity is needed in a system, DBAs can add more machines to keep up. In database terms, this means that data must be partitioned across all the machines that make up the cluster, with each individual server holding one part of the database.

 

Usually, applications need to be written specifically for horizontal scaling. But once built, it becomes very easy to scale the application to meet any workload.

 

Unless you are certain that your application will fit comfortably on a single server forever, you should write it so it can be horizontally scaled. Even then, consider making it horizontally scalable to make it future-proof and cost-effective to deploy in a cloud.

 

Horizontally scaled servers can also make use of data replication, whereby one machine holds a primary copy of the entire database while the multiple copies and/or caching are used for read-only load sharing. You often see this in large cloud-based solutions, where data can be distributed across geographic regions, and data centers by use of database read replicas. Consequently, it provides improved response time, better performance, and higher availability.

Advantages of Horizontal Scaling

Horizontal scaling has several key advantages over a vertical approach. The main advantage is that once built, horizontally scalable systems can scale to almost any size and take full advantage of elastic cloud computing – adding more servers to handle peak load. Not only is establishing a scalable system easier, with individual nodes being cheaper than in a vertical set-up but upgrades are also made quicker and more affordable. Maintenance can be easier, too, with faulty instances quickly switched out with minimal disruption to the rest of the system.

 

A high number of instances adds complexity to a system. It makes monitoring, administering, and troubleshooting more difficult and time-consuming during disaster recovery. When machines are licensed individually, licensing fees would also be higher. Not to mention, the physical space needs to house multiple servers will also bring cost and logistical issues.

A Summary of Scaling

As database requirements continue to grow, your organization will need to adopt a form of scalability to keep up. While horizontal scaling is widely considered to be modern, flexible, and advantageous, it can bring some unwanted challenges that you will eventually need to manage.

 

Whether you scale vertically or horizontally, you will always need to calculate the optimum hardware and software combination, especially from a cost perspective. You may want to use many small and cheap 4-core blade servers, or one or a few fast and large 24-core or larger servers. Even more important, check the software license cost for the total system based on the number of nodes and cores. Do the math; it is a non-trivial and important cost decision.

 

Vertical scalability brings complexity, while horizontal scalability brings logistical challenges. In either case, good tools for monitoring, analysis and administration are necessary to monitor performance, reduce the challenges, help you deliver greater productivity, and keep costs in check.

 

Whichever approach you choose will depend on your business’ requirements. However, regardless of what you opt for now, always keep in mind as your business continues to grow, you will eventually need to keep up with ever-expanding databases.

Posted on

Properly Matching Character Sets and Collations in MySQL

Have you ever come across a scenario where your data looks good until you import it into MySQL? The data you’ve imported into MySQL is suddenly displayed as “???”, letting you wonder what’s wrong in your database configuration. The reason of this problem is a character set or collation mismatch – when worked with properly, character sets and collations can make our work with MySQL a dream, or a nightmare. 

 3-Jan-21-2022-05-24-07-82-PM

 

Collations and character sets have their place – they let MySQL decide where from we acquired data and how best to work with it internally; since we can choose from a whole sea of charsets and collations within the RDBMS, properly matching them for our use case is crucial. 

 

What are Character Sets and Collations? 

Simply put, character sets define what characters are considered to be “legal” in a certain string, while collations are a set of rules that tell MySQL how to sort characters within the string. That’s why when we input, for example, data from a Chinese, Taiwanese, or Russian source without fully preparing for it, we’ll see “????” instead of our character string – our database won’t be prepared. 

Character sets and collations aren’t unique to MySQL, though – they are relevant to every database management system we find ourselves using, including Oracle, Sybase, TimescaleDB, or, you’ve guessed it, any flavor of MySQL as well. A proper match of character sets and collations is crucial if we want to work with data from “exotic” (not english) sources. Some of the character sets and collations considered “default” in MySQL are as follows: 

 

  • The default character set is latin1 with a collation of latin1_swedish_ci. This character set is a fit if we want to work with Swedish characters (MySQL is built by Swedish people), or Russian characters as well. 
  • If we find ourselves using Chinese characters, the collation that would be a fit would be big5_chinese_ci with a charset of big5. 
  • For general use cases, we should use a character set of utf8mb4 with a collation of utf8mb4_general_ci (or utf8mb4_unicode_ci.) The reason behind this is that utf8mb4 is considered the “true” form of utf8 since it can support 4 bytes in a character while utf8 only supports 3. For more “corner” use cases, there’s the utf8mb4_unicode_ci variation,  
  • Should we need Hebrew characters, we should use a Hebrew charset with a collation of hebrew_general_ci. 

You get the point – there are multiple character sets and collations we are able to choose to employ for a concrete use case. For a complete list, connect to MySQL and issue a SHOW CHARACTER SET query. Some of the results derived by that query are shown below: 

 

 

MySQL will even provide you with a description of the character set and collation, allowing you to make easier decisions. 

Properly Matching Character Sets and Collations 

As you can tell by now, there are numerous character sets and collations we are able to choose from. Properly matching them is a skill every database administrator gets hold of with experience and with the help of MySQL (see above) – there inevitably will be cases where we mismatch character sets and collations, and get to know of it after the fact, but that’s not a very big issue – and it allows us to learn as well. 

Some of you might know that some character sets consume more space on the disk than others do and once we have a lot of data that could become an issue, but here’s a lifehack: to overcome such an issue, we can issue a SHOW CHARACTER SET query and look at the “Maxlen” column (see above), or if we are dealing with UTF8, closely observe a part of the collation that is in use – e.g. “utf8mb4” will use 4 bytes instead of 3, etc. 

With all these character sets and collations, properly matching them all might be a challenge if we are not educated on how databases work – even with the query given above, MySQL doesn’t tell us all that much if we’re not aware of the basics of charsets and collations. Don’t worry much, though – the description, though very short and to the point, should become a good starting point. There’s no need to get too in depth on this one – look at the language descriptors after the charset and decide from there. Before deciding, though, you should use when to use different character sets and how to choose. 

 

When to Use Different Character Sets? 

People mostly use different character sets (i.e. not the default ones provided by MySQL) when they’re working with data from sources that MySQL would consider “exotic” (i.e. not English or Swedish of nature.) The closest definition on when you should switch to a different character set is “whenever you feel like your data belongs to an “exotic” country. Think of the countries people from Europe are vacationing in: Turkey, Dubai, Spain, Portugal, etc. There are countries that are different beasts in and of themselves, amongst them is China, Taiwan, Japan, South Korea, Russia and a couple of others (we mean those countries that talk in completely different alphabets than the one we are used to.) If you are not sure on when to make the switch and to what character set, turn to the documentation for some more explanation, but the explanation above should put you closer towards your goal. 

 

 

Going Beyond Character Sets and Collations 

Once you’ve properly matched character sets and collations, there a couple more things you need to be aware of for your database to perform at the very best of its capability – you need to keep a very close eye on your database performance and availability as well. Not even talking about security – everyone knows that after COVID, the number of data breaches have continued to rise and rise and no one wants their database infrastructure to become the next target. 

 

For that, consider employing database monitoring inside of your database infrastructure – database monitoring tools such as the ones provided by dbWatch or database vendors themselves will let you see how your database is doing from availability, performance, or security points of view all the time. The problem is, though, that database management software solutions provided by database vendors (think MariaDB, Percona, TimescaleDB, Redis, and the like), are generally expensive and you’re paying more for the name than software capabilities. With dbWatch, though, everything’s otherwise – it’s a relatively small company, but the software provided by the company is used by known companies such as H&M, tieto evry, ATEA, and the like (you can see more companies that are using dbWatch here), and they couldn’t be happier, so we’re confident that dbWatch won’t let your company down either. Make sure to give dbWatch a shot today by grabbing the 30-day free trial available on the website, and until next time! 

Posted on

Alternative Storage Engines in MySQL

If you’ve been a frequent user of MySQL now or in the past, you know for a fact that there are multiple storage engines MySQL allows us to choose from. The most frequent one is InnoDB which is known as a “high performance” storage engine meaning that if optimized properly via the parameters provided by MySQL in my.cnf, the storage engine can do wonders – MyISAM is considered more obsolete though, but it has its own use cases including providing an exact count of the rows in a specific table and storing database files in a recoverable format since the files provided by MyISAM are not bound to any other files such as ibdata1 provided by InnoDB. 
2-Dec-09-2021-08-00-27-92-AM

 

What Alternative Storage Engines are Provided by MySQL? 

 

InnoDB is the default storage engine provided by MySQL – that’s a given. However, there are a bunch of other storage engines provided by the RDBMS, including the following: 

  • Percona XtraDB – this storage engine is only available on Percona’s infrastructure (i.e. only for people using Percona Server), but it’s essentially meant to be a fit for those who want InnoDB capabilities on their infrastructure. Not only that, it’s considered an advanced version of InnoDB as it’s designed for scale on any hardware, and it has a couple of other feats designed for high performance and capability. It’s also fully backwards compatible with InnoDB, so no worries if you want to switch back towards InnoDB after you’ve tried the storage engine and disliked it. 
  • MyISAM – this storage engine was the default storage engine until MySQL 5 rolled around so it’s old as a piece of bread, and most database administrators advise against using it. In fact, the newest version of MySQL considers this storage engine to be obsolete as it’s not reliable, and also prone to crashes due to how it’s built. However, it has a couple of upsides – those include the fact that all files relevant to MyISAM are stored in .MYD (.MYData) files alongside with .MYI (.MYIndex) files and they are not dependant on any one file to work, unlike InnoDB is dependant on ibdata1. The storage engine is also renowned for its capability to actually store row count of tables inside of its own metadata (that’s why InnoDB doesn’t show the row count and MyISAM does), but that’s really the extent of the usefulness of this storage engine. Users of MyISAM will face frequent crashes, data corruption, and other issues, which means that this storage engine is not a fit for those who are looking for performance capabilities neither now, or anytime in the future. It’s useful if we want to play around with it though! 
  • MEMORY – as the name suggests, this storage engine stores all of its data inside of the memory. The storage engine is designed to be a special-purpose storage engine as its all of its contents stored in memory, unlike other storage engines. Such an approach has a glaring flaw – if our server is shut down, all of the data is gone, but on the other hand, the storage engine is very suitable as a temporary working realm or as a read-only cache for data. 
  • CSV – this storage engine is known for its capability to store data in .CSV files together with comma-separated values. There’s not much that can be said here: .csv files usually consist of data values that are separated by a comma (“,”), and that’s the primary reason the storage engine is called that way. Such a storage engine isn’t used very frequently, but it can be useful for some corner-cases of project development, for example, for bulk data imports, or simply to make the job easier for an accountant working with Excel – that way files can be imported straight into MySQL without any additional hassle.
  • ARCHIVE – this storage engine, as the name already suggests, is suitable for archival operations. The primary use case of this storage engine is to store large amounts of data that isn’t accessed frequently – for such a use case, this storage engine is golden because it’s footprint on the disk is almost non-existent allowing DBAs and server admins to focus on other tasks rather than scaling up the server simply to support an archive of data.
  • FEDERATED – this storage engine allows us to access data in a remote database without using replication or clustering in the fashion that if we query a local table with a FEDERATED storage engine, MySQL automatically pulls data from other tables running the same engine. As easy as that! One thing to note, though, is that the FEDERATED storage engine would not usually be enabled by default, but it can be enabled by running MySQL after using the –federated option. 
  • BLACKHOLE – if there’s a storage engine in MySQL that makes you question the existence of the universe, it’s BLACKHOLE. Literally – the storage engine is called after a black hole in the universe in the fashion that it accepts data, but never stores it inside of itself. You might question yourself and wonder what it’s use case is, but MySQL explains it in a simple fashion that probably doesn’t warrant further explanation at all – such a storage engine should be used performance bottlenecks in MySQL that are not related to storage engines, or used for running triggers on data that shouldn’t be kept. 
  • EXAMPLE – and last but not least, there’s an example in the MySQL infrastructure letting you get a grasp of how storage engines are built in the first place. That’s the job of the EXAMPLE storage engine – this storage engine doesn’t store data (or, better put, it doesn’t have any functionality at all), but rather, it’s intended to become an example on how to build or improve on the functionality of storage engines yourself. If you want an example that depicts how to build storage engines in the MySQL source code, look no further! Glance at the MySQL documentation and start building one yourself. 

Exploring MySQL Beyond Storage Engines 

Should you want to explore MySQL beyond it’s storage engine capabilities, be advised that it’s a dangerous world out there in the sense that there’s quite a bit of information that might not be true – to get up to date information, consider reading up on blogs such as the one provided by MariaDB, Percona, and other database vendors.

Of course, the dbWatch blog is a very good place to start – it will provide you information on various kinds of database management systems aside from MySQL, and provide you information on how to best solve the issues related to any database management system you elect to use. Make sure to keep an eye on the dbWatch blog, grab a free trial of dbWatch to see how your databases fare against issues targeting them free for 30 days, and until next time! 

Posted on

Working with MySQL vs. MariaDB – What’s Different?

Everyone has heard of MySQL. Well, probably not everyone – say, everyone in the development and database monitoring space. Do you ever wonder why? MySQL is everywhere. The industries that use MySQL as their primary database management system include the automobile industry (think Volvo, BMW, etc.), the fashion industry (think SheIn and the like), the finance industry uses it heavily, it’s prevalent in the education industry.

pexels-manuel-geissinger-325229 (1)

 

Do we even need to talk about software developers? In the software industry, MySQL is used even more heavily. We mean, think about it. Have you ever heard of anybody in the industry saying “I know of a software developer with experience who hasn’t heard of MySQL”? Chances are, you did not. And chances are, most developers would laugh at that statement – MySQL is one of the most widely available database management systems.

Most developers more familiar with MySQL probably know of MySQL flavors as well. Well, we’re talking Percona Server and MariaDB – you know the drill. In many cases, those are similar, but they have their own differences as well. In this blog, we are going to tell you how one of those (MariaDB in particular) differs from MySQL. 

 

 

What’s MariaDB and MySQL? 

Believe it or not, this question is one of the popular ones that hiring managers ask junior database administrators. Why? Because that’s a good way to determine if a beginner knows a little of their craft. Most novices think that both MariaDB and MySQL must be the one and the same – and the reason behind that is that they think that MySQL and MariaDB are built to solve the same issue, so the queries that might be run are the same, the way the databases are configured is pretty much the same, it’s just a question of the name. Ask a junior DBA such a question and you will hear a “the same DBMS, just with a different name” without much consideration. That’s not the ideal answer, though. In reality, MariaDB is a flavor of MySQL. A flavor that comes with many of its own variations of the features available in MySQL. 

That’s not to say that MySQL is a bad DBMS though – far from that – it’s just that MariaDB has a set of features not available in MySQL, and vice versa. 

 

MySQL vs. MariaDB 

Here are some of the key differences between both of the database management systems so you could better understand how database farm management might work with those: 

 

 

As you can see, the two might not seem too far apart – at least not until you look deeper into them. Many database administrators electing to use MariaDB instead of its counterpart do it because MariaDB comes with a load balancer unique to its own, though that’s not the core reason MariaDB is a competitor of MySQL – the main reason is the fact that MariaDB comes with more storage engines to choose from. 

Think about it: 

 – Is it possible to work with petabytes of data in MariaDB? Yes, but not in MySQL – one should use ColumnStore. 

  •  – Is it possible to have a crash-safe MyISAM that’s known for unreliability? Yes, but not in MySQL – one could use Aria. 
  •  – Want a read-only storage engine that would store all of its data inside of S3 storage in MySQL? Mission impossible. Turn to MariaDB and use S3 instead. 
  •  – Want an actively-developed version of FEDERATED? Turn to MariaDB and use FederatedX, an option not available in MySQL. 
  •  

Is it Worth Using MySQL? 

With all of these unique storage engines available in MariaDB, a natural question arises – should one still use MySQL for daily ops? Shouldn’t MariaDB be the option that is always used, given that it should have more use cases? Isn’t database monitoring more easy on MariaDB? No? 

Well, don’t jump to conclusions too quickly. There are a couple of things MySQL doesn’t have that MariaDB does, indeed, but there are a couple of things exclusive to MySQL as well. For example, have you heard of the Enterprise edition of MySQL? It’s not cheap, but it comes with a nice amout of features including the ability to deliver “hot”, online backups of your databases, Enterprise data masking features to protect your organizations protect your most sensitive data by hiding sensitive data when it’s required, but perhaps two of the most widely known features in this space are the firewall and the audit plugin. The MySQL Enterprise Firewall protects your MySQL installations from a wide range of cyber security threats including SQL injection. By using the firewall, you can create a “whitelist” (a list of allowed values) of SQL statements and block the rest, monitor the database for unauthorized activity, etc.: that’s one feature MariaDB doesn’t offer, and users who use MariaDB miss out on this subject. 

 

What to Choose? 

If you want to weigh your options before choosing, we have good news – it’s not that hard of a choice! If you’re a novice just learning about MySQL, go with MySQL, then transition into MariaDB when you know a little more about the DBMS. If you’re looking to work with big data sets or more advanced features, go with MariaDB, however, if you’re looking for high-level security measures (we’re talking enterprise level here) you’re better off using MySQL. 

Neither of those choices are “wrong” though – you can easily switch to MariaDB and vice versa if you get bored or if the feature list doesn’t satisfy you. Moreover, all of the monitoring tools that are available on the market, including dbWatch, let you monitor both database management systems – so you never lose out after choosing one of them.

As far as monitoring goes, though, we have good news – monitoring those two database management systems almost never differs. Seriously – one can improve performance in MySQL in the exact same way that one improves performance in MariaDB, and vice versa. The reason tools like dbWatch exist is plain and simple, though – it saves you both time and money that you will need when monitoring databases manually. Look at these database jobs: do you think you would have time to keep an eye out on each and every one of them? Not even talking about tens of database instances here.  

 

 

Tools like dbWatch help you schedule these kinds of database jobs and make sure they always run at the exact time specified – not only that, dbWatch will run your jobs in a proper way, always making sure that they never miss deadlines and do the work that they are supposed to do.

Do you have the time to take care of everything yourself? Chances are, you do not – try dbWatch for free and tell us what you think. We are sure – you will be impressed. Give dbWatch a spin and we will see you in the next one! 

 

——————-

Other blogs:

How is The Memory of Your SQL Server Doing?

Top Tips for Oracle Database Monitoring

Databases for Senior Developers

 

Posted on

Track your SQLServer database compatibility level

Two people work to improve their sql compatibility level

 

In this blog, I will discuss SQL Servers’ database compatibility level. By using dbWatch, we will check the proper setting and configure the right compatibility level based on the version you are using.  

 

Each new version of SQL Server introduces new functionalities and performance benefits. These are identified by compatibility level and cardinality estimators.  

1-Dec-05-2020-11-37-39-59-PM

As you can see each database version is associated with a compatibility level, so if you want to utilize the newest features available for your database, you need to check and set the right compatibility level for your instance.  

 

As DBA, you must ensure the compatibility level of each database is matched with your instance current SQLServer version. 

 

The usual problems of enterprises are some take too long to migrate to a newer version of SQLServer. 

 

Some DBAs end up taking two steps. For example –they first migrate the SQL Server 2000 database to SQL Server 2008 R2, and then migrate it to SQL Server 2019. This is the reason NOT to wait too long before migrating a SQL Server database to its latest version.  

 

Sooner or later, your compatibility level will have to change, and this will impact your applications and processes. By doing it sooner, you will reduce the amount of work you will have to do later.

In this view, you can manually check for each databases compatibility level, by opening the database properties window in your SQL Server management studio.

But on a database farm level, especially, when managing hundreds up to thousands of databases, this is not the ideal way to investigate for the compatibility level of your databases.

 

dbWatch Control Center provides you the overview of your database’s compatibility level and displays other attributes of the SQLServer database they are using.  

 

Let us start by clicking on the Farm Management Icon, which is at the left side of your Control Center monitor as seen in the image below. 

 

Expand Inventory Overview Performance. 


Click on the Per platform option and choose MS SQL Server. 

 

In this view displays different attributes, you have the instance status, name, database version, edition, port #, instance name, host, host IP, OS type, the creation date of your database and total DB count.  

 With this information you will have a quick understanding of the different versions of SQL server that is running within your database farm. 

 

 On top, choose the compatibility tab. 

  On the topmost part of this view, you can see the servers and databases compatibility level.  

 

It has 2 groups: 

  • Databases with lower compatibility than the server 
  • Databases with the same compatibility as the server

 As you can see, there are 2 databases that use lower compatibility level than the servers’ version, and 60 databases with the same compatibility level as the server. 

 

On the right side, you have a pie chart that displays the different compatibility levels configured on your databases. We have 2 databases with SQL Server 2014 (120) compatibility level, 50 databases with SQL Server 2016 (130) compatibility level and 10 databases with SQL Server 2019 (150). 

 

At the bottom of the view, displays the instance name, database, status, create date, recovery model, database compatibility, server compatibility, server version. 

 With this information, you will be able to analyze which databases you need to reconfigure and update their assigned compatibility level based on the servers’ compatibility level.  

 

In the image below, you can see a database farm with a total of 903 SQL Server databases with version SQLServer 2008 R2. This feature is very handy when managing these huge numbers of databases, as it helps you keep track of your databases compatibility level when it comes to the latest available version. 

The change in compatibility level tells the instance to change its feature sets, it might add new features or remove some old features so be wary of the changes that might affect your existing application.   

 

The compatibility report view enables DBAs to find where they have the potential for performance improvements by upgrading the compatibility level of a database which might be a version behind.  

  

 

For more info visit the website www.dbWatch.com   

For more tutorials visit wiki.dbWatch.com/ControlCenter  

Follow us on Facebook, LinkedIn, and Twitter - dbWatch   

Also check out our YouTube channel dbWatch  

 

Posted on

5 Tips for Server Migration Planning

Server migration planning

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.

Server migration planning

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

1. Re-hosting by Moving to a New Host– (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 of Re-hosting:

  • 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

Disadvantages of Re-hosting:

  • 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. Re-platform with New Database Platform or Infrastructure

Re-platform 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 of Re-platforming

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

Disadvantages of Re-platforming

  • 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. Re-target to Another Platform

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 of Re-targeting

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

Disadvantages of Re-targeting

  • It may consume time migrating to a new database platform as you need to map out every process happening within the database.
  • You will need to learn new tools and tech if you are not already familiar with a target platform

4. Refactor

Restructuring the enterprise architecture (databases and applications)

Advantages of Refactoring

  • 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 of Refactoring

  • 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 of Retiring

  • 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 of Retiring

  • 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

Summary for Your Server Migration Planning

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

  • Re-hosting
  • Re-platform
  • Re-target
  • 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.

Track your database health, try dbWatch Control Center today.

Posted on

Monitoring the Performance of Database Indexes with dbWatch

monitor database performance

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.  

monitor database performance

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: 

Database growth rates as seen in dbWatch.

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 rate as seen in dbWatch

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: 

monitoring performance menu in dbWatch

Configure menu in 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: 

The dbWatch configure menu.

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 configure key buffer

 

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.

 

 

Posted on

Deploying Databases Locally vs. In a Live Environment

Database working on deployment live.

All developers and database administrators (DBAs) have been in a situation where they need to test database deployment before going live. There’s no way around it. Want to be live? Test your environment in a sandbox before moving onto publicly visible spaces (e.g. the web, etc.)

 

But have you ever wondered what DBAs need to consider before deploying their database environments both locally and live? We will tell you all about that in this blog post. 

Database working on deployment live.

Local Database Deployment

Neither developers, nor higher-tier DBAs have many worries when deploying databases locally. Perhaps they worry about configuration, but not much else. With local deployment you already know all of the parameters of your system, so the only thing that you need to do is allocate them appropriately. Literally – that‘s it! If you’re running MySQL, just open up my.cnf, set your parameters to use 60-80% of RAM, exit WAMP, start it again, and that’s it. Not that hard! 

 

To deploy a database, you simply use phpMyAdmin or log in through the CLI and run a couple of CREATE DATABASE queries (advanced DBAs might even have scripts that accomplish all of these tasks for them.) Select your collation, database storage engine might be set up in my.cnf (by default, that’s InnoDB or XtraDB, depending on the database version you elect to use), and that’s it – you’re up to a good start.

 

Unfortunately, everything gets more difficult in a live environment. The reasons of that happening are numerous – live environments usually need more care and nourishment than local ones, before adjusting your settings you must carefully consider all aspects both of your server, and before you know it, customers start pouring in – what do you do now? Adjust the functionality of the server then? No – that wouldn’t work. 

Deploying Databases in a Live Environment 

Developers and database administrators deploying databases (or software in general) in a live environment need to consider the following things: 

 

  1. How big is the customer base of our present project? 5 customers are not too much to worry about in regards to downtime – 5,000,000 however, is a big deal. In that case, 5 minutes of downtime could cost your customers thousands. Are your databases prepared for that? 
  2. What parts of our project are used the most frequently? Before deploying a database, perhaps we can deploy it in a server that’s used less frequently than others? If one server is always under 88% of load, the other – under 6%, it might be time to have a closer look at the second one. Why is that the case? Perhaps we can deploy our databases on that server to not cause hassle and load issues for other servers? 
  3. Do we need to deploy one additional database? Seriously, this question is overlooked so frequently these days – developers just deploy databases here or there. Before deploying one, ask yourself: why does our team deploy a database? Is it going to be normalized? If yes, were the rest of the databases not normalized? Why? What’s the use of one additional database? Is it crucial for our project? Will it not be a burden for the underlying infrastructure? Will it help us adhere to certain regulatory requirements (e.g. GDPR and the like?) 

Consider those questions carefully. Weigh your choices on three principles: 

  1. Who? Who is deploying the database? Who is it directed at? 
  2.  What? What’s the function of the database? 
  3. Why? Why the database is being deployed? 

The answers to these three questions will determine whether deploying a database is a good option for your use case. If the answers to all of those questions are, in your opinion, solid, deploy a database. Before deploying one though, investigate your configuration files and adjust the parameters so that they use the specifics provided by your operating system to the maximum. Look at your RAM, how much space you have in your hard drives, look at what your CPU can handle, and decide what parameters are you going to set and to what values – if you have a hard time deciding, though, automating database processes can help. 

Automating Processes in Deployment 

“Decide what parameters are you going to set and to what values” – as if it were that easy. 1, 2, 3, done? No. This step requires careful, very careful consideration. One step in the wrong direction and your database is in ruins. Thankfully, there are tools that can assist you in this realm: one of them is dbWatch. 

 

With dbWatch Control Center, your databases are in safe hands, because dbWatch can support pretty much all database flavors that are out there: MySQL, MariaDB, PostgreSQL, Oracle, Sybase, even SQL Server. Once you know what database flavor you’re going to use, think of the database tasks that you’re automating: dbWatch comes with a bunch of database jobs (tasks) that can be scheduled and ran at a specific time, but those database jobs that can be ran depend on the specific database management system that you find yourself using: if you’re using MySQL, they aren’t going to be very extensive simply because of the fact that MySQL is in active beta right now, but import a Microsoft SQL Server instance into dbWatch and you will be able to choose from tens of database jobs in the performance space alone ranging from performance to security to replication.  

 

Here’s an example of how MySQL jobs would look like so you can imagine what would happen in the space of other DBMSes: 

Choose what job you want to run, schedule it by right-clicking (click Set Schedule), and your job will be automated: 

Once your job is automated, the work doesn’t end there – you can stop here, but we don’t recommend you to, because there are other places of dbWatch you will need to keep an eye on if you want to put your database instances towards the highway of performance by using database farm management. One of those is the Database Farm available within dbWatch (that’s the third icon from the top.) The database farm will let you gain an overview of the inventory that your databases are using (what platforms, versions, etc.), and also let you keep a close eye on your database jobs (both those that you have automated, and those that are not yet set up.) This page will let you get an overview with jobs with a status of “OK”, “Warning”, and an “Alarm”.

 

The jobs that you will need to keep a close eye on when you’re deploying in a live environment will be those having the statuses of a “Warning” and an “Alarm” simply because a jobs of that status imply that something is either wrong or seriously wrong in your database instance. Inspect those jobs once they turn orange (meaning a warning) or red (an alarm) – that means something goes off the rails and needs immediate attention. 

With dbWatch database monitoring, everything is simple though – you don’t need another client to run queries to fix problems here or there – click the fourth icon from the top (that’s the SQL client) and use the client existing inside of dbWatch – this SQL client will help you run queries to fix problems while switching back to the monitor will provide you with information whether the queries you ran fixed the issue or not. While the SQL client is not perfect, it can help you execute your queries both as SQL and PL, load files to be ran (think of loading SQL files with a bunch of statements, etc.) – do keep in mind that this feature can be useful both in live environments and for testing, since these features in a testing environment will help you test whether you configured settings correctly, etc.: 

dbWatch Control Center is a great fit whether you’re deploying databases locally or in a live environment – as already noted before, the main features of the dbWatch Control Center will help you run database-related jobs, while the SQL client will let you run queries to fix any issues you encounter on the way.

 

Most clients see an impact in their organization withing the first week of using the tool. Remember whether you’re deploying database instances in a local or in a live environment: use the guidance outlined in the documentation.

 

Discover the impact dbWatch can make on your organization, download a trial version of dbWatch that monitors up to 5 instances for 6 months.  

Posted on

Fix and Optimize Database Performance Degradation

database management tool
database management tool

Online transactions are the new normal. From banks, retailers and food industry, Information Systems have adapted with the times to comply with the customer’s demand.

 

However, not everything is fine the state of ‘new normal.’ The increase in demand, could case degradation in your database performance. Most common of this occurrence is slow running database that is visibly seen on top of applications. 

What is database slowness?

Let’s discuss database performance first. Database performance is the speed at which your database responds to your request of accessing its stored data. To visualize it, a simple SELECT statement, when executed, pulls resources from your database, and returns a tabulated data for display.  This seems fine in the first thousand records but when you are querying tens of thousands of rows or more, a reduced performance can often be noticed. 

Database slowness is that stunted performance. The main difference between that simple SELECT statement and database slowness is that the latter is a chronic ailment of your databases. 

Slowness can be attributed to multiple factors such as: 

  • network problems
  • missing indexes
  • improper caching
  • un-optimized queries
  • database architecture

Reduced database performance is heavily contributed by your database’s existing structure and as DBAs, it is our primary responsibility to mitigate these effects.

How you can Increase Database Speed

Monitor your network and memory

  • Other nodes
  • Estimate disk space and cache memory

Constantly check which applications are consuming a lot of resources. Be wary of cache memory and disk spaces as they can deplete with continuous reading and writing to your databases. Monitor your network connections to avoid any unplanned downtimes. See to it that the bandwidth allocated is sufficient and latency for and between database servers are kept at a minimum. 

Coordinate with your network team, developers, and other related departments to determine what is the root cause of the problem especially if it is database performance related. Fixing slowness won’t happen overnight. But with gradual steps taken, the system will be tuned to its peak performance. 

Review your database architecture and structure

Poorly defined and maintained databases will cause retrieval latency and performance issues. Missing indexes or unnecessary table locks are not only the contributors for undesirable database performance. Database architecture also plays a significant role. 

You must review your overall architecture if it follows correct normalization. Duplicate data, and missing primary keys are the common problems of fledgling database systems. These are easy to shelve for backlogs. Yet, by constantly pushing them below your priority list, the cracks in your database architecture begins to show. Sooner or later, addressing those cracks would be labor-intensive as DBAs will have to work longer hours and costly because you need to plan when and how much time you will be dedicating in fixing them. 

Take for example re-indexing tables containing millions of master records. The best approach would be to schedule it on a weekend with low transactions done with the database. Then, formulate a strategy for the execution and revert, and disseminate the scheduled hours for maintenance to all stakeholders. Most importantly, secure DBAs to work during that time frame. Essentially, procrastination negatively impacts business when bad quality persists. 

  1. Does the design make sense?
  2. What is the probability of data duplicate data per row? Is it low? Is it high?
  3. When I query, will I need to go to another schema just to retrieve that other table?
  4. Are there redundant data that will be stored in every field?
  5. Are the data type properly defined? Are there composite data types I should be wary about?
  6. Are the primary and foreign keys properly defined? Will they lead to a conflict of inputting data?
  7. Are chosen indexing approach sufficient to optimize my searches?
  8. Should I use VIEWS as a substitute for this table?
  9. How will we handle orphan data?
  10. Will we automatically flush or clean cache memory once data rows are deleted?
Based on your answers, you get a feel on what your ideal database architecture should be versus the reality of it. Although this is under the authority of DevOps and Developers, as DBAs, it is also our responsibility to manage databases.

Query Plan. Plan. Plan

Whether you are a senior DBA or junior DBA, always remember to value your query execution plans. Utilize your EXPLAIN statements, and execution plan tabs. Do this also with Oracle, Postgres or any other platforms.  

Double check your database objects and queries such as: 

1. Stored procedures 

2. Functions
3. Ad hoc queries
4. Connecting application queries

See to it that upon their execution, they do not consume a large portion of your database resources. 

JOIN and SUBQUERY statements will also be headaches to be dealt with. Irresponsible developers will match up tables containing tens of thousands of records with ambiguous or incorrect join keys. Poorly scripted subqueries will often lead to slower returns and result to NULL values even if the conditions are met. If possible, use JOIN statements as they take precedence in the query statement and limit the returned records before applying the WHERE condition. 

DBAs should search for these anomalies while finding ways to optimize them. Fine tuning these queries are no joke. It may take hours just to optimize a query.  

Using a Tool to make Database Management Effective and Efficient

With all these talk about optimization, management and monitoring, DBAs have a lot in their hands. It will be a challenge to focus on other tasks especially if your company is suffering from reduced database performance . However, you don’t have to do these tasks manually. 

Database Management and Monitoring solution such as dbWatch will assist you in tracking down resource heavy queries. dbWatch Control Center will monitor your database’s memory and storage spaces such as disk space, cache memory and temporary tables. These are supervised by jobs that deliver the information right at your dashboard. Boxed in Red are the jobs checking for memory usage. 

Screen shot shows some ways to Ways to fix and optimize Database Performance Degradation

Database and server connections are also constantly checked by dbWatch Control Center. The monitoring dashboard lists all possible issues and provide keen statistics of databases when a problem occurs. The screenshot below in red highlights the issues encountered when the job is scheduled to run. Dominantly, no backup plans were established for the instance listed which categorized them as recurring issues. 

Monitoring Your Instances Constantly

Green box shows the statistics of instances. There are 5 categories with differing colors. Red indicates a major problem has occurred within that instance. Yellow shows that the DBA’s intervention maybe needed to properly address that instance’s issue. Green highlights instances showing no signs of problems. Blue displays instance that are down while Gray points out added instances without monitoring jobs installed. 

How dbWatch lists all possible issues and provides statistics

DBA in a Single Window

 

On the database management side, you can perform your database administration task in a single window. Upon accessing the management module, look at the left side pane of the screen. Here, you will see a tree-like structure (see Red Box) where you can do the following: 

1. Configure your databases security

2. Alter indexes

3. Set up backups and restore them

4. Configure your SQL agent

You don’t need to access your database management studio to accomplish these tasks. All you need is your dbWatch Control Center monitor. 

Overview of dbWatch showing statistics.

How’s that for convenient? An all in one tool that provides solutions to your database problems. 

 

Understand more about using dbWatch for database management, book a demo today.

Posted on

Monitoring SQL Server error logs

Screen giving an idea of Monitoring sql server error logs

As a data platform expert who manages and maintains hundreds or maybe thousands of database instances, you may be thinking of a better way in managing these number of servers more efficiently. An environment this huge with a lot of vital applications relying on those databases can be quite difficult to keep track of. Especially when you’re too busy with a lot of administration work, with little resources and time you might have difficulties prioritizing your daily tasks.

 

As your day goes on you will encounter the usual errors which happens from time to time. It may be caused by different reasons for example; a user tried to input his/her credentials, but he/she failed logging in as the credential had already expired, maybe there was a job that failed due to a query timeout, or maybe there was a backup job which failed due to an unknown reason.

Screen giving an idea of Monitoring sql server error logs

The errors mentioned are automatically logged on your error logs in SQL Server. You can view all these by expanding SQL Agent on your management studio, there you will see all the logs of your database instance.

 

When monitoring tens or maybe hundreds of database instances, you need to be able to track and efficiently monitor errors happening in each instance, within the database farm you are managing.

How to Easily Track SQL Instances

You may think that there is no easy way to manage and have full control of everything that is happening within each of your instances, luckily there is dbWatch which will assist you with your daily monitoring tasks.

 

dbWatch contains various tasks which helps you manage and monitor error logs of your database instances. dbWatch helps you keep track of all errors encountered on your database farm. You can also monitor error log file sizes and check if your file size is growing too much or too fast within a period. And lastly, you can configure when you wish to recycle error logs within your database instance, which helps free up disk space being consumed by the number of growing logs. 

How to Track Error Logs Within dbWatch

  • You’ll need to start by going to the monitoring module.
  • Here you’ll select a sql server instance. Right click and select configure monitoring. (See Figure 1, below)
Selecting SQL server, screen shot
Figure 1

In this window make sure that you have installed the tasks for monitoring error logs in SQL Server. (See figure 2, below)

The following tasks are:

  • Instance error log
  • Instance error log file size check
  • Cycle error log

showing how to install the tasks for monitoring error logs in SQL Server

After installing the tasks, go back to the monitoring module.

On the column Name, input error on the filter to find the tasks regarding the error logs. (Figure 3, below)

how to input error on the filter to find the tasks regarding the error logs.
Figure 3

If you right click on instance error log, you have an option to run the task to retrieve the updated information of the collected error logs from the instance. (Figure 4, below)

How to retrieve the updated information of the collected error logs
Figure 4

Click on Details, to view the actual errors collected from this SQL Server instance. (Figure 5, below)

How to view the actual errors
Figure 5

A new window will appear, showing you details of the errors which occurred in your sql server instance.

For example, the last lines read from error log table displays all lines read, from the error log file by this task during its last execution.

 

The error history details table, shows the history of the last 20 collected records from your SQL Server error log file (error-text defined by the “error text” parameter). (Figure 6, below)

 

Later in the blog, you’ll learn how to defining the error text parameter to filter out errors that you’d like excluded.

Figure 6

SQL Server Error History

The error history graph shows the number of errors per day being registered in the log, helping you determine if the errors you are encountering are growing daily. (Figure 7)

SQL server error history graph.
Figure 7

You don’t want the monitoring task to consume to much resources, and if it takes a long time to read the error log you should investigate it (even there are no errors in the log file). If the number of records read is very high, it could indicate potential problems in your instance. (See figures 8 and 9, below.)

Showing a high readings in the SQL error log graph.
Figure 8
Another example of high error log readings in SQL Server
Figure 9

And lastly, the alert execution aggregated statistics graph. The left vertical axis shows the total rows read per day, and the right vertical axis shows the total time spent reading the SQL Server error log per day. This helps you determine if you are experiencing an increase in error on your instance. With the information available, you can deep dive on the instance where the number of errors is growing.

 

Going back to the monitoring view, click on Configure. (Figure 10, below)

Image shows how to return to monitoring view
Figure 10

A configuration window will appear for this Instance error log task. (Figure 11, below)

The Confirmation window in dbWatch.
Figure 11

You can modify the configuration based on your requirements.

The options are:

  • Error text – defines which string (errors) the check must look for. Values must be separated with commas (,).
  • Error text allowed – Specifies the text strings (specified by the “error text” parameter) to be excluded when found in the same record (error log line).
  • History threshold – the maximum numbers of error messages the history table will retain (error_log_err_histr_tab table).
  • Last run – the last time the error log has been checked.
  • Max elapsed time – the maximum execution time (in seconds) for the task. If this threshold is reached a warning is raised.
  • Max lines per execution – the maximum number of lines the task will read during execution before a warning/alarm is raised.
  • Return status – return status value (ALARM – 2, WARNING – 1, or OK – 0) when “error text” is found (or “max lines per execution” is reached).
  • Click apply to save the configuration.

You can set the schedule on when you wish this task to execute. (Figures 12 and 13, below)

How it looks when you schedule a task in dbWatch
Figure 12
The Pop-up window when scheduling a task in dbWatch.
Figure 13

Right click on the Instance error log file size check and choose Details.

Note: In order to install and make this task (Instance error log file size check) work, you need to enable xp_cmdshell in SQL Server by executing the following command below, (Figure 14) or you can set it on the configuration parameter. (Shown later.)

 EXEC sp_configure ‘xp_cmdshell’, ‘1’; 

 Reconfigure

 Go

Instance error log file size check
Figure 14

A new window will appear showing you the information in your error log directory. It shows the error log file name, create date and file size. (Figure 15, below)

How the error log directory looks in dbWatch.
Figure 15

The error log file size history graph shows you information of your historical error log size growth rate. (Figure 16)

The error log file size history in dbWatch.
Figure 16

The error log directory size history graph. Displays the number of files within your log directory, and the total file size for the accumulated logs. With the information available it will help you with your decision making and consider clearing some error log files, which are no longer needed as it consumes additional disk space within your environment. (Figure 17, below)

Error log directory size history as seen in dbWatch.
Figure 17

Go back to the monitoring view, and right click again on the Instance error log file size check. Choose Configure. (Figure 18)

How to check on Instance error log file size check.
Figure 18

Again, you can modify the following configuration based on your requirements

The options are:

  • Enable xp_cmdshell – if set to “YES” the sql server instance configuration xp_cmdshell will be set to enabled. This parameter is required if you want to allow the task to execute operating system commands, to discover the size of files and directories.
  • Error log directory size threshold – the maximum total size (in MB) of all files in the error log-file directory.
  • Error log file path – path where the error log file is located.
  • Error log size threshold – maximum size (in MB) of error log file before a warning or an alarm is returned by the alert.
  • File count threshold – the maximum number of files in the error log catalog.
  • History threshold – the maximum numbers of history statistics (in days) of error log file size.
  • Return status – return status value (ALARM – 2, WARNING – 1, or OK – 0) when the “* threshold” parameters are reached.
  • Click apply to save the configuration.
Showing how to check cycle error log.
Figure 19

Choose Cycle error log. Right click then select Details

A new window will appear which displays the specific log file and the date when it was recycled. This information gives you an idea if the error log was cleared on a specific date. (Figure 20)

The window shown here displays the specific log file.
Figure 20

Go back to the monitoring view. Right click and choose Configure.

A configuration window will appear which you can modify. (Figure 21)

The options are:

  • Cycle History – number of days to provide information when the error log and agent error log has been recycled.
  • Number of SQL Server error logs – by default, there are 7 SQL Server error logs – Errorlog and Errorlog 1 to 6. This parameter can be used to modify the registry value (REG_DWORD type) to the number of logs which you wish to maintain.
  • Click apply to save the configuration.
A configuration window to modify.
Figure 21

Summary of Monitoring SQL Server Error Logs

Now you have an idea on how you can easily track the various errors happening within your database farm. You can set the return status so that you will be notified once a certain baseline, or error has been identified by dbWatch.

 

You can also configure your own parameters or baseline based on your business requirements. It is a great way of customizing different alerts based on the errors retrieved by the dbWatch task. For more information visit: wiki.dbWatch.com 

 

Monitor your database error logs, try dbWatch Control Center today. 

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.

 database-nedir-840x400

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
  • Durability

 

Here’s how InnoDB ensures that the ACID parameters are being followed:

  • Can ensure that statements in a transaction operate as an indivisible unit, and their effects are either seen collectively or not seen at all.
  • Has logging mechanisms that record all of the changes to the database.
  • Provides row-level locking.
  • 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 thewhen 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.

 

To account for the InnoDB buffer pool value properly, keep in mind that this value can be set to up to 80% of free memory on Linux (on Windows machines, it’s a little bit less). The more memory you allow for InnoDB to use in this scenario, the more performant it will be.

 

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. It’s important to push your InnoDB instances to the next level and help ensure they stay highly performant and reliable.

 

Keep your database instances run smoothly, try dbWatch Control Center today. 

Posted on

How is increased complexity harming your DB performance?

Dealing with a wide variety of databases, platforms and versions is a reality for many companies. While it would be nice to be able to have one platform to handle all tasks necessary within an organisation, the reality is that different needs require different solutions. As a result, we create (or at least are forced to work with) diverse systems.

A variety of systems can be great for providing solutions which might not be easily available within one database platform. Unfortunately, this diversity also can bring unwanted complexity to the database management process. When a system becomes too complex, it can sometimes feel like you are losing track of the needs and goals of the organisation. While solving one problem, another problem occurs in a different database, which behaves entirely differently than the one you are working on.

Problems start to snowball, and you feel like you are losing your grasp over the multitude of systems.

In order to address this, we need to step back and make a list of the various things that can go wrong.
As many in the therapeutic fields state, “the first step is recognising that you have a problem.”

Okay, you’ve already reached that point (otherwise why would you be reading this?). So let’s get to the second step; at this point we need to figure out and document what the problems are; we can’t get started solving problems unless we know where they exist, right? Only after we understand what the pitfalls are in our database management process can we begin to map out solutions.

 

Complex Environment

We often don’t have the option of having our entire of array of databases running the same platform, or even if we do, they are likely not all running the same system. You may have systems any combination of SQL Server, Oracle, Sybase, PostgreSQL, MySQL or more (not to mention MongoDB or other NoSQL databases).

We could also be hosting different instances on different virtual servers; often in multiple locations. Each of these platforms may have useful tools for tracking down these problems, but they don’t all work for all systems. Simply trying to keep track of everything slows pretty much everything down.

Ineffective Use of DBA Time

Your DBAs may be spending a lot of time fixing a small, but pesky problem, but miss the fact that there’s a larger systemic problem that may be more important in the long run.

For example, maybe there is an annoying problem where an index keeps getting dropped on one particular table. One of your DBAs sees this, and rebuilds it, and moves on to something else. It gets dropped again the next day, and another DBA fixes this. Or maybe it gets dropped in the middle of the night, and nobody knows why; it happens silently, but the system has slowed down and the next day time gets wasted trying to find the cause.

While working on individual problems, it may be missed that there’s a fundamental issue with the database structure itself. Perhaps a larger fix, which may take a little more time, may solve the problem once and for all. While this might be caught by someone paying attention at a specific time, it’s quite easy to miss these, so the same work gets repeated over and over again.

Troubleshooting Time

Often, as is described above, it’s difficult to see what happened. The DBA is staring at the problem without seeing a larger picture of why it occurred, so he or she spends a lot of time trying to track down the root cause. There’s no overview so unless detailed notes are kept (in ways that everyone understands), the same process will likely need to be repeated each time it occurs.

Firefighting Becomes The Main Focus

Instead of being able to perfect a particular database, there’s little time for planning, tuning, and much less redesigning databases as putting out fires as they occur becomes a daily activity. Simply the process of having to take your attention away from a project can slow you down in exponential ways.

Even if a fix is quick, the time and energy spent solving problems (especially if they are complicated ones) can be extremely distracting. Simply getting back into the groove on the original project takes time, and it becomes even worse if something else goes down on a different system, server, or DB instance.

At the end of the day, there is virtually no time to do any preventative maintenance or improve the overall flow of your procedures. Even if you manage to come up with a few good scripts that can handle some of this, one that will work on Oracle will not work on SQL Server, and definitely not on PostgreSQL. The complexity itself becomes the enemy.

Finding Answers

The first step is to remember these two words:

Don’t Panic.

Simply understanding where the problems are can go a long way to helping you find solutions, and we are already partly there. Most of problems associated with complexity tend to stem from each other.

What you need is overview.

One of the main problems in a complex system is not knowing what is happening to all of your systems at once. It is the classic problem of only seeing the trees, but not the entire forest.

If we can find a way to step back and see the entire forest at once, patterns will begin to appear. Ideally it would be great to have the ability to have only the responsibility to pay attention to the larger picture without getting stuck in the mud of individual issues, however as we know, that’s typically not an option.
Also, even then, you’d still need a way of viewing all of these issues at once, rather than poring through logs just to find patterns. See where I’m going here?

The main point is that you need to be able to see what is going on you need is a good tool. Software that can enable you to take a step back. Maybe this isn’t your primary role, but if problems can be spotted before they arise, then half of your problem is solved.

This tool needs to be able to examine multiple systems and platforms at once. Instead of jumping from one tool to another, each of which has a different interface, functionality, and with varying options for customisation; it’s best to have something that can give you a high-level view of all of your systems.

Once we have this, the rest of the problems start to solve themselves. If we can spot problems before they exist, there’s better communication and less need for firefighting.

 

Standard-Logo (17)

Complete database Monitoring and Management solutions

CONTACT INFORMATION

+47 22 33 14 20 (Oslo)

sales@dbwatch.com

dbWatch AS

Nedre Slottsgate 4M
0153 Oslo, Norway

 

 

© 2024 dbWatch AS