WEBVTT 00:00.000 --> 00:09.000 All right, everybody, please find your seats, please be quiet. 00:09.000 --> 00:10.000 Can you say that? 00:10.000 --> 00:12.000 If you hold an action on the back. 00:12.000 --> 00:13.000 Okay. 00:13.000 --> 00:18.000 Yeah, and you can exit on the back as well, which please, if you can, do that. 00:18.000 --> 00:20.000 Just so they make less noise, less of a distraction. 00:20.000 --> 00:26.000 With that, V-tore is going to talk to us about some performance things in my 00:26.000 --> 00:27.000 video. 00:27.000 --> 00:28.000 Okay. 00:28.000 --> 00:29.000 Thank you. 00:29.000 --> 00:30.000 Hi, everybody. 00:30.000 --> 00:36.000 I'm V-tore Olivaila, and I've been working on data performance for quite a few 00:36.000 --> 00:37.000 years. 00:37.000 --> 00:40.000 From last five, I've been working at Huawei. 00:40.000 --> 00:44.000 And prior to that, I was working at Oracle, working on things like group 00:44.000 --> 00:47.000 application, my skill database service, and all of that. 00:47.000 --> 00:53.000 And right now, I mostly was working on H-tab and profiling tools and things like that. 00:53.000 --> 00:54.000 I know the basic performance. 00:54.000 --> 00:59.000 And also, we're on the T-P, which is what I'm talking today. 00:59.000 --> 01:05.000 So, basically, we were testing a new engineer, a new hip storage engine. 01:05.000 --> 01:07.000 That was developed in a different database. 01:07.000 --> 01:11.000 It was developed in Gauss TV, which is Postgres based. 01:11.000 --> 01:17.000 And while developing this, we had a performance, which was 1 million TPMC. 01:17.000 --> 01:23.000 And when we ran this on my SQL, we were getting a lot less performance. 01:23.000 --> 01:28.000 And it was really hard to understand exactly what was happening until the end. 01:28.000 --> 01:34.000 So it was a big process, understanding what was happening in the lead and understanding what 01:34.000 --> 01:35.000 was going on. 01:35.000 --> 01:39.000 So let me start just by saying, this is about TPCC. 01:39.000 --> 01:44.000 And this has a medium of complex transactions, five transactions like that go around. 01:44.000 --> 01:50.000 And it's the TPMC is the rate at which it executes new order transactions. 01:50.000 --> 01:51.000 So it's not all transactions. 01:51.000 --> 01:54.000 It's only about 45% of the time. 01:54.000 --> 01:55.000 Okay. 01:55.000 --> 01:58.000 And it's new order transactions per minute. 01:58.000 --> 01:59.000 Okay. 01:59.000 --> 02:03.000 So immediately our thought is what's wrong with this. 02:03.000 --> 02:06.000 So we have what can be the problem. 02:06.000 --> 02:07.000 Can it be the client? 02:07.000 --> 02:09.000 Maybe we're just measuring this wrong. 02:09.000 --> 02:12.000 Maybe it's the measure itself that it is a problem. 02:13.000 --> 02:16.000 And it could be because we were using two different benchmarks. 02:16.000 --> 02:19.000 Because there's one that we usually use for my SQL. 02:19.000 --> 02:22.000 And there's the other that we use for postgres. 02:22.000 --> 02:24.000 And they were not the same. 02:24.000 --> 02:26.000 So the first thing we should clear is that one. 02:26.000 --> 02:28.000 But also, of course, this is a new handler. 02:28.000 --> 02:30.000 Also, the storage engine is the same. 02:30.000 --> 02:32.000 But the handler is, of course, adapted to my SQL. 02:32.000 --> 02:35.000 Maybe it's something on there or the server itself. 02:35.000 --> 02:40.000 Maybe we have doing something on my SQL that makes it slower. 02:40.000 --> 02:44.000 And then the protocol also, the rest of the environment. 02:44.000 --> 02:45.000 There are different tools. 02:45.000 --> 02:47.000 There are different protocols. 02:47.000 --> 02:49.000 And maybe that also plays a role here. 02:49.000 --> 02:51.000 So this is the basic the sub-suspects. 02:51.000 --> 02:52.000 Okay. 02:52.000 --> 02:54.000 Let's start by the client. 02:54.000 --> 02:55.000 Okay. 02:55.000 --> 02:57.000 We started with one million TPMCs on GUSDV. 02:57.000 --> 03:00.000 And on my SQL with my SKOTPCC. 03:00.000 --> 03:03.000 We're on 415. 03:03.000 --> 03:04.000 Okay. 03:04.000 --> 03:08.000 We have to use the same at least to eliminate this source of noise. 03:08.000 --> 03:12.000 And of course, benchmark SQL is more developed. 03:12.000 --> 03:14.000 It has support for many databases. 03:14.000 --> 03:16.000 So we wanted to use benchmark SQL. 03:16.000 --> 03:19.000 And it was the one providing the best performance. 03:19.000 --> 03:23.000 But the problem is that on my SQL, 03:23.000 --> 03:26.000 it becomes even a bigger problem. 03:26.000 --> 03:27.000 Okay. 03:27.000 --> 03:29.000 Now we're on 140K. 03:29.000 --> 03:31.000 It's transactions per minute. 03:31.000 --> 03:35.000 And which is even even harder problem. 03:35.000 --> 03:38.000 So let's see what's happening here. 03:38.000 --> 03:40.000 First, of course, the optimizer. 03:40.000 --> 03:43.000 These are already minimally complex transactions. 03:43.000 --> 03:46.000 So the plans that are generated by the optimizer. 03:46.000 --> 03:47.000 Of course, important. 03:47.000 --> 03:50.000 So the first thing was tracking exactly what the plans were 03:50.000 --> 03:51.000 and comparing them. 03:51.000 --> 03:57.000 And understanding what is currently was being executed in this case. 03:57.000 --> 03:59.000 And we fixed several things. 03:59.000 --> 04:02.000 But eventually the main issue was around statistics. 04:03.000 --> 04:05.000 So we're not calculating them correctly. 04:05.000 --> 04:09.000 Once that was fixed, most of the plans were executed 04:09.000 --> 04:10.000 mostly similarly. 04:14.000 --> 04:15.000 Okay. 04:15.000 --> 04:17.000 But we also found an optimizer bug. 04:17.000 --> 04:20.000 And this bug was actually important to the performance drop 04:20.000 --> 04:22.000 that we were seeing. 04:22.000 --> 04:25.000 And this bug basically, in the queries in this format, 04:25.000 --> 04:28.000 it is basically ignoring the lower range. 04:28.000 --> 04:34.000 So it's getting much, many more rows than it actually needs. 04:34.000 --> 04:36.000 Okay. 04:36.000 --> 04:40.000 And the end result was actually that we improved from that to 750 04:40.000 --> 04:41.000 TPMCs. 04:41.000 --> 04:43.000 So it's already much better than we have. 04:43.000 --> 04:44.000 Okay. 04:44.000 --> 04:46.000 But it's still 25% away. 04:46.000 --> 04:48.000 And that's not good for us. 04:48.000 --> 04:49.000 Okay. 04:49.000 --> 04:51.000 So let's continue. 04:51.000 --> 04:56.000 We wanted to, using benchmark SQL brings things like 04:57.000 --> 05:00.000 I think Java to the mix and doing lots of things. 05:00.000 --> 05:03.000 So we wanted to make this simpler and understanding that different. 05:03.000 --> 05:08.000 So what we did is extract the queries and basically execute the queries directly 05:08.000 --> 05:11.000 send the single threaded execution of queries to one server in the other. 05:11.000 --> 05:16.000 Exactly the same amount so that we also profile it exactly the same patterns 05:16.000 --> 05:20.000 on both servers to understand the profile differences. 05:20.000 --> 05:21.000 Okay. 05:21.000 --> 05:25.000 But now because the strange parts, we execute this in my SQL. 05:25.000 --> 05:28.000 So the same number of transactions. 05:28.000 --> 05:31.000 And now it's twice faster in my SQL. 05:31.000 --> 05:36.000 So what is what's strange is now even stranger. 05:36.000 --> 05:38.000 Okay. 05:38.000 --> 05:39.000 Okay. 05:39.000 --> 05:43.000 So again, I wanted to simplify, I wanted to understand this fully. 05:43.000 --> 05:50.000 So we started building our own TPC client with access to both databases without having anything else in the mix. 05:50.000 --> 05:54.000 And trying to get this in also because we wanted to compare transactions to transactions. 05:54.000 --> 05:59.000 So it was better for us to just start from scratch and do this. 05:59.000 --> 06:04.000 But even in this case, and also because we're thinking that maybe on scalability with many threads, 06:04.000 --> 06:07.000 it will eventually be the reason why there's this difference. 06:07.000 --> 06:11.000 And then it will be slower again in the case of my SQL and justifies this. 06:11.000 --> 06:14.000 But the fact is my SQL was still faster in this case. 06:14.000 --> 06:19.000 So we were still seeing it being much faster than on the other case. 06:19.000 --> 06:21.000 So there was something else here. 06:21.000 --> 06:25.000 So and that was explained once we got to the prepared statements. 06:25.000 --> 06:28.000 Basically, we added prepared statements to the tool also. 06:28.000 --> 06:32.000 And once we did that, my SQL gained a bit from prepared statements. 06:32.000 --> 06:34.000 This 25%. 06:34.000 --> 06:36.000 But the fact is, it's just jumped. 06:36.000 --> 06:40.000 It was more than twice the performance with prepared statements. 06:40.000 --> 06:41.000 Okay. 06:41.000 --> 06:44.000 So there was clearly the interpretation of the statements in all of that. 06:44.000 --> 06:49.000 Have a significant weight in this thing that we were seeing. 06:49.000 --> 06:50.000 Okay. 06:50.000 --> 06:53.000 So we started thinking what can we do to try to minimize this. 06:53.000 --> 06:57.000 And basically we came up with a session plan case. 06:57.000 --> 07:02.000 And with a session plan case, what we do is basically we try to, in the same session, 07:02.000 --> 07:06.000 we don't, we try to avoid replanting. 07:06.000 --> 07:10.000 We do it once and try to reuse it as much as possible to avoid that part of the overhead. 07:10.000 --> 07:12.000 And in fact it improved a bit. 07:12.000 --> 07:16.000 So we're getting 5% already of that different spec. 07:17.000 --> 07:18.000 Okay. 07:18.000 --> 07:24.000 Now we try to compare it also with the storage engine with NODB itself. 07:24.000 --> 07:31.000 Just to see if there's something different that our handle was doing that may be causing some performance degradation also. 07:31.000 --> 07:34.000 And then we found out that although it changed a bit, 07:34.000 --> 07:39.000 we know that we, in single-threaded performance was slightly slower, 07:39.000 --> 07:43.000 but in multi-threaded performance, we scale better and all of that. 07:43.000 --> 07:44.000 Okay. 07:44.000 --> 07:45.000 They were mostly the same. 07:45.000 --> 07:49.000 But those two queries were really different. 07:49.000 --> 07:51.000 And there's also query 26. 07:51.000 --> 07:53.000 It's a different thing. 07:53.000 --> 07:55.000 So we try to understand what was happening. 07:55.000 --> 08:02.000 And the reason was that hip-technical engine is different from IODB, of course. 08:02.000 --> 08:06.000 And one of the things is that on the secondary indexes, 08:06.000 --> 08:10.000 it doesn't have the full primary key. 08:10.000 --> 08:12.000 But IODB does. 08:12.000 --> 08:17.000 So what we have here is that we're using count ID, 08:17.000 --> 08:20.000 but ID is not on the secondary key. 08:20.000 --> 08:23.000 It's only on the primary key of the database. 08:23.000 --> 08:24.000 Okay. 08:24.000 --> 08:28.000 And once it one this runs through IODB, 08:28.000 --> 08:31.000 it's occurring index anyway, 08:31.000 --> 08:34.000 because the ID is stored in that secondary index. 08:34.000 --> 08:37.000 But the fact is our hip-technical engine, it doesn't have that. 08:37.000 --> 08:39.000 So it sends direct to the row. 08:39.000 --> 08:43.000 So we had to do another look at just to find this row in the database. 08:43.000 --> 08:47.000 Okay. 08:47.000 --> 08:50.000 And this has two ways to fix. 08:50.000 --> 08:53.000 One of this in the client itself, right? 08:53.000 --> 08:56.000 Just change the ID to another field. 08:56.000 --> 08:58.000 And that is in the secondary index. 08:58.000 --> 09:01.000 It's just nothing special about this. 09:01.000 --> 09:03.000 And it's an immediate change. 09:03.000 --> 09:07.000 But I think there may be cases where this happens frequently. 09:07.000 --> 09:09.000 So we also wanted to avoid this. 09:09.000 --> 09:12.000 If users have applications like this and are using the system. 09:12.000 --> 09:14.000 And then they detect this. 09:14.000 --> 09:15.000 They use this kind of query. 09:15.000 --> 09:20.000 So basically we rewrote whenever we have a key that is non-null. 09:20.000 --> 09:21.000 And from the primary key. 09:21.000 --> 09:23.000 So we know that it's non-null. 09:23.000 --> 09:25.000 We can transform the queries like this. 09:25.000 --> 09:27.000 And we avoid this in problem entirely, 09:27.000 --> 09:29.000 even without rewriting the clients. 09:29.000 --> 09:32.000 And this brings about 10% extra. 09:32.000 --> 09:36.000 Just because of this. 09:36.000 --> 09:40.000 And another issue was this was rather strange. 09:40.000 --> 09:45.000 So basically there's this query that we know that it was returning for benchmark SQL. 09:45.000 --> 09:48.000 It was returning more rows than it was actually consuming. 09:48.000 --> 09:51.000 So basically they were using only the first row. 09:51.000 --> 09:54.000 But you should acquire that return multiple rows. 09:54.000 --> 09:55.000 Okay. 09:55.000 --> 09:58.000 This is easy to fix because you can just limit to one. 09:58.000 --> 10:00.000 And that should be there anyway. 10:00.000 --> 10:02.000 Other benchmarks actually do this. 10:02.000 --> 10:04.000 So it doesn't make sense that it's like that. 10:04.000 --> 10:07.000 But I think it's the impact that it has. 10:07.000 --> 10:11.000 On Gaussian B and mySQL is very different. 10:11.000 --> 10:15.000 Why would it be so different between the two databases? 10:15.000 --> 10:18.000 And clearly it's something that is not being processed. 10:18.000 --> 10:20.000 This is provoking something in the database. 10:20.000 --> 10:23.000 That is not not well. 10:23.000 --> 10:24.000 Okay. 10:24.000 --> 10:27.000 So we went to a news aspect for us, 10:27.000 --> 10:30.000 which is how is this being done on the protocol level. 10:30.000 --> 10:35.000 So what's happening here that is provoking this kind of behavior. 10:35.000 --> 10:39.000 So we went the root cause we went to this parameter, 10:39.000 --> 10:41.000 which is the fetch size. 10:41.000 --> 10:43.000 On JDBC you have fetch size parameter, 10:43.000 --> 10:47.000 which is basically specifies that when you issue your query to the server, 10:47.000 --> 10:50.000 it will return, if you say fetch size time, 10:50.000 --> 10:52.000 it will return 10 rows from the server. 10:52.000 --> 10:55.000 And then if you want more, you get more from the server and so on. 10:55.000 --> 10:57.000 In 10 rows each time. 10:57.000 --> 11:00.000 But if you specify fetch size 0, 11:00.000 --> 11:02.000 it just fetches all of the rows. 11:02.000 --> 11:05.000 And then you have to process them, right? 11:05.000 --> 11:10.000 MySQL has the best performance when we use fetch size 0. 11:10.000 --> 11:15.000 But if you use fetch size 10, performance dropped a lot. 11:15.000 --> 11:17.000 And Gaussian B was the opposite. 11:17.000 --> 11:19.000 So basically using fetch size 10, 11:19.000 --> 11:22.000 it was better than using fetch size 0. 11:23.000 --> 11:25.000 Okay. 11:25.000 --> 11:31.000 And the problem was that mySQL changes the way that it fetches rows, 11:31.000 --> 11:33.000 depending on this parameter. 11:33.000 --> 11:37.000 So unlike this case on a Gaussian B with fetch size 0, 11:37.000 --> 11:40.000 where it brings it all in my scroll, 11:40.000 --> 11:42.000 also brings it all of them out. 11:42.000 --> 11:45.000 So it was worse for a Gaussian B to bring it all of them out, 11:45.000 --> 11:49.000 because even this particular case instead of 10, 11:49.000 --> 11:50.000 it will return all rows. 11:50.000 --> 11:52.000 But for mySQL, 11:52.000 --> 11:57.000 this query would actually issue a first command to the server 11:57.000 --> 11:59.000 and not return any rows. 11:59.000 --> 12:02.000 And another command has to be sent to the server 12:02.000 --> 12:04.000 to fetch the 10 rows. 12:04.000 --> 12:08.000 So you have to do two round trips for the same thing. 12:12.000 --> 12:13.000 Okay. 12:13.000 --> 12:18.000 So in the end, we get to this point where we are seeing here. 12:18.000 --> 12:21.000 Of course, this is as the database was in developed. 12:21.000 --> 12:26.000 So the database itself was increasing the performance on both edges. 12:26.000 --> 12:30.000 So at this point, we are here at where we are at 1.7 million. 12:30.000 --> 12:33.000 But on Gaussian B, we are still on 1.4. 12:33.000 --> 12:36.000 So there's still a significant gap here. 12:36.000 --> 12:40.000 So we started looking at the level messages being sent. 12:40.000 --> 12:44.000 And here, of course, we see some strange things. 12:44.000 --> 12:46.000 So mySQL sends more messages. 12:46.000 --> 12:49.000 And there's slightly larger. 12:49.000 --> 12:52.000 And there's a system called instead of, for instance, 12:52.000 --> 12:55.000 we're reading a message instead of reading the data. 12:55.000 --> 12:56.000 It is in socket. 12:56.000 --> 12:58.000 You first read the size of the message. 12:58.000 --> 13:01.000 And then you do actually read another system called to read the message. 13:01.000 --> 13:04.000 Which doesn't happen in the other case of the database. 13:04.000 --> 13:09.000 And it actually is not even consistent between different products around mySQL. 13:09.000 --> 13:12.000 This happens with JDBC. 13:13.000 --> 13:15.000 And it happens to the server. 13:15.000 --> 13:17.000 But it doesn't happen with the C client. 13:17.000 --> 13:22.000 And you know, it was trying to just do a single system called another. 13:22.000 --> 13:24.000 But the impact was not that significant. 13:24.000 --> 13:25.000 Because basically all of the data was there. 13:25.000 --> 13:27.000 It was just the extra system called. 13:27.000 --> 13:30.000 It's not nice, but it's fine. 13:30.000 --> 13:33.000 The biggest difference is was this. 13:33.000 --> 13:40.000 So at some point, we understood that both proper statements were being used. 13:40.000 --> 13:44.000 My benchmark SQL, but process differently by JDBC itself. 13:44.000 --> 13:48.000 Since mySQL doesn't have both proper statements support. 13:48.000 --> 13:50.000 What happens, what happens. 13:50.000 --> 13:51.000 And there's a part here. 13:51.000 --> 13:54.000 It's between five and 15 rows that are sent. 13:54.000 --> 13:57.000 They can be joined in a single statement. 13:57.000 --> 14:03.000 But in mySQL case, it's actually executed in multiple, as multiple statements. 14:03.000 --> 14:06.000 It's done on JDBC part. 14:06.000 --> 14:13.000 So this was actually the thing that showed the biggest difference. 14:13.000 --> 14:14.000 Okay. 14:14.000 --> 14:18.000 But mySQL also has some rewrite batch statements option. 14:18.000 --> 14:20.000 But to help with this. 14:20.000 --> 14:23.000 But the thing is, this only helps on inserts. 14:23.000 --> 14:25.000 It doesn't help on updates. 14:25.000 --> 14:29.000 It just executes as it was executed in before. 14:29.000 --> 14:33.000 So now we get to two options. 14:33.000 --> 14:35.000 We try to different alternatives. 14:35.000 --> 14:37.000 One of them was basically changed. 14:37.000 --> 14:39.000 And the JDBC connector. 14:39.000 --> 14:44.000 Of course, we also tried the third one, which is changed the application and issue on our side, 14:44.000 --> 14:45.000 or testing to do this. 14:45.000 --> 14:47.000 But we wanted the margin editing. 14:47.000 --> 14:50.000 So we tried to change the JDBC connector. 14:50.000 --> 14:54.000 But it's really hard to do this on the JDBC connector itself. 14:54.000 --> 14:57.000 And we also tried this alternative question. 14:57.000 --> 15:02.000 Was adding support that is in MariaDB and tried the MariaDB connector with this. 15:02.000 --> 15:08.000 And the fact is, in the end, we got something like a very similar behavior. 15:08.000 --> 15:11.000 But the fact is that we get to the same. 15:11.000 --> 15:15.000 So we finally close the gap between both databases. 15:15.000 --> 15:22.000 So all of those things in the end actually brought us to the situation where we actually get this system in the same level. 15:22.000 --> 15:24.000 So it's not mySQL fault, it's nothing. 15:24.000 --> 15:27.000 So it's things that have to be fixed. 15:27.000 --> 15:29.000 But it's fine. 15:30.000 --> 15:36.000 And okay, so this final thought is really, okay, you have to go end to end to understand those things. 15:36.000 --> 15:42.000 Because it's really, it was really a hard process to actually find the small details on this. 15:42.000 --> 15:46.000 Because it was a sort of end to understand some of those things. 15:46.000 --> 15:53.000 And it was really nice actually to have two databases using the same storage engine, where you could do this. 15:53.000 --> 15:58.000 Because if it was not for the fact that we were putting two databases by side to compare, 15:58.000 --> 16:01.000 we would never understand that we were slower. 16:01.000 --> 16:03.000 And that was actually a good driver. 16:03.000 --> 16:06.000 It was a good driver to have, I know really on one side and this. 16:06.000 --> 16:09.000 And it was a good driver also to have two databases and storage engines in there. 16:09.000 --> 16:14.000 And seeing, oh, this is why is this worse. 16:14.000 --> 16:15.000 Okay. 16:15.000 --> 16:18.000 So, okay, that's it from me. 16:18.000 --> 16:19.000 Thank you. 16:19.000 --> 16:29.000 Thank you.