My Impressions About MONyog

At work we have been looking for tools to monitor MySQL and at the same time provide as much diagnosis information as possible upfront when an alarm is triggered. After looking around at different options, I decided to test MONyog from Webyog, the makers of the better known SQLyog. Before we go on, the customary disclaimer: This review reflects my own opinion and in no way represents any decision that my current employer may or may not make in regards of this product.

First Impression

You know what they say about the first impression, and in this where MONyog started with the right foot. Since it is an agent-less system, it only requires to install the RPM or untar the tarball in the server where you're going to run the monitor and launch the daemon to get started. How much faster or simpler can it be? But in order to start monitoring a server you need to do some preparations on it. Create a MONyog user for both the OS and the database. I used the following commands:

For the OS user run the following command as root (thank you Tom):

groupadd -g 250 monyog && useradd -c 'MONyog User' -g 250 -G mysql -u 250 monyog && echo 'your_os_password' | passwd --stdin monyog

For the MySQL user run:

GRANT SELECT, RELOAD, PROCESS, SUPER on *.* to 'adm_monyog'@'10.%' IDENTIFIED BY 'your_db_password';

Keep in mind that passwords are stored in the clear in the MONyog configuration database, defining a MONyog user helps to minimize security breaches. Although for testing purposes I decided to go with a username/password combination to SSH into the servers, it is possible to use a key which would be my preferred setting in production.

The User Interface

The system UI is web driven using Ajax and Flash which makes it really thin and portable. I was able to test it without any issues using IE 8 and Firefox in Windows and Linux. Chrome presented some minor challenges but I didn't dig any deeper since I don't consider it stable enough and didn't want to get distracted with what could've been browser specific issues.

In order to access MONyog you just point your browser the server where it was installed with an URL equivalent to:

http://monyog-test.domain.com:5555 or http://localhost:5555

You will always land in the List of Servers tab. At the bottom of this page there is a Register a New Server link that you follow and start adding servers at will. The process is straight forward and at any point you can trace your steps back to make any corrections as needed (see screenshot). Once you enter the server information with the credentials defined in the previous section, you are set. Once I went through the motions, the first limitation became obvious: You have to repeat the process for every server, although there is an option to copy from previously defined servers, it can become a very tedious process.

Once you have the servers defined, to navigate into the actual system you need to check which servers you want to review, select the proper screen from a drop down box at the bottom of the screen and hit Go. This method seems straight forward, but at the beginning it is a little bit confusing and it takes some time to get used to it.

Features

MONyog has plenty of features that make it worth trying if you're looking for a monitoring software for MySQL. Hopefully by now you have it installed and ready to go, so I'll comment from a big picture point of view and let you reach your own conclusions.

The first feature that jumps right at me is its architecture, in particular the scripting support. All the variables it picks up from the servers it monitors are abstracted in JavaScript like objects and all the monitors, graphics and screens are based on these scripts. One the plus side, it adds a a lot of flexibility to how you can customize the alerts, monitors, rules and Dashboard display. On the other hand, this flexibility present some management challenges: customize thresholds, alerts and rules by servers or group of servers and backup of customized rules. None of these challenges are a showstopper and I'm sure MONyog will come up with solutions in future releases. Since everything is stored in SQLite databases and the repositories are documented, any SQLite client and some simple scripting is enough to get backups and workaround the limitations.

The agent-less architecture requires the definition of users to log into the database and the OS in order to gather the information it needs. The weak point here is that the credentials, including passwords, are stored in the clear in the SQLite databases. A way to secure this is to properly limit the GRANTs for the MySQL users and ssh using a DSA key instead of password. Again, no showstopper for most installations, but it needs some work from Webyog's side to increase the overall system security.

During our tests we ran against a bug in the SSH library used by MONyog. I engaged their Technical Support looking forward to evaluate their overall responsiveness. I have to say it was flawless, at no point they treated me in a condescending manner, made the most of the information I provided upfront and never wasted my time with scripted useless diagnostic routines. They had to provide me with a couple of binary builds, which they did in a very reasonably time frame. All in all, a great experience.

My Conclusion

