Two Extremely Useful Tools (pt-upgrade and checkForServerUpgrade) for MySQL Upgrade Testing

https://www.percona.com/blog/wp-content/uploads/2022/08/MySQL-Upgrade-Testing.pngMySQL Upgrade Testing

MySQL Upgrade TestingMy last blog, Percona Utilities That Make Major MySQL Version Upgrades Easier, detailed the tools available from the Percona toolkit that assists us with major MySQL version upgrades. The pt-upgrade tool aids in testing application queries and generates reports on how each question performs on servers running various versions of MySQL.

MySQL Shell Upgrade Checker is a utility that helps in compatibility tests between MySQL 5.7 instances and MySQL 8.0 upgrades, which is part of the mysql-shell-utilities. The util.checkForServerUpgrade() function checks whether the MySQL 5.7 instance is ready for the MySQL 8.0 upgrade and generates a report with warnings, errors, and notices for preparing the current MySQL 5.7 setup for upgrading to MySQL 8.0.

We can run this Upgrade Checker Utility in the current MySQL 5.7 environment to generate the report; I would recommend running it on any of the replica instances that have the same configuration as the production.

The user account used to execute the upgrade checker tool must have ALL rights up to MySQL Shell 8.0.20. The user account requires RELOAD, PROCESS, and SELECT capabilities as of MySQL Shell 8.0.21.

How to generate a report using Upgrade Checker Utility

To generate a report using Upgrade Checker Utility we may either login to the shell prompt or execute directly from the command prompt.

mysqlsh -- util checkForServerUpgrade 'root@localhost:3306' --target-version=8.0.29 --config-path=/etc/my.cnf > CheckForServerUpgrade_Report.txt
Please provide the password for 'mysqluser@localhost:3306':

$ mysqlsh
MySQL  JS > util.checkForServerUpgrade('root@localhost:3306', { "targetVersion":"8.0.29", "configPath":"/etc/my.cnf"})
Please provide the password for 'mysqluser@localhost:3306':

To quit the mysqlsh command prompt, type \exit.

MySQL  JS > \exit
Bye!

Do pt-upgrade and Upgrade Checker Utility do the same tests?  No!

Don’t confuse the Upgrade Checker Utility with the pt-upgrade tool since they are used for different kinds of major version upgrade testing. The Upgrade Checker Utility performs a variety of tests on the selected MySQL server to ascertain whether the upgrade will be successful; however, the tool does not confirm whether the upgrade is compatible with the application queries or routines.

Does it check both my.cnf file and the MySQL server variables?

The utility can look for system variables declared in the configuration file (my.cnf) but removed in the target MySQL Server release, as well as system variables not defined in the configuration file but with a different default value in the target MySQL Server release.  You must give the file path to the configuration file when executing checkForServerUpgrade() for these checks. However, the tool is unable to identify the variables that have been deleted in the my.cnf file but are set in the MySQL server.

Let us remove query_cache_type from /etc/percona-server.conf.d/mysqld.cnf and run the command.

]# mysql -uroot -p -e "SHOW VARIABLES WHERE Variable_Name IN ('query_cache_type','query_cache_size')"
Enter password:
+------------------+---------+
| Variable_name    | Value   |
+------------------+---------+
| query_cache_size | 1048576 |
| query_cache_type | ON      |
+------------------+---------+

]# cat /etc/my.cnf
#
# The Percona Server 5.7 configuration file.
#
#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#   Please make any edits and changes to the appropriate sectional files
#   included below.
#
!includedir /etc/my.cnf.d/
!includedir /etc/percona-server.conf.d/
]#

Remove query_cache_type variable from mysqld.cnf:

]# sed -i '/query_cache_type/d' /etc/percona-server.conf.d/mysqld.cnf
]#

]# grep -i query /etc/my.cnf /etc/percona-server.conf.d/mysqld.cnf
/etc/percona-server.conf.d/mysqld.cnf:query_cache_size=5058320
]#

As the query cache type variable has been deleted from my.cnf,  the tool is unable to detect it.

#  mysqlsh -- util checkForServerUpgrade 'root@localhost:3306' --target-version=8.0.29 --config-path=/etc/my.cnf | grep  -B 6  -i "query_cache"
15) Removed system variables
  Error: Following system variables that were detected as being used will be
    removed. Please update your system to not rely on them before the upgrade.
  More information:
    https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html#optvars-removed

  query_cache_size - is set and will be removed
