Data storage requirements for Kontrollbase [monolith] server_statistics + server_report tables. These are the tables that accept incoming data from the polling processes. They hold the historical data for each server that’s being monitored.
AVG Per Server
Assuming stats every 10 minutes = 144/day
Assuming reports every hour = 24/day
10K for one row of server_statistics: 1.44 mb
16K for one row of server_report every hour: 390K
TOTAL: 1.82M/day of data collected per server
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 news on the application is that any users that were requesting a simple web-based install with a single config file have now gotten their wish. It’s a much streamlined process this time around.
And the final news is that I have decided to change the name of Monolith. There’s an app on the MySQL site that is from the Monolith company and it does monitoring along the lines of Zenoss – as in site wide monitoring that isn’t MySQL specific like my software. As such, to keep the projects easier to differentiate – my Monolith app will be released under the name of Kontrollbase. Kontroll is Swedish for control, and seeing as MySQL is originally Swedish and that the app helps you control the performance of MySQL… there it is. So, from now on Monolith will be referred to as Kontrollbase.
Things are looking up for release 2 and it’s coming along with good speed. More later… back to coding.
Monolith: MySQL server monitoring. I only have servers that run Redhat/Debian/Ubuntu Linux x86 and x86_64. I don’t have machines to test MySQL monitoring for the following OSes. Perhaps you would like to be a test candidate for the new version of Monolith? If so, let me know and you’ll be on the list, as well as get beta testing credit.
OSX Server: PPC and Intel
AIX
FreeBSD / OpenBSD / NetBSD
Windows Server
Solaris: Intel & Sparc
Linux servers that are NOT Intel based.
What this involves: testing the client script, perl modules, snmp stats, and other functions that may require a custom client script for that architecture.
Progress on the Open Source enterprise grade MySQL monitoring system; the schema for Monolith version 2 has been designed. Due to the many suggestions for features and the interest it has received I’ve put this on the front burner. That said, here is some more info on the next steps I’ll be taking.
What would you like to see in a free enterprise-grade monitoring system for your daily MySQL needs?I’m rewriting Monolith – MySQL DBA Console from the ground up. This will be version 2 and I would like to get some input from the global MySQL community.So far I am going with the following; comment with any improvements/additions.
Alerts - can be enabled or disabled for reporting on a per server basis
Graphs - default
As mentioned before, this is a OSS application that has no license or subscription fees and will be updated on a regular basis. It’s a mix of PHP for the web side and Perl for the system polling and reporting processes. The installer will be new, web based, and a simple process, much improved from the current method.Please add your comments now, as I am drawing up plans this week for version 2. 
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:
So, that all said, if you want to help test this script email me at ” the matt reid at g m a i l . c o m”
It’s a bit over 800 lines of perl so I’m not going to paste it here, therefore email me
Big changes in this release.
Features:
Get it here: http://sourceforge.net/projects/monolith-mysql/
Any issues or questions feel free to email me: themattreid (at) g m a i l . c o m
Updated some code to graph out the max/avg numbers for Query rate, Data growth, and Connections. Images below.
New features for the next release. As follows:
Look for 1.3 being released very soon! You’ll be able to find it here: http://sourceforge.net/projects/monolith-mysql
Here are some view tables that are going to be in the new release of Monolith. They work with the current release though, so maybe they will be useful for DBAs that have utilized the CLI to get data out of Monolith. The new release will feature an updated Status page that shows the data from these view tables instead of the “last 25 backups”. Edit the DEFINER user as needed.
mysql> show tables;
+-------------------------+
| Tables_in_monolith |
+-------------------------+
| dbs |
| process_exec |
| process_status |
| pruning |
| system |
| users |
| view_daily_result_all |
| view_daily_result_code1 |
| view_daily_result_code2 |
| view_daily_result_code3 |
+-------------------------+
10 rows in set (0.00 sec)
mysql> show create table view_daily_result_all\G
*************************** 1. row ***************************
View: view_daily_result_all
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_daily_result_all` AS select `t1`.`id` AS `id`,`t1`.`client_name` AS `client_name`,`t1`.`host` AS `host`,`t2`.`id` AS `process_exec_id`,`t2`.`file_name` AS `file_name`,`t2`.`database_id` AS `database_id`,`t2`.`duration` AS `duration`,`t2`.`file_size` AS `file_size`,`t2`.`process_status_id` AS `process_status_id`,`t2`.`creationDate` AS `creationDate`,`t3`.`description` AS `description` from ((`dbs` `t1` join `process_exec` `t2`) join `process_status` `t3`) where ((`t1`.`id` = `t2`.`database_id`) and (`t2`.`process_status_id` = `t3`.`id`) and (date_format(`t2`.`creationDate`,_latin1'%Y,%m,%e') = curdate())) order by `t2`.`creationDate` desc
1 row in set (0.00 sec)
mysql> show create table view_daily_result_code1\G
*************************** 1. row ***************************
View: view_daily_result_code1
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_daily_result_code1` AS select `t1`.`id` AS `id`,`t1`.`client_name` AS `client_name`,`t1`.`host` AS `host`,`t2`.`file_name` AS `file_name`,`t2`.`id` AS `process_exec_id`,`t2`.`database_id` AS `database_id`,`t2`.`duration` AS `duration`,`t2`.`file_size` AS `file_size`,`t2`.`process_status_id` AS `process_status_id`,`t2`.`creationDate` AS `creationDate`,`t3`.`description` AS `description` from ((`dbs` `t1` join `process_exec` `t2`) join `process_status` `t3`) where ((`t1`.`id` = `t2`.`database_id`) and (`t2`.`process_status_id` = `t3`.`id`) and (date_format(`t2`.`creationDate`,_latin1'%Y,%m,%e') = curdate()) and (`t2`.`process_status_id` = 1)) order by `t2`.`creationDate` desc
1 row in set (0.00 sec)
mysql> show create table view_daily_result_code2\G
*************************** 1. row ***************************
View: view_daily_result_code2
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_daily_result_code2` AS select `t1`.`id` AS `id`,`t1`.`client_name` AS `client_name`,`t1`.`host` AS `host`,`t2`.`file_name` AS `file_name`,`t2`.`id` AS `process_exec_id`,`t2`.`database_id` AS `database_id`,`t2`.`duration` AS `duration`,`t2`.`file_size` AS `file_size`,`t2`.`process_status_id` AS `process_status_id`,`t2`.`creationDate` AS `creationDate`,`t3`.`description` AS `description` from ((`dbs` `t1` join `process_exec` `t2`) join `process_status` `t3`) where ((`t1`.`id` = `t2`.`database_id`) and (`t2`.`process_status_id` = `t3`.`id`) and (date_format(`t2`.`creationDate`,_latin1'%Y,%m,%e') = curdate()) and (`t2`.`process_status_id` = 2)) order by `t2`.`creationDate` desc
1 row in set (0.00 sec)
mysql> show create table view_daily_result_code3\G
*************************** 1. row ***************************
View: view_daily_result_code3
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_daily_result_code3` AS select `t1`.`id` AS `id`,`t1`.`client_name` AS `client_name`,`t1`.`host` AS `host`,`t2`.`file_name` AS `file_name`,`t2`.`id` AS `process_exec_id`,`t2`.`database_id` AS `database_id`,`t2`.`duration` AS `duration`,`t2`.`file_size` AS `file_size`,`t2`.`process_status_id` AS `process_status_id`,`t2`.`creationDate` AS `creationDate`,`t3`.`description` AS `description` from ((`dbs` `t1` join `process_exec` `t2`) join `process_status` `t3`) where ((`t1`.`id` = `t2`.`database_id`) and (`t2`.`process_status_id` = `t3`.`id`) and (date_format(`t2`.`creationDate`,_latin1'%Y,%m,%e') = curdate()) and (`t2`.`process_status_id` = 3)) order by `t2`.`creationDate` desc
1 row in set (0.01 sec)
 |
 |
 |
 |
 |
 |