Sheeri dot com

I found Baron’s reasoning on why the Percona Nagios plugins do not use caching interesting. On the surface, the logic is sound – you do not want to cache when you want real-time monitoring.
I have not yet had time to look at the Percona plugins for Nagios, though I do want to, because back at PalominoDB I helped write a Nagios plugin for MySQL that allows you to do arbitrary calculations. By “arbitrary calculations” I mean you can have a calculation like “Threads_connected/max_connections*100″ and set a threshold of “>80″. You can mix and match MySQL status variables and system variables, and use any perl functions as well, including basic arithmetic.
We put a caching function in there, complete with examples. Why would we do that if caching is bad, as Baron says? Well, long-term caching is bad, but it is certainly acceptable to have caching with a threshold lower than the check interval. With the PalominoDB Nagios plugin for MySQL, you can have 10 different calculations, and set the cache threshold on a per-check basis – the examples use 60 seconds.
If I have 10 calculations, I can set it so the checks do not re-connect to MySQL if there is a file that’s less than 60 seconds old. With checks that run every 5 minutes by default, it makes complete sense to cache the first run of SHOW STATUS/SHOW VARIABLES/SHOW PROCESSLIST, and the other 9 checks use the cache file – but only if it fresh within the past minute.
It is quite likely that the Percona Nagios plugins for MySQL do not lend themselves to this type of caching. Because the PalominoDB Nagios plugin is so powerful, it makes sense to have this type of micro-caching. Otherwise, each additional monitoring check adds more strain on the database.

Edited to add: I just took a look at the Percona monitoring plugins for Nagios and they check very, very different things. The level of customization is not as flexible as it is with the PalominoDB Nagios plugin, because it is checking very different things. The Percona monitoring plugins are a set of 12 different checks, as opposed to the 1 check that the PalominoDB plugin has. Still, I could see a value in caching the output of SHOW ENGINE INNODB STATUS (or whatever it uses) for the pmp-check-mysql-innodb plugin, so you can run it three times - once with the idle_blocker_duration option, once with the waiter_count option, and once with the max_duration option, and it only runs the command needed once.

Basically it comes down to this: Baron is correct when he says "Running SHOW STATUS infrequently doesn’t add load to the server." and I am correct when I say "Running SHOW STATUS frequently adds a lot of load to the server." The Percona plugin and the PalominoDB plugin are completely different, and there seems to be very little overlap.

I have been at Mozilla nearly three months, and I used to blog a lot more than I currently do. A lot of the content I used to blog about I end up blogging and talking about in OurSQL: The MySQL Database Community Podcast. And I have also been getting used to the Mozilla firehose, as well as my own firehose of database projects that need to be done.
There are two very large projects that are time-sensitive that I am working on: migrating databases from an older data center to a newer one, and the impending public launch of the Mozilla Apps Store.
That being said, this week in Mozilla databases we have:
- migrated/improved/built our dev/stage databases for Socorro, our crash stats database.
- put monitoring on a newer backup server, after a random check showed replication had been stopped on one backup instance for several days due to the master’s binary logs changing names. Of course we also fixed that broken replication.
- made more progress getting the newer backup server to act like the older backup server – we do physical and logical backups, and currently the logical backups are working properly. The physical backups are a legacy cold backup, and I will not be migrating that, instead opting to use xtrabackup.
- turned off our Scalarc software, as we now have an appliance for our proof-of-concept test.
- retired 2 machines that were not in use, and exist in our older data center. I am always paranoid when I shut a machine down, triple-checking that I am on the right server when I type “shutdown now”.
- did a test migration of the production database for Mozilla QA, from the old data center to the new one.
- added new custom fields to Bugzilla for the release of Thunderbird 10.
- Created new databases and access for:
– Case Conductor, a replacement for Litmus for the QA team
– De Todos Para Todos, Mozilla’s outreach project to Latin America.
There is much much more to come in the weeks ahead!

I have been at Mozilla nearly three months, and I used to blog a lot more than I currently do. A lot of the content I used to blog about I end up blogging and talking about in OurSQL: The MySQL Database Community Podcast. And I have also been getting used to the Mozilla firehose, as well as my own firehose of database projects that need to be done.

There are two very large projects that are time-sensitive that I am working on: migrating databases from an older data center to a newer one, and the impending public launch of the Mozilla Apps Store.

That being said, this week in Mozilla databases we have:

- migrated/improved/built our dev/stage databases for Socorro, our crash stats database.

- put monitoring on a newer backup server, after a random check showed replication had been stopped on one backup instance for several days due to the master's binary logs changing names. Of course we also fixed that broken replication.

- made more progress getting the newer backup server to act like the older backup server - we do physical and logical backups, and currently the logical backups are working properly. The physical backups are a legacy cold backup, and I will not be migrating that, instead opting to use xtrabackup.

- turned off our Scalarc software, as we now have an appliance for our proof-of-concept test.

