A couple of weeks before I joined Oracle, I was working as a freelance consultant. One of my customers decided to migrate from PostgreSQL to MySQL.

The backstory:

• My client initially decided to build their application with PostgreSQL, with the founding team already very experienced with MySQL. Their primary reason: schema changes on live MySQL deployments were difficult.

• MySQL 5.6 introduced online DDL, addressing a large number of their issues.

• As they experienced significant traction in their private beta, they decided to switch to MySQL 5.6, where they had more comfort in the size of the support & consulting ecosystem behind it.

I should point out in advance that I think …

[…] https://markswarbrick.wordpress.com/2013/09/05/monitoring-the-cluster-using-the-lcd-panel/ […]

This post is a continuation of my research of TokuDB’s  storage engine to understand if it is suitable for timeseries workloads.

While inserting LOAD DATA INFILE into an empty table shows great results for TokuDB, what’s more interesting is seeing some realistic workloads.

So this time let’s take a look at the INSERT benchmark.

What I am going to do is to insert data in 16 parallel threads into the table from the previous post:

CREATE TABLE `sensordata` (
  `ts` int(10) unsigned NOT NULL DEFAULT '0',
  `sensor_id` int(10) unsigned NOT NULL,
  `data1` double NOT NULL,
  `data2` double NOT NULL,
  `data3` double NOT NULL,
  `data4` double NOT NULL,
  `data5` double NOT …

We’ve been hard at work on TokuMX since it’s initial release just over 2 months ago. Today we released TokuMX v1.2 which includes Hot Backup in the Enterprise Edition.

Hot Backup allows users to create a backup of a running TokuMX primary or secondary server in a replica set, with no blocking of writes for clients. We will be blogging more about the Hot Backup technology in the coming weeks. This same technology is used for Hot Backup in TokuDB.

Also worth noting are the features we’ve added since the initial TokuMX release:

• Migration Tools. Migrate to TokuMX from MongoDB using our tool that replays MongoDB repication. This allows a TokuMX server to stay in sync with a MongoDB replica set, reducing downtime for production go-live.
• Bulk Loading. Significantly improves data load speeds …

Measuring how well a MySQL is doing can be tough – most metrics are simply proxies for actual performance. Thankfully, the Percona build of MySQL allows you to see exactly how long your queries are taking. It provides a diagnostic view that shows how long queries take, allowing you to gauge its real-world performance. I’ll get to that in a little bit. Before we do that, I want to talk about some other tools you can use.

You can dump the status of the system by running “show global status;”. This will give you how many connections have aborted, rows written, and how long the server has been up. This is historical info that’s erased when you flush it or when the server restarts (planned or unplanned). By sampling at regular intervals, you can also build monitoring scripts that measure changes over time …

MySQL 5.6 has a great many new features, including, but certainly not limited to a number of performance improvements. However, besides the widely talked-about features such as InnoDB support for full text search, optimizer, performance schema improvements and …

I’ve been considering using TokuDB for a large dataset, primarily because of its high compression. The data is append-only, never updated, rarely read, and purged after a configurable time.

I use partitions to drop old data a day at a time. It’s much more efficient than deleting rows, and it lets me avoid indexing the data on the time dimension. Partitioning serves as a crude form of indexing, as well as helping purge old data.

I wondered if TokuDB supports partitioning. Then I remembered some older posts from the Tokutek blog about partitioning. The claim is that “there are almost always better (higher performing, more robust, lower maintenance) alternatives to partitioning.”

I’m not sure this is true for my use case, for a couple of reasons.

First, I clearly fall into the only category that the flowchart …

So it’s all well and good having a nice MySQL cluster running on some tiny hardware, but what can you do with it?

Well in order to demo the software at some upcoming techtours my colleague put together a little Java application to showcase what can be done.

I want to run my raspberry pi’s headless so in order to get this to work I needed to install a vncserver

sudo apt-get install tightvncserver

Once this is installed make a note of the display you set up and check you can connect to it via a vnc client.

Install the java application

configure the config file for the MySQL credentials

Run the java application in a shell window on the remote VNC session making sure the screen variable has been set accordingly

export DISPLAY=1:4

When you have it all working it should look like the following

…

We recently looked at InnoDB, Real-World Experiences and Performance Tuning sessions at MySQL Connect. Let's now focus on the MySQL tutorials. You indeed have this year the opportunity to add a third day of tutorials to your MySQL Connect registration. They will be held on Monday September 23 at Moscone South. Whether you are getting started with MySQL or an experienced user, you will get the chance to acquire in-depth knowledge, directly from the source.

Tutorials and …

I thought it would be nice to have a display on pi1 (the primary node) so found an LCD kit that hopefully I could write a little script to hack around to display the status of each data node.

So I purchased the Adafruit Char 16×2 LCD Plate from:

https://www.modmypi.com/raspberry-pi-expansion-boards/raspberry-pi-blue-and-white-16×2-LCD-kit

Then set about soldering it all up

tada!

I followed the guide here:

…