Click here to Skip to main content
Click here to Skip to main content

Tagged as

Go to top

Infobright/MySQL Statistics Utility

, 6 Jun 2012
Rate this:
Please Sign up or sign in to vote.
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' 

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.  

 

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.

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:  

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:  

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

Share

About the Author

Ryan Krage

United States United States
No Biography provided

Comments and Discussions

 
QuestionCould not connect to the database PinmemberCode and Coffee8-Jul-14 18:36 
AnswerRe: Could not connect to the database PinmemberCode and Coffee8-Jul-14 19:26 
Questiontotal raw size of the entire database PinmemberFrancis Nicholas Jr4-Jun-12 5:37 
AnswerRe: total raw size of the entire database PinmemberRyan Krage4-Jun-12 5:53 
GeneralRe: total raw size of the entire database [modified] PinmemberFrancis Nicholas Jr4-Jun-12 7:18 
GeneralRe: total raw size of the entire database PinmemberRyan Krage6-Jun-12 9:30 
GeneralRe: total raw size of the entire database PinmemberFrancis Nicholas Jr11-Jun-12 3:31 
GeneralRe: total raw size of the entire database PinmemberRyan Krage11-Jun-12 10:57 
GeneralRe: total raw size of the entire database PinmemberFrancis Nicholas Jr12-Jun-12 6:33 
GeneralRe: total raw size of the entire database PinmemberFrancis Nicholas Jr11-Jun-12 3:17 
Generalstatistics to a mySQL database PinmemberFrancis Nicholas Jr27-Jun-12 8:42 
GeneralRe: statistics to a mySQL database PinmemberRyan Krage28-Jun-12 7:54 
QuestionTotal database size PinmemberFrancis Nicholas Jr1-Jun-12 8:17 
AnswerRe: Total database size PinmemberRyan Krage1-Jun-12 8:57 
GeneralRe: Total database size [modified] PinmemberFrancis Nicholas Jr1-Jun-12 13:57 
QuestionJava JRE Pinmembercraig trombly @ infobright29-May-12 3:38 
AnswerRe: Java JRE PinmemberRyan Krage29-May-12 7:24 

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

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

| Advertise | Privacy | Mobile
Web04 | 2.8.140926.1 | Last Updated 6 Jun 2012
Article Copyright 2012 by Ryan Krage
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid