This article focuses on the database storage engines for MySQL that ensure appropriate performance and manage SQL operations for multiple table types. The article examines the differences between the most widely used MySQL storage engines. MySQL is the second most popular Relational Database Management Systems (RDBMS) in the world. Countless services and applications have MySQL […]
The post Storage Engines in MySQL appeared first on Devart Blog.
What’s a good natural key?
This is a very difficult question for most entities when you design your schema. In some rare cases, there seems to be an “obvious” candidate, such as a variety of ISO standards, including:
But even in those cases, there might be exceptions and the worst thing that can happen is a key change. Most database designs play it safe and use surrogate keys instead. Nothing wrong with that. But…
Relationship tables
There is one …
[Read more]There are a number of options for generating ID values for your tables. In this post, Alexey Mikotkin of Devart explores your choices for generating identifiers with a look at auto_increment, triggers, UUID and sequences.
AUTO_INCREMENT
Frequently, we happen to need to fill tables with unique identifiers. Naturally, the first example of such identifiers is PRIMARY KEY data. These are usually integer values hidden from the user since their specific values are unimportant.
When adding a row to a table, you need to take this new key value from somewhere. You can set up your own process of generating a new identifier, but MySQL comes to the aid of the user with the AUTO_INCREMENT column setting. It is set as a column attribute and allows you to generate unique integer identifiers. As an example, consider the …
[Read more]Due to continuous development and improvement, Percona Server for MySQL incorporates a number of improvements related to binary log handling and replication. This results in replication specifics, distinguishing it from MySQL Server.
Temporary tables and mixed logging format Summary of the fix:
As soon as some statement involving temporary tables was met when using a mixed binlog format, MySQL switched to row-based logging for all statements until the end of the session (or until all temporary tables used in the session were dropped). This is inconvenient when you have long-lasting connections, including replication-related ones. Percona Server for MySQL fixes the situation by switching between …
[Read more]
In this blog post, I’ll discuss some of the
MySQL and MariaDB default configuration differences, focusing on
MySQL 5.7 and MariaDB 10.2. Percona Server for MySQL uses the
same defaults as MySQL, so I will not list them separately.
MariaDB Server is a general purpose open source database, created by the founders of MySQL. MariaDB Server (referred to as MariaDB for brevity) has similar roots as Percona Server for MySQL, but is quickly diverging from MySQL compatibility and growing on its own. MariaDB has become the default installation for several operating systems (such as Red Hat Enterprise Linux/CentOS/Fedora). Changes in the default variables can make a large difference in the out-of-box …
[Read more]TL;DR: we still need MyISAM and myisampack because it uses less space on disk (half of compressed InnoDB) !
In the previous post, I shared my experience with InnoDB table compression on a read-only dataset. In it, I claimed, without giving much detail, that using MyISAM and myisampack would result is a more compact storage on disk. In this post, I will share more details about this claim.
In my last post about big MySQL deployments, I am quickly mentioning that InnoDB compression is allowing dividing disk usage by about 4.3 on a 200+ TiB dataset. In this post, I will give more information about this specific use case of InnoDB table compression and I will share some statistics and learnings on this system and subject. Note that I am not covering InnoDB page compression which is
MySQL theoretical limits are known and they can be found in the manual, they include:
MyISAM permits data and index files to grow up to 256 TiB by default, but this limit can be changed up to the maximum permissible size of 65,536 TiB (256^7 − 1 bytes). The maximum tablespace size depends on the InnoDB page size: 64 TiB for 16 KiB pages.(The maximum tablespace size is also the maximum size for a
This blog discusses the gradual end of MyISAM in MySQL.
The story that started 20 years ago is coming to its end. I’m talking about the old MyISAM storage engine that was the only storage provided by MySQL in 1995, and was available in MySQL for 20+ years. Actually, part of my job as a MySQL consultant for 10+ years was to discover MyISAM tables and advise customers how to convert those to InnoDB.
(Check your MySQL installation, you may still have MyISAM tables).
MySQL 5.7 still used MyISAM storage for the system tables in the MySQL schema.
In MySQL 8.0 (DMR version as of writing), the MyISAM storage engine is still available. But in a very limited scope:
I was recently asked this question by an experienced academic at the NY Oracle Users Group event I presented at.
Does MySQL support ACID? (ACID is a set of properties essential for a relational database to perform transactions, i.e. a discrete unit of work.)
Yes, MySQL fully supports ACID, that is Atomicity, Consistency, Isolation and Duration. (*)
This is contrary to the first Google response found searching this question which for reference states “The standard table handler for MySQL is not ACID compliant because it doesn’t support consistency, isolation, or durability”.
The question is however not a simple Yes/No because it depends on timing …
[Read more]
