Jean-Jerome SchmidtInstalling Kubernetes Cluster with 3 minions on CentOS 7 to manage pods and services (20.5.2015, 07:18 UTC)

Kubernetes is a system for managing containerized applications in a clustered environment. It provides basic mechanisms for deployment, maintenance and scaling of applications on public, private or hybrid setups. It also comes with self-healing features where containers can be auto provisioned, restarted or even replicated. 

Kubernetes is still at an early stage, please expect design and API changes over the coming year. In this blog post, we’ll show you how to install a Kubernetes cluster with three minions on CentOS 7, with an example on how to manage pods and services. 


Kubernetes Components

Kubernetes works in server-client setup, where it has a master providing centralized control for a number of minions. We will be deploying a Kubernetes master with three minions, as illustrated in the diagram further below.

Kubernetes has several components:

  • etcd - A highly available key-value store for shared configuration and service discovery.
  • flannel - An etcd backed network fabric for containers.
  • kube-apiserver - Provides the API for Kubernetes orchestration.
  • kube-controller-manager - Enforces Kubernetes services.
  • kube-scheduler - Schedules containers on hosts.
  • kubelet - Processes a container manifest so the containers are launched according to how they are described.
  • kube-proxy - Provides network proxy services.


Deployment on CentOS 7

We will need 4 servers, running on CentOS 7.1 64 bit with minimal install. All components are available directly from the CentOS extras repository which is enabled by default. The following architecture diagram illustrates where the Kubernetes components should reside:


1. Disable iptables on each node to avoid conflicts with Docker iptables rules:

$ systemctl stop firewalld
$ systemctl disable firewalld

2. Install NTP and make sure it is enabled and running:

$ yum -y install ntp
$ systemctl start ntpd
$ systemctl enable ntpd

Setting up the Kubernetes Master

The following steps should be performed on the master.

1. Install etcd and Kubernetes through yum:

$ yum -y install etcd kubernetes

2. Configure etcd to listen to all IP addresses inside /etc/etcd/etcd.conf. Ensure the following lines are uncommented, and assign the following values:


3. Configure Kubernetes API server inside /etc/kubernetes/apiserver. Ensure the following lines are uncommented, and assign the following values:


4. Configure the Kubernetes controller manager inside /etc/kubernetes/controller-manager. Define the minion machines’ IP addresses:


5. Define flannel network configuration in etcd. This configuration will be pulled by flannel service on minions:

$ etcdctl mk / '{"Network":""}'

6. Start and enable etcd, kube-apiserver, kube-controller-manager and kube-scheduler:

$ for SERVICES in etcd kube-apiserver kube-controller-manager kube-scheduler; do 
    systemctl restart $SERVICES
    systemctl enable $SERVICES
    systemctl status $SERVICES 

Truncated by Planet PHP, read more at the original (another 11597 bytes)

Open QueryMore Cores or Higher Clock Speed? (20.5.2015, 01:12 UTC)

This is a little quiz (could be a discussion). I know what we tend to prefer (and why), but we’re interested in hearing additional and other opinions!

Given the way MySQL/MariaDB is architected, what would you prefer to see in a new server, more cores or higher clock speed? (presuming other factors such as CPU caches and memory access speed are identical).

For example, you might have a choice between

  • 2x 2.4GHz 6 core, or
  • 2x 3.0GHz 4 core

which option would you pick for a (dedicated) MySQL/MariaDB server, and why?

And, do you regard the “total speed” (N cores * GHz) as relevant in the decision process? If so, when and to what degree?

Jean-Jerome Schmidt5 Performance tips for running Galera Cluster for MySQL or MariaDB on AWS Cloud (18.5.2015, 13:50 UTC)

Amazon Web Services is one of the most popular cloud environments. Galera Cluster is one of the most popular MySQL clustering solutions. This is exactly why you’ll see many Galera clusters running on EC2 instances. In this blog post, we’ll go over five performance tips that you need to take under consideration while deploying and running Galera Cluster on EC2. If you want to run regular MySQL on EC2, you’ll find these tips still useful because, well, Galera is built on top of MySQL after all. Hopefully, these tips will help you save time, money, and achieve better Galera/MySQL performance within AWS.

Choosing a good instance size

