Introduction In this article, we are going to test the MySQL 8 implementation of custom SQL CHECK constraints. Although the CHECK clause is a standard SQL feature, prior to MySQL 8.0.16, the clause was parsed and ignored, so this functionality could only be emulated via BEFORE INSERT and UPDATE triggers. Custom SQL CHECK constraints As I explained in this article, custom SQL CHECK constraints are very useful to ensure non-nullability constraints for JPA entity subclass-specific attributes when using the SINGLE TABLE JPA inheritance strategy. To understand the problem, consider we have the... Read More

The post MySQL 8 support for custom SQL CHECK constraints appeared first on Vlad Mihalcea.

In week 3 of our series, Benefits of SQL Diagnostic Manager for MySQL (formerly Monyog), we discuss how to identify and analyze problematic SQL queries using SQL Diagnostic Manager for MySQL. If you missed it, feel free to read our previous post on Agentless Monitoring and Cloud Readiness.

Find Problematic SQL Queries

MySQL and MariaDB currently lack advanced tools for profiling SQL queries (such as SQL Profiler of Microsoft’ SQL Server). While other monitoring tools for MySQL and MariaDB provide monitoring and advisory information on various system metrics, they do not pinpoint the problematic SQL queries. No amount of hardware upgrades and tuning of the parameters in the database server configuration file ‘my.cnf’ and the database server initialization file …

The CTO has issued the decree: “We are moving to the cloud!”  Great, so now what do we do? When it comes to migrating from on-premise to the cloud, there are many factors to consider and decisions that need to be made.  First (and probably most important) on that list: managed DBaaS or setup and manage our own infrastructure?

Type of Cloud

Managed DBaaS options are great but come with some limitations.  The main two questions that should be considered here are around staff/experience and current architecture/database design.  In cases where there is a very limited database (or operations) team, a DBaaS is a great choice. Much of the operational infrastructure is already in place with general best practices in place.  However, a big tradeoff comes around flexibility. In cases where the current infrastructure is complex (for reasons right or wrong), a DBaaS is definitely the wrong choice as you will be …

It’s bad practice to provide world-writable access to critical files in Linux, though we’ve seen time and time again that this is done to conveniently share files with other users, applications, or services. But with Xtrabackup, preparing backups could go wrong if the backup configuration has world-writable file permissions.

Say you performed a backup on a MySQL instance configured with data-at-rest encryption using the keyring plugin. On the backup directory, the generated backup-my.cnf contains these instructions to load this plugin that will be used by Xtrabackup while preparing the backup:

backup-my.cnf

[mysqld]
innodb_checksum_algorithm=crc32
innodb_log_checksum_algorithm=strict_crc32
innodb_data_file_path=ibdata1:12M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=1073741824
innodb_fast_checksum=false
innodb_page_size=16384
innodb_log_block_size=512
innodb_undo_directory=./
innodb_undo_tablespaces=0 …

MySQL has introduced the concept of functional index in MySQL 8.0.13. It is one of the much needed feature for query optimisation , we have seen about histogram in my last blog. Let us explore the functional index and its use cases.

For the below explanation, I have used a production scenario which has 16 core cpu, 32GB RAM and with MySQL version 8.0.16(Latest at the time of writing).

MySQL do support indexing on columns or prefixes of column values (length).

Example: 

mysql>show create table app_user\G
*************************** 1. row ***************************
Table: app_user
Create Table: CREATE TABLE `app_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ad_id` int(11) DEFAULT NULL,
`source` varchar(32) DEFAULT NULL,
`medium` varchar(32) DEFAULT NULL,
`campaign` varchar(32) DEFAULT NULL,
`timestamp` …

Quickly configure replication using DBdeployer [SandBox]
We might have different scenarios when we need a quick setup of replication either between the same version of MySQL (Like 8.0 --> 8.0) or between the different version of MySQL (Like 5.7 --> 8.0) to perform some testings. 

Here in this blog post, I will explain how we can create our replication lab setup quickly using the virtual machine and DBdeployer tool. 

Let's see, how to create replication between the same version and different version of MySQL using DBdeployer step by step. 

Create CentOS VM Please find my …

What are things you must consider before using Galera Cluster Streaming Replication ? 

In Streaming Replication, the node breaks the transaction into fragments, then certifies and replicates them on the slaves while the transaction is still in progress. Once certified, the fragment can no longer be aborted by conflicting transactions. Additionally, Streaming Replication allows the node to process transaction write-sets greater than 2Gb. So, How do you decide whether go with Galera Cluster Streaming Replication or not ? We caution our customers before choosing Galera Cluster Streaming Replication considering several limitations and costs attached to it (explained below the limitations of Galera Cluster Streaming Replication in this post). Even you have decided to proceed with Galera Cluster Streaming Replication, We recommend you to enable it only for a session-level and then only on specific transactions.

…

Recently we had been bitten by a Systemd limitation at the “Tasks” created per-unit ie., process. This includes both the kernel threads and user-space threads, with each thread counting individually.

Am writing this blog as a reference for someone who might come across this limitation.

We have been actively working on migration DB instances, from one DC to the newly built DC .The instances on the newer DC were provisioned with the latest hardware and latest Debian OS. Below is the detailed spec of the system.

RAM             : 244G
Core             : 44Core
HardDisk.   : SSD
IOPS             : 120K
OS         …

This is the first in a series of blog articles in which I will discuss the changing landscape of open source and money. Or, more specifically, open source databases and money. And even more specifically MySQL and its all variants (AWS Aurora, MariaDB, Percona Server, RDS/MySQL) and money. But before going too deep into what is changing, let’s review all the traditional business models in and around the MySQL marketplace.

In general, these are the following types of companies in the MySQL commercial ecosystem, sorted by total annual revenue and addressable market size:

• Developers who do not aim to monetize the open source code, just provide value to others and hope to get development and other contributions in return. This is the purest form of open source. For example, all …

MySQL supports different types of Indexes. They depend on the storage engine and on the type of data. This is the list of supported indexes:

• B-trees (the most common)
• R-trees (for spatial data)
• Hashes (for Memory engine)
• Inverted (for InnoDB Full Text)

Best Practices

My recommendation are valid for InnoDB storage engine. I won’t talk about MyISAM. There are some best practices to follow when designing your tables. These are the 3 most important: