MySQL introduced the Event Scheduler in version 5.1.6. The Event Scheduler is a MySQL-level “cron job”, which will run events inside MySQL. Up until now, this was not a very popular feature, however, it has gotten more popular since the adoption of Amazon RDS – as well as similar MySQL database as a service offerings where there is no OS level.

What is important to understand about the Event Scheduler is that it does not have any protection against multiple execution (neither does linux cron). Let’s imagine you have created an event that executes every 10 seconds, but the logic inside the event (i.e. queries or stored procedure call) can …

We recently deployed multiple new pages at VividCortex providing detailed query and metric information. To access this level of information previously, you had to utilize Top Queries, one of our most popular tools. While the power of Top Queries lies in the ability to rank queries by specific criteria, these new pages allow users to easily filter and see detailed information about any query or metric.

VividCortex provides industry-leading, one-second granularity to provide unparallelled visibility into the work being done on your database systems. We track thousands of metrics from the database and operating system, providing you with actionable analysis through our innovative tools. When you deal with this much information, simplicity is a priority. Both of these new tools are simple to use but provide a tremendous amount of data.

Selecting Queries or Metrics in the left-hand …

Following on from my two earlier posts, in the MySQL team we are proposing a new set of changes to defaults for MySQL 5.7:

default_tmp_storage_engine variable was introduced in 5.6.3, allowing the configuration of the default engine for temporary tables. This seems to be in the direction, as I commented before, of making MyISAM an optional engine. In 5.7, a separate tablespace is being created to hold those tables in order to reduce its performance penalty (those tables do not need to be redone if the server crashes, so extra writes are avoided).

However, I have seen many people assuming that because default_tmp_storage_engine has the value “InnoDB”, all temporary tables are created in InnoDB format in 5.6. This is not true: first, because implicit temporary tables are still being created in memory using …

MySQL’s innodb_file_per_table is a wonderful thing – most of the time. Having every table use its own .ibd file allows you to easily reclaim space when dropping or truncating tables. But in some use cases, it may cause significant performance issues.

Many of you in the audience are responsible for running automated tests on your codebase before deploying to production. If you are, then one of your goals is having tests run as quickly as possible so you can run them as frequently as possible. Often times you can change specific settings in your test environment that don’t affect the outcome of the test, but do improve throughput. This post discusses how innodb_file_per_table is one of those settings.

I recently spoke with a customer whose use case involved creating hundreds of tables on up to 16 schemas …

There’s no such thing as a schemaless database. I know, lots of people want a schemaless database, and lots of companies are promoting their products as schemaless DBMSs. And schemaless DBMSs exist. But schemaless databases are mythical beasts because there is always a schema somewhere. Usually in multiple places, which I will later claim is what causes grief.

There Is Always A Schema

We should define “schema” first. It comes from Greek roots, meaning “form, figure” according to my dictionary. Wikipedia says, roughly,

A database schema is its structure; a set of integrity constraints imposed on a database. These integrity constraints ensure compatibility between parts of the schema.

In other words, a schema expresses expectations about what fields exist in a database, and what their types will be. It also enforces those expectations, at least to some extent (there’s usually some …

Analyzing transactional data is becoming increasingly common, especially as the data sizes and complexity increase and transactional stores are no longer to keep pace with the ever-increasing storage. Although there are many techniques available for loading data, getting effective data in real-time into your data warehouse store is a more difficult problem.In this webinar-on-demand we showcase

In this post I will share a recovery scenario of a MySQL database restore from the binary logs. This post is also a good example of how we can achieve…

The post How to Restore / point in time recovery using binary logs MySQL first appeared on Change Is Inevitable.

If you’ve ever looked at the COM_XYZ status counters in MySQL’s SHOW STATUS output, you’ve probably seen Com_admin_commands. It’s not clear what this means, but it can be a major contributor to overall COM_ counters, and it’s actually quite important for server and application performance, as well as being a marker of code quality. In this blog post I’ll explain what the counter really means, and then as a bonus I’ll demonstrate that VividCortex will show you exactly what’s going on in the murkiness of “admin commands.”

What Does The Com_admin_commands Counter Mean?

The first question is “what is this counter?” The manual isn’t really helpful. It just says the following:

The Com_xxx statement counter variables …

Introduction

Causal consistency [1] is one of the consistency criteria that can be used on distributed databases as consistency criteria.

Distributed database provides causal consistency if read and write operations that are causally related are seen by every node of the distributed system in the same order. Concurrent writes may be seen in different order in diffrent nodes.  Causal consistency is waker than sequential consistency [2] but stronger than eventual consistency [3]. See earlier blog for more detailed description on eventual consistency https://blog.mariadb.org/eventually-consistent-databases-state-of-the-art/.

When a transaction performs a read operation followed later by a write operation, even on different object, the first read is said to be causally ordered before the write. This is because the …