Jan
09
2019
--

Percona Toolkit 3.0.13 Is Now Available

percona toolkit

percona toolkitPercona announces the release of Percona Toolkit 3.0.13 for January 9, 2019.

Percona Toolkit is a collection of advanced open source command-line tools, developed and used by the Percona technical staff, that are engineered to perform a variety of MySQL®, MongoDB® and system tasks that are too difficult or complex to perform manually. With over 1,000,000 downloads, Percona Toolkit supports Percona Server for MySQL, MySQL®, MariaDB®, Percona Server for MongoDB and MongoDB.

Percona Toolkit, like all Percona software, is free and open source. You can download packages from the website or install from official repositories.

This release includes the following changes:

Bug fixes:

  • PT-1673: pt-show-grants was incompatible with MariaDB 10+ (thanks Tim Birkett)
  • PT-1638: pt-online-schema-change was erroneously taking MariaDB 10.x for MySQL 8.0 and rejecting to work with it to avoid the upstream bug #89441 scope.
  • PT-1616: pt-table-checksum failed to resume on large tables with binary strings containing invalid UTF-8 characters.
  • PT-1573: pt-query-digest didn’t work in case of log_timestamps = SYSTEM my.cnf option.
  • PT-157: Specifying a non-primary key index with the ‘i’ part of the --source argument made pt-archiver to ignore the --primary-key-only option presence.

Improvements:

  • PT-1340: pt-stalk now doesn’t call mysqladmin debug command by default to avoid flooding in the error log. CMD_MYSQLADMIN="mysqladmin debug" environment variable reverts pt-stalk to the previous way of operation.
  • PT-1637: A new --fail-on-stopped-replication option  allows pt-table-checksum to detect failing slave nodes.

Help us improve our software quality by reporting any bugs you encounter using our bug tracking system.

Jan
09
2019
--

Amazon Aurora Serverless – The Sleeping Beauty

Amazon RDS Aurora Serverless activation times

One of the most exciting features Amazon Aurora Serverless brings to the table is its ability to go to sleep (pause) when idle. This is a fantastic feature for development and test environments. You get access to a powerful database to run tests quickly, but it goes easy on your wallet as you only pay for storage when the instance is paused.

You can configure Amazon RDS Aurora Serverless to go to sleep after a specified period of time. This can be set to anywhere between five minutes and 24 hours

configure Amazon RDS Aurora Serverless sleep time

For this feature to work, however, inactivity has to be complete. If you have so much as a single query or even maintain an idle open connection, Amazon Aurora Serverless will not be able to pause.

This means, for example, that pretty much any monitoring you may have enabled, including our own Percona Monitoring and Management (PMM) will prevent the instance from pausing. It would be great if Amazon RDS Aurora Serverless would allow us to specify user accounts to ignore, or additional service endpoints which should not prevent it from pausing, but currently you need to get by without such monitoring and diagnostic tools, or else enable them only for duration of the test run.

If you’re using Amazon Aurora Serverless to back very low traffic applications, you might consider disabling the automatic pause function, since waking up currently takes quite a while. Otherwise, your users should be prepared for a 30+ seconds wait while Amazon Aurora Serverless activates.

Having such a high time to activate means you need to be mindful of timeout configuration in your test/dev scripts so you do not have to deal with sporadic failures. Or you can also use something like the mysqladmin ping command to activate the instance before your test run.

Some activation experiments

Let’s now take a closer look at Amazon RDS Aurora Serverless activation times. These times are measured for MySQL 5.6 based Aurora Serverless – the only one currently available. I expect numbers could be different in other editions

Amazon RDS Aurora Serverless activation times

I measured the time it takes to run a trivial query (SELECT 1) after the instance goes to sleep. You’ll see I manually scaled the Amazon RDS Aurora Serverless instance to a desired capacity in ACU (Aurora Compute Units), and then had the script wait for six minutes to allow for pause to happen before running the query. The test was performed 12 times and the Min/Max/Avg times of these test runs for different settings of ACU are presented above.

You can see there is some variation between min and max times. I would expect to have even higher outliers, so plan for an activation time of more than a minute as a worst case scenario.

Also note that there is an interesting difference in the activation time between instance sizes. While in my tests the smallest possible size (2 ACU) consistently took longer to activate compared to the medium size (8 ACU), the even bigger size (64 ACU) was the slowest of all.

So make no assumptions about how long it would take for instance of given size to wake up with your workload, but rather test it if it is important consideration for you.

In some (rare) cases I also observed some internal timeouts during the resume process:

[root@ip-172-31-16-160 serverless]# mysqladmin ping -h serverless-test.cluster-XXXX.us-east-2.rds.amazonaws.com -u user -ppassword
mysqladmin: connect to server at 'serverless-test.cluster-XXXX.us-east-2.rds.amazonaws.com' failed
error: 'Database was unable to resume within timeout period.'

What about Autoscaling?

Finally, you may wonder how such Amazon Aurora Serverless pausing plays with Amazon Aurora Serverless Autoscaling ?

In my tests, I observed that resume always restores the instance size to the same ACU as it was before it was paused. However, this is where pausing configuration matters a great deal. According to this document, Amazon Aurora Serverless will not scale down more frequently than once per 900 seconds. While the document does not clarify over what period of time the conditions initiating scale down – cpu usage, connection usage etc – have to be met for scale down to be triggered, I can see that if the instance is idle for five minutes the scale down is not performed – it is just put to sleep.

At the same time, if you change this default five minute period to a longer time, the idle instance will be automatically scaled down a notch every 900 seconds before it finally goes to sleep. Consequently, when it is awakened it will not be at the last stage at which the load was applied, but instead at the stage it was at when it was scaled down. Also, scaling down is considered an event by itself, which resets the idle counter and delays the pause. For example: if the initial instance scale is 8, and the pause timer is set to 1h, it takes 1h 30 minutes for the pause to actually happen – 30 minutes to do scale down twice, plus 1 hour at the minimum size for pause to trigger

Here is a graph to illustrate this:

Amazon Aurora Serverless scale down timings

This also shows that when the load is re-applied at about 13:47, it recovers to the last number of ACU it had before the pause.

This means that a pause time of more than 15 minutes makes the pause behavior substantially different to the default.

Summary

  • Amazon Aurora Serverless automatic pause is a great for test/dev environments.
  • Resume time is relatively long, can reach as much as one minute.
  • Consider disabling automatic pausing for low traffic production applications, or at least let your users know they need to wait when they wake up the application.
  • Pause and Resume behavior is different in practice for a pause timeout of more than 15 minutes. Sticking to the default 5 minutes is recommended unless you really know what you’re doing.
Jan
08
2019
--

Percona Live 2019 Tracks

Percona Live 2019

Percona Live Percona Live 2019Open Source Database Conference 2019 in North America has moved to Austin, Texas: a cool place to be, and host to many big names in the tech space. Read what Dave Stokes, MySQL Community Manager for Oracle, has to say in favor of Austin.

If you need a conference ticket for Austin, put in your proposal now!

Those who are successful with their presentation or tutorial submissions will receive a pass to the full three days of the event. Closing date for the call for papers is Sunday, January 20.

Percona is adopting an industry trend by organizing the conference into 13 separate tracks with one Percona expert coordinating community input for each one. We believe subject-specific mini-committees of experts should provide better results than a single mega-committee covering everything.

The MySQL track is being led by Alkin Tezuysal, Senior Technical Manager

MariaDB is the responsibility of Sveta Smirnova, Principle Support Escalation Specialist.

MongoDB is being driven by Consultant Doug Duncan.

PostgreSQL is being pushed forward by Avinash Vallarapu, PostgreSQL Support Tech Lead

Other Open Source Databases well, this important challenge has been handed to Senior Support Engineer Agustín Gallego

Java Development for Open Source Databases might be of interest to developers and is being led by Rodrigo Trindade, Service Delivery Manager

Kubernetes track is being headed by Mykola Marzhan who is our Kubernetes Technical Lead

Database Security and Compliance will be overseen by Denis Farar, General Counsel and VP of HR (but make no mistake, this is still a track where tech content is very welcome)

Automation & AI topics, at the leading edge of database technology challenges, are the responsibility of Max Bubenick, Platform Lead.

Observability & Monitoring talk selection will be led by Roma Novikov, Director of Platform Engineering – so get those PMM and other OS monitoring proposals at the ready!

