Planet MySQL

Percona XtraDB Cluster 5.7.24-31.33 Is Now Available

Percona is glad to announce the release of Percona XtraDB Cluster 5.7.24-31.33 (PXC) on January 4, 2019. Binaries are available from the downloads section or from our software repositories.

Percona XtraDB Cluster 5.7.24-31.33 is now the current release, based on the following:

Deprecated

The following variables are deprecated starting from this release:

  • wsrep_preordered was used to turn on transparent handling of preordered replication events applied locally first before being replicated to other nodes in the cluster. It is not needed anymore due to the carried out performance fix eliminating the lag in asynchronous replication channel and cluster replication.
  • innodb_disallow_writes usage to make InnoDB avoid writes during SST was deprecated in favor of the innodb_read_only variable.
  • wsrep_drupal_282555_workaround avoided the duplicate value creation caused by buggy auto-increment logic, but the correspondent bug is already fixed.
  • session-level variable binlog_format=STATEMENT was enabled only for pt-table-checksum, which would be addressed in following releases of the Percona Toolkit.
Fixed Bugs
  • PXC-2220: Starting two instances of Percona XtraDB Cluster on the same node could cause writing transactions to a page store instead of a galera.cache ring buffer, resulting in huge memory consumption because of retaining already applied write-sets.
  • PXC-2230: rgcs.fc_limit=0 not allowed as dynamic setting to avoid generating flow control on every message was still possible in my.cnf due to the inconsistent check.
  • PXC-2238: setting read_only=1 caused race condition.
  • PXC-1131: mysqld-systemd threw an error at MySQL restart in case of non-existing error-log in Centos/RHEL7.
  • PXC-2269: being not dynamic, the pxc_encrypt_cluster_traffic variable was erroneously allowed to be changed by a SET GLOBAL statement.
  • PXC-2275: checking wsrep_node_address value in the wsrep_sst_common command line parser caused parsing the wrong variable.

Help us improve our software quality by reporting any bugs you encounter using our bug tracking system. As always, thanks for your continued support of Percona!

 

Managing GitHub with Terraform

If a service can be managed with API most probably you will find it in an impressive list of Terraform providers. Yes, GitHub is there, too. TwinDB hosts software in GitHub, it felt wrong I don’t manage it with Terraform yet, so I decided to give it a go. Prerequisites Directory layout I keep all […]

The post Managing GitHub with Terraform appeared first on TwinDB.

TasksMax: Another Setting That Can Cause MySQL Error Messages

Recently, I encountered a situation where MySQL gave error messages that I had never seen before:

2018-12-12T14:36:45.571440Z 0 [ERROR] Error log throttle: 150 'Can't create thread to handle new connection' error(s) suppressed 2018-12-12T14:36:45.571456Z 0 [ERROR] Can't create thread to handle new connection(errno= 11) 2018-12-12T14:37:47.748575Z 0 [ERROR] Error log throttle: 940 'Can't create thread to handle new connection' error(s) suppressed 2018-12-12T14:37:47.748595Z 0 [ERROR] Can't create thread to handle new connection(errno= 11)

I was thinking maybe we hit some

ulimit limitations or similar, but all the usual suspects were set high enough, and we were not even close to them.

After googling and discussing with the customer, I found they had had similar issues in the past, and I learned something new. Actually it is relatively new, as it has been around for a few years but is not that well known. It is called TasksMax:

Specify the maximum number of tasks that may be created in the unit. This ensures that the number of tasks accounted for the unit (see above) stays below a specific limit. This either takes an absolute number of tasks or a percentage value that is taken relative to the configured maximum number of tasks on the system. If assigned the special value “infinity“, no tasks limit is applied. This controls the “pids.max” control group attribute. For details about this control group attribute, see pids.txt.

Source Manual.

It was introduced to systemd in 2015:

I’d like to introduce DefaultTasksMax= that controls the default
value of the per-unit TasksMax= by default, and would like it to
set to some value such 1024 out-of-the-box. This will mean that any
service or scope created will by default be limited to 1024
tasks. This of course is a change from before that has the
potential to break some daemons that maintain an excessive number
of processes or threads. However, I think it’s a much better choice
to raise the limit for them, rather than stay unlimited for all
services by default. I think 1024 is not particularly low, but also
not particularly high. Note that the kernel by default limits the
number of processes to 32K in total anyway.

In the end, we can see in this commit they chose 512 to be the default settings for TasksMax, which means services that are not explicitly configured otherwise will only be able to create at most 512 processes or threads.

Why 512? I have read through the email list and there was some discussion about what should be the default. Eventually, I found this comment from one of the developers:

Anyway, for now I settled for the default TasksMax= setting of 512 for
all units, plus 4096 for the per-user slices and 8192 for each nspawn
instance. Let’s see how this will work out.

So this is how 512 become the default and no one has touched it since. MySQL is able to reach that limit and can cause error messages like those we see above.

You can increase this limit by creating a file called

/etc/systemd/system/mysqld.service  :[Service] TasksMax=infinity

You can use a specific number like 4096 (or any other number based on your workload), or infinity which means MySQL can start as many processes as it wants.

Conclusion

Not everyone will reach this limit, but if MySQL is giving error messages like this you should also check TasksMax as well as the other usual suspects. The easiest way to verify the current setting is:

#> systemctl show -p TasksMax mysql TasksMax=512


Photo by Vlad Tchompalov on Unsplash

MariaDB JIRA for MySQL DBAs

These days several kinds and forks of MySQL are widely used, and while I promised not to write about MySQL bugs till the end of 2018, I think it makes sense to try to explain basic details about bug reporting for at least one of vendors that use JIRA instances as a public bug tracking systems. I work for MariaDB Corporation and it would be natural for me to write about MariaDB's JIRA that I use every day.

