Pythian Group

Over the weekend, I worked on a client’s two computers, trying to get a slave in sync with the master. It was during this time that I began thinking about:

a) how this never should have happened in the first place.

b) how “slave drift” could be kept from happening.

c) how this is probably keeping some businesses from using MySQL.

d) how MySQL DBAs must spend thousands of hours a year wasting time fixing replication issues.

I’ll be the first person to tell you that the replication under MySQL is pretty much dead-simple to set up. My only complaint is that it is annoying to type in the two-line “CHANGE MASTER” command to set up a new slave. Even so, it makes sense.

It is also very easy, however, for a slave to end up with different data than the master server has. This can be caused by replication bugs, hardware problems, or by using non-deterministic functions. Without proper permissions, a user/developer/DBA can log into the slave server and mess the data up that way. This last is a database administrator problem, but it affects replication. There are probably other issues that astute readers will point out.

I would like to point out one common issue that would probably be categorized as a replication bug. If the master crashes for whatever reason (say, a hosting company accidentally punches the power button on a master server) it will often cause corruption of the binary log. When the master comes back up, the slave cries about a non-existent binary log position.

Possible solutions: Read the rest of this entry . . .

If you track the database world outside of MySQL, you know that Oracle is having a conference this week. It’s called Oracle Open World. Drips with irony doesn’t it? But this post isn’t about Oracle being open or otherwise.

This post is about the announcement being made Wednesday. It seems Oracle has a surprise. A pretty well kept surprise. It’s such a big deal that Larry Ellison himself is making the announcement.

Some people, including some of my colleagues at Pythian, are speculating that this is going to be an announcement about a share-nothing clustering solution.

In the first quarter of 2007, I interviewed with a company in Atlanta, seeking my first full-time job as a MySQL database administrator. They were an online company building a social-networking website with a virtual world interface (kind of like Second Life, from what I understood). They were using an (at the time) fairly unstable version of MySQL 5.1 only because it offered clustering with the ability to store data on disk while keeping the indexes in memory. Previously, in version 5.0, everything had to be stored in-memory. Much has improved with MySQL clustering since that time.

While I don’t know for certain that Larry is going to announce in-memory clustering, I kind of hope that is what it’s all about, because it would demonstrate this: Oracle is walking a trail blazed by MySQL.

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!

Read the rest of this entry . . .

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:

Read the rest of this entry . . .

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?

Read the rest of this entry . . .

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.

Read the rest of this entry . . .

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.

Read the rest of this entry . . .

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/.