tag:blogger.com,1999:blog-80078020804014972992024-03-08T12:57:46.170-08:00MySQL Mate & MatéSELECT stories FROM experience WHERE moment BETWEEN 'mate' AND 'maté'Gerry Narvajahttp://www.blogger.com/profile/14092453914112673024noreply@blogger.comBlogger25125tag:blogger.com,1999:blog-8007802080401497299.post-4013493632673255712011-10-12T16:08:00.000-07:002011-10-12T16:26:35.318-07:00TIL: Lookout For DEFINER<h2>The Issue</h2><br />
I haven't blogged in a while an I have a long TODO list of things to publish: The repository for the SNMP Agent, video and slides of my OSCON talk and a quick overview of MHA master-master support. In the meantime, here's a little fact that I didn't know from <a href="http://dev.mysql.com/doc/refman/5.1/en/create-view.html">MySQL CREATE VIEW documentation</a>:<br />
<br />
<blockquote>Although it is possible to create a view with a nonexistent <code class="literal">DEFINER</code> account, an error occurs when the view is referenced if the <code class="literal">SQL SECURITY</code> value is <code class="literal">DEFINER</code> but the definer account does not exist.</blockquote>How can this be possible?<br />
<h2>The Problem</h2>For a number of reasons we don't have the same user accounts on the master than we have on the slaves (ie: developers shouldn't be querying the master). Our configuration files include the following line:<br />
<blockquote><pre>replicate-ignore-table=mysql.user</pre></blockquote>So if we create a user on the master, the user definition doesn't go through the replication chain.<br />
<br />
So a VIEW can be created in the master, but unless we run all the proper GRANT statements on the slave as well, the VIEWs won't be effective on the slaves. Example from our slave (output formatted for clarity):<br />
<br />
<blockquote><pre>show create view view3\G
*************************** 1. row ***************************
View: view3
Create View: CREATE ALGORITHM=UNDEFINED
DEFINER=`app`@`192.168.0.1`
SQL SECURITY DEFINER VIEW `view3` AS select
[...]
show grants for `app`@`192.168.0.1`;
ERROR 1141 (42000): There is no such grant defined
for user 'app' on host '192.168.0.1'
</pre></blockquote><h2>The Solution</h2>Once again, Maatkit's to the rescue with <a href="http://www.maatkit.org/doc/mk-show-grants.html">mk-show-grants</a> on the master:<br />
<blockquote><pre>mk-show-grants | grep 192.168.0.1
-- Grants for 'app'@'192.168.0.1'
GRANT USAGE ON *.* TO 'app'@'192.168.0.1'
IDENTIFIED BY PASSWORD '*password_hash';
GRANT DELETE, EXECUTE, INDEX, INSERT, SELECT,
SHOW VIEW, UPDATE ON `pay`.* TO 'app'@'192.168.0.1';</pre></blockquote>A simple copy from the master and paste onto the slave fixed it.<br />
<h2>Conclusion</h2>Every now developers come to me with unusual questions. In this case it was: <i>How come I can access only 2 out of 3 views?</i>. In cases like these, it usually pays off to not overthink the issue and look into the details. A SHOW CREATE PROCEDURE on the 3 views quickly showed that one had a different host for the DEFINER. A quick read through the documentation and an easy test confirmed the mistake. That's why I have 3 mantras that I keep repeating to whomever wants to listen:<br />
<ul><li>Keep it simple</li>
<li>Pay attention to details</li>
<li><a href="https://en.wiktionary.org/wiki/RTFM">RTFM</a> (F is for <i>fine</i>) </li>
</ul>It constantly keeps me from grabbing some shears and going into <a href="https://en.wiktionary.org/wiki/yak_shaving">yak shaving</a> mode.Gerry Narvajahttp://www.blogger.com/profile/14092453914112673024noreply@blogger.com1tag:blogger.com,1999:blog-8007802080401497299.post-59279448803892272702011-08-17T18:17:00.000-07:002011-08-17T18:17:38.004-07:00MySQL HA Agent Mini HowTo<h3>Why This Post</h3><br />
While testing <a href="http://yoshinorimatsunobu.blogspot.com/2011/07/announcing-mysql-mha-mysql-master-high.html">Yoshinori Matsunobo's MHA agent</a> I found that although the <a href="https://code.google.com/p/mysql-master-ha/wiki/TableOfContents?tm=6">wiki</a> has a very complete documentation, it was missing a some details. This article intends to close that gap and bring up some issues to keep in mind when you do your own installation. At the end of the article I added a <b>Conclusions</b> section, if you're not interested in the implementation details, but to read my take on the project, feel free to jump straight to the end from here. <br />
<br />
<h3>My Test Case</h3><br />
Most of our production environments can be simplified to match the MHA's agent most simple use case: 1 master w/ 2 or more slaves and at least one more slave in an additional tier: <br />
<br />
<pre>Master A --> Slave B
+-> Slave C --> Slave D
</pre><br />
As noted in the <a href="https://code.google.com/p/mysql-master-ha/wiki/UseCases#Three_tier_replication">documentation</a>, in this case the MHA agent will be monitoring A, B & C only. I found that unless you have a dedicated manager node, a slave on the 3rd tier (Slave D above) is suitable for this role. All 4 servers were setup as VMs for my evaluation / tests. It makes it easier to simulate hard failure scenarios in a controlled environment. Once this is in place the fun begins.<br />
<br />
<h3>1st Step: User Accounts</h3><br />
In all the examples in the documentation it uses <i>root</i> to login into MySQL and the OS. I prefer to create specific users for each application, so I created a specific MySQL user for the MHA agent and used the linux' <i>mysql</i> user (UID/GID = 27/27 in RedHat / CentOS).<br />
<h4>MySQL Credentials</h4>Reviewing the code, I was able to determine that the agent requires to run some privileged commands like: <i>SET GLOBAL variable</i>, <i>CHANGE MASTER TO ...</i>, <i>FLUSH LOGS ...</i>, <i>SHOW SLAVE STATUS</i>, etc. and creates internal working tables to be used during the master fail over. The easiest way to set it up was using:<br />
<blockquote><pre>GRANT ALL PRIVILEGES ON *.* TO mha_user@<ip address="">'ip address' </ip> <ip address="">
IDENTIFIED BY password;</ip></pre></blockquote>This should be repeated on all 4 servers using the IP addresses for all the potential manager nodes. Yes, it would be possible to use wildcards, but I consider restricting access from specific nodes a safer practice.<br />
<br />
The MySQL replication user needs to be set up to connect from any other server in the cluster, since any of the slaves in the group could be promoted to be master, and have the rest of them connecting to it. <br />
<h4>Linux User</h4>As I mentioned before I use the default RedHat / CentOS definition for the <b>mysql</b> user. Keep in mind that if you installed from the official Oracle packages (ie: RPMs), they may not follow this criteria and could result in mismatching UID/GIDs between servers. The UIDs/GIDs for the <b>mysql</b> user and group have to be identical on <b>all</b> 4 servers. If this is not the case, you may use the following bash sequence/script as <b>root</b> to correct the situation:<br />
<br />
<blockquote><pre>#!/bin/bash
# stop mysql
/etc/init.d/mysql stop
# Change ownership for all files / directories
find / -user mysql -exec chown -v 27 {} \;
find / -group mysql -exec chgrp -v 27 {} \;
# remove old user / group and rename the new ones
# might complain about not being able to delete group.
groupdel mysql
userdel mysql
# Add the new user / group
groupadd -g 27 mysql
useradd -c "MySQL User" -g 27 -u 27 -r -d /var/lib/mysql mysql
# restart MySQL
/etc/init.d/mysql start
</pre></blockquote><br />
Once the <b>mysql</b> user is properly setup, you'll have to create password-less shared keys and authorize them on all the servers. The easiest way to do it is to create it in one of them, copy the public key to the <i>authorized_keys</i> file under the <i>/var/lib/mysql/.ssh</i> directory and then copy the whole directory to the other servers.<br />
<br />
I use the <b>mysql</b> user to run the scripts since for most distributions it can't be used to login directly and there is no need to worry about file permissions, which makes it a safe and convenient user.<br />
<br />
<h3>2nd Step: Follow The Documentation to Install and Configure</h3><br />
Once all the users have been properly setup, this step is straight forward. Check the <a href="https://code.google.com/p/mysql-master-ha/wiki/Installation">Installation</a> and <a href="https://code.google.com/p/mysql-master-ha/wiki/Configuration">Configuration</a> sections of the wiki for more details.<br />
<br />
For the placement of the configuration files I deviated a little bit from documentation, but not much:<br />
<br />
<ol><li>Used a defaults file: <i>/etc/masterha_default</i> with access <b>only</b> for user mysql since it includes the MHA agent password:<br />
<blockquote>-rw------- 1 mysql mysql 145 Aug 11 16:36 masterha_default.cnf</blockquote></li>
<li>The application settings were placed under <i>/etc/masterha.d/</i> this way they're easy to locate and won't clutter the <i>/etc</i> directory.</li>
</ol>For simplicity, I didn't include any of the optional scripts and checks (ie: secondary check) in the configurate. You may want to check the documentation and source code of these scripts. Some of them are not even code complete (ie: master_ip_failover). Unless you are implementing some of the more complicated use cases, you won't even need them. If you do, you'll need to write your own following the examples provided with the source code.<br />
<br />
Once you have everything in place, run the following checks as the <b>mysql</b> user (ie: <i>sudo su - mysql</i>):<br />
<ol><li><a href="https://code.google.com/p/mysql-master-ha/wiki/Requirements#SSH_public_key_authentication">masterha_check_ssh</a>: Using my configuration files the command line looks like:<br />
<blockquote>masterha_check_ssh --conf=/etc/masterha_default.cnf --conf=/etc/masterha.d/test.cnf</blockquote></li>
<li><a href="https://code.google.com/p/mysql-master-ha/wiki/masterha_check_repl">masterha_check_repl</a>: This test will determine whether the agent can identify all the servers in the group and the replication topology. The command line parameters are identical to the previous step.</li>
</ol><br />
Both should show and OK status at the end. All utilities have verbose output, so if something goes wrong it's easy to identify the issue and correct it.<br />
<br />
<h3>3rd Step: Run the Manager Script</h3><br />
If everything is OK, on the MHA node (Server D in my tests) run the following command as user <b>mysql</b> (ie: <i>sudo su - mysql</i>):<br />
<br />
<blockquote>masterha_manager --conf=/etc/masterha_default.cnf --conf=/etc/masterha.d/test.cnf</blockquote><br />
You have to keep in mind that should the master fail, the agent will fail over to one of the slaves and stop running. This way it'll avoid split brain situations. You will either have to build the intelligence in the application to connect to the right master when failing or use a virtual IP. In both cases you'll might need to use customized IP failover scripts. The documentation provides more details.<br />
<br />
Read the section about <a href="https://code.google.com/p/mysql-master-ha/wiki/Runnning_Background">running the script in the background</a> to choose the method that best fits your practice.<br />
<br />
You will have to configure the notification script to get notified of the master failure. The failed server will have to be removed from the configuration file before re-launching the manager script, otherwise it will fail to start.<br />
<br />
You can restart the failed server and set it up as a slave connected to the new master and reincorporate it to the replication group using <a href="https://code.google.com/p/mysql-master-ha/wiki/masterha_conf_host">masterha_conf_host</a>. <br />
<br />
<h3>Conclusion</h3><br />
This tool solves a very specific (and painful) problem which is: make sure all the slaves are in sync, promote one of them and change the configuration of all remaining slaves to replicate off the new master and it does it fairly quickly. The tool is simple and reliable and requires very little overhead. It's easy to see it is production ready.<br />
<br />
The log files are pretty verbose, which makes it really easy to follow in great detail all the actions the agent took when failing over to a slave. <br />
<br />
I recommend to any potential users to start with a simple configuration and add the additional elements gradually until it fits your infrastructure needs.<br />
<br />
Although the documentation is complete and detailed, it takes some time to navigate and to put all the pieces of the puzzle together. <br />
<br />
I would like the agent to support master-master configurations. This way it would minimize the work to re-incorporate the failed server into the pool. Yoshinori, if you're reading this, know that I'll volunteer to test master-master if you decide to implement it.Gerry Narvajahttp://www.blogger.com/profile/14092453914112673024noreply@blogger.com2tag:blogger.com,1999:blog-8007802080401497299.post-82861030252412222722011-07-21T15:24:00.000-07:002011-07-21T17:05:46.888-07:00My MySQL SNMP AgentBack in February I wrote an article titled <a href="http://mmatemate.blogspot.com/2011/02/small-fix-for-mysql-agent.html">A Small Fix For mysql-agent</a>. Since then we did a few more fixes to the agent and included a <b>Bytes Behind Master</b> (or BBM) chart. For those who can't wait to get their hands on the code, here's the current version: <a href="https://spideroak.com/share/KBSWK4A/PublicFiles/home/gnarvaja/Downloads/Shared/mysql-agent-1.0rc2_pp-13.noarch.rpm">MySQL SNMP agent RPM</a>. For those who'd like to learn about it's capabilities and issues, keep reading.<br />
<br />
<h3>What to Expect From this Version</h3><br />
The article I quoted above pretty much describes the main differences with the original project, but we went further with the changes while still relying on Masterzen's code for the data collection piece. <br />
<br />
The first big change is that we transformed Masterzen's code into a Perl module, this way we can easily plug in a new version without having to do massive editing to ours.<br />
<br />
The 2nd change is that we added the code to calculate how many bytes behind is a slave, which should be cross checked always with seconds behind master to get replication's full picture. When a slave is just a few bytes behind, the script calculates the difference straight out of the <i>SHOW SLAVE STATUS</i> information. If the SQL thread is executing statements that are in a binary log file older than the one being updated by the I/O thread, then the script logs into the master to collect the sizes of the previous binary logs and make an accurate calculation of the delta.<br />
<br />
For this change we hit another bug in CentOS 5 SNMP agent, by which 64bit counters were being truncated. The solution is to upgrade to CentOS 6 (not anytime soon, but that's another story) or a work around. We decided for the latter and display a variable flagging this value roll over. This is not needed for non-CentOS 5 platforms as far as we know.<br />
<br />
By now I expect that many of you would have a question in your mind:<br />
<br />
<h3>Why Not Branch / Fork?</h3>Why provide an RPM instead of creating a branch/fork in the original project? There are many reasons, but I'll limit myself to a couple. I trust that before you write an enraged comment you'll keep in mind that this is a personal perception, which might be in disagreement with yours.<br />
<br />
This code is different enough from the original that creating a branch to the original project would be too complicated to maintain. For example: we are using a completely different SNMP protocol and created a module out of the original code. We don't have the resources to follow behind all of Masterzen's possible patches and I wouldn't expect him to adopt my changes.<br />
<br />
If we would've created a fork (a new project derived from the original), I believe at this point, it would divert the attention from the original project or others like <a href="http://palominodb.com/blog/2011/06/09/palominodb-nagios-plugin-mysql">PalominoDB's Nagios plugin</a>.<br />
<br />
<h3>What's Next </h3>We plan to continue maintaining this RPM driven by our specific needs and keep sharing the results this way. If at some point we see it fit to drive the merge into another project or create a new fork of an existing one, we'll do it.<br />
<br />
I will be presenting the project at OSCON next week. If you're going to be around, please come to my talk: <a href="http://www.oscon.com/oscon2011/public/schedule/detail/19012">Monitoring MySQL through SNMP</a> and we can discuss issues like: why use <i>pass_persist</i>, why not use <i>information schema</i> instead of the current method, why not include <i>your personal MySQL instrumentation pet peeve</i><i><your instrumentation="" mysql="" peeve="" pet=""></your></i>, I'd be glad to sit down with you and personally chat about it.<br />
<br />
In the meantime, enjoy, provide feedback and I hope to get to know you at OSCON next Thursday.Gerry Narvajahttp://www.blogger.com/profile/14092453914112673024noreply@blogger.com1Seattle, WA, USA47.6062095 -122.332070847.485093 -122.4497023 47.727326 -122.2144393tag:blogger.com,1999:blog-8007802080401497299.post-18943515884755037022011-05-05T16:37:00.000-07:002011-05-05T16:37:27.970-07:00Some More Replication Stuff<span id="internal-source-marker_0.09417210884603522" style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline;">Listening to the <b>OurSQL </b>podcast: </span><a href="http://technocation.org/content/oursql-episode-43%3A-repli-cans-and-repli-can%2526%2523039%3Bts"><span style="background-color: transparent; color: #000099; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: underline; vertical-align: baseline;">Repli-cans and Repli-can’ts</span></a><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline;"> got me thinking, what are the issues with MySQL replication that </span><a href="https://twitter.com/#%21/sarahnovotny"><span style="background-color: transparent; color: #000099; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: underline; vertical-align: baseline;">Sarah</span></a><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline;"> and </span><a href="https://twitter.com/#%21/sheeri"><span style="background-color: transparent; color: #000099; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: underline; vertical-align: baseline;">Sheeri</span></a><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline;"> didn’t have the time to include in their episode. Here’s my list:</span><br />
<h2><span style="background-color: transparent; color: black; font-family: Arial; font-size: 18pt; font-style: normal; font-weight: bold; text-decoration: none; vertical-align: baseline;">Replication Capacity Index</span></h2><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline;">This is a concept introduced by </span><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: bold; text-decoration: none; vertical-align: baseline;">Percona</span><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline;"> in last year’s post: </span><a href="http://www.mysqlperformanceblog.com/2010/07/20/estimating-replication-capacity/"><span style="background-color: transparent; color: #000099; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: underline; vertical-align: baseline;">Estimating Replication Capacity</span></a><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline;"> which I revisited briefly during </span><a href="http://assets.en.oreilly.com/1/event/56/Advanced%20replication%20monitoring%20Presentation.pdf"><span style="background-color: transparent; color: #000099; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: underline; vertical-align: baseline;">my presentation</span></a><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline;"> at this year’s MySQL Users Conference. Why is this important? Very simple: If you use your slaves to take backups, they might be outdated and will fall further behind during the backups. If you use them for reporting, your reports may not show the latest data. If you use it for HA, you may not start writing to it until the slave caught up.</span><br />
<span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline;">Having said that, measuring replication capacity as you set up slaves is a good way to make sure that the slave servers will be able to catch up with the traffic in the master.</span><br />
<h2><span style="background-color: transparent; color: black; font-family: Arial; font-size: 18pt; font-style: normal; font-weight: bold; text-decoration: none; vertical-align: baseline;">More On Mixed Replication</span></h2><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline;">The podcast also discussed how mixed replication works and pointed to the general criteria that the server applies to switch to STATEMENT or ROW based. However there is one parameter that wasn’t mentioned and it might come back and haunt you: </span><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: italic; font-weight: normal; text-decoration: none; vertical-align: baseline;">Transaction Isolation Level.</span><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline;"> You can read all about it in the MySQL Documentation: </span><a href="http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html"><span style="background-color: transparent; color: #000099; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: underline; vertical-align: baseline;">12.3.6. SET TRANSACTION Syntax</span></a><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline;"> and in particular the </span><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: bold; text-decoration: none; vertical-align: baseline;">InnoDB</span><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline;"> setting </span><a href="http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_locks_unsafe_for_binlog"><span style="background-color: transparent; color: #000099; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: underline; vertical-align: baseline;">innodb_locks_unsafe for binlog</span></a><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline;">. </span><br />
<h2><span style="background-color: transparent; color: black; font-family: Arial; font-size: 18pt; font-style: normal; font-weight: bold; text-decoration: none; vertical-align: baseline;">Keep Binary Logs Handy</span></h2><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline;">Today I found this article from </span><a href="http://www.skysql.com/"><span style="background-color: transparent; color: #000099; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: underline; vertical-align: baseline;">SkySQL</span></a><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline;"> on </span><a href="http://planet.mysql.com/"><span style="background-color: transparent; color: #000099; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: underline; vertical-align: baseline;">Planet MySQL </span></a><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline;">about </span><a href="http://blogs.skysql.com/2011/05/my-contribution-to-mysql-56.html"><span style="background-color: transparent; color: #000099; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: underline; vertical-align: baseline;">Replication Binlog Backup</span></a><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline;">, which is a really clever idea to keep your binary logs safe with the latest information coming out of the master. It offers a method of copying them without the MySQL server overhead. If you purge binary logs automatically to free space using the variable </span><a href="http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_expire_logs_days"><span style="background-color: transparent; color: #000099; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: underline; vertical-align: baseline;">expire_logs_days</span></a><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline;">, you will still have the logs when you need them for a longer time than your disk capacity on the master might allow.</span><br />
<h2><span style="background-color: transparent; color: black; font-family: Arial; font-size: 18pt; font-style: normal; font-weight: bold; text-decoration: none; vertical-align: baseline;">Seconds Behind Master (SBM)</span></h2><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline;">Again, another topic very well explained in the podcast, but here’s another case where this number will have goofy values. Lets say you have a master </span><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: bold; text-decoration: none; vertical-align: baseline;">A</span><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline;"> that replicates master-master with server </span><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: bold; text-decoration: none; vertical-align: baseline;">B</span><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline;"> and server </span><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: bold; text-decoration: none; vertical-align: baseline;">C</span><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline;"> is a regular slave replicating off </span><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: bold; text-decoration: none; vertical-align: baseline;">A</span><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline;">. The application writes to </span><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: bold; text-decoration: none; vertical-align: baseline;">A</span><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline;"> and </span><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: bold; text-decoration: none; vertical-align: baseline;">B</span><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline;"> serves as a hot stand-by master. </span><br />
<span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline;">When we have a deployment that requires DDL and/or DML statements, we break replication going from </span><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: bold; text-decoration: none; vertical-align: baseline;">B</span><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline;"> to </span><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: bold; text-decoration: none; vertical-align: baseline;">A</span><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline;"> (A to B keeps running to catch any live transactions) and apply the modifications to </span><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: bold; text-decoration: none; vertical-align: baseline;">B</span><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline;">. Once we verify that everything is working OK on </span><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: bold; text-decoration: none; vertical-align: baseline;">B</span><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline;">, we switch the application to write to </span><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: bold; text-decoration: none; vertical-align: baseline;">B</span><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline;"> and restore replication going back to </span><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: bold; text-decoration: none; vertical-align: baseline;">A</span><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline;">. This offers a good avenue for rolling back in case the deployment breaks the database in any way (ie: rebuild B using the data in A). What we frequently see is, if the DDL/DML statement takes about 30min (1800 sec) on </span><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: bold; text-decoration: none; vertical-align: baseline;">B</span><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline;">, once we restore replication as explained, the slave </span><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: bold; text-decoration: none; vertical-align: baseline;">C</span><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline;"> will show outrageous numbers for SBM (ie: >12hs behind, I really don’t know how does the SBM arithmetic works to explain this). So it’s a good idea to complement slave drifts monitoring with </span><a href="http://www.maatkit.org/doc/mk-heartbeat.html"><span style="background-color: transparent; color: #000099; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: underline; vertical-align: baseline;">mk-heartbeat</span></a><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline;">, which uses a timestamp to measure replication drifts.</span><br />
<h2><span style="background-color: transparent; color: black; font-family: Arial; font-size: 18pt; font-style: normal; font-weight: bold; text-decoration: none; vertical-align: baseline;">Conclusion</span></h2><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline;">This episode of the </span><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: bold; text-decoration: none; vertical-align: baseline;">OurSQL</span><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline;"> podcast is a great introduction to replication and its quirks. I also believe that MySQL replication is one of the features that made the product so successful and wide spread. However, you need to understand its limitations if your business depends on it.</span><br />
<span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline;"></span><br />
<span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline;">These are my $.02 on this topic, hoping to complement the podcast. I wanted to tweet my feedback to </span><a href="https://twitter.com/#%21/oursqlcast"><span style="background-color: transparent; color: #000099; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: underline; vertical-align: baseline;">@oursqlcast</span></a><span style="background-color: transparent; color: black; font-family: Arial; font-size: 11pt; font-style: normal; font-weight: normal; text-decoration: none; vertical-align: baseline;">, but it ended up being way more than 140 characters.</span>Gerry Narvajahttp://www.blogger.com/profile/14092453914112673024noreply@blogger.com0tag:blogger.com,1999:blog-8007802080401497299.post-28108515761635485182011-02-01T15:13:00.000-08:002011-02-01T15:13:47.444-08:00A Small Fix For mysql-agentIf you're already using an <a href="https://secure.wikimedia.org/wikipedia/en/wiki/Snmp">SNMP</a> monitoring tool like <a href="http://www.opennms.org/">OpenNMS</a>, <a href="http://www.masterzen.fr/software-contributions/mysql-snmp-monitor-mysql-with-snmp/">mysql-agent</a> is a great way to add a number of graphics using <a href="http://www.net-snmp.org/">Net-SNMP</a>. However mysql-agent has a small bug that drove me crazy. I will try to highlight the process on how I discovered it (and hence fix it) since it involved learning about SNMP, how to diagnose it and eventually, once all the pieces came together, how simple it is to write your own agents.<br />
<br />
Although versions are not that important, just for the sake of completeness we were using CentOS 5.5, MySQL 5.5.8 Community RPMs, Net SNMP version 5.3.22 and OpenNMS Web Console 1.8.7.<br />
<br />
<h3>The Problem<br />
</h3>I followed the directions on the mysql-agent blog only to find that I was facing the only <a href="https://github.com/masterzen/mysql-snmp/issues/#issue/5">open issue</a> listed on mysql-agent's Github repository (spoiler alert, the solution is at the bottom). The set up has several components, which makes it difficult to diagnose:<br />
<ul><li>mysql-agent</li>
<li> snmpd + agentx</li>
<li>OpenNMS server</li>
</ul>Running <b>snmpwalk</b> on the MySQL host, as suggested in the mysql-agent article, worked fine (as far as we could tell). However, OpenNMS wasn't getting the data and the graphs weren't showing up.<br />
<br />
It turns out that, once you completed the OpenNMS configuration as described in the article, it's a good idea to run <b>snmpwalk</b> remotely, from the server running OpenNMS, as well. You need to specify your MySQL hostname instead of localhost:<br />
<blockquote>snmpwalk -m MYSQL-SERVER-MIB -v 2c -c public mysql-host enterprises.20267</blockquote><br />
In our case, it failed. Unfortunately the logs didn't offer much information and whatever was failing, it was inside <b>agentx</b>.<br />
<br />
<h3>The Alternative</h3>Since the NetSNMP Perl class hides a lot of the details of the Net SNMP API, we decided to use an alternative method to write the agent using <b>pass_persist</b>. The beauty of this method is that you only need to write a filter script: SNMP requests come through standard input (stdin) and the output needs to be printed to standard output (stdout). In consequence, the agent can be tested straight from the command line before implementing it. A nice article about pass_persist can be found <a href="http://dreness.com/wikimedia/index.php?title=Net_SNMP">here</a>. The <b>pass_persist</b> protocol is fully documented in the <a href="http://www.net-snmp.org/docs/man/snmpd.conf.html">snmpd.conf man page</a>.<br />
<br />
To follow this route we had to tweak the script a little. The tweaks included:<br />
<ul><li>No <b>daemonize</b>: Since the script used stdin/stdout, it needs to run interactively.</li>
<li>All values need to be returned as strings. It was the only work around we found to deal with 64bits values that otherwise weren't interpreted correctly.</li>
<li><b>stderr</b> needed to be redirected to a file to avoid breaking the script's returned values ( add <i>2>/tmp/agent.log</i> to the end of the command line) while you run it interactively.</li>
<li>Use <a href="http://search.cpan.org/%7Eanias/SNMP-Persist-0.05/lib/SNMP/Persist.pm">SNMP::Persist</a> Perl module to handle the SNMP protocol. </li>
</ul>Once the changes were implemented (I promise to publish the alternative mysql-agent script after some clean up) these are the steps I followed to test it (for now I'll leave the <b>-v</b> option out, along with the <b>stderr </b>redirection).<br />
<ol><li>Invoke the agent as you would've done originally, keeping in mind that now it'll run interactively. On your MySQL server:<br />
<blockquote>mysql-agent-pp -c /path/to/.my.cnf -h localhost -i -r 30</blockquote></li>
<li>Test if the agent is working properly (blue -> you type, red -> script output):<br />
<blockquote><b><span style="color: blue;">PING</span><br />
<span style="color: red;">PONG</span></b></blockquote></li>
<li>Does it actually provide the proper values?<br />
<blockquote><b><span style="color: blue;">get</span></b><br />
<b><span style="color: blue;">.1.3.6.1.4.1.20267.200.1.1.0</span></b><br />
<b><span style="color: red;">.1.3.6.1.4.1.20267.200.1.1.0</span></b><br />
<b><span style="color: red;">Counter32</span></b><br />
<b><span style="color: red;">21</span></b><br />
<b><span style="color: blue;">getnext</span></b><br />
<b><span style="color: blue;">.1.3.6.1.4.1.20267.200.1.1.0</span></b><br />
<b><span style="color: red;">.1.3.6.1.4.1.20267.200.1.2.0</span></b><br />
<b><span style="color: red;">Counter32</span></b><br />
<b><span style="color: red;">16</span></b></blockquote></li>
</ol>Note that case is important <b>PING</b> needs to be capitalized, <b>get</b> and <b>getnext</b> need to be in small caps. Once you know it works you'll need to add the <b>pass_persist</b> line to the <b>snmpd.conf</b> file and restart <b>snmpd</b>:<br />
<blockquote># Line to use the pass_persist method<br />
pass_persist .1.3.6.1.4.1.20267.200.1 /usr/bin/perl /path/to/mysql-agent -c /path/to/.my.cnf -h localhost -i -r 30</blockquote>Now execute <b>snmpwalk</b> remotely and if everything looks OK, you're good to go.<br />
<br />
On our first runs, <b>snmpwalk</b> failed after the 31st value. Re-tried the specific values and a few other ones after those with <b>get</b> and <b>getnext</b> and it became obvious that for some, the responses weren't the expected ones.<br />
<br />
<h3>The Bug and The Fix</h3>So now, having identified the failing values, it was time to dig into the source code.<br />
<br />
First the data gathering portion, which fortunately is well documented inside the source code. I found <i>ibuf_inserts</i> and <i>ibuf_merged</i> as the 31st and 32nd values (note that with <b>get</b> you can check other values further down the list, which I did to confirm that the issue was specific to some variables and not a generic problem). A little <i>grepping</i> revealed that these values were populated from the <i>SHOW INNODB STATUS</i> output, which in 5.5 didn't include the the line expected in the program logic, hence, the corresponding values stayed undefined. A patch to line 794 on the original script fixed this particular issue by setting the value to 0 for undefined values.<br />
<br />
<blockquote>794c794<br />
< $global_status{$key}{'value'} = $status->{$key};<br />
---<br />
> $global_status{$key}{'value'} = (defined($status->{$key}) and $status->{$key} ne '' ? $status->{$key} : 0);</blockquote>This fix can be used for the original script and the new pass_persist one. I already reported it upstread in GitHub.<br />
<br />
The original script still failed. OpenNMS still requires <i>getbulk</i> requests (explained in the Net-SNMP documentation) that <b>agentx</b> fails to convert into <i>getnext</i>. This can be reproduced using <b>snmpbulkwalk</b> instead of <b>snmpwalk</b> (Note: It took some tcpdump + wireshark tricks to catch the getbulk requests). The current beta of the pass_persist version of mysql-agent has been in place for a while without issues.<br />
<br />
<h3>Conclusion</h3>I'm not highlighting all the process since it was long and complicated, but I learned a few concepts in during this time the I'd like to point out<br />
<h4>Look Around Before Looking for New Toys</h4>If you're using OSS, you may already have in house most of what you need. This project started when we decided to use OpenNMS (already in place to monitor our infrastructure) and wanted to add to it the MySQL data we wanted to monitor closely. A simple Google search pointed us to <b>mysql-agent</b> right away.<br />
<h4>Embrace OSS</h4>All the tools that we used in this case are Open Source, which made it extremely easy to diagnose the source code when pertinent, try alternatives, benefit from the collective knowledge, make corrections and contribute them back to the community. A full evaluation of commercial software, plus the interaction with tech support to get to the point where we needed a patch would've been as involved as this one and the outcome wouldn't have been guaranteed either. I'm not against commercial software, but you need evaluate if it will add any real value as opposed to the open source alternatives.<br />
<h4>SNMP is Your Friend</h4>Learning about the SNMP protocol, in particular the pass_persist method was very useful. It removed the mystery out of it and writing agents in any language (even bash) is far from difficult. I'm looking forward to go deeper into MySQL monitoring using this technology.<br />
<br />
I'm hoping this long post encourages you to explore the use of SNMP monitoring for MySQL on your own. <br />
<br />
<b>Credit:</b> I need to give credit to Marc Martinez who did most of the thinking and kept pointing me in the right direction every time I got lost. <br />
<br />
<b>NOTE:</b> I'm not entirely satisfied with the current pass_persist version of mysql-agent I have in place, although it gets the job done. Once I have the reviewed version, I plan ... actually promise to publish it either as a branch of the existing one or separately.Gerry Narvajahttp://www.blogger.com/profile/14092453914112673024noreply@blogger.com5tag:blogger.com,1999:blog-8007802080401497299.post-75807019377752411342011-01-14T18:25:00.000-08:002011-01-14T18:25:32.087-08:00About InnoDB Index Size LimitationsThis is mostly a reflection on a limitation in InnoDB that, in my opinion, has persisted for too long. I founded while reviewing the <a href="http://amarok.kde.org/" title="Amarok">Amarok media player</a>. The player uses MySQL in the backend, embedded or regular server, so it makes for a great source of <i>real life</i> data. <br />
<h3>The Issue</h3>By default, Amarok uses MyISAM tables. This means that if it crashes or stops unexpectedly (a logout while playing music may cause this), the latest updates to the DB are all lost. So I've been looking into using InnoDB instead to avoid loosing my playlists or player statistics.<br />
<h3>The Problem</h3>The limitation that bothers me is this one: "<a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-restrictions.html" title="Limits on InnoDB Tables">Index key prefixes can be up to 767 bytes</a>" which has been in place for several years. <br />
Take this Amarok table for example:<br />
<blockquote>CREATE TABLE urls ( <br />
id int(11) NOT NULL AUTO_INCREMENT, <br />
deviceid int(11) DEFAULT NULL, <br />
rpath varchar(324) COLLATE utf8_bin NOT NULL, <br />
directory int(11) DEFAULT NULL, <br />
uniqueid varchar(128) COLLATE utf8_bin DEFAULT NULL,<br />
<br />
PRIMARY KEY (id), <br />
UNIQUE KEY uniqueid (uniqueid), <br />
UNIQUE KEY urls_id_rpath (deviceid, rpath), <br />
KEY urls_uniqueid (uniqueid) <br />
) ENGINE=MyISAM AUTO_INCREMENT=314 <br />
DEFAULT CHARSET=utf8 COLLATE=utf8_bin</blockquote>The result of an ALTER TABLE to convert it to InnoDB:<blockquote><pre style="font-family: inherit;">alter table urls engine=InnoDB;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes</pre></blockquote><h3>The Rant</h3>Note that the maximum key length is in <i>bytes</i>, <b>not</b> <i>characters</i>. So lets review the <i>rpath</i> column. This column stores the path to the media file in the catalog, which could easily be something like: <i>/very/long/path/to/find/a/file/with/music.mp3</i>. If it only uses English alphabet characters it's not very long, but as soon as you start using some multi-byte characters (ie: ñ, ç, ü, etc) the length of the string starts to increase in bytes (ie: ó = 4 bytes). A simple query shows the diference:<br />
<div style="font-family: inherit;"><br />
</div><blockquote style="font-family: "Courier New",Courier,monospace;"><pre>select id, rpath, bit_length(rpath) / 8 as bytes,
char_length(rpath) as chars
from urls limit 1;
+----+-----------------------------------------+---------+-------+
| id | rpath | bytes | chars |
+----+-----------------------------------------+---------+-------+
| 1 | ./home/gnarvaja/Music/Dodododódodo.mp3 | 41.0000 | 39 |
+----+-----------------------------------------+---------+-------+</pre></blockquote><h4>So how big can the index be in bytes?</h4>I let MySQL answer this question for me. I created a similar test table with only a PRIMARY and then recreated the index on <i>rpath</i>. Here's the command sequence and it's output:<br />
<blockquote><pre>CREATE TABLE urls_test (
id int(11) NOT NULL AUTO_INCREMENT,
rpath varchar(324) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
CREATE INDEX rpath_idx ON urls_test (rpath);
Query OK, 0 rows affected, 2 warnings (0.32 sec)
show warnings;
+---------+------+---------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------+
| Warning | 1071 | Specified key was too long; max key length is 767 bytes |
| Warning | 1071 | Specified key was too long; max key length is 767 bytes |
+---------+------+---------------------------------------------------------+
2 rows in set (0.00 sec)
SHOW CREATE TABLE urls_test\G
*************************** 1. row ***************************
Table: urls_test
Create Table: CREATE TABLE `urls_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`rpath` varchar(324) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`id`),
KEY `rpath_idx` (`rpath`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)</pre></blockquote><br />
So down to <b>255</b> characters from the original <b>324</b>. In this case (a music player) it may not be a significant loss, but in a world where applications should support an increasing number of International character sets, in particular Asian ones, this limitation could potentially become serious.<br />
<br />
I'm not a source code expert, so I'm not sure what it would take to remove, or at least expand the maximum key size. InnoDB's maximum key length (see URL quoted at the beginning of the article) seems like a good number: <b>3500</b> bytes ... or I may be overestimating the need for keys bigger than 255 bytes ... your call.Gerry Narvajahttp://www.blogger.com/profile/14092453914112673024noreply@blogger.com9tag:blogger.com,1999:blog-8007802080401497299.post-27681912497782451282010-12-16T10:38:00.000-08:002010-12-16T10:38:21.667-08:00MySQL 5.5 - Upgrading From Previous ReleasesMySQL 5.5 GA has finally arrived and, if you haven't done already, it's time to test it starting with the upgrade procedure. MySQL upgrades are usually easy and uneventful, not in this case so I decided to share my experience with the RH 5 RPMs.<br />
<h4>Step 0 - Is the upgrade for you?</h4>Do you use InnoDB? This is where most of the improvements have been made, and it could be the main reason why you may want to be an early adopter. For a complete reference of the improvements check the <a href="http://dev.mysql.com/doc/refman/5.5/en/innodb-5-5.html">New Features of InnoDB 1.1</a> section of the official MySQL Documentation, <a href="http://palominodb.com/blog/2010/12/15/mysql-55-ga">Sheeri Cabral</a>'s and <a href="http://ronaldbradford.com/blog/five-reasons-to-upgrade-to-mysql-5-5-2010-12-15/">Ronald Bradford</a>'s blogs.<br />
<h4>Step 1 - Read the manual</h4>Ok, I'm the first one to admit that I usually skip this step, but after a couple of false starts I decided that in this case it might be a good idea to go through the documentation, in particular <a href="http://dev.mysql.com/doc/refman/5.5/en/upgrading-from-previous-series.html">Upgrading from MySQL 5.1 to 5.5</a>. The <i style="font-weight: bold;">Incompatible change</i> entries are quite important in this release, and as you will find out, make the upgrade process a little more complex than usual. You mileage may vary, but make sure you're review them carefully.<br />
<h4>Step 2 - Take a backup</h4>I usually trust (since we test them) the overnight backups, in this case, I'm a little more paranoid since you may not be able to do a straight upgrade and will need to remove the old version before you install the new one(see next step). Taking a new backup or double checking your last backup is a very good idea. Make sure you include your current configuration file(s) with the backup.<br />
<h4>Step 3 - The upgrade process</h4>If you have been using the InnoDB plugin, you need to disable the plugin options in your configuration file before you start the upgrade. The details are in the <b>Upgrading from MySQL</b> page I quoted in Step 1, it's the first <i style="font-weight: bold;">Incompatible change</i>.<br />
<br />
Although it wasn't listed in the documentation, when tried to run an RPM upgrade (<span class="Apple-style-span" style="font-family: inherit;"><b>rpm -Uvh MySQL-server-5.5.7_rc-1.rhel5.x86_64.rpm</b>) it failed with a message telling me that I needed to remove the previous version and run a regular install (<b>rpm -i</b>) instead. I usually get a little bit anxious with this kind of recommendations, but fortunately both steps ran smoothly.</span><br />
<span class="Apple-style-span" style="font-family: inherit;"><br />
</span><br />
<span class="Apple-style-span" style="font-family: inherit;">Keep in mind that the first time you'll have to start the service with the <b>--skip-grant</b> option to run <b>mysql_upgrade</b>. If you're upgrading a production server, it highly recommended you add the<b> --skip-networking</b> otherwise you may get connections from your application(s), which you it's not a good idea until at this point. This is the 3rd <b><i>Incompatible change</i></b>. Once you're done, shutdown the MySQL service (ie: using <b>mysqladmin shutdown</b>, no need for username/password in this case) and restart it as you normally would (ie: <b>sudo /etc/init.d/mysql start</b>)</span><br />
<h4>Done</h4><div>At this point your installation should be ready for testing. As I mentioned before, make sure you double check the <b><i>Incompatible change</i></b> notes to make sure your database configuration and/or application doesn't need any further adjustments. I would also recommend following closely the posts in <a href="http://planet.mysql.com/">Planet MySQL</a> for reviews and articles from the community.</div>Gerry Narvajahttp://www.blogger.com/profile/14092453914112673024noreply@blogger.com3tag:blogger.com,1999:blog-8007802080401497299.post-87336803313630447042010-10-22T17:33:00.000-07:002010-10-22T17:33:43.949-07:00MySQL Enterprise Backup and The Meaning of IncludedDuring the MySQL Users Conference, Edward Screven did a <a href="http://en.oreilly.com/mysql2010/public/schedule/detail/12440">keynote presentation</a> that made many of us feel warm and fuzzy about Oracle's future plans for MySQL. If you advance 16m 25s into the presentation, it even gives something to rejoice the MySQL Enterprise customers: "Backup is now included". He didn't say much more after that. Asking around at the conference the days following this announcement, I couldn't get a straight answer about when and how would it be available for existing customers.<br />
<br />
Now, 6 months later (give or take a couple of weeks), the <a href="http://mysql.com/products/enterprise/features.html">MySQL Enterprise Features</a> page has no signs of the now <i>included</i> MySQL Enterprise Backup (the utility previously known as InnoDB Hot Backup) and there has been no other news supporting Edward's announcement anywhere else (if I'm wrong, please point to it with a comment).<br />
<br />
Has anybody any insight about what the definition of <i>included</i> is according to Oracle's dictionary? Maybe it's not <i>included</i> for existing customers and it will be when Oracle comes out with the new price list? This last statement will surely make existing customers pretty unhappy.<br />
<br />
Maybe there was no reason to feel warm and fuzzy after all. What is your take on this particular issue?Gerry Narvajahttp://www.blogger.com/profile/14092453914112673024noreply@blogger.com2tag:blogger.com,1999:blog-8007802080401497299.post-64221470181864224592010-09-22T15:48:00.000-07:002010-09-22T15:48:23.428-07:00A Replication SurpriseWhile working on a deployment we came across a nasty surprise. In hindsight it was avoidable, but it never crossed our minds it could happen. I'll share the experience so when you face a similar situation, you'll know what to expect.<br />
<h2>Scenario</h2>To deploy the changes, we used a pair of servers configured to replicate with each other (master-master replication). There are many articles that describe how to perform an ALTER TABLE with minimum or no downtime using MySQL replication. The simple explanation is:<br />
<ol><li>Set up a passive master of the database you want to modify the schema. </li>
<li>Run the schema updates on the passive master.</li>
<li>Let replication to catch up once the schema modifications are done.</li>
<li>Promote the passive master as the new active master.</li>
</ol>The details to make this work will depend on each individual situation and are too extensive for the purpose of this article. A simple Google search will point you in the right direction.<br />
<h2>The Plan</h2>The <b>binlog_format</b> variable was set to MIXED. While production was still running on the active master, we stopped replication from the passive to the active master so we would still get all the DML statements on the passive master while running the alter tables. Once the schema modifications were over, we could switch the active and passive masters in production and let the new passive catch up with the table modifications once the replication thread was running again.<br />
<br />
The ALTER TABLE statement we applied was similar to this one:<br />
<blockquote>ALTER TABLE tt ADD COLUMN cx AFTER c1;</blockquote>There were more columns after cx and c1 was one of the first columns. Going through all the ALTER TABLE statements takes almost 2 hour, so it was important to get the sequence of event right. <br />
<h2>Reality Kicks In</h2>It turns out that using AFTER / BEFORE or changing column types broke replication when it was writing to the binlog files in row based format, which meant that we couldn't switch masters as planned until we had replication going again. As a result we had to re-issue an ALTER TABLE to revert the changes and then repeat them without the AFTER / BEFORE. <br />
<br />
The column type change was trickier and could've been a disaster, fortunately this happened on a small table (~400 rows which meant the ALTER TABLE took less than 0.3sec). In this case we reverted the modification on the passive master and run the proper ALTER TABLE on the active master. Should this have happened with a bigger table, there was no other alternative than either rollback the deployment or deal with the locked table while the modification happened.<br />
<br />
Once this was done we were able to restart the slave threads, let it catch up and and everything was running as planned ... but with a 2hr delay. <br />
<br />
Unfortunately, using STATEMENT replication wouldn't work in this case for reasons that would need another blog article to explain. <br />
<h2>Happy Ending</h2>After the fact, I went back to the manual and I found this article: <a href="http://dev.mysql.com/doc/refman/5.1/en/replication-features-differing-tables.html">Replication with Differing Table Definitions on Master and Slave</a>. I guess we should review the documentation more often, the changes happened after 5.1.22. I shared this article with the development team, so next time we won't have surprises.Gerry Narvajahttp://www.blogger.com/profile/14092453914112673024noreply@blogger.com1tag:blogger.com,1999:blog-8007802080401497299.post-69847506722112615372010-07-30T17:23:00.000-07:002010-07-30T17:23:28.804-07:00Simple Backup ServerI have not written an article in a while, I partially blame it on the World Cup and my day job. The time has come to share some of my recent experiences with a neat project to provide several teams internally with current MySQL backups.<br />
<br />
When faced with these types of challenges is my first step is to look into OSS packages and how can they be combined into an actual solution. It helps me understand the underlying technologies and challenges.<br />
<br />
<h2>ZRM Backup</h2>I have reviewed <a href="http://www.zmanda.com/backup-mysql.html">Zmanda's Recovery Manager for MySQL</a> Community Edition in the Fall 2008 issue of <a href="http://www.paragon-cs.com/mag/">MySQL magazine</a>. It remains one of my favorite backup tools for MySQL since it greatly simplifies the task and configuration of MySQL backups taking care of most of the details. Its flexible reporting capabilities came in handy for this project as I'll explain later. Some of the key settings:<br />
<br />
<br />
<li>We included the hostname in the ZRM backup-set to make it easier to locate. Linux shell example:<br />
<br />
<blockquote><pre>export BKUP_SET=`hostname -s`-logical</pre></blockquote><br />
</li><br />
<li>Following ZRM conventions, generate a HTML report in the main backup directory.<br />
<br />
<blockquote><pre>mysql-zrm-reporter --where backup-set=$BKUP_SET --type html \
--show backup-status-info >/dir/to/backup/$BKUP_SET/report.html</pre></blockquote><br />
</li><br />
<li>The actual backup files live under the subdirectories:<br />
<br />
<blockquote><pre>/dir/to/backup/$BKUP_SET/<timestamp>/</pre></blockquote>where <i>/dir/to/backup</i> could be mounted on a NFS server<br />
<br />
Please check the <a href="http://wiki.zmanda.com/index.php/Zmanda_Recovery_Manager_for_MySQL_Users_Manual">ZRM for MySQL documentation</a> for details on its configuration and operation. Use the report format that best suits your needs, ZRM provides plenty of options and if none fits your needs exactly, you can still generate your own.<br />
<br />
<h2>lighttpd HTTP Server</h2>As a web server, <a href="http://www.lighttpd.net/">lighty</a> adds very little overhead so it can run on the same MySQL server and the backups can be served directly from it. If this is not acceptable and the backups are stored in an NFS volume, <b>lighty</b> can be installed on the NFS server, the configuration will remain very similar to the one I describe here. <br />
<br />
For this example I’ll assume that the MySQL server host is named <b>dbhost</b>, in which case the <b>/etc/lighttpd/lighttpd.conf</b> file should include:<br />
<br />
<blockquote><pre>server.modules = (
"mod_alias",
"mod_access",
"mod_accesslog" )
## Default root directory set to the main backup set
server.document-root = "/dir/to/backup/dbhost-logical/"
## Set one alias per backup type
alias.url = ( "/logical/" => "/dir/to/backup/dbhost-logical/")
alias.url += ( "/incremental/" => "/dir/to/backup/dbhost-incremental/")
## Enable logs for diagnosis
server.errorlog = "/var/log/lighttpd/error.log"
accesslog.filename = "/var/log/lighttpd/access.log"
server.port = 8088
## virtual directory listings enabled
dir-listing.activate = "enable"
## enable debugging to facilitate diagnosis
debug.log-request-header = "enable"
debug.log-response-header = "enable"
debug.log-request-handling = "enable"
debug.log-file-not-found = "enable"</pre></blockquote><br />
The <b>server.document-root</b> and <b>alias.url</b> settings should match the main directories for the ZRM backup sets.<br />
<br />
Make sure that the user and / or group defined for the lighttpd process have proper permissions to access the backup set directory tree. The backups will be available as a directory listing when using the following URLs: <b>http://dbhost:8088/logical/</b> or <b>http://dbhost:8088/incremental/</b>. Clicking on the <b>report.html</b> file in those directories (see the previous section in the article), the users have access to the backup reports and verify if any of them had errors. The files are also accessible using <a href="http://www.gnu.org/software/wget/manual/wget.html"><b>wget</b></a>.<br />
<br />
If you need to enable tighter security, <b>lighty</b> supports <b>https</b> and <b>LDAP authentication</b> the details are in its documentation and it takes less than 10 minutes to setup.<br />
<br />
<h2>monit Monitoring</h2>When you need a service to be running 24/7, <a href="http://mmonit.com/monit/documentation/monit.html">monit</a> is a great tool in the Linux arsenal to achieve it. It monitors a number of system and services variables and it will start, stop and/or restart any service under a number of conditions. For this POC, the goal is to keep <b>lighty</b> running, restarting it after an eventual crash. We are using the following configuration:<br />
<br />
<blockquote><pre>check process lighttpd
with pidfile "/var/run/lighttpd.pid"
alert some@yourorg.com NOT { instance, pid, ppid, action }
start program = "/etc/init.d/lighttpd start" with timeout 60 seconds
stop program = "/etc/init.d/lighttpd stop" with timeout 30 seconds
if 2 restarts within 3 cycles then timeout
if failed port 8088 protocol http with timeout 15 seconds within 3 cycles then restart</pre></blockquote><br />
If the process crashes or port 8088 becomes unresponsive, <b>monit</b> will (re)start <b>lighty</b> automatically.<br />
<br />
<h2>Conclusion (sort of)</h2>Implement all these tools in a new server takes less than 1 hour. During the the first couple of implementations I learned a lot about how the packages interacted, security issues and users’ needs and expectations. As the users started looking into the solution, they also came up with newer use cases.<br />
<br />
While we look for a more suitable solution, these free (as in freedom and beer) packages provided us with the opportunity to learn while still achieving our goals and delivering results to our users. Now we know what to look for if we decide to evaluate an open core or commercial solution.</li>Gerry Narvajahttp://www.blogger.com/profile/14092453914112673024noreply@blogger.com0tag:blogger.com,1999:blog-8007802080401497299.post-20076380127548554882010-07-20T16:58:00.000-07:002010-07-20T16:58:40.460-07:00Changes to the BlogHi all, it's really unfortunate that this is my first post after a while (the <a href="http://www.fifa.com/worldcup/">World Cup</a> kept me busy/distracted over most of last month), but I need to make some changes to the blog. Lately I had to reject plenty of spam coming through the comments. I will now require to be a registered user and see if I don't have to deal with what I consider one of the worst outcomes of modern technology. I hope this won't stop the feedback I get to my articles. See you soon.Gerry Narvajahttp://www.blogger.com/profile/14092453914112673024noreply@blogger.com0tag:blogger.com,1999:blog-8007802080401497299.post-45649194324473558712010-05-14T12:00:00.000-07:002010-05-14T12:00:25.061-07:00MySQL 5.1.46 With InnoDB Plugin Kicks Butt<div id="doc-contents"><h2></h2>We were discussing the recommendations we issue each quarter around MySQL and the question of using InnoDB plugin came up. We usually follow <a href="http://planet.mysql.com/" id="on1r" title="Major Tom flying over
planet MySQL">Planet MySQL</a> closely, so we read what the blogs had to say and it was all good, but we decided to provide our users some data of our own. We used our own <a href="http://sysbench.sourceforge.net/" id="sh:j" title="Checks whether your system flies or not">sysbench</a> tests on to get the information we needed.<br />
<h2>A Word About Benchmarks</h2>I don't trust most of the benchmarks that are published online because they really apply to the use case of whomever is writing the article. They are usually many factors that can influence them and I find it difficult to apply them <i>as-is</i> to our environment.<br />
<br />
I do trust the benchmarks published online as a reference on how to create and run our own benchmarks. So this article is based on this premise. I recommend you to do your own homework to verify the results for your own use cases.<br />
<h2>The Test</h2>Having said that, we use <b>sysbench</b> against the official MySQL RPM with no special adjustments to the configuration file. We run it once with the embedded InnoDB engine and re-ran them with the InnoDB plugin engine. This is the <b>bash </b>shell wrapper we use:<br />
<blockquote>#!/bin/bash<br />
# Sysbench MySQL benchmark wrapper<br />
for nthr in 1 8 16; do<br />
echo "($(date +%H:%M:%S)) -- Testing $nthr threads"<br />
sysbench --db-driver=mysql --num-threads=$nthr --max-time=900 --max-requests=500000 --mysql-user=user --mysql-password=password --test=oltp --oltp-test-mode=complex --oltp-table-size=10000000 prepare<br />
echo "($(date +%H:%M:%S)) -- Running test for $nthr threads"<br />
sysbench --db-driver=mysql --num-threads=$nthr --max-time=900 --max-requests=500000 --mysql-user=user --mysql-password=password --test=oltp --oltp-test-mode=complex --oltp-table-size=10000000 run | tee $(hostname -s)_$nthr.log<br />
echo "($(date +%H:%M:%S)) -- Cleaning up $nthr threads"<br />
sysbench --db-driver=mysql --num-threads=$nthr --max-time=900 --max-requests=500000 --mysql-user=user --mysql-password=password --test=oltp --oltp-test-mode=complex --oltp-table-size=10000000 cleanup<br />
echo "($(date +%H:%M:%S)) -- done ($nthr)"<br />
done</blockquote>I like to run a 1 thread test since it gives us an idea of the underlying raw performance. Based on other tests we have done, our systems performance peaks somewhere between 8 and 16 concurrent threads, for this test there was no point in running other configurations. You may replace "1 8 16" with the numbers you think will best represent your systems in production. All the tests are run locally, when testing across the network the numbers will vary based on your network performance.<br />
<h3>The Actual Results</h3>So, without further ado, here are the results as reported by <b>sysbench</b>:<br />
<br />
<table border="1" bordercolor="#000000" cellpadding="3" cellspacing="0" id="wnpf"><tbody>
<tr><td style="text-align: center;" width="25%"><b>Number of threads</b></td><td style="text-align: center;" width="25%"><b>No Plugin Trx/sec</b></td><td style="text-align: center;" width="25%"><b>Plugin Trx/sec</b></td></tr>
<tr><td width="25%">1</td><td width="25%">176.32</td><td width="25%">325.75</td></tr>
<tr><td width="25%">8</td><td width="25%">332.82 </td><td width="25%">742.80</td></tr>
<tr><td width="25%">16</td><td width="25%">334.47</td><td width="25%">736.40 </td></tr>
</tbody></table><br />
The results for the <i>No Plugin</i> column are in line with what we got in tests for older 5.1.x releases.<br />
<h2>Conclusion</h2>MySQL v5.1.46 using InnoDB plugin kicks ass! I apologize for the language, but the numbers are pretty amazing. I hope you find this post useful. </div><div id="google-view-footer" style="display: none;"><div id="maybecanedit" style="float: right;"><a class="google-small-link" href="http://docs.google.com/Doc?tab=edit&dr=true&id=dfm6tmhk_326dntn97hr" id="editpermissionlink" title="Edit this page"> Edit this page (if you have permission)</a> <span style="color: #676767;">|</span> <input id="report-abuse-button" onclick="reportAbuse();" type="button" value="Report abuse" /> </div><div style="float: left;"><a class="google-small-link" href="http://docs.google.com/" title="Learn more about Google Docs"> Google Docs -- Web word processing, presentations and spreadsheets.</a> </div> </div><script>
<!--
viewOnLoad();
if(window.jstiming){window.jstiming.a={};window.jstiming.c=1;var j=function(a,b,e){var c=a.t[b],g=a.t.start;if(c&&(g||e)){c=a.t[b][0];g=e!=undefined?e:g[0];return c-g}};window.jstiming.report=function(a,b,e){var c="";if(window.jstiming.pt){c+="&srt="+window.jstiming.pt;delete window.jstiming.pt}try{if(window.external&&window.external.tran)c+="&tran="+window.external.tran;else if(window.gtbExternal&&window.gtbExternal.tran)c+="&tran="+window.gtbExternal.tran();else if(window.chrome&&window.chrome.csi)c+=
"&tran="+window.chrome.csi().tran}catch(g){}if(window.chrome&&window["chrome.loadTimes"]&&window["chrome.loadTimes"]().wasFetchedViaSpdy)c+="&p=s";if(a.b)c+="&"+a.b;var f=a.t,n=f.start,k=[],h=[];for(var d in f)if(d!="start")if(d.indexOf("_")!=0){var i=f[d][1];if(i)f[i]&&h.push(d+"."+j(a,d,f[i][0]));else n&&k.push(d+"."+j(a,d))}delete f.start;if(b)for(var l in b)c+="&"+l+"="+b[l];a=[e?e:"http://csi.gstatic.com/csi","?v=3","&s="+(window.jstiming.sn||"writely")+"&action=",a.name,h.length?"&it="+h.join(","):
"","",c,"&rt=",k.join(",")].join("");b=new Image;var m=window.jstiming.c++;window.jstiming.a[m]=b;b.onload=b.onerror=function(){delete window.jstiming.a[m]};b.src=a;b=null;return a}};
window.jstiming.load.name = 'published';
var urchinPage = "/View";
function getXHR() {
if (typeof XMLHttpRequest != "undefined") {
return new XMLHttpRequest();
}
try { return new ActiveXObject("Msxml2.XMLHTTP.6.0") } catch(e) {}
try { return new ActiveXObject("Msxml2.XMLHTTP.3.0") } catch(e) {}
try { return new ActiveXObject("Msxml2.XMLHTTP") } catch(e) {}
try { return new ActiveXObject("Microsoft.XMLHTTP") } catch(e) {}
return null;
}
function reportAbuse() {
var req = getXHR();
if (req) {
var docid = 'dfm6tmhk_326dntn97hr';
var posttoken = 'eH03vCgBAAA.sUdsDN7PiPavkQ7YFlqrsTbCU8tNF2Vh68ENXI9NfPA.FPwtoPGX-f5a_XWLfgZZzg';
req.onreadystatechange = function() {
try {
if (req.readyState == 4 && req.status == 200) {
var button = document.getElementById("report-abuse-button");
button.value = 'Thank you!';
button.disabled = true;
}
} catch (ex) {
}
}
try {
req.open('POST', 'MiscCommands', true);
req.setRequestHeader('Content-Type', 'application/x-www-form-urlencoded; charset=UTF-8');
req.send('command=report_abuse&abuseDoc=' + encodeURIComponent(docid) +
'&POST_TOKEN=' + encodeURIComponent(posttoken));
} catch (ex) {
}
}
}
-->
</script>Gerry Narvajahttp://www.blogger.com/profile/14092453914112673024noreply@blogger.com2tag:blogger.com,1999:blog-8007802080401497299.post-35616320047341053762010-05-03T12:27:00.000-07:002010-05-03T15:46:51.631-07:00Simple Backup Restore TrickI don't usually post these simple tricks, but it came to my attention today and it's very simple and have seen issues when trying to get around it. This one tries to solve the question: <i>How do I restore my production backup to a different schema?</i> It looks obvious, but I haven't seen many people thinking about it.<br />
<br />
<h2></h2>Most of the time backups using <b>mysqldump</b> will include the following line:<br />
<br />
<blockquote>USE `schema`;</blockquote><br />
This is OK when you're trying to either (re)build a slave or restore a production database. But what about restoring it to a test server in a different schema?<br />
<br />
<h2>The actual trick</h2><br />
Using <b>vi</b> (or similar) editors to edit the line will most likely result in the editor trying to load the whole backup file into memory, which might cause paging or even crash the server if the backup is big enough (I've seen it happen). Using <b>sed</b> (or similar) might take some time with a big file. The quick and dirty trick I like is:<br />
<br />
<blockquote>grep -v "USE \`schema\`" backup.sql | mysql -u user -p new_schema</blockquote><br />
Adapt the <b>mysql</b> command options to your needs. It's necessary to escape the backticks (`), otherwise the shell might interpret it as your trying to execute <i>schema</i> and use the output as the actual schema name. Also, make sure that <i>new_schema</i> already exists in the server.<br />
<br />
This method is quick and dirty and leaves the original backup intact. I hope you find it useful.Gerry Narvajahttp://www.blogger.com/profile/14092453914112673024noreply@blogger.com12tag:blogger.com,1999:blog-8007802080401497299.post-63673544042894640682010-03-26T16:55:00.000-07:002010-03-26T16:55:47.177-07:00My Impressions About MONyog<h1></h1>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 <b>MONyog</b> from <a href="http://webyog.com/en/" id="v5:g" title="Webyog">Webyog</a>, the makers of the better known <b>SQLyog</b>. Before we go on, the customary disclaimer: <i>This review reflects <b>my own opinion</b> and in no way represents any decision that my current employer may or may not make in regards of this product.</i><br />
<h2>First Impression</h2>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:<br />
<br />
For the OS user run the following command as <i>root</i> (thank you Tom):<br />
<blockquote>groupadd -g 250 monyog && useradd -c 'MONyog User' -g 250 -G mysql -u 250 monyog && echo 'your_os_password' | passwd --stdin monyog</blockquote>For the MySQL user run:<br />
<blockquote>GRANT SELECT, RELOAD, PROCESS, SUPER on *.* to 'adm_monyog'@'10.%' IDENTIFIED BY 'your_db_password';</blockquote>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. <br />
<br />
<h2>The User Interface</h2>The system UI is web driven using <a href="http://en.wikipedia.org/wiki/Ajax_%28programming%29" id="qtr1" title="Ajax">Ajax</a> and <a href="http://en.wikipedia.org/wiki/Adobe_Flash" id="z.js" title="Flash">Flash</a> 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.<br />
<br />
In order to access MONyog you just point your browser the server where it was installed with an URL equivalent to:<br />
<blockquote>http://monyog-test.domain.com:5555 <i>or</i> http://localhost:5555</blockquote>You will always land in the <a href="http://webyog.com/images/screenshots_monyog/ListMultipleServers.jpg" id="illx" title="List of Servers tab">List of Servers tab</a>. At the bottom of this page there is a <b>Register a New Server</b> 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 <a href="http://webyog.com/images/screenshots_monyog/NewConnection1.jpg" id="g6ls" title="screenshot">screenshot</a>). 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 <i>every</i> server, although there is an option to copy from previously defined servers, it can become a very tedious process.<br />
<br />
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 <b>Go</b>. 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.<br />
<h2>Features</h2>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.<br />
<br />
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.<br />
<br />
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 <b>ssh</b> 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.<br />
<br />
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.<br />
<h2>My Conclusion</h2>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.<br />
<br />
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.Gerry Narvajahttp://www.blogger.com/profile/14092453914112673024noreply@blogger.com2tag:blogger.com,1999:blog-8007802080401497299.post-54220393301618728062010-03-08T16:10:00.000-08:002010-03-08T16:10:11.740-08:00Speaking At The MySQL Users ConferenceMy proposal has been accepted, yay!<br />
<br />
I'll be speaking on a topic that I feel passionate about: <a href="http://en.oreilly.com/mysql2010/public/schedule/detail/13000">MySQL Server Diagnostics Beyond Monitoring</a>. MySQL has limitations when it comes to monitoring and diagnosing as it has been widely documented in several blogs.<br />
<br />
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.<br />
<br />
I will also be discussing my future plans on <b>sarsql</b>. 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.<br />
<br />
I'm excited about meeting many old friends (and most now fellow MySQL alumni) and making new ones. I hope to see you there!Gerry Narvajahttp://www.blogger.com/profile/14092453914112673024noreply@blogger.com1tag:blogger.com,1999:blog-8007802080401497299.post-81028961243881638612010-02-12T10:44:00.000-08:002010-02-12T10:44:54.796-08:00Log Buffer #178, a Carnival of the Vanities for DBAsDave Edwards has offered me to write this week's <a href="http://www.pythian.com/news/about-log-buffer" id="lmqh" title="Log Buffer">Log Buffer</a>, and I couldn't help but jump at the opportunity. I'll dive straight into it.<br />
<h2>Oracle</h2>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.<br />
<br />
Lets start with Hemant Chitale's Common Error series and his <a href="http://hemantoracledba.blogspot.com/2010/02/some-common-errors-2-nologging-as-hint.html">Some Common Errors - 2 - NOLOGGING as a Hint</a> explaining what to expect from NOLOGGING. Kamran Agayev offers us an insight into Hemant's personality with his <a href="http://kamranagayev.wordpress.com/2010/02/02/exclusive-interview-with-hemant-k-chitale/">Exclusive Interview with Hemant K Chitale</a>. My favorite quote is:<br />
<br />
<blockquote>Do you refer to the documentation? And how often does it happen? <br />
<br />
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.</blockquote>At least I'm not the only one forgetting the exact syntax of every command.<br />
<br />
Chen Shapira offers us her thoughts on diagnostics in <a href="http://prodlife.wordpress.com/2010/02/05/automated-root-cause-analysis/">Automated Root Cause Analysis</a>, 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.<br />
<div> </div>Miladin Modrakovic explains how to avoid an Oracle 11g vulnerability: <a href="http://oraclue.com/2010/02/05/oracle-exploit-published-11g-r2/">Oracle Exploit Published 11g R2</a>. Gary Myers makes his own contribution about security issues with 10g and 11g in <a href="http://blog.sydoracle.com/2010/02/exploits-and-revoking-risks-of-revoking.html">Exploits and revoking the risks of revoking PUBLIC</a>.<br />
<br />
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 <a href="http://iggyfernandez.wordpress.com/2010/02/06/not-the-sql-of-my-kindergarten-days/" id="uus6" title="Not the SQL of My Kindergarten Days">Not the SQL of My Kindergarten Days</a> quoting some references for those who would like to dig into the details.<br />
<div><br />
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, <a href="http://news.cnet.com/8301-13505_3-10448783-16.html" id="cg-z" title="Oracle loses some MySQL mojo">Oracle loses some MySQL mojo</a>, 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 <a href="http://planet.mysql.com/" id="pj70" title="Planet MySQL">Planet MySQL</a>'s feed.<br />
<h2>MySQL</h2></div>Continuing in the context of Oracle's acquisition and Ken's resignation, Bridget Bothelo's article <a href="http://searchenterpriselinux.techtarget.com/news/article/0,289142,sid39_gci1381097,00.html" id="m4kg" title="MySQL users hope for the best, prep for the worst">MySQL users hope for the best, prep for the worst</a> 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 <a href="http://mysqlguy.net/blog/2010/02/09/when-should-we-expect-next-stable-mysql-release-beyond-51" id="gg1m" title="When should we expect the next stable MySQL release beyond 5.1?">When should we expect the next stable MySQL release beyond 5.1?</a> and Ronald Bradford's FOSDEM 2010 presentation <a href="http://ronaldbradford.com/blog/beyond-mysql-ga-patches-storage-engines-forks-and-pre-releases-fosdem-2010-2010-02-11/" id="e-th" title="Beyond MySQL GA: patches, storage engines, forks, and pre-releases – FOSDEM 2010">Beyond MySQL GA: patches, storage engines, forks, and pre-releases – FOSDEM 2010</a>.<br />
<br />
Life goes on and in there is still plenty of action in the MySQL community. As Colin Charles reminds us in his <a href="http://www.bytebot.net/blog/archives/2010/02/10/mysql-conference-update-grid-is-up-go-promote-and-register?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+ColinCharles+%28colin+charles+blog%29" id="bkon" title="MySQL Conference Update: Grid is up, go promote and register!">MySQL Conference Update: Grid is up, go promote and register!</a>, this should be an interesting year. In the storage engine and tools front, it's worth checking InfiniDB's impressive performance numbers in <a href="http://infinidb.org/infinidb-blog/infinidb-load-60-billion-ssb-rows-trended.html">InfiniDB load 60 Billion SSB rows trended</a> for storage engine developments and <a href="http://www.heidisql.com/forum.php?t=4936#p5075">RE: HeidiSQL 5.0 Beta available</a> in the tools segments.<br />
<br />
Finally to end the MySQL section with some more mundane issues, here is a collection of articles with <b>mysqldump</b> related scripts and tools: <a href="http://thenoyes.com/littlenoise/?p=100">Restore from mysqldump --all-databases in parallel</a> and <a href="http://kedar.nitty-witty.com/blog/mydumpsplitter-extract-tables-from-mysql-dump-shell-script/">MyDumpSplitter-Extract tables from Mysql dump-shell script</a>. No list of articles on backups would be complete without asking <a href="http://shinguz.blogspot.com/2010/02/can-you-trust-your-backup.html">Can you trust your backup?</a>. <br />
<br />
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 <a href="http://blog.some-abstract-type.com/2010/02/fosdem-connecting-mysql-and-python.html">FOSDEM: 'Connecting MySQL and Python', handout & wrap-up.</a><h2>SQL Server</h2>Aaron Bertrand published 2 really interesting articles that could be apply to other databases as well: <a href="http://sqlblog.com/blogs/aaron_bertrand/archive/2010/02/04/bad-error-messages.aspx" id="dezy" title="When bad error messages happen to good people">When bad error messages happen to good people</a> and <a href="http://sqlblog.com/blogs/aaron_bertrand/archive/2010/02/07/injection-is-not-always-about-sql.aspx" id="lheb" title="Injection is not always about SQL">Injection is not always about SQL</a> with a funny (if it weren't for the Prius parked in my driveway) example at the end.<br />
<br />
2010 MVP Summit is coming up and Thomas LaRock offers his <a href="http://thomaslarock.com/2010/02/2010-mvp-summit-preview/" id="j_jy" title="2010 MVP Summit Preview">2010 MVP Summit Preview</a>. His insight applies to other similar events (are you reading MySQL UC attendees?).<br />
<br />
In my experience date and time representation and manipulation are tricky in databases, these 2 articles offer some tips: <a href="http://weblogs.sqlteam.com/peterl/archive/2010/02/08/Convert-FILETIME-to-SYSTEM-time-using-T-SQL.aspx">Convert FILETIME to SYSTEM time using T-SQL</a> and Dan Guzman's <a href="http://weblogs.sqlteam.com/dang/archive/2010/02/07/Ad-Hoc-Rollup-by-datetime-Interval.aspx">Ad-Hoc Rollup by date/time Interval</a><br />
<br />
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 <a href="http://sqlblog.com/blogs/adam_machanic/archive/2010/02/08/t-sql-tuesday-002-the-roundup.aspx" id="mqzg" title="T-SQL Tuesday #002: The Roundup">T-SQL Tuesday #002: The Roundup</a> to provide with a few technical references.<br />
<h2>PostgreSQL</h2>Apparently the PostgreSQL community need their own "Geert" (see reference in the MySQL section) based on what I've read on <a href="http://people.planetpostgresql.org/andrew/">Damn it feels good to be a (perl) gangsta</a> and Josh Berkus' <a href="http://it.toolbox.com/blogs/database-soup/postgres-needs-a-new-python-driver-36815" id="p.iq" title="Postgres needs a new Python driver">Postgres needs a new Python driver</a>. Are you up to the challenge? In that case, step up to the plate, that's what Open Source is all about.<br />
<br />
The PostGIS group had an important announcement:<a href="http://www.postgresonline.com/journal/index.php?/archives/151-PostGIS-1.5.0-out-and-PLR-working-on-Windows-8.3-8.4-installs.html"> PostGIS 1.5.0 out and PLR working on Windows 8.3-8.4 installs</a> which the author calls "Perhaps the best release ever", so make space on your disk and schedule and take it for a spin.<br />
<br />
Baron Schwartz offers an interesting view on <a href="http://www.xaprb.com/blog/2010/02/08/how-postgresql-protects-against-partial-page-writes-and-data-corruption/">How PostgreSQL protects against partial page writes and data corruption</a>. It offers great insight from a well known MySQL guru.<br />
<br />
Last but not least End Point's people have determined with mathematical precision <a href="http://blog.endpoint.com/2010/02/postgresql-version-90-release-date.html">PostgreSQL version 9.0 release date prediction</a>, make sure you read the article and get ready for it.<br />
<br />
I hope I kept you reading up to this point and see you around in the blogosphere.Gerry Narvajahttp://www.blogger.com/profile/14092453914112673024noreply@blogger.com0tag:blogger.com,1999:blog-8007802080401497299.post-27757476216815202512010-02-03T14:14:00.000-08:002010-02-03T14:14:12.949-08:00Using MariaDB with MySQL SandboxA few days back <b>MariaDB</b> announced their first GA release (see <a href="http://esslingen.homeunix.net/%7Ehakan/blog/2010/02/02/released-mariadb-5-1-42/" rel="bookmark">Released: MariaDB 5.1.42</a>), 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 <a href="http://www.mysqlsandbox.net/" id="y9_7" title="MySQL Sandbox">MySQL Sandbox</a>. However Sandbox relies on the fact that the tarball and tarball target directory are prefixed with <i>mysql</i>, which is not true with <b>MariaDB</b>. So here are the 2 tricks I had to use to make it work out of the box.<br />
<br />
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.<br />
<h2>Step 1: Use A Symlink For The Tarball</h2>The <b>make_sandbox </b>script will then think it's manipulating a MySQL tarball. Assuming that the default directory is where you have the tarball:<br />
<blockquote>ln -sv mariadb-5.1.42-Linux-x86_64.tar.gz mysql-5.1.42-Linux-x86_64.tar.gz<br />
make_sandbox /home/gnarvaja/Downloads/mysql-5.1.42-Linux-x86_64.tar.gz --sandbox_directory=maria_5.1.42</blockquote><br />
Make the adjustments needed to your own platform and version.<br />
<br />
The <b>make_sanbox </b>run is going to fail since it expects a subdirectory named <i>./mysql-5.1.42-Linux-x86_64 </i>which doesn't exist since we used a MariaDB tarball.<br />
<h2>Step 2: Use A Symlink For The MariaDB Binaries Directory</h2>For the same reason as above, now create a symlink for the directory to where the tarball was extracted and re-run <b>make_sandbox</b>:<br />
<br />
<blockquote>ln -sv mariadb-5.1.42-Linux-x86_64 mysql-5.1.42-Linux-x86_64<br />
make_sandbox /home/gnarvaja/Downloads/mysql-5.1.42-Linux-x86_64.tar.gz --sandbox_directory=maria_5.1.42</blockquote><br />
Remember to always include the <b>--sandbox_directory</b> option to avoid name conflicts in case you want to compare MariaDB with the corresponding MySQL release.<br />
<br />
This time the installation will succeed and you'll be ready to start your testing.<br />
<h2>Conclusion</h2>I tried to install using the original tarball name using different options and the process failed with different error messages. I looked into the <b>make_sandbox </b>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. <br />
<br />
Giusseppe, if you happen to see this blog, I'll be glad to test a patch when you have it.Gerry Narvajahttp://www.blogger.com/profile/14092453914112673024noreply@blogger.com1tag:blogger.com,1999:blog-8007802080401497299.post-58679612069760088422010-01-13T19:35:00.000-08:002010-01-13T19:35:40.338-08:00Ubuntu Karmic's Network Manager Issues<div>Since <b>Ubuntu 8.04</b> aka <b>Hardy Heron</b>, 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.<br />
<h2>The Symptom</h2>I came across the issue for the first time while trying <b>Ubuntu's Karmic Netbook</b> remix. After overcoming the typical Broadcom wifi driver, <b>Network Manager</b> would connect, but <b>Firefox</b> would fail to load the web pages 90% of the time. Using <b>ping</b> in the command line worked just fine. Maybe I needed to update the software packages to get the latest patches, surprise, <b>apt-get</b> was having similar problems and timing out. So the problem was deep in the OS layer.<br />
<br />
After a lot fiddling and some googling I found <a href="https://bugs.launchpad.net/ubuntu/+source/eglibc/+bug/417757" id="e7e4" title="bug #417757">bug #417757</a>: <br />
</div><blockquote><blockquote><div>[...] 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). [...]<span style="font-family: sans-serif;"><span style="font-size: x-small;"><br />
</span></span></div></blockquote></blockquote>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.<br />
<h2>The Solution(s)</h2>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.<br />
<h3>Disable IPv6</h3>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 <a href="https://bugs.launchpad.net/ubuntu/+source/eglibc/+bug/417757/comments/32">here</a>. To edit the <b>/etc/sysctl.conf</b> file use:<span style="font-size: x-small;"><br />
</span><blockquote><blockquote>sudo vi /etc/sysctl.conf<br />
</blockquote></blockquote>Replace <b>vi</b> with your editor of choice. Reboot before retrying the connection. <span style="font-size: x-small;"><br />
<br />
</span>You can try the setting without changing your system configuration or restarting the machine using the following command:<span style="font-size: x-small;"><br />
</span><blockquote><blockquote>sudo sysctrl -w net.ipv6.conf.all.disable_ipv6=1<br />
</blockquote></blockquote><h3>Use OpenDNS or Google DNS Servers<br />
</h3>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 <b>/etc/dhcp3/dhclient.conf</b> file using the following command:<br />
<blockquote><blockquote>sudo vi /etc/dhcp3/dhclient.conf<br />
</blockquote></blockquote>Add the following lines after the line starting with <i>#prepend</i>:<br />
<blockquote><blockquote># OpenDNS servers<br />
prepend domain-name-servers 208.67.222.222, 208.67.220.220;<br />
# Google DNS servers<br />
prepend domain-name-servers 8.8.8.8, 8.8.4.4;<br />
</blockquote></blockquote>Or if you want to use the GUI, you can follow the instructions in <a href="http://shibuvarkala.blogspot.com/2009/12/how-to-setup-google-public-dns-in.html">How to setup Google Public DNS in Ubuntu 9.10 Karmic Koala</a>, the instructions work with any of the IP addresses above. Once you apply these changes, restart your box and retry.<br />
<h2>The Editorial</h2>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.<br />
<br />
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. <br />
<br />
Then there are comment like <a href="https://bugs.launchpad.net/ubuntu/+source/eglibc/+bug/417757/comments/26" id="p:k6" title="this one">this one</a> which miss the point completely:<br />
<blockquote><blockquote><span class="foldable-quoted">[...] > You can't tell your grandmother to edit some config files because her internet is slow </span><br />
Does your grandmother use Ubuntu then? If so, then just help her out in fixing the issue :) [...]<br />
</blockquote></blockquote>This goes against what <a href="https://bugs.launchpad.net/ubuntu/+source/eglibc/+bug/417757" id="mgk6" title="bug #1">bug #1</a> is trying to address: Wider Linux adoption.<br />
<br />
My requests to the Ubuntu community are:<br />
<ol><li>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 <b>bug #1</b> will still remain unsolved long after Ubuntu's <b>Zippy Zebra </b>release (I made up the name).<br />
</li>
<li>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.<br />
</li>
</ol>I promise that on my next article I'll write about a MySQL topic.Gerry Narvajahttp://www.blogger.com/profile/14092453914112673024noreply@blogger.com2tag:blogger.com,1999:blog-8007802080401497299.post-22067832099343427012010-01-07T15:13:00.000-08:002010-01-07T15:13:20.752-08:00sar-sql Has A WikiFinally settled for a <a href="https://wiki.ubuntu.com/sarsql">wiki for <b>sar-sql</b></a> 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.<br />
<br />
Enjoy it with responsibility.<br />
<br />
PS: I use the Kubuntu format because it is my desktop of choice.Gerry Narvajahttp://www.blogger.com/profile/14092453914112673024noreply@blogger.com0tag:blogger.com,1999:blog-8007802080401497299.post-53604673500818891522009-12-14T15:49:00.001-08:002009-12-14T15:49:59.315-08:00<h2>A Hard Look Into Replication</h2><div>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.<br />
</div><h3>Seconds Behind Master</h3><div>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 <a href="http://dev.mysql.com/doc/refman/5.1/en/show-slave-status.html" id="p4q_" target="_blank" title="manua">manua</a>l:<br />
</div><blockquote>When the slave SQL thread is actively running<br />
(processing updates), this field is the number of<br />
seconds that have elapsed since the timestamp of the<br />
most recent event on the master executed by that thread.<br />
</blockquote><div>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: <b>Master_Log_File / Exec_Master_Log_Pos</b> vs. <b>Relay_Master_Log_File / Read_Master_Log_Pos</b>. 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.<br />
</div><div><br />
</div><div><b>Sidenote: </b>These are the variables in the slave snapshot in <b>sar-sql</b>, let me know which ones do you monitor to make your slaves are healthy.<br />
</div><h3>Binary Log Format</h3><div>This item is important and encompasses which format you choose for replication. In the case I am working on, it was set to <b>STATEMENT</b>. 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 <b>ROW</b> solved most of the delays.<br />
</div><div><br />
</div><div>Although <b>binlog_format</b> 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 c<i>onnection pooling </i>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.<br />
</div><div><br />
</div><div>Another issue that came up is that, in a replication tree, no matter what the <b>binlog_format</b> 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.<br />
</div><h3>Status Variables and Logs</h3><div>As you may know, S<b>HOW GLOBAL STATUS</b> includes a number of counters that count how many times a command type was issued. So <b>Com_Insert</b> will tell you how many <b>INSERT</b>s 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 <b>Com_Insert</b> 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.<br />
</div><h3>Conclusion</h3><div>Although I understand where these <i>limitations</i> 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.<br />
</div><div><br />
</div><div>I have to point out that <a href="http://www.mysqlsandbox.net/" id="j3l4" target="_blank" title="MySQL Sandbox">MySQL Sandbox</a> is an invaluable tool to test the different replication scenarios with minimum preparation work.<br />
</div>Gerry Narvajahttp://www.blogger.com/profile/14092453914112673024noreply@blogger.com5tag:blogger.com,1999:blog-8007802080401497299.post-11082195817148159052009-12-08T15:06:00.000-08:002009-12-08T15:06:40.700-08:00sar-sql New Alpha Release<a href="http://www.blogger.com/"></a><span id="goog_1260312891417"></span><span id="goog_1260312891418"></span>I just uploaded a new tarball for sar-sql containing a few bug fixes, overall code improvements. I also added options to get a partial snapshot of <i>SHOW SLAVE STATUS</i> and <i>SHOW MASTER STATUS</i>. I chose only a few columns to avoid over complicating the project.<br />
<br />
I plan one more round of heavy code changes, but no new features until I can stabilize the code enough to release it as beta.<br />
<br />
<br />
<div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">Feel free to visit the <a href="https://launchpad.net/mysar">project page in Launchpad</a> to comment on the <b>Blueprints</b>, report new <b>bugs</b> and participate through the <b>Answers</b> section.<br />
</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><br />
</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">Thank you very much to <a href="http://patg.net/">Patrick Galbraith</a> who provided some ideas on the best way to solve some of the coding issues.<br />
</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;"><br />
</div><div style="margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px;">Enjoy the <a href="http://launchpad.net/mysar/trunk/alpha-2/+download/mysar-alpha-2.tar.gz">download</a>.<br />
</div>Gerry Narvajahttp://www.blogger.com/profile/14092453914112673024noreply@blogger.com0tag:blogger.com,1999:blog-8007802080401497299.post-30009971010910992522009-12-02T18:20:00.000-08:002009-12-02T18:20:23.028-08:00About CSV TablesAs most of MySQL users, I have often ignored what I'd like to call the <i>minor</i> storage engines. MYISAM, InnoDB and NDB (aka MySQL Cluster) are well covered in several articles; but what about engines like CSV or ARCHIVE? As part of some internal projects, we have been playing around with these 2 with some interesting results. On this article I'll concentrate on CSV.<br />
<h3>Scenario</h3>Currently we have a few servers that are storing historical data that will eventually be migrated into Oracle. Two things need to happen until we can finally decommission them: 1) export the data to CSV so it can be imported in bulk into Oracle and 2) keep the data online so it can be queried as needed until the migration is finalized. I thought it would be interesting if we could solve both issues simultaneously and decided to try the CSV engine. Here's a description of the process.<br />
<h3>Understanding CSV Engine</h3>The CSV engine, as far as I can tell, was and example storage engine that was included with earlier MySQL versions to illustrate the storage engine API. Since the data is stored in plain text files there are some limitations that need to be considered before using it:<br />
<ol><li>No support for indexes</li>
<li>No NULL columns are allowed</li>
</ol><h3>Exporting Data To CSV</h3>So, my first step was to determine what would it take to export data from a regular table to CSV. These are the basic steps:<br />
<br />
<h4>1. Create the seed for the CSV table based on an existing table</h4><blockquote>CREATE TABLE test LIKE test_csv;<br />
</blockquote><br />
If you feel adventurous, use CREATE TABLE LIKE ... SELECT ... in which case you may be able to skip the next 2 steps. The engine for the new table will be redefined at the end of the process.<br />
<br />
<h4>2. Get rid of the indexes</h4><blockquote>ALTER TABLE test_csv DROP PRIMARY KEY, DROP KEY test_idx;<br />
</blockquote><br />
Modify this statement to include <b>all</b> existing indexes.<br />
<br />
<h4>3. Get rid of NULL columns</h4><blockquote>ALTER TABLE test_csv MODIFY test_char VARCHAR(10) NOT NULL DEFAULT '', MODIFY test_date TIMESTAMP NOT NULL DEFAULT '0000-00-00';<br />
</blockquote><br />
The DEFAULT values need to be reviewed so the application makes no mistake that these should be NULL. Numeric values could be tricky since you may not find a suitable replacement for NULL. Adapt to your particular case.<br />
<br />
<h4>4. Convert to CSV</h4><blockquote>ALTER TABLE test_csv ENGINE CSV;<br />
</blockquote><br />
This step will create an empty CSV file in the schema data directory. <br />
<br />
<h4>5. Export the data</h4><blockquote>INSERT INTO test_csv SELECT * FROM test WHERE ...<br />
</blockquote><br />
This would allow you to export the portion of the data from an existing table into the CSV table/file.<br />
<br />
At this point your data is all stored in a CSV file called <b>test_csv.CSV</b> under the data subdirectory that corresponds to the schema and the table can be queried as any other regular MySQL table, which is what I was looking for at the beginning of the project.<br />
<br />
You could even update the table. If you need to load this file to any other application, just copy the file.<br />
<br />
Keep in mind that we are talking about regular text files, so they are not adequate for big number of rows and frequent write operations. <br />
<h3>Importing Data From CSV</h3>If you have to import some CSV data from another application, as long as the data formatted properly, you can just create an empty table with the right columns and then copy the CSV file with the proper table name. Example:<br />
<br />
In MySQL:<br />
<blockquote>use test<br />
CREATE TABLE test_import LIKE test_csv;<br />
</blockquote>In the OS shell:<br />
<blockquote>cp data.csv /var/lib/mysql/test/test_import.CSV<br />
</blockquote>Now if you do: SELECT * FROM test_import LIMIT 2; it should show you the data on the first 2 lines of the CSV file.<br />
<br />
<h4>Import Data Example</h4>Many banks allow you to export the list of transactions from your online statement as a CSV file. You could easily use this file as explained above to consult and/or manipulate the data using regular SQL statements.<br />
<h3>Conclusion</h3>The CSV engine provides a very flexible mechanism to move data in and out of regular text files as long as you have proper access the data directories. You can easily generate these tables from existing data. You can also easily manipulate the data with any editor and (re) import it at will. I see it as an invaluable tool to move around information, especially in development and testing environments. <br />
<br />
Do you have interesting use cases? I'd like to hear about them.Gerry Narvajahttp://www.blogger.com/profile/14092453914112673024noreply@blogger.com0tag:blogger.com,1999:blog-8007802080401497299.post-3553277216777900072009-11-24T21:04:00.001-08:002009-11-24T21:12:52.697-08:00ZRM for MySQL IssuesI really like <a href="http://www.zmanda.com/backup-mysql.html" id="s:ve" target="_blank" title="ZRM for MySQL Community Edition">ZRM for MySQL Community Edition</a> for backups. I find<br />
it very simple to install and configure and it gets the job done. It's default settings suits most installations. The challenges arise once you need to adapt it to more complex organizations. Please note that all the issues described on this article refer to Linux installations.<br />
<h3>What If DBAs Have No sudo?</h3>This was the 1st challenge that I ever faced. ZRM requires full <b>root</b> access at least during installation and in some cases, it requires some sudo permissions to be able to manage its files as well. Some organizations don't grant these permissions to the DBAs complicating the installation. When I looked into the scripts, I didn't see any reason why they wouldn't run in the user space, other than a few hard coded paths. I posted the question in the Zmanda forums and I got an answer with some instructions on how to patch the scripts. The instructions are pretty straight forward, but I haven't had a chance to test them. May be someone else could try and post the results as a comment? Just follow the link to the <a href="http://forums.zmanda.com/showthread.php?p=7771#post7771" id="a98l" target="_blank" title="original post">original post</a> and read through the thread.<br />
<h3>Install From RPM or Tarball?</h3>So far I have always used the tarball since it offers the most flexibility. However, this requires to properly set up permissions for the different directories and subdirectories. Although the <a href="http://wiki.zmanda.com/index.php/File_Locations" id="npcr" target="_blank" title="location of the main files">locations of the main files</a> are listed in the documentation, the tarball includes a script (run automatically if you use the <b>deb</b> or <b>rpm</b> packages) that sets the ownerships properly. Example:<br />
<blockquote>sudo mysql-zrm-migrate-file-ownership --user mysql --group mysql<br />
</blockquote>For multi-DBA organizations, you can easily modify the script to also grant group access to the directories using <b>chmod</b> as needed.<br />
<h3>More on Permissions</h3>Once the right access permissions have been set properly, the scripts will<br />
run OK, however a new problem arises. No matter how you set the<br />
permissions, the directory holding the backup files will only be created with user access. With the example above it means that only the user <i>mysql</i> will have access, effectively leaving the DBAs with no proper access to the files and reports even if they are included into the <i>mysql</i> group. I haven't been alble to locate where to patch the scripts properly to avoid this issue. For now, writing a wrapper that will include the lines:<b>chmod<br />
-R g+rx /path/to/backups</b> after the backup works as an easy solution.<br />
<h3>Crontab Entries</h3>The most traditional method to run any process periodically is to use <b>crontab</b>, but modern distributions are favoring the use of files in the <b>/etc/cron*</b> subdirectories that will be run by <b>run-parts</b>. Chances are your system already has some daily tasks scheduled, which can be listed using:<br />
<blockquote>run-parts --list /etc/cron.daily/<br />
</blockquote>The security in many of these modern systems don't allow the use of <b>crontab</b> anymore. In these cases the <b>mysql-zrm-scheduler</b> utility can't be used. In it's place you'll need to create a file in <b>/etc/cron.d</b> (or a wrapper script in the <b>/etc/cron.daily</b> sub directory) containing the proper entry in a format similar to the traditional crontab. The main difference is that you need to specified the user it will use to execute. A typical file would look like:<br />
<blockquote># /etc/cron.d/zrm-backup: crontab entries for mysql-zrm<br />
<br />
SHELL=/bin/sh<br />
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin<br />
# add other environment variables you might need, like: PERL5LIB<br />
<br />
30 1 * * * mysql mysql-zrm --action backup --backup-set backup<br />
</blockquote><h3>Conclusion</h3>ZRM for MySQL continues to be one of my favorite MySQL tools. None of these issues are showstoppers, however the fact remains that Zmanda hasn't released any major update to these scripts in a long time and it's starting to show. If they don't do something about it, someone else will stealing their thunder. Such is the nature of Open Source.Gerry Narvajahttp://www.blogger.com/profile/14092453914112673024noreply@blogger.com1tag:blogger.com,1999:blog-8007802080401497299.post-5451696579100020132009-11-06T15:34:00.001-08:002009-11-06T15:52:51.725-08:00My MySQL Tool ChestEvery time I need to install or reconfigure a new workstation, I review the set of tools I use. It's an opportunity to <i>refresh</i> the list, reconsider the usefulness of old tools and review new ones. During my first week at <a href="http://www.openmarket.com/">Open Market</a> I got one of these opportunities. Here is my short list of free (as in 'beer') OSS tools and why they have a place in my tool chest.<br />
<h3>Testing Environments<br />
</h3><h4>Virtual Box</h4><br />
Of all the Virtual Machines out there, I consider <a href="http://www.virtualbox.org/" id="qxjf" target="_blank" title="Virtual Box">Virtual Box</a> to be the easiest to use. Since I first looking into it while I was still working at Sun/MySQL, this package has been improved constantly. It's a must have to stage High Availability scenarios or run tools that are not available in your OS of choice.<br />
<h4>MySQL Sandbox</h4>Did you compile MySQL from source and want to test it without affecting your current installation? Will replication break when you try a new feature? Will the upgrade work as expected? There is no other way to easily test this other than <a href="http://www.mysqlsandbox.net/" id="ug23" target="_blank" title="MySQL Sandbox">MySQL Sandbox</a>. It's a must have for anyone working with MySQL regularly.<br />
<br />
<h3>Backup</h3><h4>ZRM for MySQL</h4>Many people have asked me why do I always suggest going this way when using (insert tool of preference) gets the job done. <a href="http://www.zmanda.com/backup-mysql.html" id="gtwx" target="_blank" title="ZRM for MySQL">ZRM for MySQL</a> has plenty of features that go beyond taking the actual backup, making it a breeze to actually manage the backup sets. In most cases if you use (insert tool of preference), you are still left with the additional tasks around the backups (ie: scheduling, rotation, copying backup off site, backup reports, etc). Why reinvent the wheel?<br />
<br />
<h4>Tuning</h4><br />
These are simple scripts that can quickly give you an overview of the current status of any MySQL server and assist you in making proper adjustments to improve efficiency.<br />
<br />
<h4>mysqlsla</h4><br />
I like to call <a href="http://hackmysql.com/mysqlsla" id="rrj:" target="_blank" title="mysqlsla">mysqlsla</a> the <i>Slow Query Reality Check</i>. I found that many times developers and DBAs start scanning the slow query log to find the slowest queries and start optimizing them to increase overall performance. Many fail to recognize that quick queries that are run hundreds or thousands of times in a short period of time, can have a much greater impact on performance than a dozen complex long running ones. <b>mysqlsla</b> can scan the query log, slow or general, and rank the queries based on accumulated run and lock times (among other values). This way it's easy to identify the the queries that will really impact overall performance. It might be a "SELECT COUNT(*) FROM table WHERE status = ?" instead of a query with a 5 table JOIN.<br />
<h4>mysqltuner</h4>Running <a href="http://blog.mysqltuner.com/" id="i2vi" target="_blank" title="mysqltuner">mysqltuner</a> is like taking a <i>physical exam</i> of a MySQL server. Whether you do it the first time you get into a server or after any changes to its configuration and/or environment. The script will very quickly point to the <i>low hanging fruit</i> in terms of configuration parameters. The most common issue I've caught with it is memory over allocation. This is a nasty situation that, by its very nature, if undetected it will show up in the very worst moment: under heavy load.<br />
<h4>mytop</h4><a href="http://jeremy.zawodny.com/mysql/mytop/" id="yxds" target="_blank" title="mytop">mytop</a> will show you in real time what is going on in the server. Doing load tests? Trying to catch deadlocks? Fire up your test case while keeping an eye on mytop's screen.<br />
<br />
<h3>Other</h3><h4>MySQL Workbench</h4>At this point, I haven't been able to find any tool, other than <a href="http://dev.mysql.com/doc/workbench/en/index.html" id="mrau" target="_blank" title="MySQL Workbench">MySQL Workbench</a>, to get proper DB diagrams for MySQL schemas. The ideal situation would be that every DBA would have these diagrams accessible, but the truth is, they rarely exist.<br />
<h4>sar-sql</h4>I know, this is beating my own drum, but it works and combined with some other tools, it can provide a great deal of information with negligible overhead. I wish I had more time to write about more use cases.<br />
<h3>Wildcard</h3><h4>myterm</h4>I just read about <b>myterm</b> in a <a href="http://www.jetprofiler.com/blog/8/myterm---extensible-mysql-command-line-client/" id="x8:g" target="_blank" title="recent blog">recent blog</a>. I am really intrigued by it, but haven't had any time to test it. If it works as advertised, it is a great companion to <b>sar-sql</b>.Gerry Narvajahttp://www.blogger.com/profile/14092453914112673024noreply@blogger.com0tag:blogger.com,1999:blog-8007802080401497299.post-64428284885984924102009-10-29T21:33:00.001-07:002009-10-29T21:34:40.854-07:00About This BlogIf you are wondering about the title, it makes reference to a special tea very popular in Argentina: <a href="http://www.google.com/url?q=http%3A%2F%2Fen.wikipedia.org%2Fwiki%2FYerba_mat%25C3%25A9&sa=D&sntz=1&usg=AFrqEzfglbJwst7JipqrDktNuzN54wpzSQ" target="_blank">yerba mate</a> (<a href="http://www.google.com/url?q=http%3A%2F%2Fwww.merriam-webster.com%2Fdictionary%2Fmate&sa=D&sntz=1&usg=AFrqEzfZyDtnGTfLWCdi4lJuabjLYnIPZA" target="_blank">maté</a> \ˈmä-ˌtā\ in English). In Argentina, <i>matear</i> (drinking <i>mate</i>) is a social activity, someting you often do among friends sharing stories. To avoid confusion, I'll write <i>mate</i>in italics in rest of this article.<br /><h3>In Between <i style="font-weight: normal;">Mates</i></h3>In Spanish, <i>entre mate y mate</i> refers to the stories that are shared in a circle of friends while drinking the infusion. The person who has the <i>mate</i>, always makes a pause in the conversation, sip the <i>mate</i> and pass it back to the <i>cebador</i> before continuing with the conversation. Everyone else will patiently wait and keep listening while themselves sip from the <i>mate </i>when it's their turn. The conversation then takes place, in between mates: <i>entre mate y mate</i>.<br /><h3>MySQL Mates</h3>This refers to a different topic. This blog will be mostly about MySQL but, as I have been doing for while, I will cover topics and packages that are part of the MySQL ecosysten. In the past I have posted articles about MySQL Sandbox, ZRM for MySQL and SphinxSE as well as topics that, although not MySQL specific, they affect it in one way or another. In short, MySQL companions or "mates".<br /><br />This blog is intended then as a conversation between you, reader and myself. I don't pretend to know everything and I expect to learn from your comments.<br /><br />See you around.Gerry Narvajahttp://www.blogger.com/profile/14092453914112673024noreply@blogger.com0