Polyglot Persistence is in the hands of our Senior Software Engineer Ibrar Ahmed who is waiting to hear all about your experiences with cross-database applications, data exchange and how to meet the challenges of a hybrid database world.

Migration to OpenSource Databases which is a similar-but-different track full of challenges parallel to that of polyglot applications is being watched over by Marco Tusa, Managing Consultant

Business & Enterprise track will be driven by Brian Walters, Director of Solution Engineering who is keen to hear of your case studies and experiences of the impact of open source databases on your process and organizations.

Cloud is a special case, since it touches on virtually all aspects of open source database technology. If your talk has particular relevance to ‘cloud’ then please add this track with your submission. Similarly Innovative Technologies can apply across the board, and if you have something to share that is truly new, then add that to your track list. Those that are most exciting in the context of cloud or innovative in their approach may be selected for their cloud or innovation merit, whichever track they belong to.

Our track champions will engage with community experts to select papers and shape content. If you would like to contribute by taking on talk selection, please let me know.

New speakers, and those with less experience, are welcome, we are here to help, so first check out my community blog post with links to info and video workshops on how to put together a selection-worthy proposal. Even old-hands might find some inspiration!

All in all, we think this is a great move, with the track champions contributing their passion, experience and knowledge of contemporary open source issues to the development of excellent content.  Although we’re changing several things at once, no one gets a prize for standing still. We hope you’ll continue to support and grow with us this great, open source, database focused event! Put a note in your diary to join us from May 28 – 30 in Austin, Texas.

Finally, if you would like to get in touch with any of our track champions, please let me know

Jan
07
2019
--

Understanding MySQL X (All Flavors)

what is MySQL X Protocol

what is MySQL X ProtocolSince 5.7.12 MySQL includes what is called the X plugin, but also it includes X protocol and X DevApi. But what is all this and how does it work? Let me share a personal short story on how I found myself investigating this feature. In a previous post I wrote about the MySQL Router tool, and our colleague Mr. Lefred pointed out that I was wrong about X protocol, because I mentioned it was created to be used with JSON docs. Given this input, I wanted to investigate in a little bit more depth about what all this “X” means and how it can be used in our day to day operations.

First problem I found is that the documentation is pretty extensive in the how’s but it was really hard to find the what’s. This is a bit strange, because for people trying to research about this new feature the documentation is not very helpful. In fact, I had to go to different websites to get a sense of what X means, how it works, and what it was created for.

Let’s start from the very beginning: what does the X stand for? Basically, it’s a way to name the crossover between relational and document models with extended capabilities, and the X is used for naming the three components we are describing: the plugin, the protocol and the DevApi.

X Plugin

This is the actual interface between MySQL server and the clients. By clients we can consider a variety of clients, not only the MySQL shell. It has to be installed in MySQL 5.7 versions via the INSTALL PLUGIN command but comes installed by default in MySQL 8. The plugin adds all the functionality, configuration variables, and status counters we need to use it.

It has the ability to work with both traditional SQL and Document objects, and also supports CRUD (Create, Read, Update, Delete) operations,  asynchronous query execution and so on – this provides a great capacity to extend the current way we work with MySQL.

X Protocol

This is a new client protocol created to ‘talk’ between the X Plugin and Clients.  I think it is fair to say this is an eXtended version of the MySQL protocol.
It was designed with the idea of having the capacity for asynchronous calls, meaning that you can send more than one query to server from same client without the need of waiting for first query to finish before sending the second and so. This improves the overall execution time by saving network round trips between clients and server.

Additionally, the protocol accepts CRUD operations and, of course, the handling of JSON documents and plain SQL. The protocol is fully implemented in MySQLShell and has several connectors for popular languages (Java and .Net for example)

X DevAPI

The last piece of this package is the X DevAPI protocol. Probably the best documented of these pieces is the API implemented on the MySQL Shell and connectors that supports the X Protocol. This API is designed to easily write programs from a given client using some popular languages. For example, we can easily create/test a program from MySQL Shell using Python or JavaScript.

The API defines few interesting concepts to handle sessions. These sessions can handle several connections to a server so in a specific session we can encapsulate more than one MySQL connection. You can define a basic session connection as follows (in JavaScript) using the MySQL Shell:

