Introduction

In this article we will explore one approach for deploying ProxySQL behind a load balancer in Google Cloud.

While considering the deployment of ProxySQL, one has basically the following options:

1. Install ProxySQL on existing application server(s)
2. Provision dedicated ProxySQL server(s) between your application servers and the database layer.

Each approach has its pros and cons, but if there’s a significant number of application servers (more than a dozen or so) having a dedicated ProxySQL “layer” can be a more attractive option, specially if there is no service discovery mechanism in place (e.g. Consul).

Let’s consider a simple scenario, with a master and a small number of slaves in a single geographic region. Assuming that you are following the best practice, your database servers should be split into different availability zones. So for ProxySQL, it also …

Today on the show, I am joined by my colleagues, John Schulz and Valerie Parham-Thompson. Both John and Valerie just returned from the MariaDB Conference in New York City. As MariaDB is a native, relative and viable option to MySQL, I thought that it would be a good time to delve into the platform in detail and share what we have discovered with you here today. In this episode, we find out more about the MariaDB Conference – why it was founded, what the attendance was like and who the Conference is beneficial for. We then take a closer look at MariaDB and investigate the key differences between MariaDB and MySQL – highlighting MariaDB’s newest and most user-friendly features. We also learn more about other software platforms, such as Percona and Oracle, and how they compare to MariaDB. Our insightful guests continue to offer some helpful tips and resources for anyone getting started in MariaDB. So, if you are currently using MySQL or MariaDB, …

Introduction

In my travels to visit many customers over the last few years, I often see my customers creating many or all of their MySQL InnoDB tables using auto-increment primary keys. Many Object Relational Mappers do this by default on behalf of the user. Once the tables are all created with auto increment primary keys, then the database designer/developer goes about assigning alternate keys that they will actually use to access the data. Most of the time the auto-increment key is simply there to ensure that there is a unique key on the table and it’s often not used as an access path. This is a common design pattern, but is it the best way to create tables using MySQL? I am witting this blog to present the case that it’s a pretty bad idea most of the time.

Why it’s a bad Idea

There are four reasons why, which I will explain in some depth later in the post but for now, they are:

• Auto increment keys …

Percona Live 2018 is coming up soon, and Pythian is excited to be participating again this year. The Percona Live Open Source Database Conference is the premier open source database event for individuals and businesses that develop and use open source database software. This year the conference will take place April 23 – April 25 in Santa Clara, California. Once again Pythian will be hosting the highly anticipated Pythian Community Dinner on Tuesday, April 24th at Pedro’s Restaurant and Cantina.

We’ve also got a fantastic lineup up of speakers. You’ll want to make sure you register for the following sessions:

Monday, April 23, 2018

9:30 am – 12: 30 pm PST

• Derek Downey will present “Hands on ProxySQL”
• Matthias Crauwels & Pep Pla will …

Recently we faced an issue where the config management software had automatically upgraded the mariadb-server-10.1 package to the latest 10.1.31 version. This upgrade broke the galera cluster setup for this installation.

I’ve started to recreate this issue in my local lab setup and I managed to reproduce this problem.

I have created a 3 node galera setup: galera1 (192.168.55.100), galera2 (192.168.55.101) and galera3 (192.168.55.102). All 3 servers run MariaDB-10.1.30. Galera replication is working fine.

This is the basic galera config:

# cat /etc/mysql/conf.d/cluster.cnf
#########################################################
# Galera config
#########################################################

[mysqld]
wsrep_on                                  = ON
wsrep_provider                            = /usr/lib/libgalera_smm.so
wsrep_provider_options                    =
wsrep_cluster_name                        = …

With support of multi-threads replication starting from MySQL 5.7, the operations on slave are slightly different from single-thread replication. Here is a list of some operation tips for the convenience of use as below:

1. Skip a statement for a specific channel.

Sometimes, we might find out that one of the channels stop replication due to some error, and we may want to skip the statement for that channel so that we can restart a slave for it. We need to be very careful not to skip the statement from the other channel, since the command SET GLOBAL sql_slave_skip_counter = N is for global. How can we make sure the global sql_slave_skip_counter is applied to a specific channel and not to the other channel? Here are the steps:

1.1: Stop all slaves by: stop slave;

stop slave;

1.2: Set up the count of statement to skip by: SET GLOBAL sql_slave_skip_counter = N;

SET GLOBAL …

Whenever we do upgrades for our clients from one major version of MySQL to another we strongly recommend to test in two forms.

First, it would be a performance test between the old version and the new version to make sure there aren’t going to be any unexpected issues with the query processing rates. Secondly, do a functional test to ensure all queries that are running on the old version will not have syntactic errors or problems with reserved words in the new version that we’re upgrading to.

If a client doesn’t have an appropriate testing platform to perform these types of tests, we will leverage available tools to test to the best of our ability. More often than not this includes using pt-upgrade after capturing slow logs with …

One of the more common struggles I’ve had to assist with in regard to Amazon RDS is enabling binary logging on read replicas, or forming multi-tier replication in instances using version 5.6 or later after seeing that multi-tier replication is not supported in version 5.5 (for a reason that will become clear by the end of this post.)

First off, let’s have a look at the topology that I have in place in my AWS account. As you’ll see below I have a master, blog1, and a read replica that I created via the AWS console called blog2. You’ll also notice that, despite being supported, if I select instance actions while having blog2 highlighted the option to create a read replica is grayed out.

Further, if we use the MySQL CLI to connect to blog2 and check the global variables for log_bin and binlog_format, you’ll see that binary logging is off and binlog_format is set to statement. This is strange considering that the parameter …

Once upon a time CPU utilization was quite a useful metric. Following are the output of several tools that provide CPU utilization metrics:

top

top reports a load of 1.66.

Is this correct? No. The correct load number is probably closer to 2.4.

# top -b -n 1| head -20
top - 11:27:45 up 151 days,  1:55,  7 users,  load average: 1.66, 1.84, 1.88
Tasks: 389 total,   3 running, 386 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.7%us, 20.6%sy,  1.2%ni, 77.3%id,  0.1%wa,  0.0%hi,  0.1%si,  0.0%st
Mem:  32639636k total, 32206476k used,   433160k free,   235732k buffers
Swap: 16359420k total, 10285664k used,  6073756k free,  2354840k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
16702 root      20   0 8274m 5.0g 5.0g S 85.1 16.1  59164:55 VirtualBox
 4657 root      20   0  9.8g 5.2g 5.1g S 45.5 16.6  26518:13 VirtualBox
 6239 root      20   0  9.8g 5.1g 5.1g S 39.6 16.5  31200:52 VirtualBox
27070 root …

On April 4th 2012 Xtrabackup 2.0 was released in to GA by Percona along with a new streaming feature called xbstream. This new tool allowed for compression and parallelism of streaming backups when running xtrabackup or innobackupex without having to stream using tar, then pipe to gzip or pigz, then pipe to netcat or socat to stream your backup to the recipient server. This resulted in …