Wednesday, September 21, 2005

MySQL to an Oracle DBA, Part III

Today is where we started digging our DBA teeth into MySQL. As with any database, the first thing you must do is install the software. MySQL comes in a couple different flavors depending on the platform and how you intend to use it. For example, on Linux you can install via RPM's, precompiled binaries, or source. On Windows you can install via a Windoz Installer, precompiled binaries, or source. While the quickest way to install is via RPMs or Windows Installer, you can build your mysql software tailored specifically for your hardware and the options you want to use. I chose to install from Windows Installer on Windows XP so I could work with my own laptop.

Once installed, you have to go through the configuration process. A lot of the configuration process is similar to Oracle; data goes here, logfiles (transaction logs) go on a seperate filesystem, the message logs go another place, etc. Similar to Oracle, you setup memory parameters and other features you want installed in the instance.

One interesting feature of MySQL is that you can store your tables using different "Storage Engines". A storage engine is basically the method used to access your data. You can use any number of storage engines in your database. The main storage engines are MyISAM, InnoDB, Memory, Merge, Berkley, and NDBCluster.

Why would you want to choose your storage engine? You can assign the storage engine based on what type of activity the table will have and the features you need.

Storage EngineAdvantagesDisadvantage
MyISAMExtremely fast for QueriesCan't use transactions with, Concurrent INSERTs don't scale because the entire table is locked,Dirty Reads
InnoDBCan use transactions, multi-versioning read consistency, DML scales wellQueries slower than MyISAM
MemoryTables stored directly in memory. Access is really fast. Best for TEMP type tablesTables stored directly in memory. DB Goes down, your data is toast.
MergeCan present two identical tables as one table. Kind of like a materialized view, kind of like a partitioned tableMust be MyISAM tables
BerkleyOffers transactions.Older technology
NDBClusterSupports transactions and clusters, highly scalableNot widely used.
FederatedCan store data on a seperate serveryour I/O is limited by bandwidth

Last, but not least, we talked about security. We went through the same things you would do to secure an Oracle database; protect the root OS user, protect the mysql "root" (or SYS) user, use strong passwords, protect remote root logins, etc. One interesting concept is that MySQL users are identified not only by a username, but by the host they can login from. For example, jeffh@localhost is a totally different identity than jeffh@webserver. You grant permissions to both user depending on how you want to want them to access your data. If you want the root user to only login on the server itself, you only create root@localhost. This lets you setup access rules such as "When user jeffh is logged into the database on an internal domain, he can INSERT, UPDATE, DELETE, SELECT from my tables. When user jeffh is logged in from the VPN, he can only SELECT from my tables". That's kind of cool.

Two side notes: I leared about google sets and gvim (vi for windows).


Noons said...

Something that I never sorted out: can you have multiple storage engines inthe same database? Because if not, then what is the point of "memory" for TEMP type objects if one can't put them anywhere else in the same db?

Yeah, google sets absolutely rawks. I've suggested to them they allow for sorted results. Darn useful little tool.

Jeff Hunter said...

Something that I never sorted out: can you have multiple storage engines inthe same database?
yup, you can use multiple storage engines in the same instance. You can even turn certain storage engines off in your my.cnf file if you don't want your developers using them.

Anonymous said...

Oeh I really must comment on that storage engine list... a couple of points:

- Concurrent inserts (at the "end" of the table" don't block selects, MyISAM actually exploys 3 different types of locks: read (shared, for selects), write (exclusive, for updates), and local (for concurrent inserts).
- Dirty reads can NOT occur. That's prevented by the locks, same as in any other RDBMS (concurrency control).

Think data warehousing, logging... lots of new data inserted, either separately or in bulk. Then, mostly selects.
This is, perhaps, where the myth comes from that MySQL would only be suitable for apps that mainly do selects.
Both by testing yourself and seeing different production setups out there, this is easily proven wrong. It's important to understand the differences in concurrency control. And of course, to pick the optimal storage engine for each individual table.

- Will need more memory to attain the speed of MyISAM. That's all. It's not fundamentally slower as such.

Of course you can use InnoDB for everything, makes life easy. If you have the RAM. But there are definitely tasks for which MyISAM is more optimal.

shantanu said...

>> Of course you can use InnoDB for everything,

Only if you don't need full text search and merge tables

Anonymous said...

hi Jeff - Nice articles on MySQL.

The link to the part IV of this series of articles is not working. Can you kindly check?


Anonymous said...

You can navigate to the IV part by clicking on "Newer Post"

this is the actual link: