Next Friday (31st of May) the second MySQL Meetup User Group NL of this year will be hosted by Snow IT in Geldermalsen. It is great to see that various companies are hosting the meetup and that the diversity and number of people attending is increasing. In total three presentations will be given: Choosing the […]

The post Presenting at the MySQL UG NL Meetup Q2 appeared first on Spil Games Engineering.

I had reason to look into the extended secondary index code in MariaDB and MySQL recently, and there was one bit that I really didn’t like.

MariaDB:

share->set_use_ext_keys_flag(legacy_db_type == DB_TYPE_INNODB);

MySQL:

use_extended_sk= (legacy_db_type == DB_TYPE_INNODB);

In case you were wondering what “legacy_db_type” actually does, let me tell you: it’s not legacy at all, it’s kind of key to how the whole “metadata” system in MySQL works. For example, to drop a table, this magic number is used to work out what storage engine to call to drop the table.

Now, these code snippets basically kiss goodbye to the idea of a “pluggable storage engine” architecture. If you’re not InnoDB, you don’t get to have certain features. This isn’t exactly MySQL or MariaDB encouraging an open storage engine ecosystem (quite the opposite really).

…

A while back one of my foreign keys started causing trouble. The problem was that some parent rows had tens of thousand of child rows, and the foreign key was defined with CASCADE DELETE enabled. When we deleted one of those parent rows on a master database, it took several seconds to execute the delete because of the cascade. This led to latency for the end user, and also led to replication delays.

The immediate solution was make the application tolerant of orphaned rows in the child table and to drop the explicit foreign key constraint.

I didn't really want to leave those orphaned rows hanging around in the child table, so I decided to implement an asynchronous process to delete the orphaned rows on a scheduled basis. Read on for a description of that process.

Using the sakila database as an example, imagine I drop …

A while back one of my foreign keys started causing trouble. The problem was that some parent rows had tens of thousand of child rows, and the foreign key was defined with CASCADE DELETE enabled. When we deleted one of those parent rows on a master database, it took several seconds to execute the delete because of the cascade. This led to latency for the end user, and also led to replication delays.

The immediate solution was make the application tolerant of orphaned rows in the child table and to drop the explicit foreign key constraint.

I didn't really want to leave those orphaned rows hanging around in the child table, so I decided to implement an asynchronous process to delete the orphaned rows on a scheduled basis. Read on for a description of that process.

Using the sakila database as an example, imagine I drop …

It is finally here.  After three years of development, the new version of Shard-Query is finally available for broad testing.

This new version of Shard-Query is vastly improved over previous versions in many ways.  This is in large part due to the fact that the previous version of Shard-Query (version 1.1) entered into production at a large company.  Their feedback during implementation was invaluable in building the new Shard-Query features.   The great thing is that this means that many of the new 2.0 features have already been tested in at least one production environment.

This post is intended to highlight the new features in Shard-Query 2.0.  I will be making posts about individual features as well as posting benchmark results.

…

The PHPTek Conference bills itself as the premier professional PHP conference with a community flair. Having been to dozens of PHP conferences, I thought that was a pretty bold claim.

MySQL Boogiebot

But as they say in Texas, it ain’t bragging if you can do it.

MySQL plush dolphins and BoogieBots. The wind up dancing robots were popular in Chicago

And the organizers picked perfect weather for this Chicago based show.

The quality of the presenters and presentations was amazing high. Most of these sessions covered intricacies with the PHP language. The talks covered the range from beginning Symfony 2 to advanced security. The presenters were indeed the cream of the crop and the material covered very well developed.

Oracle’s own Ligaya …

Global Transactions Identifiers are one of the new features regarding replication in MySQL 5.6. They open up a lot of opportunities to make the life of DBAs much easier when having to maintain servers under a specific replication topology. However you should keep in mind some limitations of the current implementation. This post is the first one of a series of articles focused on the implications of enabling GTIDs on a production setup.

The manual describes very nicely how to switch to GTID-based replication, I won’t repeat it.

Basically the steps are:

• Make the master read-only so that the slaves can execute all events and be in sync with the master
• Change configuration for all servers and …

Most serious users of MySQL have moved their tables to InnoDB years ago.  For those who haven't, let's discuss why InnoDB is a more scalable solution than MyISAM.

MyISAM was designed to be very fast for read queries.  It does not handle higher loads of writes very well.  It also suffers a more serious flaw: it isn't crash-safe.  In other words, you better have frequent backups.

MyISAM tables

Most serious users of MySQL have moved their tables to InnoDB years ago.  For those who haven’t, let’s discuss why InnoDB is a more scalable solution than MyISAM.

MyISAM was designed to be very fast for read queries.  It does not handle higher loads of writes very well.  It also suffers a more serious flaw: it isn’t crash-safe.  In other words, you better have frequent backups.

MyISAM tables have a read queue and a write queue.  Queries are placed into one of those two queues to be processed.  The write queue has higher priority than the read queue, but the table can only process one write query at a time.  Multiple read queries can occur at once, so the read queue will often be empty.

If a single query is added to the write queue, the read queue will …

I was sincerely affected by this last MySQL post and this other very long post from Jeremy Cole.
Yes, these two guys are MySQL rock stars and they are really impressives, their involvement in the MySQL community is utter!

I don’t want to write a long long speech about my simple life…
I just want to clarify why I do what I do.

Many people have asked or wondered without asking why I do what I do
(Jeremy Cole - 2013)

A few years ago Ashley Unitt asked me what I was most proud of, and now, I can make a complete answer. I’m very proud to take part of a community, MySQL has transformed my job into a passion and an incredible desire to share this passion, as honestly as possible.

What …