This is a follow up to my previous post titled “MySQL analytics: information_schema polling for table engine percentages”. Here’s an updated query with more output and quicker execution time. What you get: innodb table space utilization percentage, data+index usage total and per innodb/myisam engine, innodb data/index/percentage, myisam data/index/percentages, and overall percentage values. Rather useful for profiling your table engine usage.

Sample output:
innodb_tablespace_utilization_perc: 100
total_size_gb: 26.275011910126
index_size_gb: 2.994891166687
data_size_gb: 23.280120743439
innodb_total_size_gb: 6.751220703125
innodb_data_size_gb: 5.2576751708984
innodb_index_size_gb: 1.4935455322266
myisam_total_size_gb: 19.523791207001
myisam_data_size_gb: 18.02244557254
myisam_index_size_gb: 1.5013456344604
perc_index: 11.3982
perc_data: 88.6018
…

I think most people will agree that one of the biggest advantages of MySQL Community Server is that it’s free. Being free doesn’t get you a multi-million user community though; MySQL offers a great array of transactional engines, advanced high-availability features, robust I/O performance, and it powers many of the top-500 internet sites. When it […]

If you’ve ever needed to know how the data and index percentages per table engine were laid out on your MySQL server, but didn’t have the time to write out a query… here it is!

select
(select (sum(DATA_LENGTH)+sum(INDEX_LENGTH))/(POW(1024,3)) as total_size from tables) as total_size_gb,
(select sum(INDEX_LENGTH)/(POW(1024,3)) as index_size from tables) as total_index_gb,
(select sum(DATA_LENGTH)/(POW(1024,3)) as data_size from tables) as total_data_gb, 

(select ((sum(INDEX_LENGTH) / ( sum(DATA_LENGTH) + sum(INDEX_LENGTH)))*100) as perc_index from tables) as perc_index,
(select ((sum(DATA_LENGTH) / ( sum(DATA_LENGTH) + sum(INDEX_LENGTH)))*100) as perc_data from tables) as perc_data,

(select ((sum(INDEX_LENGTH) / ( sum(DATA_LENGTH) + sum(INDEX_LENGTH)))*100) as perc_index from tables where ENGINE='innodb') as innodb_perc_index,
(select ((sum(DATA_LENGTH) / ( sum(DATA_LENGTH) + sum(INDEX_LENGTH)))*100) as perc_data from tables where ENGINE='innodb') as …

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. There are several upgrades to the reporting code with improved alert algorithms as well as a new script for auto-archiving of the statistics table based […]

Let's take quick look at the performance of the new InfiniDB Subquery processing available with the 1.1.1 Alpha.  The arrow was added to be sure our timings weren't confused with the axis.

This was against a relatively small dataset, the Star Schema Benchmark with 6 million rows in the fact table.  A base query was run where the outer query...

Quick note to let our users know that there was an XML tag closure error on the “alert_22″ subroutine in the “bin/kontroll-reporter-cli.pl” script. This does not affect the webapp portion of Kontrollbase – only reports generated via the command line reporter script. It is not a fatal error but will cause the XML file to […]

If you have been wondering why the overview and graphs pages say “no data to display” on the graphs when you first install Kontrollbase, it’s because there’s no data in the database being returned from the queries that generate the graphs – this is because a new install has no data to graph. This has […]

If you’ve noticed that your recent upgrade did not go as planned and now the application does not load – please check this page: http://wiki.kontrollsoft.com/wiki/UpgradingReleases for notes on upgrades between versions. Typically you need to execute a SQL file against the current schema to bring it up to date. If you have any questions please [...]

This is a small revision and will only be available through SVN. However, it is an important one to speak about as it solves a former issue when running the application on a screen smaller than 1024px wide. While most users may not have noticed this since they have larger monitors it has been noticed [...]

Just a quick bit of news to let you all know that additions to the standard Kontrollbase and Kontrollkit userguides are being halted while we migrate the documentation to a new wiki system run by the very nice Trac software. You will be able to access the Kontrollbase and Kontrollkit documentation at http://wiki.kontrollsoft.com when it [...]