Hello, Developers and Database Administrators!
We are fortunate enough to get an opportunity to interview Mr. David Stokes, the MySQL Community Manager at Oracle and grab some knowledge about the current trends in the field of Database Administration. Dave Stokes, having 30+ years of experience in the field of IT Industry is also The Head of North Texas MySQL Meet Up Group and was formerly the Certification Manager at Sun Micro Systems and Oracle.
Bhavik: What are the biggest challenges you face being a community manager at MySQL?
Dave: Time and the fact there I cannot be two in two places at the same time. It is very hard to keep up with new developments in MySQL like the JSON data type, travel to the shows to present talks, and find time to sleep.
Bhavik: According to you, how is MySQL better than its competitors like PostgreSQL and NoSQL?
Dave: I like PostgreSQL, know several of the code committers, and they are good people. I just like MySQL better. At a functional level, they do the same thing but it is much easier to find folks with MySQL experience. The ubiquity of MySQL on the web, in my opinion, is that it is a little easier to install and run for most folks.
And NoSQL has a place in the MySQL world. You can access InnoDB or NDB storage engine data as a key/value pair using the Memcached plug-in and bypassing the syntax checking and query optimizer increases the speed by nine times. And we just added a new JSON data type with MySQL 5.7 that lets you store a valid JSON formatted document in a column of a table. And by using generated columns on data in that JSON column you can create SQL indexes to quickly search for data.
MySQL 5.7.12 introduces the X Protocol to help developers use MySQL as a data store. So the line between NoSQL and SQL is getting less distinct.
Bhavik: What is the best/proper way to migrate the database from one machine to another as Import / Export does not seem to be a right way when is comes to a large scale database?
Dave: I love MySQLDBCopy from the MySQL Utilities. It is designed to copy databases from one server to another without doing a dump, move, and restore. The MySQL Utilities are a group of FREE scripts our engineers have written (and are easily extensible) to take care of doing things like copying databases, performing a diff on databases, check for redundant indexes, clone a server, set up replication, and more.
Bhavik: Could you please elaborate about Storage Engines? MyISAM and BLACKHOLE
Dave: I am currently at an Oracle User Group Conference and the first question Oracle DBAs have when first investigating MySQL is ‘what is a storage engine? Most other databases have a storage engine that is fixed and unchangeable. We offer several storage engines with features for different needs. The NDB storage engine for cluster is designed for 99.999% uptime over other functionality.
- MyISAM is the Index Sequential Access Method that was developed by IBM decades ago to allow fast access to data stored on magnetic tapes. MyISAM was the first MySQL storage engine people used in the pre-5.6 days and it works well. But it is not transactional, offers table level locking instead of row level, and it has a hard time recovering from crashes.
- Blackhole does not record your data to disk or anywhere. It simply acknowledges that it received the data sent to it and there for it is very fast. There are some applications where you turn on the binary log, which separately logs the data, and have replication slaves read that log to copy the data. The master is very fast because it is not performing disk writes but this is a really rare edge case this day and I do not think the potential confusion is worth implementing it.
Bhavik: According to you, InnoDB or MyISAM – Which one is better to use and Why?
Dave: Oracle is putting a lot of time and effort into InnoDB as it is ACID compliant, locks records at the row level, and recovers from crashes very well. It has been the default for the last two releases. It used to be that MyIsam had a small performance benefit for heavy reads and was the way to go but since 5.5 InnoDB performs better and keeps your data safe.
Bhavik: What things do we need to keep in mind while designing a database in MySQL for a large application so that it does not hamper the speed of the application?
Dave: Your data! Normalize it! Break it down to at least the third normal form or Boyce-Codd Normal Form at a minimum and fourth or fifth normal form if possible. This is VERY hard work. Separate the stuff that changes frequently from the information that does not get updated on a regular basis. Try to keep each table lean and mean. Then resist efforts to de-normalize as you end up with duplicate fields with only one getting updated, confusing schemas, and it will bite you where it hurts.
Bhavik: What are your future plans in regards to your work?
Dave: I will be evangelizing MySQL, acting as a conduit to Oracle management when people need help, and helping people have happy healthy databases.
Bhavik: What is your advice to beginners who are looking to build a career in IT Industry?
Dave: There is a big demand for Database Administrators RIGHT NOW. I get several emails each week looking for talent. There is also a big demand for software developers who can exploit the power of a database by writing good queries, avoiding performance killers like the N+1 Problem, and letting the database do the heavy lifting.
One of the great things about MySQL is that you can load it on an old laptop for free, work through a book on the subject or online tutorial, and learn the product with only the cost of your time.
We are grateful to Dave Stokes for taking out his precious time to answer our questions and share some more knowledge on MySQL.