Nov
01
2019
--

Use MySQL Without a Password (And Still Be Secure)

Use MySQL Without a Password

Use MySQL Without a PasswordSome say that the best password is the one you don’t have to remember. That’s possible with MySQL, thanks to the auth_socket plugin and its MariaDB version unix_socket.

Neither of these plugins is new, and some words have been written about the auth_socket on this blog before, for example: how to change passwords in MySQL 5.7 when using plugin: auth_socket. But while reviewing what’s new with MariaDB 10.4, I saw that the unix_socket now comes installed by default and is one of the authentication methods (one of them because in MariaDB 10.4 a single user can have more than one authentication plugin, as explained in the Authentication from MariaDB 10.4 document).

As already mentioned this is not news, and even when one installs MySQL using the .deb packages maintained by the Debian team, the root user is created so it uses the socket authentication. This is true for both MySQL and MariaDB:

root@app:~# apt-cache show mysql-server-5.7 | grep -i maintainers
Original-Maintainer: Debian MySQL Maintainers <pkg-mysql-maint@lists.alioth.debian.org>
Original-Maintainer: Debian MySQL Maintainers <pkg-mysql-maint@lists.alioth.debian.org>

Using the Debian packages of MySQL, the root is authenticated as follows:

root@app:~# whoami
root=
root@app:~# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.27-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2019, 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 '\h' for help. Type '\c' to clear the current input statement.

mysql> select user, host, plugin, authentication_string from mysql.user where user = 'root';
+------+-----------+-------------+-----------------------+
| user | host      | plugin | authentication_string |
+------+-----------+-------------+-----------------------+
| root | localhost | auth_socket |                       |
+------+-----------+-------------+-----------------------+
1 row in set (0.01 sec)

Same for the MariaDB .deb package:

10.0.38-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04

MariaDB [(none)]> show grants;
+------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                      |
+------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED VIA unix_socket WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                  |
+------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

For Percona Server, the .deb packages from the official Percona Repo are also setting the root user authentication to auth_socket. Here is an example of Percona Server for MySQL 8.0.16-7 and Ubuntu 16.04:

root@app:~# whoami
root
root@app:~# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.16-7 Percona Server (GPL), Release '7', Revision '613e312'

Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, 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 '\h' for help. Type '\c' to clear the current input statement.

mysql> select user, host, plugin, authentication_string from mysql.user where user ='root';
+------+-----------+-------------+-----------------------+
| user | host      | plugin | authentication_string |
+------+-----------+-------------+-----------------------+
| root | localhost | auth_socket |                       |
+------+-----------+-------------+-----------------------+
1 row in set (0.00 sec)

So, what’s the magic? The plugin checks that the Linux user matches the MySQL user using the SO_PEERCRED socket option to obtain information about the user running the client program. Thus, the plugin can be used only on systems that support the SO_PEERCRED option, such as Linux. The SO_PEERCRED socket option allows retrieving the uid of the process that is connected to the socket. It is then able to get the user name associated with that uid.

Here’s an example with the user “vagrant”:

vagrant@mysql1:~$ whoami
vagrant
vagrant@mysql1:~$ mysql
ERROR 1698 (28000): Access denied for user 'vagrant'@'localhost'

Since no user “vagrant” exists in MySQL, the access is denied. Let’s create the user and try again:

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'vagrant'@'localhost' IDENTIFIED VIA unix_socket;
Query OK, 0 rows affected (0.00 sec)

vagrant@mysql1:~$ mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 45
Server version: 10.0.38-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04
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 [(none)]> show grants;
+---------------------------------------------------------------------------------+
| Grants for vagrant@localhost                                                    |
+---------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'vagrant'@'localhost' IDENTIFIED VIA unix_socket |
+---------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Success!

Now, what about on a non-debian distro, where this is not the default? Let’s try it on Percona Server for MySQL 8 installed on a CentOS 7:

mysql> show variables like '%version%comment';
+-----------------+---------------------------------------------------+
| Variable_name   | Value                                   |
+-----------------+---------------------------------------------------+
| version_comment | Percona Server (GPL), Release 7, Revision 613e312 |
+-----------------+---------------------------------------------------+
1 row in set (0.01 sec)

mysql> CREATE USER 'percona'@'localhost' IDENTIFIED WITH auth_socket;
ERROR 1524 (HY000): Plugin 'auth_socket' is not loaded

Failed. What is missing? The plugin is not loaded:

mysql> pager grep socket
PAGER set to 'grep socket'
mysql> show plugins;
47 rows in set (0.00 sec)

Let’s add the plugin in runtime:

mysql> nopager
PAGER set to stdout
mysql> INSTALL PLUGIN auth_socket SONAME 'auth_socket.so';
Query OK, 0 rows affected (0.00 sec)

mysql> pager grep socket; show plugins;
PAGER set to 'grep socket'
| auth_socket                     | ACTIVE | AUTHENTICATION | auth_socket.so | GPL     |
48 rows in set (0.00 sec)

We got all we need now. Let’s try again:

mysql> CREATE USER 'percona'@'localhost' IDENTIFIED WITH auth_socket;
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'percona'@'localhost';
Query OK, 0 rows affected (0.01 sec)

And now we can log in as the OS user “percona”.

[percona@ip-192-168-1-111 ~]$ whoami
percona
[percona@ip-192-168-1-111 ~]$ mysql -upercona
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 8.0.16-7 Percona Server (GPL), Release 7, Revision 613e312


Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, 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 '\h' for help. Type '\c' to clear the current input statement.


mysql> select user, host, plugin, authentication_string from mysql.user where user ='percona';
+---------+-----------+-------------+-----------------------+
| user    | host   | plugin   | authentication_string |
+---------+-----------+-------------+-----------------------+
| percona | localhost | auth_socket |                       |
+---------+-----------+-------------+-----------------------+
1 row in set (0.00 sec)

Success again!

Question: Can I try to log as the user percona from another user?

[percona@ip-192-168-1-111 ~]$ logout
[root@ip-192-168-1-111 ~]# mysql -upercona
ERROR 1698 (28000): Access denied for user 'percona'@'localhost'

No, you can’t.

Conclusion

MySQL is flexible enough in several aspects, one being the authentication methods. As we see in this post, one can achieve access without passwords by relying on OS users. This is helpful in several scenarios, but just to mention one: when migrating from RDS/Aurora to regular MySQL and using IAM Database Authentication to keep getting access without using passwords.

Oct
23
2019
--

ProxySQL 2.0.7 and proxysql-admin Tool Now Available

ProxySQL

ProxySQLProxySQL 2.0.7, released by ProxySQL, is now available for download in the Percona Repository along with Percona’s proxysql-admin tool.

ProxySQL is a high-performance proxy, currently for MySQL and database servers in the MySQL ecosystem (like Percona Server for MySQL and MariaDB). It acts as an intermediary for client requests seeking resources from the database. René Cannaò created ProxySQL for DBAs as a means of solving complex replication topology issues.

This release includes ProxySQL 2.0.7 which introduces many new features and enhancements and also fixes a number of bugs.

The proxysql-admin tool now supports MariaDB 10.4.

New Features

Improvements

  • PSQLADM-195: A new option --with-stats-reset has been added to the proxysql-status script to display the  *_reset tables from the stats database. If this option is not specified, these tables are not displayed by default.

Bugs Fixed

  • PSQLADM-157: In some cases, the proxysql-status script used the cat command to display a file without checking if the file existed and was readable.
  • PSQLADM-181: When run with --update-cluster --write-node=<node_name>, the proxysql-admin tool now verifies that the writer nodes are not read-only.

The ProxySQL 2.0.7 source and binary packages available from the Percona download page for ProxySQL include ProxySQL Admin – a tool developed by Percona to configure Percona XtraDB Cluster nodes into ProxySQL. Docker images for release 2.0.7 are available as well. You can download the original ProxySQL from GitHub. GitHub hosts the documentation in the wiki format.

ProxySQL is available under Open Source license GPLv3.

Aug
22
2019
--

Percona Server for MySQL 5.7.27-30 Is Now Available

Percona Server for MySQL 5.7

Percona Server for MySQLPercona announces the release of Percona Server for MySQL 5.7.27-30 on August 22, 2019 (downloads are available here and from the Percona Software Repositories). This release merges changes of MySQL 5.7.27, including all the bug fixes in it. Percona Server for MySQL 5.7.27-30 is now the current GA release in the 5.7 series. All of Percona’s software is open-source and free.

