A comparative test is conducted on the sphtracing and MySQL queries on the same machine. There are a total of 6212249 call history records. query the records of a seat from to. The SQL statement is as follows: select SQL _no_cache ID from t_toll_universal where begintime> = '2017-01-01 'and begintime <'2017-03-01' and agentid = 'xxxx
@ Infobird.cn'
And calldirection = 1 Note: The agentid field in the call history table has an index. Two groups of experiments were conducted. The first group was 100 concurrent requests and 10000 requests. The second group is 10000 requests from a single process.
I. Single process, 10000 request results
1.1. MySQLTime taken for tests: 139.393 secondsrequests per second: 71.74 [#/sec] (mean) time per request: 13.939 [MS] (mean) percentage of the requests served within a certain time (MS) 50% 14 66% 14 75% 14 80% 14 90% 14 95% 14 98% 14 99% 14 100% 16 (longest request)
1.2. SphinxTime taken for tests: 60.452 secondsrequests per second: 165.42 [#/sec] time per request: 6.045 [MS] Percentage of the requests served within a certain time (MS) 50% 6 66% 6 75% 6 80% 6 90% 6 95% 6 98% 6 99% 7 100% 7 (longest request)
2. 100 concurrent requests and 10000 data results
2.1. MySQL:Time taken for tests: 103.656 seconds // total duration requests per second: 96.47 [#/sec] (mean) // number of requests completed per second time per request: 10.366 [MS] // average duration of each request (MS) Percentage of the requests served within a certain time (MS) // requests with a long time distribution of 50% 743 50% // 66% requests completed within 743ms 927 75% 1066 80% 1227 90% 2151 95% 3665 98% 5047 99% 7054 100% 19609 (longest request)
2.2. Sphinx:Time taken for tests: 59.788 secondsrequests per second: 167.26 [#/sec] (mean) time per request: 5.979 [MS] Percentage of the requests served within a certain time (MS) 50% 26 66% 28 75% 31 80% 244 90% 355 95% 3022 98% 3169 99% 9026 100% 45253 (longest request)
Iii. Result AnalysisBefore the analysis, I would like to declare that the machine used for this test is basically a discarded laptop, so the absolute value of the data is basically meaningless. Only the performance of MySQL and sphtracing in data query is analyzed here.
3.1 Test limitationsA. Due to hardware restrictions, the performance of MySQL or Sphinx may not be fully utilized. B. No statistics on the consumption of system resources during task execution. C. Use a wireless LAN, to reduce the impact of the network on the test, the request-initiating tool AB, sph133, and MySQL are deployed on the same machine.
3.2 simple analysis of resultsA. Whether it is single-process query or multi-process query, sphenders are faster than MySQL.
2
B. Compared with single-process queries and multi-process queries, Sphinx performs more smoothly. C. Compares the Time Distribution consumed by requests. In the case of concurrency, sphinx has 90% of requests completed in 35 ms, while MySQL is 2151 ms, with a difference of nearly 7 times.
4. ConclusionWhen retrieving millions of record-level data, the use of sphenders for queries is much better than the direct use of MySQL.