ERROR: 1 errors were found. Please correct these issues before upgrading to avoid compatibility issues.

In JSON format, the report looks like this:

Note: To make the blog more readable, I shortened the report.

# mysqlsh -- util checkForServerUpgrade 'root@localhost:3306' --target-version=8.0.29 --config-path=/etc/my.cnf --output-format=JSON
{
    "serverAddress": "localhost:3306",
    "serverVersion": "5.7.39-42 - Percona Server (GPL), Release 42, Revision b0a7dc2da2e",
    "targetVersion": "8.0.29",
    "errorCount": 1,
    "warningCount": 27,
    "noticeCount": 1,
    "summary": "1 errors were found. Please correct these issues before upgrading to avoid compatibility issues.",
    "checksPerformed": [
        {
            "id": "oldTemporalCheck",
            "title": "Usage of old temporal type",
            "status": "OK",
            "detectedProblems": []
        },
        {
            "id": "reservedKeywordsCheck",
            "title": "Usage of db objects with names conflicting with new reserved keywords",
            "status": "OK",
            "detectedProblems": []
        },
…
        {
            "id": "sqlModeFlagCheck",
            "title": "Usage of obsolete sql_mode flags",
            "status": "OK",
            "description": "Notice: The following DB objects have obsolete options persisted for sql_mode, which will be cleared during upgrade to 8.0.",
            "documentationLink": "https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-removals",
            "detectedProblems": [
                {
                    "level": "Notice",
                    "dbObject": "global system variable sql_mode",
                    "description": "defined using obsolete NO_AUTO_CREATE_USER option"
                }
            ]
        },
        {
            "id": "enumSetElementLenghtCheck",
            "title": "ENUM/SET column definitions containing elements longer than 255 characters",
            "status": "OK",
            "detectedProblems": []
        },
…
        {
            "id": "removedSysVars",
            "title": "Removed system variables",
            "status": "OK",
            "description": "Error: Following system variables that were detected as being used will be removed. Please update your system to not rely on them before the upgrade.",
            "documentationLink": "https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html#optvars-removed",
            "detectedProblems": [
                {
                    "level": "Error",
                    "dbObject": "query_cache_size",
                    "description": "is set and will be removed"
                }
            ]
        },
        {
            "id": "sysVarsNewDefaults",
            "title": "System variables with new default values",
            "status": "OK",
            "description": "Warning: Following system variables that are not defined in your configuration file will have new default values. Please review if you rely on their current values and if so define them before performing upgrade.",
            "documentationLink": "https://mysqlserverteam.com/new-defaults-in-mysql-8-0/",
            "detectedProblems": [
                {
                    "level": "Warning",
                    "dbObject": "back_log",
                    "description": "default value will change"
                },
                {
                    "level": "Warning",
                    "dbObject": "innodb_max_dirty_pages_pct",
                    "description": "default value will change from 75 (%)  90 (%)"
                }
            ]
        },
        {
            "id": "zeroDatesCheck",
            "title": "Zero Date, Datetime, and Timestamp values",
            "status": "OK",
            "detectedProblems": []
        },
…
    ],
    "manualChecks": [
        {
            "id": "defaultAuthenticationPlugin",
            "title": "New default authentication plugin considerations",
            "description": "Warning: The new default authentication plugin 'caching_sha2_password' offers more secure password hashing than previously used 'mysql_native_password' (and consequent improved client connection authentication). However, it also has compatibility implications that may affect existing MySQL installations.  If your MySQL installation must serve pre-8.0 clients and you encounter compatibility issues after upgrading, the simplest way to address those issues is to reconfigure the server to revert to the previous default authentication plugin (mysql_native_password). For example, use these lines in the server option file:\n\n[mysqld]\ndefault_authentication_plugin=mysql_native_password\n\nHowever, the setting should be viewed as temporary, not as a long term or permanent solution, because it causes new accounts created with the setting in effect to forego the improved authentication security.\nIf you are using replication please take time to understand how the authentication plugin changes may impact you.",
            "documentationLink": "https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-compatibility-issues\nhttps://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-replication"
        }
    ]
}

Please read Daniel Guzmán Burgos’ blog post to find out more about the Upgrade Checker Utility and click the link to learn more about the pt-upgrade testing.

Prior to a major version upgrade, application query testing and configuration checks are an inevitable task, and the pt-upgrade and “Upgrade Checker Utility” are quite helpful.

Planet MySQL

Athena (Trailer)

https://theawesomer.com/photos/2022/08/athena_trailer_t.jpg