Bug Fixes:

  • Parallel doublewrite buffer writes must crash the server on an I/O error occurs. Bug fixed #5678.
  • On a server with two million or more tables using foreign keys and AUTOINC columns, the shutdown may take a measurable length of time. Bug fixed #5639 (Upstream #95895).
  • If large pages are enabled on the MySQL side, the maximum size for  innodb_buffer_pool_chunk_size is effectively limited to 4GB. Bug fixed #5517 (Upstream #94747).
  • The TokuDB hot backup library continually dumps TRACE information to the Server error log. The user cannot enable or disable the dump of this information. Bug fixed #4850.
  • Key rotation is redesigned to allow  Select rotate_system_key("percona_redo"). The currently used key version is displayed in the innodb_redo_key_version status. Bug fixed #5565.
  • The TokuDBBackupPlugin is optional at cmake time. Bug fixed #5748.
  • A multi-table  DELETE with a foreign key breaks replication. Bug fixed #3845.
  • TRUNCATEwith any table and interfacing with Adaptive Hash Index (AHI) can cause server stalls due to the interaction with AHI, whether the AHI is enabled or not.
    Bug fixed #5576 (upstream #94610).
  • In specific configurations and with log_slow_verbosity set to log InnoDB statistics, memory usage increases while running a stored procedure. Bug fixed #5581
  • Thread pool ability to track network I/O was disabled. Bug fixed #5723.
  • An instance started with the default values but setting the redo log to encrypt without specifying the keyring plugin parameters does not fail or throw an error.  Bug fixed #5476.
  • Setting the encryption to ON for the system tablespace generates the encryption key and encrypts system temporary tablespace pages. Resetting encryption to OFF , all subsequent pages are written to the temporary tablespace without encryption. To allow any encrypted tables to be decrypted, the generated keys are not erased. Modifying the innodb_temp_tablespace_encrypt does not affect file-per-table temporary tables. This type of table is encrypted if ENCRYPTION =’Y’ is set during the table creation. Bug fixed #5736
  • After resetting the innodb_temp_tablespace_encrypt to OFF during runtime, the subsequent file-per-table temporary tables continue to be encrypted. Bug fixed #5734

Other bugs fixed: #5752, #5749, #5746, #5744, #5743, #5742, #5740, #5711, #5695, #5681, #5669, #5645, #5638, #5593, #5532, #3970, #5696, #5689, #5146, #5715, #5662, #5420, #5149, #5686, #5688, #5697, #5716, #5725, #5773, #5775, #5820, and #5839.

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

Aug
21
2019
--

ProxySQL 2.0.6 and proxysql-admin tool Now Available

ProxySQL

ProxySQL 1.4.14

ProxySQL 2.0.6, released by ProxySQL, is now available for download in the Percona Repository along with Percona’s proxysql-admin tool.

ProxySQL is a high-performance proxy, currently for MySQL and database servers in the MySQL ecosystem (like Percona Server for MySQL and MariaDB). It acts as an intermediary for client requests seeking resources from the database. René Cannaò created ProxySQL for DBAs as a means of solving complex replication topology issues.

This release includes ProxySQL 2.0.6 which introduces many new features and enhancements and also fixes a number of bugs. The proxysql-admin tool has not changed since the previous release.

The ProxySQL 2.0.6 source and binary packages available from the Percona download page for ProxySQL include ProxySQL Admin – a tool developed by Percona to configure Percona XtraDB Cluster nodes into ProxySQL. Docker images for release 2.0.6 are available as well. You can download the original ProxySQL from GitHub. GitHub hosts the documentation in the wiki format.

ProxySQL is available under Open Source license GPLv3.

Aug
20
2019
--

Percona Server for MySQL 5.6.45-86.1 Now Available

Percona Server for MySQL 5.6.45-86.1

Percona Server for MySQL 5.6.45-86.1Percona announces the release of Percona Server for MySQL 5.6.45-86.1 on August 20, 2019. Download the latest version from the Percona web site or the Percona Software Repositories. You can also run Docker containers from the images in the Docker Hub repository.

Based on MySQL 5.6.45, and including all the bug fixes in it, Percona Server for MySQL 5.6.45-86.1 is the current GA release in the Percona Server for MySQL 5.6 series. Percona Server for MySQL is open-source and free – this is the latest release of our enhanced, drop-in replacement for MySQL.

Bugs Fixed:

  • The TokuDB hot backup library continually dumps TRACE information to the server error log. The user cannot enable or disable the dump of this information. Bug fixed #4850.
  • The TokuDBBackupPlugin is optional at cmake time. Bug fixed #5748.

Other bugs fixed: #5531, #5146 #5638, #5645, #5669, #5749 #5752, #5780, #5833, #5725 #5742, #5743, and #5746.

Release notes are available in the online documentation. Please report any bugs on the JIRA bug tracker.

