Saturday, June 8, 2013

How to Retrieve MySQL Usage Statistics

In enterprise applications, monitoring usage statistics of database servers it uses is useful in many ways. It can be a situation where you need to meter and bill usage of system, or just to keep track of how data grows and system works. 

I'm trying to make you aware of different ways of statistic retrieval methods of some popular DBMS systems. As the first step, in this post I'm going to talk about how to retrieve MySQL usage statistics.

MySQL itself stores statistics data and metadata in some of its internal databases, namely mysql, information_schema and performance_schema. We can access those databases to get what we need. MySQL also gives some special queries for that.

Let's start with a simple one. This is how you can retrieve current session statistics.


And this is how you can see global statistics.


This is one way you can retrieve disk usage statistics for each database.





With following query, disk usage statistics for each database engine can be retrieved. Here pw is used to get the output in Bytes(when pw=0), KB(when pw=1), MB(when pw=2), or GB(when pw=3) etc.
  

There are a lot of other ways you can retrieve other statistics data and metadata from MySQL. I just showed the way. You can explore more.

Reference: http://stackoverflow.com/questions/184560/how-to-monitor-mysql-space

Next post will be on how to retrieve statistics data in ORACLE.

No comments:

Post a Comment