38 All Say Inno Db Is Good, So Should We Use Memory Engine or Not

38 All Say InnoDB is Good, So Should We Use Memory Engine or Not #

In my previous article, I left you with a question: why does the value 0 appear in the last row when using a memory temporary table, but in the first row when using a disk temporary table, for two group by statements that both use order by null?

Today, let’s take a look at the reasons behind this issue.

Data Organization Structure of Memory Tables #

To simplify the analysis, let’s assume we have the following two tables, t1 and t2. Table t1 uses the Memory engine, while table t2 uses the InnoDB engine.

create table t1(id int primary key, c int) engine=Memory;
create table t2(id int primary key, c int) engine=innodb;
insert into t1 values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0);
insert into t2 values(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(0,0);

Then, let’s execute select * from t1 and select * from t2 respectively.

img

Figure 1: Results of the two queries - Position of 0

As we can see, the result of the memory table t1 has 0 in the last row, while the InnoDB table t2 has 0 in the first row.

To understand this difference, let’s start with the organization of the primary key indexes in these two engines.

Table t2 uses the InnoDB engine, and you are already familiar with the organization of its primary key index id: InnoDB stores the data on the primary key index tree, which is a B+ tree. Therefore, the data organization of table t2 looks like the following:

img

Figure 2: Data organization of table t2

The values on the primary key index are stored in order. When executing select *, it scans from left to right on the leaf nodes, which is why 0 appears in the first row of the result.

In contrast to the InnoDB engine, the Memory engine separates the data and indexes. Let’s see the data content of table t1.

img

Figure 3: Data organization of table t1

As we can see, the data part of the memory table is stored separately as an array, while the primary key id index stores the positions of each data. The primary key id is a hash index, and the keys on the index are not sorted.

In the memory table t1, when I execute select *, it performs a full table scan, which means sequentially scanning this array. Therefore, 0 is the last data that is read and put into the result set.

From this, we can see that the data organization of InnoDB and Memory engines is different:

  • InnoDB engine stores data on the primary key index, and other indexes store the primary key id. This organization is called Index Organized Table.
  • Memory engine stores data separately and the index stores the positions of the data. This organization is called Heap Organized Table.

From these differences, we can observe some typical characteristics of these two engines:

  1. InnoDB tables always store data in an ordered manner, while memory tables store data in the order of insertion.
  2. When there are holes in the data file, InnoDB tables need to write new values in fixed positions to maintain data order, while memory tables can insert new values wherever there are empty spaces.
  3. When the data position changes, InnoDB tables only need to modify the primary key index, while memory tables need to modify all indexes.
  4. When querying using the primary key index, InnoDB tables need to perform one index lookup, and when querying using a non-primary key index, they need to perform two index lookups. In contrast, memory tables don’t have this distinction, and the “status” of all indexes is the same.
  5. InnoDB supports variable-length data types, where the lengths of different records may vary. Memory tables do not support Blob and Text fields, and even if varchar(N) is defined, it is actually stored as a fixed-length string (char(N)). Therefore, each row in a memory table has the same data length. Due to these characteristics of in-memory tables, the empty space left by each deleted data row can be reused by the next data to be inserted. For example, if the following commands are executed in table t1:
delete from t1 where id=5;
insert into t1 values(10,10);
select * from t1;

In the result of the select statement, you will see that the row with id=10 appears after the row with id=4, which was the position of the previously deleted data with id=5.

It should be noted that the primary key index of table t1 is a hash index. Therefore, if a range query is executed, such as:

select * from t1 where id<5;

the primary key index will not be used, and a full table scan will be performed. You can review the content of the 4th article for more information. So, how can we enable range scanning for in-memory tables?

Hash Index and B-Tree Index #

In fact, in-memory tables also support B-Tree indexes. To create a B-Tree index on the id column, the following SQL statement can be used:

alter table t1 add index a_btree_index using btree (id);

After executing this statement, the data organization of table t1 will be as follows:

img

Figure 4 Data organization of table t1 - Adding B-Tree index

The new B-Tree index should look familiar to you, as it is similar to the B+ tree index organization used in InnoDB.

As a comparison, you can take a look at the output of the following two statements:

img

Figure 5 Comparison of query results using B-Tree and hash indexes

As you can see, when executing select * from t1 where id, the optimizer will choose the B-Tree index, resulting in a return of rows 0 to 4. By using force index to force the usage of the primary key id index, the row with id=0 is placed at the end of the result set.

In general, we perceive in-memory tables as having the advantage of being fast, and one of the reasons is the support for hash indexes in the Memory engine. Of course, the more important reason is that all the data in an in-memory table is stored in memory, and the speed of reading and writing to memory is always faster than that of disk.

However, next I will explain why I do not recommend using in-memory tables in a production environment. The reasons mainly include two aspects:

  1. Lock granularity issue;
  2. Data persistence issue.

Locks in In-Memory Tables #

Let’s first talk about the lock granularity issue in in-memory tables.

In-memory tables do not support row-level locks, only table-level locks. Therefore, as long as a table is being updated, it will block all other read and write operations on that table.

It is important to note that the table-level locks here are different from the MDL locks that we introduced before, but they are both table-level locks. Next, I will simulate the table-level lock in in-memory tables through the following scenario. img

Figure 6 Table Lock of Memory Table - Reproduction Steps

In this execution sequence, the update statement from session A takes 50 seconds to execute. During the execution of this statement, the query from session B enters into a lock-wait state. The result of the show processlist command from session C is as follows:

img

Figure 7 Table Lock of Memory Table - Result

Compared with row locks, table locks do not provide good support for concurrent access. Therefore, the lock granularity issue of memory tables determines that their performance in handling concurrent transactions is not too good.

Data Durability Issue #

Next, let’s take a look at the issue of data durability.

Storing data in memory is an advantage of memory tables, but it is also a disadvantage. When the database restarts, all memory tables will be cleared.

You may say that if the database restarts abnormally, it would not be a problem for memory tables to be cleared. However, in a high availability architecture, this characteristic of memory tables can be regarded as a bug. Why is that?

Let’s first take a look at the problems of using memory tables in an M-S architecture.

img

Figure 8 M-S Basic Architecture

Let’s look at the following timeline:

  1. Normal business access to the master database.
  2. The hardware of the standby database is upgraded, and the standby database is restarted, causing the content of memory table t1 to be cleared.
  3. After the standby database restarts, the client sends an update statement to modify the data rows of table t1, and at this time, the apply thread of the standby database will report an error “Cannot find the row to update.”

This will cause the synchronization between the master and standby databases to stop. Of course, if a master/standby switchover occurs at this time, the client will see that the data of table t1 is “lost.”

In the architecture with a proxy shown in Figure 8, it is assumed that the logic for master/standby switchover is maintained by the database system itself. For the client, it is like “network disconnection, and after reconnecting, finding that the data in the memory table is lost.”

You may say that this is not a big deal, after all, a master/standby switchover occurs, and the connection will be disconnected. The business end can perceive the exception.

However, the next feature of memory tables will make the usage scenario appear more “peculiar.” Because MySQL knows that the data in memory tables will be lost after a restart, to avoid the inconsistency between the master and standby databases after the master database restarts, MySQL does the following: after the database restarts, it writes a line of DELETE FROM t1 to the binlog.

If you use the architecture shown in Figure 9, a Dual M structure:

img

Figure 9 Dual M Structure

When the standby database restarts, the DELETE statement in the binlog of the standby database will be transmitted to the master database, and then the content of the memory table in the master database will be deleted. So, when you use it, you will find that the data in the memory table of the master database is suddenly cleared.

Based on the analysis above, you can see that memory tables are not suitable for normal data table usage in a production environment.

Some may say that memory tables are faster. You can analyze this problem as follows:

  1. If your table has a large amount of updates, then the concurrency level is an important reference indicator. InnoDB supports row locks and has better concurrency than memory tables.
  2. The data that can be stored in memory tables is not large. If you consider the performance of reads, a table with high read QPS and a small amount of data, even if you use InnoDB, the data will be cached in the InnoDB Buffer Pool. Therefore, the read performance of using InnoDB tables will not be inferior.

Therefore, I recommend that you replace ordinary memory tables with InnoDB tables. However, there is one scenario that is an exception. This scene is about the temporary table that we mentioned in chapter 35 and 36. In cases where the data volume is controllable and does not consume too much memory, you can consider using an in-memory table.

An in-memory temporary table can overcome the two limitations of in-memory tables due to the following three reasons:

  1. Temporary tables are not accessed by other threads, so there are no concurrency issues.
  2. Temporary tables need to be deleted after restart, so there is no need to worry about clearing the data.
  3. Temporary tables on the standby server do not affect the user threads on the primary server.

Now, let’s go back to the example of optimizing the join statement in chapter 35. At that time, I suggested creating an InnoDB temporary table with the following sequence of statements:

create temporary table temp_t(id int primary key, a int, b int, index(b))engine=innodb;
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);

