Showing entries 1 to 10 of 1171
10 Older Entries »
Displaying posts with tag: innodb (reset)
Troubleshooting an OLAP system on InnoDB

As a part of Mydbops Consulting we have a below problem statement from one of our client.

We have a high powered server for reporting which in turn powers our internal dashboard for viewing the logistics status.Even with a high end hardware, we had a heavy CPU usage and which in turn triggers spikes in replication lag and slowness. Below is the hardware configuration.

OS : Debian 9 (Stretch)
CPU : 40
RAM : 220G (Usable)
Disk : 3T SSD with 80K sustained IOPS.
MySQL : 5.6.43-84.3-log Percona Server (GPL)
Datasize : 2.2TB

Below is the graph on CPU utilisation from Grafana.

Since the work load is purely reporting(OLAP) we could observe a similar type of queries with different ranges. Below is the Execution plan of the query. It is a join query over 6 tables.

Explain Plan:

[Read more]
Examining MySQL InnoDB Persistent Statistics

A few days ago I wrote about how grossly outdated statistics returned through MySQL’s Information_Schema can be. In that post, Øystein Grøvlen suggested taking a look at mysql.innodb_table_stats and mysql.innodb_index_stats as a better source of information. Let’s do just that!

Let’s start with the good news. Unlike MySQL Data Dictionary Tables (mysql.table_stats, etc),  mysql.innodb_table_stats and mysql.innodb_index_stats can be queried by the user. They also contain a lot of interesting statistics. Before we get to that though, let’s examine where those tables come from in more detail.

As the manual tells us, these tables are storing …

[Read more]
Q & A on Webinar “Top 3 Features of MySQL”

First I want to thank everyone who attended my December 19, 2019 webinar “Top 3 Features of MySQL“. Recording and slides are available on the webinar page.

Here are answers to the questions from participants which I was not able to provide during the webinar.

Q: When do undo logs and redo logs come into play? Can you please explain the background operations of these logs?

A: These two are completely different structures.

Undo Logs

Belong to a single active transaction. It contains information on how to undo the latest change to the clustered index, performed by this transaction.

Let’s demonstrate how they work with an example.

Consider this table:

[Read more]
SELECT … FOR UPDATE on non-existent rows

TL; DR

SELECT … FOR UPDATE has a (not so) surprising side effect on non-existent rows: it could cause a (serious) performance penalty and even prevent you from inserting new rows at all.

Locking rows for update

A development team of ours was working on an application that needed to ensure an update on a single row item isn’t modified by another transaction. Naturally they started making use of SELECT … FOR UPDATE to lock the row before updating it. This worked excellent to keep anyone else from updating this row. However they started to get some lock wait timeouts on new inserts of totally unrelated items during a load test and they asked me to look into this.

SELECT … FOR UPDATE is described as following in the MySQL documentation:
A SELECT ... FOR UPDATE …

[Read more]
MySQL Encryption: Master Key Encryption in InnoDB

In the previous blog post of this series, MySQL Encryption: Talking About Keyrings, I described how keyrings work. In this post, I will talk about how master key encryption works and what the pros and cons are of using envelope encryption such as a master key.

The idea behind envelope encryption is that you use one key to encrypt multiple other keys. In InnoDB, this “one key” is the master encryption key and the “multiple other keys” are the tablespace keys. Those tablespace keys are the ones that are actually used to encrypt tablespaces. Graphically it can be presented like this:

 

The master key resides in the keyring, while encrypted tablespace keys reside in tablespace headers (written on page 0 of a tablespace). In the picture above:

Table A is encrypted with key 1. Key 1 is encrypted …

[Read more]
Fun with Bugs #90 - On MySQL Bug Reports I am Subscribed to, Part XXIV

Previous post in this series was published 3 months ago and the last Bug #96340 from it is already closed as fixed in upcoming MySQL 8.0.19. I've picked up 50+ more bugs to follow since that time, so I think I should send quick status update about interesting public MySQL bug reports that are still active.

As usual I concentrate mostly on InnoDB, replication and optimizer bugs. Here is the list, starting from the oldest:

  • Bug #96374  - "binlog rotation deadlock when innodb concurrency limit setted". This bug was reported by …
[Read more]
MySQL Clone Plugin Speed Test

In my previous blog, I have explained how the MySQL clone plugin works internally. In this blog, I am going to do a comparison of  Backup and Recovery speed of MySQL clone plugin with other available mysql open source backup tools.

Below tools are used for speed comparison of Backup and Recovery,

  1. Clone-Plugin
  2. Xtrabackup
  3. mysqldump
  4. mydumper with myloader
  5. mysqlpump

Test …

[Read more]
InnoDB Page Compression – MySQL 8 Compression

InnoDB Page Compression Explained 

We have several customers with multi-terabyte database infrastructure on SSDs, The SSDs are great investment for performance but they are also expensive with shorter lifespans so storage efficiency management is something we are very cautious about on SSDs, At MinervaDB Labs we spend considerable amount of time doing research on InnoDB page compressions benefits and most common mistakes. The compressed tables were first introduced in 2008  with InnoDB plugin for MySQL 5.1 . Facebook has been a major committer to this project and most of it were later implemented in upstream MySQL code as well. We can implement compression in InnoDB is two ways, Either by using Barracuda InnoDB file format or ROW_FORMAT=COMPRESSED

How InnoDB page compression works ?

When a page is …

[Read more]
MySQL load data infile made faster .

Loading any large file into MySQL server using the LOAD DATA INFILE is a time consuming process , because it is single threaded and it is a single transaction too. But with modern hardwares system resource is not a bottle neck. At Mydbops we focus on improving the efficiency of process as we value performance more. MySQL introduced the parallel load data operations in its latest minor release MySQL 8.0.17 . I had the curiosity to test this feature and wanted to know, how it can improve the data loading comparing to the existing single threaded method  . Through this blog I am going to  compare the both methods .

Remember you can use the parallel data loading utility only via MySQL Shell .

Internal Work Flow :

This section describes the …

[Read more]
Foreign Key bugs in MySQL and MariaDB

Foreign keys are a controversial topic. MySQL and MariaDB implementation has several bugs and limitations, that are discussed here.

Showing entries 1 to 10 of 1171
10 Older Entries »