Ideas, releases, practical guides, and perspectives from the people building with MySQL.
Statement Digest in MySQL
https://dev.mysql.com/doc/refman/8.0/en/performance-schema-statement-digests.html
The parser is also used by the STATEMENT_DIGEST_TEXT() and STATEMENT_DIGEST() functions,
which applications can call to compute a normalized statement
digest and a digest hash value, respectively, from an SQL
statement.
events_statement_summary_by_digest in Performance Schema contains information
…
[Read more]A few weeks back, we faced an issue in a replication environment for a Managed Services client:
LAST_ERROR_MESSAGE: Worker 2 failed executing transaction ‘UUID:GTID’ at master binlog.0012345, end_log_pos 98765; Error ‘Operation CREATE USER failed for ‘test_user’@’10.10.10.10” on query. Default database: ‘mysql’. Query: ‘CREATE USER ‘test_user’@’10.10.10.10’ IDENTIFIED WITH ‘mysql_native_password’ AS ‘************”
After some initial investigation, we noticed that the user in the replica didn’t exist! Was MySQL going crazy? But then the customer mentioned they had the following error in the primary before being able to execute the query successfully:
root@localhost [mysql]> CREATE USER 'test_user'@'10.10.10.10' identified WITH 'mysql_native_password' BY …[Read more]
MySQL gave us the JSON data type back in mid-2015 with the release of MySQL 5.7.8. Since then, it has been used as a way to escape rigid column definitions and store JSON documents of all shapes and sizes: audit logs, configuration settings, 3rd party payloads, user-defined fields, and more.
Although MySQL gives us functions for reading and writing JSON data, you’ll quickly discover something that is conspicuously missing: the ability to directly index your JSON columns.
In other databases, the best way to directly index a JSON column is usually through a type of index known as a Generalized Inverted Index, or GIN for short. Since MySQL doesn’t offer GIN indexes, we’re unable to directly index an entire stored JSON document. All is not lost though, because MySQL does give us a way to indirectly index parts of our stored JSON …
[Read more]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, …
[Read more]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 …
[Read more]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
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.
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 …
[Read more]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 …
[Read more]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
[Read more]