Monitoring Amazon RDS: Beyond Raw Logs

by Apr 7, 2017

Categories

Tags

Administration agent-based monitoring Agentless Monitoring alert responses alert thresholds alerting Alerts Amazon Aurora Amazon EC2 Amazon RDS Amazon RDS / Aurora Amazon RDS for SQL Server Amazon Redshift Amazon S3 Amazon Web Services (AWS) Analytics application monitoring Aqua Data Studio automation availability Azure Azure SQL Database azure sql managed instance Azure VM backup Backup and recovery backup and restore backup compression backup status Backup Strategy backups big data Blocking bug fixes business architecture business data objects business intelligence business process modeling business process models capacity planning change management cloud cloud database cloud database monitoring cloud infrastructure cloud migration cloud providers Cloud Readiness Cloud Services cloud storage cloud virtual machine cloud VM clusters code completion collaboration compliance compliance audit compliance audits compliance manager compliance reporting conference configuration connect to database cpu Cross Platform custom counters Custom Views customer survey customer testimonials Dark Theme dashboards data analysis Data Analytics data architect data architecture data breaches Data Collector data governance data lakes data lineage data management data model data modeler data modeling data models data privacy data protection data security data security measures data sources data visualization data warehouse database database administration database administrator database automation database backup database backups database capacity database changes database community database connection database design database developer database developers database development database diversity Database Engine Tuning Advisor database fragmentation database GUI database IDE database indexes database inventory management database locks database management database migration database monitoring database navigation database optimization database performance Database Permissions database platforms database profiling database queries database recovery database replication database restore database schema database security database support database synchronization database tools database transactions database tuning database-as-a-service databases DB Change Manager DB Optimizer DB PowerStudio DB2 DBA DBaaS DBArtisan dBase DBMS DDL Debugging defragmentation Demo diagnostic manager diagnostics dimensional modeling disaster recovery Download drills embedded database Encryption End-user Experience entity-relationship model ER/Studio ER/Studio Data Architect ER/Studio Enterprise Team Edition events execution plans free tools galera cluster GDPR Getting Started Git GitHub Google Cloud Hadoop Healthcare high availability HIPAA Hive hybrid clouds Hyper-V IDERA IDERA ACE Index Analyzer index optimization infrastructure as a service (IaaS) infrastructure monitoring installation Integrated Development Environment interbase Inventory Manager IT infrastructure Java JD Edwards JSON licensing load test load testing logical data model macOS macros managed cloud database managed cloud databases MariaDB memory memorystorage memoryusage metadata metric baselines metric thresholds Microsoft Azure Microsoft Azure SQL Database Microsoft PowerShell Microsoft SQL Server Microsoft Windows MongoDB monitoring Monitoring Tools Monyog multiple platforms MySQL news newsletter NoSQL Notifications odbc optimization Oracle PeopleSoft performance Performance Dashboards performance metrics performance monitoring performance schema performance tuning personally identifiable information physical data model Platform platform as a service (PaaS) PostgreSQL Precise Precise for Databases Precise for Oracle Precise for SQL Server Precise Management Database (PMDB) product updates Project Migration public clouds Query Analyzer query builder query monitor query optimization query performance Query Store query tool query tuning query-level waits Rapid SQL rdbms real time monitoring Real User Monitoring recovery regulations relational databases Releases Reporting Reports repository Restore reverse engineering Roadmap sample SAP Scalability Security Policy Security Practices server monitoring Server performance server-level waits Service Level Agreement SkySQL slow query SNMP snowflake source control SQL SQL Admin Toolset SQL CM SQL code SQL coding SQL Compliance Manager SQL Defrag Manager sql development SQL Diagnostic Manager SQL Diagnostic Manager for MySQL SQL Diagnostic Manager for SQL Server SQL Diagnostic Manager Pro SQL DM SQL Doctor SQL Enterprise Job Manager SQl IM SQL Inventory Manager SQL Management Suite SQL Monitoring SQL Performance SQL Quality SQL query SQL Query Tuner SQL Safe Backup SQL script SQL Secure SQL Security Suite SQL Server sql server alert SQL Server Migration SQL Server Performance SQL Server Recommendations SQL Server Security SQL statement history SQL tuning SQL Virtual Database sqlmemory sqlserver SQLyog Storage Storage Performance structured data Subversion Support tempdb tempdb data temporal data Tips and Tricks troubleshooting universal data models universal mapping unstructured data Uptime Infrastructure Monitor user experience user permissions Virtual Machine (VM) web services webinar What-if analysis WindowsPowerShell
Amazon Relational Database Service (RDS) is a hosted database service in the AWS cloud. If your organization's data is stored in one of the popular database systems, but on a company server or perhaps you're renting a dedicated server, you might want to consider switching to Amazon RDS.  With Amazon RDS, you can choose from several relational database systems:  MySQL, MariaDB, Oracle, Postgres, and SQL Server, as well as Amazon Aurora.

