Showing entries 1 to 10 of 1058
10 Older Entries »
Displaying posts with tag: innodb (reset)
MySQL 8.0 – locking details

Recently, I saw many interest in understanding and getting information about database locking.

MySQL InnoDB’s locking can be complex and having an overview not always simple.

For more information about how InnoDB locking is working, I can only recommend this excellent series of articles by Kuba:

[Read more]
Making Aurora Write Latency 15x Higher (or More!) by Choosing a Bad Primary Key

Primary Key design is an important thing for InnoDB performance, and choosing a poor PK definition will have an impact on performance and also write propagation in databases. When this comes to Aurora, this impact is even worse than you may notice.

In short, we consider a poor definition of a Primary Key in InnoDB as “anything but quasi sequential values”, which may cause very random access to data and thus increase the IO dependency.

In this post, I’ll try to demonstrate the potential impact of the primary key design when running on Aurora, and how a bad design can lead to a 15x write latency penalty (or more).

The Analysis

Recently I worked on a case where a customer was having issues with scaling writes in Aurora MySQL. While this is a known limitation in Aurora considering how the distributed storage layer syncs out data among all nodes of the cluster, we observed additional latency occurring when more …

[Read more]
Locking SELECT with CREATE TABLE, INSERT INTO, and User Variables

Tweet

Locking is an important concept in databases. They help regulate access to the data, so your SELECT queries return consistent results and DML and DDL statements leave the data and schema in a consistent state. For the data, there are four different transaction isolation levels that influence which locks are taken. The most two commonly used isolation levels are REPEATABLE READ (the default in InnoDB) and READ COMMITTED (the default in some other databases). Both of those are said to provide non-locking reads, but there is a little more to it than that.

Selecting into a user variable causing a lock wait timeout.

One case where reads are always locking is when you explicitly requests …

[Read more]
What Is InnoDB in MySQL? Tutorial with Examples and Performance Tuning Tips

There is a number of powerful MySQL storage engines at our disposal, and InnoDB is undoubtedly one of the most popular ones. It is highly reliable and efficient, so it is no wonder that it has become a default storage engine for all MySQL versions from 5.5 on. Let us take a look at its […]

The post What Is InnoDB in MySQL? Tutorial with Examples and Performance Tuning Tips appeared first on Devart Blog.

MySQL/ZFS Performance Update

As some of you likely know, I have a favorable view of ZFS and especially of MySQL on ZFS. As I published a few years ago, the argument for ZFS was less about performance than its useful features like data compression and snapshots. At the time, ZFS was significantly slower than xfs and ext4 except when the L2ARC was used.

Since then, however, ZFS on Linux has progressed a lot and I also learned how to better tune it. Also, I found out the sysbench benchmark I used at the time was not a fair choice since the dataset it generates compresses much less than a realistic one. For all these reasons, I believe that it is time to revisit the performance aspect of MySQL on ZFS.

ZFS Evolution

In 2018, I reported ZFS performance results based on version 0.6.5.6, the default version available in Ubuntu Xenial. The present post is using …

[Read more]
MySQL dump & load InnoDB Buffer Pool

For performance, having a warm InnoDB Buffer Pool is very important. What does that mean ?

A warm buffer pool means that the most used pages (working set) required by the production workload are already loaded in memory (in the buffer pool). If so, MySQL doesn’t need to read the pages from disk every time it requires the most used page and speeds up the process when the needed data is already in memory.

When you start MySQL, by default the InnoDB Buffer Pool is cold and the warm up process can even take days sometimes…

So, you can already deduce that restarting mysqld is a source of having a cold Buffer Pool as it will start empty. Another reason to have a non optimal Buffer Pool is to load it unnecessary pages. This can happen during a logical dump or load. If you regularly do a mysqldump for example (don’t forget that MySQL Shell dump & load is better if you do logical dumps, but introduces also the …

[Read more]
Storage Engines in MySQL

This article focuses on the database storage engines for MySQL that ensure appropriate performance and manage SQL operations for multiple table types. The article examines the differences between the most widely used MySQL storage engines. MySQL is the second most popular Relational Database Management Systems (RDBMS) in the world. Countless services and applications have MySQL […]

The post Storage Engines in MySQL appeared first on Devart Blog.

Percona XtraBackup Point-In-Time Recovery for the Single Database

Recovering to a particular time in the past is called Point-In-Time Recovery (PITR). With PITR you can rollback unwanted DELETE without WHERE clause or any other harmful command.

PITR with Percona XtraBackup is pretty straightforward and perfectly described in the user manual. You need to restore the data from the backup, then apply all binary logs created or updated after the backup was taken, but skip harmful event(s).

However, if your data set is large you may want to recover only the affected database or table. This is possible but you need to be smart when filtering events from the binary log. In this post, I will show how to perform such a partial recovery using Percona XtraBackup, …

[Read more]
MySQL ERROR Log Table Explained

Over the decades we have been reading the MySQL error log from the server system file, if there are any issues in MySQL or any unknown restart happened , generally we look at the mysql error log.

By default MySQL error log can be found in the default path /var/log/mysqld.log , or it can be explicitly configured using the variable log_error.

Few drawbacks using MySQL error log as FILE

  • Possibility of missing genuine errors while reading lengthy information.
  • Filtering of errors for the particular date and timeframes.
  • Cannot provide the DB server access to developers because of fear of mishandling DB servers.

To overcome the above issues , from MySQL …

[Read more]
6 Step MySQL Point-In-Time recovery on AWS RDS

Recently one of our customers ran into an issue, wherein a bad actor(code) from the application had made the wrong update to 16 M records of a critical table in the database, causing the entire production process to go down. The application Team was able to find the bad actor and block it, our Remote DBA was involved in the Data Recovery/Rollback.

Here I would like to discuss possible recovery methods for the above said scenario

Delayed Slave:

A simple and effective way to recover is by using a delayed slave, RDS started supporting this feature from version 5.6.40 and 5.7.22 i.e., you can induce a SQL thread delay-interval for applying the writes to a slave, detailed implementation is covered in our blog here. It’s …

[Read more]
Showing entries 1 to 10 of 1058
10 Older Entries »