Practical sysbench
Boros, Peter
This session will be about benchmarking MySQL and disk IO subsystems with sysbench and interpreting the results. In our consulting company, I helped a reasonable number of customers with sysbench so I know the common caveats most people run into. This talk will cover benchmarking IO subsystems with fileio tests, as well as benchmarking MySQL.
 So let's start. Hello, everyone. My name is Peter Boloz, and I work as a consultant at Sölkona. And we will talk about SID Bench and benchmarking and a bit about benchmarking in general. This talk is bigger than that one. So you would benefit the most from this if you have ever seen SID Bench or did you have seen that little stuff? I believe this is mostly based on my experience that when I have people about benchmark, that's what we'll do. We'll talk for them, and it's kind of fun to answer into this talk. So we were talking about benchmarking in general. We were talking about benchmarking IOS subsystems, which is benchmarking IOS. We were talking about benchmarking mySQL, which is benchmarking. And I will give you some tips on how did I produce the graphs I used, because last time I did 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 realized workload, but it is really good for comparison. And you can measure your systems in benchmark, or you can see your actual workload. But the actual workload is a lot less repeatable than the benchmark. But the benchmark is not necessarily close to the real workload for verification. So it's like with everything in IT, you can choose if you are testing methods we are selling this way or that different. So you can put it better that an engineer's job is to make you like compromises. Since much is pretty easy to use, I recommend all that you start with 0.5-cent bench. This is how you can compile it. Very nice packages for the reference Bellora of AutoFix. I always use this stuff almost. 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 I've set to 2, so each one is longer. And the prepared comment will take the files which the tests will be performed on. And the benchmarks which I have in these columns were made out of my laptop, so they are readily reproducible. All of this was benchmarking my laptop's SSD. So there are some parameters which we were using in the benchmark. First is the block size. We were using 16 things because that's the only big size. We were using the total size of files and the final number of files. The number of files can be important because some files like EXT4 have total number of files on the right and the left tables. So if you're doing it with only one file, you may hit that. And the database is usually more fine as well. We were using the direct file. We then initialized the random numbers, the single random numbers at the beginning of the benchmark. So if we wanted a bottom-to-battery number generation, we would vary the number of threads. We were testing synchronous and asynchronous style, and we would test random reads, writes, and random reads, writes in mix. OK, we will also use the next request, parameter. So we don't need 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. This here shows you the sample. Read throughput, writes throughput. I think the response time for the even operation. And the response time is 95% of the response time. And when I said that we vary the number of threads, does this actually matter, right? We have benchmarking the left of SSD. A left of SSD can do 1,000 diodes or 2,000 or whatever. It's a parameter of the SSD. Do we have to bother with threads? 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 cycle, our first test, we will wait until the IO request completes and only then have the result. In case of asynchronous cycle, we don't wait until the completion of the IO request, but rather check the creator and see how it goes. So in case of asynchronous IO, every more threads are removing them. Doesn't matter that much, because we are not doing asynchronous. In case of synchronous, the number of threads we will use matter a lot. You will see it from benchmark results. I like to show stuff live. So I wrote a little script, which just does the final IO. What? OK. Yes, yes. So my laptop can do like 44 times in this test. If we check the demo, then we see that this is synchronous IO on a single thread. Does it matter if I change the block size here? I have an SSD in this laptop. So if I read smaller or larger blocks, will it matter? To put the question differently, is sequential random IO, VH, different, here on SSD? We are so lucky that I have this laptop here, so we can find. If I set the block size to, let's say, 1 man, it will be a lot faster. So the nature of final, if it's sequential random, still matters on flash storage, but not as much as on spindles. Just the bomb, these are different places. I just changed the block size to 1k. And for 1k blocks, performance will start. It will be slower and slower if I lower the block size. But if I lower it under 14, which is the physical block size of the device, here I never write 1k. I only write 4k at the physical level. So this is why, if the block size reaches a certain quarter, a certain size, then it will get a lot more slow. So this is a graph of the night throughput of my laptop in case of H-Nikon SIO. And we decided this has the number of steps, doesn't matter. And this is a graph on the RISC-1 style in milliseconds. And let's talk a bit here. Is this that good? Why not? Because otherwise I wouldn't ask. So what is not visible from this graph, that the RISC-1 time is around between 10 and 20 milliseconds. But is it more like 10? Is it more like 20? What's the distribution? That's why I like Jitter thoughts. Because from this closely, you'll 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, but more or less it's 10. And the other good answer for this graph, is that can you measure the response time accurately in case of H-Nikon SIO? And we can't, because we don't wait for the completion of the actual I-O. We will check back later if the I-O operation is completed. And we can record the time when we get the results from the check. But at that time, the I-O operation will be completed for a while. OK. So this is again the right throughput. And here it is visible that the read throughput of my SSV is much higher. So it can be much faster than it can write. And we can also see that the read performance is consistent. For from the Jitter thoughts, I like to use Alpha channel for the individual thoughts. So if the thought is on top of each other, it will show a sharper color. And here you can see the read response time. SIFV bench can also do mixed throughput. And this is one root error that usually people make, that OK, my storage can do 400 lines of reads. So it will be OK. But as soon as you mix them, 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 lines of read throughput anymore. So it seems, and here is the response time of this graph. And the red one is the read. Read throughput and the blue one is the write. OK. So in last number, I started my SQL instance. And it was that it is using little state in asynchronous style. And I told them in the beginning that we will discuss results for synchronous style also. Do we have to do it at all? We can stop here, because it doesn't matter. It does matter. Asynchronous style is used for checkpointing, but for example, the read will also be synchronously. And this is the synchronous write throughput. And this graph doesn't have time in the x-axis, but rather the factor of the number of sets. And if you check this one, so the write throughput at the single set, why is this important? In my SQL 5.5 and in 5.6, if you have a single schema. Because application is single set, which means that application can't go faster than this. Why? The throughput of my storage at 16 megs is a lot better. 16 strats is a lot better. CPU? CPU, the work? The number of course? 2, but the benchmark is not CPU intensive. No, it means the strats. Yes, so I use this many strats, but this laptop has 2 CPU cores. Yeah, that's correct. OK. So if you graph the response time, you can see that up to a point, up to 16 strats, the response time somebody increases. But after a while, if I keep adding strats, the throughput doesn't change too much, but the response time, you have skyrocketed. 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 40 bar response times. And this is why it is a bad idea to set the max connections for my SQL to 10, 20,000, or something like that. If you start using all of them, this will happen. Similarly for these, so if I use more strats, this will put a wheel in place. But again, similarly, the response time will just go up after a while. And this is for these. So similarly, my need performance in isolation was pretty good. But if I do need a needs benchmark, I won't be able to do 400 megs for these anymore. And the response time is pretty similar. So after a while, the throughput doesn't grow, but the response time starts to grow exponentially. OK, so so far this was file IO, benchmarking your IO subsystem. And now let's talk about benchmarking my SQL. Here is how you can prepare test database. So you create a schema, and use the schema, and use the parallel department of LuaStrip to create your test database in parallel. And here is how do you do one benchmark declaration. One more thing with this is usually you write strips like this. That for all test modes, for all IO modes, for all set configurations, you do the benchmark, and at the end, you will aggregate it. Similarly for this is how a typical web data benchmark looks like, that you write a number of sets. And you measure these sets if you want to write another parameter, then you can do it as well. So I recommend you write basic reference strips like this. I think the most well known benchmark in Cysvenics or PPP, it has some parameters like the table size, and it has some parameters for experience as well. Like what should the insights be, how many points select should be in a transaction, how many simple ranges should be in a transaction. This is by the way in Cysvenics.com on the tour. So you can find the connection in Cysvenics.com on the tour. And for database benchmark, I chose to examine the work route now which reconnects pretty frequently. So we won't do the OTP benchmark now, but I would like to show you how can you use Cysvenics to use an issue which you may see in the production environment in kind of a sandbox by modifying the tour. So let's examine the update index that we were used for this. And I modified this script a bit that in the event function, I do a name disconnect, which means that after every transaction, Cysvenics will disconnect from the database. So what do I see? When it's here, let's say a PhD application where the PhD interpreter only runs while the HGTT request is alive. And every time, it really connects to my SQL. So if I do the benchmark without the reconnect, that means it will be OK. So this is the throughput trigger for updating by single time on certain two threads. Here the tables using 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 the while it will be kind of OK. You can also see that in my SQL, the connections are RG. So it doesn't matter that much if you are reconnecting or not. It matters a little. And now my SQL sucks, right? Oh, it sucks again. And again, what happened? My SQL is not running. Yes, it is running. Oops. It sucks again. It's here. What happened here? No? Also it's in time wait. Yes. So the issue here is that when you close the minus to your connection, the protocol level, the minus to your protocol level doesn't close the TCP-ID level connection. So if I check, also it will be in time wait. And here I'm actually out of the local IT and port range for TCP-ID. 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. OK. OK. If I limit this, then I can't have more than 1,000 connections in time wait. The kernel will simply kill it. And my benchmark is fixed now. And this will run forever because I won't go out of the local port range anymore. So if we do this again, we keep at roughly 1,000 because the rest is killed by the kernel. If we check what happens here, no, it's not killed. But here, if your traffic is high enough, then the kernel can complain that if you have TCP-ID connections, I try to break my SQL connections with that and I wasn't successful. So looks like these TCP-ID connections are there for nodeism. When you close the MySQL level connection, the server doesn't initiate the TCP-ID level connection. But if it is initiated by the client labor, so the connection will be used. I don't know if it would be better for the server to initiate the TCP-ID connection close when the client's thread is closed. OK. So thank you, everyone. Thank you. Thank you. Thank you. One more thing, which I almost forgot. My colleagues and friends from Belgium are organizing the community dinner. And they asked me to show this slide. I haven't seen that myself, so I have checked it out with you and they improvise. This is the first one. There you'll see. It has some nice logos on it. I guess that they are the companies who are the sponsors. Yes, OK. In this slide, there are some text and the map. And the text says, which bus should you take to get to the community dinner? So this is kind of a news-filled information part. And here is bus and table. So you don't have to send it away. You don't have to send it away.
 So let's start.  Hello, everyone.  My name is Peter Boloz, and I work as a consultant at  Sölkona.  And we will talk about SID Bench and benchmarking  and a bit about benchmarking in general.  This talk is bigger than that one.  So you would benefit the most from this  if you have ever seen SID Bench or did  you have seen that little stuff?  I believe this is mostly based on my experience that when I  have people about benchmark, that's  what we'll do.  We'll talk for them, and it's kind of fun to answer  into this talk.  So we were talking about benchmarking in general.  We were talking about benchmarking IOS subsystems,  which is benchmarking IOS.  We were talking about benchmarking mySQL, which is  benchmarking.  And I will give you some tips on how did I produce the  graphs I used, because last time I did 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  realized workload, but it is really good for comparison.  And you can measure your systems in benchmark, or you  can see your actual workload.  But the actual workload is a lot less repeatable than the  benchmark.  But the benchmark is not necessarily close to the  real workload for verification.  So it's like with everything in IT, you can choose if you  are testing methods we are selling this way or that  different.  So you can put it better that an engineer's job is to make  you like compromises.  Since much is pretty easy to use, I recommend all that you  start with 0.5-cent bench.  This is how you can compile it.  Very nice packages for the reference  Bellora of AutoFix.  I always use this stuff almost.  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 I've set to 2, so each one is longer.  And the prepared comment will take the files which the  tests will be performed on.  And the benchmarks which I have in these columns were made  out of my laptop, so they are readily reproducible.  All of this was benchmarking my laptop's SSD.  So there are some parameters which we were using in  the benchmark.  First is the block size.  We were using 16 things because that's the only big size.  We were using the total size of files and the final number of  files.  The number of files can be important because some files  like EXT4 have total number of files on the  right and the left tables.  So if you're doing it with only one file, you may hit that.  And the database is usually more fine as well.  We were using the direct file.  We then initialized the random numbers, the single  random numbers at the beginning of the benchmark.  So if we wanted a bottom-to-battery number  generation, we would vary the number of threads.  We were testing synchronous and asynchronous  style, and we would test random reads, writes, and random  reads, writes in mix.  OK, we will also use the next request, parameter.  So we don't need 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.  This here shows you the sample.  Read throughput, writes throughput.  I think the response time for the even operation.  And the response time is 95% of the response time.  And when I said that we vary the number of threads, does  this actually matter, right?  We have benchmarking the left of SSD.  A left of SSD can do 1,000 diodes or 2,000 or whatever.  It's a parameter of the SSD.  Do we have to bother with threads?  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 cycle, our first test, we  will wait until the IO request completes and only then  have the result.  In case of asynchronous cycle, we don't wait until the  completion of the IO request, but rather check the  creator and see how it goes.  So in case of asynchronous IO, every more threads are  removing them.  Doesn't matter that much, because we are not doing  asynchronous.  In case of synchronous, the number of threads we will use  matter a lot.  You will see it from benchmark results.  I like to show stuff live.  So I wrote a little script, which just does the final  IO.  What?  OK.  Yes, yes.  So my laptop can do like 44 times in this test.  If we check the demo, then we see that this is synchronous  IO on a single thread.  Does it matter if I change the block size here?  I have an SSD in this laptop.  So if I read smaller or larger blocks, will it matter?  To put the question differently, is sequential  random IO, VH, different, here on SSD?  We are so lucky that I have this laptop here, so we can  find.  If I set the block size to, let's say, 1 man, it will  be a lot faster.  So the nature of final, if it's sequential random, still  matters on flash storage, but not as much as on spindles.  Just the bomb, these are different places.  I just changed the block size to 1k.  And for 1k blocks, performance will start.  It will be slower and slower if I lower the block size.  But if I lower it under 14, which is the physical block  size of the device, here I never write 1k.  I only write 4k at the physical level.  So this is why, if the block size reaches a certain  quarter, a certain size, then it will get a lot more slow.  So this is a graph of the night throughput of my laptop  in case of H-Nikon SIO.  And we decided this has the number of steps, doesn't  matter.  And this is a graph on the RISC-1 style in milliseconds.  And let's talk a bit here.  Is this that good?  Why not?  Because otherwise I wouldn't ask.  So what is not visible from this graph, that the RISC-1  time is around between 10 and 20 milliseconds.  But is it more like 10?  Is it more like 20?  What's the distribution?  That's why I like Jitter thoughts.  Because from this closely, you'll 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, but more or less it's 10.  And the other good answer for this graph,  is that can you measure the response time accurately  in case of H-Nikon SIO?  And we can't, because we don't wait for the completion  of the actual I-O. We will check back later  if the I-O operation is completed.  And we can record the time when we get the results  from the check.  But at that time, the I-O operation  will be completed for a while.  OK.  So this is again the right throughput.  And here it is visible that the read throughput of my SSV  is much higher.  So it can be much faster than it can write.  And we can also see that the read performance  is consistent.  For from the Jitter thoughts, I like  to use Alpha channel for the individual thoughts.  So if the thought is on top of each other,  it will show a sharper color.  And here you can see the read response time.  SIFV bench can also do mixed throughput.  And this is one root error that usually people make,  that OK, my storage can do 400 lines of reads.  So it will be OK.  But as soon as you mix them, 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 lines of read throughput anymore.  So it seems, and here is the response time of this graph.  And the red one is the read.  Read throughput and the blue one is the write.  OK.  So in last number, I started my SQL instance.  And it was that it is using little state in asynchronous style.  And I told them in the beginning that we  will discuss results for synchronous style also.  Do we have to do it at all?  We can stop here, because it doesn't matter.  It does matter.  Asynchronous style is used for checkpointing,  but for example, the read will also be synchronously.  And this is the synchronous write throughput.  And this graph doesn't have time in the x-axis,  but rather the factor of the number of sets.  And if you check this one, so the write throughput  at the single set, why is this important?  In my SQL 5.5 and in 5.6, if you have a single schema.  Because application is single set,  which means that application can't go faster than this.  Why?  The throughput of my storage at 16 megs is a lot better.  16 strats is a lot better.  CPU?  CPU, the work?  The number of course?  2, but the benchmark is not CPU intensive.  No, it means the strats.  Yes, so I use this many strats, but this laptop has 2 CPU  cores.  Yeah, that's correct.  OK.  So if you graph the response time,  you can see that up to a point, up to 16 strats,  the response time somebody increases.  But after a while, if I keep adding strats,  the throughput doesn't change too much,  but the response time, you have skyrocketed.  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 40 bar response times.  And this is why it is a bad idea to set  the max connections for my SQL to 10, 20,000, or something  like that.  If you start using all of them, this will happen.  Similarly for these, so if I use more strats,  this will put a wheel in place.  But again, similarly, the response time will just  go up after a while.  And this is for these.  So similarly, my need performance in isolation  was pretty good.  But if I do need a needs benchmark,  I won't be able to do 400 megs for these anymore.  And the response time is pretty similar.  So after a while, the throughput doesn't grow,  but the response time starts to grow exponentially.  OK, so so far this was file IO, benchmarking  your IO subsystem.  And now let's talk about benchmarking my SQL.  Here is how you can prepare test database.  So you create a schema, and use the schema,  and use the parallel department of LuaStrip  to create your test database in parallel.  And here is how do you do one benchmark declaration.  One more thing with this is usually you write  strips like this.  That for all test modes, for all IO modes,  for all set configurations, you do the benchmark,  and at the end, you will aggregate it.  Similarly for this is how a typical web  data benchmark looks like, that you write a number of sets.  And you measure these sets if you want to write another parameter,  then you can do it as well.  So I recommend you write basic reference strips like this.  I think the most well known benchmark in Cysvenics or PPP,  it has some parameters like the table size,  and it has some parameters for experience as well.  Like what should the insights be, how many points  select should be in a transaction,  how many simple ranges should be in a transaction.  This is by the way in Cysvenics.com on the tour.  So you can find the connection in Cysvenics.com on the tour.  And for database benchmark, I chose to examine the work  route now which reconnects pretty frequently.  So we won't do the OTP benchmark now,  but I would like to show you how can you  use Cysvenics to use an issue which  you may see in the production environment  in kind of a sandbox by modifying the tour.  So let's examine the update index  that we were used for this.  And I modified this script a bit that in the event function,  I do a name disconnect, which means that after every transaction,  Cysvenics will disconnect from the database.  So what do I see?  When it's here, let's say a PhD application  where the PhD interpreter only runs  while the HGTT request is alive.  And every time, it really connects to my SQL.  So if I do the benchmark without the reconnect,  that means it will be OK.  So this is the throughput trigger for updating  by single time on certain two threads.  Here the tables using 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 the while it will be kind of OK.  You can also see that in my SQL, the connections are RG.  So it doesn't matter that much if you are reconnecting or not.  It matters a little.  And now my SQL sucks, right?  Oh, it sucks again.  And again, what happened?  My SQL is not running.  Yes, it is running.  Oops.  It sucks again.  It's here.  What happened here?  No?  Also it's in time wait.  Yes.  So the issue here is that when you close the minus  to your connection, the protocol level,  the minus to your protocol level  doesn't close the TCP-ID level connection.  So if I check, also it will be in time wait.  And here I'm actually out of the local IT and port range  for TCP-ID.  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.  OK.  OK.  If I limit this, then I can't have more than 1,000  connections in time wait.  The kernel will simply kill it.  And my benchmark is fixed now.  And this will run forever because I  won't go out of the local port range anymore.  So if we do this again, we keep at roughly 1,000  because the rest is killed by the kernel.  If we check what happens here, no, it's not killed.  But here, if your traffic is high enough,  then the kernel can complain that if you have TCP-ID  connections, I try to break my SQL connections with that  and I wasn't successful.  So looks like these TCP-ID connections  are there for nodeism.  When you close the MySQL level connection,  the server doesn't initiate the TCP-ID level connection.  But if it is initiated by the client labor,  so the connection will be used.  I don't know if it would be better for the server  to initiate the TCP-ID connection  close when the client's thread is closed.  OK.  So thank you, everyone.  Thank you.  Thank you.  Thank you.  One more thing, which I almost forgot.  My colleagues and friends from Belgium  are organizing the community dinner.  And they asked me to show this slide.  I haven't seen that myself, so I have checked it out with you  and they improvise.  This is the first one.  There you'll see.  It has some nice logos on it.  I guess that they are the companies who are the sponsors.  Yes, OK.  In this slide, there are some text and the map.  And the text says, which bus should you take  to get to the community dinner?  So this is kind of a news-filled information part.  And here is bus and table.  So you don't have to send it away.  You don't have to send it away.
