I’m seeking help from the MySQL community for what tools are used today to generate complex reports for enterprise applications that use MySQL. In an Oracle world, you have Oracle Report Writer, in Microsoft Crystal Reports.

In the open source world there is Jasper Reports, Pentaho Reports and BIRT however I don’t know the power of complex reporting with these.

If anybody has experience using or evaluating these tools please let me know. This may lead to possible work.

If you have been reading the Kontrollbase performance reports and noticed that one alert says your connection usage vs max connections ration is too high but then recommends you to decrease the max_connections variable, then you will find this fix handy. Its two simple queries that execute on the Kontrollbase schema to update the max_connections [...]

The time has come for major performance improvements to the reporter, stats-gather, alerter, and client scripts. This means that I will be rewriting the scripts in Python. A couple of reasons for this; to cut down on the number of modules that are required for the installation process (which also makes distributing the client script [...]

A new version of Kontrollbase – the enterprise monitoring, analytics, reporting, and historical analysis webapp for MySQL database administrators and advanced users of MySQL databases – is available for download. See the downloads page or run “svn update” to get your new version today. http://kontrollsoft.com/software-downloads

It didn’t take long, but the advanced analytics reporting code has been added to the command line reporter version of Kontrollbase. Click here to see an example of the tuning and analytics report output (html format). Now you have all of the reporting features (minus the growth over time) features that are offered in the [...]

The latest version of Kontrollbase – the opensource enterprise MySQL analytics and performance tuning application –  is available for download. Since the previous one, version 225, there have been a lot of great changes. The most significant being the development and inclusion of the Reporter CLI script – which is a combination of the client, [...]

More stats for your consumption! Here are the latest additions to the environment overview reporting features. On the overview page, as of revision 238, you will find the following data for your servers. Update your revision now using ’svn update’ to get the new features or wait until the next formal release if you are [...]

Reporting queries (I will use this term here) are the queries which summaries and groups data over the certain period of time. For example, in Social Network site we want to know how many messages have been sent for the given period of time, group by region and status (sent, received, etc), order by number of messages sent.

As an example I will take a table which is used to send SMS (text messages).

SQL: select concat('+', substring(region_code,1 ,2), 'xxx') as reg, status, count(*) as cnt
from messages
where submition_date between '2009-01-01' and '2009-04-01' group by reg, status
having cnt>100 order by cnt desc, status limit 100;

This query will do a range scan over the submition_date and perform a filesort. There are common well known approaches which can be used to optimize table (“covered index”, “summary tables”, using external data warehouse, etc). …

Update for all of those that are following the progress of Monolith. I’ve been busy this week coding the reporting engine - the code that analyses the hundreds of variables that are being collected - and I’m pleased to say that Perl was the correct choice for this application.Installation on the client servers will be a breeze, and relatively few non-standard modules are required for the server application.

I’ve been silently wondering for some time if the reporting engine equations should be part of a pluggable system (stored in the database) or if they should be hard coded. After writing up all of the equations, I’ve come to the conclusion that, for a quicker release, the equations and reporting engine will be hard-coded this time around for the default system alerts. Howeve -> user-defined alerts will be pluggable. Revisions ahead of 2.0 might feature pluggable main equations but I want v2.0 out asap.

Second bit of …

Large databases, long mysqldump times, long waits for globally locked tables. These problems basically never go away when you rely on mysqldump with –all-databases or a list of databases, as it dumps schemas serially. I’m not going to explain serial vs parallel processing here since that’s a larger topic. Suffice to say that in these days of multi-core / multi-cpu servers we only make use of one processor’s core when we serially export databases using mysqldump. So, I have a new script that attempts to alleviate those issues and now I need testers to provide feedback/improvements.

In order to keep some sanity when dealing with hundreds of database servers, the script takes care of the following:

1. low global locking time requirements: solved by parallel tasks / forked processes
2. backup file checking: with mysqldump files; it checks for “–Dump completed” at the end of the sql file …