Geo locations are a cornerstone of modern applications. Whether you’re a food delivery business or a family photographer, knowing the closest “something” to you or your clients can be a great feature.

In our ‘Scaling and Optimization’ training class for MySQL, one of the things we discuss is column types. The spatial types are only mentioned in passing, as less than 0.5% of MySQL users know of their existence (that’s a wild guess, with no factual basis). In this post, we briefly discuss the POINT type and how it can be used to calculate distances to the closest public park.

Import the data

To start off, we need a few tables and some data. The first table will hold the mapping between the zip code and its associated latitude/longitude. GeoNames has this data under the Creative Commons v3 license, …

Let’s continue the discovery of the MySQL Database Resource when deploying on Oracle Cloud Infrastructure using Terraform.

Last week, we saw how to create custom configurations and define user variables. Today we will see how we can define a backup policy and a maintenance window.

Backup Policy

In the oci_mysql_mysql_db_system resource, we will add a new section called backup_policy like this:

backup_policy {
       is_enabled        = "true"
       retention_in_days = "3"
       window_start_time = "01:00-00:00"
       freeform_tags = {"backup_defined_by"="Terraform"}
       pitr_policy {
            is_enabled = "true"
       }
}

This part of code (you can see in a working Terraform architecture sample), enables backup, sets the retention days to 3. It also defines the starting time …

Where I work, there is an ongoing discussion about test data generation. At the moment we do not replace or change any production data for use in the test environments, and we don’t generate test data.

That is safe and legal, because production data is tokenized. That is, PII and PCI data is being replaced by placeholder tokens which can be used by applications to access the actual protected data through specially protected access services. Only a very limited circle of people is dealing with data in behind the protected services.

Using production data in test databases is also fast, because we copy data in parallel, at line speed, or we make redirect-on-write (“copy-on-write” in the age of SSD) writeable snapshots available.

Assume for a moment we want to change that and

• mask data from production when we copy it to test databases
• reduce the amount of data used in test databases, while …

Overview

JavaScript Object Notation (JSON) is a light-weight text-based file format similar to YAML or XML which simplifies data exchange. It was invented by Douglas Crockford in the early 2000s and became increasingly popular with the rise of document-based (also called NoSQL) databases.

JSON supports strings, numbers, booleans, objects, and arrays as well as null values. A simple JSON example containing key-value pairs, an object "bandMembers" and an array "songs" would look like this:

{
  "artist": "Starlord Band",
  "bandMembers": {
    "vocals": "Steve Szczepkowski",
    "guitar": "Yohann Boudreault",
    "bass": "Yannick T.",
    "drums": "Vince T."
  },
  "bandMembersCount": 4,
  "album": "Space Rider",
  "releaseDate": "2021-10-25",
  "songs": [
    "Zero to Hero",
    "Space Riders with No Names", …

Query latencies in one data center are larger than elsewhere for one replication hierarchy, but only in the high percentiles. This impacts production and traffic is being failed away from that data center to protect production.

When the P50 and P90 look okay, but the P99 and the P99.9 do not, the database(s) operate normally, and only some queries are running slow. The initial guess was “for some queries the plan has flipped, but only in that data center.”

But first let’s have a look at the database size and the schema.

A tiny database

The schema in question holds metadata for a change data capture process, and that is not a lot.

# du -sh *
0    coredumps
5.6G    data
704M    log
0    tmp
# du -sh data/theschema
93M    data/theschema

and in memory:

The mysqld process has a …

This article outlines the basic configurations for setting up and deploying Percona XtraDB Cluster 8.0 (PXC) on Amazon EC2, as well as what is new in the setup compared to Percona XtraDB Cluster 5.7.

What is Percona XtraDB Cluster an ideal fit for?

Percona XtraDB Cluster is a cost-effective, high-performance clustering solution for mission-critical data. It combines all the improvements, and functionality found in MySQL 8 with Percona Server for MySQL‘s Enterprise features and Percona’s upgraded Galera library.

A Percona XtraDB Cluster environment is an ideal fit for applications requiring 5-9s uptime with high read …

As a Database Engineer, One of the biggest challenges in day-to-day activity is performing DDL on high-traffic and transaction-intensive tables. It will become overhead when handling a large number of servers/shards.

As a standard process, we will first deploy the changes in DEV and QA before deploying them in production.

In the sharded environment, It will become a heavy overhead to maintain the schema changes in DEV, QA, and PROD servers. Since we will have multiple servers in the sharding.

To overcome this deployment supervision, the Skeema tool will help to deploy the changes in QA, DEV, and PROD in a safe and parallel ( for Shards ) as well.

This blog focuses on the basic operation of Skeema, will have a series of blogs on Skeema

1. About the …

Update June 12, 2023: This component has been deprecated in v17 and will be removed in v18! We recommend that you instead use VTOrc with the semi_sync durability policy. Introduction # MySQL group replication is a new replication mechanism that was released in 2016. Group replication involves establishing a group of nodes that are coordinated automatically via Group Communication System (GCS) protocols, an implementation of Paxos. For a transaction to commit, a majority of the group has to agree on the order of a given transaction in the global sequence of transactions.

A database is showing replication delay, and so are all the other instances of the same replication hierarchy, all of which reside in Openstack.

Shortly before 21:30 the database begins to lag, until around 23:45, when it starts to catch up, slowly. After 00:30, we gain delay again, plateau and then around 01:45, we catch up.

The database is moving deep into replication delay sometimes. It does not do that on bare metal.

Ground Truth

The VM is a nice hardware blade simulation, 16C/32T, 128 GB of memory.

The data is on persistent volume backed by Ceph.

# df -Th /mysql/<hierarchyname>/
Filesystem                Type  Size  Used Avail Use% Mounted on
/dev/mapper/vg00-mysqlVol xfs   1.8T  1.1T  766G  58% /mysql/<hierarchyname>
# vgs
  VG    #PV #LV #SN Attr   VSize VFree
  sysvm   1   8   0 …

Recently, somebody asked me how he can find the long running transactions in MySQL.

I already have one MySQL Shell plugin that allows you to find the current transactions sorted by time. The plugin allows you to also get the details about the desired transaction. See check.getRunningStatements().

Let’s see how we can easily find those long transaction that can be a nightmare for the DBAs (see MySQL History List Length post).

SELECT thr.processlist_id AS mysql_thread_id,
       concat(PROCESSLIST_USER,'@',PROCESSLIST_HOST) User,
       Command,
       FORMAT_PICO_TIME(trx.timer_wait) AS trx_duration,
       current_statement as `latest_statement`
  FROM …