As a side note, Percona also switched to JIRA some time ago, and many of the JIRA-specific details described below (that are different comparing to good old https://bugs.mysql.com/) apply to Percona bugs tracking system as well.

Why would MariaDB bugs be interesting to an average MySQL community member who does not use MariaDB at all most of the time? One of the reasons is that some MySQL bugs are also reported (as "upstream") to MariaDB and they may be fixed there well before they are fixed in MySQL. Consider MDEV-15953 - "Alter InnoDB Partitioned Table Moves Files (which were originally not in the datadir) to the datadir" (reported by Chris Calender) as an example. It was fixed in MariaDB 5 months ago, while corresponding Bug #78164 is still "Verified" and got no visible attention for more that 3 years. The fix is 12 rows added in two files (test case aside), so theoretically can be easily used to modify upstream MySQL by an interested and determined MySQL user who already compiles MySQL from the source code (for whatever reason), if the problem is important in their environment.

Another reason is related to the fact that work on new features of MariaDB server and connectors is performed in an open manner at all stages. You can see current plans, discussions and decision making on new features happening in real time, in JIRA. Existing problems (that often affect both MySQL and MariaDB) are presented and analyzed, and reading related comments may be useful to understand current limitations of MySQL and decide if at some stage switching to MariaDB or using some related patches may help in your production environment. There is no need to wait for some lab preview release. You can also add comments on design decisions and question them before it's too late. Great example of such a useful to read (for anyone interested in InnoDB) feature request and work in progress is MDEV-11424 - "Instant ALTER TABLE of failure-free record format changes".

Yet another reason to use MariaDB JIRA and follow some bug reports and feature requests there is to find some insights on how MySQL, its components (like optimizer) and storage engines (like InnoDB) really work. Consider my Bug #82127 - "Deadlock with 3 concurrent DELETEs by UNIQUE key". This bug was originally reported to Percona as lp:1598822 (as it was first noticed with Percona's XtraDB emgine) and ended up in their JIRA as PS-3479 (still "New"). In MySQL bugs database it got "Verified" after some discussions. Eventually I gave up waiting for "upstream" to make any progress on it and reported it as MDEV-10962. In that MariaDB bug report you can find explanations of the behavior noticed, multiple comments and ideas on the root case and on how to improve locking behavior in this case, links to other related bugs etc. It's a useful reading. Moreover, we see that there are plans to improve/fix this in MariaDB 10.4.

I also like to check some problematic and interesting test cases, no matter in what bugs database it was reported, on both MariaDB Server, Percona Server and MySQL Server, as long as it's about some common features. But may be it's so because I work with all these as a support engineer.

Anyway, one day following MariaDB Server bugs may help some MySQL DBA to do the job better. So, I suggest all MySQL users to check MariaDB's JIRA from time to time. Some basic details about the differences comparing to MySQL's bugs database are presented below.

First thing to notice in case of MariaDB's JIRA is a domain name. It's jira.mariadb.org, so bug tracking system formally "belongs" to MariaDB Foundation - non-profit entity that supports continuity and open collaboration in the MariaDB ecosystem. Both MariaDB Foundation employees, MariaDB Corporation employees, developers working for partners (like Codership) and community members (like Olivier Bertrand, author of CONNECT storage engine I had written about here) work on source code (and bugs processing and fixing) together, at GitHub. Different users have different roles and privileges in JIRA, surely. But there is no other, "internal" bugs database in MariaDB Corporation. All work or bugs and features, time reporting, code review process, as well as release planning happen (or at least is visible) in an open manner, in JIRA.

Even if you do not have JIRA account, you still can see Jira Road Map, release plans and statuses. You can see all public comments and history of changes for each bug. If you create and log in into your account (this is needed to report new bugs, vote for them or watch them and get email notifications about any changes, obviously) you'll see also more details on bugs, like links to GitHub commits and pull requests related to the bug.

Unlike MySQL bugs database where bugs are split into "Categories" (where both "MySQL Server: Information schema" and "MySQL Workbench" are categories more or less of the same level) but are numbered sequentially over all categories, JIRA instances usually support "Projects", with separate "name" and sequential numbering of bugs per project.

At the moment there are 17 or so projects in MariaDB JIRA, of them the following public ones are most interesting for MySQL community users, I think:
Let's consider one MariaDB Server bug for example:

Unlike in MySQL bugs database, JIRA issues have "Type". For our case it's important that feature requests usually end up as "Task" vs "Bug" as a type for a bug. Some projects in MariaDB JIRA may also support a separate "New Feature" type to differentiate features from tasks not related to creating new code. In MySQL separate severity (S4, "Feature request") is used.

MariaDB JIRA issues have priorities from the following list:
  • Trivial
  • Minor
  • Major
  • Critical
  • Blocker
By default MariaDB bugs are filed with intermediate, "Major" priority. Priority may be changed by the bug reporter or by JIRA users (mostly developers) who work on the bug, it often changes with time (priority may increase if more users are affected, or if the fix does not happen for long enough time etc, or decrease when the problem can be workarounded somehow for affected users). Usually a bug with "Blocker" priority means there should be no next minor release for any major version listed in "Fix Version/s" without the fix.

There are many fields in MySQL bugs database to define priority of the fix (including "Priority" itself), but only "Severity" is visible to public. Usually "Severity" of the MySQL bug does NOT change with time (if only before it's "Verified").

It is normal to list all/many versions affected by the bug in JIRA in "Affected Version/s". If the bug is fixed, in "Fix Version/s" you can find the exact list of all minor MariaDB Server versions that got the fix.

Each JIRA issue has a "Status" and "Resolution". In MySQL bugs database there is just "Status" for both. Valid statuses are:
  • OPEN - this is usually a bug that is just reported or is not yet in the process of fixing.
  • CONFIRMED - this status means that some developer checked bug report and confirmed it's really a bug and it's clear how to reproduce it based on the information already present in the report. More or less this status matches "Verified" MySQL bug. But unlike in MySQL, even "Open" bug may be assigned to a developer to further work on it.
  • CLOSED - the bug is resolved somehow. See the content of the "Resolution" filed for details on how it was resolved.
  • STALLED - this is a real bug and some work on it was performed, but nobody actively works on it now.
  • IN PROGRESS - assignee is currently working on the fix for the bug.
  • IN REVIEW - assignee is currently reviewing the fix for the bug.
The following values are possible for "Resolution" field:
  • Unresolved - every bug that is not "CLOSED" is "Unresolved".
  • Fixed - every bug that was fixed with some change to the source code. If you log in to JIRA you should be able to find links to GitHub commit(s) with the fix in the "Fixed" JIRA issue.
  • Won't Fix - the problem is real, but it was decided not to fix it (as it's expected or may be too hard to fix). Consider my MDEV-15213 - "UPDATEs are slow after instant ADD COLUMN" as one of examples.
  • Duplicate - there is another bug report about the same problem. You can find link to it in the JIRA issue.
  • Incomplete - there is no way to reproduce or understand the problem based on the information provided. See MDEV-17808 for example.
  • Cannot Reproduce - bug reporter himself can not reproduce the problem any more, even after following the same steps that caused the problem before. See MDEV-17667 for example.
  • Not a Bug - the problem described is not a result of any bug. Everything works as designed and probably some misunderstanding caused bug reporter to think it was a bug. See MDEV-17790 as a typical example.
  • Done - this is used for completed tasks (like MDEV-17886) or bugs related to some 3rd party stored engine where the fix is done, but it's up to MariaDB to merge/use fixed version of the engine (like MDEV-17212).
  • Won't Do - it was decided NOT to do the task. See MDEV-16418 as one of examples.
In MySQL there are separate bug statuses for (most of) these. There are some tiny differences for the way some statuses like "Cannot reproduce" are applied by those who process bugs in MySQL vs MariaDB though.

Explanations above should be enough for any MySQL bugs database user to start using MariaDB's JIRA efficiently, I think. But I am open to any followup questions and I am considering separate blog posts explaining the life cycle of a MariaDB Server bug and some tips on efficient search in MariaDB JIRA.

RunDeck Series 5 – Encrypt Key Files And Passwords In RunDeck

While managing multi servers in a single place, we need a secure authentication method which includes SSH Keys, Passwords and etc. RunDeck is having a great feature called Key Storage. RunDeck Key Storage is a secure and encrypted place for storing confidential contents. Its using HashiCorp Vault for this. Its already enabled by default. So …

The post RunDeck Series 5 – Encrypt Key Files And Passwords In RunDeck appeared first on SQLgossip.

ProxySQL Series:​ Amazon Aurora (RDS) Read-Write Split.

In this blog we are going to see how to implement Proxysql for Amazon Aurora RDS, this would be next in series of ProxySQL blogs. Below is the list of our previous blogs on ProxySQL which provides deeper insights based on different use cases and different architecture.

ProxySQL 2.0 comes with native support for AWS Aurora, and also with many exciting new features. We will be exploring it later in upcoming blogs. Amazon Aurora is highly performant and fault tolerant MySQL Compatible DBAAS provided by Amazon. To know more about Aurora you can check our previous blog here

For the purpose of this blog I have used Aurora cluster of size 2 (1 Master + 1 Replica), Aurora is not available within free tier usage of AWS, the smallest supported instance would be t2.small (1VCPU & 2GB RAM).

Now let’s see about the end-point provided by AWS while provisioning an Aurora Cluster.

Aurora Endpoints:

Endpoints are the connection URI’s provide by AWS to connect to the Aurora database. Listed below the endpoints provided for an Aurora cluster.

  • Cluster Endpoint
  • Reader Endpoint
  • Instance Endpoint

Cluster Endpoint:

An endpoint for an Aurora DB cluster that connects to the current primary instance for that DB cluster. It provides failover support for read/write connections to the DB cluster. If the current primary instance of a DB cluster fails, Aurora automatically fails over to a new primary instance

Reader Endpoint:

An endpoint for an Aurora DB cluster that connects to one of the available Aurora Replicas for that DB cluster. Each Aurora DB cluster has a reader endpoint. The reader endpoint provides load balancing support for read-only connections to the DB cluster.

Instance Endpoint:

An endpoint for a DB instance in an Aurora DB cluster that connects to that specific DB instance. Each DB instance in a DB cluster, regardless of instance type, has its own unique instance endpoint

Among this different end-point, we will be using the “Instance Endpoint” ie., individual end-point in ProxySQL config.

The problem here is application should have read and writes split at the application layer. So that it can use the Reader and writer endpoints efficiently. But if a user migrates to Aurora for scalability then we need to have an intelligent proxy like Maxscale / ProxySQL, to have on the fly Read-Write split with almost Zero application level changes.

Proxysql With Aurora

Now let’s see the configuration of ProxySQL for Aurora.

ProxySQL Version : proxysql-rc2-2.0.0-1-centos7.x86_64.rpm

OS version : Centos 7.3

Core : 1

RAM : 1G

Aurora Version: MySQL 5.7 compatible(aurora_version-2.03.1)

Instance End points: aurora.cbexxg6tsgtv.ap-south-1.rds.amazonaws.com, aurora-ap-south-1b.cbexxg6tsgtv.ap-south-1.rds.amazonaws.com

Below is the snap of my cluster

With the above end-points, I have the below host_group in proxysql

  • 10(Writer Group)
  • 11(Reader Group)
Adding servers to proxysql :

Writer group:

ProxySQL>INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'aurora.cbexxg6tsgtv.ap-south-1.rds.amazonaws.com',3306);

Reader group:

ProxySQL>INSERT INTO mysql_servers(hostgroup_id,hostname,port,max_replication_lag) VALUES (11,'aurora-ap-south-1b.cbexxg6tsgtv.ap-south-1.rds.amazonaws.com',3306,1); ProxySQL>load mysql servers to runtime; ProxySQL>save mysql servers to disk;

Am inducing a replication lag threshold of 1secs, since its a cluster with a promised replication lag of less than 50Ms for query routing, This can be changed based on your use case and application criticalness.

Adding users:

You can have all your application user embedded into proxysql, Here am adding a single user for the purpose of the demo

ProxySQL>INSERT INTO mysql_users (username,password,default_hostgroup) VALUES ('mydbops','45264726',10); ProxySQL>load mysql users to runtime; ProxySQL>save mysql users to disk; Monitor user:

Proxysql needs a monitoring user to ping the MySQL server, make replication checks, make sure to have “replication client” privilege for monitor user

ProxySQL>UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username'; ProxySQL>UPDATE global_variables SET variable_value='KabileshKabi' WHERE variable_name='mysql-monitor_password'; ProxySQL>load mysql variables to runtime; ProxySQL>save mysql variables to disk;

You can proceed to check the monitor status from proxysql by running the below command.

ProxySQL> select * from monitor.mysql_server_ping_log order by time_start_us desc limit 3; +--------------------------------------------------------------+------+------------------+----------------------+------------+ | hostname | port | time_start_us | ping_success_time_us | ping_error | | aurora.cbexxg6tsgtv.ap-south-1.rds.amazonaws.com | 3306 | 1545126658026840 | 1634 | NULL | | aurora-ap-south-1b.cbexxg6tsgtv.ap-south-1.rds.amazonaws.com | 3306 | 1545126658024189 | 737 | NULL | | aurora.cbexxg6tsgtv.ap-south-1.rds.amazonaws.com | 3306 | 1545126648026685 | 1419 | NULL | +--------------------------------------------------------------+------+------------------+----------------------+------------+

Query Rules:

Am herewith adding the default query for RW-Split as below.

ProxySQL>INSERT INTO mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,apply) VALUES (1,1,'^SELECT.*FOR UPDATE',10,0); ProxySQL>INSERT INTO mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,apply) VALUES (2,1,'^SELECT.*',11,1);

Now the configuration for Read-Write splitting is pretty much done.

How proxysql Monitors Aurora:

Proxysql 2.0 comes with native monitoring for Aurora, by checking the variable “Innodb_read_only”, to determine the reader/Writer for the individual nodes, This info of check has to be added to the table “mysql_replication_hostgroups” in proxysql

Below is the structure of the table:

Table: mysql_replication_hostgroups

Create Table: CREATE TABLE mysql_replication_hostgroups ( writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY, reader_hostgroup INT NOT NULL CHECK (reader_hostgroupwriter_hostgroup AND reader_hostgroup>=0), check_type VARCHAR CHECK (LOWER(check_type) IN ('read_only','innodb_read_only','super_read_only')) NOT NULL DEFAULT 'read_only', comment VARCHAR NOT NULL DEFAULT '', UNIQUE (reader_hostgroup))

Here am making the check based on the hostgroup on the “INNODB_READ_ONLY” variable for each node in the hostgroup as below.

insert into mysql_replication_hostgroups values (10,11,'innodb_read_only','This is aurora cluster');

Now our setup of read-write split for Aurora is completely ready to accept traffic. We have made a read-write split with failover.

Failover handling With Proxysql.

I was just curious to note the fail-over and Switch-over with Aurora and proxysql to regroup the host group. So I induced a manual failover from the console as below

