If you are using MySQL with the InnoDB Storage Engine, you know and probably use FOREIGN KEYs. There are some who dislikes the use of these, saying the are bad for performance, but I tend to disagree, if the price of keeping my data consistent is a slight performance degradation, then so be it.

Anyway, I was going to write about a couple of lesser known FOREIGN KEY constraint attributes here. We all know what a FOREIGN KEY constraint is, right? A reference from a column or set of columns in one table to the PRIMARY KEY in another table? Yes, this is correct, but it's not the whole truth, actually, a FOREIGN KEY is a reference to a PRIMARY KEY or UNIQUE key in another table. Being able to reference a UNIQUE constraint is sometimes rather useful.

But before we go into that, lets look at a few subtle differences between a PRIMARY KEY and a UNIQUE constraint. In RDBMS design lingo, the UNIQUE constraing usually represents a …

Features:
* The ‘query sniffer’ can now connect to and retrieve data from a running instance of the MySQL-proxy program used by one or more clients to connect to MySQL.  A LUA script for controlling the proxy for use with MONyog is provided with this build.  With this sniffer option the SHOW FULL PROCESSLIST statement will not be sent and thus use of ‘query sniffer’ will not put any load on MySQL.
* SSH connections will now attempt more authentication methods in case the first method attempted failed.  This will solve problems reported with tunneling to MySQL on FreeBSD that as per default does not support the full range of SSH authentication methods (and possible similar problems).

Downloads: http://webyog.com/en/downloads.php
Purchase: …

I guess it is first reaction on new storage engine - show me benefits. So there is benchmark I made on one our servers. It is Dell 2950 with 8CPU cores and RAID10 on 6 disks with BBU, and 32GB RAM on board with CentOS 5.2 as OS. This is quite typical server we recommend to run MySQL on. What is important I used Noop IO scheduler, instead of default CFQ. Disclaimer: Please note you may not get similar benefits on less powerful servers, as most important fixes in XtraDB are related to multi-core and multi-disks utilization. Also results may be different if load is CPU bound.

I compared MySQL 5.1.30 trees - MySQL 5.1.30 with standard InnoDB, MySQL 5.1.30 with InnoDB-plugin-1.0.2 and MySQL 5.1.30 with XtraDB (all plugins statically compiled in MySQL)

For benchmarks I used scripts that emulate TPCC load and datasize 40W (about 4GB in size), 20 client connections. Please note I used …

The tutorial will cover the basics, several practical scenarios, tips and tricks, and some sneak peeks of the latest enhancements being developed.

MySQL provides a number of different tools for tuning SQL statements. Some of the key SQL tuning tools include:EXPLAIN - Displays execution plans generated by the MySQL Cost Based Optimizer.Status Variables - Contains statistics on SQL run time activity.Profiling - Contains run time statistics on each phase in the execution of individual SQL statements.Main Phases in Processing a SQL

one piece of jay’s advice to mysql got me thinking about something that bugs me about drizzle development. jay said: Make all decisions open and transparent: For the non-maintenance team, make a policy that all decisions about the kernel design be done in an open forum, with the community able to participate in the discussion. Have stewards that are willing to negotiate the design decisions with the community and do everything in a transparent manner.

since jay is one of the key lieutenants in the drizzle effort, it only seems to fair to put them up against that standard. one thing i have noticed is that there is relatively little discussion on the drizzle mailing list about all the coding that is going on. …

Since I didn’t have a blog server available in the last few months, Matt offered me to use his BigDBAHead and I accepted.  I must admit, I am not a big blogger but with WaffleGrid, there is a need to communicate more.  Since I have done most of the InnoDB hacking, here is a bit of status and roadmap information.

1. Async memcached_set

Believe it or not… all our results up to now have been done with sync IO…  We were discussing the need of doing async when I discovered memcached_behavior_set and  MEMCACHED_BEHAVIOR_NO_BLOCK. Sometimes, you just feel suddenly tired.  Preliminary results are very interesting, basically, the overhead of the set vanished. We will need to verify no coherence problem are introduced and redo our tests.

2.  Cache coherence between startup

Following a suggestion from Mark Atwood, the memcached key as been …

Yves and I had the rare opportunity to share a gig this week, which means we have been spending a lot of time over meals discussing how to improve Waffle Grid.  Yves will be blogging about some of the things we talked about very soon, I will also be publishing my EC2 experiences with Waffle Grid very soon.  Stay Tuned!

The more databases you have in your cluster the greater the probability they’re going to fail.

It’s basically MTBF/N until you have one of these boxes crash. If you have 10k machines expect multiple failures per day.

If you’re performing realtime writes to these databases you can lose a DB in the middle of a write.

Now what? Do you lose transactions?

Most people using InnoDB/MyISAM have used write caching controllers to solve this problem.

You buy this expensive card with 128MB of memory which temporarily caches writes. The card has a battery so if you lose power you spin the disks until you can get your data onto the disks.

But as I mentioned before, these devices are expensive. Expect them to add another 20% on the price of your cluster.

This doesn’t sound like a ton of cash if you have one or two machines but if you’re buying 50-1000 it’s a significant …

Sun Microsystems today announced that Ladok, Sweden's largest university database will be migrated to MySQL™, the world’s most popular open source database. Five million people are registered in the Ladok system, a national system used for the documentation of academic information. University administrators as well as more than 350,000 active students use information from the Ladok-system on a regular basis in order to manage follow–up procedures, track academic results or look up grades.