[author's note: personally, I use awk a bunch in MySQL DBA work, for tasks like scrubbing data from a production export for use in qa/dev, but usually have to resort to Perl for really complex stuff, but now I know how to do .]

Basics:
By default, fields are separated by any number of spaces. The -F option to awk changes the separator on commandline.
Print the first field, fields are separated by a colon.
awk -F: '{print $1}' /etc/passwd

Print the first and fifth field:
awk -F: '{$print $1,$5}' /etc/passwd

Can pattern match and use files, so you can replace:
grep foo /etc/passwd | awk -F: '{print $1,$5}'
with:
awk -F: '/foo/ {print $1,$5}' /etc/passwd

NF = built in variable (no $) used to mean “field number”

Here’s a matrix of all the videos up on YouTube for the 2010 O’Reilly MySQL Conference and Expo. The matrix includes the title, presenter, slide link (if it exists), video link, and link to the official conference detail page, where you can rate the session and provide feedback that the presenter will see. They are grouped mostly by topic, except for the main stage events (keynote, ignite) and interviews.

If there’s a detail missing (ie, slides, or there are other videos you know about), please add a comment so I can make this a complete matrix.TitlePresenterSlidesVideo link
(hr:min:sec)Details (Conf. site link)

Keynotes

The post is a little late since we came back to the hotel in the wee hours of the morning and I had to get up at 5:00am this morning.

Anyway, The morning’s keynotes again were quite good especially the one from Ubunto. I attend another packed Facebook session and one from Ronald Bradford. It was the first time I head him speak and he is very good.

I didn’t attend many of the afternoon events because of customer commitments but the remain group at the hotel had a passionate discussion on Oracle role in the MySQL community so its far from over and Oracle has one year to fix it. See you all next year

We started with the morning keynotes again today and I was a little surprised at the snipping going on while people are promoting their products it felt very counterproductive and will end harming the MySQL eco system. The Community Keynote on the other hand looked into the future for itself under the Oracle umbrella. Its still remains to be seen what form the conference will have next year.

The first session that I attended today were around MySQL partitioning in the beta releases and beyond. They were saying the right things but it remains to be seen when some of these features will be available. I would hope that the engineers responsible for MySQL partitioning have a little talk with their Oracle counterparts which have been at it for quite a while. They should sync the features up which would make it easier for us multi-disciplinary DBAs

The next set of sessions were on IO

Well the first day has come and gone and I really enjoyed my first day as a newbie. The keynote from Oracle was well received, they touched on the new Beta version of MySQL and the new mysql enterprise which to a trained oracle eye is looking more and more like Grid Control. The end result is providing more instrumentation to help the DBA but I am a little disappointed that a lot of that instrumentation is not actually in the database itself which forces you to buy the product.

The O’Reilly Keynote provided some interesting glimpse into the future.

There is a strong contingent from Facebook and those sessions are just packed with everybody wanting to know how they scale their infrastructure.

The spider storage engine presentation tweaked by interested with the promise of vertical partitioning…I’ll have to try that when I come back.

My presentation

I’m looking forward to traveling to San Jose for this year’s MySQL Conference. If there’s anything that can trump the drama of conf two years ago, where we observed how Sun would handle its new property, and then the drama of last year, where we observed how Oracle would handle the pending acquisition, it’s going to be the drama around this one — the first MySQLConf since the Oracle/Sun merger has been finalized and approved.

I think there is some finality to the changing of the guard this time, since there aren’t really that many companies that could conceivably swallow up Oracle itself! (Maybe I shouldn’t say that — next thing you know they’ll spin it off heh.) But regardless, I am looking forward to getting to know Edward Screven and getting a sense from the keynote

Here’s what Pythian is cooking up for MySQL Conference this year.

Monday, April 12

8:30am: Get out of bed lazy bones and head to Ballroom B

… because you’re going to want to attend Sheeri K. Cabral‘s tutorial in two parts:

MySQL Configuration Options and Files: Basic MySQL Variables (Part 1)

Unlock all the information the MySQL server can give you! MySQL has many status variables that show how well your environment utilizes its resources. There are many system variables that can be set and changed to tune the server.
Read more.
Add to your personal schedule at

Persistence Smoothie: Blending NoSQL and SQL – see user feedback and comments at http://joind.in/talk/view/1332.

Michael Bleigh from Intridea, high-end Ruby and Ruby on Rails consultants, build apps from start to finish, making it scalable. He’s written a lot of stuff, available at http://github.com/intridea. @mbleigh on twitter

NoSQL is a new way to think about persistence. Most NoSQL systems are not ACID compliant (Atomicity, Consistency, Isolation, Durability).

Generally, most NoSQL systems have:

• Denormalization
  
• Eventual Consistency
  
• Schema-Free
  
• Horizontal Scale
  

NoSQL tries to scale (more) simply, it is starting to go

Baron makes an excellent point in Why you should ignore MySQL’s key cache hit ratio — ratio is not the same as rate. Furthermore, rate is [often] the important thing to look at.

This is something that, at Pythian, we internalized a long time ago when thinking about MySQL tuning. In fact, mysqltuner 2.0 takes this into account, and the default configuration includes looking at both ratios and rates.

If I told you that your database had a ratio of temporary tables written to disk of 20%, you might think “aha, my database is slow because of a lot of file I/O caused by writing temporary tables to disk!”. However, that 20% ratio may actually mean a

I am writing because Sheeri sent me a note about a blog post written by Brian Aker, where Brian concludes, quite correctly, that (in Sheeri’s words not Brian’s)


MySQL is now just a branch (the official branch,
but a branch nonetheless, and a bunch of trademark (logo) and
copyright (docs) ownerships).


This is exactly true. No denying it. Why bother. It’s true. It’s also true for the vast majority of open-source projects, by the way.

I replied to Sheeri:


There's no denying that. The product direction will be set by whoever sets the best product management strategy backed by the most effective development effort. And there can be multiple winners.
-Paul


Well, this is the kind of

Ronald Bradford’s recent warning to be sure to know your my.cnf sections reminded me of a similar issue that I ran into last summer, where putting the “group” option in both the [mysqld_safe] and [mysqld] directives resulted in a mostly silent problem.

I started noticing this in MySQL 5.1 and it affected both the official MySQL binary and the Percona binary. In trying to be conscientious, I had the following set:

[mysqld_safe]
user=mysql
group=mysql

[mysqld]
user=mysql
group=mysql


However, when the MySQL server started up, the error log showed

[Warning] option 'group_concat_max_len': unsigned value 0 adjusted to 4

According to the official lifecycle calendar at http://www.mysql.com/about/legal/lifecycle/#calendar (http://www.mysql.com/about/legal/lifecycle/#calendar), active support for MySQL 5.0 (including regular binary updates) will end on December 31st, 2009, which is about 3 weeks away.

Many folks are still using MySQL 5.0.45, as until October that was the package that came with RedHat. That was released in July 2007, over 2 years ago!

Upgrading to MySQL 5.1 is not difficult, though it requires more steps than just upgrading the packages.

There is a list with all the changes made that might affect the upgrade process at http://www.pythian.com/news/1414/new-in-mysql-51-sheeris-presentation/. This includes which variable names have been deprecated and changed, as well as how

OpenSQLCamp was a huge success! I took videos of most of the sessions (we only had 3 video cameras, and 4 rooms, and 2 sessions were not recorded). Unfortunately, I was busy doing administrative stuff for opensqlcamp for the opening keynote and first 15 minutes of the session organizing, and when I got to the planning board, it was already full….so I was not able to give a session.

• Comparing Non-Relational Databases: MongoDB, Tokyo Tyrant, CouchDB by Igal Koshevoy of Pragmaticraft
• Drizzle Client Rewrite – Clark Boylan leads the requirements and design discussion for rewriting the Drizzle Client
• Drizzle Plugin

At the July MySQL User Group, Eric Day and Patrick Galbraith spoke about Drizzle, a lightweight, microkernel, open source database for high-performance scale-out applications, and Gearman, an open source, distributed job queuing system.

The slides can be downloaded from http://www.oddments.org/notes/DrizzleGearmanBoston2009.pdf.

The first hour of video, where Eric and Patrick talk about Drizzle, is at http://www.youtube.com/watch?v=hi4cGzFlcuU, and below:

The second part, about 1.5 hours, where Eric and Patrick talk about Gearman, and then illustrate Gearman and Drizzle working together in a custom search

I’ll be liveblogging the keynote here along with Marc Fielding, Christo Kutrovsky, Darrin Leboeuf and Luke Davies and Martin Wisniewski.

First observation: I can not believe I can not make this video full screen.

Even Pythian’s flash video player can go full screen.

Introduction time. Safra Katz.

Confirming HP is here as part of the announcement.

So presentation from HP is here first. Weird. Maybe this is a megabucks commercial keynote. Introducting Exec VP Anne Livermore.

“Very very very very very important event for HP. 100000 joint customers.”

Show of hands for HP adoption. A bit lame, she seems disappointed with how many hands are raised. :-)