Before Fail-over below is the server status and host_group.

+--------------+--------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+ | hostgroup_id | hostname | port | gtid_port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | mem_pointer | +--------------+--------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+ | 10 | aurora-ap-south-1b.cbexxg6tsgtv.ap-south-1.rds.amazonaws.com | 3306 | 0 | 1 | 0 | 0 | 1000 | 1 | 0 | 0 | | 140686935296000 | | 11 | aurora.cbexxg6tsgtv.ap-south-1.rds.amazonaws.com | 3306 | 0 | 1 | 0 | 0 | 1000 | 0 | 0 | 0 | | 140686967069728 | | 11 | aurora-ap-south-1b.cbexxg6tsgtv.ap-south-1.rds.amazonaws.com | 3306 | 0 | 1 | 0 | 0 | 1000 | 1 | 0 | 0 | | 140686967652640 | +--------------+--------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+-----------------+

The proxysql Monitor makes checks within spans of few milliseconds as below. When it finds a consecutive fail of checks for three times, it makes the fail-over.

2018-12-18 10:27:02 MySQL_Monitor.cpp:657:monitor_read_only_thread(): [ERROR] Timeout on read_only check for aurora-ap-south-1b.cbexxg6tsgtv.ap-south-1.rds.amazonaws.com:3306 after 7ms. Unable to create a connection. If the server is overload, increase mysql-monitor_connect_timeout. Error: timeout on creating new connection: Can't connect to MySQL server on 'aurora-ap-south-1b.cbexxg6tsgtv.ap-south-1.rds.amazonaws.com' (115). 2018-12-18 10:27:03 MySQL_Monitor.cpp:657:monitor_read_only_thread(): [ERROR] Timeout on read_only check for aurora-ap-south-1b.cbexxg6tsgtv.ap-south-1.rds.amazonaws.com:3306 after 2ms. Unable to create a connection. If the server is overload, increase mysql-monitor_connect_timeout. Error: timeout on creating new connection: Can't connect to MySQL server on 'aurora-ap-south-1b.cbexxg6tsgtv.ap-south-1.rds.amazonaws.com' (115). 2018-12-18 10:27:03 MySQL_Monitor.cpp:803:monitor_read_only_thread(): [ERROR] Server aurora-ap-south-1b.cbexxg6tsgtv.ap-south-1.rds.amazonaws.com:3306 missed 3 read_only checks. Assuming read_only=1

And next, it proceeds to remove the server from the writer group ‘10’ as below,

2018-12-18 10:27:03 MySQL_HostGroups_Manager.cpp:1107:commit(): [WARNING] Removed server at address 140686935296000, hostgroup 10, address aurora-ap-south-1b.cbexxg6tsgtv.ap-south-1.rds.amazonaws.com port 3306. Setting status OFFLINE HARD and immediately dropping all free connections. Used connections will be dropped when trying to use them

You can see a graceful shifting of servers across the host groups 10 as below.

+-----------+--------------------------------------------------------------+----------+--------------+---------- +----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+- -----------+ | hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | MaxConnUsed | Queries | Queries_GTID_sync | Bytes_data_sent | Bytes_data_recv | Latency_us | +-----------+--------------------------------------------------------------+----------+--------------+---------- +----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+- -----------+ | 10 | aurora-ap-south-1b.cbexxg6tsgtv.ap-south-1.rds.amazonaws.com | 3306 | OFFLINE_HARD | 0 | 0 | 7 | 0 | 7 | 256100 | 0 | 15091179 | 0 | 1157 | | 10 | aurora.cbexxg6tsgtv.ap-south-1.rds.amazonaws.com | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1695 | | 11 | aurora-ap-south-1b.cbexxg6tsgtv.ap-south-1.rds.amazonaws.com | 3306 | ONLINE | 0 | 3 | 22 | 0 | 8 | 553552 | 0 | 17702113 | 1082107502 | 1157 | +----------+--------+---------+-------------+---------+-------------------+-----------+-----+-------------------- Advantages of ProxySQL with Aurora.
  • On the wire query caching, With TTL
  • Connection Multiplexing making efficient connection usage.
  • Reduced CPU usage.
  • Automated failover with Aurora end-points and query routing.

We are exploring more, See you soon with my new exciting upcoming blogs

Thanks for your continued support and Time !!

Image Courtesy:  Jouni Rajala on Unsplash

MySQL in 2018: What’s in 8.0 and Other Observations

With most, if not all of 2018 behind us (depending on when you are reading this post), there is no doubt that it was a fantastic year for open-source SQL databases.

PostgreSQL 11 and MySQL 8 were both released, providing both communities with plenty to 'talk about'. Truth be told, both vendors have introduced many significant changes and additions in their respective releases and deserve their praise and accolades.

I normally guest post about the former here on the Severalnines blog (Many thanks to a great organization!) but I also have an interest in the latter. With many blog posts on my own website (link in my bio section), mostly targeting MySQL version 5.7, it (MySQL) is always in my peripherals.

So what does MySQL 8 have that version 5.7 does not have? What are the improvements? Well, there are many. In fact, too many to cover in just one blog post.

I recently upgraded to version 8 in my current Linux learning/development environment, so I thought to try my hand at pointing some of them out.

I cannot guarantee you an in-depth discussion on your 'favorite' new feature(s). On the other hand, I will visit those that have caught my attention either via a personal interest or by way of the many terrific blog posts published throughout the year on version 8.

MySQL is getting better and better...Terrific improvements in version 8!

Roles

With Roles, DBA's can mitigate redundancy, where many users would share the same privilege or set of privileges.

Roles are a part of the SQL standard.

After creating a specific role with the desired/required privilege(s), you can then assign users that particular role via the GRANT command or likewise, 'taketh away' with REVOKE.

Roles come with numerous benefits and to make life a bit easier, there are a couple of tables to help you keep track of them:

  • mysql.role_edges - Here you find those roles and the users they are assigned.

    mysql> DESC mysql.role_edges; +-------------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------+---------------+------+-----+---------+-------+ | FROM_HOST | char(60) | NO | PRI | | | | FROM_USER | char(32) | NO | PRI | | | | TO_HOST | char(60) | NO | PRI | | | | TO_USER | char(32) | NO | PRI | | | | WITH_ADMIN_OPTION | enum('N','Y') | NO | | N | | +-------------------+---------------+------+-----+---------+-------+ 5 rows in set (0.01 sec)
  • mysql.default_roles - Stores any default roles and those users assigned.

    mysql> DESC mysql.default_roles; +-------------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------+----------+------+-----+---------+-------+ | HOST | char(60) | NO | PRI | | | | USER | char(32) | NO | PRI | | | | DEFAULT_ROLE_HOST | char(60) | NO | PRI | % | | | DEFAULT_ROLE_USER | char(32) | NO | PRI | | | +-------------------+----------+------+-----+---------+-------+ 4 rows in set (0.00 sec)

The combination of both tables (not in the SQL JOIN sense) essentially provides a 'centralized location' where you can: know, monitor, and assess all of your implemented user-role privilege relationships and assignments.

Likely the simplest example role usage scenario would be:

You have several users who need 'read-only access' on a specific table, therefore, requiring at least the SELECT privilege. Instead of granting it (SELECT) individually to each user, you can establish (create) a role having that privilege, then assign that role to those users.

But, roles come with a small 'catch'. Once created and assigned to a user, the receiving user must have an active default role set, during authentication upon login.

While on the subject of roles and users, I feel it is important to mention the change implemented in MySQL 8 concerning the validate_password component, which is a variant of the validate_password plugin used in version 5.7 .

This component provides various distinct 'categories' of password checking: low, medium (default), and strong. Visit the validate_password component documentation for a full rundown on each levels' validation specifics.

NoSQL Mingling with SQL - The Document Store

This feature is one I am still learning about, despite a fleeting interest in MongoDB in early 2016. To date, my interest, study, and learning have been focused solely on 'SQL'. However, I am aware (through much reading on the web) that many are excited about this type of structuring (document-oriented) intertwined with 'relational SQL' now available in the MySQL 8 document store.

Below are many benefits available when using the document store. Be sure and mention your favorites I may have missed in the comments section:

  • The JSON data type has been supported since MySQL version 5.7.8 yet, version 8 introduced significant enhancements for working with JSON. New JSON specific functions along with 'shorthand' operators that can be used in place of multiple function calls - with equal results/output.
  • Perhaps one of the foremost benefits is you no longer need to implement and work with multiple database solutions since NoSQL, SQL, or a combination of the two are supported in the document store.
  • A "DevAPI", provides seamless workflow capabilities within a NoSQL data context (collections and documents). (Visit the official DevAPI user guide documentation for more information).
  • Powerful command-line sessions using Python, SQL, or Javascript as the 'shell' language.
  • ACID compliant.
  • Quickly explore and discover your data without defining a schema as you would in a relational model.