Aug
15
2019
--

Percona Server for MySQL 8.0.16-7 Is Now Available

Percona server for MySQLPercona announces the release of Percona Server for MySQL 8.0.16-7 on August 15, 2019 (downloads are available here and from the Percona Software Repositories).

This release is based on MySQL 8.0.16. It includes all bug fixes in these releases. Percona Server for MySQL 8.0.16-7 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.16 includes all the features available in MySQL 8.0.16 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.

Encryption Features General Availability (GA)

  • Temporary File Encryption (Temporary File Encryption)
  • InnoDB Undo Tablespace Encryption
  • InnoDB System Tablespace Encryption (InnoDB System Tablespace Encryption)
  • default_table_encryption

      =OFF/ON (General Tablespace Encryption)

  • table_encryption_privilege_check

     =OFF/ON (Verifying the Encryption Settings)

  • InnoDB redo log encryption (for master key encryption only) (Redo Log Encryption)
  • InnoDB merge file encryption (Verifying the Encryption Setting)
  • Percona Parallel doublewrite buffer encryption (InnoDB Tablespace Encryption)

Known Issues

  • 5865: Percona Server 8.0.16 does not support encryption for the MyRocks storage engine. An attempt to move any table from InnoDB to MyRocks fails as MyRocks currently will see all InnoDB tables as being encrypted.