The video stream is super saturated, the video is super skippy. Hopefully I can understand what’s going on well enough to do this liveblog.

So

Note: This article is about the WHERE extension to SHOW. I specifically use SHOW STATUS as an example, but WHERE is an extension to many SHOW statements.

Often DBAs will assess the health of a system by looking at some of the status variables returned by SHOW GLOBAL STATUS (specifying GLOBAL is important; remember that SHOW STATUS is the same as SHOW SESSION STATUS).

There are many status variables that SHOW GLOBAL STATUS returns. (SHOW GLOBAL STATUS on a Windows machine, MySQL version 5.0.67 returned 249, 5.1.22 returned 256 and 6.0.6-alpha returned 295 status variables!). I have used the SHOW STATUS LIKE syntax to help give me the output I really want, particularly when I forget the exact names of the status variables I am

Smithy commented on my blog post about the rumor of Monty leaving Sun with a pointer to an article on ComputerWorld Finland that mentions:

Widenius told to Computerworld Finland on Friday that negotiations are still on.

Meanwhile, Matt Asay, who seems to think Monty actually has left Sun (even though all other reports have been clear to mention that this is unconfirmed), writes of a new investment Monty has made.

Last week I speculated about the impact of Monty leaving Sun. In the end, if he does stay, it’s wonderful for

