Showing entries 1 to 10 of 21221
10 Older Entries »
Displaying posts with tag: MySQL (reset)
Installing MySQL with Docker

I often need to install a certain version of MySQL, MariaDB, or Percona Server for MySQL to run some experiments, whether to check for behavior differences or to provide tested instructions. In this blog series, I will look into how you can install MySQL, MariaDB, or Percona Server for MySQL with Docker.  This post, part one, is focused on MySQL Server.

Docker is actually not my most preferred way as it does not match a typical production install, and if you look at service control behavior or file layout it is quite different.  What is great about Docker though is that it allows installing the latest MySQL version – as well as any other version – very easily.

Docker also is easy to use when you need a simple, single instance.  If you’re looking into some replication-related behaviors, …

[Read more]
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]
My theory on technical debt and OSS

This is a hypothesis, perhaps it is true. I am biased given that I spent 15 years acknowledging tech debt on a vendor-based project (MySQL) and not much time on community-based projects.

My theory on tech debt and OSS is that there is more acknowledgement of tech debt in vendor-based projects than community-based ones. This is an advantage for vendor-based projects assuming you are more likely to fix acknowledged problems. Of course there are other advantages for community-based projects.

I think there is more acknowledgement of tech debt in vendor-based projects because the community is criticizing someone else's effort rather than their own. This is human nature, even if the effect and behavior aren't always kind. I spent many years marketing bugs that needed to be fixed -- along with many years  leading teams working to fix those bugs.

The joy of database configuration

I am wary of papers with performance results for too many products.Too many means including results from systems for which you lack expertise. Wary means I have less faith in the comparison even when the ideas in the paper are awesome. I have expertise in MySQL, MongoDB, RocksDB, WiredTiger and InnoDB but even for them I have made and acknowledged ridiculous mistakes.

Database configuration is too hard. There are too many options, most of them aren't significant and the approach is bottom-up. I an expert on this -- in addition to years of tuning I have added more than a few options to RocksDB and MySQL.

This post was motivated by PostgreSQL. I want to run the insert benchmark for it and need a good configuration. I have nothing against PG with the exception of a few too many why …

[Read more]
MySQL: Check who’s trying to access data they should not

To illustrate how easy it’s to see who’s trying to access data they have not been granted for, we will first create a schema with two tables:

mysql> create database mydata;
mysql> use mydata
mysql> create table table1 (id int auto_increment primary key, 
              name varchar(20), something varchar(20));
mysql> create table table2 (id int auto_increment primary key, 
              name varchar(20), something varchar(20));

Now, let’s create a user :

mysql> create user myuser identified by 'mypassword';

And as it’s always good to talk about SQL ROLES, let’s define 3 roles for our user:

  • myrole1: user has access to both tables in their entirety, reads and writes
  • myrole2: user has access only to `table2`, reads and writes
  • myrole3: user has only access to the column `name`of `table1` …
[Read more]
Installing MySQL on Oracle Cloud’s “Always Free” Compute Instance

At Oracle’s OpenWorld 2019, Oracle announced an Oracle Cloud Free Tier, which includes Oracle Cloud services that will always be free – as long as you use them or keep them active.

Most other hyperscale cloud vendors offer a free 12-month trial before they start charging you, so to see Oracle provide a “truly free” service is really great. And the free services are not just limited to one or two choices – you get a wide variety of free services to choose from. As of this blog post, here is a brief summary of free services (these are subject to change in the future):

Databases – Your choice of Autonomous Transaction Processing …

[Read more]
MySQL Clone Plugin Speed Test

In my previous blog, I have explained how the MySQL clone plugin works internally. In this blog, I am going to do a comparison of  Backup and Recovery speed of MySQL clone plugin with other available mysql open source backup tools.

Below tools are used for speed comparison of Backup and Recovery,

  1. Clone-Plugin
  2. Xtrabackup
  3. mysqldump
  4. mydumper with myloader
  5. mysqlpump

Test …

[Read more]
Hash join in MySQL 8

For a long time, the only algorithm for executing a join in MySQL has been variations of the nested loop algorithm. With the release of MySQL 8.0.18, the server can now execute joins using hash join. This blog post will have a look at how it works, when it is used, and how it compares to the old join algorithms in MySQL in terms of performance.…

Facebook Twitter LinkedIn

Watch Out for Disk I/O Performance Issues when Running EXT4

Recently, at Percona Live Europe 2019, Dimitri Kravchuk from Oracle mentioned that he observed some unclear drop in performance for MySQL on an ext4 filesystem with the latest Linux kernels. I decided to check this case out on my side and found out that indeed, starting from linux kernel 4.9, there are some cases with notable (up to 2x) performance drops for ext4 filesystem in direct i/o mode.

So what’s wrong with ext4? It started in 2016 from the patch that was pushed to kernel 4.9: “ext4: Allow parallel DIO reads”. The purpose of that patch was to help to improve read scalability in direct i/o mode. However, along with improvements in pure read workloads, it also introduced regression in intense mixed random read/write scenarios. And it’s quite weird, but this issue had not been …

[Read more]
Using an LSM for analytics

How do you use an LSM for analytics? I haven't thought much about it because my focus has been small data -- web-scale OLTP since 2006. It is a great question given that other RocksDB users (Yugabyte, TiDB, Rockset, CockroachDB) support analytics.

This post isn't a list of everything that is needed for an analytics engine. That has been described elsewhere. It is a description of problems that must be solved when trying to use well-known techniques with an LSM. I explain the LSM challenges for a vectorized query engine, columnar storage, analytics indexes and bitmap indexes.

There is much speculation in this post. Some of it is informed -- I used to maintain bitmap indexes at Oracle. There is also big opportunity for research and for R&D in this space. I am happy to be corrected and be told of papers that I should read.

Vectorized query engine

See …

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