- retired 2 machines that were not in use, and exist in our older data center. I am always paranoid when I shut a machine down, triple-checking that I am on the right server when I type "shutdown now".

- did a test migration of the production database for Mozilla QA, from the old data center to the new one.

- added new custom fields to Bugzilla for the release of Thunderbird 10.

- Created new databases and access for:
- Case Conductor, a replacement for Litmus for the QA team
- De Todos Para Todos, Mozilla's outreach project to Latin America.

There is much much more to come in the weeks ahead!

I have been attempting to be better about letting folks in the general MySQL world know when the videos from the Boston MySQL User Group are up. We have a great group, with anywhere from 20-50 attendees each month, and the speakers are always great, even when the group asks some tough questions.* Thanks to the generous donation of time that Richard Laskey puts in editing the videos, there are 2 videos I can share with you:

Ivan Zoratti of SkySQL presented MySQL HA Reloaded at the December MySQL Boston Users Group. Slides are online at http://www.slideshare.net/izoratti/ha-reloaded-11261643

Tim Callaghan of Tokutek presented Fractal Tree Indexes -- Theoretical Overview and Customer Use Cases at the January MySQL Boston Users Group.

The next meeting is Monday, February 13th from 7-9 pm and will feature "Scaling MySQL with Transparent Database Sharding", by Victoria Dudin of ScaleBase. RSVP for the free meeting here or wait until the video comes out, if you are not local.

* The group is really great about RSVP'ing too -- we usually get the approximate number of folks RSVP'd actually showing up. I recently went to a local user group that had over 100 people RSVP' and only about 1/3 of them showed up. I guess if you know the pattern you know how to 'read' the numbers, but as a speaker I would have been disappointed to expect an audience of 100 and have an audience of 30.

Then again, maybe the speaker was informed of this...I have no clue. I guess what I am trying to say is that I really appreciate the Boston group!

Mark Callaghan speaks at the New England Database Summit about how data manageability is more important than performance.

Peak performance is thrown out, 95%-98% is important.

Variance shouldn't be large.

Data manageability is rate of interrupts per server for the operations team. Rate of server growth much bigger than rate of new hires for the systems teams. A lot of the db team is from University of Wisconsin-Madison!

Why MySQL? Because it was there when he came. Mark and ops/engineering peers made it scale 10x. He likes MySQL for OLTP, InnoDB is "An amazing piece of software."

They can get 500,000 qps using a cached workload, which is on par with memcached.

What Facebook really does is OLTP for the social graph. The workload is secondary indexes, index-only queries, small joins but most queries use one table, multi-row transactions, majority of workload does not need SQL/optimizer, they do a physical and logical backup.

