TheMattReid

I’m attempting this is be as unbiased as possible, since I write the Monolith application. This will hopefully help one decide between a free MySQL server monitoring system and paying for a per-server based licensed product. Both have strengths and weaknesses that should be pointed out before making a decision. You can infer the weaknesses based on the strengths below. That said, let’s just get into it.

Strengths of each product over the other

Monolith - MySQL DBA Console

• presents overall database size, index size, data size, number of schema per server, and aggregate statistics for all monitored servers
• runs mysql server backups remotely and reports on backup state execution
• collects cnf files from each server during the talkback script execution for historical viewing
• built on the LAMP stack, no need for tomcat/jboss knowlege
• provides overall server report, and change control documentation with recommended actions for tuning the server(s)
• application can run under Redhat Cluster Services for high availability
• comes with XML export API for off-application data processing and trending
• compatible with Visual Mining
• is FREE software, GPL licensed, costs absolutely nothing to the user

MySQL Enterprise Dashboard

• customizable alerting instead of fixed alerts
• fine tuned date range graphing (from date to date instead of last X days)
• support for bug tracking that utilizes hooks into bugs.mysql.com
• uses local server agent for information gathering, allows CPU and OS memory to be reported on
• presentation design is arguably more easy on the eyes
• comes with the MySQL Enterprise Server software, and thus a support contract from MySQL/Sun.
• customizable organization of server list instead of organization by client name

Feel free to post your viewpoints on each if you have used both products. I’m always interested in what users have to say.

You can get Monolith here: http://sourceforge.net/projects/monolith-mysql

You can get MySQL Enterprise here: http://www.mysql.com/products/enterprise/ 

This is not related directly to MySQL, but alas I must rant. In this day and age I’m not sure why an application would require IE7 and ActiveX controls to run a ticketing system. If we’re in the technical world, as sysadmins or DBAs, which run Linux/Solaris/Unix on any good server in order to get work done, it only makes sense to use a unix based OS (osx,linux,solaris) as a workstation.

It’s easier to interface with servers, there are better terminal options (in which we live our daily lives), free options to just about everything that exists in Windows, and YET there are ticketing systems (RNT) that require IE7 and ActiveX controls - which require you to run WindowsXP. It doesn’t work on windows server 2003, it doesn’t work on Windows 2000 Pro.

So what are we left with? An impossible situation that requires an employee to run two OSes in order to get work done. Ridiculous! It’s a waste of time and resources. Not to mention that RNT has PHP code in it. Tell me what reason does RNT have to need .NET or ActiveX to get a simple, and poorly made, ticketing  application to work. There has been no answer from anyone.

That is all. It’s just a waste of time and thus, a waste of money. A poor choice in a ticketing application.

After the release of version 1.4 yesterday I was getting a lot of emails with questions so I decided to setup a wiki site for the application. It details the install process, the application design, and some other sections. You can see it here: http://www.monolith-mysql.com

Big changes in this release.

Features:

• Talkback report for local script backups to report back to monolith console
• Status page now summarizes all Codes and failed server connections for past 24 hours
• Install now has correct data import for user setup and system settings
• More GRAPHS!
• Improved logic for analytics and automated tuning recommendations, fixed thread_status bug
• Change request document generation
• Full HTML reporting for statistics gathering and tuning report
• XML API page for custom export of data with all fields available for output
• Integrates with VisualMining: see api-monitor-visualmining.php file
• Data table for index+data size reporting of all monitored databases
• Improved table information gathering via INFORMATION_SCHEMA - no more SHOW commands
• Remote backup process bugs fixed
• User access leves for admin/standard users
• Active/Inactive status now shows next to hostname list on monitor page
• Summary emails for daily backups, no more single email per server

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.

This isn’t mysql related yet, but it will be. It’s the first step in writing my own itunes xml parser so that I can put all of my xml information into a database for other relational processing.

I was needing to copy my songs to a GPS device that I use on my motorcycle (it plays mp3s too). And since I didn’t want to burn a cd and then rip the tracks back to my GPS, I figured I’d export the playlist as XML and then initiate some awk/sed to clean the xml data, then scp the files from the music server to my sd card.

The code!

#!/bin/sh
cat inputfile.xml |grep "Location” | awk -F “” {’print $2′} | sed ’s/<\/string>//g’| sed ’s/\%20/\\\\ /g’ | sed ’s/file:\/\/localhost//g’ | while read line; do
echo $line
scp media@192.168.0.103:”$line” TO_GARMIN/
done


Which, after encountering some badly named files from iTunes ripping… the code changed into this:

#!/bin/sh
COUNTER=0
cat inputfile.xml |
grep "Location” |
grep “.mp3″ |
awk -F “” {’print $2′} |
sed ’s/&/\&/g’ |
sed “s/’/\\\’/g” |
sed “s/’/\\\’/g” |
sed ’s/&/\\\\&/g’|
sed ’s/#/\\\\#/g’|
sed ’s/;/\\\\;/g’|
sed ’s/< \/string>//g’|
sed ’s/\%20/\\\\ /g’ |
sed ’s/\%5B/[/g’ |
sed ’s/\%5D/]/g’ |
sed ’s/(/\\\\(/g’|
sed ’s/)/\\\\)/g’|
sed ’s/!/\\\\!/g’|
sed ’s/%/\\\\%/g’|
sed ’s/\ /\\ /g’|
while read line; do
COUNTER=`expr $COUNTER + 1`
echo “”
echo “COPY: $line”
rsync -av –progress media@192.168.0.92:”${line}” TO_GARMIN/
CODE=$?
echo “CODE=$CODE”
echo “COUNTER=$COUNTER”
if [ “$CODE” != “0″ ]; then
echo “FAILED: $CODE: $line” >> failed.txt
fi
done


New features for the next release. As follows:

1. Status page now reports daily error code summarizations, connection failures to hosts
2. Talkback report page now reports on myback_talkback script version number
3. Talkback report page now tracks the my.cnf file during the backup process, and the cnf file contents for each host are stored as LONGTEXT in the monitor database
4. Multi-tier access levels, admins (rw) and general users (ro)
5. View tables for the status page summarizations speed up reporting
6. Daily summary email - similar to the status page, instead of singular emails for each backup success/fail notification

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)


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"