SELECT performance lower than other DMLs

Off-Topic
1

Hi there

As below, I organized the cluster and conducted DML performance test.
However, SELECT’s performance was lower than other DMLs.
This was not what I expected.
UPDATE, DELETE thought that SELECT would be performed internally and then proceeded.
However, the performance of simple SELECT statements was lower than other DMLs.

I’m attaching some information you can refer to regarding the test.

In addition, the DML performance indicators below show that the average TPS did not improve as the threads increased further.
Sixteen threads showed the average TPS of the highest point.
I expected linear performance.
What engine variable is blocking this problem?

I am sending you two reports.
One is the report collected during the test in the ‘SELECT statement’.
(report-2020-06-04T172451.tar)

https://drive.google.com/drive/folders/1A5Tp6B-3yBc8oun0KF1Vmympxxpyp5O3?usp=sharing

MemSQL Cluster info

Version : 7.0.14
4Servers( 1ma, 6leaves with HA )
host1: 1MA
host2: 2leaves
host3: 2leaves
host4: 2leaves

TABLE

  • CREATE TABLE TEST_01 (
    ID INTEGER,
    C1 VARCHAR(100),
    C2 VARCHAR(100) ,
    C3 VARCHAR(100) ,
    C4 VARCHAR(100) ,
    C5 VARCHAR(100) ,
    C6 VARCHAR(100) ,
    C7 VARCHAR(100) ,
    C8 VARCHAR(100) ,
    C9 VARCHAR(100) ,
    C10 VARCHAR(100) ,
    … ,
    C18 VARCHAR(100) ,
    UPDATE_DATE DATETIME ,
    PRIMARY KEY(ID)
    ) ;

DML

INSERT INTO TEST_01 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
SELECT ID, C1, C2, C3, C4, C5, C6, C7, …, C18 FROM TEST_01 WHERE ID = ranVal
UPDATE TEST_01 SET C8=C8+1 UPDATE_DATE = updateDate WHERE ID = ranVal
DELETE FROM TEST_01 WHERE ID = ranVal

Data

30 million initial data (ID: 1-300000)
Based on 20 column 200byte, generate the first column random value (ranVal)

100,000 data per thread

  • SELECT_CA : MA, CA, 6leaves with HA

image
image767×290 5.27 KB

INSERT

image

SELECT

image

UPDATE

image

DELETE

image

Thanks in advance
ChaeYoung.

2

How many partitions do you have, and how many cores per leaf node?

3

Hi hanson,

I shared the Google Drive link where the cluster-report was saved.
Please check the above.

Partitions are equal to the number of cores.
It’s a 24 partitions.

Test equipment specifications are as follows.

image
image697×130 16.1 KB

Thanks.
ChaeYoung.

4

@chaeyoung.ko Could you attach the profile for your SELECT in JSON format?

PROFILE SELECT ID, C1, C2, C3, C4, C5, C6, C7, …, C18 FROM TEST_01 WHERE ID = ranVal;
SHOW PROFILE JSON;
5

The highest TPS at 16 threads is expected. Your query is a single-partition query, so the cluster uses one core per thread. Once you have more than 24 threads, the TPS will go down because you do not have enough cores.

If you re-run your benchmark with 24 threads, I would expect the TPS to be higher than the 16-thread case.

6

Thank you for your answer.

I understood the efficiency of TPS per thread.

I have one more question.

Is there a way to increase the total TPS of SELECT?
I expected the SELECT result to be better than INSERT.

However,

As shown below, the total TPS (SUM TPS) hardly increased as it increased from 40 threads to 64 threads. Of course, the same goes for more threads.

The TPS (SUM TPS) of INSERT/UPDATE/DELETE increased to about 120,000 TPS. (See previous article)

tempsnip
tempsnip850×343 29.2 KB

Note:

  • Total partitions: 72 (24 x 3 hosts)

Thanks in advance!!
ChaeYoung.

7

Hi avernon,

I am attaching the file you mentioned in the link below.
https://drive.google.com/drive/folders/1A5Tp6B-3yBc8oun0KF1Vmympxxpyp5O3?usp=sharing

Thanks.
ChaeYoung.

8

I misunderstood your original message, but now I see that you have 72 database partitions and 72 CPU cores among your leaves, which makes your results more puzzling. The profile you sent for your SELECT looks correct: it is doing an IndexSeek for the WHERE clause and doing a Gather partitions:single.

Would you please open a ticket with MemSQL Support so they can do an investigation? https://support.memsql.com

9

Hi avernon,

This is just a test for proposing MemSQL to one of our customer.
So please understand that it is not easy to issue the service ticket at the moment.
I think, the point I’m asking seems to be a problem from MemSQL itself, can you find the root cause of the low performance of SELECT from what I provide?

Regards
ChaeYoung

10

What client did you use? Are all the rows from the SELECTs returning to a single client?

Unlike other DML statements, SELECT needs to return rows to the client, so that could become the bottleneck under high concurrency.