Common Table Expressions (CTE's or the WITH clause)

What else can you say about CTE's? These things are a game-changer! For starters, what exactly is a common table expression?

From Wikipedia:

"A common table expression, or CTE, (in SQL) is a temporary named result set, derived from a simple query and defined within the execution scope of a SELECT, INSERT, UPDATE, or DELETE statement."

I'll provide a simple example, demonstrating CTE's. However, their full power is not harnessed in this section, as there are many more complex use-case examples than these.

I have a simple name table with this description and data:

mysql> DESC name; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | f_name | varchar(20) | YES | | NULL | | | l_name | varchar(20) | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM name; +--------+------------+ | f_name | l_name | +--------+------------+ | Jim | Dandy | | Johhny | Applesauce | | Ashley | Zerro | | Ashton | Zerra | | Ashmon | Zerro | +--------+------------+ 5 rows in set (0.00 sec)

Let's find out how many last names start with 'Z':

mysql> SELECT * -> FROM name -> WHERE l_name LIKE 'Z%'; +--------+--------+ | f_name | l_name | +--------+--------+ | Ashley | Zerro | | Ashton | Zerra | | Ashmon | Zerro | +--------+--------+ 3 rows in set (0.00 sec)

Easy enough.

However, using the WITH clause, you can 'access' this same query results set (which can be thought of as a derived table) and refer to it later on within the same statement - or 'scope':

WITH last_Z AS ( SELECT * FROM name WHERE l_name LIKE 'Z%') SELECT * FROM last_Z; +--------+--------+ | f_name | l_name | +--------+--------+ | Ashley | Zerro | | Ashton | Zerra | | Ashmon | Zerro | +--------+--------+ 3 rows in set (0.00 sec)

I basically assign a name to the query, wrapping it in parenthesis. Then just select the data I want from what is now the last_Z CTE.

The last_Z CTE provides a complete result set, so you can filter it even further within the same statement:

WITH last_Z AS ( SELECT * FROM name WHERE l_name LIKE 'Z%') SELECT f_name, l_name FROM last_Z WHERE l_name LIKE '%a'; +--------+--------+ | f_name | l_name | +--------+--------+ | Ashton | Zerra | +--------+--------+ 1 row in set (0.00 sec)

A couple of the more powerful features are 'chaining' multiple CTE's together and referencing to other CTE's within CTE's.

Here is an example to give you an idea (although not so much useful):

WITH last_Z AS ( SELECT * FROM name WHERE l_name LIKE 'Z%'), best_friend AS ( SELECT f_name, l_name FROM last_Z WHERE l_name LIKE '%a') SELECT * from best_friend; +--------+--------+ | f_name | l_name | +--------+--------+ | Ashton | Zerra | +--------+--------+ 1 row in set (0.00 sec)

In the above query, you can see where I separated the last_Z CTE from the best_friend CTE with a comma then wrapped that query in parenthesis after the AS keyword.

Notice I am then able to refer to (and use) the last_Z CTE to essentially define the best_friend CTE.

Here are a few reasons why CTE's are such a significant improvement in version 8:

  • Other SQL vendors have supported CTE's (many since earlier versions within their individual ecosystem's) and now MySQL 8, has closed the gap in this area.
  • A standard SQL inclusion.
  • In some cases (where appropriate), CTE's are a better option than Temporary Tables, Views, Derived Tables (or Inline Views), and some subqueries.
  • CTE's can provide an 'on-the-fly' calculations results set you can query against.
  • A CTE can reference itself - known as a recursive CTE (not demonstrated here).
  • CTE's can name and use other CTE's
ClusterControl Single Console for Your Entire Database Infrastructure Find out what else is new in ClusterControl Install ClusterControl for FREE Window Functions

Analytic queries are now possible in MySQL 8. As Window functions are not my strong suit, I am focused on a more in-depth study and better understanding of them, on a whole, moving forward. These next example(s) are mostly elementary according to my understanding. Suggestions, advice, and best practices are welcome from readers.

I have this VIEW that provides a fictitious pipe data result set (something I somewhat understand):

mysql> SELECT * FROM pipe_vw; +---------+-------------+-----------+-------+-------------+------------+----------------+ | pipe_id | pipe_name | joint_num | heat | pipe_length | has_degree | wall_thickness | +---------+-------------+-----------+-------+-------------+------------+----------------+ | 181 | Joint-278 | 39393A | 9111 | 17.40 | 1 | 0.393 | | 182 | Joint-8819 | 19393Y | 9011 | 16.60 | 0 | 0.427 | | 183 | Joint-9844 | 39393V | 8171 | 10.40 | 0 | 0.393 | | 184 | Joint-2528 | 34493U | 9100 | 11.50 | 1 | 0.427 | | 185 | Joint-889 | 18393z | 9159 | 13.00 | 0 | 0.893 | | 186 | Joint-98434 | 19293Q | 8174 | 9.13 | 0 | 0.893 | | 187 | Joint-78344 | 17QTT | 179 | 44.40 | 1 | 0.893 | | 188 | Joint-171C | 34493U | 17122 | 9.45 | 1 | 0.893 | | 189 | Joint-68444 | 17297Q | 6114 | 11.34 | 0 | 0.893 | | 190 | Joint-4841R | 19395Q | 5144 | 25.55 | 0 | 0.115 | | 191 | Joint-1224C | 34493U | 8575B | 15.22 | 1 | 0.893 | | 192 | Joint-2138 | 34493C | 91 | 13.55 | 1 | 0.893 | | 193 | Joint-122B | 34493U | 9100B | 7.78 | 1 | 0.893 | +---------+-------------+-----------+-------+-------------+------------+----------------+ 13 rows in set (0.00 sec)

Imagine, I need the pipe asset records presented in some sort of row ranking depending on the length of each individual pipe. (E.g., The longest length is 'labeled' the number 1 position, the second longest length is 'labeled' position 2, etc...)

Based on the RANK() Window Function description in the documentation:

"Returns the rank of the current row within its partition, with gaps. Peers are considered ties and receive the same rank. This function does not assign consecutive ranks to peer groups if groups of size greater than one exist; the result is noncontiguous rank numbers."

It looks to be a well-suited for this requirement.

mysql> SELECT pipe_name, pipe_length, -> RANK() OVER(ORDER BY pipe_length DESC) AS long_to_short -> FROM pipe_vw; +-------------+-------------+---------------+ | pipe_name | pipe_length | long_to_short | +-------------+-------------+---------------+ | Joint-78344 | 44.40 | 1 | | Joint-4841R | 25.55 | 2 | | Joint-278 | 17.40 | 3 | | Joint-8819 | 16.60 | 4 | | Joint-1224C | 15.22 | 5 | | Joint-2138 | 13.55 | 6 | | Joint-889 | 13.00 | 7 | | Joint-2528 | 11.50 | 8 | | Joint-68444 | 11.34 | 9 | | Joint-9844 | 10.40 | 10 | | Joint-171C | 9.45 | 11 | | Joint-98434 | 9.13 | 12 | | Joint-122B | 7.78 | 13 | +-------------+-------------+---------------+ 13 rows in set (0.01 sec)

In the next scenario, I want to build even further on the previous example by ranking the records of longest to shortest lengths, but, per each individual group of the distinct wall_thickness values.

Perhaps the below query and results will explain better where my prose may have not:

mysql> SELECT pipe_name, pipe_length, wall_thickness, -> RANK() OVER(PARTITION BY wall_thickness ORDER BY pipe_length DESC) AS long_to_short -> FROM pipe_vw; +-------------+-------------+----------------+---------------+ | pipe_name | pipe_length | wall_thickness | long_to_short | +-------------+-------------+----------------+---------------+ | Joint-4841R | 25.55 | 0.115 | 1 | | Joint-278 | 17.40 | 0.393 | 1 | | Joint-9844 | 10.40 | 0.393 | 2 | | Joint-8819 | 16.60 | 0.427 | 1 | | Joint-2528 | 11.50 | 0.427 | 2 | | Joint-78344 | 44.40 | 0.893 | 1 | | Joint-1224C | 15.22 | 0.893 | 2 | | Joint-2138 | 13.55 | 0.893 | 3 | | Joint-889 | 13.00 | 0.893 | 4 | | Joint-68444 | 11.34 | 0.893 | 5 | | Joint-171C | 9.45 | 0.893 | 6 | | Joint-98434 | 9.13 | 0.893 | 7 | | Joint-122B | 7.78 | 0.893 | 8 | +-------------+-------------+----------------+---------------+ 13 rows in set (0.00 sec)

This query uses the PARTITION BY clause on the wall_thickness column because we want the ranking (that ORDER BY pipe_length DESC provides) however, we need it in the context of the individual wall_thickness groups.

Each long_to_short column ranking resets back to 1 as you encounter (or change) to a different wall_thickness column value.

Let's concentrate on the results of one single group.

