Hey everyone — it’s time to send in your article proposals for the next issue of MySQL Magazine, which is scheduled for release on October 15^th. The deadline is end-of-September, so don’t delay. You too can become rich and famous by writing for MySQL Magazine! Just send me your idea for an article to bmurphy AT paragon-cs.com.

For those who don’t know, MySQL Magazine is a quarterly publication, “by the community - for the community”, free and available for download from http://www.mysqlzine.net.

Looking forward to hearing from you!

A few days ago I had a new idea for a blog post. A post about what it really takes to be a good database administrator. I began by researching what others had done on the topic. At the end of this post you will find links to six of the posts I found that provided some insight into this question. Even after uncovering this information, I thought I could add something to the mix from my own experiences. So here we go!

(more…)

If you work with databases long enough, you run into a certain problem. You have a mysqldump file* of a table or a database, and you need to import it into your new database. However, you need to change something in the file first. Maybe the INSERT statements need to be changed to INSERT REPLACE. You fire up vi and load the file, but when you go to search and replace, vi runs out of memory and doesn’t complete the operation. Or maybe the dump file is just so big it won’t even load in the first place. What do you do in this situation?

Well, one simple solution is to use the sed tool to modify the file. Sed actually stands for “stream editor”. The vi editor would be considered a static editor in that it loads all of the file into memory at once. If you run out of memory, you are out of luck. With sed there is a very limited amount of data in memory at any time because it streams the data “through”, manipulating it as it goes. So sed can work with files that are huge, and only use a minimal amount of memory for processing.

The format of the search and replace also is similar to vi's search and replace. For example:

(more…)

I recently needed to set up multiple MySQL servers on a test computer to simulate a master-slave setup. I had never done this before, so I think it might be useful for others if I documented what occurred. This setup was done on a Linux server, however other platforms should operate similarly. I choose to use the mysqld_multi script to manage these instances. There is a way that you can compile the MySQL server with different network interfaces, but in my opinion, using mysqld_multi to manage activities is much easier.

So shall we begin?

(more…)

Welcome to the 107th edition of the Log Buffer. My name is Keith Murphy and I am a MySQL database administrator for the Pythian Group. In addition, I am the editor of MySQL Magazine. This is my second go for the Log Buffer, so I must be doing something right!

This week for the open source world brings OSCON in Portland Oregon. There are plenty of MySQL people present and there have been more posting this week from these realms than normal. Also, Lewis Cunningham, among others, posted news that EnterpriseDB released the results of their open source survey at OSCON. The 451 CAOS Theory published their thoughts on the survey.

Of interest to all DBAs is John Duncan’s post about what is called the “five minute rule”. This was introduced in 1987 by Jim Grey. And finally, before we dive into the specific server news, here is a post on Facebook’s project to build a distributed database similar to Google’s BigTable.

MySQL News:

Friday saw the release of the summer issue of MySQL Magazine. The highlights of the issue include the first annual MySQL Usage Survey. The magazine is available here. Peter, over at Percona, shows some initial benchmarks for the latest version (0.9.8) of Sphinx. If you aren’t familier with Sphinx, it is a full text search engine that easily integrates with MySQL.

Probably the biggest news this week was the announcement by Brian Aker of “Drizzle”. It is what amounts to a slimmed down version of MySQL server. These comments are from his initial post “Stored Procedures, Views, Triggers, Query Cache, and Prepared Statements are gone for now.” Interesting. My co-worker, Sheeri K. Cabral, posted a video of Brian Aker talking about the Drizzle project at this week’s OSCON. Monty Widenius writes a good summary how Drizzle can/might integrate with the MySQL “ecosphere” at large. It was very good to hear Monty say that Sun management is encouraging this project. There has been some other interesting posts about this as well including Arjen’s thoughts, and Paul McCullagh’s. Brian Moon gives a very thoughtful view on how Drizzle could potentially fit in at dealnews. While I could probably fill up the entire Log Buffer with links to post about Drizzle, I better leave it at that.

(more…)

Let me first say that the PBXT storage engine has some great people behind it. At the users conference last April, I had a chance to meet Paul McCullagh, who created PBXT, and some of the people who work on it. They are dedicated individuals who are creating something unique.

Like the InnoDB storage engine, which is backed by the Innobase company, PBXT has a company that backs it, Primebase Technologies. This means that if needed, support can be got from the company that created the product. For enterprise companies this might be important.

The basics characteristics of PBXT:

• MVCC: Multi-version concurrency control, enables reading without locking.
• Transactional: Support for BEGIN, COMMIT and ROLLBACK and recovery on startup.
• ACID compliant: Atomic, Consistent, Isolated, Durable (once committed, changes cannot be lost).
• Row-level locking: updates use row-level locking, allowing for maximum concurrency.
• Deadlock detection: immediate notification if client processes are deadlocked.
• Referential Integrity: foreign-key support.
• Write-once: PBXT avoids double-writes by using a log-based architecture.

Much of this is the same as for the other transactional storage engines, so I won’t spend time on them. What sets PBXT apart from other storage engines is the write-once characteristic. It is worth understanding.

(more…)

The next issue of MySQL Magazine is now available for download. Get it while it is hot! At forty-two great pages it is our biggest and best issue yet.  This issue is anchored by the first annual MySQL Usage Survey results.

Downloads at the MySQL Magazine homepage: http://www.mysqlzine.net

Thanks to everyone who contributed.  I couldn’t have done it without you all!!

I just put together on “meetup.com” the first Pensacola MUG meeting.  The first meeting is scheduled for August the 5th at 7:00 pm at the Panera Bread right outside the entrance to Cordova Mall.

I have thoroughly enjoyed the previous user group meetings I have attended and wanted to see if we can get a group growing here in Pensacola.  No formal agenda for the first meeting, just want to meet everyone and find out what people want to hear about in the future. I can be reached @ bmurphy AT paragon-cs.com.

The information and signup is at: http://mysql.meetup.com/300/.

It’s time to continue our series on the transactional storage engines for MySQL. Some might question why I even include Falcon because it is very much beta at this time. MySQL, however, has made quite an investment into Falcon, and while it is currently beta, the code is improving and it looks like that it will be production-worthy when MySQL server 6.0 hits GA.

If this is the case, it is important to begin to understand what Falcon was designed for and how it differs from other transactional engines such as InnoDB. I am going to concentrate quite a bit on the Falcon/InnoDB comparison as that is what everyone wants to talk about. This is despite my having heard MySQL employees repeatedly make statements to the effect of, “Falcon is not going to replace InnoDB,” or “Falcon is not competing with InnoDB.” Well, take that with a grain of salt. It certainly seems to me that they are competing for the same spot.

Warning

As I said, Falcon is beta. First off, don’t even try to use it in production. Using it in production means you will also be using MySQL Server 6.0, which itself is considered alpha. Your data will explode, be corrupted, or eaten by jackals. It won’t be pretty. It will cause great pain.

In addition, the features of Falcon are still changing. What I say here might or might not be accurate in the future.

End of Warning

So, why was Falcon even created?

(more…)

Seems I have turned into a bit of a news source. dbWatch Software sent me a news release on their dbWatch monitoring platform, which looks like it might be an interesting product for those who work in a heterogeneous database environment. Here’s the release:

FOR IMMEDIATE RELEASE

Contact:

Rob Shuster, VP US Sales
dbWatch USA
6122B S. Eagle Pass Rd.
Gold Canyon, AZ
85218

Phone: 800-270-9892

http://www.dbwatchusa.com

info@dbwatchusa.com

dbWatch Software Announces US based Sales & Support Oslo, Norway — June 23, 2008 – dbWatch Software has launched a US sales and support organization. The flagship product, dbWatch™ (v8.1), is a heterogeneous database monitoring/reporting tool currently supports Oracle, SQL Server, and MySQL.

Marek Jablonski, CEO states “As a DBA you are often stuck with multiple platforms (both OS and database systems) and multiple unmanageable tools from database providers. dbWatch™ is a single tool allowing the DBA to monitor all databases and generate professional custom reports.”

Clear Channel Norway IT Director, Jan Erik Rasmussen, reports “dbWatch provides an effective overview of our databases, and allows me to sleep well at night.”

Three dbWatch™ editions are available: Standard ($195), Professional ($1,995 to $9,995), and Enterprise ($14,995). A free 30-day evaluation is available for download.

For information: http://www.dbwatchusa.com or

Contact: info@dbwatchusa.com

Phone: 800-270-9892

It was a timely contact, coinciding with Sheeri’s review of MONyog. It sounds like dbWatch is the kind of tool that would be valuable here at Pythian, where we work with all of the DBMSs it supports. But — I haven’t tried it yet, so I put it to you: have any of you used dbaWatch? If so, what are you opinions?