Sheeri dot com

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.

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.

 I ran into this issue a while ago, and was reminded of it again recently.  mysqldump uses a default charset of utf8, even when the default charset of the server is set differently.  Why does this matter?The problem exists more in the fact that if you have string data that is in latin1 format, you are allowed to put in non-Latin characters. This can lead to lost data, especially when upgrading a major series (e.g. 5.0 to 5.1 or 5.1 to 5.5), because you're supposed to export and import the data.Also, when importing a backup of an InnoDB table, if there is an error with one of the parts of the INSERT, the whole INSERT statement rolls back.  I have experienced major data loss because the garbled characters cause an error when INSERTed, and it causes perfectly fine data *not* to import because they're in the same INSERT statement as the garbled data.For example:First, set variables such on a MySQL server (5.0 or 5.1, I haven't tested on 5.5):mysql> show global variables like '%char%';+--------------------------+----------------------------+| Variable_name            | Value                      |+--------------------------+----------------------------+| character_set_client     | latin1                     | | character_set_connection | latin1                     | | character_set_database   | latin1                     | | character_set_filesystem | binary                     | | character_set_results    | latin1                     | | character_set_server     | latin1                     | | character_set_system     | utf8                       | | character_sets_dir       | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+8 rows in set (0.00 sec) Then create these tables with data: CREATE TABLE `test_utf8` (  `kwid` int(10) unsigned NOT NULL default '0',  `keyword` varchar(80) NOT NULL default '') ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `test_utf8` VALUES(1,'watching'),(2,'poet'),(3,'просмотра'),(4,'Поэту'); CREATE TABLE `test_latin1` (  `kwid` int(10) unsigned NOT NULL default '0',  `keyword` varchar(80) NOT NULL default '') ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `test_latin1` VALUES(1,'watching'),(2,'poet'),(3,'просмотра'),(4,'Поэту'); Now compare:mysqldump test > test_export_utf8.sqlmysqldump --default-character-set=latin1 test > test_export_latin1.sql Note that the test export with the default character set of utf8 has mojibake whereas the export with latin1 does not. So be *extremely* careful when using mysqldump - whether for backups or while upgrading.  You can checksum your data before and after  an export/import with mysqldump to be sure that your data is the same. 

While the online content catalog and schedule builder are great tools to help plan out what sessions I want to see at Oracle OpenWorld, what I really want is a matrix of only the MySQL content, preferably in a matrix that easily shows all the sessions per time period.

So I decided to make the matrix myself - view the HTML online at http://technocation.org/files/doc/2011_OOW_MySQL_Content.html

Or download a PDF (one page per day) at http://technocation.org/files/doc/2011_OOW_MySQL_Content.pdf

If you have feedback, please let me know in the comments or via the e-mail address on the matrix.  These documents are for personal use unless other arrangements have been made.

To see full descriptions, click on a speaker's name to be sent to the content catalog's page for that speaker, then click on the session to get the full description.

I love Percona Live.  I think it is a great meeting of the minds.  However, I do not think it is a good replacement for the big April MySQL conference.  In fact, neither does Baron Schwartz:

"The conference is organized and owned by MySQL, not the users. It isn’t a community event. It isn’t about you and me first and foremost. It’s about a company trying to successfully build a business, and other companies paying to be sponsors and show their products in the expo hall."
Baron Schwartz, April 23, 2008.
http://www.xaprb.com/blog/2008/04/23/like-it-or-not-it-is-the-mysql-conference-and-expo/

Switch "MySQL" for "Percona", and that is exactly what Baron said in today's announcement:

"Emphasis on business. We need a place where vendors, both open-source and closed-source, can showcase their products and services. This is the hand that feeds all of us. It’s good for Percona’s business, and it’s good for everyone else’s too."

...except in 2008, Baron was calling for a community conference because what was good for business was not good for the community.  

Let me pull out that last sentence from the quote.  Read it to yourself, and tell me if you feel warm fuzzies replacing "Percona" with "Oracle" or "MySQL":

"It’s good for _______’s business, and it’s good for everyone else’s too."

Here is a question for you:  Do you think Oracle will send lots of engineers to talk about current and future plans for MySQL to a conference that is open about saying "It is good for our business?"  

It does not matter if it is Percona, Blue Gecko, or PalominoDB, if Oracle has any business sense (and they make quite a bit of money, so signs are that they have business sense) they will not send engineers to a competitor's company-branded conference.

What does Percona's founder, Peter Zaitsev, have to say about the conference?  He's not really happy about it either:

"I would like to see the conference which is focused on the product users interests rather than business interests of any particular company (or personal interests of small group of people), I would like it to be affordable so more people can attend and I’d like to see it open so everyone is invited to contribute and process is as open as possible. "
Peter Zaitzev, April 23, 2008
http://www.mysqlperformanceblog.com/2008/04/23/conference-for-mysql-users/

Peter, I would like to see that too.  In fact, a small group of folks, including Giuseppe and myself, tried to make that happen, and we had SkySQL, MariaDB, Oracle and IOUG all supporting us. 

Giuseppe called for disclosure about the conference, so I will disclose this:  Baron was not truthful when he said "To the best of our knowledge, no one else was planning one".

Giuseppe asked for full disclosure, so here is a copy and paste of a Skype conversation I had with Percona's Tom Basil on June 29th, 2011:

Sheeri K. Cabral 6/29/11 12:10 PM 

I think we should develop some ideas in case O'Reilly doesn't end up having a MySQL conference....last year the announcement was late, and it was in May, and I'm starting to think they might not be doing a conference this year, since we haven't heard anything yet.

6/29/11 12:10 PM

if that's the case, I'd like to have a conference anyway, and I'd like to explore options with you, because we need a community-run conference (not Collaborate, but maybe a co-located summit).  And obviously you've had success with Percona Live, but a multi-day conference is really different.

6/29/11 12:11 PM

(FWIW I told Colin Charles in May that I was willing to help co-chair the conference, so I'm still willing to give my support in that area).

Tom Basil 6/29/11 12:14 PM 

Sheeri, can't talk now

Sheeri K. Cabral 6/29/11 12:14 PM 

*nod*  can we schedule a conf call maybe?

Tom Basil 6/29/11 12:14 PM 

Headed out in just few min

6/29/11 12:14 PM

yes, next week

6/29/11 12:14 PM

The final videos from Open DB Camp back in May in Sardinia, Italy are now online.  The full matrix of sessions, videos and slides can be found on the schedule page.

Hands on JDBC by Sandro Pinna - video

"MySQL Plugins, What are They? How you can use them to do wonders" by Sergei Golubchek of MariaDB - video

The State of Open Source Databases by Kaj Arnö of SkySQL - video

Coming soon, videos from OSCon Data!

Next week I will give a free IOUG webinar on Securing MySQL on Wednesday, August 10, 2011 from 11:00 AM - 12:00 PM CDT (17:00 GMT):

To register for this webinar, visit https://www1.gotomeeting.com/register/979260992.  

I have gotten to uploading more of the videos from Open DB camp in Sardinia, Italy back in May:

Henrik Ingo speaks about Xtrabackup Manager - video

Linas Virbalas speaks about "Flexible Replication: MySQL -> PostgreSQL, PostgreSQL to MySQL, PostgreSQL to PostgreSQL" - video - slideshare slides

MySQL to MongoDB replication (hackfest results) - video 

Robert Hodges of Continuent speaks about Multi-Master Replication: Problems, Solutions and Arguments - video

There are a few more videos from Open DB Camp to put up, then I start to put up the content from OSCon Data!

Brian Aker presents "Drizzle, Virtualizing and Scaling MySQL for the Future" at OSCon Data 2011

http://drizzle.org

irc.freenode.net #drizzle

http://blog.krow.net

@brianaker

2005 MySQL 5.0 released - web developers wanted tons of features that were not in the release (making replication better for instance)

2008 Sun buys MySQL

2008 MySQL 6.0 is forked to become Drizzle

2009 Oracle buys Sun

2010 Drizzle developers leave Oracle

2011 First GA release, Drizzle7

MySQL's Architecture - monolithic kernel, not very modular, lots of interdependence.

Drizzle has a microkernel, which includes a listener, parser, optimizer, executioner, storage system, logging/error reporting.

Drizzle can accept SQL and http blog streaming, and memcached and gearman can easily talk to Drizzle.

Drizzle has tried to have no "gotchas"

- If you make a new field with NOT NULL, MySQL makes new values NULL.  Drizzle does not do this.

- No hacky ALTER TABLE

- Real datetime (64 bit), including microseconds

- IPV6 (apparently this is a strong reason for people switching, to support IPV6)

- No updates that complete halfway

- Default character set is UTF-8, default collation is utf8-general (charset in latin1 by default in MySQL, collation is latin1_swedish_ci - "case insensitive" by default)

Replication

- In MySQL, replication is kind of hacky [this is my summary and opinion, but it's basically what Brian said]

- Drizzle is Google Protocol Buffer Based

- Replicates row transformations

- Integrates with RabbitMQ, Cassandra, Memcached, Gearman -- right now.

DML and MySQL binary logs analog:

- DML is stored transactionally by delta in Drizzle

- InnoDB is already logging, no need to add another log for the binary log.  So it just logs DML to the transaction log.

LibDrizzle

- supports Drizzle, MySQL, SQLite

- Asynchronous

- BSD, so Lawyer-free

What else?

- No cost authentication (pam, ldap, htaccess, ...)

- Table functions (new data dictionary, including performance and thread information).  INFORMATION_SCHEMA in Drizzle is *exactly* what's specified in the SQL standard.

- Data types - native type for UUID, boolean, all known types (except SET, because it's broken by design)

- Assertions are in Drizzle, you can ask what the type of the result of combining multiple data types will be.

- About 80 conflicts in the Drizzle parser as opposed to about 200 in the MySQL parser

Roadmap - Drizzle7+

- Replication - faster than MySQL and also allows multiple masters.

Virtualization:

Virtualizing a database gives you about a 40% performance hit.  How can costs be cut?  In MySQL 5.0 the Instance Manager was created to solve that but it hasn't really been worked on.  Drizzle has worked on virtualizing databases internally within Drizzle.

- So drizzle now has catalogs.  

- One catalog has its own set of users, its own schema with tables, etc.

- A catalog is its own sandbox; there is no syntax that allows you to connect from one catalog to another, so there's no security problems.  

- Cuts the 30/40% hit from virtualizing

- Single instance maintenance - only 1 OS and 1 database to configure, unlike VMs

    - Currently only one database configuration so there's one global config for shared memory such as innodb buffer pool, but that will change in the future.

- Still allows for I/O spread on SAN/NAS

In Drizzle 7.1 - Percona's xtrabackup supports Drizzle, and ships with drizzle.  xtrabackup supports full and partial backups, no locking, single solution for point-in-time recovery in a single solution.  Because transaction log is stored in database, replication is automatically consistent with the database.  Currently does not do incremental backups with the transaction logs but that's in the future.

DBQP:

- consolidates standard testing tasks, server/test management, reporting, REGRESSION TESTING

- extended architecture allows for complex testing scenarios

- pluggable - supports new testing tools

- randgen, sql-bench, crashme, sysbench, standard drizzle-test-run suite

- Keeping tools and testing configurations in-tree facilitates testing for everyone

- supported by SkySQL

Dynamic SQL/execute()

- New UTF-8 parser

- Being extended to allow for plugging in application servers.

>120 developers since day 1

avg 26-36 per month that commit

Bugs database - http://bugs.launchpad.net/drizzle