Targeting the records with wall_thickness values 0.893, the row with pipe_length 44.40 has a corresponding long_to_short 'ranking' of 1 (it's the longest), while the row with pipe_length 7.78 has a corresponding long_to_short 'ranking' of 8 (the shortest) all within that specific group (0.893) of wall_thickness values.

Window functions are quite powerful and their entire scope and breadth could not possibly be covered in one section alone. Be sure and visit the Window Functions supported in MySQL 8 documentation for more information on those currently available.

Improved Spatial Support and Capabilities

This is a tremendous set of features included in MySQL 8. Previous versions’ support, or lack thereof, simply could not compare to other vendor implementation(s) (think PostGIS for PostgreSQL).

For the past 10 plus years, I have worked in the field as a Pipeline Surveyor, collecting GPS and asset data, so this group of changes definitely catches my attention.

Spatial data expertise is a comprehensive subject in its own right and be assured, I am far from an expert on it. However, I hope to summarize the significant changes between versions 5.7 and 8 and convey them in an clear and concise manner.

Let's familiarize ourselves with 2 key terms (and concepts) for the purposes of this section.

  1. Spatial Reference System or SRS - Here is a partial definition from Wikipedia:

    "A spatial reference system (SRS) or coordinate reference system (CRS) is a coordinate-based local, regional or global system used to locate geographical entities. A spatial reference system defines a specific map projection, as well as transformations between different spatial reference systems."

  2. Spatial Reference System Identifier or SRID - Also, Wikipedia has SRID's defined as such:

    "A Spatial Reference System Identifier (SRID) is a unique value used to unambiguously identify projected, unprojected, and local spatial coordinate system definitions. These coordinate systems form the heart of all GIS applications."

MySQL supports many spatial data types. One of the more common ones is a POINT. If you use your GPS to navigate to your favorite restaurant, then that location is a POINT on a map.

MySQL 5.7 treats pretty much every 'spatial object' as having an SRID of 0, which is significant for computations. Those calculations are computed in a Cartesian type of coordinate system. However, we all know that our globe is a sphere and far from flat. Therefore, in version 8, you have the ability to consider it as either flat or spherical in computations.

Back to those two terms, we defined previously.

Even though 0 is the default SRID in MySQL version 8, many (approximately 5,000+) other SRID's are supported.

But why is that important?

This fantastic explanation via the blog post, Spatial Reference Systems in MySQL 8.0, sums it up nicely:

"By default, if we don’t specify an SRID, MySQL will create geometries in SRID 0. SRID 0 is MySQL’s notion of an abstract, unitless, infinite, Catesian plane. While all other SRSs refer to some surface and defines units for the axes, SRID 0 does not."

Essentially, when performing calculations with SRID's other than SRID 0, then the shape of our Earth comes into play, is considered, and affects those calculations. This is crucial for any meaningful/accurate computations. For an in-depth rundown and better extrapolation, see this blog post covering geography in MySQL 8.

I also highly recommend the MySQL Server Team blog post, Geographic Spatial Reference Systems in MySQL 8.0, for clarity on SRS's. Do make sure and give it a read!

Finally, for spatial data upgrade concerns from version 5.7 to 8, visit some of the incompatible changes listed here for more information.

Other Notable Observations

Below are other release enhancements that I must acknowledge, although they are not covered in-depth in this blog post:

  • utf8mb4 is now the default character set (previously latin1) - Better support for those must have emojis in addition to some languages...
  • Transactional Data Dictionary - MySQL metadata is now housed in InnoDB tables.
  • Invisible Indexes - Set the visibility of an index for the optimizer, ultimately determining if adding or removing it (the index), is a good or bad thing. Adding an index to an existing large table can be 'expensive' in terms of locking and resources.
  • Descending Indexes - Better performance on indexed values that are stored in descending order.
  • Instant Add Column - For schema changes, specify ALGORITHM=INSTANT in ALTER TABLE statements and (if feasible for the operation) avoid metadata locks. ( For more information, see this great post by the MySQL Server Team, and the ALTER TABLE section from the official docs.)
Bonus Section: Something I Had Hoped to See... Related resources  ClusterControl for MySQL  Become a MySQL DBA blog series - Common operations - Replication Topology Changes  Become a MySQL DBA blog series - Database upgrades

Check constraints have not made their way into the MySQL product yet.

As with previous MySQL versions, check constraint syntax is allowed in your CREATE TABLE commands but it is ignored. To my knowledge, most other SQL vendors support check constraints. Come join the party MySQL!

MySQL has significantly 'stepped up' its offering in version 8. Supporting robust spatial capabilities, convenient user management role options, 'hybrid' SQL/NoSQL data solutions, and analytical functions among the numerous additional improvements, is truly remarkable.

In my opinion, with version 8, MySQL continues to provide a solid option in the ever-growing, competitive open-source SQL ecosystem, full of relevant and feature-rich solutions.

Thank you for reading.

Tags:  MySQL database trends

MariaDB 10.2.20 and MariaDB Connector/C 3.0.8 now available

The MariaDB Foundation is pleased to announce the availability of MariaDB 10.2.20, the latest release in the MariaDB 10.2 series, as well as MariaDB Connector/C 3.0.8. Both are stable releases. See the release notes and changelogs for details. Download MariaDB 10.2.20 Release Notes Changelog What is MariaDB 10.2? MariaDB APT and YUM Repository Configuration Generator […]

The post MariaDB 10.2.20 and MariaDB Connector/C 3.0.8 now available appeared first on MariaDB.org.

Revisiting my 2018 Database Wishlist

It is December and 2018 is going to end. In January, when it just started, I wrote my 2018 Database Wishlist. Probably next January I’ll write another list. But first, it makes sense to review the last one. If some of my wishes actually happen, I really should know that – I don’t want to miss something nice, or forget topics that I considered interesting less than one year ago. Well, let’ s do some copy/paste and some googling…

More research on Learned Indexes

I’m not sure if more research actually happened – I hope so, and I hope that we’ll see its results at some point. At least, it seems that the topic was not forgotten. It was mentioned at least at Artificial Intelligence conference in May, and at Stanford Seminar in October.

It’s worth noting that Wikipedia still doesn’t have a page for Learned Index.

Progress on using ML for database tuning

The Overtune website didn’t publish anything new – it just removed some previously available information. It’s now possible to register to become a tester for a beta version, so it is reasonable to think that there has been some progress. The repository is actually active. No new public articles, so bad. I’ll definitely stay tuned.

More research on stored functions transformation

I can’t find anything newer than the original paper, except for a post from Microsoft Research. I found no evidence that anyone not working at Microsoft considers this research interesting.

Galera 4

MariaDB 10.4 plans mention that Galera 4 will be included. But this could be just another optimistic hypothesis, so basically… I still see nothing new.

Transactional DDL in the MySQL ecosystem

MDEV-4259 – transactional DDL is still open, no fix version was set. They indeed dedicated resources to MDEV-11424 – Instant ALTER TABLE of failure-free record format changes.

Oracle doesn’t say much about what will be in future MySQL versions. However, someone from Oracle said that atomic ALTER TABLE is a foundation for transactional DDL, which could mean that they’re closer to that than MariaDB. So, let’s hope we’ll see this feature in the next version – but there was no claim from them about that.

Engines, engines, engines

The storage engines I mentioned are still available for MySQL and MariaDB, or come with Percona Server. Oracle still didn’t kill MyISAM. However, no, I didn’t see any big change in SPIDER.

More progress on Tarantool and CockroachDB

Apparently, Tarantool 2 (still not stable) fixed a lot of bugs and improved its SQL support. This includes removing ON CONFLICT REPLACE for UNIQUE indexes, that is also problematic for MySQL.

Cockroach actually added a lot of features. Amongst other things, I want to report the cost-based optimizer and the CDC. IMPORT command allows to import dumps from MySQL and PostgreSQL, as well as CockroachDB itself and CSV files.

Final thoughts

Some things simply didn’t happen.

Learned index structures and machine learning to tune database performance apparently weren’t forgotten, so hopefully we’ll see something interesting in the future.

Tarantool and CockroachDB show interesting enhancements. MySQL third-party storage engines didn’t introduce anything fancy, but keep on doing a good job.

Federico

How to Get Details About MyRocks Deadlocks in MariaDB and Percona Server

In my previous post on ERROR 1213 I noted that Percona Server does not support the SHOW ENGINE ROCKSDB TRANSACTION STATUS statement to get deadlock details in "text" form. I've got some clarifications in my related feature request, PS-5114. So I decided to write this followup post and show what is the way to get deadlock details for the ROCKSDB tables in current versions of MariaDB and Percona Server.

First of all, I'd like to check MariaDB's implementation of MyRocks. For this I'll re-create deadlock scenario from that my post with MariaDB 10.3.12 I have at hand. We should start with installing ROCKSDB plugin according to this KB article: openxs@ao756:~/dbs/maria10.3$ bin/mysql --no-defaults --socket=/tmp/mariadb.sock -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.12-MariaDB Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [test]> install soname 'ha_rocksdb';
Query OK, 0 rows affected (36,338 sec)

MariaDB [test]> show engines;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                          | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| ROCKSDB            | YES     | RocksDB storage engine                                                           | YES          | YES  | YES        |
...
| CONNECT            | YES     | Management of External Data (SQL/NOSQL/MED), including many file formats         | NO           | NO   | NO         |
| Aria               | YES     | Crash-safe tables with MyISAM heritage                                           | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                               | NO           | NO   | NO         |
| SEQUENCE           | YES     | Generated tables filled with sequential values                                   | YES          | NO   | YES        |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
10 rows in set (8,753 sec)

MariaDB [test]> show plugins;
+-------------------------------+----------+--------------------+---------------+---------+
| Name                          | Status   | Type               | Library       | License |
+-------------------------------+----------+--------------------+---------------+---------+
| binlog                        | ACTIVE   | STORAGE ENGINE     | NULL          | GPL     |
| mysql_native_password         | ACTIVE   | AUTHENTICATION     | NULL          | GPL     |
| mysql_old_password            | ACTIVE   | AUTHENTICATION     | NULL          | GPL     |
| wsrep                         | ACTIVE   | STORAGE ENGINE     | NULL          | GPL     |
...
| CONNECT                       | ACTIVE   | STORAGE ENGINE     | ha_connect.so | GPL     |
| ROCKSDB                       | ACTIVE   | STORAGE ENGINE     | ha_rocksdb.so | GPL     |
...
| ROCKSDB_LOCKS                 | ACTIVE   | INFORMATION SCHEMA | ha_rocksdb.so | GPL     |
| ROCKSDB_TRX                   | ACTIVE   | INFORMATION SCHEMA | ha_rocksdb.so | GPL     |
| ROCKSDB_DEADLOCK              | ACTIVE   | INFORMATION SCHEMA | ha_rocksdb.so | GPL     |
+-------------------------------+----------+--------------------+---------------+---------+
68 rows in set (2,451 sec) Note that in MariaDB just one simple INSTALL SONAME ... statement is enough to get ROCKSDB with all related plugins loaded. Do not mind time to execute statements - I am running them on a netbook that is busy compiling Percona Server 8.0.13 from GitHub concurrently, to post something about it later :)

Now, let me re-create the same deadlock scenario:

MariaDB [test]> create table t1(id int, c1 int, primary key(id)) engine=rocksdb;
Query OK, 0 rows affected (4,163 sec)

MariaDB [test]> insert into t1 values (1,1), (2,2);
Query OK, 2 rows affected (0,641 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test]> set global rocksdb_lock_wait_timeout=50;
Query OK, 0 rows affected (0,644 sec)

MariaDB [test]> set global rocksdb_deadlock_detect=ON;
Query OK, 0 rows affected (0,037 sec)

MariaDB [test]> show global variables like 'rocksdb%deadlock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| rocksdb_deadlock_detect       | ON    |
| rocksdb_deadlock_detect_depth | 50    |
| rocksdb_max_latest_deadlocks  | 5     |
+-------------------------------+-------+
3 rows in set (0,022 sec)We need two sessions. In the first one:
MariaDB [test]> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|              11 |
+-----------------+
1 row in set (0,117 sec)

MariaDB [test]> start transaction;
Query OK, 0 rows affected (0,000 sec)

MariaDB [test]> select * from t1 where id = 1 for update;
+----+------+
| id | c1   |
+----+------+
|  1 |    1 |
+----+------+
1 row in set (0,081 sec)
In the second:
MariaDB [test]> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|              12 |
+-----------------+
1 row in set (0,000 sec)

MariaDB [test]> start transaction;
Query OK, 0 rows affected (0,000 sec)

MariaDB [test]> select * from t1 where id = 2 for update;
+----+------+
| id | c1   |
+----+------+
|  2 |    2 |
+----+------+
1 row in set (0,001 sec)Back in the first:

MariaDB [test]> select * from t1 where id=2 for update; It hangs waiting for incompatible lock. In the second:

MariaDB [test]> select * from t1 where id=1 for update;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transactionNow, can we get any details about the deadlock that just happened using upstream SHOW ENGINE statement? Let's try:
MariaDB [test]> SHOW ENGINE ROCKSDB TRANSACTION STATUS\G
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'TRANSACTION STATUS' at line 1Does not work, same as in Percona Server 5.7.x. Here is a related MariaDB task: MDEV-13859 - "Add SHOW ENGINE ROCKSDB TRANSACTION STATUS or its equivalent?". Still "Open" and without any target version set, not even 10.4.

