Personal blog of Yzmir Ramirez

We want MongoDB at Percona Live!

One of the main goals of the Percona Live Data Performance Conference 2016 is celebrating and embracing the open source community. The community’s spirit of innovation, expertise and competition has produced incredible software, hardware, processes and products.

The open source community is a diverse and powerful collection of companies, organizations and individuals that have helped to literally change the world. Percona is proud to call itself a member of the open source community, and we strongly feel that upholding the principles of the community is a key to our success. These principals include an open dialog, an open mind, and a zeal for cooperative interaction. Together, we can create amazing things.

That’s why we were surprised when MongoDB declined to have us sponsor or speak at MongoDB World 2016, and even more taken aback when we were told our engineers are not welcome to attend the show. We make a special point of inviting competitors to participate in, speak at, and sponsor Percona Live – MongoDB included. We welcome our competitors to speak, sponsor and attend Percona Live because it is in the greater interest of the community at large to include all voices.

With that in mind, we’d like to extend a special offer to any MongoDB employees: sign up for the Percona Live Data Performance Conference 2016 using your company email, and receive a special VIP 25% discount off the registration price (use promo code “mongodb”).

In addition:

• We invite all MongoDB attendees to a VIP cocktail reception with the Percona Team Tuesday, April 19th from 5-6pm
• Percona is pleased to host all MongoDB attendees as special guests at the Tuesday, April 19th, Community Dinner Event at Pedro’s

It’s our way of showing our solidarity with the open source community, and expressing our belief that we work best when we work together.

See you all at Percona Live! Register here!

 Power BI is Microsoft’s tool for analyzing data and building interactive data-based dashboards and reports. At its Build developer conference in San Francisco, the company today announced the preview of Power BI Embedded. With this, developers will be able to integrate Power BI and its interactive dashboards right into their own apps. Read More

 Microsoft is holding its annual Build developer conference in San Francisco this week and after focusing on Windows, Xbox, HoloLens, and Cortana yesterday, the company will shine the spotlight on its cloud and developer tools today. You can watch the live stream right here. Read More

 Airware used to sell drone operating systems. Now it’s swiveling to sell the whole flying kit and caboodle: drone hardware, the software to control them, and the cloud where their data goes. It discovered that big enterprise companies didn’t know how to piece together drone systems themselves, so they were slow to adapting to the tech that could save them money and keep employees… Read More

In this blog post, we’ll discuss some of the basics regarding Docker MySQL replication. Docker has gained widespread popularity in recent years as a lightweight alternative to virtualization. It is ideal for building virtual development and testing environments. The solution is flexible and seamlessly integrates with popular CI tools.

 

This post walks through the setup of MySQL replication with Docker using Percona Server 5.6 images. To keep things simple we’ll configure a pair of instances and override only the most important variables for replication. You can add whatever other variables you want to override in the configuration files for each instance.

Note: the configuration described here is suitable for development or testing. We’ve also used the operating system repository packages; for the latest version use the official Docker images. The steps described can be used to setup more slaves if required, as long as each slave has a different server-id.

First, install Docker and pull the Percona images (this will take some time and is only executed once):

# Docker install for Debian / Ubuntu
apt-get install docker.io
# Docker install for Red Hat / CentOS (requires EPEL repo)
yum install epel-release # If not installed already
yum install docker-io
# Pull docker repos
docker pull percona

Now create locally persisted directories for the:

1. Instance configuration
2. Data files

# Create local data directories
mkdir -p /opt/Docker/masterdb/data /opt/Docker/slavedb/data
# Create local my.cnf directories
mkdir -p /opt/Docker/masterdb/cnf /opt/Docker/slavedb/cnf
### Create configuration files for master and slave
vi /opt/Docker/masterdb/cnf/config-file.cnf
# Config Settings:
[mysqld]
server-id=1
binlog_format=ROW
log-bin
vi /opt/Docker/slavedb/cnf/config-file.cnf
# Config Settings:
[mysqld]
server-id=2

Great, now we’re ready start our instances and configure replication. Launch the master node, configure the replication user and get the initial replication co-ordinates:

# Launch master instance
docker run --name masterdb -v /opt/Docker/masterdb/cnf:/etc/mysql/conf.d -v /opt/Docker/masterdb/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=mysecretpass -d percona:5.6
00a0231fb689d27afad2753e4350192bebc19ab4ff733c07da9c20ca4169759e
# Create replication user
docker exec -ti masterdb 'mysql' -uroot -pmysecretpass -vvv -e"GRANT REPLICATION SLAVE ON *.* TO repl@'%' IDENTIFIED BY 'slavepass'G"
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
GRANT REPLICATION SLAVE ON *.* TO repl@"%"
--------------
Query OK, 0 rows affected (0.02 sec)
Bye
### Get master status
docker exec -ti masterdb 'mysql' -uroot -pmysecretpass -e"SHOW MASTER STATUSG"
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
             File: mysqld-bin.000004
         Position: 310
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:

If you look carefully at the “docker run” command for masterdb, you’ll notice we’ve defined two paths to share from local storage:

/opt/Docker/masterdb/data:/var/lib/mysql

• This maps the local “/opt/Docker/masterdb/data” to the masterdb’s container’s “/var/lib/mysql path”
• All files within the datadir “/var/lib/mysql” persist locally on the host running docker rather than in the container

/opt/Docker/masterdb/cnf:/etc/mysql/conf.d

• This maps the local “/opt/Docker/masterdb/cnf” directory to the container’s “/etc/mysql/conf.d” path
• The configuration files for the masterdb instance persist locally as well
• Remember these files augment or override the file in “/etc/mysql/my.cnf” within the container (i.e., defaults will be used for all other variables)

We’re done setting up the master, so let’s continue with the slave instance. For this instance the “docker run” command also includes the “–link masterdb:mysql” command, which links the slave instance to the master instance for replication.

After starting the instance, set the replication co-ordinates captured in the previous step:

docker run --name slavedb -d -v /opt/Docker/slavedb/cnf:/etc/mysql/conf.d -v /opt/Docker/slavedb/data:/var/lib/mysql --link masterdb:mysql -e MYSQL_ROOT_PASSWORD=mysecretpass -d percona:5.6
eb7141121300c104ccee0b2df018e33d4f7f10bf5d98445ed4a54e1316f41891
docker exec -ti slavedb 'mysql' -uroot -pmysecretpass -e'change master to master_host="mysql",master_user="repl",master_password="slavepass",master_log_file="mysqld-bin.000004",master_log_pos=310;"' -vvv
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
change master to master_host="mysql",master_user="repl",master_password="slavepass",master_log_file="mysqld-bin.000004",master_log_pos=310
--------------
Query OK, 0 rows affected, 2 warnings (0.23 sec)
Bye

Almost ready to go! The last step is to start replication and verify that replication running:

# Start replication
docker exec -ti slavedb 'mysql' -uroot -pmysecretpass -e"START SLAVE;" -vvv
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
START SLAVE
--------------
Query OK, 0 rows affected, 1 warning (0.00 sec)
Bye
# Verify replication is running OK
docker exec -ti slavedb 'mysql' -uroot -pmysecretpass -e"SHOW SLAVE STATUSG" -vvv
mysql: [Warning] Using a password on the command line interface can be insecure.
--------------
SHOW SLAVE STATUS
--------------
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysqld-bin.000004
          Read_Master_Log_Pos: 310
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 284
        Relay_Master_Log_File: mysqld-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 310
              Relay_Log_Space: 458
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 230d005a-f1a6-11e5-b546-0242ac110004
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)
Bye

Finally, we have a pair of dockerized Percona Server 5.6 master-slave servers replicating!

As mentioned before, this is suitable for a development or testing environment. Before going into production with this configuration, think carefully about the tuning of the “my.cnf” variables and the choice of disks used for the data/binlog directories. It is important to remember that newer versions of Docker recommend using “networks” rather than “linking” for communication between containers.

Welcome to the next Percona Live featured talk with Percona Live Data Performance Conference 2016 speakers! In this series of blogs, we’ll highlight some of the speakers that will be at this year’s conference, as well as discuss the technologies and outlooks of the speakers themselves. Make sure to read to the end to get a special Percona Live registration bonus!

In this Percona Live featured talk, we’ll meet Avi Kivity, CTO of ScyllaDB. His talk will be Scylla, a Cassandra-compatible NoSQL database at 2 million requests per second. Scylla is a new NoSQL database that applies systems programming techniques to a horizontally scalable NoSQL design to achieve extreme performance improvements. I had a chance to speak with Avi and learn a bit more about Scylla and its strengths:

Percona: Give me a brief history of yourself: how you got into database development, where you work, what you love about it.

