The machine that ran this blog gave up the ghost. It was a “mini tower” sitting under a desk, and the boot disk failed. I don’t really need the machine any more, and I was too busy working to get mysql-js out the door to spend any time rebuilding it, so the site disappeared off the internet for a while.

This week we officially released MySQL Cluster 7.3, and I decided to revive my blog. Which brought up some decisions: keep it on the MySQL + Apache + PHP + Serendipity stack that it was running on? Keep hosting it from my house? Move it to a cloud provider? (Which one?) Just use Tumblr?

So, I migrated off of the old platform. I’m writing this post in Markdown and then using Octopress to build the web pages. For now, the blog is still being served from my own hardware — a low-powered Soekris machine with only flash for storage.

Moving from a dynamic …

I was testing out the latest MySQL 5.6 on Windows (having used the auto-installer) and happened to notice my RAM usage (via Windows Task Manager) was reporting quite a high value, when I had very modest ram/buffer settings (should have been around 40M, but instead it was around 400M).

After double/triple-checking my settings to make sure I didn’t overlook something obvious, I searched the bugs database, and ran across bug #68287:

“High Memory Usage with MySQL 5.6.12 GA in ‘Development Machine’ mode”

Turns out, using the auto-installer set the value of table_definition_cache=1400, when the minimum value is 400. Reducing it to 400, and restarting MySQL immediately lowered the RAM usage, and is the “work-around” identified in the bug report.

Having encountered this, …

Recently I started a project that required deleting millions of rows of data from various MySQL tables. Many of the queries which identifed the rows to delete required several joins. I decided to use common_schema's split() function to break the deletes into chunks, but when I started running the queries in common_schema some of them failed to delete any data. I assume the failures were related to the complexity of the multi-table delete statements.

Thiking of this as a two step problem a natural workaround emerged. The two steps are:

1. Identify the rows to delete
2. Delete the rows

I want to use split() for step 2, but I don't really need it for step 1.

I decided to create a …

FOREIGN KEYs in MySQL Cluster is a big step forward. It is now possible to run enterprise software with NDB Cluster as the storage backend. Over the years, the lack of FOREIGN KEYs have been one of the most limiting pieces of functionality. Who wants to fiddle with TRIGGERs or recode applications to enforce data integrity?
But finally, it is here. It is implemented natively at the Data Node level, where NDB stores its data. It is well known that FOREIGN KEYs come with an overhead. E.g., when writing a record into a child table, the existence must be checked in the parent table. Since data is distributed across multiple Data Nodes, the child record and parent record may be on different nodes or shards (Node Groups). Hence there is extra work to be done in terms of internal triggers and network communication, the latter being the more costly. The performance impact must be taken into account when doing …

Maybe you experienced some strange behaviour with MySQL: Everything is installed correctly and should work. But it does not.

Symptoms we have seen:

• MySQL starts/stops properly when started/stopped with service mysqld restart but MySQL does not start when a server is rebooted.
• Or after upgrading MySQL binaries mysqld will not start at all any more.
• Or after relocating MySQL datadir or changing default port MySQL does not start any more.

shell> service mysqld start
MySQL Daemon failed to start.
Starting mysqld:                                           [FAILED]

shell> grep mysqld /var/log/boot.log 
Starting mysqld:  [FAILED]

If you are lucky you get some error message like: ERROR! The server quit without updating PID file (/data/mysql/server.pid). or:

130620  9:49:14 …

MySQL team has recently attended several conferences and events in EMEA. At some cases we have managed only talks at some events we run a booth too, for the list of events we have been presented so far see below. For the upcoming shows we are going to be presented, please see our Wikis page.

• NoSQL Search Roadshow in Copenhagen on Jun 13, 2013:
  
  • At this show the MySQL talk on "NoSQL & MySQL" given by Ted Wenmark got more than 50% of the total 80-90 attendees. Ted got good response to the presentation, several attendees wanted to get in contact with him and wanted his slides (can be downloaded from Slideshare).
    One very interesting …

So we had MySQL User Camp on 19th June, 2013. Turn out of the people were less than what we expected based on the registration but even then it was a good gathering. Among the many familiar faces from the last MySQL User Camp in March 2013, many new faces could also be seen this time who missed MySQL User Camp last time.
 


Event started at its scheduled time sharp at 5:00 pm with a welcome speech by Sanjay Manwani, Director of MySQL India. He announced (tentative) date of next MySQL User Camp to be on 9th October, 2013.
…

This post is structured like a series of questions and answers in a conversation.  I recently had a number of conversations that all pretty much went this same way.  If you, like others, have many basic questions about how to proceed when faced with an append-only store for the first time, well then hopefully this post will help provide some answers for you.  The post focuses on column stores, the most common append-only store, but there are others.

Why do I want to use a column store? Column stores are optimal for OLAP analysis

Column stores offer substantial performance increases for OLAP  compared to row stores.  Row stores are optimized for OLTP workloads.  While a row store can be used for OLAP, it may not perform well because a row store has to retrieve every column for a row (unless there is a covering index).  This is one of the reason’s that I’ve said that covering index allows you …

Yesterday, Geir Høydalsvik and I had the pleasure of hosting a MySQL mini-seminar in Trondheim, Norway.

The topic of the day was a presentation by yours truly on how the MySQL optimizer works. Geir briefly explained how the MySQL teams are organized in Oracle, and that our focus is on delivering high quality on time.

We had lots of interesting questions and discussions (and pizza) afterwards. Of particular interest was:

• How the MySQL code base can be modularized to make it maintainable and testable. The takeaway was that MySQL has invested a lot on refactoring the last couple of years to improve in this area and will continue to do so in both the near and far future.
• How testing is done in MySQL. The answer was that the QA teams have been significantly ramped up since Sun acquired MySQL. In addition to much more resources to the QA teams, developers are now expected to write unit tests …

There are several approaches to count the occurrences of a substring inside a larger string in MySQL. Some people use replace() and length() (I would use char_length() instead) to do it. Others use stored functions. Recently I had to count the occurrences of a certain key across many rows of JSON. Instead of writing my own query using one of the approaches mentioned above I decided to use common_schema.

In the past I've discussed JSON parsing in MySQL using common_schema. In this case …