Showing entries 1 to 10 of 988
10 Older Entries »
Displaying posts with tag: Performance (reset)
MySQL Performance : Switching InnoDB REDO Threads=OFF/ON

In MySQL 8.0 we introduced a totally new design for InnoDB REDO Log management. The main difference was about implementing a lock-free solution for user threads, and use dedicated REDO threads for all background IO write work.

for more details, see an excellent and very detailed article by Pawel : https://dev.mysql.com/blog-archive/mysql-8-0-new-lock-free-scalable-wal-design/

However, over a time we also added an option to let users to switch REDO threads=OFF to enforce REDO log processing efficiency in some particular cases. Unfortunately this feature created a lot of confusions for MySQL users, and many ones interpreted this in different ways, providing different and sometimes opposite advices, etc..

My main advice will be always : test each feature yourself and within your …

[Read more]
The Impacts of Fragmentation in MySQL

Fragmentation is a common concern in some database systems. Highly fragmented tables can affect performance and resource allocation. But reducing fragmentation often involves rebuilding the table completely. This blog post will discuss fragmentation and its impact on InnoDB.

What is fragmentation?

We say that something is fragmented when it is formed by parts that are separate or placed in a different order than the natural one. In databases, we can experiment with different types of fragmentation:

  • Segment Fragmentation: segments are fragmented; they are stored not following the order of data, or there are empty pages gaps between the data pages.
  • Tablespace Fragmentation: the tablespace is stored in non-consecutive filesystem blocks.
  • Table Fragmentation: data is stored not following the primary key order (heap tables), …
[Read more]
Improving Query Performance with Multi-Valued Indexing in MySQL 8.0

Learn how Multi-Valued Indexing in MySQL 8.0 can enhance query performance by efficiently indexing and querying JSON arrays. Discover the benefits, implementation steps, and considerations for optimizing your MySQL database.

  1. Multi-Valued Indexing in MySQL 8.0
  2. Understanding Multi-Valued Indexes and their benefits
  3. Creating Multi-Valued Indexes in MySQL …
[Read more]
Enhancing Performance with Parallel Index Rebuild in MySQL 8.0.31

Learn how the parallel index rebuild feature in MySQL 8.0.31 improves performance by optimizing index and column additions. Explore the multithreaded insert phase, sort index build process, and system configurations for enhanced efficiency.

  1. Understanding Sorted Index Build
    1. When does InnoDB use a sort index build approach?
  2. Phases of Sort …
[Read more]
Exploring Aurora serverlessV2 for MySQL Part 3

Explore the powerful features of Aurora Serverless V2 for MySQL in this informative blog series. Learn about read-only scaling, parameter support, and cost performance. Compare costs between Provisioned Aurora and Aurora Serverless V2. Discover key takeaways for optimizing your MySQL deployment on the cloud. Read now!

  1. Read-only Scaling
    1. Failover replicas
[Read more]
How to Troubleshoot a MySQL Replica IO Thread that is Stuck in a Connecting State

Discover how to troubleshoot a MySQL replica IO thread stuck in a connecting state. Learn about the replication architecture, security group rules for AWS EC2 instances, and how to address common issues like network restrictions and bind address configuration.

MySQL is a powerful database management and a widely used cloud database service. One of its key features is the ability to create replicas of a master database to improve its availability and scalability. However, at times the IO thread in a MySQL replica may get stuck in a connecting state, which can cause replication issues and affect the overall data consistency …

[Read more]
MySQL BLOB Fetch Performance in Java

Cover image by DALL-E: “a manometer with pipe with a dolphin on a background, 3d render”

What’s the best way to store binary data in MySQL? This is a question that has multiple answers, depending on what you want to achieve. For example, if you need to optimize for the storage size, you probably need to use some compression algorithm that compresses your data efficiently. In my case, I really need the best performance, meaning the fastest response time to fetch the entire blob from MySQL.

Let’s put aside the question of whether MySQL is a good fit for storing binary data. The question here, is how to store binary data so the reads from DB are as fast as possible?

The …

[Read more]
Generate Invisible Primary Key (GIPK) MySQL 8.0

The Primary key is like the hero of a row, which has more beneficial features in the table while performing any task on the table.

The DBA knows the importance of the primary key in the table and how to handle it.

  1. Notable features of having a primary key:
  2. Requirements:
  3. Enabling GIPK:
  4. Handling GIPK:
[Read more]
Troubleshooting XA transactions in MySQL

This is one of the exciting troubleshooting related to XA transactions let’s dive in. Let me provide a few backgrounds.

For one of our clients, when we try to drop a table, it is waiting for metadata lock. On debugging it is one of the XA transactions is holding the shared write lock and causing metadata lock on all the other local transactions.

Let us view the metadata locks from the Performance Schema.

mysql> select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME, LOCK_TYPE,LOCK_STATUS,SOURCE from performance_schema.metadata_locks\G
******************* 1. row *********************
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: sbtest
OBJECT_NAME: sbtest_table1
LOCK_TYPE: SHARED_WRITE
LOCK_STATUS: GRANTED

SOURCE: xa.cc:284
******************* 2. row *********************
OBJECT_TYPE: TABLE …
[Read more]
MySQL Performance : Benchmark kit (BMK-kit)

The following is a short HOWTO about deployment and use of Benchmark-kit (BMK-kit). The main idea of this kit is to simplify your life in running various MySQL benchmark workloads with less blood and minimal potential errors.

Generally as simple as the following :

$ bash /BMK/sb_exec/sb11-Prepare_50M_8tab-InnoDB.sh 32   # prepare data

$ for users in 1 2 4 8 16 32 64 128 256 512 1024 2048
do   
  # run OLTP_RW for 5min each load level..
  bash /BMK/sb_exec/sb11-OLTP_RW_50M_8tab-uniform-ps-trx.sh $users 300
  sleep 15
done

the latest public online version of the following HOWTO is always available from here : http://dimitrik.free.fr/blog/posts/mysql-perf-bmk-kit.html

Read more... (42 min remaining to read)

Showing entries 1 to 10 of 988
10 Older Entries »