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. 
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
I saw some bad utilization of the query cache on one of my clusters the other day. I haven’t tuned the cache since installing the standard cnf file so let’s take the following usage graph that is output by Monolith.
Queries not cached: 40,000
Queries in cache: 250
Queries not cached: 7500
Queries in cache: 200
In the first image we see there are dramatically less queries in the cache vs. the number of queries not cached. This was pulled with the following variables:
query_cache=32M
query_cache_limit=1M
Take the second graph after changing the settings to:
query_cache=8M
query_cache_limit=128K
So after seeing the visual relation between queries in the cache and queries not cached, tuning and checking again, we have a much better utilization of the cache – less queries not cached. After watching the server more we might want to change settings again to further tune this. If I increase the query_cache size now, even more queries will stay in the cache which will allow more efficient utilization of the cache, but that’s for another time.
One thing to watch here in the trend is that the queries not cached does not continue to grow linearly in the manner that the first graph shows.
If you have Monolith installed in a RedHat cluster environment and are wondering how to make the cron job scripts only execute on the active cluster node, this script is a working example. It needs to be installed and running on each cluster node. The only variable to change is “MYHOST” depending on which node the script is running on. This analyzes the output of /usr/sbin/clustat for active node status and service type.
Feel free to use this as a starting point for other RH cluster aware wrappers.
Here are the associated crontab entries for the monolith cluster aware wrappers.
# Monolith Monitor Wrapper Scripts
02 * * * * root /usr/local/bin/monolith_cluster_report-generator.sh > /dev/null 2>&1
*/15 * * * * root /usr/local/bin/monolith_cluster_agent.sh > /dev/null 2>&1
02 * * * * root /usr/local/bin/monolith_cluster_cronexec.sh > /dev/null 2>&1
10 * * * * root /etc/init.d/crond restart > /dev/null 2>&1
And the actual wrapper:
#!/bin/sh
#mysql_service monolith-node01p started
MYHOST="monolith-node01p" #replace this hostname with whatever the hostname is of the node this script goes on
HOST_STATE=`clustat | tail -n 1 | awk {'print $2'}`
SERV_STATE=`clustat | tail -n 1 | awk {'print $3'}`
DATE=`date +'%c'`
LOG="/var/log/monolith_cluster_cronexec.log"
echo "DATE: $DATE"
echo "HOST_STATE: $HOST_STATE"
echo "SERV_STATE: $SERV_STATE"
if [ "$HOST_STATE" = "$MYHOST" ]; then
if [ "$SERV_STATE" = "started" ]; then
echo "Running cronexec..."
/var/www/html/monolith/cron/readcron.sh /var/www/html/monolith/cron root
echo "$DATE - mysql_service $SERV_STATE on $HOST_STATE :: Running Monolith Cronexec" >> $LOG
else
/bin/rm -f /etc/cron.d/monolith
echo "$DATE - mysql_service $SERV_STATE on $HOST_STATE :: Service not started, removing /etc/cron.d/monolith" >> $LOG
/etc/init.d/crond restart
fi
else
/bin/rm -f /etc/cron.d/monolith
echo "$DATE - mysql_service $SERV_STATE on $HOST_STATE :: Not active node, removing /etc/cron.d/monolith" >> $LOG
/etc/init.d/crond restart
fi
echo "DONE"
 |
 |
 |
 |
 |
 |