Pythian Group

OpenSQLCamp was a huge success! I took videos of most of the sessions (we only had 3 video cameras, and 4 rooms, and 2 sessions were not recorded). Unfortunately, I was busy doing administrative stuff for opensqlcamp for the opening keynote and first 15 minutes of the session organizing, and when I got to the planning board, it was already full….so I was not able to give a session.

• Comparing Non-Relational Databases: MongoDB, Tokyo Tyrant, CouchDB by Igal Koshevoy of Pragmaticraft
• Drizzle Client Rewrite – Clark Boylan leads the requirements and design discussion for rewriting the Drizzle Client
• Drizzle Plugin Hacking
• Drizzle is Not MySQL with Changes – Brian Aker gives a talk on Drizzle, and how Drizzle is not MySQL.
• Fractal Trees – by Bradley Kuszmaul of Tokutek
• Geographic Operators in SQL (PostGIS) by Webb Sprague
• Goal-Driven Performance – Peter Zaitsev speaks about how to achieve goal-driven performance.
• Graph Engine for MySQL – by Antony Curtis
• Intro to Cassandra
• An Intro to CouchDB: What caught Ubuntu’s eye by Mike Miller (Cloudant)
• Maria – MySQL founder Monty Widenius talks about Maria, and the past, present and future of MySQL.

Read the rest of this entry . . .

OpenSQLCamp was a huge success! Not many folks have blogged about what they learned there….if you missed it, all is not lost. We did take videos of most of the sessions (we only had 3 video cameras, and 4 rooms, and 2 sessions were not recorded).

All the videos have been processed, and I am working on uploading them to YouTube and filling in details for the video descriptions. Not all the videos are up right now….right now all the lightning talks are up.

• All the lightning talks belowin one continuous video (just over 54 minutes total)
• The Graph Engine (Antony Curtis)
• Cluster/J, a new set of Java APIs to MySQL Cluster 5.1 (John David Duncan)
• Sphinx, the fulltext storage engine (Peter Zaitsev)
• iiBench, the Indexed Insertion Benchmark (Bradley Kuszmaul of Tokutek)
• JJtree in Coco
• Integrating OSS with Windows (Tom Hanrahan of Microsoft)
• Trainwreck, an agent for MySQL replication (Domas Mituzas)
• Column Stores (David Lutz of Infobright)
• I Play With Data, about doing real statistical calculations of data, when SQL gets in the way (Joseph di Paolantonio)
• Your Guide to NoSQL (Brian Aker)
• PL/Parrot The call for PL/Parrot in Postgres is put out there (Jonathan “Duke” Leto)

Read the rest of this entry . . .

For the past two months, I have been running tests on TokuDB in my free time. TokuDB is a storage engine put out by Tokutek. TokuDB uses fractal tree indexes instead of B-tree indexes to improve performance, which is dramatically noticeable when dealing with large tables (over 100 million rows).

For those that like the information “above the fold”, here is a table with results from a test comparing InnoDB and TokuDB. All the steps are explained in the post below, if you want more details, but here’s the table:

Read the rest of this entry . . .

Mike Hogan, CEO of ScaleDB spoke at the Boston MySQL User Group in September 2009:

ScaleDB is a storage engine for MySQL that delivers shared-disk clustering. It has been described as the Oracle RAC of MySQL. Using ScaleDB, you can scale your cluster by simply adding nodes, without partitioning your data. Each node has full read/write capability, eliminating the need for slaves, while delivering cluster-level load balancing. ScaleDB is looking for additional beta testers, there is a sign up at http://www.scaledb.com.

Slides are online (and downloadable) at http://www.slideshare.net/Sheeri/scale-db-preso-for-boston-my-sql-meetup-92009

Watch the video online at http://www.youtube.com/watch?v=emu2WfNx4KA or directly embedded here:
Read the rest of this entry . . .

This is the 137th edition of Log Buffer, the weekly review of database blogs. Dave Edwards is enjoying a week off, and so as part of my plot to take over the world, I am writing this week’s Log Buffer.

First, the fun stuff: Josh Berkus tells us that the American English Translation of the Manga Guide to Databases is available in Japanese Fairies and Third Normal Form.

Then, the basics:
Giri Mandalika points to an article on Using MySQL with Java Technology. This is a basic article on how to connect, and does not go into all the wonders that Connector/J can really do. Speaking of basics, Decipher Information Systems has an article on Back to the Basics: Refreshing a Regular View Definition in SQL Server and Oracle. Chet Justice, the Oracle Nerd, writes about transactions in ORA-8177 Can’t Serialize Access For this Transaction. There is theoretical information about isolation levels that applies to all database systems. Mats Kindahl explains some transactional theory when talking about why Mixing Engines in Transactions in MySQL can get you into trouble.

It’s hard to get more basic than what data type to use for a single character Read the rest of this entry . . .

If you are using InnoDB Hot Backup and a recent version of mysqld (at least 5.0.67 or higher, including 5.1.30, though it may be later versions), your backup will run fine and output OK! at the end, as it should.

Except for one thing.

The binary log file and position do not appear in their rightful place. Here’s a snippet of the output from the backup:

innobackup: MySQL binlog position: filename 'Warning', position (Code 1287):
'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead
090210 03:55:04  innobackup: innobackup completed OK!

That’s pretty misleading — looks like the backup completed OK, but it did not show us the binary log position. What about the ibbackup_binlog_info file?

[mysql@db3:~] more ibbackup_binlog_info
Warning (Code 1287): 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead
[mysql@db3:~]

Yikes! What happened to the binary log position and file information?

Have no fear, it’s actually in another file.

Read the rest of this entry . . .

In a nutshell: What’s New in MySQL 5.1.

