In this blog post, we’ll look at MySQL 8.0 general tablespaces.

Introduction

MySQL 8.0 (the DMR version is available now) has two great features (among others):

1. The new data dictionary completely removed *.frm files, which is great
2. The ability to create a tablespace and assign a group of tables to it (originally introduced in 5.7).

With those two options, we can use MySQL for creating multi-tenant environments with a “schema per customer” approach.

Schema per Customer with MySQL 8.0

Using schema per customer with older MySQL versions presents issues  … namely the number of files. (I’ve described …

Optimiser is the brain of the RDBMS. Optimiser decides the right access method , algorithms , join order and right index to be used for better execution of the query. This blog is made to shed some lights on Cost based optimiser in MySQL 5.7. The cost or statistics are stored in the data dictionary .

What is cost-based optimization ?

• The cost model is based on estimates of cost various operations occur during query execution.

• The optimizer has a set of default “cost constants” it will make decision on execution plans.

• In MySQL 5.7, the optimizer has addition a database of cost estimates to use during the execution plan.

• These cost estimates are stored in server_cost & engine_cost tables in MySQL schema. For more details Cost Model

MySQL …

In this blog we are going to share my recent experience with an issue which we faced during migration between two Ubuntu servers.

The activity was pretty simple, the client had bought a new machine with enhanced memory and faster disk, so the data has to be transferred to the new server and live MySQL replication has to be configured between the old and the new server.

For the hot and live data transfer we used xtrabackup’s stream method, the data transfer happened smoothly without any issue.

Everything was set and we had applied the log and followed the normal procedure to change the ownership of datadir to “mysql” and all the entries in the my.cnf were double checked.

When we intended to start mysql, the service didn’t come up instead we had a weird error as below.

MySQL Error:
——————

2016-08-31 22:35:27 9938 [ERROR] InnoDB: ./ibdata1 can't be opened in …

There was a bold announcement during the MySQL Keynote at Oracle Open World. A new product that will mix up with the existing GA server, called MySQL InnoDB Cluster. This is an evolution of MySQL group replication, which has been in the labs for long time, and the MySQL shell, which was introduced as a side feature last April. The boldness I mentioned before is on account of wanting to add to a GA server something that was defined as release candidate despite never having been out of the labs. The product is interesting as it promises to be a quick and painless cluster deployment, with built-in high availability and scalability.

…

What is InnoDB Cluster?

Hot off of Oracle Open World 2016 is the lab release of MySQL’s InnoDB Cluster.

InnoDB Cluster uses the Group Replication plugin to allow for virtually synchronous replication, while also providing a MySQL Router that is aware of the cluster state. By connecting your application to the router, your application will be able to withstand failover of any node participating in the cluster.

InnoDB Cluster also provides a new MySQL Shell to interact with the cluster commands.

MySQL’s server team gives us a very good hands-on tutorial if you’re interested in getting …

Introduction

This is the final installment of a five part blog series to explore InnoDB internals by looking at the related tunable system variables. In this section we’re going to cover variables that relate to enforcing data consistency, and how index statistics are handled and stored.

Just like previous sections, I would like to emphasize something that was written in part one of this blog post series.

“I should note that while tuning recommendations are provided, this objective of this blog post series was NOT meant to be a tuning primer, but instead to explore the mechanics that each variable interacts with. As such I would like to advise against reading this guide and trying to fine tune all of the available InnoDB variables. System variable tuning is an exercise in diminishing returns, the most benefit you’ll get out of tuning your MySQL server will occur within the first 15 minutes of configuration. In …

In this blog, I will provide answers to the Q & A for the InnoDB Troubleshooting webinar.

First, I want to thank everybody for attending the August 11 webinar. The recording and slides for the webinar are available here. Below is the list of your questions that I wasn’t able to answer during the webinar, with responses:

Q: What’s a good speed for buffer pool speed/size for maximum query performance?

A: I am sorry, I don’t quite understand the question. InnoDB buffer pool is an in-memory buffer. In an ideal case, your whole active dataset (rows that are accessed by application regularly) should be in the buffer pool. There is a good …

Introduction

This is part four of a five part blog series to explore InnoDB internals by looking at the related tunable system variables. In this section we’re going to explore the mechanics that impact CPU resourcing and how InnoDB handles concurrent threads. You’ll notice that a lot of the variables covered in section relate to features that are now disabled but were a lot more prevalent in previous versions of MySQL, particularly those that were released at a time where system context switching had a greater cost than it does today. These variables are still worth discussing as you may run into older systems that utilize these mechanics, and there are even modern systems I’ve worked with that have implemented these features to tune performance to its highest potential.

Just like previous sections, I would like to emphasize something that was written in part one of this blog post series.

“I should note that …

Introduction

This is part three of a five part blog series to explore InnoDB internals by looking at the related tunable system variables. In part 2 we covered variables that had the greatest impact on the file structure of InnoDB as well as how data is written to logs. In this section we will continue looking at I/O but more specifically looking at the mechanics on how data gets written to table files as well as how background threads read from them.

Just like in part two, I would like to emphasize something that was written in part one of this blog post series.

“I should note that while tuning recommendations are provided, this objective of this blog post series was NOT meant to be a tuning primer, but instead to explore the mechanics that each variable interacts with. As such I would …

Percona announces the GA release of Percona Server 5.7.14-7 on August 23, 2016. Download the latest version from the Percona web site or the Percona Software Repositories.

Based on MySQL 5.7.14, including all the bug fixes in it, Percona Server 5.7.14-7 is the current GA release in the Percona Server 5.7 series. Percona’s provides completely open-source and free software. Find release details in the …