There are many advantages to Amazon RDS, such as server scaling and load balancing of user traffic. Best of all, it can reduce the operational costs of running database software like MySQL. With Amazon RDS, you don't need to worry about performing security updates, patching the operating system, or tuning the database. In fact, some of the patches Amazon deploys for MySQL and MariaDB are specifically designed to get better performance in a cloud setting.  Let's look at some major features of Amazon RDS.

Scaling & Load Balancing

Scaling a system's capacity seems like it should be easy, but it can be intimidating. Adding faster and more powerful servers to an infrastructure will typically address scaling needs, as well as additional storage and other such things.  With Amazon, you can achieve this by resizing an instance. Although you will have to endure some downtime while doing this, it's much easier than physically adding servers on-site, or trying to work with typical web hosting companies that provide no assistance in this area.  With Amazon RDS, it's just a matter of a few clicks to expand your server.

Don't over expand, though, in preparation for a surge in traffic that your database may encounter for a short period of time.  More hardware, even virtual hardware comes with a price.  So stay lean, and when you do expand your systems for temporary surges, be sure to contract them when it's no longer needed.

When writes and read queries are running too slow, there are several things you can do to improve performance:  better query construction and better table schema. Large, wide tables can be broken into smaller, more manageable tables to improve performance. They can be accessed via <tt>JOIN</tt>. When huge tables can't be reduced, you can try using partitioning.  This can work well.

With Amazon RDS, you also have the option of sharding data.  Sharding is a configuration in which data is stored on more than one instance.  This can significantly improve performance for large databases with plenty of traffic. It can be a more reliable and more permanent solution. You can split data by rows or by tables.  If you do this, you may want to use a special storage engine such as Spider, which has been designed for sharding. You will have to make adjustments to your application for handling writes, for it to know when to include the other tables. This can be quite involved, but the server logistics part is easy with Amazon RDS.

Database Administration

Another advantage of Amazon RDS is that it allows you to outsource operational aspects of managing a MySQL database or the like. Organizations that appreciate this aspect tend to have many developers, but don't have dedicated database administrators. You could hire a full-time DBA whose primary duty is to monitor and adjust your databases based on your specific needs and changing factors.  Although this can be costly and isn't always necessary, some organizations do this because they find the administering of databases to be complicated. Monitoring and tuning your databases should be easy.

Despite the many features related to Amazon RDS, it doesn't provide any advisors to help you monitor and take action on issues you might find with your databases. Nor does it provide any insights: there is just raw data and logs.  Sure it can collect information on slow queries, reoccurring errors, security breaches and a host of other things that would be of interest to a DBA.  But putting it all together and making sense of it to be able to act appropriately and timely requires either expertise or the right tools. Even if you have a dedicated DBA, he will still need the right tools.  Otherwise, he will be stressed and probably miss problems and trends as the develop or you'll need to hire additional DBAs.  This is where Webyog can help your staff operate efficiently and keep your staff to a minimum with database monitoring software like Monyog and SQLyog.

With Monyog, you can easily connect with Amazon RDS to monitor your MySQL or similar databases.  You can track user traffic and set up alerts to notify you when there are spikes in traffic. It can help you to see trends developing before a bottleneck forms. Then you can make adjustments to your Amazon instances to be able to handle the increased activity.  You can also set alerts to let you know when your database traffic settles down so then you can reduce the resources allocated to your Amazon account and thereby keep the cost to a minimum.  You can monitor traffic on multiple servers in your database system to determine if you need to make adjustments to balance the load better.  There are many aspects of Amazon RDS you can monitor with Monyog to be able to administer your databases, proactively and calmly. Otherwise, you will be managing your databases by exceptions, when a crisis hits.  This will lead to many problems.  Make it easier on yourself and do a better job administering your databases on Amazon RDS: use Monyog.

Highlights: Monitoring Amazon RDS: Beyond Raw Logs

This post is an extension to the webinar – Monitoring Amazon RDS: Beyond Raw Logs. Thank you everyone, who attended our Webinar.

In the webinar, Shree Nair, Product Manager, Webyog talked about the limitations of monitoring MySQL hosted in the AWS cloud using tools like CloudWatch. While CloudWatch has great capabilities for monitoring the underlying host, it provides only raw logs such as Error Log, Slow Query Log, and General Query Log.

There is no way to identify the problem SQL with CloudWatch which means you need to start monitoring beyond a limited set of database metrics before something goes horribly wrong.

He also discussed the different tools available for monitoring Amazon RDS and how you can effortlessly do file-based log monitoring using Monyog.

Here’s the complete video for all those who couldn’t attend the webinar.

We hope you found the webinar useful. We will be conducting more webinars in upcoming weeks. To keep yourself updated, subscribe to our blogs.

Download a free trial of Monyog here.