The latest from the MySQL community

Hi!
It has been very hot recently.  Please take good care of yourself.

Today, I would like to deliver a tip for “MySQL Storage Engine”, which was written by Mikiya Okuno, MySQL Senior Technical Support Engineer.  Are you familiar with storage engine?  Let’s learn about the storage engine together!

************************************
MySQL is so popular and used for various applications.  One of the reasons is its flexibility which is achieved by a technology called the “Storage Engine”.  The storage engine is an essential concept to MySQL.

MySQL utilizes virtualized and abstracted layers which can be switched per table.  These layers are the storage engine.  MySQL brings out high performance by switching depending on application’s requirements and data access pattern.  The storage engine is one of the most important elements and attracting a lot of …

Kickfire and 10gen obtain new rounds of funding. BusyBox developers drop lawsuit against Supermicro. OpenLogic and EnterpriseDB release open source survey findings. (and more)

Kickfire Raises $20 Million in Series B Financing, Kickfire (Press Release)

10gen, New Cloud Computing Firm, Closes $1.5m Series A, 10gen (Press Release)

BusyBox Developers and Supermicro Agree to End GPL Lawsuit, Software Freedom Law Center (SFLC) (Press Release)

OpenLogic Releases Survey on Enterprise Open Source …

Project: MySQL Forge RSS/Atom feeds

KEY ACCOMPLISHMENTS LAST WEEK

* Write SeleniumRC base test suite
* Think about usage of PHPUnit and/or SeleniumRC and how tests should be organised
* Install linux (Debian) + all necessary software to be able to continue GSoC work while on holiday

KEY TASKS THAT STALLED LAST WEEK

* Cleaning up the mess from the different branches

KEY CONCERNS

* The Forge bazaar branch is incomplete which is difficult to work with

TASKS IN THE UPCOMING WEEK

* Write unit tests for all HiPPO classes (HiPPO is the underlying framework, see http://www.launchpad.net/hippo)
* Start improving and extending the SeleniumRC base test suite for the Forge

The OmniMySQL project is now officially known as OmniSQL (for obvious potential legal reasons). I find myself using this tool more and more, so I thought it was about time to add some new features. The new project download site is now http://code.google.com/p/omnisql/. Let me know of any bugs and/or enhancements! CHANGELOG: 0.0.5 - [...]

Until recently, I was a student employee at the Oregon State University Open Source Lab. My career there ended, like many, with that painful process known as graduation. I got invaluable experience at the lab, not the least of which being the knowledge gained as their main (only) database administrator. One of my great pleasures in that position, was learning how to configure MySQL replication and manage clusters of replicating database servers. Even the simple case of a single master and a single slave has its edge cases.

read more

I get this question allot, and my response is "it depends", people rarely like this general response. To pick a storage engine, the user must understand the gains and losses of using the storage engine, basically understand what INNODB and MYISAM do, and how they behave under different load patterns.

When picking a storage engine I go through a checklist-below is a quick list to get an idea of the thought process.

Do you require transactions?
If yes use INNODB else you may still want to use INNODB?

Are you doing a lot of big queries that Scan 20-30% of the rows?

If yes use MYISAM. It's better at doing large queries where the query requires a full table lock. INNODB will lock each row as it scans through it which hurts query throughput.


Are you building an app to store 1 row and access said row really fast and at a high concurrency?

See …

A pair of open source aficionados are using open source to help school children build computer skills; find out how you can help. READ MORE

Eric of Proven Scaling commented on a lack of IRC action in the normal mysql channels today when he visited the #drizzle channel on irc.freenode.net.

ebergen: I'm still in #mysql-dev and #planet.mysql but they are hardly active these days [1:51pm]
rbradfor: ebergen: funny, #drizzle is where the action is. [1:51pm]

There is active movement on the Drizzle project. Why is this? Well, I think most importantly is that there is active contribution from the community, at least 5 different companies and more individuals are pushing code to Drizzle, and it’s being accepted and incorporated. Something you can not say about the MySQL Community branch.

As I write this, there are 35 active people on the #drizzle channel now, and 137 members of the …

I use Maatkit for a lot of grunt work and thought you might appreciate this quick tip. Suppose you have a bazillion tables to convert from MyISAM to InnoDB, but they are mixed in with other tables that are already InnoDB, or are another storage engine that you don't want to touch.

PLAIN TEXT CODE:

1. mk-find <db_name> --engine MyISAM --exec "ALTER TABLE %D.%N ENGINE=INNODB" --print

Here's a bonus tip, while I'm at it. I had a client a while back whose application creates tables as needed, so they had about 90,000 tables in a bunch of different databases, all named things like user_123_456_friends. I wanted to add an index to them -- but not to the ones named friends_123_456_user.

PLAIN TEXT CODE:

1. mk-find <db_name> --tblregex '^user_\d+_\d+_friends$' --exec 'ALTER TABLE %D.%N ADD …

In the current API when sending one takes the Transporter mutex and
then sends all the signals generated towards one or many nodes.
There is also some handling of adaptive sends, however this adaptive
algorithm takes care of all nodes, thus waiting for sending is global
on all nodes.

The new design uses one mutex for the sending, however this mutex only
controls the sending part of one socket. Also the time for holding the
mutex is just enough to check the state, no send operations are done
while holding the mutex.

The new adaptive algorithm will keep track of the last sent messages on
this socket and in principle the idea is that if it's at least a 90-99%
probability that it is a good idea to wait, then it will wait (unless
the application has provided the force send flag). It will do so by
keeping track of the last few messages sent. …