# Expected difference in write performance between XtraDB and MyISAM?

**URL:** <https://forums.percona.com/t/expected-difference-in-write-performance-between-xtradb-and-myisam/14361>\
**Category:** Percona Server for MySQL\
**Created:** [February 10, 2022, 6:50pm UTC](https://forums.percona.com/t/expected-difference-in-write-performance-between-xtradb-and-myisam/14361 "2022-02-10T18:50:00Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![torfo](https://avatars.discourse-cdn.com/v4/letter/t/eb9ed0/32.png) [@torfo](https://forums.percona.com/u/torfo)\
**Post date:** [February 10, 2022, 6:50pm UTC](https://forums.percona.com/t/expected-difference-in-write-performance-between-xtradb-and-myisam/14361/1 "2022-02-10T18:50:00Z")

</div>

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](https://hub.docker.com/_/percona) (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**

```auto

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

```

---

<div class="post-metadata">

**Author:** ![Michael\_Coburn](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/michael_coburn/32/18_2.png) [@Michael\_Coburn](https://forums.percona.com/u/Michael_Coburn)\
**Post date:** [February 10, 2022, 8:08pm UTC](https://forums.percona.com/t/expected-difference-in-write-performance-between-xtradb-and-myisam/14361/2 "2022-02-10T20:08:54Z")

</div>

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`.

> **[Linux perf Examples](https://www.brendangregg.com/perf.html)**
>
> Examples of using the Linux perf command, aka perf\_events, for performance analysis and debugging. perf is a profiler and tracer.