mysql cluster group by performance issue

Asked Jan 08 '16 at 05:33

Active Jan 08 '16 at 05:56

Viewed 217 times

I am working on database performance optimization issue, and I did a simple test on mysql cluster, the question is why a single group by statement in 30M records will take 1m20s so long.

environment:

linux x64 centos

mysql cluster 7.4.8

cluster:

1 master node 32GB memory, 40 core cpu

3 data nodes 32GB memory, 40 core cpu(each node)

3 sql nodes 32GB memory, 40 core cpu(each node)

test case:

test in "test" database

30,000,000 recods in test tables

test sql：

select count(*), sum(impress), sum(click) from test1 group by entitykey order by null;

2m20s with entitykey column indexed. 1m20s without entitykey column indexed.

this test result confuses me, why it's so painful slow

comparison:

MS SQL SERVER: lenovo notepad, 8GB memory, disk storage, i5 8 core cpu, finished in 10s!

I am wondering, if I missed some sql node configuration or something else?

edited Jun 20 '20 at 09:12

Community

asked Jan 08 '16 at 05:33

Lei Chi

`2m20s with Indexed, but only 1m20s without Index`...?!! – The Coder Jan 08 '16 at 05:45
In all three timings, did you determine whether the data was cached in RAM or came from disk? Run each query twice. – Rick James Jan 08 '16 at 05:53
mysql test data cached into memory, i have assigned 10GB data memory and 5GB index memory on data node. SQL SERVER is a disk storage. – Lei Chi Jan 08 '16 at 05:59
@TheCoder yes, it's true!!! i did test three times!!! – Lei Chi Jan 08 '16 at 06:05

mysql cluster group by performance issue

0 Answers0