Dienstag, September 26, 2006

Stupid bug in 0.04

Yesterday I noticed that the overall database size calculation in 0.04 contains a very stupid bug. While iterating the tables, only their index sizes get added up correctly, while the table data size does not. The display always shows the size of the last table analyzed, so with a single table example, you will not see any difference.

You can check out the trunk version vom SVN to have this fixed before the next release.

Currently I am working on stored connection profiles, which is especially interesting to myself right now, because currently I have to type in the login information each time I run the thing to test it.

Samstag, September 23, 2006

MySQL Index Analyzer: 0.04 released

Version 0.04 of the MySQL Index Analyzer has just been uploaded. This is the first version that features a Swing GUI and actually provides real analysis functions and the generation of ALTER TABLE statements to get rid of superfluous indices.

A quick overview of what is new:

  • Swing GUI
  • Analysis features as on the command line
  • Copying of generated ALTER TABLE statements to the clipboard
  • Information on data and index size distribution
  • Rudimentary analysis of possible disk space savings

Especially the size analysis is in a very early stage and will probably be way off most of the time. I do however plan to spend more work there, because I think this is a very important point.

Documentation in the distribution file is somewhat outdated, but I will give some more details here. The following example is based on a simple database with only one table called people. The table consists of four columns:

 CREATE TABLE `people` (
  `id` int(11) NOT NULL auto_increment,
  `firstname` varchar(32) default NULL,
  `lastname` varchar(32) default NULL,
  `birthday` date default NULL,
  PRIMARY KEY  (`id`),
  KEY `idx_reversename` (`lastname`,`firstname`),
  KEY `idx_id_birth_first` (`id`,`birthday`,`firstname`),
  KEY `idx_full` (`firstname`,`lastname`),
  KEY `idx_first` (`firstname`),
  KEY `idx_id_first` (`id`,`firstname`),
  KEY `idx_id_birth` (`id`,`birthday`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 

There are 7 indices defined on that table, including the PRIMARY key.

Now let's see what the MySQLIndexAnalyzer has to say:

After login, the analysis starts immediately. I will implement a more fine grained control later, for now, the whole schema is analyzed:

Thanks to the great folks of swingfx.dev.java.net waiting is done with some style ;)

As soon as the analysis is ready, you will see a screen like this:

In the upper part of the Window you see some general information about data and index sizes. The Index/Data Ration of 3.0 means, that indices for this database need 3 times the amount of space the real data eats up. This ratio is also displayed as a bar. Please be aware, that all numbers only refer to the tables shown in the details view below, the database might be a lot bigger!

The lower part shows a hierarchical view of the database structure. Tables are shown as little folders, detected index obsoletions are the leafs below them. In the example you can see, that 2 indices where found (idx_first, idx_id_birth) which can be safely removed, because they are only prefixes of idx_full and idx_id_birth_first. For each obsoletion and estimated amount of disk space savings is displayed that would result from dropping that particular index. On the table level you see the sum of all obsoleted indices savings.

Via the "Generate DML" button you get to this dialog:

From here you can either save the DML (ALTER TABLE) statements to a file or store them to the system clipboard: (Please do not blindly apply them to your database!)

Please let me know I you find this tool helpful, have suggestions on what to improve/include, and of course, if you'd like to participate in the development.

Dienstag, September 19, 2006

Internet Explorer Problems

In case you have display problems with MS Internet Explorer, try changing the font size up- and down (e. g. hold Strg and scroll with your mouse wheel once up, once down). This should correct the rendering. As I normally do not use IE (too clumsy to do on Linux) I did only notice the problem today.

Montag, September 18, 2006

GUI coming along

Just a quick update: If you check out the latest trunk version from the subversion repository you will get a working GUI version. After you connected to the database, the analysis will start and the results be displayed in a tree control.

The upper half of the result display is still non-functional, and there is nothing more you can do than look at the result, but I thought someone might be interested in this intermediate step.

Freitag, September 15, 2006

More refactoring is on the way

On the way to a modular design I refactored the source to provide the output through rendering modules. The text output is the first one of those. So from the end-user perspective nothing has changed with the version 0.03 package available now (except that it has grown bigger).

The larger download is however mostly caused by more libraries being included, especially those for the graphical interface that I have just begun. Currently it only provides a login view which lets you connect to the database. However no analysis can be started yet. If you want to have a look at what I intend to let the GUI look like, just modify the main class in the launcher script to de.shipdown....gui.MySQLIndexAnalyzer. I will try to get the first graphical version to actually show something soon.

As for the previous version: To use the package, just download and unzip into a new directory. For use on Linux make sure you chmod the "mia" script to make it executable. On both Windows and Linux you have to have a java executable on your path. If you have not, edit the "mia" or "mia.cmd" script to include the full path.