Showing entries 1 to 10 of 207
10 Older Entries »
Displaying posts with tag: Technical Track (reset)
MySQL high availability with ProxySQL, Consul and Orchestrator

In this post, we will explore one approach to MySQL high availability with ProxySQL, Consul and Orchestrator.

This is a follow up to my previous post about a similar architecture but using HAProxy instead. I’ve re-used some of the content from that post so that you don’t have to go read through that one, and have everything you need in here.

Let’s briefly go over each piece of the puzzle:

– ProxySQL is in charge of connecting the application to the appropriate backend (reader or writer).

It can be installed on each application server directly or we can have an intermediate connection layer with one or more ProxySQL servers. The former probably makes sense if you have a small number of application servers; as the number grows, the latter option becomes more attractive. Another scenario for the …

[Read more]
Join Pythian for Percona Live Europe 2019 Amsterdam

Percona Live is always a great opportunity to learn from the best and brightest in the open-source database community. This time, Percona Live Europe is being held at the Hilton Amsterdam Airport Schiphol, The Netherlands from September 30 to October 2, 2019.

Pythian will be present, as has been the case for the past few years, with some of our technical experts speaking on a variety of subjects and technologies.

Feel free to ping any of us during the conference breaks or community events, as we’ll be happy to answer any questions you have about anything open source, cloud, or data-related.

Here are some sessions you won’t want to miss:

Wednesday, October 2

9:00 AM – …

[Read more]
What to expect at ProxySQL Technology Day in Ghent

On October 3rd ProxySQL will have it’s very first technology day. They have chosen the lovely city of Ghent, Belgium, my home town, as the place to be. For those attending Percona Live Europe in Amsterdam, this is a great opportunity to extend your stay for a bit and take a two-hour train ride from the Percona Live venue at Amsterdam airport to Ghent where you can get some additional ProxySQL-specific content.

The ProxySQL team has selected a few experienced speakers to come and talk about their product. Vlad Fedorkov from ProxySQL LLC will have two sessions. The first one will be about High Performance MySQL and the second one will be about traffic management and performance troubleshooting. Oracle’s MySQL Community Manager, Frederic Descamps, will talk about using ProxySQL with InnoDB Cluster (Group Replication) and Percona’s Marco Tusa …

[Read more]
How to get the most out of your EBS performance

A commonly encountered scenario is when EBS volumes are not performing at the expected theoretical performance. Let’s look at some of the potential reasons for that and how we can “fix” it. (When I say EBS volume, I am talking about SSDs specifically. I rarely see HDDs in use anymore.)

Planning for success

First of all, keep in mind that theoretical IOPS are based on an IO size of 16KB. If you are doing 32KB operations and have a volume rated 1000 IOPS, it means you effectively have 500 IOPS available.

Instance type is closely related to IO performance. When working with databases, you want to use an EBS-optimized instance type. This ensures dedicated bandwidth is available to the IO layer. In addition to that, instance types have a cap on bandwidth and IOPS. So when picking your instance type, don’t base the …

[Read more]
How to solve SELinux and MySQL log rotation issue

Disable SELinux? Think again!

SELinux is always a complicated topic. If you search this on the web, most people will advise just disabling it, but that may not be acceptable from a security point of view in your organization. In this case, we are going to see how to solve an issue with SELinux and MySQL log rotation

We had configured log rotation as per this post. The scripts seemed to work perfectly when running manually. However, when running under cron they would fail to run. Since there were no other errors in the logs, eventually I tracked that down to SELinux. What I found is that SELinux default policies prevent logrotate daemon from making the changes to files outside of /var/log. In this case, MySQL logs were living on /var/lib/mysql so that was clearly the problem.

Figuring out SELinux

The first thing to do when debugging a …

[Read more]
Addressing common challenges when scaling out MySQL horizontally

When we explore strategies to scale our MySQL-based database layer, we realize that there are many different options. As opposed to proprietary solutions, open-source products nourish themselves from their communities and novel architectures or enhanced versions are generated, often incorporating other open source technologies. MySQL is not an exception.

Other players build completely different engines aiming to mitigate some of MySQL well-known weaknesses, but still support its wire protocol, with the challenge of sacrificing as little compatibility as possible.

In this white paper, we briefly discuss the scale-up vs scale-out dilemma, to then review the different scale-out strategies. We are also going to go over some of the most common MySQL architectures and products with horizontal scaling in mind.

Finally, we will share some guidelines to …

[Read more]
How to Test MySQL Server Hostname with ProxySQL Multiplexing

Overview

While working on a MySQL Galera cluster with ProxySQL, I was in the process of testing traffic going to the MySQL nodes by using the @@hostname command to ensure which MySQL host behind the proxy the query ran on. This was important as my client is using query rules to route traffic according to the rule to either the master or the slave.  But to my surprise, I didn’t always get the result that I was expecting.  This is where ProxySQL multiplexing comes into play.

Scenario

In my scenario, I was on a test server connecting to ProxySQL which was then routing my queries to the MySQL Galera nodes.  I would connect into ProxySQL using the MySQL client.

Important note: When testing query routing with ProxySQL using comments and the MySQL client, you have to use the “-c” command line option in order for the comment to not be stripped away when running queries.  You …

[Read more]
How to fix error when MySQL client fails to load SQL file with Blob data

In one of my latest database restore jobs, I was helping a MySQL client with issues related to mysqlbinlog and I wanted to share it here. In case you didn’t know, MySQL is a simple SQL shell with input line editing capabilities, while mysqlbinlog is a utility for processing binary logs a MySQL server. In this case, the server was MariaDB, but the utilities are the same. The database version was 10.1.38-MariaDB.

So, why use mysqlbinlog?

There are many reasons for using mysqlbinlog to process binary log files, but in this case, it was used for point-in-time recovery.

Let’s say you have an erroneous transaction that you run at 3:05 p.m. and your last full backup was run at 12 p.m. To be able to restore your database up to 3:05 p.m., you will need to restore the full backup that you took at 12 p.m. and then apply the events from your binary logs up to the time before you ran the erroneous transaction. This procedure is …

[Read more]
How to Autoscale ProxySQL in the cloud

ProxySQL is a great tool. It’s one of the most recommended technologies in our Open Source Database practice.

Many of our clients are running it or are migrating towards it, but we’ve seen that it is pretty CPU-intensive. We’ve also seen strange behavior in the connection handling when reaching the CPU saturation point.

At this point, we noticed that the frontend_connections counter in the stats_mysql_users table was not decreasing even after the connections were no longer present at the network level. This counter is used to check the max_connections value in the mysql_users configuration table, causing frontend connections to receive a “Too many connections” error. So we determined that the key element here is to scale it properly. Obviously, all the major cloud providers can help us here as they all have features like auto-scaling groups.

Jervin Real of Percona has recently …

[Read more]
Exposing MyRocks Internals Via System Variables: Part 7, Use Case Considerations

(In the previous post, Part 6, we covered Replication.)

In this final blog post, we conclude our series of exploring MyRocks by taking a look at use case considerations. After all, having knowledge of how an engine works is really only applicable if you feel like you’re in a good position to use it.

Advantages of MyRocks

Let’s start by talking about some of the advantages of MyRocks.

Compression

MyRocks will typically do a good job of reducing the physical footprint of your data. As I mentioned in my previous post in this series about compression, you have the ability to configure compression down to the individual compaction layers for each column family. You also get the advantage of the fact that data isn’t updated once it’s written to disk. Compaction, which was …

[Read more]
Showing entries 1 to 10 of 207
10 Older Entries »