A funny replication breakage kept me at the office longer than expected today (Friday 13 is not kind with me).

So question of the day: can you guess what the below UPDATE statement does (or what is wrong with it)?

> CREATE TABLE test_jfg ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, status ENUM('a','b') NOT NULL DEFAULT 'a', txt TEXT); Query OK, 0

In this blog, I’ll look at the impact of swapping on MySQL performance. 

It’s common sense that when you’re running MySQL (or really any other DBMS) you don’t want to see any I/O in your swap space. Scaling the cache size (using

innodb_buffer_pool_size

 in MySQL’s case) is standard practice to make sure there is enough free memory so swapping isn’t needed.   

But what if you make some mistake or miscalculation, and swapping happens? How much does it really impact performance? This is exactly what I set out to investigate.

My test system has the following:

• 32GB of physical memory
• OS (and swap space) on a (pretty old) Intel 520 SSD device
• Database stored on Intel 750 NVMe storage

To simulate a worst case scenario, I’m using Uniform Sysbench Workload:

sysbench …

From time to time we see in customer engagements that MySQL Master/Slave replication is set-up doing schema or table level replication filtering. This can be done either on Master or on Slave. If filtering is done on the Master (by the binlog_{do|ignore}_db settings), the binary log becomes incomplete and cannot be used for a proper Point-in-Time-Recovery. Therefore FromDual recommends AGAINST this approach.

The replication filtering rules vary depending on the binary log format (ROW and STATEMENT) See also: How Servers Evaluate Replication Filtering Rules.

For reasons of data consistency between Master and Slave FromDual recommends to use only the binary log format ROW. This is also stated in the …

The MySQL database workload is determined by the number of queries that it processes. There are several situations in which MySQL slowness can originate. The first possibility is if there is any queries that are not using proper indexing. When a query cannot make use of an index, the MySQL server has to use more resources and time to process that query. By monitoring queries, you have the ability to pinpoint SQL code that is the root cause of a slowdown.

Related resources  Become a MySQL DBA blog series - Using Explain to improve SQL Queries  Become a MySQL DBA blog series - Optimizer Hints for faster query execution …

The MySQL database workload is determined by the number of queries that it processes. There are several situations in which MySQL slowness can originate. The first possibility is if there is any queries that are not using proper indexing. When a query cannot make use of an index, the MySQL server has to use more resources and time to process that query. By monitoring queries, you have the ability to pinpoint SQL code that is the root cause of a slowdown.

Related resources  Become a MySQL DBA blog series - Using Explain to improve SQL Queries  Become a MySQL DBA blog series - Optimizer Hints for faster query execution …

Thanks Mauritz, you are right. I’ll update the post but still the explain plan is confusing!

LikeLike

This post will be updated as soon more information comes along.

This developer version wasn’t released yet, when it does, use at your own risk.

Oracle released the development version of MySQL 8.0.0-dmr on September 12th of 2016. Since then, the team have been working on the 8.0.1 development milestone. You can find the partial change list here.

The objective here is try to explain how this will have any real world impact for you from 8.0.1. Please remember though, that any changes made to this version will not be final until the General Availability (date not currently set).

These topics are aimed at the Software Engineering side and not DBA and this is why Replication, for instance, is not covered …

MySQL Cluster does an automatic sharding/partitioning to the tables across data nodes, enabling databases to scale horizontally to serve read and write-intensive workloads, but what is the default sharding key used in partitioning the data?
According to the recent update (Oct, 2016) of the MySQL Cluster white paper, primary key is the default sharding key:

By default, sharding is based on hashing of the primary key, which generally leads to a more even distribution of data and queries across the cluster than alternative approaches such as range partitioning.

However, that is not the case in all MySQL Cluster versions so far!
In this post, I’ll do some test cases on MySQL Cluster (of 4 datanodes) to confirm the default sharding key.

Testing on MySQL Cluster 7.2.26 …

Related resources  Sharding MySQL with MySQL Fabric and ProxySQL  MySQL Load Balancing with ProxySQL - an Overview  How to set up read-write split in Galera Cluster using ProxySQL  How ProxySQL adds Failover and Query Control to your MySQL Replication Setup  Two Database Sharding Tools for MySQL

Having too large a (write) workload on a master is dangerous. If the master collapses and a …

Related resources  Sharding MySQL with MySQL Fabric and ProxySQL  MySQL Load Balancing with ProxySQL - an Overview  How to set up read-write split in Galera Cluster using ProxySQL  How ProxySQL adds Failover and Query Control to your MySQL Replication Setup  Two Database Sharding Tools for MySQL

Having too large a (write) workload on a master is dangerous. If the master collapses and a …