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 …

If there is something that I love about information technology is the incredible amount of unpredictable ways that bugs can bite you where and when you less expect it.

This one I'm about to describe is a serious one, yet there has been a very quiet response from Percona bug team at Launchpad, where I first submitted it (because we use Percona server here). If you have read my other posts you know by now that I am a laz... err, busy guy so bear with me if only today I have verified that this is, in fact, an upstream bug and have raised a bug to Oracle too.
It affects 5.6.33, latest version at the time of this article.

Anyways, the bug started to manifest itself in the form of complete stalls of our main cluster, which was happening at different times and apparently in a way that was unrelated to the amount of traffic hitting the master. When stalling, system CPU time was topping 100% of total available …

With MySQL 8.0, we are bringing in an important change in the way user management DDLs are executed.

Background

User management DDLs cover following user management actions:

• Creating user/role (CREATE USER [IF NOT EXISTS] / CREATE ROLE [IF NOT EXISTS])
• Altering user account properties (ALTER USER [IF EXISTS])
• Dropping user/role from database (DROP USER [IF EXISTS] / DROP ROLE [IF EXISTS])
• Granting/Revoking privileges to/from a user/role (GRANT/REVOKE)

Each of these administrative action can handle multiple entities as a part of single command.…

You must have heard about the CVE-2016-6662, the recent zero-day exploit exposed in most of MySQL and its variants. The vulnerability flaw can be exploited by a remote attacker to inject malicious settings into your my.cnf,. you can read about the details here.

At the moment, all supported MySQL vendors by ClusterControl (Oracle, Codership, Percona, MariaDB) have been patched with a bug fix and released in their respective package repository:

With Oracle Open World behind us, we are now getting ready for the next big event, i.e. the European edition of PerconaLive. I am going to be a presenter three times:

• MySQL operations in Docker is a three-hour tutorial, and it will be an expansion of the talk by the same title presented at OOW. Attendees who want to play along can do it, by coming prepared with Docker 1.11 or later and the following images already pulled (images with [+] are mandatory, while [-] are optional):

  …

FRM-less, transactional data dictionary is arguably the most significant feature change announced MySQL 8.0 development release. The server still has two separate dictionaries (MySQL, InnoDB) but the infamous FRM files are finally being replaced with transactional, InnoDB-based storage.

While this is interesting for various reasons, this particular post will focus on the impact this change has on data dictionary performance.
Test configuration Server configuration:

• Hardware: Amazon EC2 m4.4xlarge, 500GB, 15K IOPS
• OS: Debian Jessie
• MySQL versions tested: 5.7.15, 8.0.0-dmr
• Notable customer MySQL configuration values:
  • innodb_buffer_pool_size = 4GB
  • innodb_log_file_size = 256MB
  • innodb_buffer_pool_dump_at_shutdown = 0
  • innodb_buffer_pool_load_at_startup = 0
  • …

The freshly released MySQL 8.0 includes a data dictionary, which makes MySQL much more reliable. Thanks to this features, we don't have any '.frm' files, and querying the information_schema is 30x to 100x faster than previous versions.

One drawback of the implementation is that the data dictionary tables are hidden by design.

While the reason is fully understandable (they don't want to commit on an interface that may change in the future) many curious users are disappointed, because openness is the basis of good understanding and feedback.

The problem to access the dictionary tables can be split in three parts:

• Finding the list of tables; …

MySQL is a growing presence at Oracle Open World. While most of the headlines belong to the main products, where Oracle services are aiming at world domination, MySQL shared the spotlight, as it was announced to be part of Oracle database cloud. It seems a logical move for Oracle: after all the effort to make MySQL 5.7 the biggest release ever, it stands to reason that it is offered as a competitive feature in its own database as a service.

With this offer, Oracle is applying enterprise pricing and methodologies to a target of emerging companies. MySQL in the Oracle cloud differs from the competition by a few key points:

• It's only MySQL 5.7. While this is the most advanced MySQL server …

BakaSQL: a bit of history When I joined this new company I was already aware there was a lot of work to do. There was no DBA Team to speak of; any MySQL related task was handled by the sysadmins, and the company was growing as fast as you can imagine, with 15-20 new employees arriving each month, most of them in the IT department. For sure, there was much fun ahead.

During my first week in the new job  I immediately felt that something was not right. I was receiving too much DMLs to execute each day, spanning a wide set of servers, schemas an tables. This is not something I had seen before, so I started asking around what the reason behind it was.  It turned out that there were multiple reasons behind it:

• there were known bugs in the applications that were never fixed
• application configuration items were stored in MySQL, but there was no admin panel to modify them
…

Another day at the office...

"Whoa, the write workload on our statistical cluster has  suddendly increased by 20% and the filesystem that holds the binary logs is no longer large enough".

Of course, I had warned the boss about this possibility when I received those servers with that tiny 250G filesystem for binlogs, but my red flag was just ignored as usual.

So here we are, presto I get this new shiny 600G LUN, but we need to stop the damn MySQL server in order to repoint the log_bin variable to the new storage area.

Dunno about you, but the idea of waking up at 2am to just perform a variable change is not something that makes me particularly happy. Not to mention the maintenance period that is needed around it....

So, I decided to investigate a bit about the possibilities to do such change without stopping the service.

As we all know, the log_bin …