The idea behind NOT supporting this statement by Percona, according to comments I've got in  PS-5114, is to rely on tables in the information_schema. That's what we have in the related tables, rocksdb_locks, rocksdb_trx and rocksdb_deadlock after deadlock above was detected:
MariaDB [test]> select * from information_schema.rocksdb_deadlock;
+-------------+------------+----------------+---------+------------------+-----------+------------+------------+-------------+
| DEADLOCK_ID | TIMESTAMP  | TRANSACTION_ID | CF_NAME | WAITING_KEY      | LOCK_TYPE | INDEX_NAME | TABLE_NAME | ROLLED_BACK |
+-------------+------------+----------------+---------+------------------+-----------+------------+------------+-------------+
|           0 | 1545481878 |              6 | default | 0000010080000002 | EXCLUSIVE | PRIMARY    | test.t1    |           0 |
|           0 | 1545481878 |              7 | default | 0000010080000001 | EXCLUSIVE | PRIMARY    | test.t1    |           1 |
+-------------+------------+----------------+---------+------------------+-----------+------------+------------+-------------+
2 rows in set (0,078 sec)

MariaDB [test]> select * from information_schema.rocksdb_trx;
+----------------+---------+------+-------------+------------+-------------+-------------+--------------------------+----------------+--------------+-----------+------------------------+----------------------+-----------+-------+
| TRANSACTION_ID | STATE   | NAME | WRITE_COUNT | LOCK_COUNT | TIMEOUT_SEC | WAITING_KEY | WAITING_COLUMN_FAMILY_ID | IS_REPLICATION | SKIP_TRX_API | READ_ONLY | HAS_DEADLOCK_DETECTION | NUM_ONGOING_BULKLOAD | THREAD_ID | QUERY |
+----------------+---------+------+-------------+------------+-------------+-------------+--------------------------+----------------+--------------+-----------+------------------------+----------------------+-----------+-------+
|              6 | STARTED |      |           0 |          2 |          50 |             |                        0 |              0 |            0 |         0 |                      1 |                    0 |        11 |       |
+----------------+---------+------+-------------+------------+-------------+-------------+--------------------------+----------------+--------------+-----------+------------------------+----------------------+-----------+-------+
1 row in set (0,001 sec)

MariaDB [test]> select * from information_schema.rocksdb_locks;
+------------------+----------------+------------------+------+
| COLUMN_FAMILY_ID | TRANSACTION_ID | KEY              | MODE |
+------------------+----------------+------------------+------+
|                0 |              6 | 0000010080000002 | X    |
|                0 |              6 | 0000010080000001 | X    |
+------------------+----------------+------------------+------+
2 rows in set (0,025 sec) I was not able to find any really good documentation about these tables (I checked here, there and more), especially rocksdb_deadlock that is totally undocumented, so let me try to speculate and explain my ideas on how they are supposed to work and be used together. Information about up to rocksdb_max_latest_deadlocks is stored in the rocksdb_deadlock table, each deadlock is identified by deadlock_id and in case of MariaDB you can find out when it happened using the timestamp column that is a UNIX timestamp:
MariaDB [test]> select distinct deadlock_id, from_unixtime(timestamp) from information_schema.rocksdb_deadlock;+-------------+--------------------------+
| deadlock_id | from_unixtime(timestamp) |
+-------------+--------------------------+
|           0 | 2018-12-22 14:31:18      |
+-------------+--------------------------+
1 row in set (0,137 sec)
For each deadlock you have a row per lock wait for each transaction involved, identified by transaction_id. You can see for what key value (waited_key) in that index (index_name) of what table (table_name) the transaction was waited. Victim (transaction that was rolled back to prevent deadlock) of deadlock detection is identified by the value 1 in the rolled_back column. This is all the information that persists for any notable time, and I don't like it that much, as we can not see what lock(s) transactions had at the moment. We can guess conflicting lock based on what was the waiting_key, but I'd prefer InnoDB way of showing this clearly with all the details.

If you hurry up and query the rocksdb_trx table fast enough, you can get more details about those transaction(s) involved in deadlock that are NOT rolled back (and not yet committed). Join by the transaction_id column, obviously, to get the details up to current running query and processlist connection id (rocksdb_trx.thread_id column) involved. 

If you hurry up to query rocksdb_locks table also by the transaction_id of still active transaction, you can get a list of locks it holds and then guess which one was a blocking lock. If you are not fast enough and transaction is gone you have just to assume there was some blocking lock. One day gap locks may be added, and some lock would become not good enough guess.

I miss these rocks at Beaulieu-sur-Mer. MyRocks in all implementations but Facebook's, misses one useful way to get the details about deadlock(s) that happened in the past.
To summarize, while storing information about configurable number of last deadlocks in the table seems to be a good idea, in case of ROCKSDB in both Percona and MariaDB servers (as soon as all transactions involved in deadlock are completed one way or the other) we miss some details (like thread ids for sessions involved, exact locks that each transaction held etc) comparing to the text output provided by the original upstream statement (and SHOW ENGINE INNODB STATUS\G, surely). Even if we are lucky to query all tables in time, we still probably miss lock waits table (like innodb_lock_waits) and any built in way to store information in the error log about deadlocks that happened (and all locks involved). 

Note also lack of consistency in naming (rocksdb_locks, plural vs  rocksdb_deadlock, singular, in case of MariaDB), rocksdb_deadlock.lock_type with value EXCLUSIVE vs rocksdb_locks.mode with value X etc, and and very limited documentation available. In my opinion current state is unacceptable if we hope to see wide use of MyRocks by community users and DBAs outside of Facebook.

Announcing General Availability of Percona Server for MySQL 8.0

Percona has released Percona Server for MySQL 8.0 as Generally Available (GA). Our Percona Server for MySQL 8.0 software is the company’s free, enhanced, drop-in replacement for MySQL Community Edition. Percona Server for MySQL 8.0 includes all of the great features in MySQL Community Edition 8.0. It also includes enterprise-class features from Percona made available free and open source. Percona Server for MySQL is trusted by thousands of enterprises to meet their need for a mature, proven, cost-effective MySQL solution that delivers excellent performance and reliability.

Downloads are available on the Percona Website and in the Percona Software Repositories.

Features in Percona Server for MySQL 8.0

Percona Server for MySQL 8.0 includes all of the features available in MySQL 8.0 Community Edition in addition to enterprise-grade features developed by Percona for the community.

MySQL Community Edition 8.0 Features

Some of the highlights from MySQL 8.0 contained in Percona Server for MySQL 8.0 include:

  • MySQL Document Store—Combining NoSQL functionality within the X API along with JSON enhancements such as new operators and functions enables developers to use MySQL 8.0 for non-relational data storage without the need for a separate NoSQL database.
  • Stronger SQL—With the addition of Window Functions, Common Table Expressions, Unicode safe Regular Expressions, and other improvements MySQL 8.0 provide broader support for the range of SQL standard functionality.
  • Transactional Data Dictionary—Enables atomic and crash-safe DDL operations, enhancing reliability, and eliminating the need for metadata files.
  • Security—SQL Roles, SHA2 default authentication, fine-grained privileges, and other enhancements make MySQL 8.0 more secure and adaptable to your organization’s compliance needs.
  • Geospatial—New SRS aware spatial data types, spatial indexes, and spatial functions, enabling the use of MySQL 8.0 for complex GIS use-cases.
Percona Server for MySQL 8.0 Features

Building on the upstream MySQL 8.0 Community Edition, Percona Server for MySQL 8.0 brings many great features in this release, including the following:

  • Security and Compliance:
    • Audit Logging Plugin: Provides monitoring and logging of database activity to assist organizations in meeting their compliance objectives. This feature is comparable to MySQL Enterprise Auditing.
    • PAM-based Authentication Plugin: Assists enterprises in integrating Percona Server for MySQL with their single sign-on (SSO) and two-factor authentication (2FA) systems by integrating with standard PAM modules. This feature is comparable to MySQL Enterprise Authentication.
    • Enhanced Encryption: Improves upon Transparent Data Encryption (TDE) present in MySQL Community Edition. Enhanced encryption adds support for binary log encryption, temporary file encryption, encryption support for all InnoDB tablespace types and logs, encryption of the parallel doublewrite buffer, key rotation, and support for centralized key management using Hashicorp Vault. Please Note: Some of the encryption features are still considered experimental and are not yet suitable for production use. These features together are comparable to MySQL Enterprise TDE.
  • Performance and Scalability:
    • Threadpool: Supporting 10000+ connections, this feature provides significant performance benefits under heavy load. This feature is comparable to MySQL Enterprise Scalability.
    • InnoDB Engine Enhancements: Enables highly concurrent IO-bound workloads to see significant performance improvements through parallel doublewrite, multithreaded LRU flushers, and single page eviction. In a simple benchmark, we saw a 60% performance improvement in some workloads when comparing Percona Server for MySQL to MySQL Community Edition
    • MyRocks Storage Engine: Based on the RocksDB storage library, MyRocks brings MySQL into the 21st century by being optimized for modern hardware such as nVME SSDs. Utilizing strong compression, MyRocks reduces write-amplification and storage requirements on SSDs compared to InnoDB to lower TCO and increase ROI when working with large datasets. Improved throughput consistency compared to InnoDB enables scaling cloud resources for your databases more strategically.
  • Observability and Usability:
    • Improved Instrumentation: Percona Server for MySQL 8.0 offers more than double the available performance and stats counters compared to MySQL Community Edition, as well as support for gathering per-user and per-thread statistics, and extended slow query logging capabilities. Together with free tools like Percona Monitoring and Management these enhancements enable your DBAs to troubleshoot issues faster and effectively improve your application performance.
    • Reduced Backup Impact: Lighter weight Backup Locking reduces the impact to performance and availability of performing backups.  This feature makes your backups run faster and your applications perform better during long-running backups when used together with Percona XtraBackup 8.0.
Features Removed in Percona Server for MySQL 8.0

Some features were not ported forward from Percona Server for MySQL 5.7 to Percona Server for MySQL 8.0.  Features which are unused, have something comparable included upstream, or are no longer relevant in this major release have been removed. For more information see our documentation.

  • Slow Query Log Rotation and Expiration: Not widely used, can be accomplished using logrotate
  • CSV engine mode for standard-compliant quote and comma parsing
  • Expanded program option modifiers
  • The ALL_O_DIRECT InnoDB flush method: it is not compatible with the new redo logging implementation
  • XTRADB_RSEG table removed from INFORMATION_SCHEMA
  • InnoDB memory size information from SHOW ENGINE INNODB STATUS; the same information is available from Performance Schema memory summary tables
  • Query cache enhancements: The query cache is no longer present in MySQL 8.0
