Author - Alexander Rubin

Using MySQL 5.7 Generated Columns to Increase Query Performance

MySQL Generated Columns

In this blog post, we’ll look at ways you can use MySQL 5.7 generated columns (or virtual columns) to improve query performance.
Introduction
About two years ago I published a blog post about Generated (Virtual) Columns in MySQL 5.7. Since then, it’s been one of my favorite features in the MySQL 5.7 release. The reason is simple: […]

Read more

Internal Temporary Tables in MySQL 5.7

InnoDB row operations graph from PMM

In this blog post, I investigate a case of spiking InnoDB Rows inserted in the absence of a write query, and find internal temporary tables to be the culprit.
Recently I was investigating an interesting case for a customer. We could see the regular spikes on a graph depicting “InnoDB rows inserted” metric (jumping from […]

Read more

One Million Tables in MySQL 8.0

MySQL 8.0

In my previous blog post, I talked about new general tablespaces in MySQL 8.0. Recently MySQL 8.0.3-rc was released, which includes a new data dictionary. My goal is to create one million tables in MySQL and test the performance.
Background questions
Q: Why million tables in MySQL? Is it even realistic? How does this happen?
Usually, millions of tables […]

Read more

Chasing a Hung MySQL Transaction: InnoDB History Length Strikes Back

Hung MySQL Transaction

In this blog post, I’ll review how a hung MySQL transaction can cause the InnoDB history length to grow and negatively affect MySQL performance.
Recently I was helping a customer discover why SELECT queries were running slower and slower until the server restarts (which got things back to normal). It took some time to get […]

Read more

Non-Deterministic Order for SELECT with LIMIT

Non-Deterministic Order

In this blog, we’ll look at how queries in systems with parallel processing can return rows in a non-deterministic order (and how to fix it).
Short story:
Do not rely on the order of your rows if your query does not use
ORDER BY. Even with
ORDER BY, rows with the same values can be sorted differently. To […]

Read more

Column Store Database Benchmarks: MariaDB ColumnStore vs. Clickhouse vs. Apache Spark

Column Store Database

This blog shares some column store database benchmark results, and compares the query performance of MariaDB ColumnStore v. 1.0.7 (based on InfiniDB), Clickhouse and Apache Spark.
I’ve already written about ClickHouse (Column Store database).
The purpose of the benchmark is to see how these three solutions work on a single big server, with many CPU cores and large amounts of RAM. […]

Read more

ClickHouse: New Open Source Columnar Database

Clickhouse

For this blog post, I’ve decided to try ClickHouse: an open source column-oriented database management system developed by Yandex (it currently powers Yandex.Metrica, the world’s second-largest web analytics platform).
In my previous set of posts, I tested Apache Spark for big data analysis and used Wikipedia page statistics as a data source. I’ve used the same data as […]

Read more

MySQL 8.0: Descending Indexes Can Speed Up Your Queries

MySQL 8.0 innodb_dedicated_server

In this blog, we’ll discuss descending indexes in MySQL 8.0.
Summary
The future MySQL 8.0 will (probably) have a great new feature: support for index sort order on disk (i.e., indexes can be physically sorted in descending order). In the MySQL 8.0 Labs release (new optimizer preview), when you create an index you can specify the order […]

Read more

Upgrading to MySQL 5.7? Beware of the new STRICT mode

STRICT mode

This blog post discusses the ramifications of STRICT mode in MySQL 5.7.
In short
By default, MySQL 5.7 is much “stricter” than older versions of MySQL. That can make your application fail. To temporarily fix this, change the
SQL_MODE to
NO_ENGINE_SUBSTITUTION (same as in MySQL 5.6):

MySQL

mysql> set global SQL_MODE=”NO_ENGINE_SUBSTITUTION”;

1

mysql> set global SQL_MODE=”NO_ENGINE_SUBSTITUTION”;

MySQL 5.7, dates and default values
The default
SQL_MODE in […]

Read more