Click here to Skip to main content
15,867,308 members
Articles / Database Development / MySQL

Infobright/MySQL Statistics Utility

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
6 Jun 2012MIT3 min read 33.8K   244   7   17
Display Infobright/MySQL Database Statistics in Java

Introduction

This Java CLI uses the JDBC driver to connect to an Infobright database and display its statistics (size, compressed size, compression ratio, etc.). The code can easily be modified for use with a MySQL database. The goal of this project is to provide simple, easy to read code, that shows users how to work with Java, the JDBC driver, Infobright, and MySQL.

Required software  

  • Java JRE (1.5 or greater) 
  • Java IDE (Eclipse recommended) 
  • Infobright or MySQL database  
  • JDBC Driver for MySQL  

Running the executable 

Included with the source code is a runnable JAR file. It can be run from the terminal as follows: 

java -jar ice_tools.jar [OPTIONS]  

The options are listed below.  

  • -u username  
  • -P port (NOTE: This is a capital 'P') 
  • -h hostname 
  • -p (NOTE: This flag does not take any arguments. If used, the program will ask for a password.)

 i.e. java -jar ice_tools.jar -u admin -P 3306 -h 192.168.1.10 -p 

 NOTE: If an option is not specified, the default will be used.  

  • Username: 'root' 
  • Password: blank
  • Port: '5029' 
  • Host: 'localhost' 

Image 1

Editing the source code

Using the IDE of your choosing, create a new project and import the provided files. In order to get the  code to run successfully, you must download the JDBC Driver for MySQL and add a reference to the included JAR file. To do this in Eclipse, configure the build path and add the external JAR as shown below.  

Image 2 

Now, let's take a look at some of the code. The UserInterface class is the main class for the command line interface. The code  is not very interesting as it asks for user input, iterates through a data structure, and prints out formatted text.

The MySqlConnection class is also pretty straight forward. It holds all of the connection information and the functions used to connect and disconnect from the database. 

When the program is first run, a statistics data structure is built. I would like to take a moment to describe this structure. It has three levels: overall statistics, database statistics, and tables statistics (column statistics could be added as a fourth level). The overall statistics are held in the Statistic class. It has compressed size, raw size, compression, and a hash map that maps database names to DatabaseStatistic objects. The DatabaseStatistic class holds statistics for a particular database: compressed size, raw size, compression, and a hash map that maps table names to TableStatistic objects. The TableStatistic class holds statistics for a particular table: compressed size, raw size, and compression. Note that these classes are very similar and could probably be reduced to one class. I only keep them separate to better visualize the data. 

The Statistic class contains the most interesting code. The functions inside this class are used to build the data structure. The function listed below is used to gather table statistics for a specific database. It returns a hash map of the results. Note that runQuery is a helper function.

Java
private Map<String, TableStatistic> getTables(String databaseName) throws SQLException {
	ResultSet results = runQuery("USE " + databaseName);
	String query = "SHOW TABLE STATUS WHERE ENGINE='BRIGHTHOUSE'";
	Map<String, TableStatistic> tables = new HashMap<String, TableStatistic>();
	results = runQuery(query);
	while (results.next()) {
		double compressedSize = Double.parseDouble(results.getString("Data_length")) / 1048576.0;
		double compression = Double.parseDouble(results.getString("Comment").split(": ")[1].split(",")[0]);
		double rawSize = compressedSize * compression;
		tables.put(results.getString("Name"), new TableStatistic(rawSize, compressedSize, compression));
	}
	results.close();
	return tables;

} 

NOTE: The above query is for an Infobright database. If you would like to get statistics for an InnoDB or MyISAM database, remove "where Engine='BRIGHTHOUSE'" from the query. Also, keep in mind that the compression ratio will not be stored in the the "Comment" field. This is specific to Infobright.

Useful Queries 

When tackling this project, I ran into several issues. Here are some MySQL queries that I found helpful. 

This lists the size (in MB) of each individual database:  

SQL
SELECT table_schema, sum( data_length + index_length ) / 1024 / 1024 'Data Base Size in MB',TABLE_COMMENT FROM information_schema.TABLES GROUP BY table_schema; 

This shows the version of the currently installed DBMS:  

SQL
show variables like 'version_comment'; 

History

The most recent version adds the ability to view total raw size, total compressed size, and total compression for the entire database. 

Conclusion 

This code is meant to be used as a starting point for building a database statistics application. You are encouraged to modify the code to fulfill your specific needs. 

License

This article, along with any associated source code and files, is licensed under The MIT License


Written By
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionCould not connect to the database Pin
Code and Coffee8-Jul-14 18:36
Code and Coffee8-Jul-14 18:36 
AnswerRe: Could not connect to the database Pin
Code and Coffee8-Jul-14 19:26
Code and Coffee8-Jul-14 19:26 
Hi, Ryan,

I just happened to successfully connect it. However, the terminal shows me a message as "This is not a Infobright database. Will exit now." I am confused here. So this program only works on Infobright database exclusively?
Questiontotal raw size of the entire database Pin
Francis Nicholas Jr4-Jun-12 5:37
Francis Nicholas Jr4-Jun-12 5:37 
AnswerRe: total raw size of the entire database Pin
Ryan Krage4-Jun-12 5:53
Ryan Krage4-Jun-12 5:53 
GeneralRe: total raw size of the entire database Pin
Francis Nicholas Jr4-Jun-12 7:18
Francis Nicholas Jr4-Jun-12 7:18 
GeneralRe: total raw size of the entire database Pin
Ryan Krage6-Jun-12 9:30
Ryan Krage6-Jun-12 9:30 
GeneralRe: total raw size of the entire database Pin
Francis Nicholas Jr11-Jun-12 3:31
Francis Nicholas Jr11-Jun-12 3:31 
GeneralRe: total raw size of the entire database Pin
Ryan Krage11-Jun-12 10:57
Ryan Krage11-Jun-12 10:57 
GeneralRe: total raw size of the entire database Pin
Francis Nicholas Jr12-Jun-12 6:33
Francis Nicholas Jr12-Jun-12 6:33 
GeneralRe: total raw size of the entire database Pin
Francis Nicholas Jr11-Jun-12 3:17
Francis Nicholas Jr11-Jun-12 3:17 
Generalstatistics to a mySQL database Pin
Francis Nicholas Jr27-Jun-12 8:42
Francis Nicholas Jr27-Jun-12 8:42 
GeneralRe: statistics to a mySQL database Pin
Ryan Krage28-Jun-12 7:54
Ryan Krage28-Jun-12 7:54 
QuestionTotal database size Pin
Francis Nicholas Jr1-Jun-12 8:17
Francis Nicholas Jr1-Jun-12 8:17 
AnswerRe: Total database size Pin
Ryan Krage1-Jun-12 8:57
Ryan Krage1-Jun-12 8:57 
GeneralRe: Total database size Pin
Francis Nicholas Jr1-Jun-12 13:57
Francis Nicholas Jr1-Jun-12 13:57 
QuestionJava JRE Pin
craig trombly @ infobright29-May-12 3:38
craig trombly @ infobright29-May-12 3:38 
AnswerRe: Java JRE Pin
Ryan Krage29-May-12 7:24
Ryan Krage29-May-12 7:24 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.