Features Being Deprecated in Percona Server for MySQL 8.0
  • TokuDB Storage Engine: TokuDB will be supported throughout the Percona Server for MySQL 8.0 release series, but will not be available in the next major release.  Percona encourages TokuDB users to explore the MyRocks Storage Engine which provides similar benefits for the majority of workloads and has better optimized support for modern hardware.
Additional Resources

Release Notes for Percona Server for MySQL 8.0.13-3 GA

Percona announces the GA release of Percona Server for MySQL 8.0.13-3 on December 21, 2018 (downloads are available here and from the Percona Software Repositories). This release merges changes of MySQL 8.0.13, including all the bug fixes in it. Percona Server for MySQL 8.0.13-3 is now the current GA release in the 8.0 series. All of Percona’s software is open-source and free.

Percona Server for MySQL 8.0 includes all the features available in MySQL 8.0 Community Edition in addition to enterprise-grade features developed by Percona. For a list of highlighted features from both MySQL 8.0 and Percona Server for MySQL 8.0, please see the GA release announcement.

Note: If you are upgrading from 5.7 to 8.0, please ensure that you read the upgrade guide and the document Changed in Percona Server for MySQL 8.0.

Features Removed in Percona Server for MySQL 8.0
  • Slow Query Log Rotation and Expiration: Not widely used, can be accomplished using logrotate
  • CSV engine mode for standard-compliant quote and comma parsing
  • Expanded program option modifiers
  • The ALL_O_DIRECT InnoDB flush method: it is not compatible with the new redo logging implementation
  • XTRADB_RSEG table from INFORMATION_SCHEMA
  • InnoDB memory size information from SHOW ENGINE INNODB STATUS; the same information is available from Performance Schema memory summary tables
  • Query cache enhancements: The query cache is no longer present in MySQL 8.0
Features Deprecated in Percona Server for MySQL 8.0
  • TokuDB Storage Engine: the Percona Server for MySQL 8.0 release series supports TokuDB. We are deprecating TokuDB support in the next major release. Percona encourages TokuDB users to explore the MyRocks Storage Engine which provides similar benefits for the majority of workloads and has better-optimized support for modern hardware.
Issues Resolved in Percona Server for MySQL 8.0.13-3 Improvements
  • #5014: Update Percona Backup Locks feature to use the new BACKUP_ADMIN privilege in MySQL 8.0
  • #4805: Re-Implemented Compressed Columns with Dictionaries feature in PS 8.0
  • #4790: Improved accuracy of User Statistics feature
Bugs Fixed Since 8.0.12-2rc1
  • Fixed a crash in mysqldump in the --innodb-optimize-keys functionality #4972
  • Fixed a crash that can occur when system tables are locked by the user due to a lock_wait_timeout #5134
  • Fixed a crash that can occur when system tables are locked by the user from a SELECT FOR UPDATE statement #5027
  • Fixed a bug that caused innodb_buffer_pool_size to be uninitialized after a restart if it was set using SET PERSIST#5069
  • Fixed a crash in TokuDB that can occur when a temporary table experiences an autoincrement rollover #5056
  • Fixed a bug where marking an index as invisible would cause a table rebuild in TokuDB and also in MyRocks #5031
  • Fixed a bug where audit logs could get corrupted if the audit_log_rotations was changed during runtime. #4950
  • Fixed a bug where LOCK INSTANCE FOR BACKUP and STOP SLAVE SQL_THREAD would cause replication to be blocked and unable to be restarted. #4758 (Upstream #93649)

Other Bugs Fixed:

#5155#5139#5057#5049#4999#4971#4943#4918#4917#4898, and #4744.

Known Issues in Percona Server for MySQL 8.0.13-3

We have a few features and issues outstanding that should be resolved in the next release.

Pending Feature Re-Implementations and Improvements
  • #4892: Re-Implement Expanded Fast Index Creation feature.
  • #5216: Re-Implement Utility User feature.
  • #5143: Identify Percona features which can make use of dynamic privileges instead of SUPER
Notable Issues in Features
  • #5148: Regression in Compressed Columns Feature when using innodb-force-recovery
  • #4996: Regression in User Statistics feature where TOTAL_CONNECTIONS field report incorrect data
  • #4933: Regression in Slow Query Logging Extensions feature where incorrect transaction id accounting can cause an assert during certain DDLs.
  • #5206: TokuDB: A crash can occur in TokuDB when using Native Partitioning and the optimizer has index_merge_union enabled. Workaround by using SET SESSION optimizer_switch="index_merge_union=off";
  • #5174: MyRocks: Attempting to use unsupported features against MyRocks can lead to a crash rather than an error.
  • #5024: MyRocks: Queries can return the wrong results on tables with no primary key, non-unique CHAR/VARCHAR rows, and UTF8MB4 charset.
  • #5045: MyRocks: Altering a column or table comment cause the table to be rebuilt

Find the release notes for Percona Server for MySQL 8.0.13-3 in our online documentation. Report bugs in the Jira bug tracker.

Have a nice MySQL Season Holidays

I wish you all the best for this end of 2018 and of course a nice start of 2019’s edition !

For the best possible start of 2019, I already invite you to these different events where I will have to honor to talk about MySQL:

Take some rest and see you soon again ! #MySQL8isGreat !

Database High Availability Comparison - MySQL / MariaDB Replication vs Oracle Data Guard

In the “State of the Open-Source DBMS Market, 2018”, Gartner predicts that by 2022, 70 percent of new in-house applications will be developed on an open-source database. And 50% of existing commercial databases will have converted. So, Oracle DBAs, get ready to start deploying and managing new open source databases - along with your legacy Oracle instances. Unless you’re already doing it.

So how does MySQL or MariaDB replication stack up against Oracle Data Guard? In this blog, we’ll compare the two from the standpoint of a high availability database solution.

What To Look For

A modern data replication architecture is built upon flexible designs that enable unidirectional and bidirectional data replication, as well as quick, automated failover to secondary databases in the event of unplanned service break. Failover should be also easy to execute and reliable so no committed transactions would be lost. Moreover switchover or failover should ideally be transparent to applications.

Data replication solutions have to be capable to copy data with very low latency to avoid processing bottlenecks and guarantee real-time access to data. Real-time copies could be deployed on a different database running on low-cost hardware.

When used for disaster recovery, the system must be validated to ensure application access to the secondary system with minimal service interruption. The ideal solution should allow regular testing of the disaster recovery process.

Main Topics of Comparison
  • Data availability and consistency
    • Gtid, scm
    • Mention Replication to multiple standby, async + sync models
    • Isolation of standby from production faults (e.g. delayed replication for mysql)
    • Avoid loss of data (sync replication)
  • Standby systems utilization
    • Usage of the standby
  • Failover, Switchover and automatic recovery
    • Database failover
    • Transparent application failover (TAF vs ProxySQL, MaxScale)
  • Security
  • Ease of use and management (unified management of pre-integrated components)
Data Availability and Consistency MySQL GTID

MySQL 5.5 replication was based on binary log events, where all a slave knew was the precise event and the exact position it just read from the master. Any single transaction from a master may have ended in various binary logs from different slaves, and the transaction would typically have different positions in these logs. It was a simple solution that came with limitations, topology changes could require an admin to stop replication on the instances involved. These changes could cause some other issues, e.g., a slave couldn’t be moved down the replication chain without a time-consuming rebuild. Fixing a broken replication link would require manually determining a new binary log file and position of the last transaction executed on the slave and resuming from there, or a total rebuild. We’ve all had to work around these limitations while dreaming about a global transaction identifier.

MySQL version 5.6 (and MariaDB version 10.0.2) introduced a mechanism to solve this problem. GTID (Global Transaction Identifier) provides better transactions mapping across nodes.

With GTID, slaves can see a unique transaction coming in from several masters and this can easily be mapped into the slave execution list if it needs to restart or resume replication. So, the advice is to always use GTID. Note that MySQL and MariaDB have different GTID implementations.

Oracle SCN

In 1992 with the release 7.3 Oracle introduced a solution to keep a synchronized copy of a database as standby, know as Data Guard from version 9i release 2. A Data Guard configuration consists of two main components, a single primary database, and a standby database (up to 30). Changes on the primary database are passed through the standby database, and these changes are applied to the standby database to keep it synchronized.

Oracle Data Guard is initially created from a backup of the primary database. Data Guard automatically synchronizes the primary database and all standby databases by transmitting primary database redo - the information used by every Oracle Database to protect transactions - and applying it to the standby database. Oracle uses an internal mechanism called SCN (System Change Number). The system change number (SCN) is Oracle's clock, every time we commit, the clock increments. The SCN marks a consistent point in time in the database which is a checkpoint that is the act of writing dirty blocks (modified blocks from the buffer cache to disk). We can compare it to GTID in MySQL.

Data Guard transport services handle all aspects of transmitting redo from a primary to a standby database. As users commit transactions on the primary, redo records are generated and written to a local online log file. Data Guard transport services simultaneously transmit the same redo directly from the primary database log buffer (memory allocated within system global area) to the standby database(s) where it is written to a standby redo log file.

There are a few main differences between MySQL replication and Data Guard. Data Guard’s direct transmission from memory avoids disk I/O overhead on the primary database. It is different from how MySQL works - reading data from memory decreases I/O on a primary database.

Data Guard transmits only database redo. It is in stark contrast to storage remote-mirroring which must transmit every changed block in every file to maintain real-time synchronization.

Async + Sync Models

Oracle Data Guard offers three different models for the redo apply. Adaptive models dependent on available hardware, processes, and ultimately business needs.

  • Maximum Performance - default mode of operation, allowing a transaction to commit as soon as the redo data needed to recover that transaction is written to the local redo log on the master.
  • Maximum Protection - no data loss and the maximum level of protection. The redo data needed to improve each operation must be written to both the local online redo log on the master and standby redo log on at least one standby database before the transaction commits (Oracle recommends at least two standbys). The primary database will shut down if a fault blocks it from writing its redo stream to at least one synchronized standby database.
  • Maximum Availability - similar to Maximum Protection but the primary database will not shut down if a fault prevents it from writing its redo stream.

