Robin Schumacher

I’ve always been a big proponent of data modeling and using modeling tools to create physical database designs (as an aside, I’ve never been much on logical modeling), so I’m naturally happy about our upcoming MySQL Workbench tool.  During my DBA years, I basically lived in my modeling tools and used them all the time for many different purposes.  I’ve just written a new dev zone article that talks about why modeling is important for creating high-performance databases, and how you can use modeling tools for change management/control purposes and how doing this can keep you from getting burned when DB changes go bad.  Check out the article and let me know what you think.

We’re pleased to announce that the Falcon beta is now out and available.  Falcon is MySQL’s new transaction storage engine, and offers a number of nice features for those developing applications that have transactional needs.  Falcon has all the standard transactional features you’d expect (ACID compliance, MVCC, crash recovery, etc) and some other items you will likely find helpful to have (user-defined tablespaces, SQL-based diagnostics for locking, I/O, and more.)

Please download the new Falcon beta (note that the Falcon beta is in the 6.0.4 alpha binary) and let us know what bugs you find or enhancements you’d like to see - your feedback is (as always) greatly appreciated! If you’re new to Falcon, you can check out the various tech white papers, getting started guides and FAQ’s on our 6.0 info page.

In some ways, MySQL is a victim of its own success.  Because of the focus on reliability, performance, and ease-of-use, MySQL usage continues to grow at a heavy pace.  This being the case, the demand for skilled MySQL pro’s has quickly outstripped the current supply.  Go to monster and put in ‘mysql’ and you’ll get back thousands of hits. So if you’re wondering whether getting MySQL expertise as a DBA is a good investment, wonder no more.

Without a doubt, one great way to drink in lots of MySQL DBA knowledge is to attend the upcoming MySQL User’s Conference.  At this year’s conference, there are more tracks than ever designed to help the DBA get a handle on security, performance tuning, backup and recovery, high-performance schema design, and much more.  Plus, you’ll get to see some new tools we’ve been working on to make your DBA life easier, sneak-peek’s at major upcoming features like our new online backup, and some pretty cool things on the data warehousing front. I’ll see about kicking out a DBA tutorial guide for the conference once we get a little closer, but I do want to remind you that early registration savings expires near the end of the month, so sign up soon if you want to save some $$$.

As I wrote in a previous post, I’m pretty jazzed about the upcoming MySQL Workbench as I know how nice it is to have a strong modeling tool at your disposal.  I’ve just completed a new paper that discusses the many benefits that both developers and DBAs get when they use a good data modeling tool like Workbench.  If you’ve been wondering whether a modeling tool can make a difference in your worklife, download the doc and give it a read. 

 And don’t forget to check out the latest builds of Workbench as the team is working hard on getting the first GA version out.  Your help and feedback is greatly appreciated!

I still remember my first database disaster that occurred almost 20 years ago.  I was a brand new systems engineer working on a massive mainframe database.  My leader needed me to make a few data changes, but they would have to be done while he was on vacation (of course) but he assured me nothing would go wrong.  Right.  To make a long story short, the morning I ran the jobs to make the change, I ended up deleting the entire worldwide telecommunications database of General Motors and keeping just the two records I needed to change.  Let me tell you, no amount of antiperspirant stands up under those conditions.

Thank goodness for the backup job that I ran right before the change.  I was able to restore everything back to the way it was, but brother, were there ever some anxious moments in between… 

For sure, data protection is the database professional’s #1 job.  And this week, we’re going to be talking about how Oracle DBA’s (and really any DBA) can learn how to choose the best form of MySQL backup for their situation and also utilize the nice backup solution from Zmanda if they’d like.  Both Zmanda and I will be presenting a webinar that you should check out if you have time; in it, we’ll cover backup techniques and best practices that will keep you confident, knowing that you’re protecting your databases as best you can.  Hope to ’see’ you this Thursday at 1pm EST. 

When I was a DBA, there were only a handful of must-have tools that I constantly used.  One was a good performance monitor.  Two was a cross-platform admin tool (since I worked with Oracle, SQL Server, Sybase, MySQL, etc.) And three was my data modeling/design tool.  There was absolutely no way I could have done my job in the manner I needed to without those three tools.

Of those three, there were times when I basically lived inside my modeling tool.  Why?  Because:

• Metadata management – I had to ensure data consistency and enforce standards of data elements used throughout my shop; the modeling tool was the best way to do this. 
• DB design – simply put, there was no way to more quickly put out a new DB design and forward engineer it into a new physical DB without my modeling tool
• Change management – when I made changes to the DB, I always wanted to keep a copy of how the DB looked before for change management reasons (in case I needed to put things back).  Using the modeling tool was the best way to do this.
• Packaged application management – I hated the black-box feel of packaged apps, but the modeling tool allowed me to reverse engineer and see inside the DB, which was a huge help.
• Reporting and communication – developers and business analysts always wanted layouts of the database they were using.  A couple of clicks in the modeling tool gave them what they wanted.
• Performance tuning – looking at bad datatypes, bad relations, denormalizing designs, etc., are all more easily handled in a modeling tool.

This is why I’m hyped over us releasing the beta of MySQL Workbench.  Now you and I have a nice tool to tackle the stuff above for MySQL DB’s.  Once you use it for a project or two, you’ll see what I mean about how much help a modeling tool can be.

Make sure you download the free version and let us know what you think.  You can also try out the commerical version, which has a few more time-saving things in it. Whichever edition you end up using, I think you’ll get hooked on modeling tools like I did a long time ago. 

In previous MySQL 6.0 alpha’s, the new Falcon engine didn’t handle ‘large’ transactions (meaning lots of rows inserted at one time) very well. You typically had to fall back to looping through the data with various commit points to get all the data inserted in a timely fashion.

The Falcon team should get some good kudos for putting out the latest alpha release that has much improved handling of large transactions. Below are just a few examples of large inserts on a Fedora Core box with a single CPU. Falcon was given a 200MB record cache size and InnoDB got a comparable 200MB buffer pool size.

mysql> show create table t_mG
*************************** 1. row ***************************
       Table: t_m
Create Table: CREATE TABLE `t_m` (
  `client_transaction_id` int(11) NOT NULL DEFAULT '0',
  `client_id` int(11) NOT NULL DEFAULT '0',
  `investment_id` int(11) NOT NULL DEFAULT '0',
  `action` varchar(10) NOT NULL,
  `price` decimal(12,2) NOT NULL DEFAULT '0.00',
  `number_of_units` int(11) NOT NULL DEFAULT '0',
  `transaction_status` varchar(10) NOT NULL,
  `transaction_sub_timestamp` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `transaction_comp_timestamp` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `description` varchar(200) DEFAULT NULL,
  `broker_id` bigint(10) DEFAULT NULL,
  `broker_commission` decimal(10,2) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.03 sec)

mysql> select count(*) from t_m;
+----------+
| count(*) |
+----------+
|  2000000 |
+----------+

mysql> create table t_f engine=falcon select * from t_m where 1=2;
Query OK, 0 rows affected (0.05 sec)

mysql> create table t_i engine=innodb select * from t_m where 1=2;
Query OK, 0 rows affected (0.05 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t_f select * from t_m;
Query OK, 2000000 rows affected (42.06 sec)
Records: 2000000  Duplicates: 0  Warnings: 0

mysql> rollback;
Query OK, 0 rows affected (5.09 sec)

mysql> insert into t_i select * from t_m;
Query OK, 2000000 rows affected (45.73 sec)
Records: 2000000  Duplicates: 0  Warnings: 0

mysql> rollback;
Query OK, 0 rows affected (41.81 sec)

Much better than previous alpha releases… Still, there’s more work to be done as Falcon can still experience a memory exhaustion error (remember it performs transaction management in memory) when transactions get really large as in this 5.6 million row test:

mysql> insert into t_f select * from t_m_big;
ERROR 1296 (HY000): Got error 305 'record memory is exhausted' from Falcon

The Falcon team will be addressing this issue shortly so look for even better things to come in upcoming Beta releases.

First, I’d like to thank everyone who’s downloading and testing the latest MySQL 6.0 Alpha release that contains our new Falcon transaction storage engine. For those of you who haven’t yet, let me point out something you may have missed (heck, maybe those of you who’ve already downloaded 6.0 have missed it too…) - the latest release contains the first cut of Falcon tablespaces. We get requests all the time that storage engines support the concept of tablespaces, and with Falcon, you’ve got them now. You can create user-defined tablespaces via DDL, assign your tables/indexes to whichever tablespaces you want via CREATE TABLE, and move objects to different tablespaces with simple ALTER TABLE commands.

I’ve written a quick primer on Falcon tablespaces that will help get you started, so check that out and then download 6.0 and give ‘em a try.

Yes, even though MySQL 5.1 is in beta (soon to be RC), our surveys show that folks are using 5.1 already for production use.

If you’re using 5.1 for production apps, I’d appreciate it if you could drop me a line and let me know what motivated you to move up to 5.1. What 5.1 features made you take the plunge? As we’re in the midst of planning features for the upcoming server versions, knowing what you like about 5.1 and what improvements you’d like to see over what we did in 5.1 would be a great help. Shoot me a note at rschumacher@mysql.com.

Thanks!

I’m real excited about our upcoming seminars for Oracle DBAs who want to learn MySQL. Having used Oracle myself for many years, I know full well there are lots of cases where Oracle and MySQL are a great combination to have in a side-by-side manner. There are things you can do with Oracle that you can’t do with MySQL and (believe it or not…) there are things you can do with MySQL that you can’t do with Oracle. Plus we have lots of folks who offload Oracle application data to MySQL data marts/warehouses or other systems. This being the case, it’s not surprising that even the IOUG (International Oracle Users Group) admit that now 1/3 of Oracle shops use MySQL.

The upcoming seminars, scheduled for New York (August 14th) and San Francisco (August 16th) don’t contain fluffy presenations, but instead focus on the hard-core tech details DBAs love. Oracle DBAs will leave these seminars armed with the information they need to smartly manage MySQL in their environments. Everything from the architecture similarities/differences between Oracle and MySQL, security, object management, performance and tuning, and more will be covered. And of course, the great thing is that it’s all FREE.

If you’re in or close to these cities, don’t miss out and register now. Just a casual look at DBA job ads these days will tell you that having MySQL DBA abilities is an extremely attractive trait to possess.