When you take a look at the instance chart in the AWS documentation, you’ll see that there are so many instances to choose from. Obviously, you will pick an instance depending on your application needs (therefore you have to do some benchmarking first to understand those needs), but there are couple of things to consider.

CPU and memory - rather obvious. More = better. You want to have some headroom in terms of free CPU, to handle any unexpected spikes of traffic - we’d aim for ~50% of CPU utilization max, leaving the rest of it free.

We are talking about virtualized environment thus we should mention CPU steal utilization. Virtualization offers the ability to over-subscribe the CPU between multiple instances because not all instances need CPU at the same time. Sometimes an instance cannot get the CPU cycles it wants. It can be caused by over allocation on the host’s side when there are no additional CPU cycles to share (you can prevent it from happening by using dedicated instances - “Dedicated Tenancy” can be chosen when you create new instance inside of VPC, additional charges apply) or it can also happen when the load on the instance is too high and the hypervisor throttled it down to its limits.

Network and I/O capacity - by default, on non-EBS-optimized instances, network is shared for regular traffic and EBS traffic. It means that your reads and writes will have to compete for the same resource with the replication traffic. You need to measure your network utilization to make sure it is within your instance’s capacity. You can give some free resources to EBS traffic by enabling ‘EBS-optimized’ flag for instance, but again, network capacity differs between instance types - you have to pick something which will handle your traffic.

If you have a large cluster and you feel brave, you can use ephemeral SSD storage on instances as a data directory - it will reduce expenses on pIOPS EBS volumes. On the other hand, instance crash will end in data being wiped out. Galera can recover from such state using SST, but you would have to have a large cluster spanning multiple AWS regions to even consider this setup as an option. Even in such a case, you may consider using at least one EBS-based node per region, to be able to survive crashes and have data locally for SST.

If you choose EBS as your storage, you have to remember that EBS should be warmed up before putting it into production. EBS allocates only those blocks which are actually used. If you didn’t write on a given block, it will have to be allocated once you do this. Allocation process adds overhead (per Amazon it may be up to 50% of the performance) so it is a very good practice to perform the warmup. It can be done in several ways.
If the volume is new, then you can run:

$ sudo umount /dev/xvdx
$ sudo dd if=/dev/zero of=/dev/xvdx bs=1M

If the volume was created from the snapshot of the warmed up volume, you need just to read all of the blocks by:

$ sudo umount /dev/xvdf
$ sudo dd if=/dev/xvdf of=/dev/null bs=1M

On the other hand, if the original volume has not been warmed up, then the new volume needs a thorough warming by reading each block and writing it back to the volume (no data will get lost in the process):

$ sudo umount /dev/xvdf
$ sudo dd if=/dev/xvdf of=/dev/xvdf conv=notrunc bs=1M

Truncated by Planet PHP, read more at the original (another 8954 bytes)

Chris CalenderMySQL 5.6.24 Overview and Highlights (14.5.2015, 20:56 UTC)

MySQL 5.6.24 was recently released (it is the latest MySQL 5.6, is GA), and is available for download here.

