This page presents a real-time data import benchmark for AnalyticDB for MySQL, using the TPC-H dataset. The results show how write throughput (TPS) scales with concurrent threads across two deployment types: Alibaba Cloud public cloud and physical servers.
AnalyticDB for MySQL uses a single INSERT INTO statement per batch over JDBC. Elasticsearch uses the Python Elasticsearch client with es.bulk(). Results reflect these specific methods and configurations — not bulk load or other import modes.
Test configuration
Services under test
| Service | Specification |
|---|---|
| AnalyticDB for MySQL 3.0 (Alibaba Cloud public cloud) | Elastic mode, Cluster Edition, 1 worker node (24 cores) |
| AnalyticDB for MySQL 3.0 (physical servers) | 3 physical servers, each with 32 vCPUs, 128 GiB memory, 3.84 TB SSD, 960 GB SSD, and twelve 8 TB HDDs |
| Elasticsearch 6.7.0 | Standard Edition, 1 node (24 cores) |
Client environment
| Component | Configuration |
|---|---|
| Elastic Compute Service (ECS) | 2 instances, 32 vCPUs each, 128 GiB memory, 3,576 GiB local NVMe SSD |
Deploy ECS instances in the same zone as the AnalyticDB for MySQL and Elasticsearch clusters, with sufficient bandwidth between them.
Dataset
All tests use the TPC-H dataset. For dataset details, see the TPC-H official website.
Test methods
Each service uses a multi-threaded client that reads TPC-H fragment files and imports data in batches of 2,000 rows.
AnalyticDB for MySQL (Alibaba Cloud public cloud): A Java program reads multiple local TPC-H fragment files and imports data via JDBC.
INSERT INTO lineitem values (...)AnalyticDB for MySQL (physical servers): Same method as above. Each row is 350 bits. Import results are queryable within 1 second.
INSERT INTO lineitem values (...)Elasticsearch: A Python program reads TPC-H fragment files and imports data in bulk via the Elasticsearch client.
Results
AnalyticDB for MySQL TPS increases with concurrent threads, while Elasticsearch TPS drops under higher concurrency.
| Concurrent threads | AnalyticDB for MySQL (public cloud) TPS | AnalyticDB for MySQL (physical servers) TPS | Elasticsearch TPS |
|---|---|---|---|
| 8 | 33,033 | 120,192 | 12,211 |
| 16 | 56,816 | 218,472 | 7,165 |
| 32 | 95,083 | 398,087 | 6,267 |
| 64 | 153,857 | 643,618 | 5,890 |
| 128 | 186,732 | 787,572 | 5,516 |
Table creation statements
AnalyticDB for MySQL
CREATE TABLE `lineitem` (
`l_orderkey` bigint NOT NULL COMMENT '',
`l_partkey` int NOT NULL COMMENT '',
`l_suppkey` int NOT NULL COMMENT '',
`l_linenumber` int NOT NULL COMMENT '',
`l_quantity` decimal(15, 2) NOT NULL COMMENT '',
`l_extendedprice` decimal(15, 2) NOT NULL COMMENT '',
`l_discount` decimal(15, 2) NOT NULL COMMENT '',
`l_tax` decimal(15, 2) NOT NULL COMMENT '',
`l_returnflag` varchar NOT NULL COMMENT '',
`l_linestatus` varchar NOT NULL COMMENT '',
`l_shipdate` date NOT NULL COMMENT '',
`l_commitdate` date NOT NULL COMMENT '',
`l_receiptdate` date NOT NULL COMMENT '',
`l_shipinstruct` varchar NOT NULL COMMENT '',
`l_shipmode` varchar NOT NULL COMMENT '',
`l_comment` varchar NOT NULL COMMENT ''
PRIMARY KEY(l_orderkey)
) DISTRIBUTED BY HASH(`l_orderkey`) INDEX_ALL='Y'Elasticsearch
curl -X PUT 'http://es_ip:9200/tpch' \
-H 'Content-Type: application/json' \
-d '{
"settings": {
"number_of_shards": 32,
"number_of_replicas" : 2
},
"mappings": {
"lineitem": {
"properties": {
"L_ORDERKEY": {
"type": "integer"
},
"L_PARTKEY": {
"type": "integer"
},
"L_SUPPKEY": {
"type": "integer"
},
"L_LINENUMBER": {
"type": "integer"
},
"L_QUANTITY": {
"type": "double"
},
"L_EXTENDEDPRICE": {
"type": "double"
},
"L_DISCOUNT": {
"type": "double"
},
"L_TAX": {
"type": "double"
},
"L_RETURNFLAG": {
"type": "keyword"
},
"L_LINESTATUS": {
"type": "keyword"
},
"L_SHIPDATE": {
"type": "date"
},
"L_COMMITDATE": {
"type": "date"
},
"L_RECEIPTDATE": {
"type": "date"
},
"L_SHIPINSTRUCT": {
"type": "keyword"
},
"L_SHIPMODE": {
"type": "keyword"
},
"L_COMMENT": {
"type": "keyword"
}
}
}
}
}'Elasticsearch import script
The following Python script imports data into Elasticsearch using 16 threads. Each thread reads one TPC-H fragment file (lineitem.tbl.1 through lineitem.tbl.16) and submits batches via es.bulk(), routing documents by L_ORDERKEY.
from threading import Thread
from elasticsearch import Elasticsearch
def func(i):
es = Elasticsearch(hosts=[
"es_ip:9200"
])
idx = 0
with open(r"lineitem.tbl.{}".format(i)) as f:
actions = []
while 1:
r = f.readlines(2000)
if not r:
break
for i in r:
data = i.split('|')
body = {
'L_ORDERKEY': int(data[0]),
'L_PARTKEY': int(data[1]),
'L_SUPPKEY': int(data[2]),
'L_LINENUMBER': int(data[3]),
'L_QUANTITY': float(data[4]),
'L_EXTENDEDPRICE': float(data[5]),
'L_DISCOUNT': float(data[6]),
'L_TAX': float(data[7]),
'L_RETURNFLAG': data[8],
'L_LINESTATUS': data[9],
'L_SHIPDATE': data[10],
'L_COMMITDATE': data[11],
'L_RECEIPTDATE': data[12],
'L_SHIPINSTRUCT': data[13],
'L_SHIPMODE': data[14],
'L_COMMENT': data[15]
}
actions.append({"index": {"_index": "tpch", "_type": "lineitem", "routing": int(data[0])}})
actions.append(body)
idx += 1
es.bulk(actions)
actions = []
print(idx)
if __name__ == '__main__':
for i in range(0, 16):
Thread(target=func, args=(i + 1,)).start()