MySQL

For a long time, MyDumper has been in Max Bubenick’s personal GitHub repository. Now, we decided to move to a new MyDumper’s Organization as requested earlier this year by a user from the community. There were also two other reasons why we decided to ... [More] move it. The first one is related to how the project is evolving, and the second is that it will allow us to implement integrations to other projects. We can see the evolution of the project, noting the increase in commits of the last year: We tried to keep the release cycle every two months, focusing on closing as many bugs as possible and implementing new features requested. It was not an easy task, as lots of changes had to be implemented in mydumper and myloader engine to allow the new features to be developed.  Seeing the progress that has been done, we can encourage ourselves and expect to release version 1.0.1 next year. This will be a huge step for the project, as it will mean that MyDumper is mature enough to endeavor easily any export/import task. On the other hand, moving MyDumper to an Organization will allow us to create an official Docker image and it will also allow us to create a pipeline with CircleCI. Both are on the Community wish list but it will be also useful for current and future development members, as one of the most important and difficult tasks is related to testing because of the number of use cases that we have. We expect to see higher quality on releases, not just because of the quality of code, but also because the tests cover most of the uses of mydumper and myloader. Now MyDumper has its own merch, too. By shopping for the merch you contribute to the project. Learn more here. [Less]

AWS has just announced the general availability of Aurora MySQL 8 compatibility (known as Aurora Version 3). This is long awaited addition to RDS MySQL 8 and provides many of the new features that can be found in the open-source MySQL 8 community ... [More] version. For those unfamiliar with Amazon Aurora my Understanding AWS RDS Aurora Capabilities presentation from Percona Live 2021 provides a great introduction of the benefits of this managed service. There is a lot to digest and the Aurora User Guide provides details of the new features from the MySQL 8 community version, and of Aurora 3 new features, and feature differences or unsupported features. This AWS blog post also provides a general introduction. It is very easy to spin up a new Aurora MySQL 3.01.0 cluster in an existing environment containing existing Aurora clusters. After defining new cluster and instance parameter groups for the aurora-mysql8.0 family, or starting with the available default.aurora-mysql8.0 parameter groups, there are no other differences in aws rds create-db-cluster syntax, or using the AWS Console or Terraform syntax for example. Before considering a migration of an existing Aurora cluster, there is a lot of information around parameter changes (including inclusive language functionality), and those related status and CloudWatch Metrics changes. Yes, looking at the 29 ‘Removed from Aurora MySQL version 3′, 30 ‘This parameter applies to Aurora MySQL version 3 and higher’ and presently ‘Currently not available in Aurora MySQL version 3′ LOAD|SELECT S3 capabilities is important. There are new reserved words to be aware of, you will need to note how to take advantage of roles within the Aurora permissions model. Migrating an existing Aurora MySQL 2 cluster to Aurora 3 is a little more involved than specifying the snapshot-id. Seeing your restored Aurora 2 snapshot in an Aurora 3 cluster but with a status of incompatible-parameters is a good indication that more work is needed. While I will detail some of my experiences in a subsequent post, one helpful tip is found in those additional pages of the 5 rows of logs for your new cluster after all the error.log files, you will find an upgrade-prechecks.log file. This contains an extensive list of checks and warnings performed for the upgrade. Skipping to the end of the JSON will give you an idea of your errorCount, warningCount and noticeCount. Searching then for an object of “status”: “ERROR” will find the errorCount entries matching the count. Several other checks provide a “detectedProblems” section and a “level”: “Error” which would seem to be needed to be also corrected. There are a lot of checks between the INFORMATION_SCHEMA, InnoDB internal data dictionary and actual data/files on disk. You will also be presented with a nice long list of tables/columns using reserved words, as well as character set deprecations. At a more technical glance of the documentation, there is a key change in how internal temporary tables are created, and how this differs from writer and reader instances. Benchmarking your application in different configurations will definitely be recommended. Restoring an Aurora 2 cluster into Aurora 3 also took significantly more time; many hours; than a simple restore-db-cluster-from-snapshot you may be used to. While Terraform cluster creation timeouts need to be increased for global clusters, this time the default 1h30 timeout for an instance was also exceeded. While different features will benefit different consumers of your Aurora database, one of the most anticipated is CTEs. From the operations perspective, as a managed service Aurora offers a subset of community features. One great feature that is now available in Aurora 3 is binary log filtering, a simple long-lived option in MySQL land that will help replacing more complex functionality. This is a great and long awaited version release for Aurora. [Less]

This video tutorial describes how to install Galera Manager in 15 minutes while also deploying a Galera Cluster in Amazon Web Services (AWS), all in an automated fashion!   WATCH THE VIDEO

