TAG | database monitoring
Both MariaDB and Percona Server are forks of MySQL and strive to be drop in replacements for MySQL from a binary, api compatibility, and command line perspective.
It’s great to have an alternative to MySQL since you never know what might happen to it given that Oracle bought it for 1 billion dollars. In this blog post I set out to see if these MySQL forks would work 100% with AppDynamics for Databases. If you’re not familiar with the AppDynamics for Databases product I suggest you take a few minutes to read this other blog post.
Getting both MariaDB and Percona Server installed onto test instances was pretty simple. I chose to use 2 Red Hat Enterprise Linux (RHEL) servers running on Amazon Web Services (AWS) for no particular reason other than they were quick and easy to get running. My first step was to make sure that MySQL was gone from my RHEL servers by running “yum remove mysql-server”.
Installing both MariaDB and Percona Server consisted of setting up yum repository files (documented here and here) and running the yum installation commands. This took care of getting the binaries installed so the rest of the process was related to starting and configuring the individual database servers.
The startup command for both MariaDB and Percona Server is “/etc/init.d/mysql start” so you can see that these products really do strive for direct drop in adherence to MySQL. As you can see in the screen grabs below I ended up running MariaDB 10.0.3 and Percona Server 5.5.31-30.3.
Connected to each of these databases were 1 instance of WordPress and 1 instance of Drupal in a nearly “out of the box” configuration besides adding a couple of new posts to each CMS to help drive a small amount of load. I didn’t want to set up a load testing tool so I induced a high disk I/O load on each server by running the UNIX command “cat /dev/zero > /tmp/zerofile”. This command pumps the number 0 into that file as fast as it can basically crushing the disk. (Use Ctrl-C to kill this command before you fill up your disk.)
Getting the monitoring set up was really easy. I used a test instance of AppDynamics for Databases to remotely monitor each database instance (yep, no agent install required). To initiate monitoring I opened up my AppDynamics for Databases console, navigated to the agent manager, clicked the “add agent” button, and filled in the fields as shown below (I selected MySQL as the database type):
My remote agent didn’t connect the first time I tired this because I forgot to configure iptables to let my connection through even though I had set up my AWS firewall rules properly (facepalm). After getting iptables out of the way (I just turned it off since these were test instances) my database monitoring connections came to life and I was off and running.
Taking a look at all of the data pouring into AppDynamics for Databases I can see that it is 100% compatible with MariaDB and Percona Server. There are no errors being thrown and the data is everything that it should be.
The beauty of my induced disk I/O load was that just by clicking around the web interface of WordPress and Drupal I was getting slow response times. That always makes data more interesting to look at. So here are some screen grabs for each database type for you to check out…
If you’re currently running MySQL you might want to check out MariaDB and Percona Server. It’s possible that you might see some performance improvements since the storage engine for MariaDB and Percona is XtraDB as opposed to MySQL’s InnoDB. Having choices in technology is a great thing. Having a unified monitoring platform for your MySQL, MariaDB, Percona Server, Oracle, SQL Server, Sybase, IBM DB2, and PostgreSQL database is even better. Click here to get started with your free trial of AppDynamics for Databases today.Link to this post:
Relational databases are still an important application component even in today’s modern application architectures. There is usually at least one relational database lurking somewhere within the overall application flow and understanding the behavior of these databases is major factor in rapidly troubleshooting application problems. In 2009, Amazon launched their RDS service which basically allows anyone to spin up a MySQL, Oracle, or MS-SQL instance whenever the urge strikes.
While this service is amazingly useful there are also some drawbacks:
- You cannot login and access the underlying OS of your database instance. This means that you can’t use any agent based monitoring tools to get the visibility you really want.
- The provided CloudWatch monitoring metrics are high level statistics and not helpful in troubleshooting SQL issues.
The good news is that you can monitor all of your Amazon RDS instances using AppDynamics for Databases (AppD4DB) and in this article I will show you how. If you’re unfamiliar with AppD4DB click here for an introduction.
Setting Up A Database Instance In RDS
Creating a new database instance in RDS is really simple.
Step 1, login to your Amazon AWS account and open the RDS interface.
Step 2, Initiate the “Launch a DB Instance” workflow.
Step 3, select the type of instance you want to launch. In this case we will use MySQL but I did test Oracle and MS-SQL too.
Step 4, fill in the appropriate instance details. Pay attention to the master user name and password as we will use those later when we create our monitoring configuration (although we could create a new user only for monitoring if we want).
Step 5, finish the RDS workflow. Notice I called the database “wordpress” as I will use it to host a WordPress instance. Also notice that we chose to use the “default” DB security group. You will need to access the security group settings after your new instance is created so that you allow access to the database from the internet. For the sake of testing I opened up my database to 0.0.0.0/0 (not shown in this workflow) which allows the entire internet to connect to my database if they have the credentials. You should be much more selective if you have a real database instance with production applications connected.
Step 6, wait for your instance to be created and watch for the “available” status. When you click on the database instance row you will see the details populate in the “Description” tab below. We will use the “Endpoint” information to connect AppD4DB to our new instance. (At this point you can also build the database structure and connect your application to your running instance.)
Monitor Your Database With AppD4DB
Step 1, enable database monitoring from the “Agent Manager” tab in AppD4DB. Notice we map RDS “Endpoint” to AppD4DB “Hostname or IP Address” and in this case we are using the RDS “Master Username” and “Master Password” for “Username” and “Password” in AppD4DB. Also, since Amazon does not allow any access to the associated OS (via SSH or any other method) we cannot enable OS monitoring.
Step 2, start your new database monitoring and use your application. Here is a screen grab showing a couple of slow SQL queries.
So here is what I found for each type of database offered by Amazon RDS.
- MySQL: Fully functional database monitoring.
- Oracle: Fully functional database monitoring.
- MS-SQL: All database monitoring functionality works except for File I/O Statistics. This means that we are 99% functional and capture everything else as expected including the ability to show SQL execution plans.
Amazon RDS makes it fast and easy to stand up MySQL, MS-SQL and Oracle databases. AppDynamics for Databases makes it fast and easy to monitor your RDS databases at the level required to solve your application and database problems. Sounds like a perfect match to me. Sign up for your free trial of AppD4DB and see for yourself today.Link to this post:
The most common cause of application slowdowns today is slow SQL or stored procedures in the database. The reason? Databases store large amounts of data on disk, and disk is super slow relative to data in memory (unless its SSD). As data volumes grow, the need to configure, maintain and optimize a database also grows because you can’t manage everything on one disk or volume. This is why database administrators (DBA’s) exist.
A huge problem is that developers write ad-hoc SQL queries for their applications and have no idea how those queries actually execute and retrieve data from disk in the database. They are blind to the number of records in a table, or which columns in a given table has indexes. This is why developers typically blame the DBA when their SQL queries run slow– they assume slow queries means a slow database, and that the DBA isn’t doing his job properly. This couldn’t be further from the truth.
With AppDynamics for databases we’re now giving DevOps teams visibility of how their application SQL queries actually execute within the database, so when things slow down they can now collaborate with DBA’s instead of blaming them. Thats right, users of AppDynamics will now be able to find the root cause of slow SQL and stored procedures in their application. This is indeed good news because customers can dramatically improve their end user experience, response time and application throughput by tuning their data access layer.
Obviously, SQL executes differently in different databases, thats why we built AppDynamics for databases to provide universal support for all common relational databases like Oracle, SQL Server, DB2, Sybase, MySQL and PostGres. This is a big deal because no monitoring vendor at the moment provides database diagnostics across all type of relational databases, not to mention providing the application and business transaction context.
So let’s show you a few screenshots of what AppDynamics for Databases can give you.
Single Pane of Glass View
How about a single pane of glass view to monitor ALL your databases regardless of platform? done. Application databases these days vary significantly, most use Oracle, some use MySQL and obviously .NET applications are better related to windows-based databases like SQL Server and Sybase. Its therefore important you can get a holistic view of performance across all your databases.
Real-Time Performance Metrics:
Its always good to know exactly what is happening “right now” in a database. Has the database ran out of connections? or is the database experiencing latency from locking on rows? These are just a few answers you can get from the below “Current” workspace which provides a real-time view of database resources and performance.
Historical Analysis of Database Activity:
Real-time data is good, but its also helpful to have historical data so you can identify trends, spikes and abnormal patterns in performance. For example, a simple application code change can have a dramatic impact on a database and its performance. We had an agile customer last year who deployed a code release in production and immediately saw a slowdown in application response time. When they drilled into AppDynamics for Java they noticed that one business transaction was now performing 25 SQL queries per execution instead of just 2 queries, this was the difference between 20,000 and 250,000 executions per minute in the database. Obviously when you increase concurrency that much in the database your going to experience contention and wait, being able to visually track database resource, time spent, wait states and number of executions over-time is invaluable.
Top SQL Statements and Stored Procedures:
Perhaps the most obvious view you’d expect from a database monitoring solution. This is typically what most Application Performance Monitoring (APM) vendors provide by extracting data from the JDBC or ADO.NET protocols. The big difference between those solutions and AppDynamics is that we allow you to drill down into the SQL or stored procedures and understand their execution plans, so you can actually find the root cause of why your queries run slow. This is great data for application support teams and developers who want to collaborate better with their DBA’s, so they can understand the real reason of database latency.
Most databases will automatically parse and refine the execution of SQL queries based on what plan its query optimizer selects. Just because you add an index to a table to try and make a query faster, doesn’t mean the database query optimizer will use it, and when you consider that index’s aren’t for free (they take up disk space) you might want to check exactly how the database is executing your queries. Take a look at the below explain plan and you can see how the SQL is being processed with two simple selects and two different tables. Notice how the SUBQUERY on the iplookup table is using the index ‘ian1′ because that table has over a million rows in it. If this index was accidentally dropped you can be sure this query would run significantly slower given it would be doing a full table scan on over a million records.
How frequently are applications connecting to your database? and what operations are these applications performing? Applications differ by the volume of data they request, process and manage over-time. For example, in a Cable or Telco provider you might have a customer portal application which accesses customer data inside a large Oracle schema. That same Oracle schema may also service queries from reporting applications for marketing, or perhaps batch jobs from billing applications that need to process large volumes of customer data. If you have different applications performing different operations (read, write, update, delete) at the same time on the same database then that can be a recipe for disaster. You can see from the below screenshot that the application connect to this MySQL instance is spending most of its time inserting data, meaning its write intensive versus read intensive. Obviously if the database and application was purely read intensive, you might consider moving that data to a cache in memory closer to the application logic. Remember, database calls are often remote and expensive, ever so more in the cloud where storage is less than stella.
All database schema’s have generic objects that represent users, databases, tables, indexes and so on. These objects are often configured and maintained by DBA’s to ensure that the database is optimally configured for availability and performance. Change is constant within databases because data volumes are always increasing, and application data models are always evolving to support new features. Making a single configuration change can have a dramatic impact on database and application performance. AppDynamics for databases flags and provides a full audit report on all changes made within the database as shown below. This helps DevOps and DBA’s correlate the impact of change with database performance which can be very powerful. If someone dropped an index and performance spikes shortly after then thats worth knowing!
I walked through just a few features and capabilities of AppDynamics for Databases, here’s a quick 4 minute overview of the product:
You can get started right now and sign up for our free trial.
Happy database monitoring!
Appman.Link to this post:
In my previous blog, I wrote at length about the complexities of running a data cloud in production. This logical data set, spread across many nodes, requires a whole new set of tools and methodologies to run and maintain. Today we’ll look at one of the biggest challenges in managing a data cloud – monitoring.
Database monitoring used to be easy in the days before data clouds. Datasets were stored in a single large database, and there were hundreds of off-the-shelf products available to monitor the performance of that database. When problems occurred, one had simply to open up the monitoring tool and look at a set of graphs and metrics to diagnose the problem.
There are no off-the-shelf tools for monitoring a data cloud, however. There’s no easy way to get a comprehensive view of your entire data cloud, let alone diagnose problems and monitor performance. Database monitoring solutions simply don’t cut it in this kind of environment. So how do we monitor the performance of our data cloud? I’ll tell you what I did.
It just so happens I work at AppDynamics, one of the most powerful application monitoring tools on the market. We monitor all parts of your application including the data layer, with visibility into both Relational and NoSQL systems like Cassandra. With AppDynamics I was able to create a dashboard that gives me a single pane-of-glass view into the performance of my data cloud.
This dashboard is now used in several departments at AppDynamics including Operations, QA, Performance and development teams to see how our data cloud is running. All key metrics about all of our replicas are graphed side by side on one screen. This is the dream of anyone running big data systems in production!
Of course, not all problems are system wide. More often than not you need to drill into one replica or replica set to find a problem. To do that, I simply double click on any part of my big data dashboard to focus on a single replica, change the time range, and add more metrics.
Data clouds are difficult to run, and there aren’t any database monitoring tools fit to monitor them yet. But instead of sitting around waiting for data monitoring tools to catch up with our needs, I’ve built my own Big Data Dashboard with monitoring tool designed for applications.
Of course the fun doesn’t stop here…I still need to find a way to set up alerts and do performance tuning for my data cloud. Stay tuned for more blogs in this series to see how I do it!Link to this post: