Introduction

Nowadays most companies use geographic distributed platforms to better serve their customers. It is quite common to see companies with datacenters in North America, Europe and Asia; each site hosting databases and shared data. In some cases the data is simply spread around for better accessibility; in other cases it is localized and different on each geographic location; in most cases a mix of the two.

Useless to say that most of the solutions were implemented using MySQL, and asynchronous replication. MySQL Asynchronous replication had being the most flexible solution, but at the same time also the most unreliable given the poor performance, lack of certification and possible data drift.

In this scenario the use of alternative solutions, like MySQL Synchronous (galera) replication had being a serious challenge. This because the nodes interactions was so intense and dense, that poor network performance …

Introduction

Nowadays most companies use geographic distributed platforms to better serve their customers. It is quite common to see companies with datacenters in North America, Europe and Asia; each site hosting databases and shared data. In some cases the data is simply spread around for better accessibility; in other cases it is localized and different on each geographic location; in most cases a mix of the two.

Useless to say that most of the solutions were implemented using MySQL, and asynchronous replication. MySQL Asynchronous replication had being the most flexible solution, but at the same time also the most unreliable given the poor performance, lack of certification and possible data drift.

In this scenario the use of alternative solutions, like MySQL Synchronous (galera) replication had being a serious challenge. This because the nodes interactions was so intense and dense, that poor network performance …

MySQL Replication is very simple to set up. In this post I’ll discuss its importance and five handy tools for monitoring and managing MySQL replication.

What is MySQL Replication? It’s the process of copying the (real-time events) data from one master instance to another slave instance and maintaining the redundant consistent data in a different machine. This enables a distributed database system that shares the same level of information.

In MySQL the replication works based on the three threads as shown below.

1) I/O thread on the slave server:  To start on receiving replication events, an I/O thread starts on the slave server and connects to the master server.

2) Master connection handler thread:  As a connection handier, master starts a thread whenever a replication slave connects to a master. The master server sends the events from its binary log file to the slave I/O thread, notifying slave …

As discussed in my presentation to NYLUG, I wanted to provide system administrations with some really quick analysis and performance fixes if you had limited knowledge of MySQL.

One of the most important things with MySQL is to tune memory properly. This can be complex as there are global buffers, and per session buffers, memory tables, and differences between storage engines. Even this first tip has conditions.

Configuration of MySQL can be found in the my.cnf file (How can I find that). Some variables are dynamic and some are not, and these can change between versions. Check out The most important MySQL Reference Manual page that everybody should bookmark …

What is JSON
JSON is an text based, human readable format for transmitting data between systems, for serializing objects and for storing document store data for documents that have different attributes/schema for each document. Popular document store databases use JSON (and the related BSON) for storing and transmitting data.

Problems with JSON in MySQL
It is difficult to inter-operate between MySQL and MongoDB (or other document databases) because JSON has traditionally been very difficult to work with. Up until recently, JSON is just a TEXT document. I said up until recently, so what has changed? The biggest thing is that there are new JSON UDF by Sveta Smirnova, which are part of the MySQL 5.7 Labs releases. Currently the JSON UDF are up to version 0.0.4. While these new UDF are a welcome edition to the MySQL database, they don't solve the really tough …

Sometimes replication halts with an error like:

Slave I/O: Got fatal error 1236 from master when reading data from binary log
Error reading packet from server: log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master;

If it's the SQL thread instead of the I/O thread, it might complain about 'Event too big'. The error could also be the other direction, complaining of 'Event too small'.

I rarely see this error actually have anything to do with max_allowed_packet. You can check the largest event in the master's binary log file. Even though the binary log records the length in each entry, mysqlbinlog doesn't expose it, so we have to do the math ourselves:

mysqlbinlog mysql-bin.00XXX | gawk "/^# at / { diff = $3 - prev; prev = $3; } (diff > max) { max = diff } END {print max}" -

If the result is larger than max_allowed_packet, then the problem and solution are …

This time it is nothing like a linkt to another Blog:  Galera on Mesos

Ok as a fact I was kinda involved. Even the work is done by Stefan all alone. We meat for a day in a coworking space and discussed about Galera and Mesos.
In the end Stefan produced this incredible blogpost and pushed Mesos forward.
Whats the fun about this post?
We already now Galera is already the standard in a lot of architectures. For example OpenStack. Doing consultant work for Docker also I encourage to use Galera for all this infrastructures Docker runs on. 
Mesos is about to run easy on 1000 nodes. It has a nice abstraction of nodes and framework. Companies like Airbnb, Paypal, eBay, Groupon use Mesos. Having a Galera poc for Mesos is going to make it likely to …

New version of JSON UDF functions has been just released. This version introduces two new features.

1. Escape characters in results of JSON_SEARCH function. In previous version if you had document @doc = '{"foo:bar": "baz"}' or '{"foo": {"bar": "baz"}}' return value of JSON_SEARCH(@doc, '"baz"'); was 'foo:bar::' for both. There was no way to distinguish two search paths. Now for the first document JSON_SEARCH returns 'foo\:bar::'
2. Second feature is much bigger. Now JSON functions accessing elements by a key (JSON_CONTAINS_KEY, JSON_EXTRACT, JSON_REPLACE, JSON_REMOVE, JSON_APPEND, JSON_SET, JSON_COUNT) can use alternate keypath syntax: $.key1.key2[arr_index] where $ is root element, .key is key name, [arr_index] is array index. JSON_SEARCH can also return path in this format with escaped $, . and [] symbols.

Improving the performance of a web system involves knowledge of how the entire technology stack operates and interacts. There are many simple and common tips that can provide immediate improvements for a website. Some examples include:

• Using a CDN for assets
• Compressing content
• Making fewer requests (web, cache, database)
• Asynchronous management
• Optimizing your SQL statements
• Have more memory
• Using SSD’s for database servers
• Updating your software versions
• Adding more servers
• Configuring your software correctly
• … And the general checklist goes on

Understanding where to invest your energy first, knowing what the return on investment can be, and most importantly the measurement and verification of every change made is the difference between blind trial and error and a solid plan and process. …

Have you ever had a case where you needed to find a process which sent a HUP/KILL/TERM or other signal to your database? Let me rephrase. Did you ever have to find which process messed up your night? If so, you might want to read on. I’m going to tell you how you can find it.

Granted, on small and/or meticulously managed systems tracking down the culprit is probably not a big deal. You can likely identify your process simply by checking what processes have enough privileges to send mysqld a HUP/KILL/TERM signal. However, frequently we see cases where this may not work or the elimination process would be too tedious to execute.

We recently had a case where a process was frequently sending SIGHUPs to mysqld and the customer asked us to see if we could get rid of his annoyance. This blog is the direct result of a discussion I had with my colleague …