As more people test, utilise and deploy Galera Manager, there is always room for support requests, and we see that at the Galera Manager Support Repository on Github. We encourage you to file requests, as they help with our roadmap as well! We have ... [More] had a feature request to run within a Docker container, and while we work on documentation and an official solution, we do have a temporary solution. Try it with Docker! We have had a feature request to be able to support Debian 11. The main stumbling block was that Galera Cluster didn’t have a Debian 11 release, but this has been fixed with our release of MySQL 8.0.26; one can expect this to be in a future release. For the best experience, we recommend you to install Galera Manager on a clean host, i.e. not on a host with existing running software. As you may already know, there are requirements for storage as logs can get large, for example. However, we have received requests to run apache2 instead of nginx. We have now ensured that we check for ports that are available (and will report and error, then fail if the ports required are in use), and we also have instructions on how one can run Apache and a Dockerized Galera Manager. All in, don’t forget to try Galera Manager and let us know how we can make the software better for you by filing an issue at the Galera Manager Support Repository on Github. [Less]

MySQL 8.0.27 introduced a new variable to control the maximum of parallel threads InnoDB can use for creating (sorting and building) secondary indexes: innodb_ddl_threads. This new variable is coupled with another new variable: ... [More] innodb_ddl_buffer_size. If you have fast storage and multiple CPU cores, tuning these variables can speed up secondary index creation. For this example, I used the airportdb database, and I added a secondary index to the largest table, booking. Let’s start with the default settings: SQL  alter table booking add index idx_2(flight_id, seat, passenger_id); Query OK, 0 rows affected (9 min 0.6838 sec) The default settings in MySQL 8.0.27 are: innodb_ddl_threads = 4 innodb_ddl_buffer_size = 1048576 innodb_parallel_read_threads = 4 The innodb_ddl_buffer_size is shared between all innodb_ddl_threads defined. If you increase the amount of threads, I recommend that you also increase the buffer size. To find the best values for these variables, let’s have a look at the amount of CPU cores: SQL  select count from information_schema.INNODB_METRICS where name = 'cpu_n'; +-------+ | count | +-------+ | 16 | +-------+ We have then 16 cores to share. As my machine as plenty of memory, I will allocate 1GB for the InnoDB DDL buffer. SQL  SET innodb_ddl_threads = 8; SQL  SET innodb_parallel_read_threads = 8; SQL  SET innodb_ddl_buffer_size = 1048576000; SQL  alter table booking add index idx_2(flight_id, seat, passenger_id); Query OK, 0 rows affected (3 min 9.1862 sec) Wow ! This is a nice improvement ! This is a table summary to see the impact of these variables: ddl buffer sizeddl threadsparallel read theadsexecution time1048576449 min 0.6838 sec104857600884 min 8.3601 sec1048576000883 min 9.1862 sec104857600016163 min 7.4079 sec10485760001683 min 4.1161 sec10485760001243 min 8.7854 sec10485760004123 min 5.9497 sec1048576000443 min 12.2435 sec2097152000442 min 43.6280 sec2097152000882 min 44.6516 secWe can see that with our system and our data, the best performance is achieved by increasing the buffer size used for the InnoDB DDL threads. Does that mean only the buffer size matter ? Not really. Of course it depends of your data, with only one thread but with a buffer of 2G, the execution time is 3 min 22.9617 sec. Also keeping increasing the buffer size won’t necessary improve the performance 😉 Please note that this feature also provides the possibility to build multiple indexes for a table in the same alter statement in parallel: SQL  alter table booking add index idx_2(flight_id, seat, passenger_id), add index idx_3(price, passenger_id), add index idx_4(seat,price); Query OK, 0 rows affected (10 min 17.8435 sec) With the default settings it takes 28 min 46.9949 sec. But the same query on a MySQL Database Service instance on OCI with 32 cores is executed in 2 min 11.6700 sec with a buffer size of 2G ! This new way of dealing with online DDL in InnoDB is a nice improvement, congrats InnoDB team ! And as usual, enjoy MySQL ! [Less]

