Friday, October 07, 2005

What I think about MySQL

After my series on MySQL to an Oracle DBA (here, here, here, here, and here), a lot of people have asked what I think about MySQL.

I'm not really qualified to compare the two. I think I know Oracle much better than I know MySQL, so the comparison would be jaded. Some people claim that MySQL is much easier to setup and maintain than Oracle. I'm not completely convinced of that. Each has their own set of hundreds of parameters that can be tweaked. Having installed MySQL from source a couple times, I'd actually say it's a little harder to setup in certain circumstances. Not hard, just harder.

I'm very intrigued by MySQL's backup methods. At first glance mysqldump looks more like Oracle's export program than a backup tool. An Oracle DBA would scoff this off as single point in time snapshot of the data. Of course, we Oracle DBA's know you can't import and then apply archived redo logs, but imagine a scenario where you could. Appearantly MySQL has the ablity to import and re-apply the bin log (kind of like the redo log). This concept was tossed about in the class and I'm anxious about trying it out.

I'm a little concerned about connection scalability with MySQL. The MySQL server is bound to one port. In the coming months I'll be stress testing high frequency connections to see of they scale.

I think Oracle gives you much more information about the health of the database through views than MySQL does. With Oracle, I can query v$this, x$that, and dba_whatever to tell me certain things about how the database is performing. In addition, I can write tools using these queries to automate my job and alert me to when certain conditions arise. MySQL has a lot of SHOW commands, but it's difficult to get this information in an automated fashion without some pipes, grep, and awk. Maybe I just don't know enough about it yet.

One thing I really like about MySQL is the concept of a "slow log". Here, queries that run longer than a specified threshold are logged to a "slow log" that can be investigated later. My developers will hate me. I wonder if it's to obnoxious to set my threshold at 60 seconds. Hmmm...

We'll definitely be using MySQL. It will start off with backoffice apps and progress to bigger apps. I've got half a mind to port my Oracle monitoring app to MySQL just for kicks. I'll be updating this blog as I find interesting things about MySQL.

9 comments:

Thomas Kyte said...

import your data and apply the archived redo logs to the database. This concept was tossed about in the class and I'm anxious about trying it out.

don't bother, archives can only be applied to a backup of the database - to a datafile.


If you import data into the database, you might "logically" (stress might :) have the same data but physically - it is a different, new database.

No archives could be applied to an import to "catch it up"

Anonymous said...

MySQL has a lot of SHOW commands, but it's difficult to get this information in an automated fashion without some pipes, grep, and awk.

Not really... the SHOW commands return a data structure as if they had been SELECTs. Just try and handle SHOW (also: DESCRIBE) like SELECT, and you'll see that automation is easy, without any text parsing.

Jeff Hunter said...

don't bother, archives can only be applied to a backup of the database - to a datafile.

Yeah, I wasn't very clear on that. Edited for clarity...

Anonymous said...

Regarding: "it's difficult to get this information in an automated fashion without some pipes, grep, and awk." -- I agree. Although it's not an intrinsic solution like Oracle has, http://hackmysql.com/mysqlreport is at least a little more automated, and a lot more insightful than SHOW STATUS alone.

Mike Hillyer said...

Jeff;

Don't believe everything you hear ;)

mysqldump and mysqlhotcopy can both mark their binlog position so that you can restore and then use the binary log to replay queries performed since the backup.

Now is mysqldump/mysqlhotcopy + the binlog good enough for an Oracle DBA? I'd love to see you try it and blog whether you think so!

Jeff Hunter said...

anonymous said...
http://hackmysql.com/mysqlreport

Thanks, I'll have to check that out.

mike hillyer said...
mysqldump and mysqlhotcopy can both mark their binlog position so that you can restore and then use the binary log to replay queries performed since the backup.


That's what I was trying to point out; I guess not very effectively.


good enough for an Oracle DBA?
We'll see. That's one of my projects to be featured in a blog shortly thereafter.

my word: morho - mo money, morho?

Howard J. Rogers said...

Interesting...

Please visit:

http://www.dizwell.com/downloadables/badquote.pdf

..and explain to me why things which I take are quotes from their context appear as "/" every time!

Gary Myers said...

Any views on the Oracle buyout of the makers of the InnoDB storage engine ? For internal developments, you've got the security of the GPL for the engine so it might not really matter for you.

On of the comments :
http://jeremy.zawodny.com/blog/archives/005490.html suggests that its it could be shoring up the Apps/ERP side (where a lot of their dollars are being spent) by having some muscle when it comes to SAP support for MySQL.

Jeff Hunter said...

Any views on the Oracle buyout of the makers of the InnoDB storage engine?
Oracle has been involved in open source development (reluctantly or not) for a while. They seem to be buying companies lately that don't fit into their core products. Then again, I don't own a multi-billion dollar company, so what do I know?