MONyog doesn't provide any silver bullet or obscure best practice advice. It gathers all the environment variables effectively and presents it in an attractive and easy to read format. It's a closed source commercial software, the architecture is quite open through scripting and with well documented repositories which provides a lot of flexibility to allow for customizations and expansions to fit any installations needs. For installations with over 100 servers it might be more challenging to manage the servers configurations and the clear credentials may not be viable for some organizations. If these 2 issues are not an impediment, I definitively recommend any MySQL DBA to download the binaries and take it for a spin. It might be the solution you were looking for to keep an eye on your set of servers while freeing some time for other tasks.

Let me know what do you think and if you plan to be at the MySQL UC, look me up to chat. Maybe we can invite Rohit Nadhani from Webyog to join us.

Speaking At The MySQL Users Conference

My proposal has been accepted, yay!

I'll be speaking on a topic that I feel passionate about: MySQL Server Diagnostics Beyond Monitoring. MySQL has limitations when it comes to monitoring and diagnosing as it has been widely documented in several blogs.

My goal is to share my experience from the last few years and, hopefully, learn from what others have done. If you have a pressing issue, feel free to comment on this blog and I'll do my best to include the case in my talk and/or post a reply if the time allows.

I will also be discussing my future plans on sarsql. I've been silent about this utility mostly because I've been implementing it actively at work. I'll post a road map shortly based on my latest experience.

I'm excited about meeting many old friends (and most now fellow MySQL alumni) and making new ones. I hope to see you there!

Log Buffer #178, a Carnival of the Vanities for DBAs

Dave Edwards has offered me to write this week's Log Buffer, and I couldn't help but jump at the opportunity. I'll dive straight into it.

Oracle

I'll start with Oracle, the dust of the Sun acquisition has settled, so maybe it's time to return our attention to the regular issues.

Lets start with Hemant Chitale's Common Error series and his Some Common Errors - 2 - NOLOGGING as a Hint explaining what to expect from NOLOGGING. Kamran Agayev offers us an insight into Hemant's personality with his Exclusive Interview with Hemant K Chitale. My favorite quote is:

Do you refer to the documentation? And how often does it happen?

Very frequently. Most often the SQL Reference (because I don’t — and do not intend to – memorise syntax. Syntax has to be understood rather than memorized). Also, the RMAN Reference (known as the “Backup and Recovery Reference”) and the Database Reference.

At least I'm not the only one forgetting the exact syntax of every command.

Chen Shapira offers us her thoughts on diagnostics in Automated Root Cause Analysis, and I have to agree with her that sometimes it is best to be offered good visualization tools rather than cut and dry solutions and recommendations.

 

Miladin Modrakovic explains how to avoid an Oracle 11g vulnerability: Oracle Exploit Published 11g R2. Gary Myers makes his own contribution about security issues with 10g and 11g in Exploits and revoking the risks of revoking PUBLIC.

As a MySQL DBA I've heard many times the question, "What is the right pronunciation?" and purists would say 'es-que-el' as the ANSI standard specifies. But before there were any standards, there was SEQUEL. I heard the real story many times. Iggy Fernandez's article does a pretty good job summarizing it in Not the SQL of My Kindergarten Days quoting some references for those who would like to dig into the details.

During the weeks leading to the final approval of Sun's acquisition by the EU, there was a lot of speculation about MySQL's destiny's under Oracle. I'm sure that many of the MySQL Community members that kept their cool, they did so because they knew that Ken Jacob would most likely have a say on it. So when the news of his resignation was published, I'm sure that those people (myself among them) starting scratching their heads and started wondering about MySQL's future as well. Matt Assay's news article on CNet, Oracle loses some MySQL mojo, offers a great insight on the issue including quotes of Pythian's own Sheeri Cabral. There are plenty of other articles on the issue in Planet MySQL's feed.

MySQL

Continuing in the context of Oracle's acquisition and Ken's resignation, Bridget Bothelo's article MySQL users hope for the best, prep for the worst speculates about what is in the mind of those who run MySQL in production. If you are interested in the different releases and branches, you'll find plenty of blogs this week starting with Jay Jensen's question When should we expect the next stable MySQL release beyond 5.1? and Ronald Bradford's FOSDEM 2010 presentation Beyond MySQL GA: patches, storage engines, forks, and pre-releases – FOSDEM 2010.

Life goes on and in there is still plenty of action in the MySQL community. As Colin Charles reminds us in his MySQL Conference Update: Grid is up, go promote and register!, this should be an interesting year. In the storage engine and tools front, it's worth checking InfiniDB's impressive performance numbers in InfiniDB load 60 Billion SSB rows trended for storage engine developments and RE: HeidiSQL 5.0 Beta available in the tools segments.

Finally to end the MySQL section with some more mundane issues, here is a collection of articles with mysqldump related scripts and tools: Restore from mysqldump --all-databases in parallel and MyDumpSplitter-Extract tables from Mysql dump-shell script. No list of articles on backups would be complete without asking Can you trust your backup?.

Today we were talking at work about Perl vs Python for scripting. Me, I'm a Perl 'gansta' (see the PostgreSQL section). Traditionally MySQL has had a pretty bad Python driver, but Geert Vanderkelen is working on correcting that. If you're a Python fan check his FOSDEM 2010 presentation at FOSDEM: 'Connecting MySQL and Python', handout & wrap-up.

SQL Server

Aaron Bertrand published 2 really interesting articles that could be apply to other databases as well: When bad error messages happen to good people and Injection is not always about SQL with a funny (if it weren't for the Prius parked in my driveway) example at the end.

2010 MVP Summit is coming up and Thomas LaRock offers his 2010 MVP Summit Preview. His insight applies to other similar events (are you reading MySQL UC attendees?).

In my experience date and time representation and manipulation are tricky in databases, these 2 articles offer some tips: Convert FILETIME to SYSTEM time using T-SQL and Dan Guzman's Ad-Hoc Rollup by date/time Interval

I'm really bad judging the value of SQL server articles, so I'm going to choose the easy way and trust Adam Machanic's T-SQL Tuesday #002: The Roundup to provide with a few technical references.

PostgreSQL

Apparently the PostgreSQL community need their own "Geert" (see reference in the MySQL section) based on what I've read on Damn it feels good to be a (perl) gangsta and Josh Berkus' Postgres needs a new Python driver. Are you up to the challenge? In that case, step up to the plate, that's what Open Source is all about.

The PostGIS group had an important announcement: PostGIS 1.5.0 out and PLR working on Windows 8.3-8.4 installs which the author calls "Perhaps the best release ever", so make space on your disk and schedule and take it for a spin.

Baron Schwartz offers an interesting view on How PostgreSQL protects against partial page writes and data corruption. It offers great insight from a well known MySQL guru.

Last but not least End Point's people have determined with mathematical precision PostgreSQL version 9.0 release date prediction, make sure you read the article and get ready for it.

I hope I kept you reading up to this point and see you around in the blogosphere.

Using MariaDB with MySQL Sandbox

A few days back MariaDB announced their first GA release (see Released: MariaDB 5.1.42), so it is time to start testing it and there is not better way to test any MySQL version in a control environment other than MySQL Sandbox. However Sandbox relies on the fact that the tarball and tarball target directory are prefixed with mysql, which is not true with MariaDB. So here are the 2 tricks I had to use to make it work out of the box.

These steps are explained to create a single sandbox, the tips can be extrapolated to any other configuration. Also, I am trying to avoid renaming any files and/or directories as to leave the packages as close to the original as possible.

Step 1: Use A Symlink For The Tarball

The make_sandbox script will then think it's manipulating a MySQL tarball. Assuming that the default directory is where you have the tarball:

ln -sv mariadb-5.1.42-Linux-x86_64.tar.gz mysql-5.1.42-Linux-x86_64.tar.gz
make_sandbox /home/gnarvaja/Downloads/mysql-5.1.42-Linux-x86_64.tar.gz --sandbox_directory=maria_5.1.42

Make the adjustments needed to your own platform and version.

The make_sanbox run is going to fail since it expects a subdirectory named ./mysql-5.1.42-Linux-x86_64 which doesn't exist since we used a MariaDB tarball.

Step 2: Use A Symlink For The MariaDB Binaries Directory

For the same reason as above, now create a symlink for the directory to where the tarball was extracted and re-run make_sandbox:

ln -sv mariadb-5.1.42-Linux-x86_64 mysql-5.1.42-Linux-x86_64
make_sandbox /home/gnarvaja/Downloads/mysql-5.1.42-Linux-x86_64.tar.gz --sandbox_directory=maria_5.1.42

Remember to always include the --sandbox_directory option to avoid name conflicts in case you want to compare MariaDB with the corresponding MySQL release.

This time the installation will succeed and you'll be ready to start your testing.

Conclusion

I tried to install using the original tarball name using different options and the process failed with different error messages. I looked into the make_sandbox code and I saw some dependencies that would've taken me some time to figure out and fix. This method can be considered a hack, but it gets you up and running in no time.

Giusseppe, if you happen to see this blog, I'll be glad to test a patch when you have it.

Ubuntu Karmic's Network Manager Issues

Since Ubuntu 8.04 aka Hardy Heron, I've had issues with every new release. As Ubuntu evolves into being a viable desktop OS alternative, its complexity has been growing and with the new and improved looks new challenges arise. This bug in particular has been very difficult to diagnose and I can't imagine anyone without enough Linux experience to overcome it on their own, so I decided to summarize the steps I took to fix it ... and vent my frustration at the end.

The Symptom

I came across the issue for the first time while trying Ubuntu's Karmic Netbook remix. After overcoming the typical Broadcom wifi driver, Network Manager would connect, but Firefox would fail to load the web pages 90% of the time. Using ping in the command line worked just fine. Maybe I needed to update the software packages to get the latest patches, surprise, apt-get was having similar problems and timing out. So the problem was deep in the OS layer.

After a lot fiddling and some googling I found bug #417757:

[...] In Karmic, DNS lookups take a very long time with some routers, because glibc's DNS resolver tries to do IPv6 (AAAA) lookups even if there are no (non-loopback) IPv6 interfaces configured. Routers which do not repond to this cause the lookup to take 20 seconds (until the IPv6 query times out). [...]

These routers are common place in many households and most users are completely unaware that they have their own DNS servers, what IPv6 means or even how to update the router's firmware if needed.

The Solution(s)

Going through the comments in the bug I found several recommendations, some made more sense than others, but these are the 2 I used. Most regular users will feel comfortable with these steps. I haven't tried, but it might not be necessary to apply both.

Disable IPv6

You should apply this one especially if the networks to which you connect are not using IPv6 (most home and public networks). Otherwise, skip it. The solution is explained here. To edit the /etc/sysctl.conf file use:

sudo vi /etc/sysctl.conf

Replace vi with your editor of choice. Reboot before retrying the connection.

You can try the setting without changing your system configuration or restarting the machine using the following command:

sudo sysctrl -w net.ipv6.conf.all.disable_ipv6=1

Use OpenDNS or Google DNS Servers

If the previous solution isn't enough and/or you want to try these DNS servers instead of relying on your router or ISP's DNS servers (in many cases it'll improve the DNS lookup performance) edit your /etc/dhcp3/dhclient.conf file using the following command:

sudo vi /etc/dhcp3/dhclient.conf

Add the following lines after the line starting with #prepend:

# OpenDNS servers
prepend domain-name-servers 208.67.222.222, 208.67.220.220;
# Google DNS servers
prepend domain-name-servers 8.8.8.8, 8.8.4.4;

Or if you want to use the GUI, you can follow the instructions in How to setup Google Public DNS in Ubuntu 9.10 Karmic Koala, the instructions work with any of the IP addresses above. Once you apply these changes, restart your box and retry.

The Editorial

If you just read the article for the technical content, this is a good spot to stop. If you're interested in my rant, keep going.

It is well known that long term Linux users have been frustrated by the complications that have been popping up with video (in particular dual head setups), sound and networking in the releases post Ubuntu 8.04 (Hardy). The last 3 releases have improved the overall GUI usability a lot, but they have introduced a number of bugs and issues that make those improvements irrelevant. It's easy to find articles in the web about these issues and I've been hearing and reading about them at multiple Linux and MySQL forums.

Then there are comment like this one which miss the point completely:

[...] > You can't tell your grandmother to edit some config files because her internet is slow
Does your grandmother use Ubuntu then? If so, then just help her out in fixing the issue :) [...]

This goes against what bug #1 is trying to address: Wider Linux adoption.

My requests to the Ubuntu community are:

1. Stop fiddling with the UI and start solving real usability problems. Without easy display, sound and network integration supporting widespread installed hardware, only the übergeeks are going to use Linux and bug #1 will still remain unsolved long after Ubuntu's Zippy Zebra release (I made up the name).
   
2. This is another example where the Open Source community can be as bad as regular companies addressing real world needs. The OSS advantage is in the community members that, instead of spreading FUD and useless comments and articles, come up with proper suggestions and contributions. Unfortunately, sometimes it takes time to find them and until a given software package is forked, no real progress is made.
   

I promise that on my next article I'll write about a MySQL topic.

sar-sql Has A Wiki

Finally settled for a wiki for sar-sql using Ubuntu's own wiki. Right now it only has my regular installation procedure. I will enhance and keep adding items as my time allows. I hope it will help to shape the future of the script.

Enjoy it with responsibility.

PS: I use the Kubuntu format because it is my desktop of choice.

A Hard Look Into Replication

For some time now I've been struggling with a slave that invariably stays behind its master. I have been looking at every detail I can possibly think and in the process discovered a number of replication details I wasn't aware until now. I haven't too much information about them in the documentation, but they can affect the way you look at your slaves.

Seconds Behind Master

This is the first value that to look at when evaluating replication, most of the monitoring systems I know of rely on it. According to the manual:

When the slave SQL thread is actively running
(processing updates), this field is the number of
seconds that have elapsed since the timestamp of the
most recent event on the master executed by that thread.

In fast networks, most of the time, this is an accurate estimate of replication status, but many times you'll see this value to be in the ten of thousands of seconds and not a minute later it falls back to 0. In a chain of master and slaves, the number on the last slave measures how far behind it is from the master at the top of the chain. Under heavy load on the top master, it can even go back and forth wildly. Because of this, I've learned not to trust this value alone. It is a good idea then to compare other variables as well. For example: Master_Log_File / Exec_Master_Log_Pos vs. Relay_Master_Log_File / Read_Master_Log_Pos. The 2nd pair will point to the last statement executed on the slave in relation to the master's binary log file (keep in mind that the statements are actually being executed from the Relay Log file). The first one, will point to the latest statement read from the master and being copied into the Relay Log. Checking all these variables in context will tell you the real status of the slaves.

Sidenote: These are the variables in the slave snapshot in sar-sql, let me know which ones do you monitor to make your slaves are healthy.

Binary Log Format

This item is important and encompasses which format you choose for replication. In the case I am working on, it was set to STATEMENT. An initial look, revealed that the master had bursts of very high traffic, after which the slaves started lagging behind significantly. Most likely (still trying to prove this), because a number of big INSERTs and UPDATEs are being processed at the same time on the master, and inevitably are serialized on the slaves. Without going into the details, switching to ROW solved most of the delays.

Although binlog_format is a dynamic variable, the change will not take place right away. It will be applied to newly created threads/connections. Which means that if you have connection pooling in place  (very common with web applications) , it might take a while until the change actually happens. If you want to force the change as soon as possible, you will have to find a mechanism friendly to your particular environment to regenerate the connections.

Another issue that came up is that, in a replication tree, no matter what the binlog_format variables establishes for the slaves in the middle of the chain. The binary log format of the top master will be used across the chain.

Status Variables and Logs

As you may know, SHOW GLOBAL STATUS includes a number of counters that count how many times a command type was issued. So Com_Insert will tell you how many INSERTs were issued since the server is up. That is, without counting the replication thread. So you may issue thousands of INSERTs on the master, and while Com_Insert will be updated accordingly, it won't change in the slave. Very frustrating when I tried to evaluate if the INSERT rate in the slave matched the rate on the master. The general log has a similar issue, it won't record any statement executed by the slave threads.

Conclusion

Although I understand where these limitations may originate from the way MySQL replication works, it does frustrate me since it really limits the type of tests and diagnostics that can be set up to find what's causing the issues on these servers.

I have to point out that MySQL Sandbox is an invaluable tool to test the different replication scenarios with minimum preparation work.