Sheeri Dot Org

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.sql

mysqldump –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