by Scott Loudon | Sep 15, 2020 | Cloud, database monitoring, virtual storage
A DBA’s Transition Guide for Hosting on the AWS Cloud
So your organization has decided to migrate your traditional on-premises IT infrastructure to the AWS Cloud in the hopes of realizing cost savings, and to cut down on the time it takes to provision and configure services to support new and changing application workloads. Applications can evolve over time to cloud-centric architectures in order to realize cost savings. But what about all the extra administrative tasks and pressures that go along with the additional speed and agility that cloud hosting provides? How do you keep a handle on all the new instances and know when there are server sprawl issues? Or, even better, avoid server sprawl issues in the first place?
Every DBA knows that whenever anything goes wrong it is always the database that is guilty until proven innocent. So how can DBAs adapt to the new challenges of AWS hosting to remain valuable assets to our organizations?
For the purposes of this blog we will focus on database monitoring and management using the AWS CloudWatch service. CloudWatch ingests performance data from a wide range of AWS resources, applications and services, sends alerts when needed, and keeps a 15-day historical record of performance information. You can even configure CloudWatch with alarm actions to automatically take corrective measures in response to certain predefined event types (but that is a blog for another time). As an added bonus, the CloudWatch “free tier” should be sufficient to perform the heavy lifting of issue detection and identification for most application databases.
Monitoring Performance Metrics of Databases Managed with Amazon RDS
As with traditional on-premises databases, CPU utilization and available memory are two sides of the same performance tuning coin for databases in the AWS Cloud.
You can use the CPUUtilization metric in CloudWatch to keep a historical view of CPU usage for databases managed with Amazon Relational Database Service (Amazon RDS). To get a more complete picture of how an RDS database instance is performing, you can combine CPU monitoring with these additional metrics:
- FreeableMemory, which shows the amount of available memory
- SwapUsage, which shows how much data in memory is being paged to disk due to memory shortages
You can also configure CloudWatch to send alerts when thresholds are crossed.
One of the best features of cloud hosting is you are no longer locked into a specific database footprint based on hardware that was purchased. If you start to see a trend of CPU availability consistently running above 80%, or you’re seeing a shortage of free memory, it could be time to take advantage of the cloud’s on-demand scalability and plan to grow your DB instance to increase capacity. Likewise, if you notice that your databases are consistently showing a large amount of free memory and CPU, then think about scaling down the database instance class to save money.
Storage Monitoring and Auto Scaling To Avoid Server Sprawl
In the AWS cloud, there is never a good reason for running out of available storage on a production database, or any database for that matter. For example, you can use the CloudWatch FreeStorageSpace metric to measure the amount of storage space available to a database instance and trigger alerts as needed. Amazon RDS hosted databases also support storage auto scaling on all major RDS database offerings. This option automatically increases the storage by 5 GB or 10% of currently allocated storage, whichever is higher.
The amount of input/output operations per second (IOPS) for a given database is derived from the storage type you are using together with the amount of storage allocated. It is important to know what IOPS numbers your current storage supports, and you can define the CloudWatch metrics ReadIOPS and WriteIOPS to notify you if you are approaching that level to avoid an issue.
You can get additional IOPS by moving to faster storage or growing your storage footprint to a certain degree. If you exhaust those options and are certain that poor application coding is not leading to excessive read/write activity, it may be time to start thinking about moving to the Provisioned IOPS (PIOPS) storage type, which can provide a higher level of guaranteed I/O for an additional cost.
CloudWatch also offers metrics for ReadLatency, WriteLatency, and DiskQueueDepth for you to configure if you want to keep a closer eye on those parameters.
Monitoring Database Connections
The CloudWatch DatabaseConnections parameter lets you monitor the number of active connections to your database and can alert you when the value approaches the max_connections property for the database.
The default value for max_connections is derived from the total memory and is database-specific, so it is important to check the setting for each database. You can also modify the default value of this parameter if required.
As you can see, CloudWatch simplifies a number of key database monitoring and management tasks. But CloudWatch is just one of several DBA support options you can try on AWS Cloud. You can also subscribe to Amazon RDS events to be notified about changes to a database instance, leverage the Performance Insights dashboard to help analyze database issues, and more.
If your company is thinking of migrating your databases to a cloud or managed hosting provider, Buda Consulting can help you choose the best option for your workloads, and can act as your “first line of defense” when problems like server sprawl arise. We also offer “personalized” managed database environments for Oracle, SQL Server and MySQL workloads.
Contact us to schedule a free consultation today.
For more information:
by Robert Buda | Dec 19, 2013 | Oracle, Oracle In-memory Database
One of the major announcements Oracle made during the Open World was the launch of Oracle In Memory Database Option. The In-Memory option to Oracle Database 12c is 100 percent compatible with existing applications and leverages all existing Oracle Database functionality. At first look, some key benefits jump out:
- Though this is a paid option, it promises a high ROI
- It takes just one change in parameter to turn it on
- From published information, it increases query performance on identical hardware by up to 100x (though it does require a lot of memory)
- It also provisions for a 2x to 4x increase in insert and update performance (after removing unnecessary indexes)
- It is backward-compatible to all existing applications and does not require any changes to applications
Beyond the speed and performance increases, the game-changing advantage I see is that the in-memory database stores data both in row orientation (as oracle always has) and in column orientation—similar to column-oriented databases like Vertica. Why is this important?
This can be a game changer because as we all know, some of the most significant tasks in architecting and maintaining databases involve indexes. Database architects and administrators spend quite a bit of effort in determining what indexes are necessary and what type they should be (considering there are numerous types). And once they plan this out, just the creation of the indexes is a huge task, after which there is also ongoing maintenance. And in many cases, we all are constantly created new indexes that were not foreseen in the design requirements earlier.
With the in-memory option, the only indexes that are necessary are those that enforce referential integrity—those involved in primary and foreign key relationships. Additionally, systems that have a large number of indexes now may see a significant reduction of disk requirements as the need for those indexes is eliminated.
Other vendor’s offerings, such as SAP HANA, are taking a similar approach, implementing in-memory storage and processing, and columnar oriented data management. However, Oracle’s offering is special because nothing, including hardware and applications, has to change in order to take advantage of this new technology and the significant performance benefits that it yields. This could help change the landscape completely—especially for organizations that deal with massive, distributed data warehouses on a global scale.
Have you tried out the in-memory option on Oracle 12c? What has been your experience? We’d be delighted to share your experience with our readers.
by Robert Buda | Nov 30, 2011 | MySQL
The Impact of Query Rewrite on MySQL Query Optimization
When processing a query, every relational database vendor takes a number of similar steps to parse, validate, and optimize a query. MySQL does the following:
- Validates the query syntax — does it use valid operators, are the clauses in the proper order, etc.
- Validates the query semantics — are the the objects (tables, views, columns) valid database objects in the database, etc.
- Transforms the query — rewrites the query so the optimizer can more easily choose the best path.
- Optimizes the query — determines the best execution path based on a large number of factors including statistics about the table and index data.
When analyzing a database performance problem, it helps to have an understanding of what is happening during step 3 and 4 above.
Occasionally I find a blog article that does a great job of explaining one these processes for a particular database vendor and I would like to share one that I found today. This excellent post was written by Guilhem Bichot and does a great job of illustrating the transformation (query rewrite) step and the impact of this step on the execution path selection.
This post describes the process in MySQL but the principles are similar for any database vendor.
Note that the example that Guilhem uses shows how the database itself may introduce a query hint while rewriting the query. I have mentioned in the past that I avoid hints because over time the usefulness and necessity of the hint may change and it may indeed hurt you. Of course, since the query rewrite process is executed in real time it is not subject to this problem.
by Robert Buda | Jun 21, 2011 | Database, SQL Server
In an earlier blog post about database hints, I gave a number of reasons why I dont like to use hints in SQL queries. Today I wanted to follow up to share one situation where I think hints are OK, even necessary.
As described very well in an article in an article by Andrew Kelly in SQL Server Magazine (InstantDoc 129916), hints are sometimes necessary when we wish to minimize logging during bulk loading operations. In particular, when issuing a bulk insert, using the TABLOCK hint is necessary in order to reduce logging to the minimum amount even when recovery mode is set to simple.
I still feel strongly that we should avoid the use of optimizer hints in select statements that are embedded in applications for the reasons that I mention in my original post, but Andrew’s article describes one situation where I think they are acceptable.
If you have comments about hints that you would like to share, please reply.
by Robert Buda | Feb 3, 2011 | Best Practices, Oracle
As we evaluate our clients’ Oracle databases and help them solve problems relating to performance and stability, we see many causes for these issues.
However, there are a small number of basic issues that tend to cause problems on the majority of systems.
Our Tech Tips document describes these issues and discusses methods of identifying them and resolving them.
Topics include:
- Statistics Gathering
- Sga Sizing
- Redo Log Sizing
- Index Usage
- Extent Management
