FEED Validator

for Atom and RSS and KML

Congratulations!

This is a valid RSS feed.

Recommendations

This feed is valid, but interoperability with the widest range of feed readers could be improved by implementing the following recommendations.

line 23, column 0: description should not contain aria-level attribute (181 occurrences) [help]
```
<li aria-level="1">MySQL Shell: Download and install MySQL Shell from the of ...
```

Source: http://www.tusacentral.net/joomla/index.php/mysql-blogs?format=feed&type=rss

<?xml version="1.0" encoding="utf-8"?>

<?xml-stylesheet href="/joomla/plugins/system/jce/css/content.css?badb4208be409b1335b815dde676300e" type="text/css"?>
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom">
<channel>
<title>TusaCentral - MySQL Blogs</title>
<description><![CDATA[]]></description>
<link>http://www.tusacentral.net/joomla/index.php/mysql-blogs</link>
<lastBuildDate>Wed, 18 Jun 2025 20:53:03 +0000</lastBuildDate>
<generator>Joomla! - Open Source Content Management</generator>
<atom:link rel="self" type="application/rss+xml" href="http://www.tusacentral.net/joomla/index.php/mysql-blogs?format=feed&type=rss"/>
<language>en-gb</language>
<item>
<title>How to Set Up the Development Environment for MySQL Shell Plugins for Python</title>
<link>http://www.tusacentral.net/joomla/index.php/mysql-blogs/259-how-to-set-up-the-development-environment-for-mysql-shell-plugins-for-python</link>
<guid isPermaLink="true">http://www.tusacentral.net/joomla/index.php/mysql-blogs/259-how-to-set-up-the-development-environment-for-mysql-shell-plugins-for-python</guid>
<description><![CDATA[MySQL Shell is a powerful tool for managing MySQL databases, and one of its most exciting features is the ability to extend its functionality using plugins. If you're a Python developer, you can create custom plugins to automate tasks, add new commands, or<img src="http://www.tusacentral.net/joomla/images/stories/mysql-shell/dolphin-programming.jpeg" alt="dolphin programming" width="300" height="300" style="margin-left: 10px; margin-bottom: 10px; float: right;" /> integrate MySQL Shell with other tools. However debugging the python code could be cumbersome for mysql-shell given the python code requires some specific objects available only when running inside the shell.
In this blog post, we'll walk you through the steps to set up your development environment for creating MySQL Shell plugins in Python while able to modify and debug the mysql-shell itself.
<h2>Prerequisites</h2>
Before we dive into the setup, ensure you have the following installed on your system:
 
<ul>
<li aria-level="1">MySQL Shell: Download and install MySQL Shell from the official MySQL website.</li>
<li aria-level="1">Python 3.8 or later: MySQL Shell plugins require Python 3.8+. You can download Python from python.org.</li>
<li aria-level="1">A code editor: Use your favorite code editor, such as VS Code</li>
<li aria-level="1">Git: Optional, but useful for version control.</li>
</ul>
 
A directory structure as follow:
<pre>mysql_source/
├── boost
├── mysql-8.4.4  <-- directory where we compile the mysql server 
├── mysql-server <-- directory with the source code (from git or package)
├── mysql-shell  <-- directory with the mysql-shell code from git
├── mysql-shell-build-vs <-- directory where we build using Visual Studio
└── mysql-shell-plugins  <-- directory with the example plugin 
</pre>
 
<h2>Step 1: Collect and compile MySQL server code</h2>
If you want to clone the MySQL repository:
git clone <a href="https://github.com/mysql/mysql-server.git"></a><a href="https://github.com/mysql/mysql-server.git">https://github.com/mysql/mysql-server.git</a>
git fetch
git checkout tags/mysql-8.4.4 -b mysql-8.4.4 
 
Or just download the source code from the website for that specific release:
curl <a href="https://dev.mysql.com/get/Downloads/MySQL-8.4/mysql-8.4.4.tar.gz"></a><a href="https://dev.mysql.com/get/Downloads/MySQL-8.4/mysql-8.4.4.tar.gz">https://dev.mysql.com/get/Downloads/MySQL-8.4/mysql-8.4.4.tar.gz</a> - o mysql-8.4.4.tar.gz
 
In both cases once you have the code locally we need to configure and build the libraries.
For more information about how to compile and dependencies check <a href="https://dev.mysql.com/doc/refman/8.4/en/source-installation.html">here</a>
 
Then we go inside the compile directory (mysql-8.4.4) and configure it:
cmake /<path>/mysql_source/mysql-server -DCMAKE_INSTALL_PREFIX=/<path>/mysql_templates/mysql-8.4.4 -DWITH_DEBUG=1 -Dprotobuf_BUILD_SHARED_LIBS=ON
 
And we compile the needed libraries:
  cmake --build . --target mysqlclient
  cmake --build . --target mysqlxclient
 
There is no need to compile the whole server, unless you really want to. 
 
<h2>Step 2: Collect and compile MySQL Shell code</h2>
Once the server libs are prepared then go for mysql-shell.
Get the code:
git clone <a href="https://github.com/mysql/mysql-server.git"></a><a href="https://github.com/mysql/mysql-server.git">https://github.com/mysql/mysql-server.git</a>
git fetch
git checkout tags/8.4.4 -b 8.4.4
 
Now my advice is to always do a compilation outside the code editor to be sure we have all the needed components, for more info check <a href="https://github.com/mysql/mysql-shell/blob/master/INSTALL">here</a>
 
To configure:
cmake /<path>/mysql_source/mysql-shell -DMYSQL_SOURCE_DIR=/<path>/mysql_source/mysql-server/ -DMYSQL_BUILD_DIR=/<path>/mysql_source/mysql-8.4.4 -DHAVE_PYTHON=1 -DHAVE_V8=0 -DWITH_TESTS=1 -DCMAKE_BUILD_TYPE=Debug -DCMAKE_INSTALL_PREFIX=/<path>/mysql_source/mysql_shell_bin
 
If all went well you can check if the mysql-shell works properly running it from the destination path (in the example) /opt/mysql_source/mysql_shell_bin/bin/mysqlsh 
<h2>Step 3: Set Up a Python Virtual Environment</h2>
To avoid conflicts with other Python projects, it's a good idea to create a virtual environment for your MySQL Shell plugin development.
Open your terminal and navigate to your project directory.
Create a virtual environment:
python -m venv mysqlsh-plugin-env
Activate the virtual environment:
 
On macOS/Linux:
source mysqlsh-plugin-env/bin/activate
 
Verify that the virtual environment is active. Your terminal prompt should now show the name of the virtual environment.
 
<h2>Step 4: For lazy people (as myself) download and use existing plugin demos.</h2>
Our dear friend <a href="https://lefred.be/">Lefred </a>has built a very nice suite that I normally use as a starting point.
Get the code, go to your mysql_source directory and:
git clone <a href="https://github.com/lefred/mysqlshell-plugins.git"></a><a href="https://github.com/lefred/mysqlshell-plugins.git">https://github.com/lefred/mysqlshell-plugins.git</a> 
 
In mysql-shell at the moment you can load the plugin at startup only if they are located in the sub-directory ~/.mysqlsh/plugins or where the lib/mysqlsh/plugins is located. See <a href="https://bugs.mysql.com/bug.php?id=117569">FR here</a> for a more convenient way to dynamically pass the path to browse. 
Anyhow for now and the purpose of this blog let us use a a trick and create a symlink:
mkdir ~/.mysqlsh/plugins
ln -s <path>/mysql_source/mysql-shell-plugins/demo ~/.mysqlshell/plugins/demo
 
Ok now our code side is set. 
Download and configure VisualStudio.
Add few extension, mine are the following:
<img src="http://www.tusacentral.net/joomla/images/stories/mysql-shell/1.jpg" alt="1" width="473" height="700" />
 
Then open the directory where you have clone the mysql-shell code inside VS and save it as workspace:
<img src="http://www.tusacentral.net/joomla/images/stories/mysql-shell/2.jpg" alt="2" width="285" height="616" />
 
Then click again on the File and add Folder to workspace and add mysql-shell-plugin/demo directory. 
 
Now we need to configure the cmake side of the project:
<img src="http://www.tusacentral.net/joomla/images/stories/mysql-shell/3.jpg" alt="3" width="501" height="318" />
 
Click on the icon on the left first then on the settings wheel to open the specific menu. 
I suggest you to change things at workspace level choosing the workspace tab:
<img src="http://www.tusacentral.net/joomla/images/stories/mysql-shell/4.jpg" alt="4" width="280" height="103" />
 
Look for the Cmake: Build Directory option and set it to /<add full path>/mysql_source/mysql-shell-build-vs as outlined before. 
Then look for the Cmake: Configure Settings option and click on edit settings.json and set as follow: "cmake.configureSettings": {
"MYSQL_SOURCE_DIR":"/<add full path>/mysql_source/mysql-server/",
"MYSQL_BUILD_DIR":"/<add full path>/mysql_source/mysql-8.4.4",
"HAVE_PYTHON":true,
"BUNDLED_POLYGLOT_DIR":0,
"WITH_TESTS":true,
"CMAKE_BUILD_TYPE":"Debug",
},
The above will create the Cmake configuration and structure in /<add full path>/mysql_source/mysql-shell-build-vs. 
 
Once this is done compile the project clicking on the small icon close to build
<img src="http://www.tusacentral.net/joomla/images/stories/mysql-shell/5.jpg" alt="5" width="471" height="283" />
This will build the whole project, sit down and relax while compilation will complete. 
 
Once this is complete click on the run-and-debug  icon: <img src="http://www.tusacentral.net/joomla/images/stories/mysql-shell/6.jpg" alt="6" width="344" height="441" />
Then click on create a launch.json
Choose mysql-shell-vs:
<img src="http://www.tusacentral.net/joomla/images/stories/mysql-shell/7.jpg" alt="7" width="627" height="163" />
 
<img src="http://www.tusacentral.net/joomla/images/stories/mysql-shell/8.jpg" alt="8" width="581" height="386" />
Then pick C/C++ gdb Attach.
This will open a json file, we are interested in the values inside the “configurations”, it should be something like this:       {
            "name": "(gdb) Launch",
            "type": "cppdbg",
            "request": "launch",
            "program": "/<add full path>/mysql_source/mysql-shell-build-vs/bin/mysqlsh",
            "args": [
                "--verbose=1"
            ],
            "stopAtEntry": false,
            "cwd": "/<add full path>/mysql_source/mysql-shell-build-vs/",
            "environment": [{"name": "PYTHONPATH", "value": "PATH to your python directory"}],
            "externalConsole": false,
            "MIMode": "gdb",
            "setupCommands": [
                {
                    "description": "Enable pretty-printing for gdb",
                    "text": "-enable-pretty-printing",
                    "ignoreFailures": true
                },
                {
                    "description": "Set Disassembly Flavor to Intel",
                    "text": "-gdb-set disassembly-flavor intel",
                    "ignoreFailures": true
                }
            ]
        }
After that you need to add the Python debugger information:
     <code> {</code>
<code>            "name": "plugin-p",</code>
<code>            "type": "debugpy",</code>
<code>            "request": "attach",</code>
<code>            "connect": {</code>
<code>                "host": "localhost",</code>
<code>                "port": 5678</code>
<code>            },</code>
<code>            "pathMappings": [</code>
<code>                {</code>
<code>                    "localRoot": "${workspaceFolder}",</code>
<code>                    "remoteRoot": "."</code>
<code>                }</code>
<code>            ],</code>
<code>            "justMyCode": false</code>
<code>        },</code>
As you can see what we will do is to attach the debugger to a process that will spin up inside the code.
 
Save the file, and in the left panel you will now have the option to run and debug using both debuggers. 
Before running the debug let us do a couple of more things.
Click on the project explorer and open the init.py file in the demo directory 
 
<img src="http://www.tusacentral.net/joomla/images/stories/mysql-shell/9.jpg" alt="9" width="313" height="446" />
 
Before the instruction: import mysqlsh, add this text:
import debugpy
debugpy.listen(("localhost", 5678))
print("Waiting for debugger attach...")
debugpy.wait_for_client()
print("Debugger attached.")
If the module debugpy is not installed do it with:
pip install debugpy
Then before the last line add:
import mysqlsh
shell = mysqlsh.globals.shell
 
This to create the shell object that is available only internally to the mysql-shell.
 
Now let us add a couple of breakpoints one in the mysql-shell code and another inside the python code.
In mysql_shell.cc look for:
  for (const auto &dir : plugin_directories) {
    get_plugins(file_list, dir, true);
  }
In mysql-shell 8.4.4 is at line 964.
Put a breakpoint on get_plugins
Then open oracle8ball.py and put a breakpoint on the last line:
 print(answers[index]) 
 
Run the C/C++ debugger… 
The execution will stop and in the debug left panel you will see the variable informations plus the debug toolbar:
<img src="http://www.tusacentral.net/joomla/images/stories/mysql-shell/10.jpg" alt="10" width="778" height="307" />
This is standard debug for C/C++ projects … Let us click to proceed.
Execution will continue until loading the plugins then will stop. It will hang because the line we have inserted:
<samp>debugpy.wait_for_client()</samp>
 Now run the Python debugger it will connect to the dbugger we defined in the code allowing the process to continue.
In the terminal window the mysql-shell prompt will appear but not only:
<img src="http://www.tusacentral.net/joomla/images/stories/mysql-shell/10-1.jpg" alt="10 1" width="556" height="313" />
We can see that the python debugger is now also attached. 
 
Let us move to python and execute the demo.oracle8ball()
<img src="http://www.tusacentral.net/joomla/images/stories/mysql-shell/10-2.jpg" alt="10 2" width="524" height="337" />
 
Execution is suspended and our debugger stop at our breakpoint:
<img src="http://www.tusacentral.net/joomla/images/stories/mysql-shell/11.jpg" alt="11" width="786" height="321" />
 
Please note two things, the first is that also in this case we can see the whole set of information related to local and global variables including the shell object and the other is that the debugger menu shows that now the navigation button refers to the python debugger. 
 
This level of integration will give anyone a more friendly environment where to develop your mysql-shell extensions. This is particularly important for the ones who wish to standardize and simplify the DBA operation or to give developers easy access to independently and safely perform actions on the databases. 
<h2>Tips for Developing MySQL Shell Plugins</h2>
Use the MySQL Shell API: MySQL Shell provides a rich API for interacting with MySQL databases. Refer to the official documentation for details.
<ul>
<li aria-level="1">Test Incrementally: Test your plugin functions frequently to catch errors early.</li>
<li aria-level="1">Leverage Python Libraries: You can use any Python library in your plugin, so take advantage of the vast Python ecosystem.</li>
</ul>
<h1>Conclusion</h1>
Setting up a development environment for MySQL Shell plugins in Python is straightforward. With the right tools and a bit of Python knowledge, you can create powerful extensions to enhance MySQL Shell's functionality. Whether you're automating database tasks or integrating with other systems, MySQL Shell plugins offer endless possibilities.
 
Happy coding!
 
]]></description>
<category>MySQL</category>
<pubDate>Thu, 27 Feb 2025 15:39:47 +0000</pubDate>
</item>
<item>
<title>MySQL latest performance review</title>
<link>http://www.tusacentral.net/joomla/index.php/mysql-blogs/258-mysql-latest-performance-review</link>
<guid isPermaLink="true">http://www.tusacentral.net/joomla/index.php/mysql-blogs/258-mysql-latest-performance-review</guid>
<description><![CDATA[This article is focused on describing the latest performance benchmarking executed on the latest release of MySQL and Percona. <img src="http://www.tusacentral.net/joomla/images/stories/mysql_performance_january_2025/dolphin_jumping_outside.jpeg" alt="dolphin jumping outside" width="286" height="286" style="margin-left: 10px; float: right;" />
In this set of tests I have used the machine described <a href="https://github.com/Tusamarco/blogs/blob/master/testmachine/testmachine.md">here</a>.  
<h3>Assumptions</h3>
There are many ways to run tests, and we know that results may vary depending on how you play with many factors, like the environment or the MySQL server settings. However, if we compare several versions of the same product on the same platform, it is logical to assume that all the versions will have the same “chance” to behave well or badly unless we change the MySQL server settings. 
Because of this, I ran the tests changing only things in a consistent way, with the intent to give the same opportunity to each solution., with the clear assumption that if you release your product based on the defaults, that implies you had tested with them and consider them the safest for generic use. 
I also applied some <a href="https://github.com/Tusamarco/blogs/blob/master/testmachine/mysql_configuration.md">modifications </a>and ran the tests again to see how optimization would impact performance. 
<h3>What tests do we run?</h3>
High level, I run one set of test:
<ul>
<li aria-level="1">TPC-C (<a href="https://www.tpc.org/tpcc/"></a><a href="https://www.tpc.org/tpcc/">https://www.tpc.org/tpcc/</a>) like </li>
</ul>
The full methodology and test details can be found <a href="https://github.com/Tusamarco/benchmarktools/blob/main/docs/plan.md">here</a>, while actual commands are available:
<ul>
<li aria-level="1"><a href="https://github.com/Tusamarco/benchmarktools/blob/main/software/fill_sysbench_map.sh">Sysbench</a></li>
<li aria-level="1"><a href="https://github.com/Tusamarco/benchmarktools/blob/main/software/fill_tpcc_map.sh">TPC-C</a> </li>
</ul>
 
<h3>Why do I only run TPC-C tests?  </h3>
Well I am, normally, more interested in testing scenarios that are closer to reality than a single function test as we normally do with sysbench. 
This is it, while it is not possible to get the perfect benchmark test fitting all real usage, we need to keep in mind the rule of 80%. 
If you use MYSQL/InnoDB I expect that you have an OLTP kind of traffic, more than Key/Value or else. Given that while testing the single function, as we do with sysbench, may be useful to identify regression points or so. To get the wide scenario, TPC-C is a better way to go, given it implies not only a more intense write load, TPC-C test is 50/50 r/w, but also a schema structure with relations, foreign keys and constraints. In short, it is closer to the common use of a relational database management system.
 
<h2>Results</h2>
The tests done have two different kinds of isolation levels. <a href="http://www.tusacentral.net/joomla/#Repeatable_reads">Repeatable Read</a> and <a href="http://www.tusacentral.net/joomla/#Read_committed">Read Committed</a>. The first is the default in MySQL/InnoDB, while the second is the default in many other very well known RDBMS. 
 
As usual an image is more descriptive than many words:
<a href="http://www.tusacentral.net/joomla/images/stories/mysql_performance_january_2025/TPC-C_Read_Committed__Operations_sec.png" target="_parent"><img src="http://www.tusacentral.net/joomla/images/stories/mysql_performance_january_2025/TPC-C_Read_Committed__Operations_sec.png" alt="TPC C Read Committed Operations sec" width="600" height="322" /></a>
<a href="http://www.tusacentral.net/joomla/images/stories/mysql_performance_january_2025/TPC-c_Repeatable_Read__Operations_sec.png"><img src="http://www.tusacentral.net/joomla/images/stories/mysql_performance_january_2025/TPC-c_Repeatable_Read__Operations_sec.png" alt="TPC c Repeatable Read Operations sec" width="600" height="322" /></a>
You can also compare these trends (not the values) with the previous results published <a href="http://www.tusacentral.net/joomla/index.php/mysql-blogs/256-sakila-where-are-you-going">here</a>.
Let us comment a bit on these images. 
The first comment we should make is that nowadays our systems must be ready to scale. Period, no discussion, also doing benchmarks up to 1024 threads is not enough. In reality we have 4000 or even more connections, given that doing benchmarking exercises and stopping the load at 128 threads or lower, makes no sense. Here it is quite clear that doing something like that could be very misleading. 
Old MySQL versions are still faster than newer with low level of concurrency, but they do not scale. So if I stop my tests at 64 or 128 threads I will conclude that MySQL 5.7 is always better than newer versions, while if I go on loading, I can see that old version performance drop after 64 concurrent threads. 
 
The second comment is that while in previous tests (see mentioned article) we saw that newer versions were not performing better or even consistently. With the latest releases MySQL had not only stabilized the server behaviour, but done significant fixes to the performance issues it had. 
If we remove the 5.7 version from the graphs we can see clearer what is going on:
<a href="http://www.tusacentral.net/joomla/images/stories/mysql_performance_january_2025/TPC-C_Read_Committed__Operations_sec_no57.png"><img src="http://www.tusacentral.net/joomla/images/stories/mysql_performance_january_2025/TPC-C_Read_Committed__Operations_sec_no57.png" alt="TPC C Read Committed Operations sec no57" width="600" height="322" /></a>
<a href="http://www.tusacentral.net/joomla/images/stories/mysql_performance_january_2025/TPC-c_Repeatable_Read__Operations_sec_no57.png"><img src="http://www.tusacentral.net/joomla/images/stories/mysql_performance_january_2025/TPC-c_Repeatable_Read__Operations_sec_no57.png" alt="TPC c Repeatable Read Operations sec no57" width="600" height="322" /></a>
If you notice the servers lines after 32 threads and especially after 64 threads diverge and we have two groups one see Percona and MySQL 8.0.40 in the lower set while Percona and MySQL 8.4 and 9.x are in the upper group. 
This is quite great news and very nice to see, because if I have to read it in a way I see a positive sign indicating how Oracle/MySQL is progressively resolving some performance issues and gaining ground again. 
 <a href="http://www.tusacentral.net/joomla/static/data_Tpccallno5.7_.html">see also dynamic graphs</a>
<h1>Conclusions</h1>
The tests performed using TPC-C like tests confirms the initial finding of my colleague <a href="https://www.percona.com/blog/mysql-8-4-3-and-9-1-0-major-performance-gains-revealed/">here</a>, and give us a picture that is more positive of what we had. At the same time they indicate that the race to get better performance is open again, and I am intrigued to see what will come next.
For now we can say that MySQL 9.2 is the better performing MySQL version currently available, for its stability and scalability. 
Great job!  
 ]]></description>
<category>MySQL</category>
<pubDate>Tue, 28 Jan 2025 16:35:38 +0000</pubDate>
</item>
<item>
<title>How to migrate a production database to Percona Everest (MySQL) using Clone</title>
<link>http://www.tusacentral.net/joomla/index.php/mysql-blogs/257-how-to-migrate-a-production-database-to-percona-everest-mysql-using-clone</link>
<guid isPermaLink="true">http://www.tusacentral.net/joomla/index.php/mysql-blogs/257-how-to-migrate-a-production-database-to-percona-everest-mysql-using-clone</guid>
<description><![CDATA[The aim of this long article is to give you the instructions and tools to migrate your production database, from your current environment to a solution based on <a href="https://docs.percona.com/everest/index.html">Percona Everest (MySQL)</a>.
Nice, you decided to test Percona Everest, and you found that it is the tool you were looking for to manage your private DBaaS. For sure the easiest part will be to run new environments to get better understanding and experience on how the solution works. However, the day when you will look to migrate your existing environments will come. What should you do?
Prepare a plan! In which the first step is to understand your current environment. 
 When I say understand the current environment, I mean that you need to have a clear understanding of:
<ul>
<li style="font-weight: 400;" aria-level="1">the current dimensions (CPU/Memory/Disk utilization)</li>
<li style="font-weight: 400;" aria-level="1">the way it is accessed by the application, what kind of query you have, is Read or Write intensive, do you have pure OLTP or also some analytic, any ELT processing</li>
<li style="font-weight: 400;" aria-level="1">the way it is used, constant load or by time of the day or by day of the year? Do you have any peak ie: Black Friday </li>
<li style="font-weight: 400;" aria-level="1">what is the RPO/RTO, do you need a Disaster Recovery site? </li>
<li style="font-weight: 400;" aria-level="1">Who is accessing your database, and why. </li>
<li style="font-weight: 400;" aria-level="1">What MySQL version are you using, is it compatible with Percona Everest MySQL versions? </li>
</ul>
Once you have all the information, it is time to perform a quick review if the solution could fit or not, for this step, given its complexity, I suggest you contact Percona and get help from our experts to take the right decision.   
From the above process you should come with few clear indications such as:
<ul>
<li style="font-weight: 400;" aria-level="1">Needed resources</li>
<li style="font-weight: 400;" aria-level="1">It is more read, write or 50/50</li>
<li style="font-weight: 400;" aria-level="1">The level of recovery I need</li>
</ul>
The first thing to do is to calculate the optimal configuration. For this you can help yourself with the <a href="https://github.com/Tusamarco/mysqloperatorcalculator">mysqloperatorcalculator</a>. The tool will give you the most relevant variables to set for MySQL, configuration that you will be able to pass to Percona Everest while creating the new cluster.  
To install <a href="https://docs.percona.com/everest/install/SetupPrereqs.html">Percona Everest see here</a>
<h2>Create the new cluster</h2>
It is now time to open our Percona Everest console and start the adventure. 
<a href="https://www.percona.com/blog/wp-content/uploads/2024/09/everest1-a.jpg"><img src="http://www.tusacentral.net/joomla/images/stories/how_to_migrate_toPeverest/everest1-a.jpg" alt="everest1 a" width="1024" height="169" class="alignnone size-large wp-image-98244" /></a>
In the basic information step, look at the supported versions for Database Server
<a href="https://www.percona.com/blog/wp-content/uploads/2024/09/everest2-a.jpg"><img src="http://www.tusacentral.net/joomla/images/stories/how_to_migrate_toPeverest/everest2-a.jpg" alt="everest2 a" width="1024" height="478" class="alignnone size-large wp-image-98246" /></a>
This version and the source version must match to safely use the CLONE plugin. Note that you cannot clone between MySQL 8.0 and MySQL 8.4 but can clone within a series such as MySQL 8.0.37 and MySQL 8.0.42. Before 8.0.37, the point release number also had to match, so cloning the likes of 8.0.36 to 8.0.42 or vice-versa is not permitted
It is now time to set the resources, the value of them should come from the analysis previously performed.
<a href="https://www.percona.com/blog/wp-content/uploads/2024/09/everest3-a.jpg"><img src="http://www.tusacentral.net/joomla/images/stories/how_to_migrate_toPeverest/everest3-a.jpg" alt="everest3 a" width="1024" height="446" class="alignnone size-large wp-image-98248" /></a>
Given that choose 1 (one) node, then Custom and feel the fields as appropriate.
<a href="https://www.percona.com/blog/wp-content/uploads/2024/09/everest4-a.jpg"><img src="http://www.tusacentral.net/joomla/images/stories/how_to_migrate_toPeverest/everest4-a.jpg" alt="everest4 a" width="1024" height="481" class="alignnone size-large wp-image-98250" /></a>
In advance configuration add the IP(s) you want to allow to access the cluster. You must add the IP of the source, IE 18.23.4.12/32.  
In the set database engine parameters add the values (for MySQL only) that the mysqloperatorcalculator is giving you. Do not forget to have the mysqld section declaration.
For example, in our case I need to calculate the needed values for a MySQL server with 4 CPU 8GB ram serving OLTP load, once you have the mysqloperatorcalculator tool running:
<pre class="lang:sh decode:true">$ curl -i -X GET -H "Content-Type: application/json" -d '{"output":"human","dbtype":"pxc", "dimension": {"id": 999, "cpu":4000,"memory":"8G"}, "loadtype": {"id": 3}, "connections": 300,"mysqlversion":{"major":8,"minor":0,"patch":36}}' http://127.0.0.1:8080/calculator</pre>
You will get a set of values that after cleanup looks like:
<pre class="lang:sh decode:true">[mysqld]
binlog_cache_size = 262144
binlog_expire_logs_seconds = 604800
binlog_format = ROW
… snip …
loose_wsrep_sync_wait = 3
loose_wsrep_trx_fragment_size = 1048576
loose_wsrep_trx_fragment_unit = bytes
</pre>
Add the text in the TEXTAREA for the database parameters.
<a href="https://www.percona.com/blog/wp-content/uploads/2024/09/everest5-a.jpg"><img src="http://www.tusacentral.net/joomla/images/stories/how_to_migrate_toPeverest/everest5-a.jpg" alt="everest5 a" width="1024" height="354" class="alignnone size-large wp-image-98252" /></a>
Enable monitoring if you like then click on Create database.
Once ready you will have something like this:
<a href="https://www.percona.com/blog/wp-content/uploads/2024/09/everest6.jpg"><img src="http://www.tusacentral.net/joomla/images/stories/how_to_migrate_toPeverest/everest6.jpg" alt="everest6" width="1024" height="158" class="alignnone size-large wp-image-98253" /></a>
Or from shell
<pre class="lang:sh decode:true">$ kubectl get pxc
NAME ENDPOINT STATUS PXC PROXYSQL HAPROXY AGE
test-prod1 xxx ready 1 1 2m49s
$ kubectl get pods
NAME READY STATUS RESTARTS AGE
percona-xtradb-cluster-operator-fb4cf7f9d-97rfs 1/1 Running 0 13d
test-prod1-haproxy-0 3/3 Running 0 106s
test-prod1-pxc-0 2/2 Running 0 69s
</pre>
We are now ready to continue our journey.
<h2>Align the system users</h2>
This is a very important step. Percona Everest use the Percona Operator who will create a <a href="https://docs.percona.com/percona-operator-for-mysql/pxc/users.html">set of system users</a> in the database, and these users must be present also in the source with the same level of GRANTS, otherwise after the clone phase is terminated, the system will not work correctly.
Keep in mind Percona Everest will create the users with some generated password, these passwords may or may not fit your company rules or be simply too crazy. Do not worry you will be able to change them. For now, let's see what the system has generated. 
<a href="https://www.percona.com/blog/wp-content/uploads/2024/09/everest8.jpg"><img src="http://www.tusacentral.net/joomla/images/stories/how_to_migrate_toPeverest/everest8.jpg" alt="everest8" width="1024" height="234" class="alignnone size-large wp-image-98255" /></a>
To see how to access the cluster click on the “^” top right, it will expand the section. User is “root” now unhide the password… Ok I don’t know you, but I do not like it at all. Let me change to the password I have already defined for root in the source. 
Percona Everest is not (yet) allowing you to modify the system users’ passwords from the GUI, but you can do it from command line:
<pre class="lang:sh decode:true">DB_NAMESPACE=namespace';
DB_NAME='cluster-name';
USER='user';
PASSWORD='new-password';
kubectl patch secret everest-secrets-"$DB_NAME" -p="{"stringData":{"$USER": "$PASSWORD"}}" -n "$DB_NAMESPACE"
</pre>
Before changing let us check what password we have also for the other system users. 
About system users in Operator for MySQL (PXC based) we have the following:
<ul>
<li style="font-weight: 400;" aria-level="1">root</li>
<li style="font-weight: 400;" aria-level="1">operator</li>
<li style="font-weight: 400;" aria-level="1">xtrabackup</li>
<li style="font-weight: 400;" aria-level="1">monitor</li>
<li style="font-weight: 400;" aria-level="1">replication</li>
</ul>
To get all of them use command line:
<pre class="lang:sh decode:true">DB_NAMESPACE='namespace'; DB_NAME='cluster-name'; kubectl get secret everest-secrets-"$DB_NAME" -n "$DB_NAMESPACE" -o go-template='{{range $k,$v := .data}}{{"### "}}{{$k}}{{"| pw: "}}{{$v|base64decode}}{{"nn"}}{{end}}'|grep -E 'operator|replication|monitor|root||xtrabackup'
### monitor| pw: $&4fwdoYroBxFo#kQi
### operator| pw: NNfIUv+iL+J!,.Aqy94
### replication| pw: Rj89Ks)IVNQJH}Rd
### root| pw: f~A)Nws8wD<~%.j[
### xtrabackup| pw: h)Tb@ij*0=(?,?30
</pre>
Now let me change my root user password:
<pre class="lang:sh decode:true">$ DB_NAMESPACE='namespace'; DB_NAME='cluster-name'; USER='root'; PASSWORD='root_password'; kubectl patch secret everest-secrets-"$DB_NAME" -p="{"stringData":{"$USER": "$PASSWORD"}}" -n "$DB_NAMESPACE"</pre>
Now if I collapse and expand again (forcing a reload of the section):
<a href="https://www.percona.com/blog/wp-content/uploads/2024/09/everest9.jpg"><img src="http://www.tusacentral.net/joomla/images/stories/how_to_migrate_toPeverest/everest9.jpg" alt="everest9" width="853" height="191" class="alignnone size-full wp-image-98256" /></a>
My root user password is aligned with the one I pushed. 
As we have seen we have to decide what to do, so first thing is to check if our SOURCE has or not the users defined. If not, then it is easy we will just grab the users from the newly generated cluster and recreate them in the SOURCE.
To do so we will query the source database:
<pre class="lang:sh decode:true">(root@localhost) [(none)]>select user,host,plugin from mysql.user order by 1,2;
+----------------------------+---------------+-----------------------+
| user | host | plugin |
+----------------------------+---------------+-----------------------+
| app_test | % | mysql_native_password |
| dba | % | mysql_native_password |
| dba | 127.0.0.1 | mysql_native_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.pxc.internal.session | localhost | caching_sha2_password |
| mysql.pxc.sst.role | localhost | caching_sha2_password |
| mysql.session | localhost | caching_sha2_password |
| mysql.sys | localhost | caching_sha2_password |
| operator | % | caching_sha2_password |
| pmm | 127.0.0.1 | caching_sha2_password |
| pmm | localhost | caching_sha2_password |
| replica | 3.120.188.222 | caching_sha2_password |
| root | localhost | caching_sha2_password |
+----------------------------+---------------+-----------------------+
</pre>
We are lucky and there is nothing really conflicting, so we can export and create the users inside the SOURCE. To do so you can use <a href="https://docs.percona.com/percona-toolkit/pt-show-grants.html">pt-show-grants</a>:
<pre class="lang:sh decode:true">pt-show-grants --host cluster-end-point --port 3306 --user dba --password dba --only 'monitor'@'%','xtrabackup'@'%',operator@'%',replication@'%',root@'%</pre>
This will generate an SQL output that you can run on the source. Please review it before running to be sure it will be safe for you to run it.
Once applied to source we will have:
<pre class="lang:sh decode:true">+----------------------------+---------------+-----------------------+
| user | host | plugin |
+----------------------------+---------------+-----------------------+
| app_test | % | mysql_native_password |
| dba | % | mysql_native_password |
| dba | 127.0.0.1 | mysql_native_password |
| monitor | % | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.pxc.internal.session | localhost | caching_sha2_password |
| mysql.pxc.sst.role | localhost | caching_sha2_password |
| mysql.session | localhost | caching_sha2_password |
| mysql.sys | localhost | caching_sha2_password |
| operator | % | caching_sha2_password |
| pmm | 127.0.0.1 | caching_sha2_password |
| pmm | localhost | caching_sha2_password |
| replica | 3.120.188.222 | caching_sha2_password |
| replication | % | caching_sha2_password |
| root | % | caching_sha2_password |
| root | localhost | caching_sha2_password |
| xtrabackup | % | caching_sha2_password |
+----------------------------+---------------+-----------------------+
</pre>
The last step to do about the users, is to create a specific user to use for the migration. We will use it to perform the clone and after that we will remove it. 
On SOURCE:
<pre class="lang:mysql decode:true">create user migration@'%' identified by 'migration_password';
grant backup_admin on *.* to migration@'%'
</pre>
On RECEIVER (new cluster):
<pre class="lang:mysql decode:true"> create user migration@'%' identified by 'migration_password';
GRANT SYSTEM_USER, REPLICATION SLAVE, CONNECTION_ADMIN, BACKUP_ADMIN, GROUP_REPLICATION_STREAM, CLONE_ADMIN,SHUTDOWN ON *.* to migration@'%';
</pre>
<h2>Let us go CLONING </h2>
First, is the CLONE plugin already there?
Discover this querying the two systems:
<pre class="lang:mysql decode:true">SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME = 'clone';
SOURCE:
+-------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+-------------+---------------+
| clone | ACTIVE |
+-------------+---------------+
</pre>
<pre class="lang:mysql decode:true">RECEIVER:
mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME = 'clone';
Empty set (0.42 sec)
</pre>
RECEIVER doesn’t have the plugin active. Let us activate it:
<pre class="lang:mysql decode:true">INSTALL PLUGIN clone SONAME 'mysql_clone.so';</pre>
Warning! If your source is behind a firewall, you need to allow the RECEIVER to connect, to get the IP of the RECEIVER just do:
<pre class="lang:sh decode:true">kubectl -n namespace exec mysqlpodname -c pxc -- curl -4s ifconfig.me</pre>
This will return an IP, you need to add that IP to the firewall and allow the access. Keep this value aside, you will also need later to setup the asynchronous replication. 
 
Are we ready? Not really, there is a caveat here. If we clone with the Galera library active, the cloning will fail. 
To have it working we must:
<ol>
<li style="font-weight: 400;" aria-level="1">disable the wsrep provider</li>
<li style="font-weight: 400;" aria-level="1">stop operator probes to monitor the pod</li>
<li style="font-weight: 400;" aria-level="1">connect directly to the pod to run the operation and to monitor it. </li>
</ol>
To do the above, on the receiver, we can:
<ol>
<li style="font-weight: 400;" aria-level="1">add wsrep_provider=none to the configuration</li>
<li style="font-weight: 400;" aria-level="1">as soon as the pod is up (monitor the log) issue from command line the command: 
<pre class="lang:sh decode:true">kubectl -n namespace exec pod-name -c pxc -- touch /var/lib/mysql/sleep-forever</pre>
</li>
<li style="font-weight: 400;" aria-level="1">Connect to the pod using: 
<pre class="lang:sh decode:true">kubectl exec --stdin --tty <pod name> -n <namespace> -c pxc -- /bin/bash</pre>
</li>
</ol>
During the time of the operations, the cluster will not be accessible from its end point and HAProxy pod will result down as well, all this is OK, don’t worry.
<h3>Let us go…</h3>
While monitoring the log and pod:
<pre class="lang:sh decode:true">kubectl logs pod-name --follow -c pxc
kubectl get pods
</pre>
<a href="https://www.percona.com/blog/wp-content/uploads/2024/09/everest10-a.jpg"><img src="http://www.tusacentral.net/joomla/images/stories/how_to_migrate_toPeverest/everest10-a.jpg" alt="everest10" width="1024" height="125" class="alignnone size-large wp-image-98258" /></a>
Once you click continue and then edit database, the pod will be restarted.
Wait for the message in the log:
<pre class="lang:sh decode:true">[MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.36-28.1'  socket: '/tmp/mysql.sock'  port: 3306  Percona XtraDB Cluster (GPL), Release rel28, Revision bfb687f, WSREP version 26.1.4.3.
2024-07-29T17:22:11.933714Z 0 [System] [MY-013292] [Server] Admin interface ready for connections, address: '10.1.68.172'  port: 33062</pre>
As soon as you see it, run the command to prevent Operator to restart the pod:
<pre class="lang:sh decode:true">kubectl -n namespace exec pod-name -c pxc -- touch /var/lib/mysql/sleep-forever</pre>
Confirm file is there: 
<pre class="lang:sh decode:true">kubectl -n namespace exec pod-name -c pxc -- ls -l /var/lib/mysql|grep sleep</pre>
Checking the status you will have:
<pre class="lang:sh decode:true">NAME READY STATUS RESTARTS AGE
percona-xtradb-cluster-operator-fb4cf7f9d-97rfs 1/1 Running 0 13d
test-prod1-haproxy-0 2/3 Running 0 21h
test-prod1-pxc-0 1/2 Running 0 46s
</pre>
Now you can connect to your pod only locally:
<pre class="lang:sh decode:true">kubectl exec --stdin --tty <pod name> -n <namespace> -c pxc -- /bin/bash</pre>
Once there:
<pre class="lang:sh decode:true">mysql -uroot -p<root_password></pre>
And you are in.
I suggest you to open two different bash terminals and in one run the monitor query:
<pre class="lang:sh decode:true">while [ 1 == 1 ]; do mysql -uroot -p<root_password> -e "select id,stage,state,BEGIN_TIME,END_TIME,THREADS,((ESTIMATE/1024)/1024) ESTIMATE_MB,format(((data/estimate)*100),2) 'completed%', ((DATA/1024)/1024) DATA_MB,NETWORK,DATA_SPEED,NETWORK_SPEED from performance_schema.clone_progress;";sleep 1;done;</pre>
This command will give you a clear idea of the status of the cloning process.
To clone from a SOURCE you need to tell the RECEIVER which source to trust.
On the other bash, inside the mysql client:
<pre class="lang:mysql decode:true">SET GLOBAL clone_valid_donor_list = 'source_public_ip:port';
CLONE INSTANCE FROM 'migration'@'ip':port IDENTIFIED BY 'XXX';</pre>
While cloning your monitor query will give you the status of the operation:
<pre class="lang:mysql decode:true">+------+-----------+-------------+----------------------------+----------------------------+---------+-----------------+------------+---------------+------------+------------+---------------+
| id | stage | state | BEGIN_TIME | END_TIME | THREADS | ESTIMATE_MB | completed% | DATA_MB | NETWORK | DATA_SPEED | NETWORK_SPEED |
+------+-----------+-------------+----------------------------+----------------------------+---------+-----------------+------------+---------------+------------+------------+---------------+
| 1 | DROP DATA | Completed | 2024-07-30 15:07:17.690966 | 2024-07-30 15:07:17.806309 | 1 | 0.00000000 | NULL | 0.00000000 | 0 | 0 | 0 |
| 1 | FILE COPY | In Progress | 2024-07-30 15:07:17.806384 | NULL | 4 | 130692.40951157 | 3.55 | 4642.11263657 | 4867879397 | 491961485 | 491987808 |
| 1 | PAGE COPY | Not Started | NULL | NULL | 0 | 0.00000000 | NULL | 0.00000000 | 0 | 0 | 0 |
| 1 | REDO COPY | Not Started | NULL | NULL | 0 | 0.00000000 | NULL | 0.00000000 | 0 | 0 | 0 |
| 1 | FILE SYNC | Not Started | NULL | NULL | 0 | 0.00000000 | NULL | 0.00000000 | 0 | 0 | 0 |
| 1 | RESTART | Not Started | NULL | NULL | 0 | 0.00000000 | NULL | 0.00000000 | 0 | 0 | 0 |
| 1 | RECOVERY | Not Started | NULL | NULL | 0 | 0.00000000 | NULL | 0.00000000 | 0 | 0 | 0 |
+------+-----------+-------------+----------------------------+----------------------------+---------+-----------------+------------+---------------+------------+------------+---------------+
</pre>
When the process is completed, the mysqld will shut down.
Checking in the log you will see something like this:
<pre class="lang:sh decode:true">The /var/lib/mysql/sleep-forever file is detected, node is going to infinity loop
If you want to exit from infinity loop you need to remove /var/lib/mysql/sleep-forever file</pre>
Do not worry all is good!
At this point we want to have MySQL start again and validate the current files:
<pre class="lang:sh decode:true">kubectl -n namespace exec podname -c pxc – mysqld &</pre>
Check the log and if all is ok, connect to mysql using local client:
<pre class="lang:sh decode:true">kubectl exec --stdin --tty <pod name> -n <namespace> -c pxc -- /bin/bash
mysql -uroot -p<password></pre>
Issue shutdown command from inside.
It is time to remove the wsrep_provider=none and after the sleep-forever file.
Go to the Percona Everest GUI and remove from the Database Parameters wsrep_provider=none click continue and then edit database.
Final step, remove the file:
<pre class="lang:sh decode:true">kubectl -n namespace exec podname -c pxc -- rm -f /var/lib/mysql/sleep-forever</pre>
Cluster will come back (after few restarts) with the new dataset and pointed to the SOURCE GTID:
<pre class="lang:mysql decode:true">mysql> select @@gtid_executed;
+-----------------------------------------------+
| @@gtid_executed |
+-----------------------------------------------+
| aeb22c03-7f13-11ee-9ff6-0224c88bdc4c:1-698687 |
+-----------------------------------------------+
</pre>
<h2>Enable Replication</h2>
Now if you are used to Percona Operator for MySQL (PXC based) you probably know that it does support <a href="https://www.percona.com/blog/migration-of-a-mysql-database-to-a-kubernetes-cluster-using-asynchronous-replication/">remote asynchronous replication</a>. This feature is available in the operator used by Everest but it is not exposed yet. The benefit of using the “native” replication is that the replication will be managed by the operator in case of pod crash. This will allow the cluster to continue to replicate cross pods.
On the other hand, the method described below, which for the moment (Percona Everest v1.0.1) is the only applicable, require manual intervention to start the replication in case of pod failure. 
Clarified that, here are the steps you need to follow to enable replication between the new environment and your current production. 
On source:
<pre class="lang:mysql decode:true">CREATE USER <replicauser>@'3.120.188.222' IDENTIFIED BY '<replicapw>';
GRANT REPLICATION SLAVE ON *.* TO replica@'<replica_external_ip>';</pre>
The IP of replica_external_ip is the one I told you to keep aside, for convenience here the command to get it again:
<pre class="lang:sh decode:true">kubectl -n namespace exec podname -c pxc -- curl -4s ifconfig.me</pre>
On Receiver, connect to the pod using mysql client and type:
<pre class="lang:mysql decode:true">CHANGE REPLICATION SOURCE TO SOURCE_HOST='<source>', SOURCE_USER=<replicauser>, SOURCE_PORT=3306, SOURCE_PASSWORD='<replicapw>', SOURCE_AUTO_POSITION = 1</pre>
Then start replication as usual.
If all was done right, you will have the Replication working and your new database is replicating from current production, keeping the two in sync.
<pre class="lang:mysql decode:true">mysql> show replica statusG
*************************** 1. row ***************************
Replica_IO_State: Waiting for source to send event
Source_Host: 18.198.187.64
Source_User: replica
Source_Port: 3307
Connect_Retry: 60
Source_Log_File: binlog.000001
Read_Source_Log_Pos: 337467656
Relay_Log_File: test-prod1-pxc-0-relay-bin.000002
Relay_Log_Pos: 411
Relay_Source_Log_File: binlog.000001
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
… snip …
Executed_Gtid_Set: aeb22c03-7f13-11ee-9ff6-0224c88bdc4c:1-698687
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Source_TLS_Version:
Source_public_key_path:
Get_Source_public_key: 0
Network_Namespace:
</pre>
<h2>Final touch</h2>
The final touch is to move the cluster from 1 node to 3 nodes.
<pre class="lang:sh decode:true">$ kubectl get pods
NAME READY STATUS RESTARTS AGE
percona-xtradb-cluster-operator-fb4cf7f9d-97rfs 1/1 Running 0 14d
test-prod1-haproxy-0 2/2 Running 6 (48m ago) 77m
test-prod1-pxc-0 1/1 Running 0 45m
</pre>
To do so, open the Percona Everest GUI, edit your database and in the Resources tab, choose 3 nodes, then continue till the end and edit database.
<a href="https://www.percona.com/blog/wp-content/uploads/2024/09/everest11-a.jpg"><img src="http://www.tusacentral.net/joomla/images/stories/how_to_migrate_toPeverest/everest11-a.jpg" alt="everest11 a" width="1024" height="416" class="alignnone size-large wp-image-98260" /></a>
At the end of the update process, you will have:
<pre class="lang:sh decode:true">$ kubectl get pods
NAME READY STATUS RESTARTS AGE
percona-xtradb-cluster-operator-fb4cf7f9d-97rfs 1/1 Running 0 14d
test-prod1-haproxy-0 2/2 Running 6 (151m ago) 3h1m
test-prod1-haproxy-1 2/2 Running 0 103m
test-prod1-haproxy-2 2/2 Running 0 102m
test-prod1-pxc-0 1/1 Running 0 149m
test-prod1-pxc-1 1/1 Running 0 103m
test-prod1-pxc-2 1/1 Running 0 93m
</pre>
At this point you have your new environment ready to go. 
<h2>Post migration actions</h2>
Remember that there are always many other things to do once you have migrated the data:
<ul>
<li style="font-weight: 400;" aria-level="1">Validate Data Integrity
<ul>
<li style="font-weight: 400;" aria-level="2">Consistency Check: Use tools like mysqlcheck or Percona’s pt-table-checksum to ensure data integrity and consistency between MySQL 8.0 and Percona Everest.</li>
<li style="font-weight: 400;" aria-level="2">Query Testing: Run critical queries and perform load testing to ensure that performance metrics are met and that queries execute correctly.</li>
</ul>
</li>
<li style="font-weight: 400;" aria-level="1">Test and Optimize
<ul>
<li style="font-weight: 400;" aria-level="2">Benchmarking: Conduct performance benchmarking to compare MySQL 8.0 and Percona Everest. Use tools like sysbench or MySQL’s EXPLAIN statement to analyze query performance.</li>
<li style="font-weight: 400;" aria-level="2">Optimization: Tweak Percona Everest settings based on the benchmark results. Consider features like Percona’s Query Analytics and Performance Schema for deeper insights.</li>
</ul>
</li>
<li style="font-weight: 400;" aria-level="1">Enable Backup schedule and Point In time Recovery <a href="https://www.percona.com/blog/wp-content/uploads/2024/09/everest12-a.jpg"><img src="http://www.tusacentral.net/joomla/images/stories/how_to_migrate_toPeverest/everest12-a.jpg" alt="everest12 a" width="1024" height="376" class="alignnone size-large wp-image-98262" /></a></li>
<li style="font-weight: 400;" aria-level="1">Switch to Production
<ul>
<li style="font-weight: 400;" aria-level="2">Cutover Plan: Develop a cutover plan that includes a maintenance window, final data synchronization, and the switchover to the new database.</li>
<li style="font-weight: 400;" aria-level="2">ALWAYS perform a backup of the platform.</li>
<li style="font-weight: 400;" aria-level="2">Monitoring and Support: Set up monitoring with tools like Percona Monitoring and Management (PMM) to keep an eye on performance, queries, and server health.</li>
</ul>
</li>
<li style="font-weight: 400;" aria-level="1">Verification and Documentation:
<ul>
<li style="font-weight: 400;" aria-level="2">Data Validation: Conduct thorough testing to confirm that all application functionality works as expected with Percona Everest.</li>
<li style="font-weight: 400;" aria-level="2">Documentation: Update your database documentation to reflect the new setup, configurations, and any changes made during the migration.</li>
</ul>
</li>
</ul>
<h2>Summary of commands </h2>
<table>
<tbody>
<tr>
<td>Use</td>
<td>Command</td>
</tr>
<tr>
<td>Get cluster state</td>
<td>kubectl get pxc</td>
</tr>
<tr>
<td>Get list of the pods</td>
<td>kubectl get pods</td>
</tr>
<tr>
<td>Return password for system users</td>
<td>DB_NAMESPACE='namespace'; DB_NAME='cluster-name'; kubectl get secret everest-secrets-"$DB_NAME" -n "$DB_NAMESPACE" -o go-template='{{range $k,$v := .data}}{{"### "}}{{$k}}{{"| pw: "}}{{$v|base64decode}}{{"nn"}}{{end}}'|grep -E 'operator|replication|monitor|root||xtrabackup'</td>
</tr>
<tr>
<td>Change password for a given user</td>
<td>DB_NAMESPACE='namespace'; DB_NAME='cluster-name'; USER='root'; PASSWORD='root_password'; kubectl patch secret everest-secrets-"$DB_NAME" -p="{"stringData":{"$USER": "$PASSWORD"}}" -n "$DB_NAMESPACE</td>
</tr>
<tr>
<td>Show the pod log for a specific container Tail style</td>
<td>kubectl logs pod-name --follow -c pxc</td>
</tr>
<tr>
<td>Return public IP for that pod</td>
<td>kubectl -n namespace exec podname -c pxc -- curl -4s ifconfig.me</td>
</tr>
<tr>
<td>Prevent operator to restart the pod</td>
<td>kubectl -n namespace exec pod-name -c pxc -- touch /var/lib/mysql/sleep-forever</td>
</tr>
<tr>
<td>Remove the sleep-forever file</td>
<td>kubectl -n namespace exec pod-name -c pxc – rm -f /var/lib/mysql/sleep-forever</td>
</tr>
<tr>
<td>Connect to pod bash</td>
<td>kubectl exec --stdin --tty <pod name> -n <namespace> -c pxc -- /bin/bash</td>
</tr>
<tr>
<td> </td>
<td> </td>
</tr>
</tbody>
</table>
<h1>References</h1>
<a href="https://www.percona.com/blog/understanding-what-kubernetes-is-used-for-the-key-to-cloud-native-efficiency/">https://www.percona.com/blog/understanding-what-kubernetes-is-used-for-the-key-to-cloud-native-efficiency/</a>
<a href="https://www.percona.com/blog/should-you-deploy-your-databases-on-kubernetes-and-what-makes-statefulset-worthwhile/">https://www.percona.com/blog/should-you-deploy-your-databases-on-kubernetes-and-what-makes-statefulset-worthwhile/</a>
<a href="https://www.tusacentral.com/joomla/index.php/mysql-blogs/242-compare-percona-distribution-for-mysql-operator-vs-aws-aurora-and-standard-rds">https://www.tusacentral.com/joomla/index.php/mysql-blogs/242-compare-percona-distribution-for-mysql-operator-vs-aws-aurora-and-standard-rds</a>
<a href="https://www.tusacentral.com/joomla/index.php/mysql-blogs/243-mysql-on-kubernetes-demystified">https://www.tusacentral.com/joomla/index.php/mysql-blogs/243-mysql-on-kubernetes-demystified</a>
<a href="https://github.com/Tusamarco/mysqloperatorcalculator">https://github.com/Tusamarco/mysqloperatorcalculator</a>
<a href="https://www.percona.com/blog/migration-of-a-mysql-database-to-a-kubernetes-cluster-using-asynchronous-replication/">https://www.percona.com/blog/migration-of-a-mysql-database-to-a-kubernetes-cluster-using-asynchronous-replication/</a>
 ]]></description>
<category>MySQL</category>
<pubDate>Mon, 02 Sep 2024 13:47:01 +0000</pubDate>
</item>
<item>
<title>Sakila, Where Are You Going?</title>
<link>http://www.tusacentral.net/joomla/index.php/mysql-blogs/256-sakila-where-are-you-going</link>
<guid isPermaLink="true">http://www.tusacentral.net/joomla/index.php/mysql-blogs/256-sakila-where-are-you-going</guid>
<description><![CDATA[This article is in large part the same of what I have published in the <a href="https://www.percona.com/blog/sakila-where-are-you-going/">Percona blog</a>. However I am reproposing it here given it is the first some other benchmarking exercise that I am probably going to present here in an extended format, while it may be more concise in other platforms. 
In any case why this tests. 
I am curious, and I do not like (at all) what is happening around MySQL and MariaDB, never like it, but now is really think is time to end this negative trend, that is killing not only the community, but the products as well. 
<h2>The tests</h2>
<h3>Assumptions</h3>
There are many ways to run tests, and we know that results may vary depending on how you play with many factors, like the environment or the MySQL server settings. However, if we compare several versions of the same product on the same platform, it is logical to assume that all the versions will have the same “chance” to behave well or badly unless we change the MySQL server settings. 
Because of this, I ran the tests ON DEFAULTS, with the clear assumption that if you release your product based on the defaults, that implies you had tested with them and consider them the safest for generic use. 
I also applied some <a href="https://github.com/Tusamarco/blogs/blob/master/sakila_where_are_you_going/config_changes.txt">modifications </a>and ran the tests again to see how optimization would impact performance. 
<h3>What tests do we run?</h3>
High level, we run two sets of tests:
<ul>
<li style="font-weight: 400;" aria-level="1">Sysbench</li>
<li style="font-weight: 400;" aria-level="1">TPC-C (<a href="https://www.tpc.org/tpcc/" rel="nofollow">https://www.tpc.org/tpcc/</a>) like </li>
</ul>
The full methodology and test details can be found <a href="https://github.com/Tusamarco/benchmarktools/blob/main/docs/plan.md" rel="nofollow">here</a>, while actual commands are available:
<ul>
<li style="font-weight: 400;" aria-level="1"><a href="https://github.com/Tusamarco/benchmarktools/blob/main/software/fill_sysbench_map.sh" rel="nofollow">Sysbench</a></li>
<li style="font-weight: 400;" aria-level="1"><a href="https://github.com/Tusamarco/benchmarktools/blob/main/software/fill_tpcc_map.sh" rel="nofollow">TPC-C</a> </li>
</ul>
<h2>Results</h2>
While I have executed the whole set of tests as indicated on the page, and all the results are visible <a href="https://github.com/Tusamarco/blogs/tree/master/sakila_where_are_you_going">here</a>, for brevity and because I want to keep this article at a high level, I will report and cover only the Read-Write tests and the TPC-C. 
This is because, in my opinion, they offer an immediate and global view of how the server behaves. They also represent the most used scenario, while the other tests are more interesting to dig into problems.   
The sysbench read/write tests reported below have a lower percentage of writes ~36% and ~64% reads, where reads are point selects and range selects. TPC-C instead has an even distribution of 50/50 % between read and write operations. 
<h3>Sysbench read and write tests </h3>
Test using default configurations only MySQL in different versions. 
Small dataset:
<a href="https://www.percona.com/blog/wp-content/uploads/2024/06/mysql_trend_default_rw_small.png"><img src="http://www.tusacentral.net/joomla/images/stories/sakila_where_are_you_going/mysql_trend_default_rw_small.jpg" alt="mysql trend default rw small" width="800" height="333" class="aligncenter wp-image-96835 size-large" /></a>
Optimized configuration only MySQL:
<a href="https://www.percona.com/blog/wp-content/uploads/2024/06/mysql_trend_optimized_rw_small_100_range.png"><img src="http://www.tusacentral.net/joomla/images/stories/sakila_where_are_you_going/mysql_trend_optimized_rw_small_100_range.jpg" alt="mysql trend optimized rw small 100 range" width="800" height="333" class="aligncenter wp-image-96839 size-large" /></a>
Large dataset using defaults:
<a href="https://www.percona.com/blog/wp-content/uploads/2024/06/mysql_trend_default_rw_large_100_range.png"><img src="http://www.tusacentral.net/joomla/images/stories/sakila_where_are_you_going/mysql_trend_default_rw_large_100_range.jpg" alt="mysql trend default rw large 100 range" width="800" height="333" class="aligncenter wp-image-96833 size-large" /></a>
Using optimization:
<a href="https://www.percona.com/blog/wp-content/uploads/2024/06/mysql_trend_optimized_rw_large_100_range.png"><img src="http://www.tusacentral.net/joomla/images/stories/sakila_where_are_you_going/mysql_trend_optimized_rw_large_100_range.jpg" alt="mysql trend optimized rw large 100 range" width="800" height="333" class="aligncenter wp-image-96837 size-large" /></a>
The first two graphs are interesting for several reasons, but one that jumps out is that we cannot count on DEFAULTS as a starting point. Or, to be correct, we can use them as the base from which we must identify better defaults; this is also corroborated by Oracle's recent decision to modify many defaults in 8.4 (<a href="https://lefred.be/content/mysql-8-4-lts-new-production-ready-defaults-for-innodb/" rel="nofollow">see article</a>). 
Given that I will focus on the results obtained with the optimized configs.
Now looking at the graphs above, we can see that:
<ol>
<li style="font-weight: 400;" aria-level="1">MySQL 5.7 is performing better in both cases just using defaults.</li>
<li style="font-weight: 400;" aria-level="1">Given bad defaults, MySQL 8.036 was not performing well in the first case; just making some adjustments allowed it to over-perform 8.4 and be closer to what 5.7 can do.</li>
</ol>
<h3>TPC-C tests</h3>
As indicated, TPC-C tests are supposed to be write-intensive, using transactions and more complex queries with join, grouping, and sorting.
I was testing the TPC-C using the most common isolation modes, Repeatable Reads, and Read Committed.
While we experienced several issues during the multiple runs, those were not consistent, mainly due to locking timeouts. Given that, while I am representing the issue presence with a blank in the graph, they are not to be considered to impact the execution trend but only represent a saturation limit. 
Test using optimized configurations:
<a href="https://www.percona.com/blog/wp-content/uploads/2024/06/tpcc-RepeatableRead-with-optimized_only_mysql.png"><img src="http://www.tusacentral.net/joomla/images/stories/sakila_where_are_you_going/tpcc_RepeatableRead_with_defaults_only_mysql.jpg" alt="tpcc RepeatableRead with defaults only mysql" width="800" height="333" class="aligncenter wp-image-96870 size-large" /></a>
Test using optimized configurations:
<a href="https://www.percona.com/blog/wp-content/uploads/2024/06/tpcc-ReadCommitted-with-optimized_only_mysql.png"><img src="http://www.tusacentral.net/joomla/images/stories/sakila_where_are_you_going/tpcc_ReadCommitted_with_optimized_only_mysql.jpg" alt="tpcc ReadCommitted with optimized only mysql" width="800" height="333" class="aligncenter wp-image-96866 size-large" /></a>
In this test we can observe that MySQL 5.7 is better performing in comparison with the other MySQL versions.  
<h3>What if we compare it with Percona Server for MySQL and MariaDB?</h3>
I will present only the optimized tests here for brevity because, as I saw before, we know defaults are not serving us well. 
<a href="https://www.percona.com/blog/wp-content/uploads/2024/06/mysql_versions_compare_optimized_rw_small_100_range.png"><img src="http://www.tusacentral.net/joomla/images/stories/sakila_where_are_you_going/mysql_versions_compare_optimized_rw_small_100_range.jpg" alt="mysql versions compare optimized rw small 100 range" width="800" height="333" class="aligncenter wp-image-96848 size-large" /></a>
<a href="https://www.percona.com/blog/wp-content/uploads/2024/06/mysql_versions_compare_optimized_rw_large_100_range.png"><img src="http://www.tusacentral.net/joomla/images/stories/sakila_where_are_you_going/mysql_versions_compare_optimized_rw_large_100_range.jpg" alt="mysql versions compare optimized rw large 100 range" width="800" height="333" class="aligncenter wp-image-96846 size-large" /></a>
When comparing the MYSQL versions against Percona Server for MySQL 8.0.36 and MariaDB 11.3, we see how MySQL 8.4 is doing better only in relation to MariaDB; after that, it remains behind also compared to MySQL 8.0.36. 
<h4>TPC-C</h4>
<a href="https://www.percona.com/blog/wp-content/uploads/2024/06/tpcc-RepeatableRead-optimized_all.png"><img src="http://www.tusacentral.net/joomla/images/stories/sakila_where_are_you_going/tpcc_RepeatableRead_optimized_all.jpg" alt="tpcc RepeatableRead optimized all" width="800" height="333" class="aligncenter wp-image-96867 size-large" /></a>
<a href="https://www.percona.com/blog/wp-content/uploads/2024/06/tpcc-ReadCommitted-optimized_all.png"><img src="http://www.tusacentral.net/joomla/images/stories/sakila_where_are_you_going/tpcc_ReadCommitted_optimized_all.jpg" alt="tpcc ReadCommitted optimized all" width="800" height="333" class="aligncenter wp-image-96854 size-large" /></a>
As expected, MySQL 8.4 is not acting well here either, and only MariaDB is performing worse. Note how Percona Server for MySQL 8.0.36 is the only one able to handle the increased contention. 
<h2>What are these tests saying to us?</h2>
Frankly speaking, what we get here is what most of our users get as well, but on their own skin. MySQL performances are degrading with the increase of versions. 
For sure, MySQL 8.x comes with interesting additions; however, if you consider performance as the first and most important topic, then MySQL 8.x is not any better. 
Having said this, we must say that probably most of the ones still using MySQL 5.7 (and we have thousands of them) are right. Why embark on a very risky migration and then discover that you have lost a considerable percentage in performance?  
Regarding this, if we analyze the data and convert the trends into transactions/sec, we can identify the following scenarios if we compare the tests done using TPC:
<a href="https://www.percona.com/blog/wp-content/uploads/2024/06/tpcc_trx_lost_rr.jpg"><img src="http://www.tusacentral.net/joomla/images/stories/sakila_where_are_you_going/tpcc_trx_pct_lost_rr.jpg" alt="tpcc trx lost rr" width="800" height="581" class="aligncenter wp-image-96871 size-large" /></a>
<a href="https://www.percona.com/blog/wp-content/uploads/2024/06/tpcc_trx_lost_rc.jpg"><img src="http://www.tusacentral.net/joomla/images/stories/sakila_where_are_you_going/tpcc_trx_lost_pct_rc.jpg" alt="tpcc trx lost pct rc" width="800" height="549" class="aligncenter wp-image-96872 size-large" /></a>
As we can see, the performance degradation can be significant in both tests, while the benefits (when present) are irrelevant. 
In absolute numbers:
<a href="https://www.percona.com/blog/wp-content/uploads/2024/06/tpcc_trx_lost_rr-1.jpg"><img src="http://www.tusacentral.net/joomla/images/stories/sakila_where_are_you_going/tpcc_trx_lost_rr.jpg" alt="tpcc trx lost rr" width="800" height="581" class="aligncenter wp-image-96874 size-large" /></a>
<a href="https://www.percona.com/blog/wp-content/uploads/2024/06/tpcc_trx_lost_rc-1.jpg"><img src="http://www.tusacentral.net/joomla/images/stories/sakila_where_are_you_going/tpcc_trx_lost_rc.jpg" alt="tpcc trx lost rc" width="800" height="549" class="aligncenter wp-image-96873 size-large" /></a>
 In this scenario, we need to ask ourselves, can my business deal with such a performance drop?
<h3>Considerations</h3>
When MySQL was sold to SUN Microsystems, I was in MySQL AB. I was not happy about that move at all, and when Oracle took over SUN, I was really concerned about Oracle's possible decision to kill MySQL. I also decided to move on and join another company. 
In the years after, I changed my mind, and I was supporting and promoting the Oracle/MySQL work. In many ways, I still am. 
They did a great job rationalizing the development, and the code clean-up was significant. However, something did not progress with the rest of the code. The performance decrease we are seeing is the cost of this lack of progress; see also Peter's article <a href="https://www.percona.com/blog/is-oracle-finally-killing-mysql/">Is Oracle Finally Killing MySQL?.</a>
On the other hand, we need to recognize that Oracle is investing a lot in performance and functionalities when we talk of the OCI/MySQL/Heatwave offer. Only those improvements are not reflected in the MySQL code, no matter if it is Community or Enterprise. 
Once more, while I consider this extremely sad, I can also understand why. 
Why should Oracle continue to optimize the MySQL code for free when cloud providers such as Google or AWS use that code, optimize it for their use, make billions, and not even share the code back? 
We know this has been happening for many years now, and we know this is causing a significant and negative impact on the open source ecosystem. 
MySQL is just another Lego block in a larger scenario in which cloud companies are cannibalizing the work of others for their own economic return. 
What can be done? I can only hope we will see a different behavior soon. Opening the code and investing in projects that will help communities such as MySQL to quickly recover the lost ground. 
Let me add that while is perfectly normal in our economy to look for profit, at the end this is what capitalism is for, it is not normal, or for better say it is negative, to look for profit without keeping in mind you are burning out the resources. that gives you that profit. 
This last is consumerism, using and abusing, without keeping in mind you MUST give the resources you use the time/energy/opportunity to renew and florish is stupid, short sight and suicidal.
Perfectly in line with our times isen't it?   
So let say that many big names in the cloud, should seriously rethink what they are doing, not because they need to be nice. But because they will get better outcome and income helping the many opensource community instead as they are doing today, abusing them. 
In the meantime, we must acknowledge that many customers/users are on 5.7 for a good reason and that until we are able to fix that, they may decide not to migrate forever or, if they must, to migrate to something else, such as Postgres. 
Then Sakila will slowly and painfully die as usual for the greed of the human being, nothing new in a way, yes, but not good.
<a href="https://www.percona.com/blog/wp-content/uploads/2024/06/dolphin_heatwave3.jpeg"><img src="http://www.tusacentral.net/joomla/images/stories/sakila_where_are_you_going/dolphin_heatwave3.jpeg" alt="dolphin heatwave3" width="600" height="600" class="aligncenter wp-image-96858 size-medium" style="display: block; margin-left: auto; margin-right: auto;" /></a>
Happy MySQL to all.  
 ]]></description>
<category>MySQL</category>
<pubDate>Tue, 18 Jun 2024 13:22:55 +0000</pubDate>
</item>
<item>
<title>Is MySQL Router 8.2 Any Better?</title>
<link>http://www.tusacentral.net/joomla/index.php/mysql-blogs/255-is-mysql-router-8-2-any-better</link>
<guid isPermaLink="true">http://www.tusacentral.net/joomla/index.php/mysql-blogs/255-is-mysql-router-8-2-any-better</guid>
<description><![CDATA[In my previous article, <a href="https://www.percona.com/blog/comparisons-of-proxies-for-mysql/">Comparisons of Proxies for MySQL</a>, I showed how MySQL Router was the lesser performing Proxy in the comparison. From that time to now, we had several MySQL releases and, of course, also some new MySQL Router ones.
Most importantly, we also had MySQL Router going back to being a level 7 proxy capable of redirecting traffic in case of R/W operations (<a href="https://dev.mysql.com/doc/mysql-router/8.2/en/router-read-write-splitting.html" rel="nofollow">see this</a>).
All these bring me hope that we will also have some good improvements in what is a basic functionality in a router: routing.
So with these great expectations, I had to repeat the exact same tests I did in my previous tests, plus I tested for MySQL Router only the cost of encapsulating the select inside a transaction.
Just keep in mind that for all the tests, MySQL Router was configured to use the read/write split option.
<h2>The results</h2>
Given this is the continuation of the previous blog, all the explanations about the tests and commands used are in the <a href="https://www.percona.com/blog/comparisons-of-proxies-for-mysql/">first article</a>. If you did not read that, do it now, or it will be difficult for you to follow what is explained later.
As indicated, I was looking to identify when the first proxy would reach a dimension that would not be manageable. The load is all in creating and serving the connections, while the number of operations is capped at 100.
<a href="https://www.percona.com/blog/wp-content/uploads/2024/01/events_rate_82.png"><img src="https://www.percona.com/blog/wp-content/uploads/2024/01/events_rate_82-1024x608.png" alt="" width="600" height="356" class="aligncenter wp-image-93339 size-large" /></a>
As you can see, MySQL Router was reaching the saturation level and was unable to serve traffic at exactly the same time as the previous test.
<h2>Test two</h2>
When the going gets tough, the tough get going reprise ;) 
Let’s remove the –rate limitation and see what will happen. First, let us compare MySQL router versions only:
<a href="https://www.percona.com/blog/wp-content/uploads/2024/01/router_82_80_comparison_events.png"><img src="http://www.tusacentral.net/joomla/images/stories/ismysqlrouter82better/router_82_80_comparison_events.png" alt="router 82 80 comparison events" width="600" height="376" class="size-large wp-image-93348 aligncenter" /></a>
As we can see the MySQL Router version 8.2 is doing better up to 64 concurrent threads.
<a href="https://www.percona.com/blog/wp-content/uploads/2024/01/router_82_80_comparison_latency.png"><img src="https://www.percona.com/blog/wp-content/uploads/2024/01/router_82_80_comparison_latency-1024x680.png" alt="" width="600" height="398" class="size-large wp-image-93349 aligncenter" /></a>
Latency follows the same trend in old and new cases, and we can see that the new version is acting better, up to 1024 threads.
Is this enough to cover the gap with the other proxies? Which, in the end, is what we would like to see. 
<a href="https://www.percona.com/blog/wp-content/uploads/2024/01/events_norate_82.png"><img src="http://www.tusacentral.net/joomla/images/stories/ismysqlrouter82better/events_norate_82.png" alt="events norate 82" width="600" height="326" class="size-large wp-image-93337 aligncenter" /></a>
<a href="https://www.percona.com/blog/wp-content/uploads/2024/01/latency_norate_82.png"><img src="http://www.tusacentral.net/joomla/images/stories/ismysqlrouter82better/latency_norate_82.png" alt="latency norate 82" width="600" height="364" class="size-large wp-image-93342 aligncenter" /></a>
Well, I would say not really; we see a bit of better performance with low concurrent threads, but still not scaling and definitely lower than the other two.
Now let us take a look at the CPU saturation:
<a href="https://www.percona.com/blog/wp-content/uploads/2024/01/cpu_saturation.png"><img src="http://www.tusacentral.net/joomla/images/stories/ismysqlrouter82better/cpu_saturation.png" alt="cpu saturation" width="600" height="300" class="size-full wp-image-93334 aligncenter" /></a>
<a href="https://www.percona.com/blog/wp-content/uploads/2024/01/cpu.png"><img src="http://www.tusacentral.net/joomla/images/stories/ismysqlrouter82better/cpu.png" alt="cpu" width="600" height="300" class="size-full wp-image-93335 aligncenter" /></a>
Here, we can see how MySQL Router hits the top as soon as the rate option is lifted and gets worse with the increase of the running threads.
<h2>Test three</h2>
This simple test was meant to identify the cost of a transaction, or better, what it will cost to include selects inside a transaction.
<a href="https://www.percona.com/blog/wp-content/uploads/2024/01/read_events_trx.png"><img src="http://www.tusacentral.net/joomla/images/stories/ismysqlrouter82better/read_events_trx.png" alt="read events trx" width="600" height="356" class="size-large wp-image-93344 aligncenter" /></a>
<a href="https://www.percona.com/blog/wp-content/uploads/2024/01/latency_events_trx.png"><img src="http://www.tusacentral.net/joomla/images/stories/ismysqlrouter82better/latency_events_trx.png" alt="latency events trx" width="600" height="371" class="size-large wp-image-93340 aligncenter" /></a>
As we can clearly see, MySQL Router, when handling selects inside a transaction, will drop its performance drastically going back to version 8.0 performance.
<h3>Conclusions</h3>
To the initial question — Is MySQL Router 8.2 any better? — we can answer a small (very small) yes.
However, it is still far, far away from being competitive with ProxySQL (same proxy level) or with HAProxy. The fact it is not able to serve efficiently the requests also inside the lower set of concurrent threads, is disappointing.
Even more disappointing because MySQL Router is presented as a critical component in the MySQL InnoDB Cluster solution. How can we use it in the architectures if the product has such limitations?
I know that Oracle suggests to scale out, and I agree with them. When in need to scale with MySQL Router, the only option is to build a forest. However, we must keep in mind that each MySQL Router connects and queries the data nodes constantly and intensively. Given that it requires adding a forest of router nodes to scale, it is not without performance impact, given the increasing noise generated on the data nodes.
Anyhow also, if there is a theoretical option to scale, that is not a good reason to use a poor performing component.
I would prefer to use ProxySQL with Group Replication and add whatever script is needed in mysqlshell to manage it as Oracle is doing for the MySQL InnoDB cluster solution.
What also left me very unhappy is that MySQL InnoDB Cluster is one of the important components of the OCI offer for MySQL. Is Oracle using MySQL Router there as well? I assume so. Can we trust it? I am not feeling like I can.
Finally, what has been done for MySQL Router so far leads me to think that there is no real interest in making it the more robust and performing product that MySQL InnoDB Cluster deserves.
I hope I am wrong and that we will soon see a fully refactored version of MySQL Router. I really hope Oracle will prove me wrong.
Great MySQL to everyone.]]></description>
<category>MySQL</category>
<pubDate>Wed, 10 Jan 2024 18:05:47 +0000</pubDate>
</item>
<item>
<title>Export and import of MySQL passwords using caching_sha2 </title>
<link>http://www.tusacentral.net/joomla/index.php/mysql-blogs/254-export-and-import-of-mysql-passwords-using-caching-sha2</link>
<guid isPermaLink="true">http://www.tusacentral.net/joomla/index.php/mysql-blogs/254-export-and-import-of-mysql-passwords-using-caching-sha2</guid>
<description><![CDATA[Some fun is coming <a href="https://www.percona.com/blog/wp-content/uploads/2023/09/user-migration.jpg"><img src="https://www.percona.com/blog/wp-content/uploads/2023/09/user-migration-300x136.jpg" alt="" width="380" height="172" class="wp-image-91109 alignright" style="float: right;" /></a>
While I was writing the internal guidelines on how to migrate from MariaDB to Percona Server, I had to export the users accounts in a portable way. This given MariaDB uses  some non standard syntax brings me to first test some external tools such as Fred <a href="https://github.com/lefred/mysqlshell-plugins/wiki/user#getusersgrants">https://github.com/lefred/mysqlshell-plugins/wiki/user#getusersgrants</a> and our <a href="https://github.com/percona/percona-toolkit/blob/3.x/bin/pt-show-grants" rel="nofollow">PT-SHOW-GRANTS</a> tool.
Useless to say this had open a can worms, given first I had to fix/convert the specifics for MariaDB (not in the scope of this blog), then while testing I discover another nasty issue, that currently prevent us to easily export the new Passwords in MySQL 8 (and PS 8) when caching_sha2 is used. 
 
So what is the problem I am referring to?
Well the point is that when you generate passwords with caching_sha2 (default in mysql 8) the password generated can (will) contain characters that are not portable, not even between mysql 8. 
Let's see a practical example to understand.
If I use old mysql_native_password and I create a user such as: 
<pre class="lang:mysql decode:true">create user dba@'192.168.1.%' identified with mysql_native_password by 'dba';
</pre>
My authentication_string will be: 
<pre class="lang:mysql decode:true">root@localhost) [(none)]>select user,host,authentication_string,plugin from mysql.user where user ='dba' order by 1,2;
+------+-------------+-------------------------------------------+-----------------------+
| user | host | authentication_string | plugin |
+------+-------------+-------------------------------------------+-----------------------+
| dba | 192.168.1.% | *381AD08BBFA647B14C82AC1094A29AD4D7E4F51D | mysql_native_password |
+------+-------------+-------------------------------------------+-----------------------+
</pre>
At this point if you want to export the user:
<pre class="lang:mysql decode:true">(root@localhost) [(none)]>show create user dba@'192.168.1.%'G
*************************** 1. row ***************************
CREATE USER for dba@192.168.1.%: CREATE USER `dba`@`192.168.1.%` IDENTIFIED WITH 'mysql_native_password' AS '*381AD08BBFA647B14C82AC1094A29AD4D7E4F51D' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
1 row in set (0.01 sec)
</pre>
You just need to use the text after the semicolon and all will work fine. Remember that when you want to preserve the already converted password you need to use the IDENTIFIED … AS <PW> not the BY or you will re-convert the password ;).
 Anyhow .. this is simple and what we are all used to. 
Now if you instead try to use caching_sha2 things will go differently:
<pre class="lang:mysql decode:true">root@localhost) [(none)]>create user dba@'192.168.4.%' identified with caching_sha2_password by 'dba';
Query OK, 0 rows affected (0.02 sec)
(root@localhost) [(none)]>select user,host,authentication_string,plugin from mysql.user where user ='dba' order by 1,2;
+------+-------------+------------------------------------------------------------------------+-----------------------+
| user | host | authentication_string | plugin |
+------+-------------+------------------------------------------------------------------------+-----------------------+
| dba | 192.168.1.% | *381AD08BBFA647B14C82AC1094A29AD4D7E4F51D | mysql_native_password |
| dba | 192.168.4.% | $A$005$@&%1H5iNQx|.l{N7T/GosA.Lp4EiO0bxLVQp8Zi0WY2nXLr8TkleQPYjaqVxI7 | caching_sha2_password |
+------+-------------+------------------------------------------------------------------------+-----------------------+
2 rows in set (0.00 sec)
</pre>
Probably you will not see it here given that while converted on your screen the special characters will be replaced, but the password contains invalid characters. 
If I try to extract the Create USER text I will get:
<pre class="lang:mysql decode:true"> (root@localhost) [(none)]>show create user dba@'192.168.4.%'G
*************************** 1. row ***************************
CREATE USER for dba@192.168.4.%: CREATE USER `dba`@`192.168.4.%` IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$@&%1H5iNQx|.l{N7T/GosA.Lp4EiO0bxLVQp8Zi0WY2nXLr8TkleQPYjaqVxI7' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
1 row in set (0.00 sec)</pre>
However if I try to use this text to generate the user after I drop it: 
<pre class="lang:mysql decode:true">(root@localhost) [(none)]>drop user dba@'192.168.4.%';
Query OK, 0 rows affected (0.02 sec)
(root@localhost) [(none)]>create user dba@'192.168.4.%' IDENTIFIED AS 'NQx|.l{N7T/GosA.Lp4EiO0bxLVQp8Zi0WY2nXLr8TkleQPYjaqVxI7';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS 'NQx|.l{N7T/GosA.Lp4EiO0bxLVQp8Zi0WY2nXLr8TkleQPYjaqVxI7'' at line 1
</pre>
Don’t waste time, there is nothing wrong in the query, except the simple fact that you CANNOT use the text coming from the authentication_string when you have caching_sha2. 
So? What should we do? 
The answer is easy, we need to convert the password into binary and use/store that. 
Let us try.
First create the user again:
<pre class="lang:mysql decode:true">(root@localhost) [(none)]>select user,host,authentication_string,plugin from mysql.user where user ='dba' order by 1,2;
+------+-------------+------------------------------------------------------------------------+-----------------------+
| user | host | authentication_string | plugin |
+------+-------------+------------------------------------------------------------------------+-----------------------+
| dba | 192.168.1.% | *381AD08BBFA647B14C82AC1094A29AD4D7E4F51D | mysql_native_password |
| dba | 192.168.4.% | $A$005$X>ztS}WfR"k~aH3Hs0hBbF3WmM2FXubKumr/CId182pl2Lj/gEtxLvV0 | caching_sha2_password |
+------+-------------+------------------------------------------------------------------------+-----------------------+
2 rows in set (0.00 sec)
(root@localhost) [(none)]>exit
Bye
[root@master3 ps80]# ./mysql-3307 -udba -pdba -h192.168.4.57 -P3307
...
(dba@192.168.4.57) [(none)]>
</pre>
OK as you can see I create the user and can connect, but as we know the PW is not portable.
Let us convert it and create the user:
<pre class="lang:mysql decode:true">(root@localhost) [(none)]>select user,host,convert(authentication_string using binary),plugin from mysql.user where user ='dba' and host='192.168.4.%' order by 1,2;
+------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+
| user | host | convert(authentication_string using binary) | plugin |
+------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+
| dba | 192.168.4.% | 0x2441243030352458193E107A74537D0157055C66527F226B7E5C6148334873306842624633576D4D32465875624B756D722F434964313832706C324C6A2F674574784C765630 | caching_sha2_password |
+------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------+
</pre>
So the password is: 
<pre class="lang:mysql decode:true">0x2441243030352458193E107A74537D0157055C66527F226B7E5C6148334873306842624633576D4D32465875624B756D722F434964313832706C324C6A2F674574784C7656
</pre>
Let us use it:
<pre class="lang:mysql decode:true">(root@localhost) [(none)]>drop user dba@'192.168.4.%';
Query OK, 0 rows affected (0.02 sec)
(root@localhost) [(none)]>create user dba@'192.168.4.%' IDENTIFIED with 'caching_sha2_password' AS 0x2441243030352458193E107A74537D0157055C66527F226B7E5C6148334873306842624633576D4D32465875624B756D722F434964313832706C324C6A2F674574784C765630;
Query OK, 0 rows affected (0.03 sec)
</pre>
Let us check the user now:
<pre class="lang:mysql decode:true">(root@localhost) [(none)]>select user,host, authentication_string,plugin from mysql.user where user ='dba' and host= '192.168.4.%' order by 1,2;
+------+-------------+------------------------------------------------------------------------+-----------------------+
| user | host | authentication_string | plugin |
+------+-------------+------------------------------------------------------------------------+-----------------------+
| dba | 192.168.4.% | $A$005$X>ztS}WfR"k~aH3Hs0hBbF3WmM2FXubKumr/CId182pl2Lj/gEtxLvV0 | caching_sha2_password |
+------+-------------+------------------------------------------------------------------------+-----------------------+
1 row in set (0.00 sec)
[root@master3 ps80]# ./mysql-3307 -udba -pdba -h192.168.4.57 -P3307
(dba@192.168.4.57) [(none)]>select current_user();
+-----------------+
| current_user() |
+-----------------+
| dba@192.168.4.% |
+-----------------+
1 row in set (0.00 sec)
</pre>
As you can see the user has been created correctly and password is again in encrypted format. 
In short what you need to do when in need to export users from MySQL/PS 8 is:
<ol>
<li style="font-weight: 400;" aria-level="1">Read the user information</li>
<li style="font-weight: 400;" aria-level="1">Convert Password to hex format when plugin is caching_sha2</li>
<li style="font-weight: 400;" aria-level="1">Push the AS <password> converted to a file or any other way you were used to</li>
</ol>
 
Another possible solution is to use at session level the parameter print_identified_with_as_hex. If set causes SHOW CREATE USER to display such hash values as hexadecimal strings rather than as regular string literals. Hash values that do not contain unprintable characters still display as regular string literals, even with this variable enabled.
This at the end is exactly what Fred and I have done for our tools:
See:
<ul>
<li style="font-weight: 400;" aria-level="1">Fred: <a href="https://github.com/lefred/mysqlshell-plugins/commit/aa5c6bbe9b9aa689bf7266f5a19a35d0091f6568">https://github.com/lefred/mysqlshell-plugins/commit/aa5c6bbe9b9aa689bf7266f5a19a35d0091f6568</a></li>
<li style="font-weight: 400;" aria-level="1">Pt-show-grants: <a href="https://github.com/percona/percona-toolkit/blob/4a812d4a79c0973bf176105b0d138ad0a2a46b2f/bin/pt-show-grants#L2058" rel="nofollow">https://github.com/percona/percona-toolkit/blob/4a812d4a79c0973bf176105b0d138ad0a2a46b2f/bin/pt-show-grants#L2058</a></li>
</ul>
<h1>Conclusions</h1>
MySQL 8 and Percona server comes with a more secure hashing mechanism caching_sha2_password which is also the default. However if you have the need to migrate users and you use your own tools to export and import the passwords, you must update them as indicated. Or use the Percona Toolkit tools that we keep up to date for you.
 
Have fun with MySQL!!]]></description>
<category>MySQL</category>
<pubDate>Sun, 24 Sep 2023 15:39:08 +0000</pubDate>
</item>
<item>
<title>Proof of Concept: Horizontal Write Scaling for MySQL with Kubernetes Operator</title>
<link>http://www.tusacentral.net/joomla/index.php/mysql-blogs/253-horizontal-write-scaling-for-mysql-with-operator-poc</link>
<guid isPermaLink="true">http://www.tusacentral.net/joomla/index.php/mysql-blogs/253-horizontal-write-scaling-for-mysql-with-operator-poc</guid>
<description><![CDATA[Historically MySQL is great in horizontal READ scale. The scaling in that case is offered by the different number of Replica nodes, no matter if using standard asynchronous replication or synchronous replication. 
However those solutions do not offer the same level of scaling for writes operation. 
Why? Because the solutions still rely on writing in one single node that works as Primary. Also in case of multi-Primary the writes will be distributed by transaction. In both cases, when using virtually-synchronous replication, the process will require certification from each node and local (by node) write, as such the number of writes are NOT distributed across multiple nodes but duplicated. 
The main reason behind this is that MySQL is a relational database system (RDBMS), and any data that is going to be written in it, must respect the RDBMS rules (<a href="https://en.wikipedia.org/wiki/Relational_database">https://en.wikipedia.org/wiki/Relational_database</a>). In short any data that is written must be consistent with the data present. To achieve that the data needs to be checked with the existing through defined relations and constraints. This action is something that can affect very large datasets and be very expensive. Think about updating a table with millions of rows that refer to another table with another million rows.
An image may help:
<a href="http://www.tusacentral.net/joomla/images/stories/mysql_horizontal_scaling/schema-db.gif"><img src="http://www.tusacentral.net/joomla/images/stories/mysql_horizontal_scaling/schema-db.gif" alt="schema db" width="478" height="600" class="alignnone size-full wp-image-87678" style="display: block; margin-left: auto; margin-right: auto;" /></a>
Every time I will insert an order, I must be sure that all the related elements are in place and consistent. 
This operation is quite expensive but our database is able to run it in a few milliseconds or less, thanks to several optimizations that allow the node to execute most of them in memory, with none or little access to mass storage. 
The key factor is that the whole data structure resides in the same location (node) facilitating the operations.
Once we have understood that, it will also become clear why we cannot have relational data split in multiple nodes and have to distribute writes by table. If I have a node that manages only the items, another the orders, another the payments, I will need to have my solution able to deal with distributed transactions, each of which needs to certify and verify other nodes data. 
This level of distribution will seriously affect the efficiency of the operation which will increase the response time significantly. This is it, nothing is impossible however the performances will be so impacted that each operation may take seconds instead milliseconds or fraction of it, unless lifting some of the rules breaking the relational model.
MySQL as well as other RDBMS are designed to work respecting the model and cannot scale in any way by fragmenting and distributing a schema, so what can be done to scale?
 
The alternative is to split a consistent set of data into fragments. What is a consistent set of data? It all depends on the kind of information we are dealing with. Keeping in mind the example above where we have a shop online serving multiple customers, we need to identify which is the most effective way to split the data.
For instance if we try to split the data by Product Type (Books, CD/DVD, etc) we will have a huge duplication of data related to customers/orders/shipments and so on, all this data is also quite dynamic given I will have customers constantly ordering things. 
 
Why duplicate the data? Because if I do not duplicate that data I will not know if a customer had already bought or not that specific item, or I will have to ask again about the shipment address and so on. Which means also that any time a customer buys something or puts something in the wish list I have to reconcile the data in all my nodes/clusters.
 
On the other hand if I choose to split my data by country of customer’s residence the only data I will have to duplicate and keep in sync is the one related to the products, of which the most dynamic one will be the number of items in stock. This of course unless I can organize my products by country as well, which is a bit unusual nowadays, but not impossible. 
 
Another possible case is if I am a health organization and I manage several hospitals. As for the example above, it will be easier to split my data by hospital given most of the data related to patients is bound to the hospital itself as well as treatments and any other element related to hospital management. While it will make no sense to split by patient's country of residence.
 
This technique of splitting the data into smaller pieces is called sharding and at the moment is the only way we have to scale RDBM horizontally. 
 
In the MySQL open source ecosystem we have only two consolidated ways to perform sharding, Vitess and ProxySQL. The first one is a complete solution that takes ownership of your database and manages almost any aspect of its operations in a sharded environment, this includes a lot of specific features for DBA to deal with daily operations like table modifications, backup and more. 
 
While this may look great it also comes with some string attached, including the complexity and proprietary environment. That makes Vitess a good fit for “complex” sharding scenarios where other solutions may not be enough.
 
ProxySQL does not have a sharding mechanism “per se” but given the way it works and the features it has, allow us to build simple sharding solutions. 
It is important to note that most of the DBA operations will still be on DBA to be executed with incremented complexity given the sharding environment. 
 
There is a third option which is application aware sharding. 
This solution sees the application be aware of the need to split the data in smaller fragments and internally point the data to different “connectors” who are connected to multiple data sources. 
In this case the application is aware of a customer country and will redirect all the operations related to him to the datasource responsible for the specific fragment.
Normally this solution requires a full code re-design and could be quite difficult to achieve when it is injected after the initial code architecture definition.  
On the other hand, if done at design it is probably the best solution, because it will allow the application to define the sharding rules and can also optimize the different data sources using different technologies for different uses.
One example could be the use of a RDBMS for most of the Online transaction processing (OLTP) data shared by country, and having the products as distributed memory cache with a different technology. At the same time all the data related to orders, payments and customer history can be consolidated in a data warehouse used to generate reporting.    
 
As said the last one is probably the most powerful, scalable and difficult to design and unfortunately it represents probably less than the 5% of the solution currently deployed. 
As well as very few cases are in the need to have a full system/solution to provide scalability with sharding. 
 
By experience, most of the needs for horizontal scaling fell in the simple scenario, where there is the need to achieve sharding and data separation, very often with sharding-nothing architecture. In shared-nothing, each shard can live in a totally separate logical schema instance / physical database server / data center / continent. There is no ongoing need to retain shared access (from between shards) to the other unpartitioned tables in other shards.
 
<h2>The POC</h2>
<h4>Why this POC?</h4>
In the years I have faced a lot of customers that were talking about scaling their database solution and looking at very complex sharding as Vitess as the first and only way to go. 
This without even considering if their needs were driving them there for real. 
<img src="http://www.tusacentral.net/joomla/images/stories/mysql_horizontal_scaling/kiss.png" alt="kiss" width="216" height="188" class="wp-image-87680 alignleft" style="margin-right: 10px; float: left;" />
In my experience, and talking with several colleagues, I am not alone, when analyzing the real needs and after discussing with all the parties impacted, only a very small percentage of customers were in the real need of complex solutions. Most of the others were just trying to avoid a project that will implement simple shared-nothing solutions. Why? Because apparently it is simpler to migrate data to a platform that does all for you, than accept a bit of additional work and challenge at the beginning but keep a simple approach. Also going for the last shining things always has its magic.
On top of that, with the rise of Kubernetes and Mysql Operators, a lot of confusion starts to circulate, most of which generate by the total lack of understanding that a Database and a relational database are two separate things. That lack of understanding the difference and the real problems attached to a RDBMS had brought some to talk about horizontal scaling for databases, with a concerning superficiality and without clarifying if they were talking about RDBMS or not. As such some clarification is long due as well as putting back the KISS principle as the main focus.
 
Given that, I thought that refreshing how ProxySQL could help in building a simple sharding solution may help to clarify the issues, reset the expectations and show how we can do things in a simpler way.  (See my old post <a href="https://www.percona.com/blog/mysql-sharding-with-proxysql/">https://www.percona.com/blog/mysql-sharding-with-proxysql/</a>). 
 
To do so I had built a simple POC that illustrates how you can use Percona Operator for MySQL (POM) and ProxySQL to build a sharded environment with a good level of automation for some standard operations like backup/restore software upgrade and resource scaling. 
 
<h4>Why Proxysql?</h4>
In the following example we mimic a case where we need a simple sharding solution, which means we just need to redirect the data to different data containers, keeping the database maintenance operations on us. In this common case we do not need to implement a full sharding system such as Vitess. 

As illustrated above ProxySQL allows us to set up a common entry point for the application and then redirect the traffic on the base of identified sharding keys. It will also allow us to redirect read/write traffic to the primary and read only traffic to all secondaries. 
 
The other interesting thing is that we can have ProxySQL as part of the application pod, or as an independent service. Best practices indicate that having ProxySQL closer to the application will be more efficient especially if we decide to activate the caching feature.  
 
<h4>Why POM</h4>
Percona Operator for MySQL comes with three main solutions, Percona Operator for PXC, Percona Operator for MySQL Group Replication and Percona Operator for Percona server. The first two are based on virtually-synchronous replication, and allow the cluster to keep the data state consistent across all pods, which guarantees that the service will always offer consistent data.  In K8s context we can see POM as a single service with native horizontal scalability for reads, while for writes we will adopt the mentioned sharding approach.
 
The other important aspects of using a POM based solution is the automation it comes with. Deploying POM you will be able to set automation for backups, software updates, monitoring (using PMM) and last but not least the possibility to scale UP or DOWN just changing the needed resources. 
<h3>The elements used</h3>
<a href="http://www.tusacentral.net/joomla/images/stories/mysql_horizontal_scaling/horizontal_scaling.jpg"><img src="http://www.tusacentral.net/joomla/images/stories/mysql_horizontal_scaling/horizontal_scaling.jpg" alt="horizontal scaling" width="450" height="422" class="alignnone wp-image-87688" /></a>
In our POC I will use a modified version of sysbench (https://github.com/Tusamarco/sysbench), that has an additional field continent and I will use that as a sharding key. At the moment and for the purpose of this simple POC I will only have 2 Shards.
 
As the diagram above illustrates here we have a simple deployment but good enough to illustrate the sharding approach.
We have:
<ul>
<li style="font-weight: 400;" aria-level="1">The application(s) node(s), it is really up to you if you want to test with one application node or more, nothing will change, as well as for the ProxySQL nodes, just keep in mind that if you use more proxysql nodes is better to activate the internal cluster support or use consul to synchronize them. </li>
<li style="font-weight: 400;" aria-level="1">Shard 1 is based on POM with PXC, it has:</li>
</ul>
<ul>
<li style="font-weight: 400;" aria-level="2">Load balancer for service entry point
<ul>
<li style="font-weight: 400;" aria-level="3">Entry point for r/w</li>
<li style="font-weight: 400;" aria-level="3">Entry point for read only</li>
</ul>
</li>
<li style="font-weight: 400;" aria-level="2">3 Pods for Haproxy
<ul>
<li style="font-weight: 400;" aria-level="3">Haproxy container</li>
<li style="font-weight: 400;" aria-level="3">Pmm agent container</li>
</ul>
</li>
<li style="font-weight: 400;" aria-level="2">3 Pods with data nodes (PXC)
<ul>
<li style="font-weight: 400;" aria-level="3">PXC cluster node container</li>
<li style="font-weight: 400;" aria-level="3">Log streaming</li>
<li style="font-weight: 400;" aria-level="3">Pmm container </li>
</ul>
</li>
<li style="font-weight: 400;" aria-level="2">Backup/restore service </li>
</ul>
<ul>
<li style="font-weight: 400;" aria-level="1">Shard 2 is based on POM for Percona server and Group Replication (technical preview)  
<ul>
<li style="font-weight: 400;" aria-level="2">Load balancer for service entry point
<ul>
<li style="font-weight: 400;" aria-level="3">Entry point for r/w</li>
<li style="font-weight: 400;" aria-level="3">Entry point for read only</li>
</ul>
</li>
<li style="font-weight: 400;" aria-level="2">3 Pods for MySQL Router (testing)
<ul>
<li style="font-weight: 400;" aria-level="3">MySQL router container</li>
</ul>
</li>
<li style="font-weight: 400;" aria-level="2">3 Pods with data nodes (PS with GR)
<ul>
<li style="font-weight: 400;" aria-level="3">PS -GR cluster node container</li>
<li style="font-weight: 400;" aria-level="3">Log streaming</li>
<li style="font-weight: 400;" aria-level="3">Pmm container </li>
</ul>
</li>
<li style="font-weight: 400;" aria-level="2">Backup/restore on scheduler</li>
</ul>
</li>
</ul>
 
Now you may have noticed that the representation of the nodes are different in size, this is not a mistake while drawing. It indicates that I have allocated more resources (CPU and Memory) to shard1 than shard2. Why? Because I can and I am simulating a situation where a shard2 gets less traffic, at least temporarily, as such I do not want to give it the same resources as shard1. I will eventually increase them if I see the needs. 
<h2>The settings</h2>
<h3>Data layer</h3>
Let us start with the easy one, the Data layer configuration. Configuring correctly the environment is the key, and to do so I am using a tool that I wrote specifically to calculate the needed configuration in K8s POM environment, you can find it here (<a href="https://github.com/Tusamarco/mysqloperatorcalculator">https://github.com/Tusamarco/mysqloperatorcalculator</a>).
Once you have compiled it and run you can simply ask what “dimensions” are supported, or you can define a custom level of resources, but you will still need to indicate the level of expected load. In any case please refer to the README in the repository which has all the instructions.
The full cr.yaml for PXC shard1 is <a href="https://github.com/Tusamarco/blogs/blob/master/mysql_horizontal_scaling/cr_pxc.yml">here</a>, while the one for PS-GR <a href="https://github.com/Tusamarco/blogs/blob/master/mysql_horizontal_scaling/cr_ps_gr.yml">here</a>. 
For Shard 1: I asked for resources to cover traffic of type 2 (Light OLTP), configuration type 5 (2XLarge) 1000 connections.
For Shard2: I ask for resources to cover traffic of type 2 (Light OLTP), configuration type 2 (Small), 100 connections.     
Once you have the CRs defined you can follow the official guidelines to set the environment up ( PXC (<a href="https://docs.percona.com/percona-operator-for-mysql/pxc/index.html">https://docs.percona.com/percona-operator-for-mysql/pxc/index.html</a>), PS (<a href="https://docs.percona.com/percona-operator-for-mysql/ps/index.html">https://docs.percona.com/percona-operator-for-mysql/ps/index.html</a>)
 
It is time now to see the Proxysql settings.
<h3>ProxySQL and Sharding rules</h3>
As mentioned before we are going to test the load sharding by continent, and as also mentioned before we know that ProxySQL will not provide additional help to automatically manage the sharded environment. 
Given that one way to do it is to create a DBA account per shard, or to inject shard information in the commands while executing. I will use the less comfortable one just to prove if it works, the different DBA accounts. 
 
We will have 2 shards, the sharding key is the continent field, and the continents will be grouped as follows:
<ul>
<li style="font-weight: 400;" aria-level="1">Shard 1:
<ul>
<li style="font-weight: 400;" aria-level="2">Asia</li>
<li style="font-weight: 400;" aria-level="2">Africa</li>
<li style="font-weight: 400;" aria-level="2">Antarctica</li>
<li style="font-weight: 400;" aria-level="2">Europe</li>
<li style="font-weight: 400;" aria-level="2">North America</li>
</ul>
</li>
<li style="font-weight: 400;" aria-level="1">Shard 2:
<ul>
<li style="font-weight: 400;" aria-level="2">Oceania</li>
<li style="font-weight: 400;" aria-level="2">South America</li>
</ul>
</li>
</ul>
 
The DBAs users:
<ul>
<li style="font-weight: 400;" aria-level="1">dba_g1</li>
<li style="font-weight: 400;" aria-level="1">dba_g2</li>
</ul>
 
The application user:
<ul>
<li style="font-weight: 400;" aria-level="1">app_test</li>
</ul>
 
The host groups will be:
<ul>
<li style="font-weight: 400;" aria-level="1">Shard 1
<ul>
<li style="font-weight: 400;" aria-level="2">100 Read and Write</li>
<li style="font-weight: 400;" aria-level="2">101 Read only</li>
</ul>
</li>
<li style="font-weight: 400;" aria-level="1">Shard 2
<ul>
<li style="font-weight: 400;" aria-level="2">200 Read and Write</li>
<li style="font-weight: 400;" aria-level="2">201 Read only</li>
</ul>
</li>
</ul>
 
Once that is defined, we need to identify which query rules will serve us and how.
What we want is to redirect all the incoming queries for:
<ul>
<li style="font-weight: 400;" aria-level="1">Asia, Africa, Antarctica, Europe and North America to shard 1.</li>
<li style="font-weight: 400;" aria-level="1">Oceania and South America to shard 2</li>
<li style="font-weight: 400;" aria-level="1">Split the queries in R/W and Read only</li>
<li style="font-weight: 400;" aria-level="1">Prevent the execution of any query that do not have a shard key</li>
<li style="font-weight: 400;" aria-level="1">Backup data at regular interval and store it in a safe place</li>
</ul>
<a href="http://www.tusacentral.net/joomla/images/stories/mysql_horizontal_scaling/query_rukes_sharding.png"><img src="http://www.tusacentral.net/joomla/images/stories/mysql_horizontal_scaling/query_rukes_sharding.png" alt="query rukes sharding" width="800" height="280" class="alignnone size-large wp-image-87681" /></a>
Given the above we first define the rules for the DBAs accounts:
We set the Hostgroup for each DBA and then if the query matches the sharding rule we redirect it to the proper sharding, otherwise the HG will remain as set.
This allows us to execute queries like CREATE/DROP table on our shard without problem, but will allow us to send data where needed. 
For instance the one below is the output of the queries that sysbench will run.
Prepare:
<pre class="lang:mysql decode:true">INSERT INTO windmills_test1 /* continent=Asia */ (uuid,millid,kwatts_s,date,location,continent,active,strrecordtype) VALUES(UUID(), 79, 3949999,NOW(),'mr18n2L9K88eMlGn7CcctT9RwKSB1FebW397','Asia',0,'quq')
</pre>
In this case I have the application simply injecting a comment in the INSERT SQL declaring the shard key, given I am using the account dba_g1 to create/prepare the schemas, rules  32/32 will be used and give I have sett apply=1, ProxySQL will exit the query rules parsing and send the command to relevant hostgroup.
Run:
<pre class="lang:mysql decode:true">SELECT id, millid, date,continent,active,kwatts_s FROM windmills_test1 WHERE id BETWEEN ? AND ? AND continent='South America'
SELECT SUM(kwatts_s) FROM windmills_test1 WHERE id BETWEEN ? AND ? and active=1 AND continent='Asia'
SELECT id, millid, date,continent,active,kwatts_s FROM windmills_test1 WHERE id BETWEEN ? AND ? AND continent='Oceania' ORDER BY millid
SELECT DISTINCT millid,continent,active,kwatts_s FROM windmills_test1 WHERE id BETWEEN ? AND ? AND active =1 AND continent='Oceania' ORDER BY millid
UPDATE windmills_test1 SET active=? WHERE id=? AND continent='Asia'
UPDATE windmills_test1 SET strrecordtype=? WHERE id=? AND continent='North America'
DELETE FROM windmills_test1 WHERE id=? AND continent='Antarctica'
INSERT INTO windmills_test1 /* continent=Antarctica */ (id,uuid,millid,kwatts_s,date,location,continent,active,strrecordtype) VALUES (?, UUID(), ?, ?, NOW(), ?, ?, ?,?) ON DUPLICATE KEY UPDATE kwatts_s=kwatts_s+1
</pre>
The above are executed during the tests. 
In all of them the sharding key is present, either in the WHERE clause OR as comment. 
Of course if I execute one of them without the sharding key,  the firewall rule will stop the query execution, ie: 
<pre class="lang:mysql decode:true">mysql> SELECT id, millid, date,continent,active,kwatts_s FROM windmills_test1 WHERE id BETWEEN ? AND ?;
ERROR 1148 (42000): It is impossible to redirect this command to a defined shard. Please be sure you Have the Continent definition in your query, or that you use a defined DBA account (dba_g{1/2})
</pre>
Check <a href="https://github.com/Tusamarco/blogs/blob/master/mysql_horizontal_scaling/operator_sharding_with_proxysql_public.txt">here </a>for full command list
<h3>Setting up the dataset</h3>
Once we have the rules set it is time to setup the schemas and the data using sysbench (<a href="https://github.com/Tusamarco/sysbench">https://github.com/Tusamarco/sysbench</a>), remember to use windmills_sharding tests.  
The first operation is to build the schema on SHARD2 without filling it with data. This is a DBA action as such we will execute it using the dba_g2 account:
<pre class="lang:sh decode:true">sysbench ./src/lua/windmills_sharding/oltp_read.lua --mysql-host=10.0.1.96 --mysql-port=6033 --mysql-user=dba_g2 --mysql-password=xxx --mysql-db=windmills_large --mysql_storage_engine=innodb --db-driver=mysql --tables=4 --table_size=0 --table_name=windmills --mysql-ignore-errors=all --threads=1 prepare
</pre>
Setting table_size and pointing to the ProxySQL IP/port will do, and I will have: 
<pre class="lang:mysql decode:true">mysql> select current_user(), @@hostname;
+----------------+-------------------+
| current_user() | @@hostname |
+----------------+-------------------+
| dba_g2@% | ps-mysql1-mysql-0 |
+----------------+-------------------+
1 row in set (0.01 sec)
mysql> use windmills_large;
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_windmills_large |
+---------------------------+
| windmills1 |
| windmills2 |
| windmills3 |
| windmills4 |
+---------------------------+
4 rows in set (0.01 sec)
mysql> select count(*) from windmills1;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.09 sec)
</pre>
All set but empty.
Now let us do the same but with the other DBA user:
<pre class="lang:sh decode:true">sysbench ./src/lua/windmills_sharding/oltp_read.lua --mysql-host=10.0.1.96 --mysql-port=6033 --mysql-user=dba_g1 --mysql-password=xxx --mysql-db=windmills_large --mysql_storage_engine=innodb --db-driver=mysql --tables=4 --table_size=400 --table_name=windmills --mysql-ignore-errors=all --threads=1 prepare
</pre>
If I do now the select above with user dba_g2:
<pre class="lang:mysql decode:true">mysql> select current_user(), @@hostname;select count(*) from windmills1;
+----------------+-------------------+
| current_user() | @@hostname |
+----------------+-------------------+
| dba_g2@% | ps-mysql1-mysql-0 |
+----------------+-------------------+
1 row in set (0.00 sec)
+----------+
| count(*) |
+----------+
| 113 |
+----------+
1 row in set (0.00 sec)
</pre>
While If I reconnect and use dba_g1:
<pre class="lang:mysql decode:true">mysql> select current_user(), @@hostname;select count(*) from windmills1;
+----------------+--------------------+
| current_user() | @@hostname |
+----------------+--------------------+
| dba_g1@% | mt-cluster-1-pxc-0 |
+----------------+--------------------+
1 row in set (0.00 sec)
+----------+
| count(*) |
+----------+
| 287 |
+----------+
1 row in set (0.01 sec)
</pre>
I can also check on ProxySQL to see which rules were utilized:
<pre class="lang:mysql decode:true">select active,hits,destination_hostgroup, mysql_query_rules.rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply,flagIn,flagOUT FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules ORDER BY mysql_query_rules.rule_id;
+------+-----------------------+---------+---------------------+----------------------------------------------------------------------------+-------+--------+---------+
| hits | destination_hostgroup | rule_id | match_digest | match_pattern | apply | flagIN | flagOUT |
+------+-----------------------+---------+---------------------+----------------------------------------------------------------------------+-------+--------+---------+
| 3261 | 100 | 20 | NULL | NULL | 0 | 0 | 500 |
| 51 | 200 | 21 | NULL | NULL | 0 | 0 | 600 |
| 2320 | 100 | 31 | NULL | scontinents*(=|like)s*'*(Asia|Africa|Antarctica|Europe|North America)'* | 1 | 500 | 0 |
| 880 | 200 | 32 | NULL | scontinents*(=|like)s*'*(Oceania|South America)'* | 1 | 500 | 0 |
| 0 | 100 | 34 | NULL | scontinents*(=|like)s*'*(Asia|Africa|Antarctica|Europe|North America)'* | 1 | 600 | 0 |
| 0 | 200 | 35 | NULL | scontinents*(=|like)s*'*(Oceania|South America)'* | 1 | 600 | 0 |
| 2 | 100 | 51 | NULL | scontinents*(=|like)s*'*(Asia|Africa|Antarctica|Europe|North America)'* | 0 | 0 | 1001 |
| 0 | 200 | 54 | NULL | scontinents*(=|like)s*'*(Oceania|South America)'* | 0 | 0 | 1002 |
| 0 | 100 | 60 | NULL | NULL | 0 | 50 | 1001 |
| 0 | 200 | 62 | NULL | NULL | 0 | 60 | 1002 |
| 7 | NULL | 2000 | . | NULL | 1 | 0 | NULL |
| 0 | 100 | 2040 | ^SELECT.*FOR UPDATE | NULL | 1 | 1001 | NULL |
| 2 | 101 | 2041 | ^SELECT.*$ | NULL | 1 | 1001 | NULL |
| 0 | 200 | 2050 | ^SELECT.*FOR UPDATE | NULL | 1 | 1002 | NULL |
| 0 | 201 | 2051 | ^SELECT.*$ | NULL | 1 | 1002 | NULL |
+------+-----------------------+---------+---------------------+----------------------------------------------------------------------------+-------+--------+---------+
</pre>
<h3>Running the application</h3>
Now that the data load test was successful, let us do the real load following the indication as above but use 80 Tables and just a bit more records like 20000, nothing huge. 
 
Once the data is loaded we will have the 2 shards with different numbers of records, if all went well the shard2 should have ¼ of the total and shard1 ¾ .
 
When load is over I have as expected:
<pre class="lang:mysql decode:true">mysql> select current_user(), @@hostname;select count(*) as shard1 from windmills_large.windmills80;select /* continent=shard2 */ count(*) as shard2 from windmills_large.windmills80;
+----------------+--------------------+
| current_user() | @@hostname |
+----------------+--------------------+
| dba_g1@% | mt-cluster-1-pxc-0 |
+----------------+--------------------+
1 row in set (0.00 sec)
+--------+
| shard1 |
+--------+
| 14272 | ← Table windmills80 in SHARD1
+--------+
+--------+
| shard2 |
+--------+
| 5728 | ← Table windmills80 in SHARD2
+--------+
</pre>
As you may have already noticed, I used a trick to query the other shard using the dba_g1 user, I just passed in the query  the shard2 definition as a comment. That is all we need.
Let us execute the run command for writes in sysbench and see what happens.
The first thing we can notice while doing writes is the query distribution: 
<pre class="lang:mysql decode:true">+--------+-----------+----------------------------------------------------------------------------+----------+--------+----------+----------+--------+---------+-------------+---------+
| weight | hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | MaxConnUsed | Queries |
+--------+-----------+----------------------------------------------------------------------------+----------+--------+----------+----------+--------+---------+-------------+---------+
| 10000 | 100 | ac966f7d46c04400fb92a3603f0e2634-193113472.eu-central-1.elb.amazonaws.com | 3306 | ONLINE | 24 | 0 | 138 | 66 | 25 | 1309353 |
| 100 | 101 | a5c8836b7c05b41928ca84f2beb48aee-1936458168.eu-central-1.elb.amazonaws.com | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 |
| 10000 | 200 | a039ab70e9f564f5e879d5e1374d9ffa-1769267689.eu-central-1.elb.amazonaws.com | 3306 | ONLINE | 24 | 1 | 129 | 66 | 25 | 516407 |
| 10000 | 201 | a039ab70e9f564f5e879d5e1374d9ffa-1769267689.eu-central-1.elb.amazonaws.com | 6447 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 |
+--------+-----------+----------------------------------------------------------------------------+----------+--------+----------+----------+--------+---------+-------------+---------+
</pre>
Where we can notice that the load in connection is evenly distributed, while the load is mainly going to shard1 as we expected given we have an unbalanced sharding by design.
 
At MySQL level we had:
 
Questions
<a href="http://www.tusacentral.net/joomla/images/stories/mysql_horizontal_scaling/pxc_ps_write_questions.png"><img src="http://www.tusacentral.net/joomla/images/stories/mysql_horizontal_scaling/pxc_ps_write_questions.png" alt="pxc ps write questions" width="1024" height="187" class="alignnone size-large wp-image-87684" /></a>
Com Type
<a href="http://www.tusacentral.net/joomla/images/stories/mysql_horizontal_scaling/pxc_ps_write_com.png"><img src="http://www.tusacentral.net/joomla/images/stories/mysql_horizontal_scaling/pxc_ps_write_com.png" alt="pxc ps write com" width="1024" height="179" class="alignnone size-large wp-image-87683" /></a>
<a href="http://www.tusacentral.net/joomla/images/stories/mysql_horizontal_scaling/number_operation_writes.png"><img src="http://www.tusacentral.net/joomla/images/stories/mysql_horizontal_scaling/number_operation_writes.png" alt="number operation writes" width="473" height="133" class="alignnone size-full wp-image-87687" /></a>
The final point is, what is the gain using this sharding approach?
Well we still need to consider the fact we are testing on a very small set of data, however if we can already identify some benefit here, that will be an interesting result. 
 
Let see the write operations with 24 and 64 threads:
<a href="http://www.tusacentral.net/joomla/images/stories/mysql_horizontal_scaling/writes_write.png"><img src="http://www.tusacentral.net/joomla/images/stories/mysql_horizontal_scaling/writes_write.png" alt="writes write" width="450" height="395" class="alignnone wp-image-87693" /></a> <a href="http://www.tusacentral.net/joomla/images/stories/mysql_horizontal_scaling/latency_write.png"><img src="http://www.tusacentral.net/joomla/images/stories/mysql_horizontal_scaling/latency_write.png" alt="latency write" width="450" height="395" class="alignnone wp-image-87686" /></a>
We get a gain of ~33% just using sharding, while for latency we do not have a cost on the contrary also with small load increase we can see how the sharded solution performs better. Of course we are still talking about low number of rows and running threads but gain is there. 
 
<h3>Backup </h3>
The backup and restore operation when using POM is completely managed by the operator (see instructions in the POM documentation <a href="https://docs.percona.com/percona-operator-for-mysql/pxc/backups.html">https://docs.percona.com/percona-operator-for-mysql/pxc/backups.html</a> and <a href="https://docs.percona.com/percona-operator-for-mysql/ps/backups.html">https://docs.percona.com/percona-operator-for-mysql/ps/backups.html</a> ).
The interesting part is that we can have multiple kind of backup solution, like:
<ul>
<li style="font-weight: 400;" aria-level="1">On demand</li>
<li style="font-weight: 400;" aria-level="1">Scheduled </li>
<li style="font-weight: 400;" aria-level="1">Full Point in time recovery with log streaming</li>
</ul>
Automation will allow us to set schedule as simple as this: 
<pre class="lang:yaml decode:true"> schedule:
- name: "sat-night-backup"
schedule: "0 0 * * 6"
keep: 3
storageName: s3-eu-west
- name: "daily-backup"
schedule: "0 3 * * *"
keep: 7
storageName: s3-eu-west
</pre>
Or if you want to run the on demand:
<pre class="lang:sh decode:true">kubectl apply -f backup.yaml</pre>
Where the backup.yaml file has very simple informations:
<pre class="lang:yaml decode:true">apiVersion: ps.percona.com/v1alpha1
kind: PerconaServerMySQLBackup
metadata:
name: ps-gr-sharding-test-2nd-of-may
# finalizers:
# - delete-backup
spec:
clusterName: ps-mysql1
storageName: s3-ondemand
</pre>
Using both methods we will be able to soon have a good set of backups like:
POM (PXC)
<pre class="lang:sh decode:true">cron-mt-cluster-1-s3-eu-west-20234293010-3vsve mt-cluster-1 s3-eu-west s3://mt-bucket-backup-tl/scheduled/mt-cluster-1-2023-04-29-03:00:10-full Succeeded 3d9h 3d9h
cron-mt-cluster-1-s3-eu-west-20234303010-3vsve mt-cluster-1 s3-eu-west s3://mt-bucket-backup-tl/scheduled/mt-cluster-1-2023-04-30-03:00:10-full Succeeded 2d9h 2d9h
cron-mt-cluster-1-s3-eu-west-2023513010-3vsve mt-cluster-1 s3-eu-west s3://mt-bucket-backup-tl/scheduled/mt-cluster-1-2023-05-01-03:00:10-full Succeeded 33h 33h
cron-mt-cluster-1-s3-eu-west-2023523010-3vsve mt-cluster-1 s3-eu-west s3://mt-bucket-backup-tl/scheduled/mt-cluster-1-2023-05-02-03:00:10-full Succeeded 9h 9h
</pre>
POM (PS) *
<pre class="lang:sh decode:true">NAME STORAGE DESTINATION STATE COMPLETED AGE
ps-gr-sharding-test s3-ondemand s3://mt-bucket-backup-tl/ondemand/ondemand/ps-mysql1-2023-05-01-15:10:04-full Succeeded 21h 21h
ps-gr-sharding-test-2nd-of-may s3-ondemand s3://mt-bucket-backup-tl/ondemand/ondemand/ps-mysql1-2023-05-02-12:22:24-full Succeeded 27m 27m
</pre>
To note that as DBA, we still need to validate the backups with a restore procedure, that part is not automated (yet). 
*Note that Backup for POM PS is available only on demand given the solution is still in technical preview
<h3>When will this solution fit in?</h3>
As mentioned multiple times, this solution can cover simple cases of sharding, better if you have shared-nothing. 
It also requires work from the DBA side in case of DDL operations or resharding. 
You also need to be able to change some SQL code in order to be sure to have present the sharding key/information, in any SQL executed.
 
<h3>When will this solution not fit in?</h3>
There are several things that could prevent you to use this solution, the most common ones are:
<ul>
<li style="font-weight: 400;" aria-level="1">You need to query multiple shards at the same time. This is not possible with ProxySQL</li>
<li style="font-weight: 400;" aria-level="1">You do not have a DBA to perform administrative work and need to rely on automated system</li>
<li style="font-weight: 400;" aria-level="1">Distributed transaction cross shard</li>
<li style="font-weight: 400;" aria-level="1">No access to SQL code.</li>
</ul>
<h2>Conclusions</h2>
We do not have the Amletic dilemma about sharding or not sharding. 
When using a RDBMS like MySQL, if you need horizontal scalability, you need to shard. 
The point is there is no magic wand or solution, moving to sharding is an expensive and impacting operation. If you choose it at the beginning, before doing any application development, the effort can be significantly less. 
Doing sooner will also allow you to test proper solutions, where proper is a KISS solution, always go for the less complex things, in 2 years you will be super happy about your decision.  
If instead you must convert a current solution, then prepare for a bloodshed, or at least for a long journey. 
In any case we need to keep in mind few key points:
<ul>
<li style="font-weight: 400;" aria-level="1">Do not believe most of the articles on the internet that promise you infinite scalability for your database. If there is no distinction in the article about a simple database and a RDBMS, run away. </li>
<li style="font-weight: 400;" aria-level="1">Do not go for the last shiny things just because they shine. Test them and evaluate IF it makes sense for you. Better to spend a quarter testing now a few solutions, than fight for years with something that you do not fully comprehend.  </li>
<li style="font-weight: 400;" aria-level="1">Using containers/operators/kubernetes do not scale per se, you must find a mechanism to have the solution scaling, there is absolutely NO difference with premises. What you may get is a good level of automation, however that will come with a good level of complexity, it is up to you to evaluate if it makes sense or not.  </li>
</ul>
 
As said at the beginning for MYSQL the choice is limited. Vitess is the full complete solution, with a lot of coding to provide you a complete platform to deal with your scaling needs.
However do not be so fast to exclude ProxySQL as possible solutions. There are out there already many using it also for sharding. 
This small POC used a synthetic case, but it also shows that with just 4 rules you can achieve a decent solution. A real scenario could be a bit more complex … or not. 
 
<h2>References</h2>
Vitess (<a href="https://vitess.io/docs/">https://vitess.io/docs/</a>)
ProxySQL (<a href="https://proxysql.com/documentation/">https://proxysql.com/documentation/</a>)
Firewalling with ProxySQL (<a href="https://www.tusacentral.com/joomla/index.php/mysql-blogs/197-proxysql-firewalling">https://www.tusacentral.com/joomla/index.php/mysql-blogs/197-proxysql-firewalling</a>)
Sharding:
<ul>
<li style="font-weight: 400;" aria-level="1"><a href="https://www.percona.com/blog/mysql-sharding-with-proxysql/">https://www.percona.com/blog/mysql-sharding-with-proxysql/</a></li>
<li style="font-weight: 400;" aria-level="1"><a href="https://www.percona.com/blog/horizontal-scaling-in-mysql-sharding-followup/">https://https://medium.com/pinterest-engineering/sharding-pinterest-how-we-scaled-our-mysql-fleet-3f341e96ca6fwww.percona.com/blog/horizontal-scaling-in-mysql-sharding-followup/</a></li>
<li style="font-weight: 400;" aria-level="1"><a href="https://quoraengineering.quora.com/MySQL-sharding-at-Quora">https://quoraengineering.quora.com/MySQL-sharding-at-Quora</a></li>
<li style="font-weight: 400;" aria-level="1">https://skywalking.apache.org/blog/skywalkings-new-storage-feature-based-on-shardingsphere-proxy-mysql-sharding/</li>
</ul>
 ]]></description>
<category>MySQL</category>
<pubDate>Thu, 11 May 2023 12:20:20 +0000</pubDate>
</item>
<item>
<title>Which is the best Proxy for Percona MySQL Operator?</title>
<link>http://www.tusacentral.net/joomla/index.php/mysql-blogs/252-comparisons-of-proxies-for-mysql</link>
<guid isPermaLink="true">http://www.tusacentral.net/joomla/index.php/mysql-blogs/252-comparisons-of-proxies-for-mysql</guid>
<description><![CDATA[<h2>Overview</h2>
HAProxy, ProxySQL, MySQL Router (AKA MySQL Proxy), in the last few years I had to answer multiple times on what proxy to use and in what scenario. When designing an architecture there are many components that need to be considered before deciding what is the best solution.
When deciding what to pick, there are many things to consider like where the proxy needs to be, if it “just” needs to redirect the connections or if more features need to be in, like caching, filtering, or if it needs to be integrated with some MySQL embedded automation.
Given that, there never was a single straight answer, instead an analysis needs to be done. Only after a better understanding of the environment, the needs and the evolution that the platform needs to achieve is it possible to decide what will be the better choice.
However recently we have seen an increase in usage of MySQL on Kubernetes, especially with the adoption of Percona Operator for MySQL. In this case we have a quite well define scenario that can resemble the image below:
<a href="https://www.percona.com/blog/wp-content/uploads/2023/03/proxys_gr_comparison-default.png"><img src="http://www.tusacentral.net/joomla/images/stories/proxies_comparisons_march_2023/proxys_gr_comparison-default.png" alt="proxys gr comparison default" width="536" height="614" class="aligncenter wp-image-86219 size-full" style="display: block; margin-left: auto; margin-right: auto;" /></a>
In this scenario the proxies need to sit inside Pods balancing the incoming traffic from the Service LoadBalancer connecting with the active data nodes.
Their role is merely to be sure that any incoming connection is redirected to nodes that are able to serve them, which include having a separation between Read/Write and Read Only traffic, separation that can be achieved, at service level, with automatic recognition or with two separate entry points. 
In this scenario it is also crucial to be efficient in resource utilization and scaling with frugality. In this context features like filtering, firewalling or caching are redundant and may consume resources that could be allocated to scaling. Those are also features that will work better outside the K8s/Operator cluster, given the closer to the application they are located, the better they will serve. 
About that we must always remember the concept that each K8s/Operator cluster needs to be seen as a single service, not as a real cluster. In short each cluster is in reality a single database with High Availability and other functionalities built in. 
<a href="https://www.percona.com/blog/wp-content/uploads/2023/03/proxys_gr_comparison-default.png"></a>
Anyhow, we are here to talk about Proxies. Once we have defined that we have one clear mandate in mind, we need to identify which product allow our K8s/Operator solution to:
<ul>
<li style="font-weight: 400;" aria-level="1">Scale at the maximum the number of incoming connections</li>
<li style="font-weight: 400;" aria-level="1">Serve the request with the higher efficiency</li>
<li style="font-weight: 400;" aria-level="1">Consume less resources as possible</li>
</ul>
<a href="https://www.percona.com/blog/wp-content/uploads/2023/03/proxys_gr_comparison-default.png"></a>
<h2>The Environment</h2>
To identify the above points I have simulated a possible K8s/Operator environment, creating:
<ul>
<li style="font-weight: 400;" aria-level="1">One powerful application node, where I run sysbench read only tests, scaling from 2 to 4096 threads. (Type c5.4xlarge)</li>
<li style="font-weight: 400;" aria-level="1">3 Mid data nodes with several gigabytes of data in with MySQL and Group Replication (Type m5.xlarge)</li>
<li style="font-weight: 400;" aria-level="1">1 Proxy node running on a resource limited box (Type t2.micro)</li>
</ul>
<a href="https://www.percona.com/blog/wp-content/uploads/2023/03/proxys_gr_comparison-default.png"></a>
<h2>The Tests </h2>
We will have very simple test cases. The first one has the scope to define the baseline, identifying the moment when we will have the first level of saturation due to the number of connections. In this case we will increase the number of connections and keep a low number of operations. 
The second test will define how well the increasing load is served inside the range we had previously identified. 
For documentation the sysbench commands are:
Test1
<pre class="lang:sh decode:true">sysbench ./src/lua/windmills/oltp_read.lua --db-driver=mysql --tables=200 --table_size=1000000
--rand-type=zipfian --rand-zipfian-exp=0 --skip_trx=true --report-interval=1 --mysql-ignore-errors=all
--mysql_storage_engine=innodb --auto_inc=off --histogram --stats_format=csv --db-ps-mode=disable --point-selects=50
--reconnect=10 --range-selects=true –rate=100 --threads=<#Threads from 2 to 4096> --time=1200 run
</pre>
Test2
<pre class="lang:sh decode:true">sysbench ./src/lua/windmills/oltp_read.lua --mysql-host=<host> --mysql-port=<port> --mysql-user=<user>
--mysql-password=<pw> --mysql-db=<schema> --db-driver=mysql --tables=200 --table_size=1000000 --rand-type=zipfian
--rand-zipfian-exp=0 --skip_trx=true --report-interval=1 --mysql-ignore-errors=all --mysql_storage_engine=innodb
--auto_inc=off --histogram --table_name=<tablename> --stats_format=csv --db-ps-mode=disable --point-selects=50
--reconnect=10 --range-selects=true --threads=<#Threads from 2 to 4096> --time=1200 run
</pre>
<h2>Results</h2>
<h3>Test 1</h3>
As indicated here I was looking to identify when the first Proxy will reach a dimension that would not be manageable. The load is all in creating and serving the connections, while the number of operations is capped to 100. 
<a href="https://www.percona.com/blog/wp-content/uploads/2023/03/events_rate2.png"><img src="http://www.tusacentral.net/joomla/images/stories/proxies_comparisons_march_2023/events_rate2.png" alt="events rate2" width="600" height="401" class="alignnone size-large wp-image-86207" style="display: block; margin-left: auto; margin-right: auto;" /></a>
As you can see and as I was expecting, the three Proxies were behaving more or less the same, serving the same number of operations (they were capped so why not), until not.
MySQL router after 2048 connection was not able to serve anything more.
NOTE: MySQL Router was actually stopped working at 1024 threads, but using version 8.0.32 I enabled the feature: connection_sharing. That allows it to go a bit further.  
Let us take a look also the the latency:
<a href="https://www.percona.com/blog/wp-content/uploads/2023/03/latency95_rate.png"><img src="http://www.tusacentral.net/joomla/images/stories/proxies_comparisons_march_2023/latency95_rate.png" alt="latency95 rate" width="600" height="424" class="alignnone size-large wp-image-86209" style="display: block; margin-left: auto; margin-right: auto;" /></a>
Here the situation starts to be a little bit more complicated. MySQL Router is the one that has the higher latency no matter what. However HAProxy and ProxySQL have an interesting behavior. HAProxy is performing better with a low number of connections, while ProxySQL is doing better when a high number of connections is in place.  
This is due to the multiplexing and the very efficient way ProxySQL uses to deal with high load.
Everything has a cost:
<a href="https://www.percona.com/blog/wp-content/uploads/2023/03/node-summary-cpu.png"><img src="http://www.tusacentral.net/joomla/images/stories/proxies_comparisons_march_2023/node-summary-cpu.png" alt="node summary cpu" width="600" height="241" class="alignnone size-large wp-image-86215" style="display: block; margin-left: auto; margin-right: auto;" /></a>
HAProxy is definitely using less user’s CPU resources than ProxySQL or MySQL Router …
<a href="https://www.percona.com/blog/wp-content/uploads/2023/03/node-summary-cpu-saturation.png"><img src="http://www.tusacentral.net/joomla/images/stories/proxies_comparisons_march_2023/node-summary-cpu-saturation.png" alt="node summary cpu saturation" width="600" height="238" class="alignnone size-large wp-image-86214" style="display: block; margin-left: auto; margin-right: auto;" /></a>
.. we can also notice that HAProxy barely reaches on average the 1.5 CPU load while ProxySQL is at 2.50 and MySQL Router around 2. 
To be honest I was expecting something like this, given ProxySQL's need to handle the connections and the other basic routing. What was instead a surprise was MySQL Router, why does it have a higher load?
<h4>Brief summary</h4>
This test highlights that HAProxy and ProxySQL are able to reach a level of connection higher than the slowest runner in the game (MySQL Router). It is also clear that traffic is better served under a high number of connections by ProxySQL but it requires more resources. 
<h2>Test 2</h2>
When the going gets tough, the tough gets going
Well let us remove the –rate limitation and see what will happen. 
<a href="https://www.percona.com/blog/wp-content/uploads/2023/03/events_perf.png"><img src="http://www.tusacentral.net/joomla/images/stories/proxies_comparisons_march_2023/events_perf.png" alt="events perf" width="800" height="475" class="alignnone size-large wp-image-86205" style="display: block; margin-left: auto; margin-right: auto;" /></a>
The scenario with load changes drastically. We can see how HAProxy is able to serve the connection and allow the execution of a higher number of operations for the whole test. ProxySQL is immediately after it and is behaving quite well up to 128 threads, then it just collapses. 
MySQL Router never takes off; it always stays below the 1k reads/second while HAProxy was able to serve 8.2k and  ProxySQL 6.6k.
<a href="https://www.percona.com/blog/wp-content/uploads/2023/03/latency95_perf.png"><img src="http://www.tusacentral.net/joomla/images/stories/proxies_comparisons_march_2023/latency95_perf.png" alt="latency95 perf" width="800" height="531" class="alignnone size-large wp-image-86208" style="display: block; margin-left: auto; margin-right: auto;" /></a>
Taking a look at the Latency, we can see that HAProxy had a gradual increase as expected, while ProxySQL and MySQL Router just went up from the 256 threads on. 
To observe that both ProxySQL and MySQL Router were not able to complete the tests with 4096 threads.
<a href="https://www.percona.com/blog/wp-content/uploads/2023/03/node-summary-cpu-perf.png"><img src="http://www.tusacentral.net/joomla/images/stories/proxies_comparisons_march_2023/node-summary-cpu-perf.png" alt="node summary cpu perf" width="800" height="318" class="alignnone size-large wp-image-86212" style="display: block; margin-left: auto; margin-right: auto;" /></a>
Why? HAProxy stays always below 50% cpu, no matter the increasing number of threads/connections, scaling the load very efficiently. MySQL router was almost immediately reaching the saturation point, being affected not only by the number of threads/connections but also by the number of operations, and that was not expected given we do not have a level 7 capability in MySQL Router.
Finally ProxySQL, which was working fine up to a certain limit, then it reached saturation point and was not able to serve the load. I am saying load because ProxySQL is a level 7 proxy and is aware of the content of the load. Given that on top of multiplexing, additional resource consumption was expected.   
<a href="https://www.percona.com/blog/wp-content/uploads/2023/03/node-summary-cpu-saturation-perf.png"><img src="http://www.tusacentral.net/joomla/images/stories/proxies_comparisons_march_2023/node-summary-cpu-saturation-perf.png" alt="node summary cpu saturation perf" width="800" height="319" class="alignnone size-large wp-image-86213" style="display: block; margin-left: auto; margin-right: auto;" /></a>
Here we just have a clear confirmation of what already said above, with 100% cpu utilization reached by MySQL Router with just 16 threads, and ProxySQL way after at 256 threads.
<h2>Brief Summary</h2>
HAProxy comes up as the champion in this test, there is no doubt that it was able to scale the increasing load in connection without being affected significantly by the load generated by the requests. The lower consumption in resources also indicates the possible space for even more scaling.
ProxySQL was penalized by the limited resources, but this was the game, we have to get the most out of the few available. This test indicates that it is not optimal to use ProxySQL inside the Operator, actually it is a wrong choice if low resource and scalability is a must.    
MySQL Router was never in the game. Unless a serious refactoring, MySQL Router is designed for very limited scalability, as such the only way to adopt it is to have many of them at application node level. Utilizing it close to the data nodes in a centralized position is a mistake.  
<h2>Conclusions</h2>
I started showing an image on how the MySQL service is organized and want to close showing the variation that for me is the one to be considered the default approach:
<a href="https://www.percona.com/blog/wp-content/uploads/2023/03/proxys_gr_comparison-haproxy.jpg"><img src="https://www.percona.com/blog/wp-content/uploads/2023/03/proxys_gr_comparison-haproxy.jpg" alt="" width="536" height="651" class="size-full wp-image-86220 aligncenter" style="display: block; margin-left: auto; margin-right: auto;" /></a>
This to highlight that we always need to choose the right tool for the job. 
The Proxy in architectures involving MySQL/PS/PXC is a crucial element for the scalability of the cluster, no matter if using K8s or not. It is important to choose the one that serves us better, which in some cases can be ProxySQL over HAProxy. <a href="https://www.percona.com/blog/wp-content/uploads/2023/03/proxys_gr_comparison-haproxy.jpg"> </a>
However when talking of K8s and Operators we must recognize the need to optimize the resources usage for the specific service. In that context there is no discussion about it, HAProxy is the best solution and the one we should go to. <a href="https://www.percona.com/blog/wp-content/uploads/2023/03/proxys_gr_comparison-haproxy.jpg"> </a>
My final observation is about MySQL Router (aka MySQL Proxy). 
Unless a significant refactoring of the product at the moment it is not even close to what the other two can do. From the tests done so far, it requires a complete reshaping starting to identify why it is so subject to the load coming from the query, more than the load coming from the connections.   
<a href="https://www.percona.com/blog/wp-content/uploads/2023/03/proxys_gr_comparison-haproxy.jpg"></a>
Great MySQL to everyone. 
<h2>References</h2>
https://www.percona.com/blog/boosting-percona-distribution-for-mysql-operator-efficiency/
https://www.slideshare.net/marcotusa/my-sql-on-kubernetes-demystified
https://docs.haproxy.org/2.7/configuration.html
https://proxysql.com/documentation/
https://dev.mysql.com/doc/mysql-router/8.0/en/
 
 ]]></description>
<category>MySQL</category>
<pubDate>Sun, 19 Mar 2023 17:57:59 +0000</pubDate>
</item>
<item>
<title>Help! I am out of disk space!</title>
<link>http://www.tusacentral.net/joomla/index.php/mysql-blogs/251-help-i-am-out-of-disk-space</link>
<guid isPermaLink="true">http://www.tusacentral.net/joomla/index.php/mysql-blogs/251-help-i-am-out-of-disk-space</guid>
<description><![CDATA[How we could  fix a nasty out of space issue leveraging the flexibility of Percona MySQL operator (PMO)  <img src="http://www.tusacentral.net/joomla/images/stories/DiskSpaceFull.png" alt="DiskSpaceFull" width="350" height="208" style="float: right;" />
When planning a database deployment, one of the most challenging factors to consider is the amount of space we need to dedicate for Data on disk.
This is even more cumbersome when working on bare metal. Given it is definitely more difficult to add space when using this kind of solution in respect to the cloud. 
This is it, when using cloud storage like EBS or similar it is normally easy(er) to extend volumes, which gives us the luxury to plan the space to allocate for data with a good grade of relaxation. 
Is this also true when using a solution based on Kubernetes like Percona Operator for MySQL? Well it depends on where you run it, however if the platform you choose supports the option to extend volumes K8s per se is giving you the possibility to do so as well.
However, if it can go wrong it will, and ending up with a fully filled device with MySQL is not a fun experience. 
As you know, on normal deployments, when mysql has no space left on the device, it simply stops working, ergo it will cause a production down event, which of course is an unfortunate event that we want to avoid at any cost.  
This blog is the story of what happened, what was supposed to happen and why. 
<h2>The story </h2>
Case was on AWS using EKS.
Given all the above, I was quite surprised when we had a case in which a deployed solution based on PMO went out of space. However we start to dig and review what was going on and why.
The first thing we did was to quickly investigate what was really taking space, that could have been an easy win if most of the space was taken by some log, but unfortunately this was not the case, data was really taking all the available space. 
The next step was to check what storage class was used for the PVC
<pre class="lang:sh decode:true">k get pvc
NAME VOLUME CAPACITY ACCESS MODES STORAGECLASS
datadir-mt-cluster-1-pxc-0 pvc-<snip> 233Gi RWO io1
datadir-mt-cluster-1-pxc-1 pvc-<snip> 233Gi RWO io1
datadir-mt-cluster-1-pxc-2 pvc-<snip> 233Gi RWO io1
</pre>
Ok we use the io1 SC, it is now time to check if the SC is supporting volume expansion:
<pre class="lang:sh decode:true">kubectl describe sc io1
Name: io1
IsDefaultClass: No
Annotations: kubectl.kubernetes.io/last-applied-configuration={"apiVersion":"storage.k8s.io/v1","kind":"StorageClass","metadata":{"annotations":{"storageclass.kubernetes.io/is-default-class":"false"},"name":"io1"},"parameters":{"fsType":"ext4","iopsPerGB":"12","type":"io1"},"provisioner":"kubernetes.io/aws-ebs"}
,storageclass.kubernetes.io/is-default-class=false
Provisioner: kubernetes.io/aws-ebs
Parameters: fsType=ext4,iopsPerGB=12,type=io1
AllowVolumeExpansion: <unset> <------------
MountOptions: <none>
ReclaimPolicy: Delete
VolumeBindingMode: Immediate
Events: <none>
</pre>
And no is not enabled, in this case we cannot just go and expand the volume, must change the storage class settings first. To enable volume expansion, you need to delete the storage class and enable it again. 
Unfortunately we were unsuccessful in doing that operation, because the storage class kept staying as unset for  ALLOWVOLUMEEXPANSION. 
As said this is a production down event, so we cannot invest too much time in digging why it was not correctly changing the mode, we had to act quickly. 
The only option we had to fix it was:
<ul>
<li style="font-weight: 400;" aria-level="1">Expand the io1 volumes from AWS console (or aws client)</li>
<li style="font-weight: 400;" aria-level="1">Resize the file system </li>
<li style="font-weight: 400;" aria-level="1">Patch any K8 file to allow K8 to correctly see the new volumes dimension  </li>
</ul>
Expanding EBS volumes from the console is trivial, just go to Volumes, select the volume you want to modify, choose modify and change the size of it with the one desired, done. 
Once that is done connect to the Node hosting the pod which has the volume mounted like:
<pre class="lang:sh decode:true"> k get pods -o wide|grep mysql-0
NAME READY STATUS RESTARTS AGE IP NODE
cluster-1-pxc-0 2/2 Running 1 11d 10.1.76.189 <mynode>.eu-central-1.compute.internal</pre>
Then we need to get the id of the pvc to identify it on the node
<pre class="lang:sh decode:true">k get pvc
NAME STATUS VOLUME CAPACITY ACCESS MODES STORAGECLASS
datadir-cluster-1-pxc-0 Bound pvc-1678c7ee-3e50-4329-a5d8-25cd188dc0df 233Gi RWO io1
</pre>
One note, when doing this kind of recovery with a PXC based solution, always recover node-0 first, then the others.  
So we connect to <mynode> and identify the volume: 
<pre class="lang:sh decode:true">lslbk |grep pvc-1678c7ee-3e50-4329-a5d8-25cd188dc0df
nvme1n1 259:4 0 350G 0 disk /var/lib/kubelet/pods/9724a0f6-fb79-4e6b-be8d-b797062bf716/volumes/kubernetes.io~aws-ebs/pvc-1678c7ee-3e50-4329-a5d8-25cd188dc0df <-----
</pre>
At this point we can resize it:
<pre class="lang:sh decode:true">root@ip-<snip>:/# resize2fs /dev/nvme1n1
resize2fs 1.45.5 (07-Jan-2020)
Filesystem at /dev/nvme1n1 is mounted on /var/lib/kubelet/plugins/kubernetes.io/aws-ebs/mounts/aws/eu-central-1a/vol-0ab0db8ecf0293b2f; on-line resizing required
old_desc_blocks = 30, new_desc_blocks = 44
The filesystem on /dev/nvme1n1 is now 91750400 (4k) blocks long.
</pre>
The good thing is that as soon as you do that the MySQL daemon see the space and will restart, however it will happen only on the current pod and K8 will still see the old dimension:
<pre class="lang:sh decode:true">k get pv
NAME CAPACITY ACCESS MODES RECLAIM POLICY STATUS CLAIM STORAGECLASS REASON
pvc-1678c7ee-3e50-4329-a5d8-25cd188dc0df 333Gi RWO Delete Bound pxc/datadir-cluster-1-pxc-0 io1
</pre>
To allow k8 to be align with the real dimension we must patch the information stored, the command is the following:
<pre class="lang:sh decode:true">kubectl patch pvc <pvc-name> -n <pvc-namespace> -p '{ "spec": { "resources": { "requests": { "storage": "NEW STORAGE VALUE" }}}}'
Ie:
kubectl patch pvc datadir-cluster-1-pxc-0 -n pxc -p '{ "spec": { "resources": { "requests": { "storage": "350" }}}}'
</pre>
Remember to use as pvc-name the NAME coming from 
<pre>kubectl get pvc.</pre>
Once this is done k8 will see the new volume dimension correctly.
Just repeat the process for Node-1 and Node-2 and …done the cluster is up again.
Finally do not forget to modify your custom resources file (cr.yaml) to match the new volume size. IE:
<pre class="lang:sh decode:true"> volumeSpec:
persistentVolumeClaim:
storageClassName: "io1"
resources:
requests:
storage: 350G
</pre>
The whole process took just a few minutes, it was time now to investigate why the incident happened and why the storage class was not allowing extension in the first place.  
 
<h2>Why it happened</h2>
Well first and foremost the platform was not correctly monitored. As such there was lack of visibility about the space utilization, and no alert about disk space. 
This was easy to solve just enabling the PMM feature in the cluster cr and set the alert in PMM once the nodes join it (see <a href="https://docs.percona.com/percona-monitoring-and-management/get-started/alerting.html">https://docs.percona.com/percona-monitoring-and-management/get-started/alerting.html</a> for details on how to).
The second issue was the problem with the storage class. Once we had the time to carefully review the configuration files, we identified that there was an additional tab in the SC class, which was causing k8 to ignore the directive. 
Was suppose to be:
<pre class="lang:sh decode:true">kind: StorageClass
apiVersion: storage.k8s.io/v1
metadata:
name: io1
annotations:
storageclass.kubernetes.io/is-default-class: "false"
provisioner: kubernetes.io/aws-ebs
parameters:
type: io1
iopsPerGB: "12"
fsType: ext4
allowVolumeExpansion: true <----------
It was:
kind: StorageClass
apiVersion: storage.k8s.io/v1
metadata:
name: io1
annotations:
storageclass.kubernetes.io/is-default-class: "false"
provisioner: kubernetes.io/aws-ebs
parameters:
type: io1
iopsPerGB: "12"
fsType: ext4
allowVolumeExpansion: true. <---------</pre>
What was concerning was the lack of error returned by the Kubernetes API, so in theory the configuration was accepted but not really validated. 
In any case once we had fix the typo and recreated the SC, the setting for volume expansion was correctly accepted:
<pre class="lang:sh decode:true">kubectl describe sc io1
Name: io1
IsDefaultClass: No
Annotations: kubectl.kubernetes.io/last-applied-configuration={"allowVolumeExpansion":true,"apiVersion":"storage.k8s.io/v1","kind":"StorageClass","metadata":{"annotations":{"storageclass.kubernetes.io/is-default-class":"false"},"name":"io1"},"parameters":{"fsType":"ext4","iopsPerGB":"12","type":"io1"},"provisioner":"kubernetes.io/aws-ebs"}
,storageclass.kubernetes.io/is-default-class=false
Provisioner: kubernetes.io/aws-ebs
Parameters: fsType=ext4,iopsPerGB=12,type=io1
AllowVolumeExpansion: True
MountOptions: <none>
ReclaimPolicy: Delete
VolumeBindingMode: Immediate
Events: <none>
</pre>
<h2>What should have happened instead?</h2>
If a proper monitoring and alerting was in place, the administrators would have the time to act and extend the volumes without downtime. 
However, the procedure for extending volumes on K8 is not complex but also not as straightforward as you may think. My colleague Natalia Marukovich wrote this blog post (<a href="https://www.percona.com/blog/percona-operator-volume-expansion-without-downtime/">https://www.percona.com/blog/percona-operator-volume-expansion-without-downtime/</a>)  that gives you the step by step instructions on how to extend the volumes without downtime.
<h2>Conclusions</h2>
Using the cloud, containers, automation or more complex orchestrators like Kubernetes, do not solve all, do not prevent mistakes from happening, and more importantly do not make the right decisions for you. 
You must set up a proper architecture that includes backup, monitoring and alerting. You must set the right alerts and act on them in time. 
Finally automation is cool, however the devil is in the details and typos are his day to day joy. Be careful and check what you put online, do not rush it. Validate, validate validate… 
Great stateful MySQL to all. ]]></description>
<category>MySQL</category>
<pubDate>Thu, 19 Jan 2023 14:11:24 +0000</pubDate>
</item>
<item>
<title>MySQL Dual password how to manage them programmatically</title>
<link>http://www.tusacentral.net/joomla/index.php/mysql-blogs/250-mysql-dual-password-how-to-manage-them-programmatically</link>
<guid isPermaLink="true">http://www.tusacentral.net/joomla/index.php/mysql-blogs/250-mysql-dual-password-how-to-manage-them-programmatically</guid>
<description><![CDATA[What is dual password in MYSQL and how it works was already covered by my colleague Brian Sumpter here (<a href="https://www.percona.com/blog/using-mysql-8-dual-passwords/">https://www.percona.com/blog/using-mysql-8-dual-passwords/</a>). <img src="http://www.tusacentral.net/joomla/images/stories/rpa-cognitive-blog-img.jpg" alt="rpa cognitive blog img" width="400" height="215" style="float: right;" />
However let me do a brief recap here about it.
Dual password is the MySQL mechanism that allows you to keep two passwords active at the same time. This feature is part of a more extended set of Password management features implemented in MySQL 8 to enforce better security and secrets management, like:
<ul>
<li style="font-weight: 400;" aria-level="1">Internal Versus External Credentials Storage</li>
<li style="font-weight: 400;" aria-level="1">Password Expiration Policy</li>
<li style="font-weight: 400;" aria-level="1">Password Reuse Policy</li>
<li style="font-weight: 400;" aria-level="1">Password Verification-Required Policy</li>
<li style="font-weight: 400;" aria-level="1">Dual Password Support</li>
<li style="font-weight: 400;" aria-level="1">Random Password Generation</li>
<li style="font-weight: 400;" aria-level="1">Failed-Login Tracking and Temporary Account Locking</li>
</ul>
The most important and requested features are the password expiration and verification policy. The problem in implementing them is the complexity of replacing passwords for accounts on very large platforms, like with thousands of applications and hundreds of MySQL servers. 
In fact, while for a single user it is not so complex to change his own password when requested at login, for an application using thousands of sub-services it may require some time. The problem in performing the password change is that while executing the modification some services will have the updated password while others will still use the old one. Without Dual Password a segment of nodes will receive error messages in connecting creating service disruption.
Now let us cover this blog topic. 
With Dual Password it is instead possible to declare a new password keeping the old still active until the whole upgrade has been completed. 
This highlight two very important aspects:
<ul>
<li style="font-weight: 400;" aria-level="1">When automating the password update, it is better to not use a password expiration policy, but base the expiration on the completion of the new password deployment.</li>
<li style="font-weight: 400;" aria-level="1">We need to be sure the account we are changing the password to keeps the password active until we need it, and that is correctly removed when done. </li>
</ul>
As you see I am focusing on the cases when we have automation and not the single interactive user update. 
How dual password works
Let us assume we have create a user like:
<pre class="lang:mysql decode:true">create user dualtest@'192.168.4.%' identified by 'password1';
grant all on test.* to dualtest@'192.168.4.%';
</pre>
This will generate an entry in MySQL mysql.user table as:
<pre class="lang:mysql decode:true">(root@localhost) [(none)]>select user,host, plugin, authentication_string, password_last_changed,User_attributes from mysql.user where user = 'dualtest'\G
*************************** 1. row ***************************
user: dualtest
host: 192.168.4.%
plugin: mysql_native_password
authentication_string: *668425423DB5193AF921380129F465A6425216D0
password_last_changed: 2022-11-17 08:31:37
User_attributes: NULL
1 row in set (0.00 sec)
</pre>
At this point our user will be able to connect from any application located in the correct network and act on the test schema. 
After some time, you as application owner,will be notified by your DBA team that the user dualtest is required to change the password in order to respect the security constraints.
At this point there are two options:
<ol>
<li style="font-weight: 400;" aria-level="1">You have privileges  to use Dual Password (the required dynamic privilege to use dual Password is APPLICATION PASSWORD ADMIN).</li>
<li style="font-weight: 400;" aria-level="1">You do not have the right privileges.</li>
</ol>
In case 2 your DBA team must perform the change for you, and then they will let you know the new password.
In case 1 you can do the operation yourself. 
In the last case what you will do is: 
<pre class="lang:mysql decode:true crayon-selected">ALTER USER 'dualtest'@'192.168.4.%' IDENTIFIED BY 'password2' RETAIN CURRENT PASSWORD;
</pre>
Then check it is done properly:
<pre class="lang:mysql decode:true">select user,host, plugin, authentication_string, password_last_changed,User_attributes from mysql.user where user ='dualtest' order by 1,2\G
*************************** 1. row ***************************
user: dualtest
host: 192.168.4.%
plugin: mysql_native_password
authentication_string: *DC52755F3C09F5923046BD42AFA76BD1D80DF2E9
password_last_changed: 2022-11-17 08:46:28
User_attributes: {"additional_password": "*668425423DB5193AF921380129F465A6425216D0"}
1 row in set (0.00 sec)
</pre>
As you can see here the OLD password has been moved to the User_attributes JSON field that is used in MYSQL8 to store several values. 
At this point you can rollout safely the password change and that change can take an hour or a week, no production impact given the applications will be able to use either of them. 
Once the process is complete, you can ask your DBA team to remove OLD password, or do:
<pre class="lang:mysql decode:true">ALTER USER 'dualtest'@'192.168.4.%' DISCARD OLD PASSWORD;</pre>
Then check if the password has being removed properly:
<pre class="lang:mysql decode:true">(root@localhost) [(none)]>select user,host, plugin, authentication_string, password_last_changed,User_attributes from mysql.user where user ='dualtest' order by 1,2\G
*************************** 1. row ***************************
user: dualtest
host: 192.168.4.%
plugin: mysql_native_password
authentication_string: *DC52755F3C09F5923046BD42AFA76BD1D80DF2E9
password_last_changed: 2022-11-17 08:46:28
User_attributes: NULL
1 row in set (0.00 sec)
</pre>
If all is clean the process can be considered complete. 
Of course all this should be automated and executed by code not by hand high level it should be more or less like this:
<pre class="lang:sh decode:true">{}Input new password
- Check for additional_password in User_attributes in mysql.user
<> If no value you can proceed otherwise exit (another change is in place)
- Read and store authentication_string for the user you need to change password
- Change current password with: Alter user ... RETAIN CURRENT PASSWORD
- Check for additional_password in User_attributes in mysql.user
<> If value is present and match the password stored then you can proceed otherwise exit given there is an error in Dual Password or the passwords are different
- Run update on all application nodes, and verify new password on each application node
<> At regular interval check the number of completed changes and check the additional_password in User_attributes in mysql.user to be sure it is still there
[] When all application nodes are up to date
<> If verification is successful 100%
- Remove OLD password with: ALTER USER ... DISCARD OLD PASSWORD;
- Check for additional_password in User_attributes in mysql.user
<> If no value is present close with OK otherwise report Error for password not removed
() complete
</pre>
<h2>Conclusion</h2>
As also Brian mentioned, those are the small things that could make the difference when in large deployments and enterprise environments. Security is a topic that very often is underestimated in small companies or start-ups, but that is wrong, security operations like password rotation are crucial for your safety. 
It is nice to see that MySQL is finally adopting simple but effective steps to help DBAs to implement proper procedures without causing production impact and without the need to become too creative. 
<h2> </h2>
<h2>References </h2>
<a href="https://www.percona.com/blog/using-mysql-8-dual-passwords/">https://www.percona.com/blog/using-mysql-8-dual-passwords/</a>
<a href="https://dev.mysql.com/doc/refman/8.0/en/password-management.html#dual-passwords">https://dev.mysql.com/doc/refman/8.0/en/password-management.html#dual-passwords</a>]]></description>
<category>MySQL</category>
<pubDate>Thu, 17 Nov 2022 16:26:45 +0000</pubDate>
</item>
<item>
<title>ProxySQL support for MySQL caching_sha2_password</title>
<link>http://www.tusacentral.net/joomla/index.php/mysql-blogs/249-proxysql-support-for-mysql-caching-sha2-password</link>
<guid isPermaLink="true">http://www.tusacentral.net/joomla/index.php/mysql-blogs/249-proxysql-support-for-mysql-caching-sha2-password</guid>
<description><![CDATA[In our time, every day we use dozens if not hundreds of applications connecting to some kind of data repository. This simple step is normally executed over the network and given so, it is subject to possible sniffing with all the possible related consequences.<img src="http://www.tusacentral.net/joomla/images/stories/proxysql/brokenlock.png" alt="brokenlock" style="float: right;" /> 
Given that it is normally better to protect your connection using data encryption like SSL, or at the minimum, make the information you pass to connect less easy to be intercepted. 
At the same time it is best practice to not store connection credential in clear text, not even inside a table in your database. Doing that is the equivalent of writing your password over a sticky note on your desk. Not a good idea.
The main options are instead in either transforming the passwords to be less identifiable like hashing or to store the information in an external centralized vault. 
In MySQL the passwords are transformed in order to not be clear text, and several different plugins are used to authenticate the user. From version 8 MySQL uses caching_sha2_password as default authentication plugin. The caching_sha2_password and sha256_password authentication plugins provide more secure password encryption than the mysql_native_password plugin, and caching_sha2_password provides better performance than sha256_password. Due to these superior security and performance characteristics of caching_sha2_password, it is as of MySQL 8.0 the preferred authentication plugin, and is also the default authentication plugin rather than mysql_native_password.
In this regard recently I got the same question again “Can we use ProxySQL with MySQL 8 authorization mechanism?”, and I decided it was time to write this short blog post.
The short answer is “Yes you can”, however do not expect to have full caching_sha2_password support.
This is because ProxySQL does not fully support the caching_sha2_password mechanism internally and given that a “trick” must be used. 
So, what should we do when using MySQL 8 and ProxySQL? 
In the text below we will see what can be done to continue to use ProxySQL with MySQL and Percona server 8. 
Note that I have used the Percona proxysql_admin tool to manage the users except in the last case. Percona proxysql_admin tool is a nice tool that helps you to manage ProxySQL and in regard to user it also manage and synchronize users from your Percona or MySQL  
In the following examples:
Proxysql is on 192.168.4.191
User name/password is msandbox/msandbox
<h2>Using hashing.</h2>
By default MySQL comes with caching_sha2_password as such if I create a user names msandbox I will have: 
<pre class="lang:mysql decode:true">DC1-1(root@localhost) [(none)]>select user,host, authentication_string,plugin from mysql.user order by 1,2;
+----------------------------+--------------------+------------------------------------------------------------------------+-----------------------+
| user | host | authentication_string | plugin |
+----------------------------+--------------------+------------------------------------------------------------------------+-----------------------+
| msandbox | % | $A$005$Z[z@l'O%[Q5t^ EKJDgxjWXJjDpDEUv91oL7Hoh/0NydTeCzpV.aI06C9. | caching_sha2_password | <---- this user
+----------------------------+--------------------+------------------------------------------------------------------------+-----------------------+
</pre>
Then I use percona_scheduler_admin to sync the users:
<pre class="lang:mysql decode:true">./percona-scheduler-admin --config-file=config.toml --syncusers 
Syncing user accounts from PXC(192.168.4.205:3306) to ProxySQL
Removing existing user from ProxySQL: msandbox
Adding user to ProxySQL: msandbox
Synced PXC users to the ProxySQL database!
mysql> select * from mysql_users ;
+------------+------------------------------------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+-----------------------------+
| username   | password                                                               | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | attributes | comment                     |
+------------+------------------------------------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+-----------------------------+
| msandbox   | $A$005$Z[z@l'O%[Q5t^ EKJDgxjWXJjDpDEUv91oL7Hoh/0NydTeCzpV.aI06C9 | 1      | 0       | 100               | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |            |                             |
+------------+------------------------------------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+-----------------------------+</pre>
And set the query rules:
<pre class="lang:mysql decode:true">insert into mysql_query_rules (rule_id,proxy_port,username,destination_hostgroup,active,retries,match_digest,apply) values(1048,6033,'msandbox',100,1,3,'^SELECT.*FOR UPDATE',1);
insert into mysql_query_rules (rule_id,proxy_port,username,destination_hostgroup,active,retries,match_digest,apply) values(1050,6033,'msandbox',101,1,3,'^SELECT.*$',1);
load mysql query rules to run;save mysql query rules to disk;
</pre>
Now I try to connect passing by ProxySQL:
<pre class="lang:sh decode:true"># mysql -h 192.168.4.191 -P6033 -umsandbox -pmsandbox
ERROR 1045 (28000): ProxySQL Error: Access denied for user 'msandbox'@'192.168.4.191' (using password: YES)
</pre>
My account will fail to connect given failed authentication.
To fix this I need to drop the user and recreate it with a different authentication plugin in my MySQL server:
<pre class="lang:mysql decode:true">drop user msandbox@'%';
create user 'msandbox'@'%' identified with mysql_native_password BY 'msandbox';
grant select on *.* to 'msandbox'@'%';
select user,host, authentication_string,plugin from mysql.user order by 1,2;
+----------+--------------------+-------------------------------------------+-----------------------+
| user | host | authentication_string | plugin |
+----------+--------------------+-------------------------------------------+-----------------------+
| msandbox | % | *6C387FC3893DBA1E3BA155E74754DA6682D04747 | mysql_native_password |
+----------+--------------------+-------------------------------------------+-----------------------+
</pre>
At this point I can re-run 
<pre class="lang:sh decode:true">./percona-scheduler-admin --config-file=config.toml --syncusers</pre>
if I try to connect again: 
<pre class="lang:mysql decode:true"># mysql -h 192.168.4.191 -P6033  -umsandbox -pmsandbox
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6708563
Server version: 8.0.28 (ProxySQL). <---------------------------- Connecting to proxysql
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
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> show global variables like 'version%';
+-------------------------+------------------------------------------------------------------------------------+
| Variable_name           | Value                                                                              |
+-------------------------+------------------------------------------------------------------------------------+
| version                 | 8.0.25-15.1         <--- Percona/MySQL version                                     |
| version_comment         | Percona XtraDB Cluster binary (GPL) 8.0.25, Revision 8638bb0, WSREP version 26.4.3 |
| version_compile_machine | x86_64                                                                             |
| version_compile_os      | Linux                                                                              |
| version_compile_zlib    | 1.2.11                                                                             |
| version_suffix          | .1                                                                                 |
+-------------------------+------------------------------------------------------------------------------------+
6 rows in set (0.02 sec)</pre>
This is the only way to keep the password hashed in MySQL and in ProxySQL.
<h2>Not using Hashing</h2>
What if you cannot use mysql_native_password for the password in your MySQL server?
There is a way to still connect, however I do not recommend it given for me is highly insecure, but for completeness I am going to illustrate it.
First of all disable password hashing in Proxysql:
<pre class="lang:mysql decode:true">update global_variables set Variable_Value='false' where Variable_name='admin-hash_passwords'; </pre>
At this point instead sync the user you can locally create the user like:
<pre class="lang:mysql decode:true">insert into mysql_users (username,password,active,default_hostgroup,default_schema,transaction_persistent,comment) values ('msandbox','msandbox',1,100,'mysql',1,'generic test for security'); 
mysql> select * from runtime_mysql_users where username ='msandbox'; 
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------------------------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | attributes | comment                   |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------------------------+
| msandbox | msandbox | 1      | 0       | 100               | mysql          | 0             | 1                      | 0            | 1       | 0        | 10000           |            | generic test for security |
| msandbox | msandbox | 1      | 0       | 100               | mysql          | 0             | 1                      | 0            | 0       | 1        | 10000           |            | generic test for security |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------------------------+</pre>
As you can see doing that will prevent the password to be hashed and instead it will be clear text.
At this point you will be able to connect to MySQL 8 using the caching_sha2_password, but the password is visible in ProxySQL.
Let me repeat, I DO NOT recommend using it this way, because for me it is highly insecure. 
 
<h1>Conclusion</h1>
While it is still possible to configure your user in MySQL to connect using ProxySQL, it is obvious that we have a gap in the way ProxySQL supports security. 
The hope is that this gap will be filled soon by the ProxySQL development team, also if looking to the past issues this seems pending from years now. 
<h1>References</h1>
<a href="https://proxysql.com/documentation/mysql-8-0/">https://proxysql.com/documentation/mysql-8-0/</a>
<a href="https://github.com/sysown/proxysql/issues/2580">https://github.com/sysown/proxysql/issues/2580</a>
https://www.percona.com/blog/upgrade-your-libraries-authentication-plugin-caching_sha2_password-cannot-be-loaded/]]></description>
<category>MySQL</category>
<pubDate>Thu, 03 Nov 2022 13:47:19 +0000</pubDate>
</item>
<item>
<title>Zero impact on index creation with Aurora 3</title>
<link>http://www.tusacentral.net/joomla/index.php/mysql-blogs/248-zero-impact-on-index-creation-with-aurora-3</link>
<guid isPermaLink="true">http://www.tusacentral.net/joomla/index.php/mysql-blogs/248-zero-impact-on-index-creation-with-aurora-3</guid>
<description><![CDATA[<img src="http://www.tusacentral.net/joomla/images/stories/ddl/aurora_ddl_notes.jpeg" alt="aurora ddl notes" style="float: right;" />Last quarter of 2021 AWS released Aurora version 3. This new version aligns Aurora with the latest MySQL 8 version porting many of the advantages MySQL 8 has over previous versions.
While this brings a lot of new interesting features for Aurora, what we are going to cover here is to see how DDLs behave when using the ONLINE option. With a quick comparison with what happens in MySQL 8 standard and with Group Replication. 
<h2>Tests</h2>
All tests were run on an Aurora instance r6g.large with secondary availability zone. The test was composed by:
        4 connections
<ul>
<li style="list-style-type: none;">
<ul>
<li style="font-weight: 400;" aria-level="2">#1 to perform ddl</li>
<li style="font-weight: 400;" aria-level="2">#2 to perform insert data in the table I am altering</li>
<li style="font-weight: 400;" aria-level="2">#3 to perform insert data on a different table </li>
<li style="font-weight: 400;" aria-level="2">#4 checking the other node operations</li>
</ul>
</li>
</ul>
In the Aurora instance, a sysbench schema with 10 tables and 5 million rows was created, just to get a bit of traffic. While the test table with 5ml rows as well was: 
<pre class="lang:mysql decode:true">CREATE TABLE `windmills_test` (
`id` bigint NOT NULL AUTO_INCREMENT,
`uuid` char(36) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`millid` smallint NOT NULL,
`kwatts_s` int NOT NULL,
`date` date NOT NULL,
`location` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`active` tinyint NOT NULL DEFAULT '1',
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`strrecordtype` char(3) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`id`),
KEY `IDX_millid` (`millid`,`active`),
KEY `IDX_active` (`id`,`active`),
KEY `kuuid_x` (`uuid`),
KEY `millid_x` (`millid`),
KEY `active_x` (`active`),
KEY `idx_1` (`uuid`,`active`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin ROW_FORMAT=DYNAMIC
</pre>
The executed commands:
<pre class="lang:sh decode:true">Connection 1:
ALTER TABLE windmills_test ADD INDEX idx_1 (`uuid`,`active`), ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE windmills_test drop INDEX idx_1, ALGORITHM=INPLACE;
Connection 2:
while [ 1 = 1 ];do da=$(date +'%s.%3N');mysql --defaults-file=./my.cnf -D windmills_large -e "insert into windmills_test select null,uuid,millid,kwatts_s,date,location,active,time,strrecordtype from windmills4 limit 1;" -e "select count(*) from windmills_large.windmills_test;" > /dev/null;db=$(date +'%s.%3N'); echo "$(echo "($db - $da)"|bc)";sleep 1;done
Connection 3:
while [ 1 = 1 ];do da=$(date +'%s.%3N');mysql --defaults-file=./my.cnf -D windmills_large -e "insert into windmills3 select null,uuid,millid,kwatts_s,date,location,active,time,strrecordtype from windmills4 limit 1;" -e "select count(*) from windmills_large.windmills_test;" > /dev/null;db=$(date +'%s.%3N'); echo "$(echo "($db - $da)"|bc)";sleep 1;done
Connections 4:
while [ 1 = 1 ];do echo "$(date +'%T.%3N')";mysql --defaults-file=./my.cnf -h <secondary aurora instance> -D windmills_large -e "show full processlist;"|egrep -i -e "(windmills_test|windmills_large)"|grep -i -v localhost;sleep 1;done
</pre>
Operations: 1) start inserts from connections 2) start commands in connections 4 - 5 on the other nodes 3) execute: DC1-1(root@localhost) [windmills_large]>ALTER TABLE windmills_test ADD INDEX idx_1 (`uuid`,`active`), ALGORITHM=INPLACE, LOCK=NONE;
With this, what I was looking to capture is the operation impact in doing a common action as creating an Index. My desired expectation is to have no impact when doing operations that are declared “ONLINE” such as creating an index, as well as data consistency between nodes. 
Let us see what happened…
<h2>Results</h2>
While running the insert in the same table, performing the alter:
<pre class="lang:mysql decode:true">mysql> ALTER TABLE windmills_test ADD INDEX idx_1 (`uuid`,`active`), ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (16.51 sec)
Records: 0 Duplicates: 0 Warnings: 0
</pre>
Is NOT stopping the operation in the same table or any other table in the Aurora instance.
We can only identify a minimal performance impact:
<pre class="lang:sh decode:true">[root@ip-10-0-0-11 tmp]# while [ 1 = 1 ];do da=$(date +'%s.%3N');mysql --defaults-file=./my.cnf -D windmills_large -e "insert into windmills_test select null,uuid,millid,kwatts_s,date,location,active,time,strrecordtype from windmills4 limit 1;" -e "select count(*) from windmills_large.windmills_test;" > /dev/null;db=$(date +'%s.%3N'); echo "$(echo "($db - $da)"|bc)";sleep 1;done
.347
.283
.278
.297
.291
.317
.686 ← start
<Snip>
.512 ← end
.278
.284
.279
</pre>
The secondary node is not affected at all, and this is because Aurora managed at storage level the data replication. Given that there is no such thing as Apply from Relaylog, as we have in standard MySQL asynchronous or data replicated with Group Replication.  
The result is that in Aurora 3, we can have zero impact index (or any other ONLINE/INSTANT) operation, with this I include the data replicated in the other instances for High Availability. 
If we compare this with Group replication (see <a href="https://www.percona.com/blog/online-ddl-with-group-replication-in-mysql-8-0-27/">blog</a>):
<pre class="lang:sh decode:true"> GR Aurora 3
Time on hold for insert for altering table ~0.217 sec ~0.523 sec
Time on hold for insert for another table ~0.211 sec ~0.205 sec
</pre>
However, keep in mind that MySQL with Group Replication will still need to apply the data on the Secondaries. This means that if your alter was taking 10 hours to build the index, the Secondary nodes will be misaligned with the Source for approximately another 10 hours. 
With Aurora 3 or with PXC, changes will be there when Source has completed the operation.    
What about Percona XtraDB Cluster (PXC)? Well, with PXC we have a different scenario:
<pre class="lang:sh decode:true"> PXC(NBO) Aurora 3
Time on hold for insert for altering table ~120 sec ~0.523 sec
Time on hold for insert for another table ~25 sec ~0.205 sec</pre>
We will have a higher impact while doing the Alter operation, but the data will be on all nodes at the same time maintaining a high level of consistency in the cluster. 
<h2>Conclusion</h2>
Aurora is not for all use, and not for all budgets, however it has some very good aspects like the one we have just seen. The Difference between standard MySQL and Aurora is not in the time of holding/locking (aka operation impact), but on the HA aspects. If I have my data/structure on all my Secondary at the same time of the Source, I will feel much more comfortable, than having to wait an additional T time. 
This is why PXC in that case is a better alternative if you can afford locking time, if not, well Aurora 3 is your solution, just do your math properly and be conservative with the instance resources. 
 ]]></description>
<category>MySQL</category>
<pubDate>Wed, 20 Apr 2022 12:05:56 +0000</pubDate>
</item>
<item>
<title>A face to face with semi-synchronous replication</title>
<link>http://www.tusacentral.net/joomla/index.php/mysql-blogs/247-a-face-to-face-with-semi-synchronous-replication</link>
<guid isPermaLink="true">http://www.tusacentral.net/joomla/index.php/mysql-blogs/247-a-face-to-face-with-semi-synchronous-replication</guid>
<description><![CDATA[<a href="http://www.tusacentral.net/joomla/images/stories/semi-sync/face_to_face.jpeg"><img src="http://www.tusacentral.net/joomla/images/stories/semi-sync/face_to_face.jpeg" alt="" width="300" height="212" class="size-medium wp-image-80671 alignright" style="float: right;" /></a>
Last month I performed a review of the Percona Operator for MySQL Server (<a href="https://www.percona.com/doc/kubernetes-operator-for-mysql/ps/index.html">https://www.percona.com/doc/kubernetes-operator-for-mysql/ps/index.html</a>) which is still Alpha.  That operator is based on Percona Server and uses standard asynchronous replication, with the option to activate semi-synchronous  replication to gain higher levels of data consistency between nodes.
The whole solution is composed as:
<a href="https://www.percona.com/blog/wp-content/uploads/2022/04/operator.svg"><img src="https://www.percona.com/blog/wp-content/uploads/2022/04/operator.svg" alt="" width="499" height="409" class="alignnone wp-image-80663" /></a>
Additionally, Orchestrator (<a href="https://github.com/openark/orchestrator">https://github.com/openark/orchestrator</a>) is used to manage the topology and the settings to enable on the replica nodes, the semi-synchronous flag if required. While we have not too much to say when using standard Asynchronous replication, I want to spend two words on the needs and expectations on the semi-synchronous (semi-sync) solution.
<h2>A look into semi-synchronous</h2>
Difference between Async and Semi-sync. Asynchronous:
<a href="http://www.tusacentral.net/joomla/images/stories/semi-sync/async-replication-diagram.png"><img src="http://www.tusacentral.net/joomla/images/stories/semi-sync/async-replication-diagram.png" alt="" width="725" height="304" class="alignnone size-full wp-image-80662" /></a>
The above diagram represents the standard asynchronous replication. This method is expected by design, to have transactions committed on the Source that are not present on the Replicas. The Replica is supposed to catch-up when possible.   
It is also important to understand that there are two steps in replication:
<ul>
<li style="font-weight: 400;" aria-level="1">Data copy, which is normally very fast. The Data is copied from the binlog of the Source to the relay log on the Replica (IO_Thread).</li>
<li style="font-weight: 400;" aria-level="1">Data apply, where the data is read from the relay log on the Replica node and written inside the database itself (SQL_Thread). This step is normally the bottleneck and while there are some parameters to tune, the efficiency to apply transactions depends on many factors including schema design. </li>
</ul>
Production deployments that utilize the Asynchronous solution are typically designed to manage the possible inconsistent scenario given data on Source is not supposed to be on Replica at commit. At the same time the level of High Availability assigned to this solution is lower than the one we normally obtain with (virtually-)synchronous replication, given we may need to wait for the Replicare to catch-up the gap accumulated in the relay-logs before performing the fail-over.
Semi-sync:
<a href="http://www.tusacentral.net/joomla/images/stories/semi-sync/semisync-replication-diagram.png"><img src="http://www.tusacentral.net/joomla/images/stories/semi-sync/semisync-replication-diagram.png" alt="" width="725" height="396" class="alignnone size-full wp-image-80665" /></a>
The above diagram represents the Semi-sync replication method.The introduction of semi-sync adds a checking step on the Source before it returns the acknowledgement to the client. This step happens at the moment of the data-copy, so when the data is copied from the Binary-log on Source to the Relay-log on Replica. 
This is important, there is NO mechanism to ensure a more resilient or efficient data replication, there is only an additional step, that tells the Source to wait a given amount of time for an answer from N replicas, and then return the acknowledgement or timeout and return to the client no matter what. 
This mechanism is introducing a possible significant delay in the service, without giving the 100% guarantee of data consistency. 
In terms of availability of the service, when in presence of high load, this method may lead the Source to stop serving the request while waiting for acknowledgements, significantly reducing the availability of the service itself.   
At the same time only acceptable settings for rpl_semi_sync_source_wait_point is AFTER_SYNC (default) because:  In the event of source failure, all transactions committed on the source have been replicated to the replica (saved to its relay log). An unexpected exit of the source server and failover to the replica is lossless because the replica is up to date.
All clear? No? Let me simplify the thing. 
<ul>
<li style="font-weight: 400;" aria-level="1">In standard replication you have two moments (I am simplifying)
<ul>
<li style="font-weight: 400;" aria-level="2">Copy data from Source to Replica</li>
<li style="font-weight: 400;" aria-level="2">Apply data in the Replica node</li>
</ul>
</li>
<li style="font-weight: 400;" aria-level="1">There is no certification on the data applied about its consistency with the Source</li>
<li style="font-weight: 400;" aria-level="1">With asynchronous the Source task is to write data in the binlog and forget</li>
<li style="font-weight: 400;" aria-level="1">With semi-sync the Source writes the data on binlog and waits T seconds to receive acknowledgement from N servers about them having received the data.</li>
</ul>
To enable semi-sync you follow these steps:  <a href="https://dev.mysql.com/doc/refman/8.0/en/replication-semisync-installation.html">https://dev.mysql.com/doc/refman/8.0/en/replication-semisync-installation.html</a>
In short:
<ul>
<li style="font-weight: 400;" aria-level="1">Register the plugins</li>
<li style="font-weight: 400;" aria-level="1">Enable Source rpl_semi_sync_source_enabled=1</li>
<li style="font-weight: 400;" aria-level="1">Enable Replica rpl_semi_sync_replica_enabled = 1
<ul>
<li style="font-weight: 400;" aria-level="2">If replication is already running STOP/START REPLICA IO_THREAD</li>
</ul>
</li>
</ul>
And here starts the fun, be ready for many “wait whaaat?”. 
What is the T and N I have just mentioned above?
Well the T is a timeout that you can set to avoid having the source wait forever for the Replica acknowledgement. The default is 10 seconds. What happens if the Source waits for more than the timeout?  <img src="http://www.tusacentral.net/joomla/images/stories/semi-sync/wait_whaat.jpeg" alt="" width="115" height="79" class="wp-image-80666 alignright" style="float: right;" /> rpl_semi_sync_source_timeout controls how long the source waits on a commit for acknowledgment from a replica before timing out and reverting to asynchronous replication.
Careful of the wording here! The manual says SOURCE, so it is not that MySQL revert to asynchronous, by transaction or connection, it is for the whole server.
Now analyzing the work-log (see <a href="https://dev.mysql.com/worklog/task/?id=1720">https://dev.mysql.com/worklog/task/?id=1720</a> and more in the references) the Source should revert to semi-synchronous as soon as all involved replicas are aligned again. 
However, checking the code (see  <a href="https://github.com/mysql/mysql-server/blob/beb865a960b9a8a16cf999c323e46c5b0c67f21f/plugin/semisync/semisync_source.cc#L844">https://github.com/mysql/mysql-server/blob/beb865a960b9a8a16cf999c323e46c5b0c67f21f/plugin/semisync/semisync_source.cc#L844</a> and following), we can see that we do not have a 100% guarantee that the Source will be able to switch back.
Also in the code: But, it is not that easy to detect that the replica has caught up.  This is caused by the fact that MySQL's replication protocol is  asynchronous, meaning that if thesource does not use the semi-sync protocol, the replica would not send anything to thesource.
In all the runned tests the Source was not able to switch back. In short Source was moving out from semi-sync and that was forever, no rollback. Keep in mind that while we go ahead.
What is the N I mentioned above? It represents the number of Replicas that must provide the acknowledgement back. 
If you have a cluster of 10 nodes you may need to have only 2 of them involved in the semi-sync, no need to include them all. But if you have a cluster of 3 nodes where 1 is the Source, relying on 1 Replica only, is not really secure. What I mean here is that if you choose to be semi-synchronous to ensure the data replicates, having it enabled for one single node is not enough, if that node crashes or whatever, you are doomed, as such you need at least 2 nodes with semi-sync.
Anyhow, the point is that if one of the Replica takes more than T to reply, the whole mechanism stops working, probably forever. 
As we have seen above, to enable semi-sync on Source we manipulate the value of the GLOBAL variable rpl_semi_sync_source_enabled.
However if I check the value of rpl_semi_sync_source_enabled when the Source shift to simple Asynchronous replication because timeout:
select @@rpl_semi_sync_source_enabled;
<pre class="lang:mysql decode:true">select @@rpl_semi_sync_source_enabled;
+--------------------------------+
| @@rpl_semi_sync_source_enabled |
+--------------------------------+
| 1 |
+--------------------------------+
</pre>
As you can see the Global variable reports a value of 1, meaning that semi-sync is active also if not.
In the documentation it is reported that to monitor the semi-sync activity we should check for Rpl_semi_sync_source_status. Which means that you can have <a href="https://www.percona.com/blog/wp-content/uploads/2022/04/wait_whaat.jpeg"><img src="http://www.tusacentral.net/joomla/images/stories/semi-sync/wait_whaat.jpeg" alt="" width="113" height="77" class="wp-image-80666 alignright" style="float: right;" /></a> Rpl_semi_sync_source_status = 0 and rpl_semi_sync_source_enabled =1 at the same time.
Is this a bug? Well according to documentation: When the source switches between asynchronous or semisynchronous replication due to commit-blocking timeout or a replica catching up, it sets the value of the Rpl_semi_sync_source_status or Rpl_semi_sync_source_status status variable appropriately. Automatic fallback from semisynchronous to asynchronous replication on the source means that it is possible for the rpl_semi_sync_source_enabled or rpl_semi_sync_source_enabled system variable to have a value of 1 on the source side even when semisynchronous replication is in fact not operational at the moment. You can monitor the Rpl_semi_sync_source_status or Rpl_semi_sync_source_status status variable to determine whether the source currently is using asynchronous or semisynchronous replication.
It is not a bug. However, because you documented it, it doesn’t change the fact this is a weird/unfriendly/counterintuitive way of doing, that opens the door to many, many possible issues. Especially given you know the Source may fail to switch semi-synch back.  
Just to close this part, we can summarize as follows:
<ul>
<li style="font-weight: 400;" aria-level="1">You activate semi-sync setting a global variable</li>
<li style="font-weight: 400;" aria-level="1">Server/Source can disable it (silently) without changing that variable </li>
<li style="font-weight: 400;" aria-level="1">Server will never restore semi-sync automatically</li>
<li style="font-weight: 400;" aria-level="1">The way to check if semi-sync works is to use the Status variable</li>
<li style="font-weight: 400;" aria-level="1">When Rpl_semi_sync_source_status = 0 and rpl_semi_sync_source_enabled =1 you had a Timeout and Source is now working in asynchronous replication</li>
<li style="font-weight: 400;" aria-level="1">The way to reactivate semi-sync is to set rpl_semi_sync_source_enabled  to OFF first then rpl_semi_sync_source_enabled = ON. </li>
<li style="font-weight: 400;" aria-level="1">Replicas can be set with semi-sync ON/OFF but unless you do not STOP/START the replica_IO_THREAD the state of the variable can be inconsistent with the state of the Server.</li>
</ul>
What can go wrong?
<h2>Semi-synchronous is not seriously affecting the performance</h2>
Others had already discussed semi-sync performance in better details. However I want to add some color given the recent experience with our operator testing. In the next graphs I will show you the behavior of writes/reads using Asynchronous replication and the same load with Semi-synchronous. For the record the test was a simple Sysbench-tpcc test using 20 tables, 20 warehouses, 256 threads for 600 seconds.
<a href="http://www.tusacentral.net/joomla/images/stories/semi-sync/2.png"><img src="http://www.tusacentral.net/joomla/images/stories/semi-sync/2.png" alt="" width="900" height="401" class="alignnone wp-image-80652 size-large" /></a> <a href="http://www.tusacentral.net/joomla/images/stories/semi-sync/3.png"><img src="http://www.tusacentral.net/joomla/images/stories/semi-sync/3.png" alt="" width="900" height="421" class="alignnone size-large wp-image-80653" /></a>
The one above indicates a nice and consistent set of load in r/w with minimal fluctuations. This is what we like to have. 
The graphs below, represent the exact same load on the exact same environment but with semi-sync activated and no timeout. 
<a href="http://www.tusacentral.net/joomla/images/stories/semi-sync/9.png"><img src="http://www.tusacentral.net/joomla/images/stories/semi-sync/9.png" alt="" width="900" height="421" class="alignnone wp-image-80657 size-large" /></a> <a href="http://www.tusacentral.net/joomla/images/stories/semi-sync/10.png"><img src="http://www.tusacentral.net/joomla/images/stories/semi-sync/10.png" alt="" width="900" height="420" class="alignnone wp-image-80658 size-large" /></a>
Aside from the performance loss (we went from Transaction 10k/s to 3k/s), the constant stop/go imposed by the semi-sync mechanism has a very bad effect on the application behavior when you have many concurrent threads and high loads. I challenge any serious production system to work in this way.   
Of course results are inline with this yoyo game:
<a href="http://www.tusacentral.net/joomla/images/stories/semi-sync/1c.png"><img src="http://www.tusacentral.net/joomla/images/stories/semi-sync/1c.png" alt="" width="900" height="356" class="alignnone size-large wp-image-80651" /></a>
In the best case, when all was working as expected, and no crazy stuff happening I had something around the 60% loss. I am not oriented to see this as  a minor performance drop. 
<h2>But at least your data is safe</h2>
As already stated at the beginning the scope of semi-synchronous replication is to guarantee that the data in server A reaches server B before returning the OK to the application. 
In short, given a period of 1 second we should have minimal transactions in flight and limited transactions in the apply queue. While for standard replication (asynchronous), we may have … thousands. 
In the graphs below we can see two lines:
<ul>
<li style="font-weight: 400;" aria-level="1">The yellow line represents the number of GTIDs “in flight” from Source to destination, Y2 axes.  In case of Source crash, those transactions are lost and we will have data loss.</li>
<li style="font-weight: 400;" aria-level="1">The blue line represents the number of GTIDs already copied over from Source to Replica but not applied in the database Y1 axes. In case of Source crash we must wait for the Replica to process these entries, before making the node Write active, or we will have data inconsistency.</li>
</ul>
Asynchronous replication:
<a href="http://www.tusacentral.net/joomla/images/stories/semi-sync/4.png"><img src="http://www.tusacentral.net/joomla/images/stories/semi-sync/4.png" alt="" width="900" height="526" class="alignnone wp-image-80654 size-large" /></a>
As expected we can see a huge queue in applying the transactions from relay-log, and some spike of transactions in flight. 
Using Semi-synchronous replication:
<a href="http://www.tusacentral.net/joomla/images/stories/semi-sync/8.png"><img src="http://www.tusacentral.net/joomla/images/stories/semi-sync/8.png" alt="" width="900" height="421" class="alignnone size-large wp-image-80656" /></a>
Yes, apparently we have reduced the queue and no spikes so no data loss.
But this happens  when all goes as expected, and we know in production this is not the normal. What if we need to enforce the semi-sync but at the same time we cannot set the Timeout to ridiculous values like 1 week? 
Simple, we need to have a check that puts back the semi-sync as soon as it is silently disabled (for any reason). However doing this without waiting for the Replicas to cover the replication gap, cause the following interesting effects:
<a href="http://www.tusacentral.net/joomla/images/stories/semi-sync/5.png"><img src="http://www.tusacentral.net/joomla/images/stories/semi-sync/5.png" alt="" width="900" height="525" class="alignnone size-large wp-image-80655" /></a>
Thousands of transactions queued and shipped with the result of having a significant increase of the possible data loss and still a huge number of data to apply from the relay-log. 
So the only possible alternative is to set the Timeout to a crazy value, However this can cause a full production stop in the case a Replica hangs or for any reason it disables the semi-sync locally. 
 
<h2>Conclusion</h2>
First of all I want to say that the tests on our operator using Asynchronous replication, shows a consistent behavior with the standard deployments in the cloud or premises.  It has the same benefits, like better performance and same possible issues as longer time to failover when it needs to wait a Replica to apply the relay-log queue. 
The semi-synchronous flag in the operator is disabled, and the tests I have done bring me to say “keep it like that!”. At least unless you know very well what you are doing and are able to deal with a semi-sync timeout of days.
I was happy to have the chance to perform these tests, because they gives me a way/time/need to investigate more on the semi-synchronous feature. Personally, I was not convinced about the semi-synchronous replication when it came out, and I am not now. I never saw a less consistent and less trustable feature in MySQL as semi-sync.  
If you need to have a higher level of synchronicity in your database just go for Group Replication, or Percona XtraDB Cluster and stay away from semi-sync. 
Otherwise, stay on Asynchronous replication, which is not perfect but it is predictable.  
<h2>References</h2>
<a href="https://www.percona.com/blog/2012/01/19/how-does-semisynchronous-mysql-replication-work/">https://www.percona.com/blog/2012/01/19/how-does-semisynchronous-mysql-replication-work/</a>
<a href="https://www.percona.com/blog/percona-monitoring-and-management-mysql-semi-sync-summary-dashboard/">https://www.percona.com/blog/percona-monitoring-and-management-mysql-semi-sync-summary-dashboard/</a>
<a href="https://www.percona.com/blog/2012/06/14/comparing-percona-xtradb-cluster-with-semi-sync-replication-cross-wan/">https://www.percona.com/blog/2012/06/14/comparing-percona-xtradb-cluster-with-semi-sync-replication-cross-wan/</a>
<a href="https://datto.engineering/post/lossless-mysql-semi-sync-replication-and-automated-failover">https://datto.engineering/post/lossless-mysql-semi-sync-replication-and-automated-failover</a>
<a href="https://planetscale.com/blog/mysql-semi-sync-replication-durability-consistency-and-split-brains">https://planetscale.com/blog/mysql-semi-sync-replication-durability-consistency-and-split-brains</a>
<a href="https://percona.community/blog/2018/08/23/question-about-semi-synchronous-replication-answer-with-all-the-details/">https://percona.community/blog/2018/08/23/question-about-semi-synchronous-replication-answer-with-all-the-details/</a>
<a href="https://dev.mysql.com/doc/refman/8.0/en/replication-semisync-installation.html">https://dev.mysql.com/doc/refman/8.0/en/replication-semisync-installation.html</a>
<a href="https://dev.mysql.com/worklog/task/?id=1720">https://dev.mysql.com/worklog/task/?id=1720</a>
<a href="https://dev.mysql.com/worklog/task/?id=6630">https://dev.mysql.com/worklog/task/?id=6630</a>
<a href="https://dev.mysql.com/worklog/task/?id=4398">https://dev.mysql.com/worklog/task/?id=4398</a>
<a href="https://github.com/mysql/mysql-server/blob/beb865a960b9a8a16cf999c323e46c5b0c67f21f/plugin/semisync/semisync_source.cc#L844">https://github.com/mysql/mysql-server/blob/beb865a960b9a8a16cf999c323e46c5b0c67f21f/plugin/semisync/semisync_source.cc#L844</a>
<a href="https://github.com/mysql/mysql-server/blob/beb865a960b9a8a16cf999c323e46c5b0c67f21f/plugin/semisync/semisync_source.cc#L881">https://github.com/mysql/mysql-server/blob/beb865a960b9a8a16cf999c323e46c5b0c67f21f/plugin/semisync/semisync_source.cc#L881</a>]]></description>
<category>MySQL</category>
<pubDate>Tue, 12 Apr 2022 10:00:01 +0000</pubDate>
</item>
<item>
<title>Online DDL with Group Replication In MySQL 8.0.27</title>
<link>http://www.tusacentral.net/joomla/index.php/mysql-blogs/246-online-ddl-with-group-replication-in-mysql-8-0-27</link>
<guid isPermaLink="true">http://www.tusacentral.net/joomla/index.php/mysql-blogs/246-online-ddl-with-group-replication-in-mysql-8-0-27</guid>
<description><![CDATA[Last April 2021 I wrote an <a href="https://www.percona.com/blog/2021/04/15/online-ddl-with-group-replication-in-percona-server-for-mysql-8-0-22/">article</a> about Online DDL and Group Replication. At that time we were dealing with MySQL 8.0.23 and also opened a <a href="https://bugs.mysql.com/bug.php?id=103421">bug</a> report which did not have the right answer to the case presented.
Anyhow, in that article I have shown how an online DDL was de facto locking the whole cluster for a very long time even when using the consistency level set to EVENTUAL.
This article is to give justice to the work done by the MySQL/Oracle engineers to correct that annoying inconvenience. 
Before going ahead, let us remember how an Online DDL was propagated in a group replication cluster, and identify the differences with what happens now, all with the consistency level set to EVENTUAL (<a href="https://dev.mysql.com/doc/refman/8.0/en/group-replication-configuring-consistency-guarantees.html">see</a>).
In MySQL 8.0.23 we were having:
<table>
<tbody>
<tr>
<td><a href="https://www.percona.com/blog/wp-content/uploads/2022/01/1-gr-ddl.png"><img src="http://www.tusacentral.net/joomla/images/stories/gr_ddl_8_0_27/1-gr-ddl.png" alt="1 gr ddl" width="201" height="222" class="alignnone size-full wp-image-79683" /></a></td>
<td><a href="https://www.percona.com/blog/wp-content/uploads/2022/01/gr-ddl-2-old.png"><img src="http://www.tusacentral.net/joomla/images/stories/gr_ddl_8_0_27/gr-ddl-2-old.png" alt="gr ddl 2 old" width="201" height="222" class="alignnone size-full wp-image-79689" /></a></td>
<td><a href="https://www.percona.com/blog/wp-content/uploads/2022/01/gr-ddl-3-old.png"><img src="http://www.tusacentral.net/joomla/images/stories/gr_ddl_8_0_27/gr-ddl-3-old.png" alt="gr ddl 3 old" width="211" height="242" class="alignnone size-full wp-image-79684" /></a></td>
</tr>
</tbody>
</table>
While in MySQL 8.0.27 we have:
<table>
<tbody>
<tr>
<td><a href="https://www.percona.com/blog/wp-content/uploads/2022/01/1-gr-ddl.png"><img src="http://www.tusacentral.net/joomla/images/stories/gr_ddl_8_0_27/1-gr-ddl-new.png" alt="1 gr ddl new" width="201" height="222" class="alignnone size-full wp-image-79683" /></a></td>
<td><a href="https://www.percona.com/blog/wp-content/uploads/2022/01/gr-ddl-2-new.png"><img src="http://www.tusacentral.net/joomla/images/stories/gr_ddl_8_0_27/gr-ddl-2-new.png" alt="gr ddl 2 new" width="231" height="257" class="alignnone size-full wp-image-79686" /></a></td>
<td><a href="https://www.percona.com/blog/wp-content/uploads/2022/01/gr-ddl-3-new.png"><img src="http://www.tusacentral.net/joomla/images/stories/gr_ddl_8_0_27/gr-ddl-3-new.png" alt="gr ddl 3 new" width="201" height="212" class="alignnone size-full wp-image-79687" /></a></td>
</tr>
</tbody>
</table>
 
As you can see from the images we have 3 different phases. Phase 1 is the same between version 8.0.23 and version 8.0.27. 
Phase 2 and 3 instead are quite different. In MySQL 8.0.23 after the DDL is applied on the Primary it is propagated to the other nodes, but a metalock was also acquired and the control was NOT returned. The result was that not only the session executing the DDL was kept on hold, but also all the other sessions performing modifications. 
Only when the operation was over on all secondaries, the DDL was pushed to Binlog and disseminated for Asynchronous replication, lock raised and operation can restart.
Instead, in MySQL 8.0.27,  once the operation is over on the primary the DDL is pushed to binlog, disseminated to the secondaries and control returned. The result is that the write operations on primary have no interruption whatsoever and the DDL is distributed to secondary and Asynchronous replication at the same time. 
This is a fantastic improvement, available only with consistency level EVENTUAL, but still, fantastic.
<h3>Let's see some numbers.</h3>
To test the operation, I have used the same approach used in the previous tests in the article mentioned above.
<pre class="lang:sh decode:true">Connection 1:
ALTER TABLE windmills_test ADD INDEX idx_1 (`uuid`,`active`), ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE windmills_test drop INDEX idx_1, ALGORITHM=INPLACE;
Connection 2:
while [ 1 = 1 ];do da=$(date +'%s.%3N');/opt/mysql_templates/mysql-8P/bin/mysql --defaults-file=./my.cnf -uroot -D windmills_large -e "insert into windmills_test select null,uuid,millid,kwatts_s,date,location,active,time,strrecordtype from windmill7 limit 1;" -e "select count(*) from windmills_large.windmills_test;" > /dev/null;db=$(date +'%s.%3N'); echo "$(echo "($db - $da)"|bc)";sleep 1;done
Connection 3:
while [ 1 = 1 ];do da=$(date +'%s.%3N');/opt/mysql_templates/mysql-8P/bin/mysql --defaults-file=./my.cnf -uroot -D windmills_large -e "insert into windmill8 select null,uuid,millid,kwatts_s,date,location,active,time,strrecordtype from windmill7 limit 1;" -e "select count(*) from windmills_large.windmills_test;" > /dev/null;db=$(date +'%s.%3N'); echo "$(echo "($db - $da)"|bc)";sleep 1;done
Connections 4-5:
while [ 1 = 1 ];do echo "$(date +'%T.%3N')";/opt/mysql_templates/mysql-8P/bin/mysql --defaults-file=./my.cnf -uroot -D windmills_large -e "show full processlist;"|egrep -i -e "(windmills_test|windmills_large)"|grep -i -v localhost;sleep 1;done
</pre>
Modifying a table with ~5 million rows:
<pre class="lang:mysql decode:true">node1-DC1 (root@localhost) [windmills_large]>select count(*) from windmills_test;
+----------+
| count(*) |
+----------+
| 5002909 |
+----------+
</pre>
The numbers below represent the time second.milliseconds taken by the operation to complete. While I was also catching the state of the ALTER on the other node I am not reporting it here given it is not relevant. 
<pre class="lang:sh decode:true">EVENTUAL (on the primary only)
-------------------
Node 1 same table:
.184
.186 <--- no locking during alter on the same node
.184
<snip>
.184
.217 <--- moment of commit
.186
.186
.186
.185
Node 1 another table :
.189
.198 <--- no locking during alter on the same node
.188
<snip>
.191
.211 <--- moment of commit
.194
</pre>
As you can see there is just a very small delay at the moment of commit but other impact.
Now if we compare this with the recent tests I have done for PXC Non Blocking operation (see <a href="https://www.percona.com/blog/percona-xtradb-cluster-non-blocking-operation-for-online-schema-upgrade/">here</a>) with same number of rows and same kind of table/data:
<table border="1">
<thead>
<tr><th>Action</th><th align="right">Group Replication</th><th align="right">PXC (NBO)</th></tr>
</thead>
<tbody>
<tr>
<td>Time on hold for insert in altering table</td>
<td align="right">~ 0.217 sec</td>
<td align="right">~ 120 sec</td>
</tr>
<tr>
<td>Time on hold for insert in another table</td>
<td align="right">~ 0.211 sec</td>
<td align="right">~ 25 sec</td>
</tr>
</tbody>
</table>
 
However, yes there is a however, PXC was maintaining consistency between the different nodes during the DDL execution, while MySQL 8.0.27 with Group Replication was postponing consistency on the secondaries, thus Primary and Secondary were not in sync until full DDL finalization on the secondaries.
<h1>Conclusions</h1>
MySQL 8.0.27 comes with this nice fix that significantly reduces the impact of an online DDL operation on a busy server. But we can still observe a significant misalignment of the data between the nodes when a DDL is executing. 
On the other hand PXC with NBO is a bit more “expensive” in time, but nodes remain aligned all the time.
At the end is what is more important for you to choose one or the other solution, consistency vs. operational impact.
Great MySQL to all.]]></description>
<category>MySQL</category>
<pubDate>Tue, 11 Jan 2022 13:04:00 +0000</pubDate>
</item>
<item>
<title>A look into Percona XtraDB Cluster Non Blocking Operation for Online Schema Upgrade</title>
<link>http://www.tusacentral.net/joomla/index.php/mysql-blogs/245-a-look-into-percona-xtradb-cluster-non-blocking-operation-for-online-schema-upgrade</link>
<guid isPermaLink="true">http://www.tusacentral.net/joomla/index.php/mysql-blogs/245-a-look-into-percona-xtradb-cluster-non-blocking-operation-for-online-schema-upgrade</guid>
<description><![CDATA[Percona XtraDB Cluster 8.0.25 has introduced a new option to perform online schema modifications: NBO (<a href="https://www.percona.com/doc/percona-xtradb-cluster/LATEST/features/nbo.html#nbo">Non Blocking Operation</a>).
When using PXC the cluster relies on wsrep_OSU_method parameter to define the Online Schema Upgrade (OSU) method the node uses to replicate DDL statements.  <img src="http://www.tusacentral.net/joomla/images/stories/NBO/breaking_bariers.jpg" alt="breaking bariers" width="400" height="239" style="float: right;" />
Until now we normally have 3 options:
<ul>
<li style="font-weight: 400;" aria-level="1">Use Total Isolation Order (TOI, the default)</li>
<li style="font-weight: 400;" aria-level="1">Use Rolling Schema Upgrade (RSU)</li>
<li style="font-weight: 400;" aria-level="1">Use Percona’s online schema change tool (TOI + <a href="https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html">PTOSC</a>)</li>
</ul>
Each method has some positive and negative aspects. TOI will lock the whole cluster from being able to accept data modifications for the entire time it takes to perform the DDL operation. RSU will misalign the schema definition between the nodes, and in any case the node performing the DDL operation is still locked. Finally TOI+PTOSC will rely on creating triggers and copying data, so in some cases this can be very impactful. 
The new Non Blocking Operation (NBO) method is to help to reduce the impact on the cluster and make it easier to perform some DDL operations.
At the moment we only support a limited set of operations with NBO like:
<ul>
<li style="font-weight: 400;" aria-level="1">ALTER INDEX</li>
<li style="font-weight: 400;" aria-level="1">CREATE INDEX</li>
<li style="font-weight: 400;" aria-level="1">DROP INDEX</li>
</ul>
Any other command will result in an error message ER_NOT_SUPPORTED_YET.
But let us see how it works and what is the impact while we will also compare it with the default method TOI.
What we will do is working with 4 connections:
1 to perform ddl 2 to perform insert data in the table being altered 3 to perform insert data on a different table  4-5 checking the other two nodes operations
PXC must be at least Ver 8.0.25-15.1.
The table we will modify is :
<pre class="lang:mysql decode:true">DC1-1(root@localhost) [windmills_s]>show create table windmills_test\G
*************************** 1. row ***************************
Table: windmills_test
Create Table: CREATE TABLE `windmills_test` (
`id` bigint NOT NULL AUTO_INCREMENT,
`uuid` char(36) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`millid` smallint NOT NULL,
`kwatts_s` int NOT NULL,
`date` date NOT NULL,
`location` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`active` tinyint NOT NULL DEFAULT '1',
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`strrecordtype` char(3) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`id`),
KEY `IDX_millid` (`millid`,`active`),
KEY `IDX_active` (`id`,`active`),
KEY `kuuid_x` (`uuid`),
KEY `millid_x` (`millid`),
KEY `active_x` (`active`)
) ENGINE=InnoDB AUTO_INCREMENT=8199260 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)
</pre>
And contains ~5 million rows.
<pre class="lang:sh decode:true">DC1-1(root@localhost) [windmills_s]>select count(*) from windmills_test;
+----------+
| count(*) |
+----------+
| 5002909 |
+----------+
1 row in set (0.44 sec)
</pre>
The commands. Connection 1:
<pre class="lang:sh decode:true"> ALTER TABLE windmills_test ADD INDEX idx_1 (`uuid`,`active`), ALGORITHM=INPLACE;
ALTER TABLE windmills_test drop INDEX idx_1, ALGORITHM=INPLACE;
</pre>
 
Connection 2:
<pre class="lang:sh decode:true">while [ 1 = 1 ];do da=$(date +'%s.%3N');/opt/mysql_templates/PXC8P/bin/mysql --defaults-file=./my.cnf -uroot -D windmills_s -e "insert into windmills_test select null,uuid,millid,kwatts_s,date,location,active,time,strrecordtype from windmills7 limit 1;" -e "select count(*) from windmills_s.windmills_test;" > /dev/null;db=$(date +'%s.%3N'); echo "$(echo "($db - $da)"|bc)";sleep 1;done</pre>
 
Connection 3:
<pre class="lang:sh decode:true"> while [ 1 = 1 ];do da=$(date +'%s.%3N');/opt/mysql_templates/PXC8P/bin/mysql --defaults-file=./my.cnf -uroot -D windmills_s -e "insert into windmills8 select null,uuid,millid,kwatts_s,date,location,active,time,strrecordtype from windmills7 limit 1;" -e "select count(*) from windmills_s.windmills_test;" > /dev/null;db=$(date +'%s.%3N'); echo "$(echo "($db - $da)"|bc)";sleep 1;done</pre>
 
Connections 4-5:
<pre class="lang:sh decode:true">while [ 1 = 1 ];do echo "$(date +'%T.%3N')";/opt/mysql_templates/PXC8P/bin/mysql --defaults-file=./my.cnf -uroot -D windmills_s -e "show full processlist;"|egrep -i -e "(windmills_test|windmills_s)"|grep -i -v localhost;sleep 1;done</pre>
Operations:
<ul>
<li>start inserts from connections</li>
<li>start commands in connections 4 - 5 on the other nodes</li>
<li>execute: 
<ul>
<li style="font-weight: 400;" aria-level="1">for TOI 
<ul>
<li aria-level="2">
<pre class="">DC1-1(root@localhost) [windmills_s]>SET SESSION wsrep_OSU_method=TOI;</pre>
</li>
</ul>
</li>
<li style="font-weight: 400;" aria-level="1">for NBO
<ul>
<li aria-level="2">
<pre class="">DC1-1(root@localhost) [windmills_s]>SET SESSION wsrep_OSU_method=NBO;</pre>
</li>
</ul>
</li>
<li style="font-weight: 400;" aria-level="1">For both
<ul>
<li>
<pre class="">DC1-1(root@localhost) [windmills_s]>ALTER TABLE windmills_test ADD INDEX idx_1 (`uuid`,`active`), ALGORITHM=INPLACE, LOCK=shared;</pre>
</li>
</ul>
</li>
</ul>
</li>
</ul>
<h1> </h1>
<h1 style="padding-left: 40px;">Let us run it</h1>
<h2>Altering a table with TOI.</h2>
<pre class="lang:sh decode:true">DC1-1(root@localhost) [windmills_s]>ALTER TABLE windmills_test ADD INDEX idx_1 (`uuid`,`active`), ALGORITHM=INPLACE;
Query OK, 0 rows affected (1 min 4.74 sec)
Records: 0 Duplicates: 0 Warnings: 0
</pre>
 
Inserts in the altering table (connection 2):
<pre class="lang:sh decode:true">.450
.492
64.993 <--- Alter blocks all inserts on the table we are altering
.788
.609
</pre>
 
Inserts on the other table (connection 3):
<pre class="lang:sh decode:true">.455
.461
64.161 <--- Alter blocks all inserts on all the other tables as well
.641
.483
</pre>
 
On the other nodes at the same time of the ALTER we can see:
<pre class="lang:sh decode:true">Id User db Command Time State Info Time_ms Rows_sent Rows_examined
15 system user windmills_s Query 102 altering table ALTER TABLE windmills_test ADD INDEX idx_1 (`uuid`,`active`), ALGORITHM=INPLACE 102238 0 0 <--- time from start
</pre>
So in short we have the whole cluster locked for ~64 seconds. During this period of time, all the operations to modify data or structure were on hold. 
<h2> </h2>
<h2>Let us now try with NBO</h2>
Inserts in the altering table:
<pre class="lang:sh decode:true">.437
.487
120.758 <---- Execution time increase
.617
.510
</pre>
 
Inserts on the other table:
<pre class="lang:sh decode:true">.468
.485
25.061 <---- still a metalock, but not locking the other tables for the whole duration
.494
.471
</pre>
 
On the other nodes at the same time of the ALTER we can see:
<pre class="lang:sh decode:true">Id User db Command Time State Info Time_ms Rows_sent Rows_examined
110068 system user windmills_s Connect 86 altering table ALTER TABLE windmills_test ADD INDEX idx_1 (`uuid`,`active`), ALGORITHM=INPLACE 120420 0 0
</pre>
 
In this case what is also interesting to note is that:
<ol>
<li style="font-weight: 400;" aria-level="1">We have a moment of metalock:<ol>
<li aria-level="2">
<pre class="">110174 pmm 127.0.0.1:42728 NULL Query 2 Waiting for table metadata lock SELECT x FROM information_schema.tables  WHERE TABLE_SCHEMA = 'windmills_s' 1486 10   0</pre>
</li>
<li aria-level="2">
<pre class="">110068 system user connecting host windmills_s Connect 111 closing tables ALTER TABLE windmills_test ADD INDEX idx_1 (`uuid`,`active`), ALGORITHM=INPLACE 111000 0 0</pre>
</li>
</ol></li>
<li>The execution time is longer </li>
</ol>
Summarizing:
<pre class="lang:sh decode:true"> TOI NBO
Time on hold for insert for altering table ~64 sec ~120 sec
Time on hold for insert for another table ~64 sec ~25 sec
metalock whole time only at the end
</pre>
<h2> </h2>
<h2>What is happening, what are the differences and why takes longer with NBO?</h2>
Let see at very high level how the two works:
<ul>
<li style="font-weight: 400;" aria-level="1">TOI: when you issue a DDL like ADD INDEX a metadata lock is taken on the table and it will be released only at the end of the operation. During this time, you cannot: 
<ul>
<li style="font-weight: 400;" aria-level="2">Perform DMLs on any cluster node</li>
<li style="font-weight: 400;" aria-level="2">Alter another table in the cluster</li>
</ul>
</li>
<li style="font-weight: 400;" aria-level="1">NBO: the metadata lock is taken at the start and at the end for a very brief period of time. The ADD INDEX operation will then work on each node independently. The lock taken at the end is to have all the nodes agree on the operation and commit or roll back (using cluster error voting). This final phase costs a bit more in time and is what adds a few seconds to the operation execution. But during the operation:
<ul>
<li style="font-weight: 400;" aria-level="2">You can alter another table (using NBO)</li>
<li style="font-weight: 400;" aria-level="2">You can continue to insert data, except in the table(s) you are altering.</li>
<li style="font-weight: 400;" aria-level="2">On node crash the operation will continue on the other nodes, and if successful it will persist.  </li>
</ul>
</li>
</ul>
In short the cluster server behavior changes significantly when using NBO, offering significant flexibility compared to TOI. The cost in time should not linearly increase with the dimension of the table, but more in relation to the single node efficiency in performing the ALTER operation.       
<h1>Conclusion</h1>
NBO can be significantly helpful to reduce the impact of DDL on the cluster, for now limited to the widely used creation/modification/drop of an index. But in the future … we may expand it. 
The feature is still a technology preview, so do not trust in production, but test it and let us know what you think. 
Final comment. Another distribution has introduced NBO, but only if you buy the enterprise version.
Percona, which is truly open source with facts not just words, has implemented NBO in standard PXC, and the code is fully open source. This is not the first one, but just another of the many features Percona is offering for free, while others ask you to buy the enterprise version.
Enjoy the product and let us have your feedback!
Great MySQL to all! 
 ]]></description>
<category>MySQL</category>
<pubDate>Fri, 10 Dec 2021 10:00:29 +0000</pubDate>
</item>
</channel>
</rss>

If you would like to create a banner that links to this page (i.e. this validation result), do the following:

Download the "valid RSS" banner.
Upload the image to your own server. (This step is important. Please do not link directly to the image on this server.)
Add this HTML to your page (change the image src attribute if necessary):

<a href="http://www.feedvalidator.org/check.cgi?url=http%3A//www.tusacentral.net/joomla/index.php/mysql-blogs%3Fformat%3Dfeed%26type%3Drss"><img src="valid-rss-rogers.png" alt="[Valid RSS]" title="Validate my RSS feed" /></a>

If you would like to create a text link instead, here is the URL you can use:

http://www.feedvalidator.org/check.cgi?url=http%3A//www.tusacentral.net/joomla/index.php/mysql-blogs%3Fformat%3Dfeed%26type%3Drss

Home · About · News · Docs · Terms