Showing entries 1 to 10 of 174
10 Older Entries »
Displaying posts with tag: storage engine (reset)
COUNT(*) vs COUNT(col) in MySQL

Looking at how people are using COUNT(*) and COUNT(col), it looks like most of them think they are synonyms and just use what they happen to like, while there is a substantial difference in performance and even query results. Also, we find a difference in execution on InnoDB and MyISAM engines.

NOTE: All tests were applied for MySQL version 8.0.30, and in the background, I ran every query three to five times to make sure that all of them were fully cached in the buffer pool (for InnoDB) or by the filesystem (for MyISAM).

Count function for Innodb engine:

Let’s have look at the following series of examples for InnoDB engine:

CREATE TABLE count_innodb (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  val_with_nulls int(11) default NULL,
  val_no_null int(10) unsigned NOT NULL,
  PRIMARY KEY idx (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

(mysql) > select count(*) from count_innodb; …
[Read more]
A MyRocks Use Case

I wrote this post on MyRocks because I believe it is the most interesting new MySQL storage engine to have appeared over the last few years. Although MyRocks is very efficient for writes, I chose a more generic workload that will provide a different MyRocks use case.

The use case is the TPC-C benchmark but executed not on a high-end server but on a lower-spec virtual machine that is I/O limited like for example, with AWS EBS volumes. I decided to use a virtual machine with two CPU cores, four GB of memory, and storage limited to a maximum of 1000 IOPs of 16KB. The storage device has performance characteristics pretty similar to an AWS gp2 EBS volume of about 330 GB in size. I emulated these limits using the KVM iotune settings in my lab.

<iotune>
     <total_iops_sec>1000</total_iops_sec> …
[Read more]
MyRocks Use Case: Big Dataset

One of the questions I am often asked is in what cases I would prefer MyRocks over InnoDB. We have covered MyRocks in our blog previously:

MyRocks Performance – Percona Database Performance Blog

Saving With MyRocks in The Cloud – Percona Database Performance Blog

But it would be good to refresh some materials.

This time I want to take an interesting (and real) data set, which I also covered previously: the Reddit Comments dataset (see Big Dataset: All Reddit Comments – Analyzing with ClickHouse – Percona Database Performance Blog). The dataset is still available for download from …

[Read more]
Building Percona Server for MySQL 8.0 with RocksDB Storage Engine on macOS

In Percona Server for MySQL 8.0.29-21, we added one more patch that helps us to build server code on macOS. To be precise here, we still could do this even before this patch but only partially. Now it is possible to build RocksDB Storage Engine as well.

A word of disclaimer here, at the moment, by macOS we still understand macOS for Intel x86_64 architecture (the most recent ARM versions with Apple M1 / M2 processors are out of the scope of this blog post). Moreover, Percona does not provide …

[Read more]
Temporary Tables in MySQL – Never Ending Story?

If you ever had to deal with performance and/or disk space issues related to temporary tables, I bet you eventually found yourself puzzled. There are many possible scenarios depending on the type of temporary table, settings, and MySQL version used. We have observed a pretty long evolution in that matter due to a couple of reasons. One of them was the need to completely eliminate the need to use the deprecated MyISAM engine, and at the same time introduce more performant and reliable alternatives. Another set of improvements was required related to InnoDB, where it was necessary to lower the overhead of temporary tables using that engine.

For that reason, I decided to gather them in a sort of summary which may help to troubleshoot their usage. Due to vast changes between major MySQL releases, I divided the article by them.

[Read more]
Reminder: TokuDB Storage Engine Will Be Disabled by Default in Percona Server for MySQL 8.0.26

As we’ve communicated in our blog post in May, the TokuDB Storage Engine has been marked as “deprecated” in Percona Server for MySQL 8.0. It will be removed in a future version (Percona Server for MySQL 8.0.28, expected to ship in Q1 2022).

With the release of Percona Server for MySQL 8.0.26, the storage engine will still be included in the binary builds and packages but will be disabled by default. If you are upgrading from a previous version, the TokuDB Storage Engine plugin will fail with an error message at server startup if it is installed.

You will still be able to re-enable it manually so that you can perform the necessary migration steps.

Re-enabling the TokuDB …

[Read more]
Heads-Up: TokuDB Support Changes and Future Removal from Percona Server for MySQL 8.0

Back in December 2018, when we announced the general availability of Percona Server for MySQL 8.0, we also announced that the TokuDB Storage Engine has been marked as “deprecated” in this release, recommending to use the MyRocks Storage Engine as an alternative. We believe that MyRocks provides similar benefits for the majority of workloads and is better optimized for modern hardware.

Since then, we have continued maintaining the storage engine in the 8.0 release, e.g. by incorporating bug fixes. However, the ongoing amount of …

[Read more]
InnoDB File Growth Weirdness

There is a common pattern in life, you often discover or understand things by accident. Many scientific discoveries fit such a description. In our database world, I was looking to see how BLOB/TEXT columns are allocated using overlay pages and I stumbled upon something interesting and unexpected. Let me present to you my findings, along with my attempt at explaining what is happening.

InnoDB Tablespaces

The first oddity I found is a bunch of free pages in each tablespace it is skipping. Here’s an example from a simple table with only an integer primary key and a char(32) column:

root@LabPS8_1:~/1btr# innodb_space -f /var/lib/mysql/test/t1.ibd space-extents
start_page  page_used_bitmap
0               #####################################........................... <--- free pages
64              ################################################################
128 …
[Read more]
Which Version of MySQL Should I Use for MyRocks?

As database footprints continue to explode, many companies are looking for ways to deal with such rapid growth.  One approach is to refactor traditional relational databases to fit into a NoSQL engine, where horizontal scalability is easier.  However, in many cases, this is in no way a trivial undertaking.

Another approach that has been gaining interest is the use of MyRocks as an alternative storage engine to the traditional InnoDB.  While not for everyone, in certain use cases it could be a potential solution.  As with so many things open source, the next standard questions are: which version should I use?  Any differences with the engine if I use MyRocks with MySQL 5.7 vs 8.0?

In this post, I wanted to touch on this and give some high-level thoughts on MyRocks when it comes to the version of MySQL.

[Read more]
Working to Validate MyRocks in the Enterprise with Dropbox

Percona Technical Account Managers get the privilege of working with some of our largest enterprise clients day in and day out.  As such, we get to really focus on how to best leverage our technology to generate measurable benefits for our users.  While it is fun to “nerd out” and always strive to use the latest and greatest, we need to stay focused on demonstrating business value and a genuine need.  Over the past few months, I’ve been working with one of my larger clients, Dropbox, along with our professional services team to validate the use of Percona Server for MySQL with the MyRocks storage engine over a large portion of their MySQL infrastructure.

Please note – this is not meant to be a deep dive into the technical details around …

[Read more]
Showing entries 1 to 10 of 174
10 Older Entries »