Now that you understand the characteristics of in-memory tables, you can see that using an in-memory temporary table here would be more effective for three reasons:

  1. Compared to InnoDB tables, in-memory tables do not need disk writes, so writing data to the temp_t table is faster.
  2. The index on b uses a hash index, which has faster lookup speed compared to a B-Tree index.
  3. The temporary table only has 2000 rows, so it occupies limited memory.

Therefore, you can modify the statement sequence of the [35th chapter] by changing the temporary table t1 to an in-memory temporary table and creating a hash index on the b field:

create temporary table temp_t(id int primary key, a int, b int, index (b))engine=memory;
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);

img

Figure 10: Execution result using in-memory temporary table.

As you can see, both the data import time and the join execution time using the in-memory temporary table are faster than using the InnoDB temporary table.

Summary #

In this article, I started with the question of whether to use in-memory tables and introduced several characteristics of the Memory engine.

As we can see, due to data loss upon restart, if a standby server restarts, it will cause the primary and standby synchronization threads to stop. If the primary server is in a dual M architecture with the standby server, it may also result in the deletion of the memory table data on the primary server.

Therefore, in production, I do not recommend using ordinary in-memory tables.

If you are a DBA, you can add rules like this to the table creation review system, requiring the business to use InnoDB tables. As we analyzed in the article, InnoDB tables have good performance and provide data security. In contrast, in-memory tables do not support row-level locks, and update statements will block queries, so their performance may not be as good as expected.

Based on the characteristics of in-memory tables, we also analyzed a use case, which is in-memory temporary tables. With the support of hash indexes, in-memory temporary tables can greatly accelerate complex queries.

Finally, let me leave you with a question.

Suppose you have just taken over a database and discovered an in-memory table. Restarting the standby server will definitely clear the memory table data on the standby server, thus causing the primary and standby synchronization to stop. In this case, the best practice is to modify it into an InnoDB engine table.

Assume that at that time, the business scenario temporarily does not allow you to modify the engine. What automated logic can you add to avoid the interruption of primary and standby synchronization?

Please share your thoughts and analysis in the comments section. I will discuss this question with you at the end of the next article. Thank you for listening, and feel free to share this article with more friends to read together.