Scenario: Someone wants to know which of the over 50 MySQL users have certain privileges.

There are many ways to solve this problem. Some of these scenarios are tedious and repetitious, others take no time at all.

The issue, of course, lies in what the “certain” privileges are. If it is “who has the SUPER privilege?” then a simple

SELECT user,host FROM mysql.user WHERE Super_priv='Y';

is sufficient. If it is “who has write access to the foo database”, you might write:

SELECT user,host FROM db WHERE Db='foo' AND Select_priv='Y';

but that only shows who explicitly has read permissions on that database; it does not include those who have global read permissions. The full query would be:
(more…)

Last week Giuseppe Maxia announced the Call for Papers for the 2009 MySQL Users Conference and Expo, and also announced that there would be an unconference, MySQL Camp, organized by me.

It’s true! Currently MySQL Camp is set to happen, though I am still working out details with Colin Charles and Giuseppe Maxia. We had originally talked about having MySQL Camp on Tuesday and Wednesday, but I would like to add Monday so that folks attending the conference who are not attending a tutorial have a choice on Monday. I am also looking into lunch options, since the conference venue does not have many options within walking distance.

There will be plenty of

I have not said much about Drizzle here; that is because there is not much to say. It is premature, really, to say anything about it at this point. Some have said they will support Drizzle; as Pythian supports several database systems, it is very likely that we will support Drizzle as well. Particularly since there is in-house Drizzle expertise already. But I digress; my point is that it is premature to really say much about Drizzle.

My involvement in Drizzle goes back to around the end of April/beginning of May 2008. Given my early involvement, (more…)

Back in May 2006, I have started my blog using the Blogger platform and one month later moved it to my own website using Wordpress. Couple month later, I joined Pythian and, since then, the vast majority of my blogging activities has been on the Pythian Group Blog.

The Pythian blog has grown significantly since then and many more excellent authors started blogging there. While the Pythian blog was mostly focused on Oracle database just a couple years ago, it’s has got very broad coverage now and is including MySQL, SQL Server and Oracle databases as well as Oracle Application Server, Oracle eBusiness Suite and other

The MySQL Community version is different in theory from the Enterprise version in relation to the following points:

0) It’s free
1) It has community patches
2) It is released less often
3) It is tested less strictly

In reality, the first two differences are not applicable — the binaries and source code for Enterprise can be freely and legally downloaded at http://mirror.provenscaling.com/mysql/enterprise/. The process for adding community patches to the MySQL source code has not been changed sufficiently to be able to actually add community patches and encourage more community development.

I understand that MySQL (and now Sun) needs to make money. I also understand that development takes a lot of effort, and seeing an ROI is important. The Community/Enterprise split was

Brian Aker gives the “zinger” lightning talk about the newly announced “Drizzle”. This short (under 8 minutes) video captures Aker’s highlights of why he started the Drizzle project and how Drizzle is different from MySQL — both in what has been removed from MySQL and what features Drizzle can accomodate.

Play the video directly in your browser at http://technocation.org/node/576/play or download the 116 Mb file at http://technocation.org/node/576/download.

Yesterday I had a good conversation with Monty Widenius (a MySQL founder) about MySQL 5.1. Specifically, about the fact that MySQL 5.1 is not a GA (generally available) release.

My impression, which was wrong, was that it was difficult getting critical mass to download 5.1 and use it simply because it was not a GA release yet. I thought the paradox of needing to have a certain amount of usage before release was the barrier.

That’s not the case at all.
(more…)