Release notes: Changes in release 5.1.x (Production).

And yes, very early on (at about two minutes in), I talk about my take on Monty’s controversial post at Oops, we did it again.

To play the video directly, go to http://www.youtube.com/watch?v=Hs4S7vONGMQ. Or watch it embedded inline here:

Read the rest of this entry . . .

Architecture of Maria: A New Storage Engine with a Transactional Design

Goals of Maria:

• ACID compliant
  
• MVCC, transactional
  
• default non-transactional and default transactional storage engine for MySQL
  
• MyISAM replacement, including temporary table use
  
• Storage engine good for data warehousing.
  
• Allow any length transactions to take place
  
• all indexes should have equal speed (clustered indexes are not in the plan)
  
• log shipping — incremental backups just by copying the logs
  
• used as a standalone library
  
• fast count(*)
  
• allow copying of Maria tables between different Maria servers
  
• Better blob handling (than MyISAM) — no memory copying, or extra memory used for blobs on INSERT/UPDATE
  
• BLOBs are allocated in big sequential blocks means that there is less fragmentation over time.
  
• BLOBs are stored so Maria can be easily extended to have access to any part of a BLOB with a single fetch in the future

Maria is actually faster than MyISAM.
Will support READ COMMITTED and REPEATABLE READ (was a specific question).

“You should be able to upgrade Maria and have it just work, without ever having to backup and restore as part of the upgrade.”

Why do we need 2 storage engines, Maria and Falcon?
More storage engines = more scenarios to be able to handle. Falcon good for lots of memory, and shorter queries, Maria is to take care of long transactions and data warehousing side, and also to be an overall “good” engine for most purposes (at least as good as InnoDB).

Project plan — MySQL 5.1 and higher. Maria strives to be bug-free.

Maria 1.0 = “crash safe”, released in Jan 2008. Cacheable row format.
Maria 1.5 = “concurrent insert/select”, Apr/May 2008. Will be merged into the MySQL 6.0 release. Non-transactional.
Supports:

• External/internal row CHECK/REPAIR/compressing
  
• Different row formats — fixed size, dynamic length, compressed, page
  
• Fast checksum of a table
  
• maria_check
  

MySQL 6.0 (includes Maria)
Maria 2.0 = “Transactional and ACID” alpha, Q4 2008
Maria 3.0 = “High concurrency + Online backup” alpha, Q1 2009; GA, Q2 2009

Advantages of Maria compared to MyISAM

• Data, index and statistics are crash safe. On crash, everything will rollback to the state of the start of the statement or last LOCK TABLES commands.
  
• Can replay everything from the log. Still need to backup the .frm files though.
  
• LOAD INDEX can skip index blocks for unwanted indexes
  
• most parts of Maria have unit tests.
  
• Supports all MyISAM formats and a new PAGE format, where data is stored in pages — this is:
  
  
  
  • Only crashsafe, transactional row format for Maria
    
  • Versioned, so you can have concurrent insert/select
    
  • Cached by page cache so will give noticeable speed improvement on systems with poor data caching
    
  
  
• MyISAM is non-transactional only, Maria can support transactional (crash-safe) and non-transactional formats
  
• Maria uses big log files by default — 1G
  
• Log control file = maria_log_control
  
• Log files = maria_log.?????
  
• If a log file is already in use by a transaction, create a new one, otherwise, reuse it. SHOW LOGS will show which logfiles exist, which ones are in use, and you can PURGE the logs.
  
• Maria uses 8K pages by default (MyISAM uses 1K pages by default. This should be faster on static size indexes but slower on variable length keys, until Maria 2.0 where there will be a directory for index pages.
  

Before Apr/May release of Maria 1.5

• Statistics and indexes versioned (data is already versioned)
  
• FULLTEXT (boolean mode) and R-TREE indexes need to be made crash-safe
  
• Parallel recovery
  

Disadvantages, not likely to be fixed

• No external locking
  
• One page size for both index and data
  
• only one page cache
  
• index number and checksums require an extra 5 bytes per page
  
• doesn’t support RAID
  
• Minimum data file size for BLOCK format is 16K, with 8K pages
  
• Storage of rows < 25 bytes are not efficient for the PAGE format
  

XDB indexes — group records, and get the min and max for each group (groups are not sorted), and save that in a separate block. This means that stats like min and max are very fast. This is something that will help data warehousing queries.

Resources:

• Maria worklog (tasks to do)
  
• 5.1 Storage Engine documentation — note that Maria docs have not made it to the manual yet.
  
• http://monty-says.blogspot.com
  
• Download Maria
  
• FAQ and answers about Maria
  
• Maria Documentation on the MySQL Forge.
  

Best quote from Monty: “You don’t steal ideas in open source. You respect people.”

Someone asked me what applications were good/bad for MySQL Cluster. As I’ve now actually had experience with a Cluster setup and a real-life application of it, and dug through the manual, I present a few characteristics of applications that will work with Cluster, and why they are so (so that if you have an application that meets some of the characteristics but not all of them, you can decide whether it’s worth it to use Cluster or not).

Firstly, I’ll state this — there’s actually a very limited application to MySQL cluster. I haven’t assessed the disk-based cluster, only the memory-based one, so I don’t know what really changes with disk-based. But after you see this list, you certainly will want to re-think your use of disk-based cluster if a lot of the inner workings don’t change.

The factors are listed below, but the “ruler” I keep in my mind is the fact that MySQL Cluster was developed for telecom applications. The basic characteristic is a lot of writes, small data in amounts that can fit into memory, and the data being transient in nature. Something like a “session” table is a great application.

So here are the characteristics of a good application for MySQL Cluster, with explanations of why: Read the rest of this entry . . .