Athena (Trailer)

Link

After a soldier loses his youngest brother in an apparent police altercation, he returns home to help bring justice to the fallen. But things quickly escalate as the community wants revenge. Director Romain Gavras’ immersive Athena drops us smack-dab into the middle of a tense and chaotic scene. Coming to Netflix 9.23.2022.

The Awesomer

Mining the MySQL Performance Schema for Transactions

The MySQL Performance Schema is a gold mine of valuable data.
Among the many nuggets you can extract from it is an historical report of transactions: how long a transaction took to execute, what queries were executed in it (with query metrics), and idle time between queries.
Mining this information is not trivial, but it’s fun and this blog post shows how to start.

Planet MySQL

Seven Ways To Reduce MySQL Costs in the Cloud

https://www.percona.com/blog/wp-content/uploads/2022/08/image1.pngReduce MySQL Costs in the Cloud

Reduce MySQL Costs in the CloudWith the economy slowing down and inflation raging in many parts of the world, your organization will love you if you find ways to reduce the costs of running their MySQL databases. This is especially true if you run MySQL in the cloud, as it often allows you to see the immediate effect of those savings, which is what this article will focus on.

With so many companies announcing layoffs or hiring freezes, optimizing your costs may free enough budget to keep a few team members on or hire folks your team needs so much. 

1. Optimize your schema and queries

While optimizing schema and queries is only going to do so much to help you to save on MySQL costs in the cloud, it is a great thing to start with. Suboptimal schema and queries can require a much larger footprint, and it is also something “fully managed” Database as a Service (DBaaS) solutions from major cloud vendors do not really help you with. It is not uncommon for a system with suboptimal schema and queries to require 10x or more resources to run than an optimized system. 

At Percona, we’ve built Percona Monitoring and Management (PMM), which helps you to find out which of the queries need attention and how to optimize it. If you need more help, Percona Professional Services are often able to get your schema and queries in shape in a matter of days, providing long-term saving opportunities with a very reasonable upfront cost.

2. Tune your MySQL configuration

Optimal MySQL configuration depends on the workload, which is why I recommend tuning your queries and schema first. While gains from MySQL configuration tuning are often smaller than from fixing your queries, it is still significant. We have an old but still very relevant article on the basic MySQL settings you’ll want to tune, which you can check out. You can also consider using tools like Releem or Ottertune to help you to get a better MySQL configuration for your workload.  

More advanced tuning might include exploring alternative storage engines, such as MyRocks (included in Percona Distribution for MySQL). MyRocks can offer fantastic compression and minimize required IO, thereby drastically reducing storage costs. For a more in-depth look at MyRocks performance in the cloud, check out the blog Scaling IO-Bound Workloads for MySQL in the Cloud. 

3. Implement caching

Caching is cheating — and it works great! The most state-of-the-art caching for MySQL is available by rolling out ProxySQL. It can provide some additional performance benefits, such as connection pooling and read-write splitting, but I think caching is most generally useful for MySQL cost reduction. ProxySQL is fully supported by Percona with a Percona Platform subscription and is included in Percona Distribution for MySQL.  

Enabling query cache for your heavy queries can be truly magical — often enough it is the heaviest queries that do not need to provide the most up-to-date information and can have their results cached for a significant time.

You can read more about how to configure ProxySQL caching on the Percona Blog, as well as these articles:

4. Rightsize your resources

Once you have optimized your schema and queries and tuned MySQL configuration, you can check what resources your MySQL instances are using and where you can put them on a diet without negatively impacting performance. CPU, memory, disk, and network are four primary resources that impact MySQL performance and often can be managed semi-independently in the cloud.  For example, if your workload needs a lot of CPU power but does not need a lot of memory for caching, you can consider CPU-intensive instances. PMM has some great tools to understand which resources are most demanded by your workload.  

MySQL Costs in the Cloud

You can also read some additional resource-specific tips in my other article on MySQL performance

In our experience, due to the simplicity of “scaling with credit cards”, many databases in the cloud become grossly over-provisioned over time, and there can be a lot of opportunity for savings with instance size reduction!

5. Ditch DBaaS for Kubernetes

The price differential between DBaaS and comparable resources continues to grow.  With the latest Graviton instances, you will pay double for Amazon RDS compared to the cost of the underlying instance. Amazon Aurora, while offering some wonderful features, is even more expensive. If you are deploying just a couple of small nodes, this additional cost beats having to hire people to deploy and manage “do it yourself” solutions. If you’re spending tens of thousands of dollars a month on your DBaaS solution, the situation may be different.   

