Planet MySQL

Planet MySQL -
  1. MySQL 8.0 is out. Is this a great piece of news? No. But MySQL 8.0 is wonderful, and this IS a great piece of news! It has lots of interesting features, really. Oracle advertised some of them very well with talks at conferences, blog posts, etc. However I am very pleased by a features that they did not advertised at all: resource groups. The documentation describes them in detail, but here is a small recap. As we all know, MySQL has system (background) threads, and user (foreground) threads. Until now, nothing could be done to change their priority. All we could do was to tune InnoDB concurrency tickets to make sure that long running queries don’t prevent other queries from using CPU time. Basically, what we do is splitting the execution of a query to improve concurrency. Now we can also affect threads priority. By default, all threads have the same priority. We can increase the priority of system threads, or decrease the priority of user threads. In any case, no user thread can have more priority than any system thread. Furthermore, we can limit the execution of some threads to one or more virtual CPUs of our choice. How are these things done? By creating a resource group and setting THREAD_PRIORITY and VCPU attributes. THREAD_PRIORITY is a number (-20..0 for system threads, 0..19 for user threads; lower numbers have higher priority). VCPU is a virtual CPU number (see which ones are available with lscpu), or a range, or a list of numbers/ranges. Then, we can assign running threads to one of the groups we created. For example, to create a group for system threads: CREATE RESOURCE GROUP sql_thread TYPE = SYSTEM VCPU = 0,1 THREAD_PRIORITY = -10; To assign an existing thread to that group, check its id with SHOW PROCESSLIST, and then run something like: SET RESOURCE GROUP sql_thread FOR 10; Some possible ideas: Increase priority of replication threads to reduce slave lag; Increase priority of the even scheduler; Decrease priority of long-running analytical queries. I plan to spend some time to experiment what happens if we change the priority of specific system threads on a busy server. I expect this feature to be helpful during peaks of CPU usage. Unfortunately, resource groups can only be assigned to running threads. There is no way to tell MySQL that connections from a certain user should use a certain group. However, we can achieve the same result by making a change in the applications. After connecting, the application can run: SET RESOURCE GROUP my_group; Federico
  2. Yesterday I was presenting on the MySQL Document Store and was asked if the _id fields created by the server as an InnoDB primary key is a UUID.  I knew that it was not a UUID but I had to hit the documentations ( to find out what the document ID really is -- a very interesting piece of information.The Details If you are inserting a document lacking a _id key, the server generates a value. The _id is 32 bits of a unique prefix (4 bytes), a time stamp (8 bytes), and serial number (16 bytes). The prefix is assigned by the InnoDB Cluster to help ensure uniqueness across a cluster. The timestamp is the encoded startup time of the server.  The serial numbers uses the auto increment offset and auto increment increment server variables .  From the manual page: This document ID format ensures that: The primary key value monotonically increments for inserts originating from a single server instance, although the interval between values is not uniform within a table. When using multi-primary Group Replication or InnoDB cluster, inserts to the same table from different instances do not have conflicting primary key values; assuming that the instances have the auto_increment_* system variables configured properly. PropertiesOnce set, the _id can not be set to another value. Inserting your own value overrides the server assignment. And if you attempt to insert a document with the same _id you the server will generate a duplicate primary key error.The _id values must be always increasing and sequential for optimal InnoDB performance. The server will keep track of theses numbers across restarts.The generated _id values for each table/collection  are unique across instances to avoid primary key conflicts and minimize transaction certification in multi-primary Group Replication or InnoDB cluster environments.Required?So, you are adding a document to a collection and you get an ERROR: 5115!  That means in the following cast that the _id key/value pair is needed:JS> db.foobar.add(-> {-> Data: "This is a test!"-> }-> )->ERROR: 5115: Document is missing a required fieldJS> db.foobar.add( { Data: "This is a test!" , -> _id: "first" } )Query OK, 1 item affected (0.0076 sec)
  3. When maintainng any piece of software, we usually deal with two kind of actions: bug fixing, new features. bugs and features A bug happens when there is an error in the software, which does not behave according to the documentation or the specifications. In short, it's a breech of contract between the software maintainer and the users. The promise, i.e. the software API that was published at every major version, is broken, and the software must be reconciled with the expectations and fixed, so that it behaves again as the documentation says. When we fix a bug in this way, we increment the revision number of the software version (e.g. 1.0.0 to 1.0.1. See semantic versioning). New features, in turn, can be of two types: backward compatible enhancements, which add value to the software without breaking the existing functionality. This is the kind of change that requires an increment of the minor indicator in the version (for example: 1.1.15 to 1.2.0.) Incompatible changes that break the existing behavior and require users to change their workflow. This kind of change requires bumping up the major number in the version (as in 2.1.3 to 3.0.0.) Not a bug, nor a feature, but an adjustment. The above concepts seem simple enough: you either fix something that's broken or add new functionality. However, when maintaining a tool that has the purpose of helping users to deal with another software (as it is the case of dbdeployer that helps users to deploy MySQL databases) there is yet another category of changes that don't fall into the standard categories: it's what happens when the software being helped (MySQL) changes its behavior, which would break the normal functioning of the helping tool, giving the maintainer a difficult choice: shall I modify the tool's interface to adapt to the new behavior, breaking existing procedures? or shall I adapt the tool's functioning behind the scenes to keep the interface unchanged? My philosophy with dbdeployer (and MySQL-Sandbox before it) is to preserve the tool's interface, so that users don't have to change existing procedures. I call this kind of changes adjustments, because they are not bugs, as they are not a consequence of a coding error, and not a feature, as the intervention is not a conscious decision to add new functionality, but an emergency operation to preserve the status quo. You can think of this category as a capricious change in specifications, which so often happens to software developers, with the difference that the one changing the specs is not the user, but a third party who doesn't know, or care, about our goal of preserving the API integrity. For example, from MySQL 8.0.3 to 8.0.4 there was a change in the default authentication plugin. Instead of mysql_native_password, MySQL 8.0.4 uses caching_sha2_password. The immediate side effect for MySQL-Sandbox and dbdeployer was that replication doesn't work out of the box. A possible solution would be to force the old authentication plugin, but this would not allow users to test the new one. Since the main reason to use a tool like dbdeployer is to experiment with new releases safely, I had to keep the default behavior. Thus, I left the default plugin in place, and changed the way the replication works. It's an ugly workaround actually, but allows users to see the new behavior without losing existing functionality.To complete the adjustment, I added a new option --native-auth-plugin, which would deploy using the old mysql_native_password. In total, the adjustment consists of a behind-the-scenes change, almost undetectable by users, and a new option to keep using the familiar authentication if users want it. From the point of view of semantic versioning, this kind of change is a backward-compatible modification of the API, which warrants an increase of the minor number of the version. Another example: when MySQL went from 8.0.4 to 8.0.11, it introduced a deal breaker change: the X Plugin is now loaded by default. This is easy for users of MySQL as a document store, as they don't need to enable the plugin manually, but bad news for anyone else, as the server is opening a port and a socket that many users may not choose to open voluntarily. What's worse, when installing more sandboxes of version 8.0.11 in the same host (for example in replication), one will succeed in reserving the plugin port and socket, while the others will have the error log populated with surprising errors about a socket being already in use. The solution is similar to the previous one. When dbdeployer detect MySQL 8.0.11 or newer, it adds options to customize the mysqlx plugin port and socket, thus allowing a frictionless deployment where the new functionality is available to the brave experimenters. At the same time, I added a new option (--disable-mysqlx) for the ones who really don't want an extra port and socket in their servers, not even for testing. These adjustment are usually costly additions. While the added code is not that much, they require extra tests, which are often complex and require more time to write and execute them. The process to add an adjustment goes mostly like this: I dedicate my morning walk to think about the fix. Sometimes the fix requires several walks, while I decide the less intrusive solution. If the walk has been fruitful, writing the code requires just a few minutes. If I missed something, I iterate. Then the more difficult part: writing meaningful tests that prove that the adjustment is correct and it doesn't introduce side effects in any MySQL version. And of course the option that reintroduces the old behavior must be tested too. A positive side effect of this exercise is that often I realize that I was missing a test for an important behavior and then I write down that as well. The test suite included 6,000+ tests 1 month ago, and now it has almost doubled.
  4. Restore requests are common and so are the restores of specific entities: a database, or one or more table(s). This has been discussed a lot and we have plenty of tools and solutions already available. In this blog post we will cover an interesting solution that I came across when I received a restoration request from a client with a specific scenario. The scenario? Well, the client was on a Windows server with 400GB of mysqldump and wanted to restore a table. As Linux players we already know of some tools and techniques to export a table or database from mysqldump – for example, using sed command or using the script mysqldumpsplitter (based on sed itself). But on Windows we are powerless by not being able to use sed (we’re sad without sed.) Also, there was no cygwin to ease up the pain. We had to come-up with a solution that works on Windows as well. During this discussion, my Pythian colleague, Romuller, suggested a cool but simple trick which enlightens us and offers one more way of exporting or recovering a table from a full mysqldump. So the trick here is as follows: – Create a user that has very specific grants, limited to one or more table(s) or database(s) that we need to restore. – Load mysqldump into the database with that user provide with –force. The option –force will ignore all the errors that will occur due to lack of privileges of the new user we created specifically for restore. Easy right? Database Consultants like to KISS ;). Let’s give it a try. I selected a table “stories” & create the “bad” situation by dropping that table. mysql> use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +------------------------+ | Tables_in_test | +------------------------+ ... | stories | ... +------------------------+ mysql> select count(*) from stories; +----------+ | count(*) | +----------+ | 881 | +----------+ 1 row in set (0.02 sec) mysql> drop table stories; Query OK, 0 rows affected (0.29 sec) Let’s begin the recovery phase now following the grants method. 1. Create the user with limited grants only on test.stories table. mysql> grant all privileges on test.stories to 'stories'@localhost identified by 'X'; Query OK, 0 rows affected, 1 warning (0.03 sec) mysql> show warnings; +---------+------+------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1287 | Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. | +---------+------+------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) Wait, there is a warning. We see this warning in MySQL 5.7.6 onward and it says GRANT commands will be deprecated in favour of CREATE USER statement to create new users. So, we shall have following practice to be ready for MySQL 8 :) CREATE USER 'stories'@’localhost’ identified with mysql_native_password by ‘X'; grant all privileges on test.stories to 'stories'@'localhost'; 2. Load the mysqldump using the same user with –force. [root@mysql1c ~]# cat fuldump.sql | mysql -ustories -pX test --force mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1044 (42000) at line 22: Access denied for user 'stories'@'localhost' to database 'archive' ... ERROR 1142 (42000) at line 420: ALTER command denied to user 'stories'@'localhost' for table 'emp_new' ... ERROR 1142 (42000) at line 1966: ALTER command denied to user 'stories'@'localhost' for table 'user_address' 3. Verify table is restored: mysql> show tables; +------------------------+ | Tables_in_test | +------------------------+ ... | stories | ... +------------------------+ mysql> select count(*) from stories; +----------+ | count(*) | +----------+ | 881 | +----------+ 1 row in set (0.00 sec) Conclusion: When you compare the table that is being restored to the other one, mysqldump is smaller. This method may take a lot of time just ignoring errors due to –force option. Of course, in most cases you will end up reading the whole file. If our table appears early in the mysqldump, we may monitor the progress and kill the process as well. Otherwise, it may make more sense to try and install Cygwin or move the backup to a Linux Box to extract a database object from the backup file. Hope this helps.
  5. Percona announces the release of Percona Toolkit 3.0.9 on April 20, 2018. 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: New Tools: PT-1501: pt-secure-collect – new tool to collect and sanitize pt-tools outputs New Features: PT-1530: Add support for encryption status to pt-mysql-summary PT-1526: Add ndb status to pt-mysql-summary (Thanks Fernando Ipar) PT-1525: Add support for MySQL 8 roles into pt-mysql-summary PT-1509: Make pt-table-sync only set binlog_format when necessary (Thanks Moritz Lenz) PT-1508: Add --read-only-interval and --fail-successive-errors flags to pt-heartbeat (Thanks Shlomi Noach) PT-243: Add --max-hostname-length and --max-line-length flags to pt-query-digest Bug Fixes: PT-1527: Fixed pt-table-checksum ignores --nocheck-binlog-format Improvements: PT-1507: pt-summary does not reliably read in the transparent huge pages setting (Thanks Nick Veenhof) Help us improve our software quality by reporting any bugs you encounter using our bug tracking system. The post Percona Toolkit 3.0.9 Is Now Available appeared first on Percona Database Performance Blog.