For this release, there are 4 “Functionality Added or Changed” items:

  • Functionality Added/Changed: CMake support was updated to handle CMake version 3.1.
  • Functionality Added/Changed: The server now includes its version number when it writes the initial “starting” message to the error log, to make it easier to tell which server instance error log output applies to. This value is the same as that available from the version system variable. (Bug #74917)
  • Functionality Added/Changed: ALTER TABLE did not take advantage of fast alterations that might otherwise apply to the operation to be performed, if the table contained temporal columns found to be in pre-5.6.4 format (TIME, DATETIME, and TIMESTAMP columns without support for fractional seconds precision). Instead, it upgraded the table by rebuilding it. Two new system variables enable control over upgrading such columns and provide information about them:
    • avoid_temporal_upgrade controls whether ALTER TABLE implicitly upgrades temporal columns found to be in pre-5.6.4 format. This variable is disabled by default. Enabling it causes ALTER TABLE not to rebuild temporal columns and thereby be able to take advantage of possible fast alterations.
    • show_old_temporals controls whether SHOW CREATE TABLE output includes comments to flag temporal columns found to be in pre-5.6.4 format. Output for the COLUMN_TYPE column of the INFORMATION_SCHEMA.COLUMNS table is affected similarly. This variable is disabled by default.
  • Functionality Added/Changed: Statement digesting as done previously by the Performance Schema is now done at the SQL level regardless of whether the Performance Schema is compiled in and is available to other aspects of server operation that could benefit from it. The default space available for digesting is 1024 bytes, but can be changed at server startup using the max_digest_length system variable.

In addition to those, there were 50 other bug fixes:

  • 15 InnoDB
  •   4 Replication
  •   1 Partitioning
  • 30 Miscellaneous

The highlights for me are the Partitioning bug and 2 of the Replication bugs (of the 15 InnoDB bugs, 5 were related to full-text search, and 6 were related to Memcached plugin, and the other 4 were mostly obscure):

  • Partitioning: A number of ALTER TABLE statements that attempted to add partitions, columns, or indexes to a partitioned table while a write lock was in effect for this table were not handled correctly.
  • Replication: When gtid_mode=ON and slave_net_timeout was set to a low value, the slave I/O thread could appear to hang. This was due to the slave heartbeat not being sent regularly enough when the dump thread found many events that could be skipped. The fix ensures that the heartbeat is sent correctly in such a situation.
  • Replication: When replicating from a MySQL 5.7.6 or later server to a MySQL 5.6.23 or earlier server, if the older version applier thread encountered an Anonymous_gtid_log_event it caused an assert. The fix ensures that these new log events added in MySQL 5.7.6 and later do not cause this problem with MySQL 5.6.24 and later slaves.


So while there were no major changes, the partitioning fix covered a number of bugs, the replication fixes could potentially be important for you, and the numerous InnoDB full-text and Memcached fixes would be important if you’re using either of those. Thus if you rely on any of this, I’d consider upgrading.

The full 5.6.24 changelogs can be viewed here (which has more details about all of the bugs listed above):

Hope this helps. :)


Chris CalenderMySQL 5.5.43 Overview and Highlights (14.5.2015, 20:03 UTC)

MySQL 5.5.43 was recently released (it is the latest MySQL 5.5, is GA), and is available for download here:

This release, similar to the last 5.5 release, is mostly uneventful.

There were only 2 “Functionality Added or Changed” items this time, and 10 additional bugs fixed.