MySQL  localhost:33060+ ssl  JS > var test = require('mysqlx');
 MySQL  localhost:33060+ ssl  JS > var session = mysqlx.getSession({host: 'localhost', user: 'root', password: 'root', port: 3306});

So what’s new here? How does it help, and how I can make use of it? First let’s try to illustrate the architecture:

 

MySQL X the components

As you may notice, the X plugin adds a new interface that talks to X protocol, then this protocol is able to interact with connectors that supports the protocol (as mentioned above). The classic functionality is still present, so we just extended its functionality. The good part of this is that the protocol is capable of operating with both relational data and document store.

So now let’s check the funny part by putting all pieces together using a simple example using MySQL Shell:

[root@data1 ~]# mysqlsh
MySQL Shell 8.0.13
Copyright (c) 2016, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type '\help' or '\?' for help; '\quit' to exit.
 MySQL  JS > var test_conn = require('mysqlx');
 MySQL  JS > var session = mysqlx.getSession({host: 'localhost', user: 'root', password: 'root', port: 33060});   #creating session, notice X protocol listen port 33060 by default
 MySQL  JS > test_collection = session.getSchema('test').createCollection("people");
<Collection:people>
 MySQL  JS > test_collection.add({birth:"1988-06-12", Name: "Francisco"});
Query OK, 1 item affected (0.0456 sec)
 MySQL  JS > test_collection.add({birth:"2001-11-03", Name: "Maria", Nickname: "Mary"});
Query OK, 1 item affected (0.0255 sec)
 MySQL  JS > test_collection.find();
[
    {
        "Name": "Francisco",
        "_id": "00005c19099f0000000000000004",
        "birth": "1988-06-12"
    },
    {
        "Name": "Maria",
        "Nickname": "Mary",
        "_id": "00005c19099f0000000000000005",
        "birth": "2001-11-03"
    }
]
2 documents in set (0.0005 sec)
 MySQL  JS > \sql 									#simple command to switch between modes
Switching to SQL mode... Commands end with ;
 MySQL  SQL > \connect root@localhost
Creating a session to 'root@localhost'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 36 (X protocol)
Server version: 8.0.11 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  localhost:33060+ ssl  SQL > use test
Default schema set to `test`.
Fetching table and column names from `test` for auto-completion... Press ^C to stop.
 MySQL  localhost:33060+ ssl  test  SQL >  CREATE TABLE `people2` (
                                       ->   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
                                       ->   `birth` datetime NOT NULL,
                                       ->   `name` varchar(45) NOT NULL DEFAULT '',
                                       ->   `nickname` varchar(45) NULL DEFAULT '',
                                       ->   PRIMARY KEY (`id`)
                                       -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.1056 sec)
 MySQL  localhost:33060+ ssl  test  SQL > insert into people2(birth, name, nickname) values('2010-05-01', 'Peter', null), ('1999-10-14','Joseph', 'Joe');
Query OK, 2 rows affected (0.0326 sec)
 MySQL  localhost:33060+ ssl  test  SQL > select * from people2;
+----+---------------------+--------+----------+
| id | birth               | name   | nickname |
+----+---------------------+--------+----------+
|  1 | 2010-05-01 00:00:00 | Peter  | NULL     |
|  2 | 1999-10-14 00:00:00 | Joseph | Joe      |
+----+---------------------+--------+----------+
2 rows in set (0.0004 sec)
 MySQL  localhost:33060+ ssl  test  SQL > select * from people;
+-----------------------------------------------------------------------------------------------------+------------------------------+
| doc                                                                                                 | _id                          |
+-----------------------------------------------------------------------------------------------------+------------------------------+
| {"_id": "00005c19099f0000000000000004", "Name": "Francisco", "birth": "1988-06-12"}                 | 00005c19099f0000000000000004 |
| {"_id": "00005c19099f0000000000000005", "Name": "Maria", "birth": "2001-11-03", "Nickname": "Mary"} | 00005c19099f0000000000000005 |
+-----------------------------------------------------------------------------------------------------+------------------------------+
2 rows in set (0.0028 sec)

Interesting right? Within the same shell, I’ve created session to run over X protocol, and handled both document and relational objects, all without quitting from shell.

