Expected difference in write performance between XtraDB and MyISAM?

MySQL & MariaDB Percona Server for MySQL 8.0
1

I have a table with 250k rows and I want to update e.g. 50k of them (see example at the end). The update Query for XtraDB tables is considerable slower than what I expected. I am aware of costs for transactional storage engines and overhead for compression - I just did not expect it to be of this magnitude. Where does it come from? Is that normal or am I doing something wrong?

Environment

  • “percona:ps-8” from Docker Hub (currently 8.0.26-17) with default config
  • random laptop

Datapoints

  • XtraDB, ROW_FORMAT=Compressed: 12 s
  • XtraDB, ROW_FORMAT=Dynamic: 10 s
  • MyISAM: 6 s

Example


mysql> SHOW CREATE TABLE medium_test; 
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
| Table       | Create Table                                                                                                                                                                                                                                                                                    | 
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
| medium_test | CREATE TABLE `medium_test` ( 
  `id` int NOT NULL AUTO_INCREMENT, 
  `some_number` int NOT NULL, 
  PRIMARY KEY (`id`) USING BTREE, 
  KEY `some_number` (`some_number`) USING BTREE 
) ENGINE=InnoDB AUTO_INCREMENT=250002 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED | 
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
1 row in set (0.01 sec) 


-- Insert 250k values. (Stored Procedure, counting "some_number" from 0 to 250k, takes ~14 min)

 
mysql> UPDATE medium_test SET some_number = some_number + 1 WHERE some_number > 200000; 
Query OK, 50000 rows affected (12.23 sec) 
Rows matched: 50000  Changed: 50000  Warnings: 0 
 
mysql> ALTER  TABLE medium_test ENGINE=MyISAM; 
Query OK, 250001 rows affected (12.83 sec) 
Records: 250001  Duplicates: 0  Warnings: 0 
 
mysql> UPDATE medium_test SET some_number = some_number + 1 WHERE some_number > 200000; 
Query OK, 50000 rows affected (5.81 sec) 
Rows matched: 50000  Changed: 50000  Warnings: 0 
 
mysql> ALTER  TABLE medium_test ENGINE=InnoDb, ROW_FORMAT=Dynamic; 
Query OK, 250001 rows affected (15.26 sec) 
Records: 250001  Duplicates: 0  Warnings: 0 
 
mysql> UPDATE medium_test SET some_number = some_number + 1 WHERE some_number > 200000; 
Query OK, 50000 rows affected (10.43 sec) 
Rows matched: 50000  Changed: 50000  Warnings: 0
1 Like
2

Hi @torfo , welcome to the Percona forums!

It is expected that MyISAM will be faster than InnoDB, since MyISAM isn’t transactional and therefore has less overhead.

Regarding compression - this as well will be slower since you are taxing the CPUs of your laptop with additional work to compress the data before writing.

If you want to dive deeply into which code factors are contributing to the relative slowness of InnoDB vs MyISAM I recommend you work with the tool perf.

1 Like