A few years ago, building your own database service using building blocks like EC2, EBS, or using a DBaaS solution such as Amazon RDS for MySQL were the only solutions. Now, another opportunity has emerged — using Kubernetes.  

You can get Amazon EKS – Managed Kubernetes Service, Google Kubernetes Engine (GKE), or Azure Kubernetes Service (AKS) for a relatively small premium as infrastructure costs. Then, you can use Percona’s MySQL Operator to deploy and manage your database at a fraction of the complexity of traditional deployments. If you’re using Kubernetes for your apps already or use an infrastructure as code (IaC) deployment approach, it may even be handier than DBaaS solutions and 100% open source. 

Need help? Percona has your back with Percona Platform.

6. Consider lower-cost alternatives

A few years ago, only major cloud providers (in the U.S.: AWS, GCP,  Azure) had DBaaS solutions for MySQL.  Now the situation has changed, with MySQL DBaaS being available from second-tier and typically lower-cost providers, too. You can get MySQL DBaaS from Linode, Digital Ocean, and Vultr at a significantly lower cost (though, with fewer features). You can also get MySQL from independent providers like Aiven.    

If you’re considering deploying databases on a cloud vendor different than the one your application uses, make sure you’re using a close location for deployment and make sure to check the network latency between your database and your application — poor network latency or reliability issues can negate all the savings you’ve achieved. 

7. Let experts manage your MySQL database

If you’re spending more than $20,000 a month on your cloud database footprint, or if your application is growing or changing rapidly, you’ll often have better database performance, security, and reliability at lower cost by going truly “fully managed” instead of “cloud fully managed.”  The latter relies on “shared responsibility” in many key areas of MySQL best practices.  

“Cloud fully managed” will ensure the database infrastructure is operating properly (if scaled with a credit card), but will not fully ensure you have optimal MySQL configuration and optimized queries, are following best security practices, or picked the most performant and cost-effective instance for your MySQL deployment. 

Percona Managed Services is a solution from Percona to consider, though there are many other experts on the market that can take better care of your MySQL needs than DBaaS at major clouds.  

Summary

If the costs of your MySQL infrastructure in the cloud are starting to bite, do not despair. There are likely plenty of savings opportunities available, and I hope some of the above tips will apply to your environment.  

It’s also worth noting that my above advice is not theory, but is based on the actual work we’ve done at Percona. We’ve helped many leading companies realize significant cost savings when running MySQL in the cloud, including Patreon, which saved more than 50% on their cloud database infrastructure costs with Percona. 

 

Learn how Patreon saved more than 50% on database infrastructure costs

Percona Database Performance Blog

Caveman Spark AR-15 Crush Washers That Change Color When Hot

https://www.thefirearmblog.com/blog/wp-content/uploads/2022/08/Spark-Crush-Washers-Orange-180×180.png

Caveman LLC Spark AR-15 Crush WashersEarlier this year, Caveman LLC introduced a series of crush washers for AR-15s that change to bright colors when the barrel starts to heat up past 131 degrees F (55 degrees C). Caveman states that the intended idea behind their Spark AR-15 Crush Washers is to warn shooters when the barrel is too hot to […]

Read More …

The post Caveman Spark AR-15 Crush Washers That Change Color When Hot appeared first on The Firearm Blog.

The Firearm Blog

Why You Should Use Administrative Interfaces to Manage Linux Servers

https://static1.makeuseofimages.com/wordpress/wp-content/uploads/2022/08/linux-management-interfaces-systems.jpg

The biggest problem for Linux system and server administrators is troubleshooting the errors encountered. Fixing these issues, managing security problems, and analyzing the primary cause behind such issues from the command screen can sometimes pose serious challenges.

Linux itself is a command-line universe. It is not easy to learn all the commands and their parameters, let alone use them to troubleshoot errors.

That’s why there are Linux management interfaces to keep everything in sight. Most system and server administrators prefer these administrative interfaces for managing their Linux systems instead. Here’s why you should consider using an admin interface to manage a Linux server.

Why Use an Admin Interface for Linux Management?

For Linux system administrators, it is important to learn how these interfaces work in addition to knowing how to use the management interfaces properly. To summarize this, you can think of management interfaces as tools that you will use between your network management station and the object or tool you want to manage, in this case, a Linux machine.