Bugs Fixed

  • Parallel doublewrite buffer writes must crash the server on an I/O error occurs. Bug fixed #5678.
  • After resetting the innodb_temp_tablespace_encrypt to OFF during runtime the subsequent file-per-table temporary tables continue to be encrypted. Bug fixed #5734.
  • Setting the encryption to ON for the system tablespace generates an encryption key and encrypts system temporary tablespace pages. Resetting the encryption to OFF, all subsequent pages are written to the temporary tablespace without encryption. To allow any encrypted tables to be decrypted, the generated keys are not erased. Modifying they innodb_temp_tablespace_encrypt does not affect file-per-table temporary tables. This type of table is encrypted if ENCRYPTION='Y' is set during table creation. Bug fixed #5736.
  • An instance started with the default values but setting the redo log to encrypt without specifying the keyring plugin parameters does not fail or throw an error. Bug fixed #5476.
  • The rocksdb_large_prefix allows index key prefixes up to 3072 bytes. The default value is changed to TRUE to match the behavior of the innodb_large_prefix. Bug fixed #5655.
  • On a server with two million or more tables, a shutdown may take a measurable length of time. Bug fixed #5639.
  • The changed page tracking uses the LOG flag during read operations. The redo log encryption may attempt to decrypt pages with a specific bit set and fail. This failure generates error messages. A NO_ENCRYPTION flag lets the read process safely disable decryption errors in this case. Bug fixed #5541.
  • If large pages are enabled on MySQL side, the maximum size for innodb_buffer_pool_chunk_size is effectively limited to 4GB. Bug fixed 5517 (upstream #94747 ).
  • The TokuDB hot backup library continually dumps TRACE information to the server error log. The user cannot enable or disable the dump of this information. Bug fixed #4850.

Other bugs fixed: #5688,
#5723, #5695, #5749, #5752, #5610, #5689, #5645, #5734, #5772, #5753, #5129, #5102, #5681, #5686, #5681, #5310, #5713, #5007, #5102, #5129, #5130, #5149, #5696, #3845, #5149, #5581, #5652, #5662, #5697, #5775, #5668, #5752, #5782, #5767, #5669, #5753, #5696, #5733, #5803, #5804, #5820, #5827, #5835, #5724, #5767, #5782, #5794, #5796, #5746, and #5748.

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.

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

Aug
14
2019
--

MySQL 8 and MySQL 5.7 Memory Consumption on Small Devices

MySQL 8 and MySQL 5.7 Memory Consumption

MySQL 8 and MySQL 5.7 Memory ConsumptionWhile we often run MySQL on larger scale systems in Production for Test and Dev, sometimes we want to run MySQL on the tiniest cloud instances possible or just run it on our laptops. In these cases, MySQL 8 and MySQL 5.7 memory consumption is quite important.

In comparing MySQL 8 vs MySQL 5.7, you should know that MySQL 8 uses more memory. Basic tests on a 1GB VM with MySQL 8 and MySQL 5.7 (actually they’re Percona Server versions) running the same light workload, I see the following vmstat output:

MySQL 5.7 vmstat output

procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 4  0  65280  71608  58352 245108    0    0  2582  3611 1798 8918 18  9 11 33 30
 4  0  65280  68288  58500 247512    0    0  2094  2662 1769 8508 19  9 13 30 29
 3  1  65280  67780  58636 249656    0    0  2562  3924 1883 9323 20  9  7 37 27
 4  1  65280  66196  58720 251072    0    0  1936  3949 1587 7731 15  7 11 36 31

MySQL 8.0 vmstat output

procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa st
 9  1 275356  62280  60832 204736    0    0  2197  5245 2638 13453 24 13  2 34 27
 3  0 275356  60548  60996 206340    0    0  1031  3449 2446 12895 25 11 10 28 26
 7  1 275356  78188  59564 190632    0    1  2448  5082 2677 13661 26 13  6 30 25
 4  1 275356  76516  59708 192096    0    0  2247  3750 2401 12210 22 12  4 38 24

As you can see, MySQL 8 uses some 200MB more swap and also uses less OS cache, signaling more memory being allocated and at least “committed.” If we look at the “top” output we see:

MySQL 5.7

mysql 5.7

MySQL 8.0

MySQL 8.0

This also shows more Resident memory and virtual memory used by MySQL8.   Virtual Memory, in particular, is “scary” as it is well in excess of the 1GB of physical memory available on these VMs.  Of course, Virtual Memory usage (VSZ) is a poor indicator of actual memory needs for modern applications, but it does corroborate the higher memory needs story.

In reality, though, as we know from the “vmstat” output, neither MySQL 8 nor MySQL 5.7 is swapping with this light load, even though there isn’t much “room” left. If you have more than a handful of connections or wish to run some applications on the same VM, you would get swapping (or OOM killer if you have not enabled swap).

It would be an interesting project to see how low I can drive MySQL 5.7 and MySQL 8 memory consumption, but I will leave it to another project. Here are the settings I used for this test:

[mysqld]
innodb_buffer_pool_size=256M
innodb_buffer_pool_instances=1
innodb_log_file_size=1G
innodb_flush_method=O_DIRECT
innodb_numa_interleave=1
innodb_flush_neighbors=0
log_bin
server_id=1
expire_logs_days=1
log_output=file
slow_query_log=ON
long_query_time=0
log_slow_rate_limit=1
log_slow_rate_type=query
log_slow_verbosity=full
log_slow_admin_statements=ON
log_slow_slave_statements=ON
slow_query_log_always_write_time=1
slow_query_log_use_global_control=all
innodb_monitor_enable=all
userstat=1

Summary:  When moving to MySQL 8 in a development environment, keep in mind it will require more memory than MySQL 5.7 with the same settings.

Aug
12
2019
--

ProxySQL 2.0.5 and proxysql-admin tool Now Available

ProxySQL 2.0.5

ProxySQL 1.4.14

ProxySQL 2.0.5, released by ProxySQL, is now available for download in the Percona Repository along with Percona’s proxysql-admin tool.

ProxySQL is a high-performance proxy, currently for MySQL and database servers in the MySQL ecosystem (like Percona Server for MySQL and MariaDB). It acts as an intermediary for client requests seeking resources from the database. René Cannaò created ProxySQL for DBAs as a means of solving complex replication topology issues.

This release includes ProxySQL 2.0.5 which fixes many bugs and introduces a number of features and enhancements. The proxysql-admin tool has been enhanced to support the following new options:

  • The --add-query-rule option creates query rules for synced MySQL users. This option is only applicable for the singlewrite mode and works together with the --syncusers and --sync-multi-cluster-users options.
  • The --force option skips existing configuration checks in mysql_servers, mysql_users and mysql_galera_hostgroups tables. This option will only work together with the –enable option: proxysql-admin --enable --force.
  • The --update-mysql-version option updates the mysql-server_version variable in ProxySQL with the version from a node in Percona XtraDB Cluster.

The ProxySQL 2.0.5 source and binary packages available from the Percona download page for ProxySQL include proxysql-admin – a tool developed by Percona to configure Percona XtraDB Cluster nodes into ProxySQL. Docker images for release 2.0.5 are available as well. You can download the original ProxySQL from GitHub. GitHub hosts the documentation in the wiki format.

ProxySQL 2.0.5 Improvements

  • PSQLADM-49: Create rules for –syncusers. When running with --syncusers or --sync-multi-cluster-users, the --add-query-rule option can now be specified to add the singlewriter query rules for the new users.
  • PSQLADM-51: Update mysql-server_version variable. The --update-mysql-version command has been added to set the mysql-server_version__ global variable in ProxySQL.  This will take the version from a node in the cluster and set it in ProxySQL.

Bugs Fixed

  • PSQLADM-190: The --remove-all-servers option did not work on enable. When running with proxysql-cluster, the galera hostgroups information was not replicated which could result in failing to run --enable on a different ProxySQL node.  The --force option was added for --enable to be able to ignore any errors and always configure the cluster.
  • PSQLADM-199: query-rules removed during proxysql-cluster creation with PXC operator. When using the PXC operator for Kubernetes and creating a proxysql-cluster, the query rules could be removed.  The code was modified to merge the query rules (rather than deleting and recreating).  If the --force option was specified, then a warning was issued in case any existing rules were found; otherwise an error was issued. The --disable-updates option was added to ensure that ProxySQL cluster updates did not interfere with the current command.
  • PSQLADM-200: users were not created for –-syncusers with PXC operator. When using the PXC operator for Kubernetes, the --syncusers command was run but the mysql_users table was not updated.  The fix for PSQLADM-199 that suggested to use --disable-updates also applies here.

ProxySQL is available under Open Source license GPLv3.

Jul
17
2019
--

MySQL: Disk Space Exhaustion for Implicit Temporary Tables

Implicit Temporary Tables

Implicit Temporary TablesI was recently faced with a real issue about completely exhausting the disk space on MySQL. This was a serious issue because of the continuous outages of the service, as the customer had to constantly restart the server and wait for the next outage.

What was happening? In this article, I’m going to explain it and propose solutions.

Implicit temporary tables

MySQL needs to create implicit temporary tables for solving some kinds of queries. The queries that require a sorting stage most of the time need to rely on a temporary table. For example, when you use GROUP BY, ORDER BY or DISTINCT.  Such queries are executed in two stages: the first is to gather the data and put them into a temporary table, the second is to execute the sorting on the temporary table.

A temporary table is also needed in case of some UNION statements evaluation, for VIEW that cannot use merge, for derived tables when using subqueries, for multiple-table UPDATE, and some other cases.

If the temporary table is small it can be created into the memory, otherwise, it’s created on the disk. Needless to say that an in-memory temporary table is faster. MySQL creates an in-memory table, and if it becomes too large it is converted to an on-disk table. The maximum size for in-memory temporary tables is defined by the tmp_table_size or max_heap_table_size value, whichever is smaller. The default size in MySQL 5.7 is 16MB. If you run queries on a large amount of data, or if you have not optimized queries, you can increase the variables. When setting the threshold, take into consideration the available RAM and the number of concurrent connections you have during your peaks. You cannot indefinitely increase the variables, as at some point you’ll need to let MySQL use on-disk temporary tables.

Note: the temporary table is created on-disk if the tables involved have TEXT or BLOB columns, even if the size is less than the configured threshold.

Temporary tables storage engine

Until MySQL 5.6, all the on-disk temporary tables are created as MyISAM. The temporary table is created in-memory or on-disk, depending on the configuration, and it’s dropped immediately at the end of the query. From MySQL 5.7, they are created as InnoDB by default. Then you can rely on the advanced features.

The new default is the best option for the overall performance and should be used in the majority of the cases.

A new configuration variable is available to set the storage engine for the temporary tables:  internal_tmp_disk_storage_engine. The variable can be set to  innodb (default if not set) or myisam.

The potential problem with InnoDB temporary tables

Although using InnoDB is the best for performance, a new potential issue could arise. In some particular cases, you can have disk exhaustion and server outage.

As any other InnoDB table in the database, the temporary tables have their own tablespace file. The new file is in the data directory together with the general tablespace, with the name ibtmp1. It stores all the tmp tables. A tablespace file cannot be shrunk, and it grows constantly as long as you don’t run a manual OPTIMIZE TABLE. The ibtmp1 makes no difference, as you cannot use OPTIMIZE. The only way to shrink ibtmp1 size to zero is to restart the server.

Fortunately, even if the file cannot shrink, after the execution of a query the temporary table is automatically dropped and the space in the tablespace can be reused for another incoming query.

Let’s think now about the following case:

  • you have non-optimized queries that require the creation of very large on-disk tmp tables
  • you have optimized queries, but they are creating very large on-disk tmp tables because you are doing in purpose computation on a very large dataset (statistics, analytics)
  • you have a lot of concurrent connections running the same queries with tmp table creation
  • you don’t have a lot of free space in your volume

In such a situation it’s easy to understand that the file ibtmp1 size can increase considerably and the file can easily exhaust the free space. This was happening several times a day, and the server had to be restarted in order to completely shrink the ibtmp1 tablespace.

It’s not mandatory that the concurrent queries are launched exactly at the same time. Since a query with a large temporary table will take several seconds or minutes to execute, it is sufficient to have queries launched at different times while the preceding ones are still running. Also, you have to consider that any connection creates its own temporary table, so the same exact query will create another exact copy of the same temporary table into the tablespace. Exhausting the disk space is very easy with non-shrinkable files!

So, what to do to avoid disk exhaustion and outages?

The trivial solution: use a larger disk

This is really trivial and can solve the problem, but it is not the optimal solution. In fact, it’s not so easy to figure out what your new disk size should be. You can guess by increasing the disk size step by step, which is quite easy to do if your environment is in the cloud or you have virtual appliances on a very large platform. But it’s not easy to do in on-premise environments.

But with this solution, you can risk having unneeded expenses, so keep that in mind.

You can also move the ibtmp1 file on a dedicated large disk, by setting the following configuration variable:

[mysqld]
innodb_temp_data_file_path = ../../tmp/ibtmp1:12M:autoextend

A MySQL restart is required.

Please note that the path has to be specified as relative to the data directory.

Set an upper limit for ibtmp1 size

For example:

[mysqld]
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:10G

In this case, the file cannot grow more than 10GB. You can easily eliminate the outages, but this is a dangerous solution. When the data file reaches the maximum size, queries fail with an error indicating that the table is full. This is probably bad for your applications.

Step back to MyISAM for on-disk temporary tables

This solution seems to be counterintuitive but it could be the best way to avoid the outages in a matter of seconds and is guaranteed to use all needed temporary tables.

You can set the following variable into my.cnf:

internal_tmp_disk_storage_engine = MYISAM

Since the variable is dynamic you can set it also at runtime:

SET GLOBAL internal_tmp_disk_storage_engine = MYISAM;

Stepping back to MyISAM, you will considerably decrease the possibility of completely filling your disk space. In fact, the temporary tables will be created into different files and immediately dropped at the end of the query. No more issues about a forever increasing file.

And while there is always the possibility to see the same issue, just in case you can run the queries at the exact same time or really very close. In my real-world case, this was the solution to avoid all the outages.

Optimize your queries

This is the most important thing to do. After stepping back the storage engine to MyISAM to mitigate the outage occurrences, you have to absolutely take the time to analyze the queries.

The goal is to decrease the size of the on-disk temporary tables. It’s not the aim of this article to explain how to investigate the queries, but you can rely on the slow log, on a tool like pt-query-digest and on EXPLAIN.

Some tips:

  • create missing indexes on the tables
  • add more filters in the queries to gather less data, if you don’t really need it
  • rewrite queries to optimize the execution plan
  • if you have very large queries on purpose, you can use a queue manager in your applications to serialize their executions or to decrease the concurrency

This will be the longest activity, but hopefully, after all the optimizations, you can return to set the temporary storage engine to InnoDB for better performance.

Conclusion

Sometimes the improvements can have unexpected side effects. The InnoDB storage engine for on-disk temporary tables is a good improvement, but in some particular cases, for example, if you have non-optimized queries and little free space, you can have outages because of “disk full” error. Stepping back the tmp storage engine to MyISAM is the fastest way to avoid outages, but the optimization of the queries is the more important thing to do as soon as possible in order to return to InnoDB. And yes, even a larger or dedicated disk may help. It’s a trivial suggestion, I know, but it can definitely help a lot.

By the way, there’s a feature request about the issue: https://bugs.mysql.com/bug.php?id=82556

Further readings:
https://www.percona.com/blog/2007/01/19/tmp_table_size-and-max_heap_table_size/
https://www.percona.com/blog/2017/12/04/internal-temporary-tables-mysql-5-7/
http://mysqlserverteam.com/mysql-5-7-innodb-intrinsic-tables/
https://dev.mysql.com/doc/refman/5.7/en/internal-temporary-tables.html

 

Jul
03
2019
--

Percona Server for MySQL Highlights – binlog_space_limit

Percona Server for MySQL

Percona Server for MySQLI think it is often confusing to compare upstream MySQL and Percona Server for MySQL, and some helpful information can be found in the introductory notes. But what does that really mean for an ordinary DBA, especially if none of the known big extra features are important in a particular use case?

In this article, I would like to start a series of short blog posts highlighting small, often less known, but potentially useful features, available in Percona Server for MySQL. Let’s start with a relatively new feature.

Limit the disk space used by binary logs problem

Historically, dealing with binary logs and the disk space used by them was quite challenging. The only thing that let you control this is the expire_logs_days variable, which can be useless in some scenarios (binlogs growing faster than can be purged) and does not solve all problems. Therefore, in order to avoid disk full and server crashes, it is often needed to create smart scripts that monitor the situation and call PURGE BINARY LOGS TO when necessary.

This isn’t very convenient and so a feature request appeared in September 2012 – https://bugs.mysql.com/bug.php?id=66733 to use the already existing logic for relay logs and apply it for binary logs as well. It has not been addressed in upstream yet.

We attempted to deal with it in Percona Server by introducing max_binlog_files variable (Percona Server 5.5.27, October 2012). This was a good step forward, but still not as good as expected. Even though limiting the number of binary log files gives much better control in terms of disk space used by them, it has one serious downside. It does not take into account situations when there may be binary logs lot smaller or a lot bigger then the max_binlog_size specifies.

Most common situations like that may be that the server rotates to a new binary log faster due to FLUSH LOGS issued, or due to restarts. A couple of such FLUSH commands in a row may completely ruin the ability to point in time recovery by losing a large part of the history of the binary log. An opposite case is when a binary log gets bigger than maximum size due to large transactions (which are never going to be split).

Recently, a new variable in MySQL 8.0 has replaced the expire_logs_days– the binlog_expire_logs_secondsand also a period of 30 days became the new default (previously it was unlimited).  It is certainly better to have more precise control on binary logs history then “days”, for example, we can set the rotation period to 2 days 6 hours and 30 minutes or whatever best matches the backup schedule. Again, a good time limit control on binary logs may not be sufficient in all cases and won’t help much when it is hard to predict how much data will be written or changed over time. Only space-based limits can actually save us from hitting the disk space problem.

Expected solution is there

Finally, something better has arrived, the binlog_space_limit variable, introduced in Percona Server 5.7.23 on September 2018 (ported to 8.0 as well). The idea is very simple: it does the same thing as relay_log_space_limit does for the relay logs. Finally, both sides of binary logging have comparable functionality when it comes to controlling the space on the disk.

How to use it

When the binlog_space_limit variable is set to 0 (default), the feature is turned off and no limit is set apart from the one imposed by expire_logs_days. I think it is a good idea is to dedicate a separate disk partition for the binary logs (or binary and redo logs), big enough to keep as many of them as needed for a good backup and replication strategy, and set the variable to around 95% of this partition size. Remember, that even though max_binlog_sizedefines the maximum size of a single binary log, this is not a hard limit. This is because a single transaction is never split between multiple binlog files and has to be written to the same binary log as a whole.

Let’s say the max_binlog_size is set to 1GB (default and maximum), and the binlog_space_limit is set to 10GB, but it happens that a really big transaction changes 3GB of data. What will happen with the binlog_space_limit set is that in order to store that new 3GB in the binary log (when the total size of logs is going to hit the limit), it will remove as many old binary logs as needed first, in this case, up to three oldest ones. This way there is no risk in hitting disk full situation even if the free disk space is smaller then the new binary log addition would need. But still, I would recommend reserving at least little more than 1GB of free disk space for such partition, just in case.

Example settings for a 16GB disk partition, dedicated for binary logs only, may look like this:

[mysqld]
max_binlog_size = 512M
binlog_space_limit = 15G

One thing that could be still improved about this functionality is to make this variable dynamic. But to stay consistent with the one for relay logs, it would be best if the upstream makes this one dynamic first ?

Best binlog rotation strategy?

There may be still cases where I think you would rather prefer to stay with a time-based limit. Let’s imagine a situation where the full backups are made every week, and the binlog_space_limit is set to hold an average total size of two weeks of writes, so all should be safe. However, some heavy maintenance tasks alter a large portion of the data, creating more binary logs in one day then usually it takes two weeks. This may also break the point in time recovery capability. So if possible, maybe better to give an ample supply for the disk partition and specify 8 days rotation via expire_logs_days or binlog_expire_logs_secondsinstead.

Do you like it?

I am very interested to find out if this feature was already noticed by the community and how useful you think it is. Your comments are more than welcome!

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