Working on a real case scenario in a five node Percona XtraDB Cluster (PXC), we were forced to use wsrep_sync_wait = 1, because the app does reads-after-write and we send reads to all the nodes. We had the idea to leave some nodes in DESYNC mode to ... [More] reduce the flow control messages during peak load and expected to have a steadier write throughput keeping the read consistency. We decided to test Perconas’s new PXC Scheduler Handler which is an application that manages integration between ProxySQL and Galera/PXC (the scope is to maintain the ProxySQL mysql_server table, if a negative scenario occurs, like: failures, service degradation, and maintenance). However, we realized that when a node is in DESYNC mode, it is kicked out of the read hostgroup. That is why we asked Marco Tusa to implement this new feature which will remove the node from the read hostgroup if wsrep_local_recv_queue is higher than max_replication_lag. Environment 5 PXC nodes 1 ProxySQL server In db01, we run sysbench to simulate write traffic:sysbench /usr/share/sysbench/oltp_insert.lua --threads=50 --tables=100 --mysql-password= --mysql-user= --report-interval=5 --time=600 --tx-rate=0  runIn ProxySQL we run sysbench to simulate read-only traffic:sysbench /usr/share/sysbench/oltp_read_only.lua --tables=100 --mysql-password= --mysql-user= --mysql-host=127.0.0.1 run --threads=32 --report-interval=5 --time=600 --db-ps-mode=disable DESYNC Test The goal of this test is to see the differences between wsrep_desync ON/OFF and wsrep_sync_wait = 1. In the next graph, we are going to see both scenarios in the same graph, on the left of each graph when wsrep_desync is ON and on the right when it is OFF. As you can see there are decreases in the read traffic when DESYNC is OFF. It occurs in  the same period of time when the flow control messages are sent: This is expected and it is not new. The number of queries executed were:    DESYNC ON  queries:                             30561552 (50934.44 per sec.)     DESYNC OFF queries:                             28324704 (47195.67 per sec.)Basically, you can execute 8% of queries if flow control is not enabled. Consistency Test Now, we are going to simulate a scenario when the cluster receives CPU-intensive queries. We are going to execute in db03 (or any other node):for j in $(seq 1 40) ; do  for i in $(seq 1 250); do  echo "SELECT pad FROM sbtest.sbtest1 GROUP BY pad LIMIT 10" | mysql --password= --user= --host= ;  done > /dev/null 2>&1 &  doneIt starts 40 threads that execute 250 times the same group by query which is enough for our testing. And we are going to add a timestamp column on the sbtest1 to monitor the lag:ALTER TABLE sbtest.sbtest1 add  `tnow` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),Now we have this 4 scenario where we are going to monitor with:select timediff(tnow,current_timestamp(6))  from sbtest1 order by tnow desc limit 1And the status variable: wsrep_local_recv_queue. This will be the header of the tables:| timediff | query time |  wsrep_local_recv_queue |– With wsrep_desync=OFF and wsrep_sync_wait=1| 00:00:02.553139 | 2.69 sec |  237 | | 00:00:00.223150 | 0.26 sec |   72 |– With wsrep_desync=OFF and wsrep_sync_wait=0| 00:00:01.711082 | 1.74 sec |  183 | | 00:00:01.175446 | 1.20 sec |  112 |– With wsrep_desync=ON and wsrep_sync_wait=1| -00:00:00.021460 | 1.30 sec | 13542 | | -00:00:00.009098 | 1.28 sec | 13184 |– With wsrep_desync=ON and wsrep_sync_wait=0| -00:01:02.065854 | 0.46 sec | 635387 | | -00:01:02.882633 | 0.54 sec | 643742 |With wsrep_desync=OFF, the behavior is similar, and this means that the node needs to be on sync and then it checks wsrep_sync_wait.  With wsrep_desync=ON and wsrep_sync_wait=1, we can see that the query is delayed because it needs to apply the transaction in the apply queue. It is not the case when wsrep_sync_wait=0, which data is far behind the writer node, and the query is answered immediately. The two cases that matter are when wsrep_sync_wait=1, and both cases are read-consistent even if they show different timediff values, as the query time is measuring the flow control lag when wsrep_desync=OFF and the apply queue time when wsrep_desync=ON.  Cluster Behavior During Load Increases It is time to merge both test and simulate when ProxySQL spreads the CPU-intensive queries over the cluster. We are going to execute in ProxySQL the same script:for j in $(seq 1 40) ; do  for i in $(seq 1 250); do  echo "SELECT pad FROM sbtest.sbtest1 GROUP BY pad LIMIT 10" | mysql --password= --user= --host= ;  done > /dev/null 2>&1 &  doneIn the next graph we are going to see how the active connections on different nodes went up and down as the status of the node changed:  And when the script finished it went back to normal. In ProxySQL, you will see how the status of the servers changes to ONLINE to OFFLINE_SOFT and back to ONLINE because of PXC Scheduler Handler intervention, like this:proxysql> select hostgroup_id,hostname,status from runtime_mysql_servers order by hostgroup_id ='101' ; +--------------+---------------+--------------+ | hostgroup_id | hostname      | status       | +--------------+---------------+--------------+ | 101          | 10.127.70.242 | ONLINE       | | 101          | 10.127.70.243 | ONLINE       | | 101          | 10.127.70.244 | OFFLINE_SOFT | | 101          | 10.127.70.245 | ONLINE       | | 101          | 10.127.71.3   | ONLINE       | +--------------+---------------+--------------+ proxysql> select hostgroup_id,hostname,status from runtime_mysql_servers order by hostgroup_id ='101' ; +--------------+---------------+--------------+ | hostgroup_id | hostname      | status       | +--------------+---------------+--------------+ | 101          | 10.127.70.242 | ONLINE       | | 101          | 10.127.70.243 | ONLINE       | | 101          | 10.127.70.244 | ONLINE       | | 101          | 10.127.70.245 | OFFLINE_SOFT | | 101          | 10.127.71.3   | ONLINE       | +--------------+---------------+--------------+This can be reviewed also in PMM: This means that PXC Scheduler Handler is helping us to spread the load across DESYNC nodes, improving the response time for the read-only traffic. Architecture With traditional replication we had an abstract diagram like this: We didn’t have any production-ready option to guarantee Read Consistency on the Read Replicas. With Galera/PXC we don’t need the Replication Manager as it will be replaced with PXC Scheduler Handler:  We have the same amount of nodes with Read Consistency (thanks to wsrep_sync_wait) and a synced secondary writer node. What is missing, but not difficult to add, is a tool that monitors the WriterNodes, as in a failure scenario, we might want to keep not less than two synced nodes. Conclusion I think that Marco Tusa did a great job with the PXC Scheduler Handler which allowed us to implement this new Architecture that might help people that need to scale reads, need consistent write throughput, need consistent reads, and don’t want reads to affect the flow of the replication process. If you’re interested in learning more about Percona XtraDB Cluster and ProxySQL, be sure to check out Percona’s Training Services. We offer an advanced two-day hands-on tutorial for XtraDB Cluster, in addition to our one-day ProxySQL intensive. Contact us today to learn more! [Less]