So that you can imagine it better, you can think of it this way. Imagine you have a Linux server. To manage this server and access various objects, you need to use some management protocol. It is possible to monitor the relationship between these management protocols and the object to be managed with management interfaces.

It is quite difficult to do all this tracking from the command screen. You need to spend a lot of time on the command screen and master the Linux networking commands. Moreover, even if you do all these, there’s an increased possibility of making mistakes. As a result, it will be risky and difficult to manage a system manually using commands.

Using a Web Interface for Linux Administration

Web interfaces are accessible and easy to use. If you’re managing a system using a web interface, you can often find databases, customer information, user agreements, uploaded files, IP addresses, and even error logs, all in one place. Since everything will be in front of your eyes, you can perform your management operations with just a few mouse clicks.

What Is Webmin?

It is very practical to manage web-based systems with Webmin. If you have used environments such as cPanel and Plesk before, you will never be unfamiliar when using Webmin. Moreover, Webmin is open source and has a lot of features.

Webmin allows you to manage the accounts of all registered users in the system from a single location. Furthermore, no coding abilities are required. You also don’t require shell commands to configure your network or change network files, as Webmin can assist you with network configurations as well.

Another management issue that Linux users are closely familiar with is disk partitioning. Webmin comes with partitioning and automatic backup features. It also takes care of security protocols so you don’t have to worry about SSL renewal. In addition, there is a command shell feature using which you can issue Linux and Unix commands within Webmin.

Today, cloud technologies continue to grow at a very rapid pace. If you are considering using a cloud computing service or want to build your system on a cloud, Webmin also has a cloud installation feature.

Another very useful feature of Webmin is that it has different modules. Since it is open source, you can write your own modules and can even benefit from ready-made modules on the internet. For example, using the Virtualmin GPL module, you can control your hosting service. Moreover, it is possible to manage virtual hosts and DNS from here.

If you have more than one virtual server, Virtualmin GPL creates a Webmin user for each virtual server. Each server manages only its own virtual server with Webmin. Thus, it is possible to have independent mailboxes, websites, applications, database servers, and software in each of these virtual servers.

Package Configuration in Linux System Management

Another topic that Linux system administrators should be familiar with is package configuration and management. When installing a package on your system, you only follow what is happening on the command screen. The download process takes place, it writes what the installed files are, and you are given information about the installation. However, this adventure is not that simple.

When you want to install a package, it needs to be configured system-wide. To give an example from Debian and Ubuntu systems, the configuration tool that does this is debconf. It configures the package you want to install, according to the settings in the dpkg-reconfigure file.

It would make sense to examine it through an example to better understand why you should consider using debconf within the management interfaces. You can query the packages available in your debconf database using a simple command. The below debconf-show command lets you query the entire database and the –listowners parameter returns only owners:

sudo debconf-show 

Now try to reconfigure an item of your choice using dpkg-reconfigure:

sudo dpkg-reconfigure wireshark-common

As you can see, a configuration interface for wireshark-common will open. Now the configuration operations will be easier using the debconf interface. There is no debconf command on the command line, though. This is because debconf is already integrated into dpkg.

If you are going to write your own Linux packages and use them in system administration, it will be useful to be familiar with debconf. Because it provides an interface to talk to users who will install your package and get some input from them. For this, you need to use the frontend and backend APIs that debconf provides.

Importance of Admin Interfaces in Linux System Management

There are a lot of commands you can use when managing Linux systems and servers. Each of these commands has dozens of different parameters. Of course, it is very valuable for you to become familiar with and learn about them. However, you can’t ignore the convenience and accessibility provided by management interfaces.

Even just to change a basic configuration setting, you need to make some changes to the files. Moreover, these changes can damage your system. In a large-scale project, such configuration issues can cause huge problems in terms of both expenses and security. However, the management interfaces will save you from this whole pile of commands and parameters.

The main purpose here is to reduce the workload and save time. Webmin and debconf are just examples. You may also want to learn technologies such as Cockpit and Nagios. These are powerful Linux system and server administrator tools that are used frequently and will be useful to you.

MUO – Feed

Objects + Duct Tape vs. Hydraulic Press

https://theawesomer.com/photos/2022/08/duct_tape_hydraulic_press_t.jpg

Objects + Duct Tape vs. Hydraulic Press

Link

Duct tape is an incredibly strong and versatile mending tool. But can it help objects stand up to the force of the mighty 150-ton hydraulic press? HPC wrapped some everyday items in thick layers of the sticky silver stuff to see if it improves its ability to survive the press?

The Awesomer