Practical sysbench
This is a modal window.
The media could not be loaded, either because the server or network failed or because the format is not supported.
Formal Metadata
| Title |
| |
| Subtitle |
| |
| Title of Series | ||
| Number of Parts | 199 | |
| Author | ||
| License | CC Attribution 2.0 Belgium: You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor. | |
| Identifiers | 10.5446/32610 (DOI) | |
| Publisher | ||
| Release Date | ||
| Language |
Content Metadata
| Subject Area | ||
| Genre | ||
| Abstract |
|
FOSDEM 201474 / 199
2
3
10
11
12
13
14
17
18
19
23
24
25
27
29
45
46
47
48
49
50
51
52
55
56
57
58
65
67
68
70
72
74
75
76
77
78
79
81
82
84
86
87
89
90
93
94
100
101
102
103
104
106
107
109
110
111
112
113
114
115
119
122
123
124
126
127
128
129
137
139
141
143
145
147
150
154
155
158
161
163
164
165
166
168
171
174
175
176
179
182
183
185
188
190
191
192
194
195
196
00:00
WorkloadComputer fileBenchmarkSimulationBitoutputGraph (mathematics)FeedbackCondensationInformation technology consultingMultiplication signSoftware testingProcess (computing)Cartesian coordinate systemPhysical systemPairwise comparisonNumberSet (mathematics)Renewal theoryOrder (biology)Direction (geometry)Musical ensembleFormal languageComputer animation
02:43
Multiplication signEvoluteElectronic mailing listFrequencyTerm (mathematics)Sheaf (mathematics)Basis <Mathematik>Event horizonMachine visionForm (programming)WebsiteSummierbarkeitMereologyProcess (computing)MathematicsWordAreaDataflowQuicksortCASE <Informatik>ResultantState of matterRule of inferenceBitStudent's t-testArithmetic meanIntegrated development environmentInformationDirection (geometry)Disk read-and-write headGroup actionRight angleSocial classDivision (mathematics)Self-organizationLine (geometry)PlanningExecution unitDecision theoryConnected spaceForcing (mathematics)Insertion loss1 (number)Water vaporContext awarenessLikelihood functionCellular automatonEndliche ModelltheorieWave packetPhysical lawDependent and independent variablesNumberMaxima and minimaVariancePhysical systemOperator (mathematics)Complete metric spaceKernel (computing)SynchronizationReading (process)2 (number)Graph (mathematics)Thread (computing)Data storage deviceFunction (mathematics)BenchmarkCore dumpParameter (computer programming)LaptopPlotterComputer fileRandom number generationTotal S.A.Point (geometry)Slide ruleResponse time (technology)Formal languageClient (computing)DatabaseTable (information)Degree (graph theory)Bus (computing)Mathematical optimizationScripting languageLevel (video gaming)Server (computing)Interpreter (computing)Error messageWritingIterationoutputDifferent (Kate Ryan album)Characteristic polynomialBlock (periodic table)LengthConsistencySoftware testingSemiconductor memoryCommunications protocolGraph coloringAlpha (investment)Declarative programmingObject-oriented programmingInterior (topology)Limit (category theory)DivisorMixed realityDatabase transactionSingle-precision floating-point formatProduct (business)Metric systemReplication (computing)Natural numberBoundary value problemRandomizationLocal ringLoginRange (statistics)PhysicalismSequenceComputer animation
Transcript: English(auto-generated)
00:00
So let's start. Hello, everyone. My name is Peter Borosz, and I work as a consultant at Percona. And we will talk about Sysbench and benchmarking and a bit about benchmarking in general.
00:20
This talk is beginner level, so you would benefit the most from this if you have ever seen Sysbench or did very little stuff with it. This is mostly based on my experience that when I have people about benchmarks, that's what was useful for them, and it's kind of condensed into this talk.
00:45
So we're going to talk about benchmarking in general. We will talk about benchmarking iOS subsystems with Sysbench. We will talk about benchmarking MySQL with Sysbench. And I will give you some tips on how did I produce
01:01
the graphs I used, because last time I gave this talk, this was the first question. So what is a benchmark? A benchmark is a synthetic workload. And it doesn't really represent a real-life workload, but it is really good for comparison.
01:21
And you can measure your system's benchmark, or you can simulate your actual workload. But the actual workload, the actual workload, simulation of the actual workload is a lot less predictable than the benchmark.
01:40
But the benchmark is not necessarily close to the real workload of your application. So it's like with everything in IT, you can choose if your testing method will correspond this way or the other way. Or you can put it better that an engineer's job is to meet the right compromises.
02:04
Sysbench is pretty easy to use. I recommend to always use Frank, 0.5 Sysbench. This is how you can combine it. Frank creates nice packages for Frank and Valora out of it. I always use his stuff, almost.
02:23
So let's do a five-level benchmark. This is how you create a set of files for five-level. We create 32 bits of files here. The number of files are 32, so each file is one bit. And the feedback command will take the files
02:42
which the tests will be performed on. And the benchmarks which either this counts or counts were made on my laptop, so they are readily reproducible. All this was benchmarking my laptop's SSD. So there are some parameters which we were using in the benchmark.
03:01
First is the block size. We were using 16-bit because that's the only big size. We were using the total size of files and the number of files. The number of files can be important because some file systems, like .exe and .exe4, have a lot of problems on writing the inode tables.
03:22
So if you do it with only one file, you make it. And the database is usually more files as well. We will use direct file. We will initialize the random numbers, the single random numbers at the beginning of the benchmark.
03:41
So if we wanted a bottleneck or random number generation, we will vary the number of threads. We will test synchronous and asynchronous IO. And we will test random reads, threads, and random reads, threads in mix. OK, we will also use the next request parameter
04:03
so we don't limit the benchmark based on requests the next time parameter. And we will capture the metrics every second. So this is how the output looks like. This is a write test.
04:22
This here shows you the sample, reads throughput, threads throughput, and I think the response time for the needed operation. And the response time is 95% the response time. And when I said that we vary the number of threads,
04:43
does this actually matter, right? We are benchmarking the rest of SSD. A lot of SSD can do 1000 IOPs or 2000 or whatever. It's a parameter of the SSD. Do we have to bother with threads?
05:02
Yes, probably. The core concept for this is independent, which happens to be the core concept for 95% of data is related to questions. So, in case of synchronous IO, our first test, we will wait until the IO request completes
05:24
and only then have the result. In case of asynchronous IO, we don't wait until the completion of the IO request, but rather check back later and see how it goes. So in case of asynchronous IO,
05:42
adding more threads or removing them doesn't matter that much, because we are not doing this in this. In case of synchronous IO, the number of threads we will use matter a lot. You will see it from benchmark results.
06:00
I like to show stuff live, so I wrote a little script, which just does the final work.
06:23
So my laptop can do 44 banks in this task. If we check the demo, then we see that this is synchronous IO on a single cell.
06:41
Does it matter if I change the block size here, right? I have an SSD in this laptop, so if I did smaller or larger blocks, will it matter? To put the question differently, is sequential or random IO an immediate difference on SSD?
07:01
We are so lucky that I have this laptop here, just so we can think. If I set the block size to, let's say, 1 man, it will be a lot faster. So the nature of IO,
07:22
if it's sequential or random, still matters on flex storage, but not as much as on spindles, just the boundaries are in different places. I just changed the block size to 1K, and for 1K blocks,
07:40
performance will start. It will be slower and slower if I lower the block size, but if I lower it under 4K, which is the physical block size of the device, here I never write 1K, I always write 4K at the physical level. So this is why,
08:01
if the block size reaches a certain size, then it will get a lot more slow. So this is a graph of the line throughput of my laptop in case of AsyncOSIO,
08:23
and we decided that the number of strands doesn't matter. And this is a graph from the response time in milliseconds. And let's stop a bit here. Is it that good? Why not?
08:41
Because otherwise I wouldn't ask, right? So what is not visible from this graph is that the response time is between 10 and 20 milliseconds. But is it more like 10? Is it more like 20? What's the distribution?
09:03
That's why I like jitter plots, because from this plot we will see that 10 is more frequent, because we have more frequent samples, and we can see that it's more like an either-or relationship, that sometimes it's 20,
09:22
but more or less it's 10. And the other good answer is that can we measure the response time accurately in case of AsyncOSIO? And we can't, because we don't wait for the completion of the actual IO,
09:43
we will check back later if the IO operation is completed, and we can record the time when we get the results from that check, but at that time the IO operation may be completed for a while.
10:01
So this is again the write throughput, and here it is visible that the read throughput of my SSD is much higher. So it can read much faster than it can write. And we can also see that the read performance is consistent.
10:24
From the jitter plots I like to use alpha channels for the individual dots, so if they plot it on top of each other it will show a sharper color. And here you can see the read response time.
10:42
Since then you can also do mixed throughput, and this is one rookie error that usually people make, that OK, my storage can do 400 lengths of reads, so it will be OK. But as soon as you mix them,
11:01
the read performance, the characteristics of read performance will change. So if I do reads and writes at the same time, I'm not able to do 400 reads throughput anymore. So it seems, and here is the response time of this graph,
11:22
and the red one is the read, the read throughput, and the blue one is the write. OK, so in last November I started a MySQL instance, and it wrote that it is using Linux native asynchronous IO.
11:44
And I told you at the beginning that we will discuss results for synchronous IO, so do we have to do it? At all or we can stop here? Because it doesn't matter. It does matter.
12:00
Asynchronous IO is used for check pointing, but for example the redo logs are written synchronously. And this is a synchronous write throughput, and this graph doesn't have time in the x-axis, but rather the factor of the number of threads. And if you check this one,
12:23
so the write throughput at a single thread, why is this important? In MySQL 5.5 and in 5.6 if you have a single schema, because replication is single-threaded, right? Which means that replication can't go faster than this.
12:44
Why? The throughput of my storage at 16 megs is a lot better. 16 threads is a lot better. CPU?
13:02
2. But the benchmark is not CPU-intensive. So I use this many threads, but this laptop has 2 CPU cores.
13:21
So if you graph the response time, you can see that up to a point, up to 16 threads, the response time somewhat increases, but after a while, if I keep adding threads, the throughput doesn't change too much, but the response time will skyrocket, right?
13:42
So, and this is another point to make, that every system has an optimal degree of parallelism, and if you go over that, then you will get whatever the response time is. And this is why it is a bad idea to set max connections from MySQL to 10-20,000
14:03
or something like that, because if you start using all of them, you don't think this will happen. Similarly for leads, so if I use more threads, the leads throughput will increase, but again, similarly,
14:21
the response time will just go up after a while. And this is for leads. So similarly, my lead performance in isolation was pretty good, but if I do this benchmark, I won't be able to do 400 megs for leads anymore,
14:45
and the response time is pretty similar, so after a while, the throughput doesn't grow, but the response time starts to grow exponentially.
15:01
Ok, so, so far this was FileIO, benchmarking your IOS subsystem, and now let's talk about benchmarking MySQL. Here is how you can prepare a test database, so you create a schema,
15:20
and use the schema and use the parallel declare.lua script to create your test database in parallel. And here is how you do one benchmark iteration. One more thing with this is,
15:43
usually you write scripts like this, that for all test modes, for all IOS modes, for all thread configurations, you do the benchmark and at the end you will aggregate it. Similarly, this is how a typical OLTP benchmark looks like,
16:06
that you write a number of threads, and you measure the threads, if you want to write another parameter, then you can do it as well, so I recommend you write basic scripts like this.
16:22
I think the most well-known benchmark in systench is OLTP, it has some parameters like the table size, and it has some parameters for experience as well, like what should the int size be, how many point selects should be in a transaction, how many simple edges should be in a transaction.
16:44
This is by the way in systench in common.ua, so you can find the definition in common.ua. And for database benchmark, I chose to examine the workload, now it reconnects pretty frequently.
17:03
So we won't do the OLTP benchmark now, but I would like to show you, how can you use systench to reproduce an issue which you may see in the production environment, and in kind of a sample, by modifying the Lua.
17:26
So let's edit, let's examine through my systench, let's examine the updateIndex.lua,
17:41
which we will use for this, and I modified this script a bit, that in event function, I do a DB disconnect, which means that after every transaction, systench will disconnect from the database. So what do I simulate here? Let's say a PHP application,
18:01
where the PHP interpreter only runs while the HTTP request is alive, and at the time it will reconnect to my SQL. So, if I do the benchmark without the disconnect,
18:22
then it will be OK. So, this is the throughput again, for updating by single language on 32 threads. Here the tables used in the benchmarks are,
18:42
the benchmark is rather small, so all of this is in memory. But, if I add the disconnect back, and do it again, then for a while it will be kind of OK.
19:04
You can also see that in MySQL, the connections are cheap, so it doesn't matter that much, if you are reconnecting or not, it matters a little. And now MySQL sucks, right?
19:23
Oh, it sucks again. And again. What happened? MySQL is not running? Yes, it is running. Oops. It sucks again. It's here. What happened here?
19:48
No? Also, it's in time wait. Yes. So, the issue here is that when you close a MySQL connection,
20:01
the protocol level, the MySQL protocol level doesn't close the TCP IP level connection. So, if I check, also it's in time wait, and here I'm actually out of the local
20:23
for TCP IP. How can I fix that? I can limit the number of connections the number of connections at the kernel level that can be in time wait.
20:51
If I limit this, then I can't have more than a thousand connections in time wait. The kernel will simply kill it.
21:01
And my benchmark is fixed now. And this will end forever. Because I won't go out of the local port range anymore. So, if we do this again,
21:27
we keep roughly a thousand because the rest is killed by the kernel. If we check what happens here, no, it's not here, but here if your traffic is high enough, then the kernel can complain
21:41
that if you had TCP IP connections, I tried to break MySQL connections with that and I wasn't successful. So, looks like these TCP IP connections are there for like no reason. When you close the MySQL level connection,
22:01
the server doesn't initiate the TCP IP level connection, but it is initiated by the client later, so the connection will be reused. I don't know if it's probably it would be better for the server to initiate the TCP IP connection close when
22:22
the client thread is closed. Okay.
22:43
Which I almost forgot. My colleagues and friends from Belgium are organizing the community dinner and they asked me to show these slides. I haven't seen them myself, so I'll check it out with you and improvise. This is the first one. It has some nice
23:02
logos on it. I guess that they are the companies who are sponsors, right? Yes, okay. In this slide there are some text and the map. The text says which bus should you take to get to the community dinner. So this is kind of a useful information part.
23:26
And here is bus and table. So you don't have to send it away.