Showing entries 41 to 50 of 1001
« 10 Newer Entries | 10 Older Entries »
Displaying posts with tag: Insight for DBAs (reset)
How To Make Schema Changes and Not Die Trying

Schema changes are required to add new features or to fix bugs in an application. However, there is no standard procedure to make the changes in a quick and safe manner. If the changes are not made considering the necessary precautions, you may face unwanted outages on the database that can cause serious problems to your business. In this blog post, I will delve into the most important things to consider while preparing a schema change.

Table size and concurrency

When assessing a schema change, one of the most important things to consider is the table size and concurrency. For small tables, the ALTER operation usually takes a few milliseconds up to a few seconds. Here is where concurrency plays another important role: if the table has periods of low concurrency during the day and the application allows having it locked for a few seconds or minutes, …

[Read more]
Auto-Increment Counter Persistence in MySQL 8: Comparing the Evolution From MySQL 5.7

The auto-increment feature, which generates unique values for primary key columns, is an integral part of the database’s design. With the release of MySQL 8, a notable enhancement was introduced to the auto-increment counter. Compared to MySQL 5.7, this enhancement ensures that the maximum auto-increment counter value persists between server restarts, providing enhanced consistency and reliability in data management. In this article, we will look into this enhancement, compare MySQL 5.7 with MySQL 8, and provide practical examples to demonstrate the differences.

Auto-increment in MySQL 5.7

In MySQL 5.7, the auto-increment counter works as follows: when a new row is inserted into a table with an auto-increment column, the counter increments by 1, and the generated value is used for the primary key of the inserted row. This counter value is stored in memory and is not persistent across server restarts. As a result, if the server …

[Read more]
MySQL Performance Tuning 101: Key Tips to Improve MySQL Database Performance

This post was originally published in June 2020 and was updated in September 2023.

While there is no magic bullet for MySQL performance tuning, there are a few areas that can be focused on upfront that can dramatically improve the performance of your MySQL installation. While much information has been published on this topic over the years, I wanted to break down some of the more critical settings that anyone can implement with no guesswork required.

Depending on the version of MySQL you are running, some of the default values used in this post may differ from your install, but the premise is still largely the same.

What are the Benefits of MySQL Performance Tuning?

MySQL performance tuning offers several significant advantages for effective database management and optimization. Let’s explore these benefits in …

[Read more]
Understanding How MySQL Collation and Charset Settings Impact Performance

This blog was originally published in February 2019 and was updated in September 2023.

Web applications rely on databases to run the internet, powering everything from e-commerce platforms to social media networks to streaming services. MySQL is one of the most popular database management systems, playing a pivotal role in the functionality and performance of web applications.

In today’s blog, I’ll take a look at MySQL collation and charset settings to shed light on how they impact the performance of web applications and how to use them to effectively communicate with your users.

Understanding Character Sets and Encoding in MySQL

Character sets and encoding in MySQL play a vital role in how data is stored and retrieved in a database. A character set is a collection of characters with unique representations for each character, such as letters, numbers, and symbols, that define how data is …

[Read more]
Understanding MySQL Triggers: Exploring How Triggers Impact MySQL Memory Allocation

This blog was originally published in November 2021 and was updated in September 2023.

MySQL server performance can sometimes be perplexing, and if you’ve ever wondered about the role of triggers in influencing your MySQL server’s memory allocation, this post is for you. MySQL triggers are a powerful tool for database administrators and developers, enabling them to automate tasks, enforce data consistency, and respond to events within the database seamlessly.

Here, we first cover the basics of MySQL Triggers, and then we take a deeper dive, exploring their impact on memory usage and providing strategies to optimize MySQL server performance.

What is a Trigger in MySQL?

A Trigger in MySQL is a database object that plays a pivotal role in database management. It is essentially a set of instructions or a program that is executed automatically in response to specific events or actions occurring within the …

[Read more]
Failover and Recovery Scenarios in InnoDB Cluster and ClusterSet

This blog post will focus on failover and recovery scenarios inside the InnoDB Cluster and ClusterSet environment. To know more about the deployments of these topologies, you can refer to the manuals – InnoDB Cluster and Innodb ClusterSet setup.

In the below snippet, we have two clusters (cluster1 and cluster2), which are connected via an async channel and combined, known as a ClusterSet topology. We are going to use the below topology in all of our cases.

MySQL  127.0.0.1:3308 ssl  JS > myclusterset.status({extended: 1})
{
    "clusters": {
        "Cluster1": {
            "clusterRole": "PRIMARY", …
[Read more]
The Deprecation of qpress/QuickLZ Compression Algorithm

To reduce the backup size, save storage space, and speed up the backup and restore process, you can compress a backup with Percona XtraBackup. The XtraBackup --compress option makes XtraBackup compress all output data, including the transaction log file and metadata files, with one of the supported compression

[Read more]
How To Use systemd in Linux to Configure and Manage Multiple MySQL Instances

This blog describes how to configure systemd for multiple instances of MySQL. With package installations of MySQL using YUM or APT, it’s easy to manage MySQL with systemctl, but how will you manage it when you install from the generic binaries?

Here, we will configure multiple MySQL instances from the generic binaries and manage them using systemd.

Why do you need multiple instances on the same server?

We will do that, but why would you need multiple instances on the same host in the first place? Why not just create another database on the same instance? In some cases, you will need multiple instances on the host. 

  1. You can have a host with two or three instances configured as a delayed replica of the source server with SQL Delay of, let’s say, 24hr, 12hr, and 6/3hrs.
  2. Backup testing. You can run multiple instances on a server to test your backups with the correct version and configs. …
[Read more]
InnoDB ClusterSet Deployment With MySQLRouter

This blog post will cover the basic setup of the InnoDB ClusterSet environment, which provides disaster tolerance for InnoDB Cluster deployments by associating a primary InnoDB Cluster with one or more replicas in alternate locations/different data centers. InnoDB ClusterSet automatically manages replication from the primary cluster to the replica clusters via a specific ClusterSet Async replication channel. If the primary cluster becomes inaccessible due to a loss of network connectivity or a data center issue, you can make a replica cluster active in its place.

Now, let’s see in detail how exactly we can configure the topology.

We have used the sandbox environment available via MySQLShell utility for this setup.

Environment

Cluster1:
         127.0.0.1:3308
         127.0.0.1:3309
         127.0.0.1:3310

Cluster2:
         127.0.0.1:3311
         127.0.0.1:3312
         127.0.0.1:3313

Router: …
[Read more]
An Introduction to MySQL Replication: Exploring Different Types of MySQL Replication Solutions

This blog was originally published in February 2017 and was updated in September 2023.

In this blog post, I provide an in-depth introduction to MySQL Replication, answering what it is, how it works, its benefits and challenges, as well as reviewing some of the MySQL replication concepts that are part of the MySQL environment (and Percona Server for MySQL specifically). I will finish by also clarifying some of the common misconceptions people have about replication and how Percona can help.

Since I’ve been working on the Solution Engineering team, I’ve noticed that – although information is plentiful – replication is often misunderstood or incompletely understood.

What is MySQL Replication?

MySQL replication is …

[Read more]
Showing entries 41 to 50 of 1001
« 10 Newer Entries | 10 Older Entries »