In reply to a question about how to conditionally drop an index on a table in MySQL. It appears the syntax doesn’t exist. However, maybe it does and I missed it. If I did miss it, I’m sure somebody will let me know. However, I simply have a ... [More] dropIndexIfExists stored procedure for this type of database maintenance. Below is my dropIndexIfExists stored procedure: -- Conditionally drop the procedure. DROP PROCEDURE IF EXISTS dropIndexIfExists; -- Change the default semicolon delimiter to write a PSM -- (Persistent Stored Module) or stored procedure. DELIMITER $$ -- Create the procedure. CREATE PROCEDURE dropIndexIfExists ( pv_table_name VARCHAR(64) , pv_index_name VARCHAR(64)) BEGIN /* Declare a local variable for the SQL statement. */ DECLARE stmt VARCHAR(1024); /* Set a session variable with two parameter markers. */ SET @SQL := CONCAT('ALTER TABLE ',pv_table_name,'DROP INDEX ',pv_index_name); /* Check if the constraint exists. */ IF EXISTS (SELECT NULL FROM information_schema.statistics s WHERE s.index_schema = database() AND s.table_name = pv_table_name AND s.index_name = pv_index_name) THEN /* Dynamically allocated and run statement. */ PREPARE stmt FROM @SQL; EXECUTE stmt; DEALLOCATE PREPARE stmt; END IF; END; $$ -- Reset the default semicolon delimiter. DELIMITER ; You call the procedure like: CALL dropIndexIfExists('payment','idx_payment01'); As always, I hope this helps those looking for a solution. [Less]

I was working through some tutorials for my students and noticed that there was a change in how a WHERE clause must be written against the information_schema.table_constraints table. It might have been made in an earlier release, I actually hadn’t ... [More] checked it since 2014 when I wrote this early post on capturing MySQL Foreign Keys. You could use the following WHERE case insensitive clause: WHERE tc.constraint_type = 'foreign key' Now, you must use a case sensitive WHERE clause: WHERE tc.constraint_type = 'FOREIGN KEY' I’d love to know why but I can’t seem to find a note on the change. As always, I hope this helps those looking for an answer. [Less]

A year ago, I blogged about An Unprivileged User can Crash your MySQL Server.  At the time, I presented how to protect yourself against this problem without explaining how to generate a crash.  In this post, I am revisiting this vulnerability, not ... [More] giving the exploit yet, but presenting the fix.  Also, because the default configuration of Group Replication in 5.7 is still vulnerable (it is not in [Less]

New research combined with anecdotal information suggest the traditional kick-off to the holiday shopping season may have run its course.