Giuseppe Maxia of Continuent and long time creator of MySQL Sandbox.

Only works on Unix-like servers. Works with MySQL, Percona & MariaDB servers. MySQL server has the data directory, the port and the socket – you can’t share these.

To use it: make_sandbox foo.tar.gz. Then just do ./use.

$SANDBOX_HOME is ~/sandboxes. You can also create ~/opt/mysql/ and if you have MySQL 5.0.91 binary in that directory, you can just do “sb 5.1.91″.

Sandbox has features to start replication systems as well. You can have varying master/slave setups with varying versions as well (good idea to test from MySQL -> MariaDB master->slave for migration).

You can now also play with tungsten-sandbox, which is a great way to start playing with …

Steve Hardy of Zarafa.

Work that has been done to make Zarafa better. Why do you optimise your buffer pool? To decrease your I/O load. How can you do it? Buy more RAM, page compression, less (smaller) data, rearrange data.

MariaDB or Percona Server allows you to inspect your buffer pool (unsure if this is now available in MySQL 5.6). Giuseppe in the audience says this is available in MySQL 5.6, but Steve used this on MariaDB 5.2.

Strategies to fix it: Make records smaller. Remove indexes if you can use others almost as efficiently. Make records that are accessed around the same time have a higher chance of being on the same page. Use page compression. Buy more RAM. Try Batched Key Access (BKA) in MariaDB 5.3+.

Best to view the presentation since …

Stéphane Combaudon of Dailymotion.

Index: separate data structure to speed up SELECTs. Think of index in a book. In MySQL, key=index. Consider that indexes are trees.

InnoDB’s clustered index – data is stored with the Primary Key (PK) so PK lookups are fast. Secondary keys hold the PK values. Designing InnoDB PK’s with care is critical for performance.

An index can filter and/or sort values. An index can contain all the fields needed for the query you don’t need to go to the table (a covering index).

MySQL only uses 1 index per table per query (not 100% true – OR clauses), so think of a composite index when you can. Can’t index TEXT fields (use a prefix). Same for BLOBs and long VARCHARs.

Indexes: speed up queries, increases the size of your dataset, slows down writes. How big is the write slowdown? Simple test by Stephane, for in-memory workloads he says adding 2 keys makes performance 2x …

Oli Sennhauser of FromDual.

Synchronous multi-master replication with the Galera plugin. Your application connects to the load balancer and it redirects read/write traffic to the various MySQL Galera nodes. Tested a setup with 17 SQL nodes and you can have even more. Scaling reads and also a little bit for scaling writes is what Galera is good for.

If one node fails, the other two nodes still communicates with each other and the load balancer is aware of the failed node.

Why Galera? There is master-slave replication but its not multi-master, and its asynchronous and you can get inconsistencies. There is master-master replication but its asynchronous and can have inconsistencies and conflicts if you write on both nodes. MHA/MMM/Tungsten are not providing new technology but are based on the MySQL replication technology. …

Mark Riddoch of SkySQL.

This was a talk about the future in general. What people would like. Etc. Not about something that exists yet, hence the sparse notes.

Trace statements are good for the “why”. You move on to the debugger, but what is a useful SQL debugger? Profilers addresses the “when”.

SQL developer tools: manually run queries (traditional route for development, effective way to test SQL statements, some indication of performance), server logs (alerts developers of serious issues like the slow query log – identify poorly written queries, requires server access, not effective in a cloud environment (?)), external monitoring (network sniffing of connection packets, Ethereal dissectors – allow individual connections to be traced, no server access, privileged network access, complex to interpret, possibility of packet loss), intrusive tools (insert …

Ralf Gebhardt of SkySQL.

Cluster: shared nothing architecture (no single point of failure), synchronous replication between nodes, ACID transactions, row level locking. In-memory storage (some data can be stored on disk, but indexes must be in-memory). Checkpointing to disk for durability. It supports two types of indexes – ordered T-trees, unique hash indexes. Online operations like adding node groups, software upgrades, table alterations. Quick standard architecture diagram displayed about MySQL Cluster.

Network partitioning protocol is designed to avoid a split brain scenario. Is there at least one node from each node group? If not then this part cannot continue – graceful shutdown. Are all nodes present from any node group? If so, then this is the only viable cluster – continue to operate. Ask the arbitrator – the arbitrator which parts will continue if no arbitrator …

First talk at FOSDEM MySQL Devroom by Oli Sennhauser, of FromDual. Quick notes/liveblog of the talk, plus links from a quick search.

New Release Model: starts with at least in beta quality, milestone releases are RC quality (every 3-6 months), between milestones new features are allowed, GA releases every 12-18 months, no more than 2 releases in active support. There is also MySQL Labs, in where features can make it into a release model, but not necessarily.

Oli’s guess: MySQL 5.6 GA in April 2012 (Collaborate) or June 2012. As a consequence, MySQL 5.0 and MySQL 5.1 will be EOL probably by April this year.

New improvements in partitioning: explicit partition selection, exchanging partitions (good for ETL jobs).

New improvements in InnoDB: InnoDB INFORMATION_SCHEMA has got some new entries. Buffer …

I’m about to head to Paris to present at the February meetup of the MySQL User Group in Paris, France. It happens 1st February from 6-8pm at the Patricks Irish Pub. Its free to attend, and I understand that SkySQL keeps this event afloat.

I’m also heading to my first FOSDEM right afterwards and will definitely hang out at the MySQL & Friends Devroom. There is an amazing lineup of speakers, with all talks being about 25-30 minutes, it looks like it is going to be a lot of fun. To boot, Michael “Monty” Widenius will also be there, so expect lots of Salmiakkikossu.

If you want …

As many of you know, the schedule had been set for FOSDEM.

Giuseppe had posted the full scehdule here:

http://datacharmer.blogspot.com/2012/01/mysql-and-friends-schedule-at-fosdem.html

However, I refreshed the FOSDEM list (provided at fosdem.org) and noticed some changes to the schedule late last week.

http://fosdem.org/2012/schedule/track/mysql_and_friends_devroom

Specifically, I noticed new talks by Oli Sennhauser ("New Features in MySQL 5.6"), Alexey Yurchenko ("Galera 2.0"), Steve Hardy ("Optimizing your innodb buffer pool usage"), Liz van Dijk ("Virtualized Databases"), and Stéphane Combaudon ("Practical indexing guidelines").

…

The MySQL DevRoom at FOSDEM is ready. The schedule has been voted. Thanks to all who have participated. Now, let's make sure that the event is successful. The schedule is juicy, and not only because I have three talks in it!