_FOSDEM_2014__Practical_sysbench_A5pj9fjFqjM_0_359.png _FOSDEM_2014__Practical_sysbench_A5pj9fjFqjM_360_635.png _FOSDEM_2014__Practical_sysbench_A5pj9fjFqjM_636_683.png _FOSDEM_2014__Practical_sysbench_A5pj9fjFqjM_684_1067.png _FOSDEM_2014__Practical_sysbench_A5pj9fjFqjM_1068_1187.png _FOSDEM_2014__Practical_sysbench_A5pj9fjFqjM_1188_3803.png _FOSDEM_2014__Practical_sysbench_A5pj9fjFqjM_3804_4727.png _FOSDEM_2014__Practical_sysbench_A5pj9fjFqjM_4728_5615.png _FOSDEM_2014__Practical_sysbench_A5pj9fjFqjM_5616_5903.png _FOSDEM_2014__Practical_sysbench_A5pj9fjFqjM_5904_6851.png _FOSDEM_2014__Practical_sysbench_A5pj9fjFqjM_6852_7403.png _FOSDEM_2014__Practical_sysbench_A5pj9fjFqjM_7404_7739.png _FOSDEM_2014__Practical_sysbench_A5pj9fjFqjM_7740_9059.png _FOSDEM_2014__Practical_sysbench_A5pj9fjFqjM_9060_9191.png _FOSDEM_2014__Practical_sysbench_A5pj9fjFqjM_9192_9323.png _FOSDEM_2014__Practical_sysbench_A5pj9fjFqjM_9324_9371.png _FOSDEM_2014__Practical_sysbench_A5pj9fjFqjM_9372_9419.png _FOSDEM_2014__Practical_sysbench_A5pj9fjFqjM_9420_9539.png _FOSDEM_2014__Practical_sysbench_A5pj9fjFqjM_9540_9731.png _FOSDEM_2014__Practical_sysbench_A5pj9fjFqjM_9732_9791.png _FOSDEM_2014__Practical_sysbench_A5pj9fjFqjM_9792_10535.png _FOSDEM_2014__Practical_sysbench_A5pj9fjFqjM_10536_10595.png _FOSDEM_2014__Practical_sysbench_A5pj9fjFqjM_10596_10655.png _FOSDEM_2014__Practical_sysbench_A5pj9fjFqjM_10656_11807.png _FOSDEM_2014__Practical_sysbench_A5pj9fjFqjM_11808_11879.png _FOSDEM_2014__Practical_sysbench_A5pj9fjFqjM_11880_12023.png _FOSDEM_2014__Practical_sysbench_A5pj9fjFqjM_12024_12083.png _FOSDEM_2014__Practical_sysbench_A5pj9fjFqjM_12084_12131.png _FOSDEM_2014__Practical_sysbench_A5pj9fjFqjM_12132_12191.png _FOSDEM_2014__Practical_sysbench_A5pj9fjFqjM_12192_12527.png _FOSDEM_2014__Practical_sysbench_A5pj9fjFqjM_12528_12647.png _FOSDEM_2014__Practical_sysbench_A5pj9fjFqjM_12648_25523.png _FOSDEM_2014__Practical_sysbench_A5pj9fjFqjM_25524_30323.png _FOSDEM_2014__Practical_sysbench_A5pj9fjFqjM_30324_34019.png _FOSDEM_2014__Practical_sysbench_A5pj9fjFqjM_34020_34151.png _FOSDEM_2014__Practical_sysbench_A5pj9fjFqjM_34152_35509.png
So let's start. Hello, everyone. My name is Peter Boloz, and I work as a consultant at Sölkona. And we will talk about SID Bench and benchmarking and a bit about benchmarking in general. This talk is bigger than that one. So you would benefit the most from this if you have ever seen SID Bench or did you have seen that little stuff? I believe this is mostly based on my experience that when I have people about benchmark, that's what we'll do. We'll talk for them, and it's kind of fun to answer into this talk. So we were talking about benchmarking in general. We were talking about benchmarking IOS subsystems, which is benchmarking IOS. We were talking about benchmarking mySQL, which is benchmarking. And I will give you some tips on how did I produce the graphs I used, because last time I did 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 realized workload, but it is really good for comparison. And you can measure your systems in benchmark, or you can see your actual workload. But the actual workload is a lot less repeatable than the benchmark. But the benchmark is not necessarily close to the real workload for verification. So it's like with everything in IT, you can choose if you are testing methods we are selling this way or that different. So you can put it better that an engineer's job is to make you like compromises. Since much is pretty easy to use, I recommend all that you start with 0.5-cent bench. This is how you can compile it. Very nice packages for the reference Bellora of AutoFix. I always use this stuff almost. 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 I've set to 2, so each one is longer. And the prepared comment will take the files which the tests will be performed on. And the benchmarks which I have in these columns were made out of my laptop, so they are readily reproducible. All of this was benchmarking my laptop's SSD. So there are some parameters which we were using in the benchmark. First is the block size. We were using 16 things because that's the only big size. We were using the total size of files and the final number of files. The number of files can be important because some files like EXT4 have total number of files on the right and the left tables. So if you're doing it with only one file, you may hit that. And the database is usually more fine as well. We were using the direct file. We then initialized the random numbers, the single random numbers at the beginning of the benchmark. So if we wanted a bottom-to-battery number generation, we would vary the number of threads. We were testing synchronous and asynchronous style, and we would test random reads, writes, and random reads, writes in mix. OK, we will also use the next request, parameter. So we don't need 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. This here shows you the sample. Read throughput, writes throughput. I think the response time for the even operation. And the response time is 95% of the response time. And when I said that we vary the number of threads, does this actually matter, right? We have benchmarking the left of SSD. A left of SSD can do 1,000 diodes or 2,000 or whatever. It's a parameter of the SSD. Do we have to bother with threads? 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 cycle, our first test, we will wait until the IO request completes and only then have the result. In case of asynchronous cycle, we don't wait until the completion of the IO request, but rather check the creator and see how it goes. So in case of asynchronous IO, every more threads are removing them. Doesn't matter that much, because we are not doing asynchronous. In case of synchronous, the number of threads we will use matter a lot. You will see it from benchmark results. I like to show stuff live. So I wrote a little script, which just does the final IO. What? OK. Yes, yes. So my laptop can do like 44 times in this test. If we check the demo, then we see that this is synchronous IO on a single thread. Does it matter if I change the block size here? I have an SSD in this laptop. So if I read smaller or larger blocks, will it matter? To put the question differently, is sequential random IO, VH, different, here on SSD? We are so lucky that I have this laptop here, so we can find. If I set the block size to, let's say, 1 man, it will be a lot faster. So the nature of final, if it's sequential random, still matters on flash storage, but not as much as on spindles. Just the bomb, these are different places. I just changed the block size to 1k. And for 1k blocks, performance will start. It will be slower and slower if I lower the block size. But if I lower it under 14, which is the physical block size of the device, here I never write 1k. I only write 4k at the physical level. So this is why, if the block size reaches a certain quarter, a certain size, then it will get a lot more slow. So this is a graph of the night throughput of my laptop in case of H-Nikon SIO. And we decided this has the number of steps, doesn't matter. And this is a graph on the RISC-1 style in milliseconds. And let's talk a bit here. Is this that good? Why not? Because otherwise I wouldn't ask. So what is not visible from this graph, that the RISC-1 time is around between 10 and 20 milliseconds. But is it more like 10? Is it more like 20? What's the distribution? That's why I like Jitter thoughts. Because from this closely, you'll 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, but more or less it's 10. And the other good answer for this graph, is that can you measure the response time accurately in case of H-Nikon SIO? And we can't, because we don't wait for the completion of the actual I-O. We will check back later if the I-O operation is completed. And we can record the time when we get the results from the check. But at that time, the I-O operation will be completed for a while. OK. So this is again the right throughput. And here it is visible that the read throughput of my SSV is much higher. So it can be much faster than it can write. And we can also see that the read performance is consistent. For from the Jitter thoughts, I like to use Alpha channel for the individual thoughts. So if the thought is on top of each other, it will show a sharper color. And here you can see the read response time. SIFV bench can also do mixed throughput. And this is one root error that usually people make, that OK, my storage can do 400 lines of reads. So it will be OK. But as soon as you mix them, 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 lines of read throughput anymore. So it seems, and here is the response time of this graph. And the red one is the read. Read throughput and the blue one is the write. OK. So in last number, I started my SQL instance. And it was that it is using little state in asynchronous style. And I told them in the beginning that we will discuss results for synchronous style also. Do we have to do it at all? We can stop here, because it doesn't matter. It does matter. Asynchronous style is used for checkpointing, but for example, the read will also be synchronously. And this is the synchronous write throughput. And this graph doesn't have time in the x-axis, but rather the factor of the number of sets. And if you check this one, so the write throughput at the single set, why is this important? In my SQL 5.5 and in 5.6, if you have a single schema. Because application is single set, which means that application can't go faster than this. Why? The throughput of my storage at 16 megs is a lot better. 16 strats is a lot better. CPU? CPU, the work? The number of course? 2, but the benchmark is not CPU intensive. No, it means the strats. Yes, so I use this many strats, but this laptop has 2 CPU cores. Yeah, that's correct. OK. So if you graph the response time, you can see that up to a point, up to 16 strats, the response time somebody increases. But after a while, if I keep adding strats, the throughput doesn't change too much, but the response time, you have skyrocketed. 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 40 bar response times. And this is why it is a bad idea to set the max connections for my SQL to 10, 20,000, or something like that. If you start using all of them, this will happen. Similarly for these, so if I use more strats, this will put a wheel in place. But again, similarly, the response time will just go up after a while. And this is for these. So similarly, my need performance in isolation was pretty good. But if I do need a needs benchmark, I won't be able to do 400 megs for these anymore. And the response time is pretty similar. So after a while, the throughput doesn't grow, but the response time starts to grow exponentially. OK, so so far this was file IO, benchmarking your IO subsystem. And now let's talk about benchmarking my SQL. Here is how you can prepare test database. So you create a schema, and use the schema, and use the parallel department of LuaStrip to create your test database in parallel. And here is how do you do one benchmark declaration. One more thing with this is usually you write strips like this. That for all test modes, for all IO modes, for all set configurations, you do the benchmark, and at the end, you will aggregate it. Similarly for this is how a typical web data benchmark looks like, that you write a number of sets. And you measure these sets if you want to write another parameter, then you can do it as well. So I recommend you write basic reference strips like this. I think the most well known benchmark in Cysvenics or PPP, it has some parameters like the table size, and it has some parameters for experience as well. Like what should the insights be, how many points select should be in a transaction, how many simple ranges should be in a transaction. This is by the way in Cysvenics.com on the tour. So you can find the connection in Cysvenics.com on the tour. And for database benchmark, I chose to examine the work route now which reconnects pretty frequently. So we won't do the OTP benchmark now, but I would like to show you how can you use Cysvenics to use an issue which you may see in the production environment in kind of a sandbox by modifying the tour. So let's examine the update index that we were used for this. And I modified this script a bit that in the event function, I do a name disconnect, which means that after every transaction, Cysvenics will disconnect from the database. So what do I see? When it's here, let's say a PhD application where the PhD interpreter only runs while the HGTT request is alive. And every time, it really connects to my SQL. So if I do the benchmark without the reconnect, that means it will be OK. So this is the throughput trigger for updating by single time on certain two threads. Here the tables using 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 the while it will be kind of OK. You can also see that in my SQL, the connections are RG. So it doesn't matter that much if you are reconnecting or not. It matters a little. And now my SQL sucks, right? Oh, it sucks again. And again, what happened? My SQL is not running. Yes, it is running. Oops. It sucks again. It's here. What happened here? No? Also it's in time wait. Yes. So the issue here is that when you close the minus to your connection, the protocol level, the minus to your protocol level doesn't close the TCP-ID level connection. So if I check, also it will be in time wait. And here I'm actually out of the local IT and port range for TCP-ID. 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. OK. OK. If I limit this, then I can't have more than 1,000 connections in time wait. The kernel will simply kill it. And my benchmark is fixed now. And this will run forever because I won't go out of the local port range anymore. So if we do this again, we keep at roughly 1,000 because the rest is killed by the kernel. If we check what happens here, no, it's not killed. But here, if your traffic is high enough, then the kernel can complain that if you have TCP-ID connections, I try to break my SQL connections with that and I wasn't successful. So looks like these TCP-ID connections are there for nodeism. When you close the MySQL level connection, the server doesn't initiate the TCP-ID level connection. But if it is initiated by the client labor, so the connection will be used. I don't know if it would be better for the server to initiate the TCP-ID connection close when the client's thread is closed. OK. So thank you, everyone. Thank you. Thank you. Thank you. One more thing, which I almost forgot. My colleagues and friends from Belgium are organizing the community dinner. And they asked me to show this slide. I haven't seen that myself, so I have checked it out with you and they improvise. This is the first one. There you'll see. It has some nice logos on it. I guess that they are the companies who are the sponsors. Yes, OK. In this slide, there are some text and the map. And the text says, which bus should you take to get to the community dinner? So this is kind of a news-filled information part. And here is bus and table. So you don't have to send it away. You don't have to send it away.
a bit about benchmarking in general. This talk is benchmarking IOS subsystems, which is take the files which the tests will be performed the total size of files and the final number of you can do it as well. So I recommend you write that myself, so I have checked it out with you