Is this all? Of course not! We are just scratching the surface, we haven’t used asynchronous calls nor CRUD operations. In fact, these topics are enough for a blog post each. Hopefully, though, the What’s are answered for now – at least a little –and if that’s the case, I’ll be very happy!


Photo by Deva Darshan on Unsplash

Jan
04
2019
--

Amazon RDS Aurora MySQL – Differences Among Editions

differences MySQL aurora versions

differences MySQL aurora versionsAmazon Aurora with MySQL Compatibility comes in three editions which, at the time of writing, have quite a few differences around the features that they support.  Make sure you don’t assume the newer Aurora 2.x supports everything in Aurora 1.x. On the contrary, right now Aurora 1.x (MySQL 5.6 based) supports most Aurora features.  The serverless option was launched for this version, and it’s not based on the latest MySQL 5.7.  However, the serverless option, too, has its own set of limitations

I found a concise comparison of what is available in which Amazon Aurora edition hard to come by so I’ve created one.  The table was compiled based mostly on documentation research, so if you spot some mistakes please let me know and I’ll make a correction.

Please keep in mind, this is expected to change over time. For example Amazon Aurora 2.x was initially released without Performance_Schema support, which was enabled in later versions.

There seems to be lag porting Aurora features from MySQL 5.6 compatible to MySQL 5.7 compatible –  the current 2.x release does not include features introduced in Aurora 1.16 or later as per this document

A comparison table

MySQL 5.6 Based MySQL 5.7 Based Serverless MySQL 5.6 Based
Compatible to MySQL MySQL 5.6.10a MySQL 5.7.12 MySQL 5.6.10a
Aurora Engine Version 1.18.0 2.03.01 1.18.0
Parallel Query Yes No No
Backtrack Yes No No
Aurora Global Database Yes No No
Performance Insights Yes No No
SELECT INTO OUTFILE S3 Yes Yes Yes
Amazon Lambda – Native Function Yes No No
Amazon Lambda – Stored Procedure Yes Yes Yes
Hash Joins Yes No Yes
Fast DDL Yes Yes Yes
LOAD DATA FROM S3 Yes Yes No
Spatial Indexing Yes Yes Yes
Asynchronous Key Prefetch (AKP) Yes No Yes
Scan Batching Yes No Yes
S3 Backed Based Migration Yes No No
Advanced Auditing Yes Yes No
Aurora Replicas Yes Yes No
Database Cloning Yes Yes No
IAM database authentication Yes Yes No
Cross-Region Read Replicas Yes Yes No
Restoring Snapshot from MySQL DB Yes Yes No
Enhanced Monitoring Yes Yes No
Log Export to Cloudwatch Yes Yes No
Minor Version Upgrade Control Yes Yes Always On
Data Encryption Configuration Yes Yes Always On
Maintenance Window Configuration Yes Yes No

Hope this is helps with selecting which Amazon Aurora edition is right for you, when it comes to supported features.


Photo by Nathan Dumlao on Unsplash

Jan
04
2019
--

Percona XtraDB Cluster 5.6.42-28.30 Is Now Available

Percona XtraDB Cluster 5.7

Percona XtraDB Cluster 5.6Percona announces the release of Percona XtraDB Cluster 5.6.42-28.30 (PXC) on January 4, 2019. Binaries are available from the downloads section or our software repositories.

Percona XtraDB Cluster 5.6.42-28.30 is now the current release, based on the following:

All Percona software is open-source and free.

Fixed Bugs

  • PXC-2281: Debug symbols were missing in Debian dbg packages.
  • 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-2230rgcs.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.

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!

Jan
04
2019
--

Percona XtraDB Cluster 5.7.24-31.33 Is Now Available

Percona XtraDB Cluster 5.7

Percona XtraDB Cluster 5.7Percona 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-1131mysqld-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!

 

Jan
02
2019
--

TasksMax: Another Setting That Can Cause MySQL Error Messages

TasksMax setting causing errors MySQL

TasksMax setting causing errors MySQLRecently, 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

Dec
21
2018
--

Announcing General Availability of Percona Server for MySQL 8.0

Percona Server for MySQL 8.0

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

Dec
21
2018
--

Release Notes for Percona Server for MySQL 8.0.13-3 GA

Percona Server for MySQL 8.0

Percona Server for MySQL 8.0

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.

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com