When it comes to choosing your MySQL replication setup, you have the choice between Asynchronous replication or Semi-Synchronous replication.

  • Asynchronous binlog apply is the default method for MySQL replication. The master writes events to its binary log and slaves request them when they are ready. There is no guarantee that any event will ever reach any slave.
  • Semi-synchronous commit on primary is delayed until master receives an acknowledgment from the semi-synchronous slave that data is received and written by the slave. Please note that semi-synchronous replication requires an additional plugin to be installed.
Standby Systems Utilization

MySQL is well known for its replication simplicity and flexibility. By default, you can read or even write to your standby/slave servers. Luckily, MySQL 5.6 and 5.7 brought many significant enhancements to Replication, including Global Transaction IDs, event checksums, multi-threaded slaves and crash-safe slaves/masters to make it even better. DBAs accustomed to MySQL replication reads and writes would expect a similar or even simpler solution from it's bigger brother, Oracle. Unfortunately not by default.

The standard physical standby implementation for Oracle is closed for any read-write operations. In fact, Oracle offers logical variation but it has many limitations, and it's not designed for HA. The solution to this problem is an additional paid feature called Active Data Guard, which you can use to read data from the standby while you apply redo logs.

Active Data Guard is a paid add-on solution to Oracle’s free Data Guard disaster recovery software available only for Oracle Database Enterprise Edition (highest cost license). It delivers read-only access, while continuously applying changes sent from the primary database. As an active standby database, it helps offload read queries, reporting and incremental backups from the primary database. The product’s architecture is designed to allow standby databases to be isolated from failures that may occur at the primary database.

An exciting feature of Oracle database 12c and something that Oracle DBA would miss is the data corruption validation. Oracle Data Guard corruption checks are performed to ensure that data is in exact alignment before data is copied to a standby database. This mechanism can also be used to restore data blocks on the primary directly from the standby database.

Failover, Switchover, and Automatic Recovery

To keep your replication setup stable and running, it is crucial for the system to be resilient to failures. Failures are caused by either software bugs, configuration problems or hardware issues, and can happen at any time. In case a server goes down, you need an alarm notification about the degraded setup. Failover (promotion of a slave to master) can be performed by the admin, who needs to decide which slave to promote.

The admin needs information about the failure, the synchronization status in case any data will be lost, and finally, steps to perform the action. Ideally, all should be automated and visible from a single console.

There are two main approaches to MySQL failover, automatic and manual. Both options have its fans, we describe the concepts in another article.

With the GTID, the manual failover becomes much easier. It consists of steps like:

  • Stop the receiver module (STOP SLAVE IO_THREAD)
  • Switch master (CHANGE MASTER TO <new_master_def>)
  • Start the receiver module (START SLAVE IO_THREAD)

Oracle Data Guard comes with a dedicated failover/switchover solution - Data Guard Broker. The broker is a distributed management framework that automates and centralizes the creation, maintenance, and monitoring of Oracle Data Guard configurations. With the access to the DG broker tool, you can perform configuration changes, switchovers, failovers and even dry test of your high availability setup. The two main actions are:

  • The command SWITCHOVER TO < standby database name > is used to perform the switchover operation. After the successful switchover operation, database instances switch places and replication continues. It’s not possible to switchover when standby is not responding or it’s down.
  • The common FAILOVER TO <standby database name> is used to perform the failover. After the failover operation, the previous primary server requires recreation but the new primary can take the database workload.

Speaking about failover, we need to consider how seamless your application failover can be. In the event of a planned/unplanned outage, how efficiently can user sessions be directed to a secondary site, with minimal business interruption.

The standard approach for MySQL would be to use one of the available Load Balancers. Starting from HAProxy which is widely used for HTTP or TCP/IP failover to database aware Maxscale or ProxySQL.

In Oracle, this problem is addressed by TAF (Transparent Application Failover). Once switchover or failover occurs, the application is automatically directed to the new primary. TAF enables the application to automatically and transparently reconnect to a new database, if the database instance to which the connection is made fails.

ClusterControl Single Console for Your Entire Database Infrastructure Find out what else is new in ClusterControl Install ClusterControl for FREE Security

Data security is a hot issue for many organizations these days. For those who need to implement standards like PCI DSS or HIPAA, database security is a must. The cross WAN environments might lead to concerns about data privacy and security especially as more businesses are having to comply with national and international regulations. MySQL binary logs used for replication may contain easy to read sensitive data. With the standard configuration, stealing data is a very easy process. MySQL supports SSL as a mechanism to encrypt traffic both between MySQL servers (replication) and between MySQL servers and clients. A typical way of implementing SSL encryption is to use self-signed certificates. Most of the time, it is not required to obtain an SSL certificate issued by the Certificate Authority. You can either use openssl to create certificates, example below:

$ openssl genrsa 2048 > ca-key.pem $ openssl req -new -x509 -nodes -days 3600 -key ca-key.pem > ca-cert.pem $ openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem > client-req.pem $ openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem $ openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem > client-req.pem $ openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem $ openssl rsa -in client-key.pem -out client-key.pem $ openssl rsa -in server-key.pem -out server-key.pem

Then modify replication with parameters for SSL.

….MASTER_SSL=1, MASTER_SSL_CA = '/etc/security/ca.pem', MASTER_SSL_CERT = '/etc/security/client-cert.pem', MASTER_SSL_KEY = '/etc/security/client-key.pem';

For more automated option, you can use ClusterControl to enable encryption and manage SSL keys.

In Oracle 12c, Data Guard redo transport can be integrated with a set of dedicated security features called Oracle Advanced Security (OAS). Advanced Security can be used to enable encryption and authentication services between the primary and standby systems. For example, enabling Advanced Encryption Standard (AES) encryption algorithm requires only a few parameter changes in sqlnet.ora file to make redo (similar to MySQL binlog) encrypted. No external certificate setup is required and it only requires a restart of the standby database. The modification in sqlnet.ora and wallet are simple as:

Create a wallet directory

mkdir /u01/app/wallet

Edit sqlnet.ora

ENCRYPTION_WALLET_LOCATION= (SOURCE= (METHOD=file) (METHOD_DATA= (DIRECTORY=/u01/app/wallet)))

Create a keystore

ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/wallet' identified by root ;

Open store

ADMINISTER KEY MANAGEMENT set KEYSTORE open identified by root ;

Create a master key

ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY root WITH BACKUP;

On standby

copy p12 and .sso files in the wallet directory and to update the sqlnet.ora file similar to the primary node.

Related resources  Comparing Replication Solutions from Oracle and MySQL  Comparing Oracle RAC HA Solution to Galera Cluster for MySQL or MariaDB  Migrating from Oracle to PostgreSQL - What You Should Know

For more information please follow Oracle's TDE white paper, you can learn from the whitepaper how to encrypt datafile and make wallet always open.

Ease of Use and Management

When you manage or deploy Oracle Data Guard configuration, you may find out that there are many steps and parameters to look for. To answer that, Oracle created DG Broker.

You can certainly create a Data Guard configuration without implementing the DG Broker but it can make your life much more comfortable. When it's implemented, the Broker’s command line utility - DGMGRL is probably the primary choice for the DBA. For those who prefer GUI, Cloud Control 13c has an option to access DG Broker via the web interface.

The tasks that Broker can help with are an automatic start of the managed recovery, one command for failover/switchover, monitoring of DG replication, configuration verification and many other.

DGMGRL> show configuration Configuration - orcl_s9s_config Protection Mode: MaxPerformance Members: s9sp - Primary database s9ss - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 12 seconds ago

MySQL does not offer a similar solution to Oracle DG Broker. However you can extend its functionality by using tools like Orchestrator, MHA and load balancers (ProxySQL, HAProxy or Maxscale). The solution to manage databases and load balancers is ClusterControl. The ClusterControl Enterprise Edition gives you will a full set of management and scaling features in addition to the deployment and monitoring functions offered as part of the free Community Edition.

Tags:  oracle data guard oracle data guard broker high availability MySQL MariaDB

MySQL Enterprise Monitor - The Best Way to Monitor Your MySQL Database Instances

MySQL Enterprise Monitor is the best way to monitor MySQL databases from a single to multiple instances on your network or in the cloud. Period. This, to me at least, is the core tool that comes with an Enterprise Subscription. You can use it to monitor the free Community Edition of the MySQL Server but you do need to purchase the subscription.

MEM monitors of MySQL instances (and their hosts), gives notification of potential issues or problems, and it will also provide advice on how to correct issues.

 

The MySQL Enterprise Monitor Agent monitors the MySQL server, including checking the server accessibility, configuration, obtains the server ID, and the environment to enable collecting more detailed information. In addition to the information accessible by accessing variable and configuration information within the server, other configuration parameters, such as the replication topology, are also collected from the server.

 

The collected data is sent to MySQL Enterprise Service Manager for analysis and presentation. The MySQL Enterprise Service Manager analyzes, stores and presents the data collected by the agent. And you can view all this information from a web browser with an easy to use and understand interface. And yes, there are demos https://www.mysql.com/products/enterprise/demo.html

 

Improve Your Queries

MEM does more than just watch the server. The MySQL Enterprise Monitor Proxy and Aggregator intercepts queries as they are transmitted from client applications to the monitored MySQL instance and transmits them to the MySQL Enterprise Service Manager for analysis by the Query Analyzer.

This data is from Performance Schema, rather than at the wire protocol to provide data about what the statements do to generate their result sets that other sources cannot provide such as table lock time, how many rows were examined versus returned, how many temporary tables were created ( and whether any were created on disk), whether range scans were done, whether sorting happened, how many rows were sorted, and what form the sort took.  You also get histograms of response times, standard deviation of response times, and other valuable information. 

 

Learn More Today

You can find out more about MySQL Enterprise Monitor and how to try it free at https://www.mysql.com/downloads/enterprise/

MariaDB 10.4.1 and MariaDB Connector/Node.js 2.0.2 now available

The MariaDB Foundation is pleased to announce the availability of MariaDB 10.4.1, the first beta release in the MariaDB 10.4 series, as well as MariaDB Connector/Node.js 2.0.2, the first release candidate of the 100% JavaScript non-blocking MariaDB client for Node.js. See the release notes and changelogs for details. Download MariaDB 10.4.1 Release Notes Changelog What […]

The post MariaDB 10.4.1 and MariaDB Connector/Node.js 2.0.2 now available appeared first on MariaDB.org.

Pages