Pythian Group

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?

Read the rest of this entry . . .

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?

InnoDB is a storage engine that uses MVCC (described shortly) to provide ACID-compliant transactional data storage using row-level locking.  MVCC stands for Multi-Version Concurrency Control.  It is how InnoDB allows multiple transactions to look at a data set of one or more tables and have a consistent view of the data. MVCC keeps a virtual snapshot of the dataset for each transaction.  An example will make this clear.

Let’s assume you have two transactions (and only two transactions) running on a system. If transaction A starts at 10:45:56 and ends at 10:45:89, it gets a consistent view of the dataset during the time that the transaction runs.  If transaction B starts at 10:45:65, it would see exactly the same view of the dataset that transaction A saw when it began the transaction.  If transaction B started at 10:45:95, it would see the modified dataset after transaction A made modifications. During the duration of each transaction, the dataset that each sees does not change, except for the modifications the transaction itself makes.  Consider that a typical production database server is running hundreds of queries a second, and you realize that the job of MVCC/the InnoDB storage engine gets very complicated maintaining all these views of the data.

MySQL server storage engines use three different locking options: table-level locking, page-level locking, and row-level locking.  With table-level locking, if a query accesses the table it will lock the entire table and not allow access to the table from other queries.  The benefit of this is that it entirely eliminates deadlocking issues. The disadvantage is that, as mentioned, no other queries have access to the table while it is locked.  If you had a table with 16,000,000 rows and needed to modify one row, the entire table is inaccessible by other queries. The MyISAM and memory storage engine use table-level locking.

Page-level locking is locking of a group of rows instead of a the entire table. The number of rows actually locked will vary based on a number of factors. Going back to our example of a 16,000,000-row table, lets assume a page-level lock is used.  If a page consists of 1,000 rows (this would vary depending on the size of the rows and the actual amount of memory allocated to a page), a lock would lock only a thousand rows.  Any of the other 15,999,000 rows could be used by other queries without interference. The BDB storage engine uses page-level locking.

Row-level locking, as the name suggests, acquires a lock on as small an amount as a single row from a table. This will block the minimal amount of table content and allows for the most concurrency on a table without problems. InnoDB and Falcon both use row-level locking.

While the InnoDB configuration options are not strictly related to the transactional characteristics (other than innodb_flush_log_at_trx_commit), I thought it would be useful to have them here for reference.  These are the most common or most important configuration parameters:

Read the rest of this entry . . .

I began to write a post on InnoDB transactions, but there was so much background material that I decided first to write a post introducing transactions, and then one on how InnoDB implements them.  If there is good response from these two posts, I will continue with other posts on the major storage engines and their transactional characteristics.

When reading through literature about RDBMSs (Relational Database Management Systems), you will see the terms transaction(s), and ACID-compliance.

A transaction is a sequence of statements that are executed on a RDBMS. For a transaction to be completed and database changes made permanent, a transaction has to be completed in its entirety. If a transaction is not completed for any reason, it is rolled back to the original state of  the database before the transaction began execution. The characteristics that define a transaction are called ACID.  ACID stands for: Atomicity, Consistency, Isolation, and Durability.

According to Wikipedia’s article on ACID, it is “a set of properties that guarantee that database transactions are processed reliably.” It is useful to understand the different parts of ACID, so here they are briefly:

Atomicity

The ability of the database to guarantee that either every part of the transaction is performed or none is performed.

Consistency

The database remaining in a consistent state before the start of a transaction and after the transaction is finished.

Isolation

The ability of the database to make operations in a transaction appear isolated from other concurrent operations.

Durability

The guarantee that once the user has been notified of success, the transaction will persist through all conditions, including system failure.  Durability is frequently accomplished through writing all transactions to a log file.  This log file can be “replayed” in the event of a system failure.  It is only after the transaction is written to this log file that the transaction can be considered safe.

So now you have some background.  Next time, we will take a look at how the InnoDB storage engine implements transactions.

As an editor (of http://www.mysqlzine.net), I cringe at the title of this post. However, it is absolutely accurate.  Recently, we had a situation where we had two servers running Sun Solaris 10 on some high-end Sun hardware.  I don’t remember exactly, but it was one of Sun’s upper-end boxes with AMD procs. Nice boxes, really. The two servers are configured in a master-master circular replication setup.

Here is the problem.  On both servers, the error logs were being created incorrectly.  On one of them, it was creating an error log that was 154 megabytes in size.  FLUSH LOGS worked, but the newly-created error log would be the same size. While there was some data in the file that I could use the cat, head or string command to discern, the majority of the file was not text data.

After working on this for a bit, I logged into the secondary server and discovered that the error log on this server didn’t look right either  — the same characteristics of large size with almost no actual text data.  The only difference is that these error logs were around 20 megabytes in size. I googled around a bit and couldn’t discover anyone with a similar problem.

I can’t figure out what was causing this.  We checked everything we could think of, and during some other maintenance, restarted the mysqld daemon. That didn’t work either — when the server came back online it was experiencing exactly the same problem with the error log. Finally, during hardware maintenance to upgrade the memory, the servers were rebooted.  The next morning, I checked them, and found both error logs working exactly as they should. However, it took that server reboot to fix the problem.

I am at a loss to explain what was wrong.  If anyone has any thoughts or a similar experience, I would love to hear from you!

I just wanted to thank everyone who participated in the survey that Mark Schoonover and I created. My endless thanks goes to Mark who did a lot of work on this.

The results will be coming out in the Summer issue of MySQL Magazine which will be online July the 15th. I am putting together the articles now and it looks like it’s going to be a great one!

Recently, a customer wondered if they should start using the innodb_file_per_table option, or if they should continue to use the large InnoDB tablespace files created by the innodb_data_file_path option in the my.cnf option file.

Many people still use the older innodb_data_file_path option because it is the default for MySQL server. So, what are the benefits of using innodb_file_per_table instead?

The innodb_file_per_table makes for easier-to-manage files. With this option each InnoDB table has its own data and index file under the database directory. As an example, if you had table foo located in database xyz the InnoDB data file for table foo would be /var/lib/mysql/data/xyz/foo.idb. Each table would have its own idb table in the appropriate database directory. This is in contrast to using the innodb_data_file_path option with (typically) one large file in the root of your data directory. For example, it might be /var/lib/mysql/data/ibdata1.idb. All table data and indexes would be stored in this one file, and it can be very large  and unwieldy. I don’t recall the largest ibdata file I have seen, but what do you do if you have a 100 gig InnoDB tablespace file? It can, and does, happen. The file contains what amounts to all the data of all your databases on the server.

Read the rest of this entry . . .

I have a question I wanted to throw out. The term “GA” gets batted around all the time as meaning, the production-ready version of MySQL server. However, googling for quite a bit, I can’t find a definition for GA (other than what I stated above, i.e. production-ready). What does this mean in terms of bugs? Features? Anything else I might be missing?

I believe it means that there are no known “critical” (whatever that means) bugs and there will definitely be no more features added. Can anyone point me to a good definition?

In case you haven’t heard, on Monday, MySQL released the next RC of 5.1.25. It is available to the community, so download it now and take it for a spin!

http://dev.mysql.com/downloads/mysql/5.1.html.

I finished uploading the backup presentation that I did last Monday at the Boston MySQL User’s Group. It is here: http://www.paragon-cs.com/presentations.

I cover the basics of backup/recovery and disaster planning. Total time is about an hour and three minutes. It was a lot of fun and the Bostonians seem to appreciate it. Thanks, Sheeri for filming and editing!