MySQL分区:性能提高适用于多个分区表.为什么? [英] MySQL Partitioning: Performance increase For multiple partitioned tables. Why?

问题描述

我已经实现了一个基准测试，可以测试10个不同表的读写持久性.

I have implemented a benchmark which tests the permanence of reads and writes on 10 different tables.

我有10个Java线程，每个线程仅在其表上执行查询:

I have 10 java threads , each performs queries on its table only:

线程1在表1上执行操作， 线程2在Table2上执行操作，

Threads 1 performs operations on Table1, Threads 2 performs operations on Table2,

我已经比较了在未分区的Table [1-10]上并行执行多个读取操作并行运行这些线程的性能，与将每个TableX划分为9个相等的分区(每个代表一周的测量，总共9周的数据.

I have compered the performance of running those threads in parallel executing multiple reads on non partitioned Table[1-10], vs the performance of the same benchmark while partitioning each TableX to 9 equal partitions (each represents a week of measurements, total 9 weeks of data).

我收到了有趣且无法解释的结果:

I have received interesting and non explainable results:

对于非分区表，单个线程的单个操作的性能要好两倍，而对于分区表，所有线程的所有操作的总时间要快5倍.

The performance of a single operation for a single thread is two times better for a non partitioned table, while the total time of all operations for all threads is 5 times faster for the partitioned tables.

我检查了日志，发现分区表可以并行执行更多操作，尽管单个操作较长，但针对不同线程的多个操作并行执行，因此执行速度更快.

I checked the logs, and noticed that partitioned tables enable more operations to be paralleled, and although a single operation is longer, multiple operations for different threads are paralleled , and therefore finish faster.

我不明白为什么会这样，因为每个线程仅在自己的表上执行操作?

I don't understand why is this happen, since each thread execute operations only on its own table???

我仅对所有表和分区使用MyISAM引擎.

I am using only MyISAM engine for all my tables and partitions.

推荐答案

查看给定的文章分区如何帮助提高服务器性能

Check out the given article that how Partitioning help to improve server performance

如何确定是否您应该使用表分区

以及由专家回答的几乎相同的问题

and also almost same question answered by expert check it out

Stackoverflow问题

这篇关于MySQL分区:性能提高适用于多个分区表.为什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！