The latest from the MySQL community

NoSQL Development in MySQL Using Python.

In This Tutorial, I will talk about below topic:-
 Introduction to NoSQL + MySQL =MySQL 8.0.
 What is XdevAPI , Xprotocol , Xplugin ?
 How Application communicate with MySQL using XdevAPI ?
 Installation online and offline.
 How to use XdevAPI to develop application using MySQL as NoSQL databases (Similar to MongoDB , ES ) ?
 Conclusion.


Introduction to NoSQL + MySQL =MySQL 8.0

Whenever we have fast changing data , lots of data it is tedious task for application developers to depend on MySQL DBA/developer to get required schema details , instead MySQL document store has new paradigm shift over traditional SQL model which is schema-less , low maintenance, no need to maintain multiple databases (likewise one database for relational model and another for document model).
…

Percona XtraDB Cluster 8.0 comes with an upgraded Galera 4.0 library, which provides a new feature – streaming replication. Let’s review what it is and when it might be helpful.

Previous versions of Percona XtraDB Cluster with Galera 3.x had a limitation in how big transactions are handled.

Let’s review the performance under sysbench-tpcc workload when in parallel we update a big update on a table that is even non-related to the tables in the primary workload.

Without Streaming Replication

Let’s run two workloads.

1. sysbench-tpcc workload with 1 sec resolution
2. In parallel run UPDATE oltp.sbtest SET k=k+1 LIMIT 1000000

Running update:

mysql> update sbtest1 set k=k+1 limit 1000000;
Query OK, 1000000 rows affected (34.48 sec)
Rows matched: 1000000  Changed: …

I was testing the latest Percona XtraDB Cluster 8.0 (PXC) release which has the Galera 4 plugin, and I would like to share my experiences and thoughts on the Streaming Replication feature so far.

What Is Streaming Replication, in One Sentence?

In Galera 4, the large transaction could split into smaller fragments, and even before it got committed these fragments have been replicated to the other nodes and have already started the certification and apply process.

The manual describes all the pros and cons, but let’s see how it works. I have created a table with 10M rows and I am going to run some large updates on that.

First I have run the updates without Streaming Replication, and because it is disabled by …

ARM community that has developers from varied organizations has contributed some really good patches to MySQL. Most of them are awaiting acceptance. Blog is meant to analyze these patches along with their pros and cons. Hopefully this would help ease MySQL/Oracle to accept these long-awaited patches.

Community Patches

1. Optimizing checksum

• MySQL uses 2 types of checksum: crc32c and crc32. They both are different since both uses different polynomials.
  • crc32c is used in MySQL by InnoDB to calculate page-checksum.
  • crc32 is used in MySQL for table checksum, binlog-checksum, etc…

crc32c

• Page checksum is calculated during each page read/write so crc32c can quickly show up as one of the top functions in perf report. Ensuring use of optimized versions of it could help improve the overall throughput of the system.
…

For more information on how to create a service look at man systemd.service Install Directory for MCM installation is /opt Download latest tar package of MCM from edelivery.oracle.com or support.oracle.com (today this is package: MySQL Cluster Manager 1.4.8 TAR for Generic Linux x86 (64bit)) Run commands below once you have downloaded the MCM package:

sudo su -
cd /opt
tar xzf /tmp/mcm-1.4.8-linux-glibc2.12-x86-64bit.tar.gz
mv mcm-1.4.8-linux-glibc2.12-x86-64bit mcm
cp /opt/mcm/mcm1.4.8/etc/mcmd.ini /opt/mcm/
mkdir /opt/mcm/mcm_data

Set manager-directory to folder /opt/mcm/mcm_data in configuration file:

cat /opt/mcm/mcmd.ini | sed 's/^##manager-directory.*/manager-directory = \/opt\/mcm\/mcm_data/' >> /opt/mcm/mcmd.ini

Structure should be:

 …

The MySQL Document Store is an quick and easy to use NoSQL JSON Document Database that was designed to be easy to use.  But there is one thing that may not be obvious if you are working to modify multiple documents. 

So let us start with a very simple example collection of three documents.

>db.xyz.find()
{
    "a": 1,
    "_id": "00005eb2ba3c0000000000000004"
}
{
    "b": 2,
    "_id": "00005eb2ba3c0000000000000005"
}
{
    "c": 3,
    "_id": "00005eb2ba3c0000000000000006"
}
3 documents in set (0.0008 sec)

So we have three different documents and later determine we need to add the same key/value pair for all of them.

But the following will not work:

>db.xyz.modify().set("fizz","buzz")
…

In Percona’s Managed Services, we start up and shut down MySQL servers all the time.  Over the years, we’ve seen various issues occur due to the way servers are shut down.  No matter if it is a situation where unexpected errors appear in the log or you’re stuck waiting for hours for a server to finishing shut down… Here are some steps we can take to minimize any trouble and prepare MySQL for shutdown.

This list of “super safe practices” was born from lessons we’ve learned in a wide variety of MySQL environments.

1. Stop Replication.

Under some (rare) circumstances, a slave may try to startup in the incorrect position. To help minimize this risk, stop the IO thread first so it’s not receiving new events.

STOP SLAVE IO_THREAD;

Wait for the SQL thread to apply all events, then stop it too.

SHOW SLAVE STATUS\G
STOP SLAVE SQL_THREAD;

This puts both of the …

On a busy server, the binary logs can end up being one of the largest contributors to amount of disk space used. That means higher I/O, larger backups (you are backing up your binary logs, right?), potentially more network traffic when replicas fetch the logs, and so on. In general, binary logs compress well, so it has been a long time wish for a feature that allowed you to compress the logs while MySQL are still using them. Starting from MySQL 8.0.20 that is now possible. I will take a look at the new feature in this post.

Configuration

The binary log compression feature is controlled by two variables, one for enabling the feature and another to specify the compression level. These are summarized in the below table.

Use MySQL Enterprise Audut plugin to track data changes on specific tables. Scripts and documentation on github: https://github.com/wwwted/Database-change-tracking Tracked data (audit logs) can then be inserted into reporting db using parser scripts. Limitations:

• Changing tracking rules stopps all tracking for existing connections until the reconnect. This is a limitation in MySQL EE Audit filter handling.
• No filtering for different users, easy to implement if needed in change_tracking.sql SP (START/STOP).
• Only tracking INSERT/UPDATE/DELETE statements. Easy to modify if needed.

1 Enable tracking on target server …

In this day and age, large enterprise companies make use of Ansible, Puppet, or Chef to provision MySQL servers, be it replica sets or clusters. This eases the burden of deployment and workflow management.

However, for some smaller companies, the learning curve hampers the immediate adoption of automation software. This is where the MySQL Shell helps, by allowing you to deploy an N-node InnoDB Cluster or ReplicaSet in less than 60 lines of code.

The Requirements

• Percona Server for MySQL version 8.0.17 or later, preferable version 8.0.19, each node started as standalone servers
• Percona MySQL Shell 8.0 or equivalent upstream version
• MySQL root user and password or equivalent user with grant option
• Hostname configured on each node, can be done with …