Avi: Unlike perhaps many database developers, I approached databases up from the kernel and the filesystem layers. As the maintainer of the Linux Kernel-based Virtual Machine (KVM) project, I had extensive experience in kernel programming, especially scaling loads to many cores. Before that, at Exanet, I worked on a distributed filesystem (now Dell’s FluidFS), where I gained storage and distributed systems experience. Applying this low-level experience to a high-level application like a database has been very rewarding for me.

I work as ScyllaDB’s CTO in our Herzliyya, Israel headquarters, but our development team is scattered around twelve countries! Since ScyllaDB is a remote-work champion, I have the pleasure of working with the very best developers on the planet.

Percona: Your talk is going to be “Scylla, a Cassandra-compatible NoSQL database at 2 million requests per second.” What is it about Scylla that makes it an obvious choice for an adopter? Is there a specific workload or scenario that it handles well?

Avi: As Scylla is a drop-in replacement for Cassandra, existing Cassandra users are our obvious target. Cassandra compatibility means that the Cassandra file formats, drivers, query language, management tools, and even configuration files are all understood by Scylla. Your existing applications, data and Cassandra skills transfer with very little effort. However, on average you gain up to 10 times the throughput, with a sizable reduction in latency; at the higher percentiles, you gain even more! The throughput improvement can be translated to smaller clusters, higher application throughput, a bigger load safety margin, or a combination of all of these.

As a very high-throughput database, Scylla is a good fit for the Internet of Things (IoT) and web-scale data stores. Its low latency (no Garbage Collection pauses!) make it a good fit for ad-tech applications. Even non-Cassandra users with high-throughput or strict low-latency requirements should take a good look at Scylla.

Percona: Where are you in the great NoSQL vs. MySQL debate? Why would somebody choose NoSQL (and specifically, Scylla) over MySQL?

Avi: Both SQL and NoSQL have their places. SQL offers great flexibility in your query choices, and excellent ACIDity. NoSQL trades off some of that flexibility and transactional behavior, but in return it gives you incredible scalability, geographical distribution and availability – and with Scylla, amazing throughput.

A great advantage of the Scylla architecture (which, to be fair, we inherited from Cassandra) is its symmetric structure. All nodes have the same role: there are no masters and slaves, metadata nodes or management nodes. A symmetric architecture means linear scaling as you add nodes, without a specific node becoming a bottleneck. This is pretty hard to achieve in a MySQL deployment.

Percona: What do you see as an issue that we the open source database community needs to be on top of concerning NoSQL, Cassandra, or Scylla? What keeps you up at night?

Avi: The NoSQL movement placed great emphasis on scale-out, almost completely ignoring scale-up. Why bother with per-node performance if you can simply add more nodes? Operational costs and complexity, that’s why! With Scylla, we’re trying to bring the same kind of attention to per-node performance that traditional SQL databases have while still providing the NoSQL goodness.

When we investigated the performance bottlenecks in Cassandra, we saw that while non-blocking message-passing was used between nodes (as it should be), blocking locks were used for inter-core communications, and blocking I/O APIs were used for storage access. To fix this problem, we wrote Seastar (http://seastar-project.org), a server application framework that uses non-blocking message-passing for inter-core communications and storage access. Scylla builds on Seastar and uses it to achieve its performance goals.

Percona: What are you most looking forward to at Percona Live Data Performance Conference 2016?

Avi: This is my first Percona Live conference, so I’m excited!  I’m looking forward to engaging with Percona Live attendees, seeing how Scylla can help them and understanding which features we need to prioritize on the Scylla roadmap.

You can read more of Avi’s thoughts on NoSQL, SQL and Scylla at the Scylla blog, or follow him on Twitter.

Want to find out more about Avi and Scylla? Register for Percona Live Data Performance Conference 2016, and see his talk Scylla, a Cassandra-compatible NoSQL database at 2 million requests per second. Use the code “FeaturedTalk” and receive $100 off the current registration price!

The Percona Live Data Performance Conference is the premier open source event for the data performance ecosystem. It is the place to be for the open source community as well as businesses that thrive in the MySQL, NoSQL, cloud, big data and Internet of Things (IoT) marketplaces. Attendees include DBAs, sysadmins, developers, architects, CTOs, CEOs, and vendors from around the world.

The Percona Live Data Performance Conference will be April 18-21 at the Hyatt Regency Santa Clara & The Santa Clara Convention Center.

 Following from the unmitigated disaster that was Windows 8, Windows 10 has been doing rather well for Microsoft. After announcing that its flagship operating system ran on 200 million monthly active devices in January, the company today used its Build keynote to announce that this number is now up to 270 million monthly active devices. Read More

 Microsoft is holding its annual Build developer conference in San Francisco this week and the company is kicking off the event with its first of two major keynotes this morning. You can watch the live stream right here.
The keynote is scheduled to start at 8:30am Pacific, 11:30am Eastern, and 17:30 Central European Time. Read More

 Asana, an enterprise app that lets people set and track projects and other goals, has hit a goal of its own: today, the company is announcing that it has raised $50 million. The Series C round — led by Y-Combinator’s Sam Altman — values the company at $600 million, the company tells me. As a bit of context, Asana last raised $28 million in 2012; that Series B was at a… Read More

In this blog post, we’ll discuss read-write split routing in MaxScale.

The two previous posts have shown how to setup high availability (HA) with Maxscale using asynchronous replication and how we monitor replication.

Now let’s focus on the routing module performing read-write splits.

This is our current configuration:

[Splitter Service]
type=service
router=readwritesplit
servers=percona1, percona2
max_slave_replication_lag=30
user=maxscale
passwd=264D375EC77998F13F4D0EC739AABAD4

This router module is designed to spread the read queries across multiple servers (slaves by default), and send the write queries to a single server: the master.

This module is intended to work with Asynchronous Master-Slave replication but also with Galera replication if you plan to write to a single node.

So what is routed to the Master?

• Write statements
• All statements within an open transaction, even if this transaction is read only
• Store procedure and user-defined function call.
• DDL statements
• Execution of prepared statements (EXECUTE)
• All statements using temporary tables

Example:

• percona1: master
• percona2 and percona3: slaves

Let’s connect to MaxScale with the MySQL’s interactive client:

mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| percona2   |
+------------+
mysql> start transaction;
mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| percona1   |
+------------+
mysql> rollback;
mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| percona2   |
+------------+

Now let’s try with a READ ONLY transaction:

mysql> start transaction read only;
mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| percona1   |
+------------+

As we can see, MaxScale doesn’t support READ ONLY transactions. It considers them the same as any transaction. This means they are routed to the master as a WRITE.

We’ve already seen the 

max_slave_replication_lag

 optional parameter, but there are some others:

• max_slave_connections

  : defines the maximum number of slaves a router session uses, the default is to use all the ones available

• use_sql_variables_in

  : defines where queries’ reading session variables should be routed. Valid values are master and all (the latter being the default)

• weightby

  : defines the name of the value used to calculate the weights of the server

Now let’s play with the

weightby

 . So in this configuration, we will target 10% of the reads to percona2, and 90% to percona3:

[Splitter Service]
type=service
router=readwritesplit
servers=percona1, percona2, percona3
weightby=myweight
...
[percona2]
type=server
address=192.168.90.3
port=3306
protocol=MySQLBackend
myweight=1
[percona3]
type=server
address=192.168.90.4
port=3306
protocol=MySQLBackend
myweight=9

We restart MaxScale, and verify the settings of the service:

# maxadmin -pmariadb show service "Splitter Service"
Service 0x363b460
	Service:                             Splitter Service
	Router:                              readwritesplit (0x7fe7f1e88540)
	State:                                       Started
	Number of router sessions:           	0
	Current no. of router sessions:      	0
	Number of queries forwarded:          	0
	Number of queries forwarded to master:	0
	Number of queries forwarded to slave: 	0
	Number of queries forwarded to all:   	0
	Master/Slave percentage:		0.00%
	Connection distribution based on myweight server parameter.
		Server               Target %    Connections  Operations
		                               Global  Router
		percona3             90.0%     0       0       0
		percona2             10.0%     0       0       0
		percona1             100.0%     0       0       0
	Started:                             Wed Feb 24 22:39:27 2016
	Root user access:                    Disabled
	Backend databases
		192.168.90.4:3306  Protocol: MySQLBackend
		192.168.90.3:3306  Protocol: MySQLBackend
		192.168.90.2:3306  Protocol: MySQLBackend
	Routing weight parameter:            myweight
	Users data:                          0x36397c0
	Total connections:                   2
	Currently connected:                 2
	SSL:  Disabled

The target % seems correct, let’s test it!

for i in `seq 1 10`;
do mysql -h 192.168.90.5 -BN -umanager -ppercona -e "select @@hostname; select sleep(10)" 2>/dev/null &
done
percona2
percona2
percona2
percona2
percona2
percona2
percona3
percona3
percona3
percona3

That doesn’t seem good! Let’s check the service again:

Service 0x363b460
	Service:                             Splitter Service
	Router:                              readwritesplit (0x7fe7f1e88540)
	State:                                       Started
	Number of router sessions:           	10
	Current no. of router sessions:      	10
	Number of queries forwarded:          	30
	Number of queries forwarded to master:	0
	Number of queries forwarded to slave: 	30
	Number of queries forwarded to all:   	0
	Master/Slave percentage:		0.00%
	Connection distribution based on myweight server parameter.
		Server               Target %    Connections  Operations
		                               Global  Router
		percona3             90.0%     10      10      5
		percona2             10.0%     10      10      5
		percona1             100.0%     10      10      0
	Started:                             Wed Feb 24 22:39:27 2016
	Root user access:                    Disabled
	Backend databases
		192.168.90.4:3306  Protocol: MySQLBackend
		192.168.90.3:3306  Protocol: MySQLBackend
		192.168.90.2:3306  Protocol: MySQLBackend
	Routing weight parameter:            myweight
	Users data:                          0x36397c0
	Total connections:                   12
	Currently connected:                 12
	SSL:  Disabled

Five operations for both . . . this looks like a normal load balancer, 50%-50%.

So that doesn’t work as we expected. Let’s have a look at other router options:

• slave_selection_criteria

  . Controls how the router chooses the slaves and how it load balances the sessions. There are some parameter options:

  • LEAST_GLOBAL_CONNECTIONS. Slave with least connections from MaxScale, not on the server itself
  • LEAST_ROUTER_CONNECTIONS. Slave with least connections from this service
  • LEAST_BEHIND_MASTER. Slave with smallest replication lag
  • LEAST_CURRENT_OPRTATIONS. Slave with least active operations (this is the default)

• master_accept_reads

  . Uses the master for reads

The are some others; please check the online manual for:

• max_sescmd_history

• disable_sescmd_history

That explains the behavior we just observed. But what if we want to use the weight setting, and not spread the reads equivalently on the slaves?

I found the answer on IRC in the #maxscale freenode. Markus Makela (markusjm) explained to me that the default configuration in 1.3.0 is to use all the slaves, and load balance the actual statements. So to achieve what we want to do, we need to use these options in the service section:

router_options=slave_selection_criteria=LEAST_GLOBAL_CONNECTIONS
max_slave_connections=1

Let’s test it:

for i in `seq 1 10`;
do mysql -h 192.168.90.5 -BN -umanager -ppercona -e "select @@hostname; select sleep(10)" 2>/dev/null &
done
percona3
percona3
percona3
percona3
percona3
percona2
percona3
percona3
percona3
percona3
Service 0x1d88560
	Service:                             Splitter Service
	Router:                              readwritesplit (0x7f9c018c3540)
	State:                                       Started
	Number of router sessions:           	10
	Current no. of router sessions:      	10
	Number of queries forwarded:          	30
	Number of queries forwarded to master:	0
	Number of queries forwarded to slave: 	30
	Number of queries forwarded to all:   	0
	Master/Slave percentage:		0.00%
	Connection distribution based on myweight server parameter.
		Server               Target %    Connections  Operations
		                               Global  Router
		percona3             90.0%     9       9       9
		percona2             10.0%     1       1       1
		percona1             100.0%     10      10      0
	Started:                             Wed Feb 24 22:58:21 2016
	Root user access:                    Disabled
	Backend databases
		192.168.90.4:3306  Protocol: MySQLBackend
		192.168.90.3:3306  Protocol: MySQLBackend
		192.168.90.2:3306  Protocol: MySQLBackend
	Routing weight parameter:            myweight
	Users data:                          0x1d8a480
	Total connections:                   12
	Currently connected:                 12
	SSL:  Disabled

Yes! It worked as expected!

max_slave_connections

 sets the maximum number of slaves a router session uses at any moment. The default is to use all available slaves. When we set it to 1, we get one master and one slave connection per client, and the connections are balanced according to the server weights. The new mechanism uses statements instead of connections for load balancing (see MXS-588).

Finally, this routing module also support routing hints. I’ll cover them in my next MaxScale post.

More information: https://github.com/mariadb-corporation/MaxScale/blob/develop/Documentation/Routers/ReadWriteSplit.md