Shulou(Shulou.com)05/31 Report--

This article focuses on "stress testing of character functions in MySQL". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn the stress test of character function in MySQL.

There are so many string processing functions in MySQL that when I was sorting out this part of the content, it lit up in front of my eyes, and I had a feeling that I had entered the Grand View Garden. Oh, there was this function, oh, it could be realized like this, why didn't I think of it before, and so on.

For example, the functions of the string lookup function and the three instr,locate,position functions are very similar. If you want to implement a function, you can use Instr,locate,position to find the starting position of the string bar from the string foobarbar.

SELECT INSTR ('foobarbar',' bar')

SELECT LOCATE ('bar',' foobarbar')

SELECT POSITION ('bar' IN' foobarbar')

The expression of the main syntax is different, of course, there are still some differences in parameter settings.

For the above three functions, I am a little confused, which one should I use? Which one do you recommend? I think we can get a preliminary conclusion through two testing methods, the first is the performance of multithreaded calls under high concurrency, and the second is the performance of single-threaded execution. If you are superior in the comparison test, is there any reason not to recommend it?

To achieve these two functions, MySQL missing provides a toolset, the first of which is the performance of concurrent execution, which can be tested using mysqlslap, which comes with MySQL.

The second single-threaded stress test can be realized by using MySQL's very characteristic function benchmark.

If you use myslap for stress testing, the statement using mysqlslap is similar to the following.

Mysqlslap-concurrency=50100-create-schema= "test"-query= "SELECT POSITION ('bar' in' foobar');"-number-of-queries=50000

Of course, here we make it more difficult. One is that the concatenation of strings is more complex. We can use the string function repeat to get a very long string, such as concat (concat (repeat ('abc',500),' foobarbar'), repeat ('abc',500)) to get a very long string.

The test results obtained in this way are relatively more meaningful.

We increased the frequency of calls to 1 million, using concurrency of 50 and 100 for testing.

The result of position function

Benchmark

Average number of seconds to run all queries: 34.789seconds

Minimum number of seconds to run all queries: 34.789 seconds

Maximum number of seconds to run all queries: 34.789 seconds

Number of clients running queries: 50

Average number of queries per client: 20000

Benchmark

Average number of seconds to run all queries: 35.312 seconds

Minimum number of seconds to run all queries: 35.312 seconds

Maximum number of seconds to run all queries: 35.312 seconds

Number of clients running queries: 100

Average number of queries per client: 10000

You can see that in the case of concurrency of 50 and 100, 100 takes a little longer to call.

The results obtained with locate and Instr are similar, both between 36 seconds and 37 seconds.

From 1 million tests, we can draw a preliminary udev conclusion, that is, in this scenario, the performance of position is quite better.

And what about the stress test of a single thread? we use benchmark to simulate.

For example, the function of pressure test md5 is in such a format.

Select benchmark (500000000, md5 ('test'))

For position and locate,instr, you just need to adjust the function, and we'll specify it 1 million times.

The performance result of position,locate,instr is

1 row in set (8.23 sec)

1 row in set (8.21 sec)

1 row in set (8.23 sec)

You can see that the performance is almost the same, with locate slightly higher.

From this test, we can also see the obvious performance difference, single-thread pressure test is more than 8 seconds, but the concurrent time is about 36 seconds, this difference is actually very big.

At this point, I believe you have a deeper understanding of "stress testing of character functions in MySQL". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.