Most of this does not require SQL [blogger's note - they built Cassandra]. Why is the grass greener on the other side? automated replacement of failed nodes, less downtime on schema changes and/or fewer schema changes, multi-master, better compressions, etc.

Circa 2010, 13 million queries per second, 4 ms reads, 5 ms writes, 38GB peak network per second, etc.

Why so many servers? Big data high queries per seconds. They add servers to add IOPS, so they're interested in compression and flash, so they can get more IOPS. If they do remain on disk, write-optimized dbs are interesting too. About 10 people on the db team, which is very small for a company that size.

How to scale MySQL? Fix stalls to make use of capacity, improve efficiency to use fewer queries/fewer data. Fixing stalls doesn't make MySQL faster, makes it less slow.

[blogger's note - I stopped taking notes here because this is a rehash of the "How Facebook Does MySQL" talk that has been done over and over...]

[restarted when he started talking about data manageability again]

How Facebook got it's data manageable.

pylander- sheds load during a query pileup - kills dup queries, limits # of queries from some specific accounts -- take off on Highlander: there can be only one.

dogpile - collects data during a query pileup - gets perf counters and list of running queries, generates HTML page with interesting results.

Online schema change tool, for frequent schema changes, especially adding indexes. This briefly locks the table, to setup triggers to track changes, copy data to a new table with the new desired schema, replay changes to the new table, then briefly lock the table again as you rename the new table as the target table.

Manageability is a work in progress -- working on:
- make InnoDB compression work for OLTP
- Faker - tool for prefetching for replication slaves - replay workload is: page read, do some modification, page write. bottleneck might be disk reads, work is done by a single thread, transactions on master are concurrent. Faker has multiple threads replay transactions in "fake-changes" mode, no undo, no rollback, read-only, fetches into the buffer pool the pages needed for that transaction. Captures about 70% of disk reads for replication, they're working on fixes to get it up to 80-90%.
- auto replacement - replace failed and unhealthy MySQL servers.
- Auto resharding - sharding is easy, re-sharding is hard.

open issues in manageability:
diagnose why one host is slow, others are not.
....and some more.

I am honored to have been selected to give a 3-hour tutorial on white-hat Google hacking and other MySQL security topics at the 2012 Percona Live: MySQL Conference and Expo. The tutorial schedule can be found here and for more about the presentation I will be giving, you can see Google Hacking MySQL and More MySQL Security. I am also pleased that Percona has given a discount code for OurSQL: The MySQL Database Community Podcast listeners - 10% off any ticket price with code PL-pod.

The discount is on top of early bird prices, so you get two discounts this way!

I cannot wait for the conference -- all the tutorials and sessions look very top-notch.

Yesterday, during a session at a User Group Leader's conference, I suggested to the MySQL Community Team (Keith Larson and Dave Stokes) that it would be nice to see all the events that Oracle does that are MySQL-related, because everyone else is posting their MySQL events to Planet MySQL and Oracle was talking about events I had never heard of. I noted that http://events.oracle.com/search/search?group=Events&keyword=mysql has an RSS feed.

Well, Keith and Dave also thought that was a good idea, so they added the feed. Looks like Oracle's feed isn't so great, though, and they're working on the fix with the appropriate tech folks within Oracle.

I think it is ridiculous that some folks say things like "I hope planet mysql has not been hijacked!". Of course it has not -- this is a silly technical issue, which is pretty obvious once you think about it -- when the feed was added, it added the most recent posts. It looks like those posts might be in the future or something.

It's just a broken RSS feed, folks. Why is everyone so quick to try to accuse Oracle of bad stuff? They certainly have not been perfect, but they have done a lot of good, sprinkled in with some bad stuff here and there. Just take a deep breath, relax, and think about e-mailing the community team when that happens.

That's what I did.

This week I have been at the IOUC User Group Leader's Conference, and I have met a ton of great folks who are user group leaders and made some great contacts for future speaking engagement. Follow this space to learn about calls for papers for international conferences! First up is the call for papers for the OUG Harmony conference.

The OUG is the Oracle Users Group for Finland and Latvia, in conjunction with Estonia and Russia. The Finland conference is specifically looking for MySQL content, and is May 30-31st in Hämeenlinna, Finland. Talks can be in Finnish or English, and they're looking for good basic MySQL information.

I spoke today at the conference about the MySQL community - who it is, how it's grown, and what the challenges are as we try to find our place under the Oracle banner. Slides are at http://bit.ly/mysqlcomm2012.

I have to say that I love doing slides in HTML and CSS, as I'm already very familiar with it, and I don't have to worry about a separate Office program. I marvel at how open Mozilla is, and you too can make slides as I did, though I'd recommend changing the CSS so you can represent your company. It took me about 5 minutes to figure out how to work things, thanks to our public documentation at https://wiki.mozilla.org/HTML_Slides -- which, by the way, I found by Google searching for "Mozilla slides".

Note that anyone can develop slides on Mozilla's site or export it yourself to work on offline...I found it handy to develop the slides right on the Mozilla type, and whenever I saved it the display version automatically updates. Pretty nifty stuff.

Last week, Baron pointed out that semi-synchronous replication is not synchronous. I learned a lot reading that post, but I was surprised it was used to pimp the Percona cluster, with no comparison to MySQL's own cluster solution -- that would be a much more fair comparison. There is one critical point Baron did not make, though....

whether it's semi-synchronous replication or regular asynchronous replication, there is no guarantee of data integrity. I saw this over and over when I was consulting. Just because replication is not failing does *not* mean that the data on the master and slave are in sync.

There is no form of replication that verifies data integrity. You can check if the data on the slave is in sync with the data on the master with pt-table-checksum and pt-table-sync, from the Percona toolkit. I use those tools widely.

I have not yet started using the new version, which boasts just working out of the box -- right now there are many options I use, in addition to the ones listed in the blog post that even I reference to this day I have also started using --chunk-size-limit, to avoid pt-table-checksum skipping chunks that are just a bit too large.

I am excited about the rewrite of the tools and have it in my plan to use them. I hope they will save me a lot of time.

In my mind, checksumming is as critical as backups (in fact, if you backup from a slave, you must verify that the slave is in sync with the master and has no data integrity issues). It is not optional. Hopefully you already know that replication does not verify data integrity, but if you did not know, now you know and you also know how to check for that.

What I do is have the checksum run with the modulo/offset feature every so often on the master, and within a week or 2 of a data discrepancy happening, I find it, using a daily monitoring check to see if the checksum table on the slave has matching values for the data on the master and the slave.

Many of us know what it's like to work at an open source company. About 6 weeks ago I started my job as a Senior MySQL DB Admin/Architect (DBA but the "A" stands for both) at Mozilla. And I have to say, working for an open company is a lot different from working for an open source company.

There's so much more that's, well, open.

I can point to the Bugzilla bugs database, where all our ticket tracking is done. It's open to the public, although on the systems side we mark a lot of bugs private because they contain important information like hostnames and IP addresses and what ports are open vs. not.

Or I could point to my director's quest to make the IT department more open - one that I think is possible, although it does make our legal team try to figure out exactly what constitutes an employee and what does not (or rather, what could be argued in a court of law if it comes down to that).

The greatest example that I have seen of how open the company is came from a coworker's blog about our anti-SOPA/PIPA efforts - the blog points to an etherpad document used to collaborate on what needed to be done. You can see that it was a lot of work, but the part that stands out to me is this:

You can see it.

It's amazing to me how open the company is, and how very little suffers from it. Plans are divulged, there are very few secrets -- most of those are required by the folks we work with, not by Mozilla itself.

Today, English versions of major sites like Wikipedia and Mozilla (my employer) are going dark to protest SOPA and the PROTECT IP Act. If you think SOPA/PROTECT IP Act is not a big problem, or if you are having trouble explaining why it is a problem to folks who are not as aware, a post by Mitchell Baker should help clear things up. Baker illuminates the problem, complete with references. You should read the article, it is not very long, but here is a snippet:

"Assume there’s a corner store in your neighborhood that rents movies. But the movie industry believes that some or even all of the videos in that store are unauthorized copies, so that they’re not being paid when people watch their movies. What should be done?

SOPA/PIPA don’t aim at the people trying to get to the store. SOPA/ PIPA don’t penalize or regulate the store itself. SOPA and PIPA penalize us if we don’t block the people trying to get to the store.

The solution under the proposed bills is to make it as difficult as possible to find or interact with the store. Maps showing the location of the store must be changed to hide it(1). The road to the store must be blocked off so that it’s difficult to physically get to there(2). Directory services must unlist the store’s phone number and address(3). Credit card companies(4) would have to cease providing services to the store. Local newspapers would no longer be allowed to place ads for the video store(5). And to make sure it all happens, any person or organization who doesn’t do this is subject to penalties(6). Even publishing a newsletter that tells people where the store is would be prohibited by this legislation(7)."

(footnotes left out, this is just a copy/paste, click through to to the article to see the notes).

Thanks to the extraordinary work of volunteer superstar Richard Laskey, the videos for the September, October and November Boston MySQL user group meetings are available. Whether you want to encrypt, renormalize or learn how MTV uses MongoDB, there are now full-length videos available:

MongoDB @MTV by Jeff Yemin of MTV Networks, September 2011

Renormalize - Solving Performance Problems in MySQL without Denormalization by Ari Weil of Akiban, October 2011.

MySQL Encryption with Gazzang by Mike Frank of Gazzang, November 2011.

Enjoy the videos!

My first week at Mozilla was relatively uneventful. I spent it at the Mountain View office, meeting people in person, having a few meetings, and actually doing a few tasks in addition to all of the setup and overhead that comes with being a new employee.

After traveling back home to Boston, my second week of work was a bit more eventful. We had a RAID array fail when we went to replace a disk. The RAID array held all of our e-mail, so until we could restore a backup and work on getting as much post-backup data we could out of corrupt databases (LDAP and MySQL), nobody had e-mail.

E-mail is a very big deal in any company, and Mozilla, with around 600 employees, is no exception. It was fascinating to watch my new coworkers deal with a crisis of this magnitude. A person's true self is brought out under pressure, and I got to see how everyone acted and reacted.

Folks who wanted to help but could not stepped aside to let those with the requisite knowledge/experience fix things. Higher-ups asked for statuses. Employees found ways to communicate without e-mail.

I watched this all unfold with an observant eye - especially within my team. I am happy to learn that my coworkers (including my boss) are smart, helpful, know when to jump in and when to stay out of the way, sensitive to those who are under pressure, do not spend time rat-holing on the past, and avoid the blame game.

Of course, as Tim Callaghan pointed out at the December Boston MySQL User Group on Monday evening, what would I have done if I found out that my team was a bunch of finger-pointers, my boss was a jerk, and everyone complained selfishly? Luckily, that's not a scenario I have to worry about.

Sheeri's feed from the Mozilla.com IT blog

I have been the Community Liaison and a Senior DBA at PalominoDB for 15 months, and doing remote DBA work for 4 years.  In that time I have learned that "consultant" need not be a dirty word, and that in the DBA world it is actually extremely valuable to have a remote DBA with lots of outside experience, and a team of remote DBAs for when your primary contact is sick or goes on holiday.As with everything, there are downsides to remote database management.  Even though there is a lot of architecture experience among the remote DBAs I know, we are not often invited to architecture meetings.  This is because time is the unit of currency, and while sitting in an hour-long meeting to give 5 minutes of feedback can save hours down the road, it's hard to see that.  Many clients have gotten around this by having all DDL checked and performed by remote DBAs, and that helps a lot.There is also no ownership - we can recommend solutions and technologies, but the client makes the actual decision about whether something needs to be done or not.  I look forward to actually owning architecture after 4 years of making "strong recommendations".Since folks will ask, I have taken a job as a Senior DBA/Architect with Mozilla, starting Monday.  A former co-worker told me about the job; I was not particularly looking for anything, but I was intrigued.I have said before that it is hard to find a good in-house DBA if you are not a huge company like Facebook or Google or Yahoo, and that is still true.  At Mozilla, they are 100% open and public about their ideas, and they do a lot of behind-the-scenes work.  Sound familiar?They also allow their developers to develop on whatever platforms work best.  Their biggest database is their crash reporting database (and they do read it, so do submit your crashes).  They have MySQL, PostgreSQL, and MongoDB, and are starting to move some applications around, as developers are not always aware of what platforms will work best.  There is another DBA, so I will not be alone, but I expect to be just as challenged at Mozilla as I have been at PalominoDB and Pythian.So, to keep up with PalominoDB, you can:- like the PalominoDB page on Facebook- follow @palominodb on Twitter- connect with Laine on LinkedIn- follow PalominoDB on LinkedIn- continue to read PalominoDB's blog and Planet MySQL To keep up with me, you can:- follow @sheeri on Twitter- read my blog and Planet MySQL- connect with me on LinkedIn- subscribe to the OurSQL podcast (details at http://www.oursql.com/?page_id=2)

Our resident Postgres expert Emanuel spoke at the Libre Software World Conference last week about PostgreSQL 9.1, and as the Community Liasion for PalominoDB for a few more days, I am posting the slides so that folks can download them.  They are in PPT format:

The New England Database Summit is an all day conference-style event where participants from the research community and industry in the New England area can come together to present ideas and discuss their research and experiences working with on data-related problems.  It is an academic conference with applications to real life, and includes any type of database.

The 5th annual NEDB will be held in Cambridge, MA MIT (in 32-123) on Friday, February 3, 2012.  Anyone who would like is welcome to present a poster (registration required), or submit a short paper for review.  We plan to accept 8--10 papers for presentation (15 minutes) at the meeting.   All posters will be accepted.

For more details, and to register and / or upload a paper, see:

http://db.csail.mit.edu/nedbday12/

From time to time, organizations want to know if there are any users that are not used.  For clients using MySQL 5.1 and up, that can handle a 1% overhead for general logging, we will enable the general log for a period of time and analyze the users that connect.

Note: we have some extremely busy clients, and we very rarely have a problem turning the general log on, other than making sure we're rotating and compressing logs so we do not run out of disk space.

Once we have the logs, I run this little perl tool I made -- I call it genlog_users.pl:

#!/usr/bin/perl

my $infile=$ARGV[0];

my %seen=();

my @uniq=();

open (INPUT, "<$infile");

while (<INPUT>) {

  my $line=$_;

  if ($line=~/Connect/) {

    if ($line=~/(\S*@\S*)/) { push(@uniq, $1) unless $seen{$1}++; }

 } # end if line matches Connect

}

close INPUT;

open (OUTPUT, ">>..users.txt");

$,="\n";

print OUTPUT (keys %seen);

print OUTPUT ("\n");

close OUTPUT;

----------

I hope it is useful for whoever stumbles on this; I know it has been useful for me in the past -- it's just doing some string matching, and I bet if I used Python it would be done in half the lines, but it's already fewer than 20 lines, so it's pretty small to begin with.

Percona Live: London was a rousing success for PalominoDB.  I was sad that I could not attend, but I got a few people who sent "hellos" to me via my coworkers.  But on to the most important stuff -- slides from our presentations are online!

René Cannao spoke about MySQL Backup and Recovery Tools and Techniques (description) - slides (PDF)

Jonathan delivered a 3-hour tutorial about Advanced MySQL Scaling Strategies for Developers (description) - slides (PDF)

Enjoy!

Monitoring systems are a great thing, and we rely heavily on Nagios here at PalominoDB.  We also rely heavily on xtrabackup for (mostly) non-locking, "warm" backups of MySQL.  In order to get a consistent backup with a proper binlog position on a slave, xtrabackup stops replication for a short period of time.  If the monitoring system catches this, the pager will go off, and usually in the middle of the night.

Nagios can have specific windows when it is on or off for a particular host or service, but you have to remember that when you change the time the backup runs.  I prefer to call a script from cron, just before I call the backup script, so that I can easily see that they are related.

Note that this script works even if you have Nagios password protected with .htaccess, because wget allows for a username and password.  This script is not perfect - there is not a lot of error checking, and log files are hard-coded.  But it does what it needs to.

The script is called like this:

./scheduleDowntimeByService service length host

Sample cron entries (note that the service name depends on what you are checking and the service names themselves):

00 5 * * 0 /home/palominodb/bin/scheduleDowntimeByService.pl MySQL+Replication+Lag 3600 hostname

00 5 * * 0 /home/palominodb/bin/scheduleDowntimeByService.pl MySQL+Slave+IO+Thread 3600 hostname

00 5 * * 0 /home/palominodb/bin/scheduleDowntimeByService.pl MySQL+Slave+SQL+Thread 3600 hostname

Here is the script itself:

cat scheduleDowntimeByService.pl 

#!/usr/bin/perl

use strict;

use POSIX;

my $service = shift @ARGV;

my $length = shift @ARGV;

my $host = shift @ARGV;

unless ($length ) {

        $length = 3600;

}

my $startTime = time();

my $endTime = $startTime + $length;

my $nagios_start = POSIX::strftime("%m-%d-%Y %H:%M:00", localtime($startTime));

my $nagios_end = POSIX::strftime("%m-%d-%Y %H:%M:00", localtime($endTime));

$nagios_start =~ s@:@%3A@g;

$nagios_start =~ s@-@%2D@g;

$nagios_start =~ s@ @%20@g;

$nagios_end =~ s@:@%3A@g;

$nagios_end =~ s@-@%2D@g;

$nagios_end =~ s@ @%20@g;

my $URL = 'https://monitoring.company.com/nagios//cgi-bin/cmd.cgi?fixed=1&start_time=' .  $nagios_start . '&end_time=' . $nagios_end . '&cmd_typ=56&cmd_mod=2&host=' . $host . '&service=' . $service . '&com_data=Backups&btnSubmit=Commit';

my $cmd = "/usr/bin/wget --quiet --user=user --password=PASS -O /tmp/nagios_downtime.html '$URL'"

;

open ( L, ">>/tmp/nagios_downtime.log" );

print L print $cmd . "\n";

print L `$cmd`;

close L;

Last week, I won USD$100*.  Gazzang is giving away $100 every Friday in October, in honor of National Cyber Security Awareness Month.  There are only 2 more weeks left -- all you have to do is tweet @Gazzang_Inc one way to improve your own cyber security.

My winning tweet:

@Gazzang_Inc one way is to secure MySQL! With Gazzang or the tips in bit.ly/oursql059 or bit.ly/oursql061.

The tip can be any kind of cyber security.  I believe you have to tweet on Friday, though.....so wait until tomorrow!

(Tune in tomorrow where I tell you how you can win USD$100, like I did last week!)

Not signed up for Percona Live:London and need some motivation to do so?  How about a £40.00 discount?  That means the Expo Hall is free, regular registration is £260.00 and the tutorials + conference price is £500.00.  Use discount code "PDBUK".

In just 3 weeks, RightScale is having a cloud conference in Santa Clara, CA Nov 8-9.  There is a Zero-to-cloud training on Tuesday, November 8th, and you can get a 25% discount on the training by using code "RSCONF25".

RightScale and Eucalyptus are teaming up to provide a training, too -- on Thursday, November 10th.  If the name Eucalyptus sounds familiar, it is because former MySQL employees Mårten Mickos and Mark Atwood work there.  There is a 25% discount off that training by using code "RSEUCT25".

I forget from whom I first learned about kSar, but I am in debt to that person once again.  I first learned about it about a year ago, and it has been extremely useful whenever I am trying to debug system where I do not have access to trending graphs.  kSar is an open source graphing tool for Linux, Mac OS X and Solaris sar output.  Graphs can be generated and exported to many different formats (JPG, PNG, and even a PDF with all the graphs in it).

By simply copying a few files from /var/log/sa (at least that's the default on Linux, and of course it does depend on sar being installed and running, but 95% of the time it is), running kSar and choosing the data file, you get beautiful graphs.  Try it right now, it will take you less than 10 minutes. (Note, I have a Mac, and I know the client graphical program works on Linux/Unix/Solaris, but I have no clue if it works on Windows -- there is a run.sh script, but it just calls a jar file, so I am pretty sure it will work on Windows.)

I copy the /var/log/sa/sar* files to my laptop, then cat them together (usually cd /path/to/sa, cat sar* > alldays), then run kSar and click "Data -> Load from text file".  It is just that simple.

The call for papers for Collaborate ends tomorrow, Friday October 14th.  Collaborate is Sunday, April 22nd through Thursday April 26th at the Mandalay Bay Convention Center in Las Vegas.  This year the folks scheduling the MySQL Conference and Expo have smartly chosen not to conflict, and in fact they are 2 weeks apart.

Collaborate is the largest independent (not put on by Oracle) conference for users of Oracle software.  This year we are having a one-day MySQL track at Collaborate, so that speakers and attendees can focus and optimize their time spent at the conference.

This is a great chance to give a talk to operations folks about running MySQL.  The audience is mixed, with some beginners and some intermediate folks.  We expect over 100 attendees to attend the MySQL sessions, as have done in the past 2 years.  

The track will have 1 or 2 MySQL talks per session time, so that the attendance is bigger and there is less that each attendee can miss -- last year the feedback from attendees was "I want to clone myself!" and the feedback fom speakers was "I wish there were more people in the room".  This year we are rectifying that.

So, don't forget to submit your paper today!

I normally do not repost articles and make teeny tiny blog posts (I have noticed a few bloggers who do that, and it bothers me), but I found a short article on TechRepublic called Are tech certs valuable or not? to be quite interesting.

The author did an informal poll of a few CBS interactive higher-ups, and it was interesting to note that in addition to the expected "it depends" and "experience is the most important thing" and "beware the person with many certs and no experience" answers, the MySQL certification was specifically noted as being good:

Doug Lane, Director of Site Reliability and Performance Engineering, says, “The MySQL DBA certifications are valuable to me, since we support so many instances of that. Frankly, the Java and MS certs became less valuable due to the number of those who were just paper certified but not actually practitioners.”

Interestingly, he says the reason the MySQL certifications are valuable is because CBS interactive supports so many instances of MySQL, but he implies that folks who have MySQL DBA certifications also have experience (as opposed to Java/MS certs).

Last night at the Oracle OpenWorld MySQL Community Reception, there were lots of old and new friends milling about.  It occurred to me that there is one very important thing Oracle has NOT changed about the MySQL world - the rock stars and higher-ups are still readily accessible.

One of the things I love about being in the open source community is that you can have an in-depth conversation with someone, and only later on find out that this person is famous.  For the most part, rock stars and important people are readily accessible.  They stay in the same hotels that attendees do, they take the same elevators, they are not whisked away by bodyguards, and they do not play the "don't you know who I am?" card.

Now, it's not surprising that the community rock stars like Mark Callaghan, Baron Schwartz, Giuseppe Maxia and Sarah Novotny are still as accessible as ever.  However, Ed Screven and Thomas Ulin were also around for the party, and I can confirm that Thomas was one of the last dozen or so to leave (Ronald Bradford and I closed out the party and were the last to leave).

So, kudos to Oracle for not keeping your VIPs locked up in a bunker.  I am very glad to see this aspect of open source culture still thriving.

For those who missed my presentations at Oracle OpenWorld (or if you caught it but want the slides) - PDF slide decks are online:

Securing MySQL (IOUG)  - http://technocation.org/files/doc/MySQLSecurity2011_09.pdf

How to Be a Rockstar MySQL DBA - http://technocation.org/files/doc/2011_09_RockStar.pdf

Thomas Ulin, VP of MySQL Enginnering, speaks about "State of the Dolphin" at 2011 Oracle OpenWorld.  There are some pretty cool new features in MySQL 5.6 development milestone release 2, and they are all quite stable, which is exciting.  They want to add more features before going GA with MySQL 5.6, but the ones in there are pretty ready to go.

"The 15-minute rule [MySQL can be installed in 15 minutes] is now down to 3 minutes for the full MySQL stack."  Download one package, and a GUI helps you install and configure everything.

MySQL Enterprise HA: Windows Server Failover Clustering, uses Windows Server Failover Clustering from Microsoft and the cluster is managed through the Windows tools.

Ulin talked a lot about how MySQL is good on Windows, and how it is better and lower TCO than Microsoft SQL Server.  They are focusing on Visual Studio, MS Office Integration, Entity Framework, Windows Administration tooling, and more.

Talks about the parts of MySQL Enterprise Edition:

MySQL Workbench

MySQL Backup - completely new from the ground up, Ulin does not have slides but there is a presentation on Wednesday I am looking forward to going to.  

MySQL Enterprise Security: New a few weeks ago, new authentication modules - PAM (uses LDAP, Kerberos, etc) or Windows authentication.  A good slide with how it works:

The application server sends the authentication information to MySQL, which is sent to the PAM library, which verifies the credentials, and returns "yes/no" to the mysql user.  "CREATE USER joe IDENTIFIED WITH pam_plugin AS 'joe';" - NOTE: specific privileges are still managed on the MySQL level (e.g. not everyone gets the SUPER privilege!)

MySQL Enterprise Scalability - thread pooling.  Pool contains a configurable number of thread groups (default=16) and each group manages up to 4096 re-usable threads.  Each connection asssigned to a  group via round robin.  20x better scalability read/write using thread pooling, 3x better scalability for read-only workloads.  There is an API so the thread pooling can be implemented manually.

OVM template for MySQL - on Oracle Linux.

Announcements

5.6 DMR 2 (development milestone release)

Close enough to be an RC or GA, they are very confident.  But they are not releasing as GA because they want to add more features.

New features - builds on MySQL 5.5 by improving:

optimizer - better performance, scalability

 - filesort optimizations with small limits

 - avoids creating intermediate sorted files by producing an ordered result set using a single table scan and sort on the fly.  

index condition pushdowns

 - was a 6.0 feature

 - good for composite indexes

 - why wouldn't you have this turned on, btw?  

batched key access and multi-range read

 - Improves performance of disk-bound JOIN queries

 - handles batches of keys instead of traditional nested-loop joins which do one key at a time.  Takes advantage of sequential reads.

postponed materialization

 - of views/subqueries in the FROM clause (aka derived tables)

 - Allows fast EXPLAINs for views/subqueries

 - Avoid materialization when possible, faster bail out

 - A key can be generated for derived tables.  

 - Unsurprisingly, this optimization is huge - 240x better execution times (e.g. drop from 8 minutes to about 2 seconds)

EXPLAIN for INSERT, UPDATE, DELETE

 Persistent Optimizer Statistics

 Optimizer traces

performance schema: better instrumentation

- up to about 500 instrumentation points

 - Statements/stages - where do my most resource-intensive queries spend the most time?

 - TAble/Index I/O, Table Locks - which tables/indexes cause the most load/contention?

 - Users/Host/accounts - using the most resources

 - Network I/O

 - Sumaries - agg by thread, user, host, account or object

innodb - better transactional throughput

 - new I_S tables: Metrics, Systems, Buffer pool info

 - Dump/restore buffer pool

 - Limit insert buffer size (ibuf)

replication for HA, data integrity

 - Better data integrity: crash-safe slaves, replication checksums, crash-safe binlog

 - Better performance: multi-threaded slaves, reduced binlog size for row-based binary logging

- Extra flexibility - time-delayed replication

- Simpler troubleshooting - row-based replication logging of original query

- Enhanged monitoring/management.

"NotOnlySQL" (NoSQL) for more flexibility

Misc

 - ipv6 improvements

 - Unicode support for Windows commandline client

 - import/export to/from partitioned tables

 - explicit partition selection

 - GIS/MyISAM: Precise spatial operations

MySQL Cluster 7.2: DMR 2

Close enough to be an RC or GA, they are very confident.  But they are not releasing as GA because they want to add more features.

 - 70x higher complex query performance using Adaptive Query Localization (example was an 11 way join)

 - native memcached API - with no application changes.  reuses standard memcached clients & libraries.  Also eliminates cache invalidation slamming MySQL

 - MSQL 5.5 Server Integration - previous versions were integrated with MySQL 5.1, so in MySQL Cluster 7.2 you have access to the new features - upgrading can be done in an online fashion with the rolling upgrade.

 - Multi-geographical site clustering - DR and data locality, no passive resources.  Can split data nodes and node groups across data centers and have auto-failover.

 - Simplified active/active replication - eliminates requirement for application and schema changes.  There are transaction-level rollbacks.  This helps with conflict detection when more than one master is written to.

 - Consolidated privileges for easier provisioning and administration

MySQL Enterprise Oracle Certifications

 - All the Oracle Fusion MiddleWare ship with the MySQL 5.x JDBC driver.

MySQL is being put into or being supported by a lot of Oracle products, including Fusion MiddleWare, including Oracle E-business suite.  This means you can use MySQL as a data source.

Coming soon: using the Auditing API in MySQL 5.5, Oracle will be adding an audit plugin that can (among other things) produce an audit stream that Oracle Audit Vault can then use.

A short while ago I posted about the Oracle OpenWorld Schedule Matrix of MySQL sessions (in PDF and HTML formats).  We have printed up a (small) number of schedules to have on hand at the MySQL Community kiosk at the User Group Pavillion in Moscone West.

Yes, you read that correctly -- the User Group Pavillion will include a MySQL Community kiosk this year!  Sarah and I have been coordinating the effort to staff the kiosk and figure out what we need to provide.

Sadly, it's just a kiosk (same as all the other User Group organizations get), so we cannot have a ton of flyers there.  To that end, we have created a QR code that resolves to www.kimtag.com/MySQL, which is where we are putting many links.  

To that end, we'd like your help figuring out what we have missed.  In order to keep the list of links as short and relevant as possible we have put as many aggregate links as we could, for example we link to planet.mysql.com instead of individual blogs, and we are only listing the major conferences with over 500 attendees expected.  The links at www.kimtag.com/MySQL as of the time of this blog writing are:

- MySQL sessions at OOW

- Planet MySQL

- dev.mysql.com (docs, etc)

- mysql.com

- MySQL User Groups (forge.mysql.com list) - so if you have a user group, make sure to update the forge page!

- Percona Live 2012 Conference & Expo

- MySQL videos on YouTube

- IOUG MySQL Council

- OurSQL Podcast Blog

- OurSQL iTunes link

- MySQL Experts podcast

- Book: MySQL Administrator's bible*

- Book: High Performance MySQL

- Book: Expert PHP/MySQL

- Book: MySQL High Availability

If you think of a link we should put on there, please comment below.

For what it's worth, the paper we will have will be:

- The current day's schedule

- A flyer about Percona Live 2012 MySQL Conference & Expo

- A poster of the QR code and a few small paper slips with the QR code

- IOUG MySQL Council business cards

There are 28 videos, all linked below, on the OSCon and OSCon data 2011 playlist that I have put online for free (with permission from the presenters and O'Reilly).  O'Reilly videos are available from the conference proceedings website.  Probably the best way to find all the videos in one place is to search for the 'oscon' tag on YouTube.