Out of the 10 bugs, there was 1 InnoDB bug, 1 replication bug, and 6 crashing bugs, all of which seemed rather minor or obscure. Here are the ones worth noting:

  • Functionality Changed: CMake support was updated to handle CMake version 3.1.
  • Functionality Added: The server now includes its version number when it writes the initial “starting” message to the error log, to make it easier to tell which server instance error log output applies to. This value is the same as that available from the version system variable. (Bug #74917)
  • Replication: When using a slave configured to use a special character set such as UTF-16, UTF-32, or UCS-2, the receiver (I/O) thread failed to connect. The fix ensures that in such a situation, if a slave’s character set is not supported then default to using the latin1 character set.
  • InnoDB: Certain InnoDB errors caused stored function and trigger condition handlers to be ignored.
  • Large values of the transaction_prealloc_size system variable could cause the server to allocate excessive amounts of memory. The maximum value has been adjusted down to 128K. A similar change was made for transaction_alloc_block_size. Transactions can still allocate more than 128K if necessary; this change reduces the amount that can be preallocated, as well as the maximum size of the incremental allocation blocks.
  • Crashing Bug: Ordering by a GROUP_CONCAT() result could cause a server exit.
  • Crashing Bug: A malformed mysql.proc table row could result in a server exit for DROP DATABASE of the database associated with the proc row.
  • Crashing Bug: A server exit could occur for queries that compared two rows using the <=> operator and the rows belonged to different character sets.
  • Crashing Bug: The optimizer could raise an assertion due to incorrectly associating an incorrect field with a temporary table.
  • Crashing Bug: The server could exit due to an optimizer failure to allocate enough memory for resolving outer references.
  • Crashing Bug: Creating a FEDERATED table with an AUTO_INCREMENT column using a LIKE clause results in a server exit.

I don’t think I’d call any of these critical, but if running 5.5, especially if not a very recent 5.5, you should consider upgrading.

For reference, the full 5.5.43 changelog can be viewed here:

Hope this helps.


Oli SennhauserControlling worldwide manufacturing plants with MySQL (14.5.2015, 19:43 UTC)

A MySQL customer of FromDual has different manufacturing plants spread across the globe. They are operated by local companies. FromDuals customer wants to maintain the manufacturing receipts centralized in a MySQL database in the Head Quarter in Europe. Each manufacturing plant should only see their specific data.


Manufacturing log information should be reported backup to European Head Quarter MySQL database.

The process was designed as follows:


Preparation of Proof of Concept (PoC)

To simulate all cases we need different schemas. Some which should be replicated, some which should NOT be replicated:


CREATE TABLE finance.accounting (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `data` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `data_rename` (`data`)


CREATE TABLE crm.customer (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `data` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `data_rename` (`data`)


-- Avoid specifying Storage Engine here!!!
CREATE TABLE erp.manufacturing_data (
, manufacture_plant VARCHAR(32)
, manufacture_info VARCHAR(255)
, KEY (manufacture_plant)

CREATE TABLE erp.manufacturing_log (
, manufacture_plant VARCHAR(32)
, log_data VARCHAR(255)
, KEY (manufacture_plant)

MySQL replication architecture

Before you start with such complicated MySQL set-ups it is recommended to make a little sketch of what you want to build:


Preparing the Production Master database (Prod M1)

To make use of all the new and cool features of MySQL we used the new GTID replication. First we set up a Master (Prod M1) and its fail-over System (Prod M2) in the customers Head Quarter:

# /etc/my.cnf


binlog_format            = row          # optional
log_bin                  = binary-log   # mandatory, also on Slave!
log_slave_updates        = on           # mandatory, also on Slave!
gtid_mode                = on           # mandatory, also on Slave!
enforce_gtid_consistency = on           # mandatory, also on Slave!
server-id                = 39           # mandatory, also on Slave!

This step requires a system restart (one minute downtime).

Preparing the Production Master standby database (Prod M2)

On Master (Prod M1):

GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.1.%' IDENTIFIED BY 'secret';

mysqldump -u root --set-gtid-purged=on --master-data=2 --all-databases --triggers --routines --events > /tmp/full_dump.sql

On Slave (Prod M2):

, MASTER_USER='replication', MASTER_PASSWORD='se

Truncated by Planet PHP, read more at the original (another 10571 bytes)

Chris CalenderWant to be a MySQL/MariaDB Support Engineer? (14.5.2015, 19:40 UTC)

MariaDB is looking to hire Support Engineers. If interested, email me your resume.

I look forward to hearing from you. :)


Chris CalenderMySQL 5.7.7 Overview and Highlights (14.5.2015, 19:38 UTC)

MySQL 5.7.7 was recently released (it is the latest MySQL 5.7, and is the first “RC” or “Release Candidate” release of 5.7), and is available for download here and here.

As for the fixes/changes, there are quite a few again, which is expected in an early RC release.

The main highlights for me were (though the enhancements, and potentially impactful changes, are definitely not limited to this list):

  • Optimizer Note: It is now possible to provide hints to the optimizer within individual SQL statements, which enables finer control over statement execution plans than can be achieved using the optimizer_switch system variable. Optimizer hints are specified as /*+ … */ comments following the SELECT, INSERT, REPLACE, UPDATE, or DELETE keyword of statements or query blocks. Hints are also permitted in statements used with EXPLAIN, enabling you to see how hints affect execution plans.
  • Security Note: The C client library now attempts to establish an SSL connection by default whenever the server is enabled to support SSL. This change affects these standard MySQL client programs: mysql, mysql_config_editor, mysql_install_db, mysql_plugin, mysql_secure_installation, mysql_upgrade, mysqladmin, mysqlbinlog, mysqlcheck, mysqldump, mysqlimport, mysqlshow, and mysqlslap. It will also affect new releases of MySQL Connectors that are based on the C client library: Connector/C, Connector/C++, and Connector/ODBC.
  • Spatial Data Support: The ST_Buffer(), ST_Difference(), ST_Distance(), ST_Intersection(), ST_IsSimple(), ST_SymDifference(), and ST_Union() functions have been reimplemented to use the functionality available in Boost.Geometry. The functions may raise an exception for invalid geometry argument values when the previous implementation may not have.
  • InnoDB: The innodb_file_format default value was changed to Barracuda. The previous default value was Antelope. This change allows tables to use Compressed or Dynamic row formats.
  • InnoDB: The innodb_large_prefix default value was changed to ON. The previous default was OFF. When innodb_file_format is set to Barracuda, innodb_large_prefix=ON allows index key prefixes longer than 767 bytes (up to 3072 bytes) for tables that use a Compressed or Dynamic row format.
  • InnoDB: The innodb_strict_mode default value was changed to ON. The previous default was OFF. When innodb_strict_mode is enabled, InnoDB raises error conditions in certain cases, rather than issuing a warning and processing the specified statement (perhaps with unintended behavior).

    The configuration parameter default changes described above may affect replication and mysqldump operations. Consider the following recommendations when using the new default settings:
    • When replicating or replaying mysqldump data from older MySQL versions to MySQL 5.7.7 or higher, consider setting innodb_strict_mode to OFF to avoid errors. Target settings should not be more strict than source settings.
    • When replicating from MySQL 5.7.7 or higher to older slaves, consider setting innodb_file_format=Barracuda and innodb_large_prefix=ON on the slave so that the target and source have the same settings.
  • InnoDB: To address a scalability bottleneck for some workloads where LOCK_grant is locked in read-mode, LOCK_grant locks are now partitioned. Read lock requests on LOCK_grant now acquire one of multiple LOCK_grant partitions. Write locks must acquire all partitions. To address another scalability bottleneck, the server no longer performs unnecessary lock acquisitions when creating internal temporary tables. (Bug #72829)
  • Replication: The XA implementation in MySQL has been made much more compatible with the XA specification.
  • Replication: The defaults of some replication related variables have been modified. The following changes have been made:
    • binlog_gtid_simple_recovery=TRUE
    • binlog-format=ROW
    • binlog_error_action=ABORT_SERVER
    • sync_binlog=1
    • slave_net_timeout=60

Again, there are numerous enhancements and many bug fixes, so please check out the full changelogs. If you’re running some 5.7 version, then you should definitely upgrade. (But this should not be used for production systems yet, of course.)

You can view the full 5.7.7 changelogs here:

Hope this helps.


Chris CalenderMySQL 5.7.6 Overview and Highlights (14.5.2015, 19:35 UTC)

MySQL 5.7.6 was recently released (it is the latest MySQL 5.7, and is the “m16″ or “Milestone 16″ release), and is available for download here and here.

As for the fixes/changes, there are quite a few (the official release was again split into 3 separate emails), which is expected in a “milestone” release.

The main highlights for me were (though the enhancements, and potentially impactful changes, are definitely not limited to this list):

  • Incompatible Change: The CREATE USER and ALTER USER statements have additional account-management capabilities. Together, they now can be used to fully establish or modify authentication, SSL, and resource-limit properties, as well as manage password expiration and account locking and unlocking. … A new statement, SHOW CREATE USER, shows the CREATE USER statement that creates the named user. The accompanying Com_show_create_user status variable indicates how many times the statement has been executed.
  • Configuration Note: mysqld now supports a –daemonize option that causes it to run as a traditional, forking daemon. This permits the server to work with operating systems that use systemd for process control.
  • Installation Note: The mysqld server and mysql_upgrade utility have been modified to make binary (in-place) upgrades from MySQL 5.6 easier without requiring the server to be started with special options. The server checks whether the system tables are from a MySQL version older than 5.7 (that is, whether the mysql.user table has a Password column). If so, it permits connections by users who have an empty authentication plugin in their mysql.user account row, as long as they have a Password value that is empty (no password) or a valid native (41-character) password hash.
  • Performance Schema Notes: The Performance Schema now allocates memory incrementally, scaling its memory use to actual server load, instead of allocating all the memory it needs during server startup. Consequently, configuration of the Performance Schema is easier; most sizing parameters need not be set at all. A server that handles a very low load will consume less memory without requiring explicit configuration to do so.
  • Incompatible Change: A new C API function, mysql_real_escape_string_quote(), has been implemented as a replacement for mysql_real_escape_string() because the latter function can fail to properly encode characters when the NO_BACKSLASH_ESCAPES SQL mode is enabled.
  • InnoDB: InnoDB system tablespace data is now exposed in the INNODB_SYS_TABLESPACES and INNODB_SYS_DATAFILES Information Schema tables.
  • InnoDB: Numerous (7) buffer pool flushing-related enhancements were added.
  • InnoDB: The default setting for the internal_tmp_disk_storage_engine option, which defines the storage engine the server uses for on-disk internal temporary tables, is now INNODB. With this change, the Optimizer uses the InnoDB storage engine instead of MyISAM for internal temporary tables.
  • InnoDB: InnoDB now supports native partitioning.
  • InnoDB: InnoDB now supports the creation of general tablespaces using CREATE TABLESPACE syntax. Tables are added to a general tablespace using CREATE TABLE tbl_name … TABLESPACE [=] tablespace_name or ALTER TABLE tbl_name TABLESPACE [=] tablespace_name syntax.
  • InnoDB: InnoDB now supports 32KB and 64KB page sizes. For both page sizes, the maximum record size is 16KB.
  • InnoDB: Replication-related support was added to InnoDB which enables prioritization of slave applier transactions over other transactions in deadlock scenarios. This transaction prioritization mechanism is reserved for future use.
  • InnoDB: The Performance Schema now instruments stage events for monitoring InnoDB ALTER TABLE and buffer pool load operations.
  • Replication: MySQL Multi-Source Replication adds the ability to replicate from multiple masters to a slave. MySQL Multi-Source Replication topologies can be used to back up multiple servers to a single server, to merge table shards, and consolidate data from multiple servers to a single server. See MySQL Multi-Source Replication. As part of MySQL Multi-Source Replication, replication channels have been added. Replication channels enable a slave to open multiple connections to replicate from, with each channel being a connection to a master. See Replication Channels.

Again, there are numerous enhancements and hundreds of bug fixes, so please check out the full changelogs. If you’re running some 5.7 version, then you should definitely upgrade. (But this should not be used for production systems yet, of course.)

You can view the full 5.7.6 changelogs here:

Hope this helps.


Jean-Jerome SchmidtLatest Severalnines Resources: New Galera Cluster Training, New ClusterControl Docker Image, Monitoring Galera and more! (13.5.2015, 12:51 UTC)

Check Out Our Latest Technical Resources for MySQL, MariaDB, Postgres and MongoDB.

This blog is packed with all the latest resources and tools we’ve recently published! Please do check it out and let us know if you have any comments or feedback.


Product Announcements & Resources

New 1-Day Instructor-led Online Training Course:

Automation & Management of Galera Clusters for MySQL, MariaDB & Percona XtraDB

  • When: The first training course will take place on June 12th 2015 - European time zone
  • Where: In a virtual classroom as well as a virtual lab for hands-on lab exercises
  • How: Reserve your seat online and we will contact you back with all the relevant details

You will learn about:

  • Galera Cluster, system architecture & multi-data centre setups
  • Automated deployment & node / cluster recovery
  • How to best migrate data into Galera Cluster
  • Monitoring & troubleshooting basics
  • Load balancing and cluster management techniques

Sign up now!


New ClusterControl Docker Image

For those of you interested in and working with Docker, you can now instantly manage and monitor an existing database infrastructure thanks to our new Docker image, which comes with ClusterControl installed and is configured with all of its relevant components.

Get the image


Technical Webinar - Replay

A Deep Dive Into How to Monitor Galera Cluster for MySQL & MariaDB

In this webinar, our colleague Krzysztof Książek, Senior Support Engineer, provided a deep-dive session on what to monitor in Galera Cluster for MySQL & MariaDB. Krzysztof is a MySQL DBA with experience in managing complex database environments for companies like Zendesk, Chegg, Pinterest and Flipboard. If you’re in Operations and your job is to monitor the health of MySQL/MariaDB Galera Cluster or Percona XtraDB Cluster, then this webinar replay is for you!

View the replay


Technical Blogs

Here is a listing of our most recent technical blogs. Do check them out and let us know if you have any questions.

As you will see, we had a bit of a focus on Galera in April ;-)

Truncated by Planet PHP, read more at the original (another 2668 bytes)

LinksRSS 0.92   RDF 1.
Atom Feed