Nodejs sequelize (MariaDB) 速度比較

4 months ago

資料庫效能測試mysqlslap

mysqlslap –user=root — query=’use PRESURE_DB;select * from test where id=5000’ — number-of-queries=10000 — create-schema=PRESURE_DB;
mysqlslap –user=root — query=’use PRESURE_DB;insert into test(field1) values(’t’)’ — number-of-queries=20000 — create-schema=PRESURE_DB;
mysqlslap –user=root — query=’use PRESURE_DB;update test set field1=’r’’ — number-of-queries=1 — create-schema=PRESURE_DB;

1. Insert data, Sequelize (function: Model.create), mysqlslap (insert) Table test(field1) , field1 type: char Data test(’t’)

Number of clients running queries: 1
Average number of queries per client: 1,10,100,1000,10000

1 time Sequelize : 0.004 s (function: Model.create)

mysqlslap: <=0.001 s

10time Sequelize : 0.009 s

mysqlslap: <=0.001 s

100time Sequelize : 0.025~0.038 s

mysqlslap : 0.004 s

1000time Sequelize : 0.100~0.138 s

mysqlslap : 0.032 s

10000time Sequelize : 0.5~0.578 s

mysqlslap : 0.328 s

1. Modify data, Sequelize (function: Model.set), mysqlslap (insert) Table test(field1) , field1 type: char Data test(’t’)

Number of clients running queries: 1
Average number of queries per client: 1,10,100,1000,10000

1 time Sequelize : 0.003 s (function: Model.create)

mysqlslap: <=0.001 s

10time Sequelize : 0.007~0.009 s

mysqlslap: <=0.001 s

100time Sequelize : 0.025 s

mysqlslap : 0.004 s

1000time Sequelize : 0.104~0.108 s

mysqlslap : 0.034~0.037 s

10000time Sequelize : 0.5~0.606 s

mysqlslap : 0.331~0.344 s

1. Search data, Sequelize (function: Model.findById), mysqlslap (select ) Table test(field1) , field1 type: char Data test(’t’)

Number of clients running queries: 1
Average number of queries per client: 1,10,100,1000,10000

1 time Sequelize : 0.004 s (function: Model.create)

mysqlslap: <=0.001 s

10time Sequelize : 0.017 s

mysqlslap: 0.001 s

100time Sequelize : 0.126~0.136 s

mysqlslap : 0.007 s

1000time Sequelize : 0.768~0.811 s

mysqlslap : 0.059~0.061 s

10000time Sequelize : 5.789~6.083 s

mysqlslap : 0.581~0.603 s

1. 本次測量了新增、修改、查詢功能。
2. 本次測驗sequelize API邏輯部分是以最直觀方式寫法去實作，代表並非是最佳效率寫法，目的為若是以api最差效能來對比mysqlslap觀察差異度。
3. 結論：效率差異最大的為查詢功能，findbyId 、find、findAll類似的sequelize api是效率很差的，網路上指出若需要查詢資料，則使用query自訂搜尋，效率是最好的，另外單筆多項一次輸入查詢比起多筆多項個別查詢效率好非常多，此為需要注意部分，套用ORM其中有許多便利的優點但也需要注意其效能問題。

Author : Eric Hong (eric@appmaster.cc)