Click here to Skip to main content
11,705,762 members (58,314 online)
Click here to Skip to main content

Tagged as

Infobright/MySQL Statistics Utility

, 6 Jun 2012 MIT 13.1K 193 7
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

You may also be interested in...

Comments and Discussions

 
QuestionCould not connect to the database Pin
Code and Coffee8-Jul-14 18:36
memberCode and Coffee8-Jul-14 18:36 
AnswerRe: Could not connect to the database Pin
Code and Coffee8-Jul-14 19:26
memberCode and Coffee8-Jul-14 19:26 
Questiontotal raw size of the entire database Pin
Francis Nicholas Jr4-Jun-12 5:37
memberFrancis Nicholas Jr4-Jun-12 5:37 
AnswerRe: total raw size of the entire database Pin
Ryan Krage4-Jun-12 5:53
memberRyan Krage4-Jun-12 5:53 
GeneralRe: total raw size of the entire database Pin
Francis Nicholas Jr4-Jun-12 7:18
memberFrancis Nicholas Jr4-Jun-12 7:18 
GeneralRe: total raw size of the entire database Pin
Ryan Krage6-Jun-12 9:30
memberRyan Krage6-Jun-12 9:30 
GeneralRe: total raw size of the entire database Pin
Francis Nicholas Jr11-Jun-12 3:31
memberFrancis Nicholas Jr11-Jun-12 3:31 
GeneralRe: total raw size of the entire database Pin
Ryan Krage11-Jun-12 10:57
memberRyan Krage11-Jun-12 10:57 
GeneralRe: total raw size of the entire database Pin
Francis Nicholas Jr12-Jun-12 6:33
memberFrancis Nicholas Jr12-Jun-12 6:33 
GeneralRe: total raw size of the entire database Pin
Francis Nicholas Jr11-Jun-12 3:17
memberFrancis Nicholas Jr11-Jun-12 3:17 
Generalstatistics to a mySQL database Pin
Francis Nicholas Jr27-Jun-12 8:42
memberFrancis Nicholas Jr27-Jun-12 8:42 
GeneralRe: statistics to a mySQL database Pin
Ryan Krage28-Jun-12 7:54
memberRyan Krage28-Jun-12 7:54 
QuestionTotal database size Pin
Francis Nicholas Jr1-Jun-12 8:17
memberFrancis Nicholas Jr1-Jun-12 8:17 
AnswerRe: Total database size Pin
Ryan Krage1-Jun-12 8:57
memberRyan Krage1-Jun-12 8:57 
GeneralRe: Total database size Pin
Francis Nicholas Jr1-Jun-12 13:57
memberFrancis Nicholas Jr1-Jun-12 13:57 
QuestionJava JRE Pin
craig trombly @ infobright29-May-12 3:38
membercraig trombly @ infobright29-May-12 3:38 
AnswerRe: Java JRE Pin
Ryan Krage29-May-12 7:24
memberRyan 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 | Terms of Use | Mobile
Web03 | 2.8.150819.1 | Last Updated 6 Jun 2012
Article Copyright 2012 by Ryan Krage
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid