Infobright/MySQL Statistics Utility

Download ICE_Tools.zip - 981.3 KB

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.