Showing entries 1 to 10 of 1055
10 Older Entries »
Displaying posts with tag: innodb (reset)
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]
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]
Webinar Summary: Migrate your EOL MySQL Servers

This brief summarises the proceedings and outcomes of the 2nd MyWebinar which was held on 13th February 2021 at Online Webinar. As part of our thought leadership webinar series, our latest hosting webinar Migrate your EOL MySQL Servers (seamless migration to MySQL group replication / InnoDB cluster)

We have conducted MyWebinar with a very positive response with the help of software like zoom hosting arrangement and YouTube streaming and commitment of our business team, We have easily planned the perfect broadcasting for all of the attendees.

Over 30+ people took part in our webinar on 13th Feb 2021, to learn MySQL EOL and upgrade path. The session “Migrate your EOL MySQL servers to HA Complaint GR Cluster / InnoDB Cluster With Zero Downtime” by  …

[Read more]
Various Types of InnoDB Transaction Isolation Levels Explained Using Terminal

The goal of this blog post is to explain the various types of transaction isolation levels available in MySQL. After reading the blog, you will be able to explain dirty reads, non-repeatable reads, and the concept of phantom rows as well.

What is the Isolation Level in MySQL?

Isolation (I) is one of the properties from ACID. It defines how each transaction is isolated from other transactions and is a critical component of application design. As per the SQL:1992 standard, InnoDB has four types of Isolation levels. Below, I have listed the types in order, and each transaction isolation level provides better consistency compared to the previous one.

  • READ-UNCOMMITTED
  • READ-COMMITTED
  • REPEATABLE-READ – ( MySQL’s DEFAULT )
  • SERIALIZABLE

You can change the isolation level using the variable “transaction_isolation” at runtime. As transaction isolation changes can …

[Read more]
The MySQL Clone Wars: Plugin vs. Percona XtraBackup

Large replication topologies are quite common nowadays, and this kind of architecture often requires a quick method to rebuild a replica from another server.

The Clone Plugin, available since MySQL 8.0.17, is a great feature that allows cloning databases out of the box. It is easy to rebuild a replica or to add new nodes to a cluster using the plugin. Before the release of the plugin, the best open-source alternative was Percona XtraBackup for MySQL Databases.

In this blog post, we compare both alternatives for cloning purposes. If you need to perform backups, Percona XtraBackup is a better tool as it supports compression and incremental backups, among other features not provided by the plugin. The plugin supports compression only for network transmission, not for storage.

But one of the plugin’s strong points is simplicity. …

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