Monitoring MariaDB Server with bpftrace on Linux
Kravchuk, Valerii
Bpftrace is a relatively new open source tracer for modern Linux (kernels 5.x.y) for analyzing production performance problems and troubleshooting software. Basic usage of the tool, as well as bpftrace-based one liners and small scripts useful for MariaDB DBAs (and even developers) are presented. Problems of MariaDB Server dynamic tracing with bpftrace are discussed.
 Hi, my name is Valery Kravchuk and today I'm going to speak about using BPF Trace for monitoring and solving performance problems with MariaDB Server on Linux. I am a principal support engineer in MariaDB Corporation for five years and in this professional services industry around MySQL and MariaDB for 15-16 years soon actually. I used to work in MySQL, then Sun Oracle per corner. Since I quit from Oracle, I became a public person. I created a blog, a Twitter account, started to participate in conferences. Initially, I concentrated mostly in MySQL bugs, hence the names of my accounts, but in the process of work I have to share the way to solve problems and they are not always related to bugs, so there is a lot of how-to's. For historical reasons, all my slides that are always public for the conferences are kind of references, so whenever you see something underlined there, it's a link and you should follow it. You can find a lot of detailed information as a result. A word of warning while I am working for MariaDB Corporation, I am not in a mission from them here and I only speak for myself. The approach is used for troubleshooting the ideas, the conclusions from that are mine and may not be shared by my current or previous employees or partners. So what is the main problem for MariaDB DBA when there is some performance problem? It's to find out what's happening, actually, what is the reason. There are different sources of information, ways to trace and profile server, starting from debug binaries that developers prefer to use while working on bugs identified that adds a lot of tracing possibilities. They are not practical to use in production because they are slow. There are MySQL and MariaDB ways to find some of this information at SQL level. Different show statements appear at different times, different tables, for example, in the information scheme. Some of them are added by plugins or by additional tools like user stuff that is available in MariaDB and Perconos server and account separation per user, table, index, reads, writes, and all that stuff. There is extended slow query log that does not only register the time to execute the query but more details about it, like was it using temporary table, how much time was spent at specific stages and all that. Moreover, starting from MySQL 5.5, a separate performance schema was created based on the idea of instrumenting the code to collect performance-related information in the memory buffers that are recycled, reused, so it's a very efficient way to collect this information that then can be curated by SQL statements on performance schema. The idea is great, the code is instrumented more and more with every version and MariaDB tries to keep up with upstream, so also adding some features here from time to time. What else besides that? There are tools outside of MariaDB. Pre-writing system Linux in our case, the kernel, drivers, and all the application and system software is to be monitored and profiled. So one of the ideas that I like personally is to use OS level tracing and profiling tools. Historically, there were many tools starting from kernels 2.6, there is a system of tools from PROC file system with samples that can be checked there by reading files to soon edit F-trace file system and F-trace system that allows to add dynamic tracing anytime. A bit later, Perf profiler was added, allows to do a sampling profiling of the content of processors, for example, and access other performance counters. The last addition in case of Linux is so-called EBPF extended Berkeley packet filter system that added a separate flexible and still safe virtual machine for executing code inside the kernel in a safe and seamless manner. And a lot of tools were based on these frameworks, the last of them at the moment is BPF trace and I'm going to speak about it as well. So my idea is that all MariaDB DPAs that are working with modern Linux kernels kernel 5 whatever should be ready and should use BPF trace tool for troubleshooting. I will show some examples, discuss some problems, the main idea is that the performance impact of such a tracing if it's done in a smart way will be really small compared to anything else. So we can have a continuous monitoring and still tracing not just sampling of stuff we are interested in. I'm not going to speak about performance schema because it's covered elsewhere, I'm not going to speak about other tools for dynamic tracing on Linux again because it's covered elsewhere including my own talks previously at Fosden and other conferences. So BPF trace is one of those tools we see it on this chart that covers the entire stack starting from the application code to device drivers with kernel subsystems in between with access to performance counters, disk Ion network and all other systems. So just the reason of a set of such a global tools. So we are going to check it. All these tools tracing essentially that they do is doing something when some event happens. We need to know what events are traceable so we can classify them. There are events in the kernel, there are events in the application code. Some events appears only if the developer cared to instrument the code like it happens with performance schema in MySQL and my ADP by adding trace points. There are kernel trace points, there are trace points in the user code so called USDT, the Relent Statically Defined Tracing or detrace probes by detrace system on Solaris that introduced them first. Of all these I am mostly interested in dynamic tracing of kernel and specifically MariaDB. So my main point of interest is user probes. So what the EBPF system gives us? EBPF system allows us to create quite complex actions, to execute complex actions represented as a program for virtual machine that runs in kernel. It's LLVM. So whatever we can run LLVM that is safe enough to run in kernel, that can be the action when whatever event is happening. How is this working? We generate the bytecode for this virtual machine as a user. It then passed to the kernel and it's verified. This is an important thing that makes the entire process safe. This verification makes sure that we cannot access a memory that we should not access, that the execution of our program completes an infinite time, there are no endless loops and all that. And if it's so, the program is executed, if not the error is returned and it can access all kinds of events that can be traced, performance events and everything, write things back to the user space. But what's even more interesting is that it can collect the information in memory buffers at kernel context, so called maps or associative arrays. So we can aggregate the information there, collect how many times specific actions happened and have that index action as an index in our associative arrays and counter whatever else time as an element of that index. And export only the summary information, this is really great stuff. The EBPF system evolved with time, so there was at kernel four different versions, new and new features were added. But basically now we have a way to associate quite an arbitrary and powerful EBPF program bytecode for LLVM with all event sources that we already discussed, profiling information, hardware counters and more. With kernel five, we can do that for anything. That's great. Usually these days, unlike maybe 10 years ago, nobody is writing bytecode for LLVM directly, so we use some front end tools to generate this bytecode. It can be BCC tools, BPF compiler collection, or it can be BPF trace that does most of the work for us, allowing to just define the program as a set of probe action pairs, or maybe probe, filter, condition when action should be applied, action triples. So here on this picture, we can see different kinds of probes that we can define action for with BPF trace. They are dynamic tracing probes, user probe, probe that is executed when we return from the function, URED probe, static trace at user level, same at kernel level. There are special probes to run at the beginning and at the end of execution, for example, to provide some right information. There are kernel trace points, there are hardware counters, probes can be executed once in a while, there is a profiling profiler and all that stuff. So how to start with BPF trace? You have to install it if you use modern Linux, for example Fedora 33, just install BPF trace and all the dependencies, or you can build it from source. And then as soon as you can run BPF trace, run it with minus H, for example, option, and you will see the commands. So these commands, there are many in modern versions, two are most important. Minus L is the listing probes for a given template, so you can see which system calls you have as a trace point, for example. After finding out specific one, you can define the action for it. And yes, the most important is that you can provide the program, a set of so formed statements of the structure I've already presented. This for me looks more or less like AWK program. It's one of my favorite text processing languages, so it's quite familiar. The syntax is also resembling me a C language, printf is exactly C. There are built-in arguments, for example, call me the command that is executing, there are built-in functions and all that stuff. Go read the reference guide, or you can start, if you like to study by example, you can start by checking the content of the tool subdirectory at GitHub for the BPF trace. There are a lot of examples. The programs themselves are with BT file extension, and there are examples explaining the usage. I've studied several of them, some of them highlighted here, more highlighted in my blog post, mostly related to disk IE. And got a lot of hints and found out a lot of good ways to structure the program, built in functions approach. So studying programming by example is one of the techniques that can be applied. So based on that and my previous dynamic tracing attempts with Perf, I can easily do things, for example, like capturing queries with execution time. So for this, I need to know where the query is executed in the code of MariaDB, and I know it's a dispatch command function. The third argument is the query text, when I enter the function, the query processing begins essentially when I exit from it, the query is completed. So I've defined two probes for the MariaDB binary called MariaDBD. I've used my name here for a reason because I used to do that, even though at recent version it's not necessary. And there is yet another reason that is covered in my blog post. I was a bit lazy. So I've defined probe that runs when we start. I'm collecting SQL. And in another associative arrays, I am collecting starting times for the SQL when it's executed by specific thread. ThreadID is also an embedded built-in variable. When I get out of the function, I'm checking that I have started counting for specific thread and compiling the difference of time and present it in milliseconds. Here is the output. For example, for select sleep 4, I will sleep in exactly 4 seconds up to a millisecond precision. So that's cool. What else? I am usually required by my SQL developers in case of performance problems to provide stack traces. There is a built-in function useTack to provide the user stack trade. There is a key stack for kernel stack, and I can use VPS trace with profile probe as a performance profile. So here at 99 hertz, 99 times per second, I am checking that for the process named MariaDbd. I'm just printing user stack that I have for each of the threads. I'm dumping that. And this can be post-processed if you care to find out. You can summarize, count different stack traces. You can build flame graphs. There is an older block post that shows what is possible. More or less would PTPMP and other powerman profiles, the other perf with minus g option. Another request I sometimes get is not to sample but to trace specific function calls. And it can be not only calls inside MariaDB source code but system libraries calls like malloc or pthreadmutexloc in this case. You can trace that too. You just need to point out a probe will be in the shared library. Give name, know where the arguments are passed. In this case, I do not need arguments. I need user stack. How we ended up calling this function. And I am counting for each unique stack how many times it was executed because the request originally was to count how many times this happens. Know that for such often high frequency events, the performance impact is notable. Here we see like 50% drop in performance and more just because we are collecting very often and export a lot of raw traces basically to the user land. Even into a memory file system, it's still slow. If you are more careful and do probes less often, for example, profile 99 times per second, the BPF trace approach is providing the least possible impact. The fastest tracing is with BPF traces. One of my examples presented here shown. So is it so easy to use BPF trace? Yes. But you need to have a root of pseudo privilege. In some cases even root. You have to care to write your probe carefully to not write too much to the user land because it impacts performance and not to abuse CPU and memory while you are working in can. So aggregation still must be smart. There are some tactical problems like I do not know how to add dynamic probe with BPF trace to some line inside the function while it's perfectly doable with Perf. It's doable just like the experience and had not studied it in detail. There will be problems surely with C++, which all member functions is hardly traceable in any way including BPF trace. We will also need headers if we access complex structures and it may be not only kernel headers, but header specific to the application software. I had problems with that with trace and complex stuff. Perf sometimes is easier to deal with. You have to your binaries to be compiled with symbolic information and from read a bit may mean installing additional debugging for packages or even building binaries from source. Some of these problems are resolved recently by different way to build BPF binary source called Core Compile 1s run everywhere system based also on BPF type format so that we have a compact way to represent the symbolic information for the kernel for example to make programs independent and we can do it probably for application software as well. So the most important problem is the lack of real life experience with BPF trace and the main reason is that customers do not yet use Linux kernel file. So eventually we will be there. It's the future. It may be somewhat crazy to use application MariaDB with operating system profiling tools that are usually associated with system administrators or performance engineers or kernel hackers, but it really works. As in case of open source software like MariaDB or MySQL there is no good reason not to try to use these tools because you can study the source code and not just try things but be sure what to code in your probes to end up with useful reason. That's all. Thank you very much. I'm ready for your questions. If you have found something that looks like a bug in MariaDB please report it. Thank you again and bye.
 Hi, my name is Valery Kravchuk and today I'm going to speak about using BPF Trace for  monitoring and solving performance problems with MariaDB Server on Linux.  I am a principal support engineer in MariaDB Corporation for five years and in this professional  services industry around MySQL and MariaDB for 15-16 years soon actually.  I used to work in MySQL, then Sun Oracle per corner.  Since I quit from Oracle, I became a public person.  I created a blog, a Twitter account, started to participate in conferences.  Initially, I concentrated mostly in MySQL bugs, hence the names of my accounts, but  in the process of work I have to share the way to solve problems and they are not always  related to bugs, so there is a lot of how-to's.  For historical reasons, all my slides that are always public for the conferences are  kind of references, so whenever you see something underlined there, it's a link and you should  follow it.  You can find a lot of detailed information as a result.  A word of warning while I am working for MariaDB Corporation, I am not in a mission from them  here and I only speak for myself.  The approach is used for troubleshooting the ideas, the conclusions from that are mine  and may not be shared by my current or previous employees or partners.  So what is the main problem for MariaDB DBA when there is some performance problem?  It's to find out what's happening, actually, what is the reason.  There are different sources of information, ways to trace and profile server, starting  from debug binaries that developers prefer to use while working on bugs identified that  adds a lot of tracing possibilities.  They are not practical to use in production because they are slow.  There are MySQL and MariaDB ways to find some of this information at SQL level.  Different show statements appear at different times, different tables, for example, in the  information scheme.  Some of them are added by plugins or by additional tools like user stuff that is available in  MariaDB and Perconos server and account separation per user, table, index, reads, writes, and  all that stuff.  There is extended slow query log that does not only register the time to execute the  query but more details about it, like was it using temporary table, how much time was  spent at specific stages and all that.  Moreover, starting from MySQL 5.5, a separate performance schema was created based on the  idea of instrumenting the code to collect performance-related information in the memory  buffers that are recycled, reused, so it's a very efficient way to collect this information  that then can be curated by SQL statements on performance schema.  The idea is great, the code is instrumented more and more with every version and MariaDB  tries to keep up with upstream, so also adding some features here from time to time.  What else besides that?  There are tools outside of MariaDB.  Pre-writing system Linux in our case, the kernel, drivers, and all the application and  system software is to be monitored and profiled.  So one of the ideas that I like personally is to use OS level tracing and profiling tools.  Historically, there were many tools starting from kernels 2.6, there is a system of tools  from PROC file system with samples that can be checked there by reading files to soon  edit F-trace file system and F-trace system that allows to add dynamic tracing anytime.  A bit later, Perf profiler was added, allows to do a sampling profiling of the content  of processors, for example, and access other performance counters.  The last addition in case of Linux is so-called EBPF extended Berkeley packet filter system  that added a separate flexible and still safe virtual machine for executing code inside  the kernel in a safe and seamless manner.  And a lot of tools were based on these frameworks, the last of them at the moment is BPF trace  and I'm going to speak about it as well.  So my idea is that all MariaDB DPAs that are working with modern Linux kernels kernel 5  whatever should be ready and should use BPF trace tool for troubleshooting.  I will show some examples, discuss some problems, the main idea is that the performance impact  of such a tracing if it's done in a smart way will be really small compared to anything  else.  So we can have a continuous monitoring and still tracing not just sampling of stuff we  are interested in.  I'm not going to speak about performance schema because it's covered elsewhere, I'm not going  to speak about other tools for dynamic tracing on Linux again because it's covered elsewhere  including my own talks previously at Fosden and other conferences.  So BPF trace is one of those tools we see it on this chart that covers the entire stack  starting from the application code to device drivers with kernel subsystems in between  with access to performance counters, disk Ion network and all other systems.  So just the reason of a set of such a global tools.  So we are going to check it.  All these tools tracing essentially that they do is doing something when some event happens.  We need to know what events are traceable so we can classify them.  There are events in the kernel, there are events in the application code.  Some events appears only if the developer cared to instrument the code like it happens  with performance schema in MySQL and my ADP by adding trace points.  There are kernel trace points, there are trace points in the user code so called USDT,  the Relent Statically Defined Tracing or detrace probes by detrace system on Solaris  that introduced them first.  Of all these I am mostly interested in dynamic tracing of kernel and specifically MariaDB.  So my main point of interest is user probes.  So what the EBPF system gives us?  EBPF system allows us to create quite complex actions, to execute complex actions represented  as a program for virtual machine that runs in kernel.  It's LLVM.  So whatever we can run LLVM that is safe enough to run in kernel, that can be the action when  whatever event is happening.  How is this working?  We generate the bytecode for this virtual machine as a user.  It then passed to the kernel and it's verified.  This is an important thing that makes the entire process safe.  This verification makes sure that we cannot access a memory that we should not access,  that the execution of our program completes an infinite time, there are no endless loops  and all that.  And if it's so, the program is executed, if not the error is returned and it can access  all kinds of events that can be traced, performance events and everything, write things back to  the user space.  But what's even more interesting is that it can collect the information in memory buffers  at kernel context, so called maps or associative arrays.  So we can aggregate the information there, collect how many times specific actions happened  and have that index action as an index in our associative arrays and counter whatever  else time as an element of that index.  And export only the summary information, this is really great stuff.  The EBPF system evolved with time, so there was at kernel four different versions, new  and new features were added.  But basically now we have a way to associate quite an arbitrary and powerful EBPF program  bytecode for LLVM with all event sources that we already discussed, profiling information,  hardware counters and more.  With kernel five, we can do that for anything.  That's great.  Usually these days, unlike maybe 10 years ago, nobody is writing bytecode for LLVM directly,  so we use some front end tools to generate this bytecode.  It can be BCC tools, BPF compiler collection, or it can be BPF trace that does most of the  work for us, allowing to just define the program as a set of probe action pairs, or maybe probe,  filter, condition when action should be applied, action triples.  So here on this picture, we can see different kinds of probes that we can define action  for with BPF trace.  They are dynamic tracing probes, user probe, probe that is executed when we return from  the function, URED probe, static trace at user level, same at kernel level.  There are special probes to run at the beginning and at the end of execution, for example,  to provide some right information.  There are kernel trace points, there are hardware counters, probes can be executed once in a  while, there is a profiling profiler and all that stuff.  So how to start with BPF trace?  You have to install it if you use modern Linux, for example Fedora 33, just install BPF trace  and all the dependencies, or you can build it from source.  And then as soon as you can run BPF trace, run it with minus H, for example, option,  and you will see the commands.  So these commands, there are many in modern versions, two are most important.  Minus L is the listing probes for a given template, so you can see which system calls  you have as a trace point, for example.  After finding out specific one, you can define the action for it.  And yes, the most important is that you can provide the program, a set of so formed statements  of the structure I've already presented.  This for me looks more or less like AWK program.  It's one of my favorite text processing languages, so it's quite familiar.  The syntax is also resembling me a C language, printf is exactly C.  There are built-in arguments, for example, call me the command that is executing, there  are built-in functions and all that stuff.  Go read the reference guide, or you can start, if you like to study by example, you can start  by checking the content of the tool subdirectory at GitHub for the BPF trace.  There are a lot of examples.  The programs themselves are with BT file extension, and there are examples explaining  the usage.  I've studied several of them, some of them highlighted here, more highlighted in my blog  post, mostly related to disk IE.  And got a lot of hints and found out a lot of good ways to structure the program, built  in functions approach.  So studying programming by example is one of the techniques that can be applied.  So based on that and my previous dynamic tracing attempts with Perf, I can easily do things,  for example, like capturing queries with execution time.  So for this, I need to know where the query is executed in the code of MariaDB, and I  know it's a dispatch command function.  The third argument is the query text, when I enter the function, the query processing  begins essentially when I exit from it, the query is completed.  So I've defined two probes for the MariaDB binary called MariaDBD.  I've used my name here for a reason because I used to do that, even though at recent version  it's not necessary.  And there is yet another reason that is covered in my blog post.  I was a bit lazy.  So I've defined probe that runs when we start.  I'm collecting SQL.  And in another associative arrays, I am collecting starting times for the SQL when it's executed  by specific thread.  ThreadID is also an embedded built-in variable.  When I get out of the function, I'm checking that I have started counting for specific  thread and compiling the difference of time and present it in milliseconds.  Here is the output.  For example, for select sleep 4, I will sleep in exactly 4 seconds up to a millisecond precision.  So that's cool.  What else?  I am usually required by my SQL developers in case of performance problems to provide  stack traces.  There is a built-in function useTack to provide the user stack trade.  There is a key stack for kernel stack, and I can use VPS trace with profile probe as  a performance profile.  So here at 99 hertz, 99 times per second, I am checking that for the process named MariaDbd.  I'm just printing user stack that I have for each of the threads.  I'm dumping that.  And this can be post-processed if you care to find out.  You can summarize, count different stack traces.  You can build flame graphs.  There is an older block post that shows what is possible.  More or less would PTPMP and other powerman profiles, the other perf with minus g option.  Another request I sometimes get is not to sample but to trace specific function calls.  And it can be not only calls inside MariaDB source code but system libraries calls like  malloc or pthreadmutexloc in this case.  You can trace that too.  You just need to point out a probe will be in the shared library.  Give name, know where the arguments are passed.  In this case, I do not need arguments.  I need user stack.  How we ended up calling this function.  And I am counting for each unique stack how many times it was executed because the request  originally was to count how many times this happens.  Know that for such often high frequency events, the performance impact is notable.  Here we see like 50% drop in performance and more just because we are collecting very  often and export a lot of raw traces basically to the user land.  Even into a memory file system, it's still slow.  If you are more careful and do probes less often, for example, profile 99 times per second,  the BPF trace approach is providing the least possible impact.  The fastest tracing is with BPF traces.  One of my examples presented here shown.  So is it so easy to use BPF trace?  Yes.  But you need to have a root of pseudo privilege.  In some cases even root.  You have to care to write your probe carefully to not write too much to the user land because  it impacts performance and not to abuse CPU and memory while you are working in can.  So aggregation still must be smart.  There are some tactical problems like I do not know how to add dynamic probe with BPF  trace to some line inside the function while it's perfectly doable with Perf.  It's doable just like the experience and had not studied it in detail.  There will be problems surely with C++, which all member functions is hardly traceable in  any way including BPF trace.  We will also need headers if we access complex structures and it may be not only kernel headers,  but header specific to the application software.  I had problems with that with trace and complex stuff.  Perf sometimes is easier to deal with.  You have to your binaries to be compiled with symbolic information and from read a bit may  mean installing additional debugging for packages or even building binaries from source.  Some of these problems are resolved recently by different way to build BPF binary source  called Core Compile 1s run everywhere system based also on BPF type format so that we have  a compact way to represent the symbolic information for the kernel for example to make programs  independent and we can do it probably for application software as well.  So the most important problem is the lack of real life experience with BPF trace and  the main reason is that customers do not yet use Linux kernel file.  So eventually we will be there.  It's the future.  It may be somewhat crazy to use application MariaDB with operating system profiling tools  that are usually associated with system administrators or performance engineers or kernel hackers,  but it really works.  As in case of open source software like MariaDB or MySQL there is no good reason not to try  to use these tools because you can study the source code and not just try things but be  sure what to code in your probes to end up with useful reason.  That's all.  Thank you very much.  I'm ready for your questions.  If you have found something that looks like a bug in MariaDB please report it.  Thank you again and bye.
mariadb_bpftrace_0_179.png mariadb_bpftrace_180_299.png mariadb_bpftrace_300_347.png mariadb_bpftrace_348_419.png mariadb_bpftrace_420_503.png mariadb_bpftrace_504_2039.png mariadb_bpftrace_2040_2567.png mariadb_bpftrace_2568_7643.png mariadb_bpftrace_7644_8915.png mariadb_bpftrace_8916_9635.png mariadb_bpftrace_9636_11003.png mariadb_bpftrace_11004_13607.png mariadb_bpftrace_13608_14687.png mariadb_bpftrace_14688_15059.png mariadb_bpftrace_15060_15479.png mariadb_bpftrace_15480_16355.png mariadb_bpftrace_16356_17627.png mariadb_bpftrace_17628_17699.png mariadb_bpftrace_17700_18215.png mariadb_bpftrace_18216_19247.png mariadb_bpftrace_19248_21227.png mariadb_bpftrace_21228_21347.png mariadb_bpftrace_21348_21467.png mariadb_bpftrace_21468_22691.png mariadb_bpftrace_22692_24371.png mariadb_bpftrace_24372_24911.png mariadb_bpftrace_24912_28019.png mariadb_bpftrace_28020_28871.png mariadb_bpftrace_28872_29219.png mariadb_bpftrace_29220_29478.png
Hi, my name is Valery Kravchuk and today I'm going to speak about using BPF Trace for monitoring and solving performance problems with MariaDB Server on Linux. I am a principal support engineer in MariaDB Corporation for five years and in this professional services industry around MySQL and MariaDB for 15-16 years soon actually. I used to work in MySQL, then Sun Oracle per corner. Since I quit from Oracle, I became a public person. I created a blog, a Twitter account, started to participate in conferences. Initially, I concentrated mostly in MySQL bugs, hence the names of my accounts, but in the process of work I have to share the way to solve problems and they are not always related to bugs, so there is a lot of how-to's. For historical reasons, all my slides that are always public for the conferences are kind of references, so whenever you see something underlined there, it's a link and you should follow it. You can find a lot of detailed information as a result. A word of warning while I am working for MariaDB Corporation, I am not in a mission from them here and I only speak for myself. The approach is used for troubleshooting the ideas, the conclusions from that are mine and may not be shared by my current or previous employees or partners. So what is the main problem for MariaDB DBA when there is some performance problem? It's to find out what's happening, actually, what is the reason. There are different sources of information, ways to trace and profile server, starting from debug binaries that developers prefer to use while working on bugs identified that adds a lot of tracing possibilities. They are not practical to use in production because they are slow. There are MySQL and MariaDB ways to find some of this information at SQL level. Different show statements appear at different times, different tables, for example, in the information scheme. Some of them are added by plugins or by additional tools like user stuff that is available in MariaDB and Perconos server and account separation per user, table, index, reads, writes, and all that stuff. There is extended slow query log that does not only register the time to execute the query but more details about it, like was it using temporary table, how much time was spent at specific stages and all that. Moreover, starting from MySQL 5.5, a separate performance schema was created based on the idea of instrumenting the code to collect performance-related information in the memory buffers that are recycled, reused, so it's a very efficient way to collect this information that then can be curated by SQL statements on performance schema. The idea is great, the code is instrumented more and more with every version and MariaDB tries to keep up with upstream, so also adding some features here from time to time. What else besides that? There are tools outside of MariaDB. Pre-writing system Linux in our case, the kernel, drivers, and all the application and system software is to be monitored and profiled. So one of the ideas that I like personally is to use OS level tracing and profiling tools. Historically, there were many tools starting from kernels 2.6, there is a system of tools from PROC file system with samples that can be checked there by reading files to soon edit F-trace file system and F-trace system that allows to add dynamic tracing anytime. A bit later, Perf profiler was added, allows to do a sampling profiling of the content of processors, for example, and access other performance counters. The last addition in case of Linux is so-called EBPF extended Berkeley packet filter system that added a separate flexible and still safe virtual machine for executing code inside the kernel in a safe and seamless manner. And a lot of tools were based on these frameworks, the last of them at the moment is BPF trace and I'm going to speak about it as well. So my idea is that all MariaDB DPAs that are working with modern Linux kernels kernel 5 whatever should be ready and should use BPF trace tool for troubleshooting. I will show some examples, discuss some problems, the main idea is that the performance impact of such a tracing if it's done in a smart way will be really small compared to anything else. So we can have a continuous monitoring and still tracing not just sampling of stuff we are interested in. I'm not going to speak about performance schema because it's covered elsewhere, I'm not going to speak about other tools for dynamic tracing on Linux again because it's covered elsewhere including my own talks previously at Fosden and other conferences. So BPF trace is one of those tools we see it on this chart that covers the entire stack starting from the application code to device drivers with kernel subsystems in between with access to performance counters, disk Ion network and all other systems. So just the reason of a set of such a global tools. So we are going to check it. All these tools tracing essentially that they do is doing something when some event happens. We need to know what events are traceable so we can classify them. There are events in the kernel, there are events in the application code. Some events appears only if the developer cared to instrument the code like it happens with performance schema in MySQL and my ADP by adding trace points. There are kernel trace points, there are trace points in the user code so called USDT, the Relent Statically Defined Tracing or detrace probes by detrace system on Solaris that introduced them first. Of all these I am mostly interested in dynamic tracing of kernel and specifically MariaDB. So my main point of interest is user probes. So what the EBPF system gives us? EBPF system allows us to create quite complex actions, to execute complex actions represented as a program for virtual machine that runs in kernel. It's LLVM. So whatever we can run LLVM that is safe enough to run in kernel, that can be the action when whatever event is happening. How is this working? We generate the bytecode for this virtual machine as a user. It then passed to the kernel and it's verified. This is an important thing that makes the entire process safe. This verification makes sure that we cannot access a memory that we should not access, that the execution of our program completes an infinite time, there are no endless loops and all that. And if it's so, the program is executed, if not the error is returned and it can access all kinds of events that can be traced, performance events and everything, write things back to the user space. But what's even more interesting is that it can collect the information in memory buffers at kernel context, so called maps or associative arrays. So we can aggregate the information there, collect how many times specific actions happened and have that index action as an index in our associative arrays and counter whatever else time as an element of that index. And export only the summary information, this is really great stuff. The EBPF system evolved with time, so there was at kernel four different versions, new and new features were added. But basically now we have a way to associate quite an arbitrary and powerful EBPF program bytecode for LLVM with all event sources that we already discussed, profiling information, hardware counters and more. With kernel five, we can do that for anything. That's great. Usually these days, unlike maybe 10 years ago, nobody is writing bytecode for LLVM directly, so we use some front end tools to generate this bytecode. It can be BCC tools, BPF compiler collection, or it can be BPF trace that does most of the work for us, allowing to just define the program as a set of probe action pairs, or maybe probe, filter, condition when action should be applied, action triples. So here on this picture, we can see different kinds of probes that we can define action for with BPF trace. They are dynamic tracing probes, user probe, probe that is executed when we return from the function, URED probe, static trace at user level, same at kernel level. There are special probes to run at the beginning and at the end of execution, for example, to provide some right information. There are kernel trace points, there are hardware counters, probes can be executed once in a while, there is a profiling profiler and all that stuff. So how to start with BPF trace? You have to install it if you use modern Linux, for example Fedora 33, just install BPF trace and all the dependencies, or you can build it from source. And then as soon as you can run BPF trace, run it with minus H, for example, option, and you will see the commands. So these commands, there are many in modern versions, two are most important. Minus L is the listing probes for a given template, so you can see which system calls you have as a trace point, for example. After finding out specific one, you can define the action for it. And yes, the most important is that you can provide the program, a set of so formed statements of the structure I've already presented. This for me looks more or less like AWK program. It's one of my favorite text processing languages, so it's quite familiar. The syntax is also resembling me a C language, printf is exactly C. There are built-in arguments, for example, call me the command that is executing, there are built-in functions and all that stuff. Go read the reference guide, or you can start, if you like to study by example, you can start by checking the content of the tool subdirectory at GitHub for the BPF trace. There are a lot of examples. The programs themselves are with BT file extension, and there are examples explaining the usage. I've studied several of them, some of them highlighted here, more highlighted in my blog post, mostly related to disk IE. And got a lot of hints and found out a lot of good ways to structure the program, built in functions approach. So studying programming by example is one of the techniques that can be applied. So based on that and my previous dynamic tracing attempts with Perf, I can easily do things, for example, like capturing queries with execution time. So for this, I need to know where the query is executed in the code of MariaDB, and I know it's a dispatch command function. The third argument is the query text, when I enter the function, the query processing begins essentially when I exit from it, the query is completed. So I've defined two probes for the MariaDB binary called MariaDBD. I've used my name here for a reason because I used to do that, even though at recent version it's not necessary. And there is yet another reason that is covered in my blog post. I was a bit lazy. So I've defined probe that runs when we start. I'm collecting SQL. And in another associative arrays, I am collecting starting times for the SQL when it's executed by specific thread. ThreadID is also an embedded built-in variable. When I get out of the function, I'm checking that I have started counting for specific thread and compiling the difference of time and present it in milliseconds. Here is the output. For example, for select sleep 4, I will sleep in exactly 4 seconds up to a millisecond precision. So that's cool. What else? I am usually required by my SQL developers in case of performance problems to provide stack traces. There is a built-in function useTack to provide the user stack trade. There is a key stack for kernel stack, and I can use VPS trace with profile probe as a performance profile. So here at 99 hertz, 99 times per second, I am checking that for the process named MariaDbd. I'm just printing user stack that I have for each of the threads. I'm dumping that. And this can be post-processed if you care to find out. You can summarize, count different stack traces. You can build flame graphs. There is an older block post that shows what is possible. More or less would PTPMP and other powerman profiles, the other perf with minus g option. Another request I sometimes get is not to sample but to trace specific function calls. And it can be not only calls inside MariaDB source code but system libraries calls like malloc or pthreadmutexloc in this case. You can trace that too. You just need to point out a probe will be in the shared library. Give name, know where the arguments are passed. In this case, I do not need arguments. I need user stack. How we ended up calling this function. And I am counting for each unique stack how many times it was executed because the request originally was to count how many times this happens. Know that for such often high frequency events, the performance impact is notable. Here we see like 50% drop in performance and more just because we are collecting very often and export a lot of raw traces basically to the user land. Even into a memory file system, it's still slow. If you are more careful and do probes less often, for example, profile 99 times per second, the BPF trace approach is providing the least possible impact. The fastest tracing is with BPF traces. One of my examples presented here shown. So is it so easy to use BPF trace? Yes. But you need to have a root of pseudo privilege. In some cases even root. You have to care to write your probe carefully to not write too much to the user land because it impacts performance and not to abuse CPU and memory while you are working in can. So aggregation still must be smart. There are some tactical problems like I do not know how to add dynamic probe with BPF trace to some line inside the function while it's perfectly doable with Perf. It's doable just like the experience and had not studied it in detail. There will be problems surely with C++, which all member functions is hardly traceable in any way including BPF trace. We will also need headers if we access complex structures and it may be not only kernel headers, but header specific to the application software. I had problems with that with trace and complex stuff. Perf sometimes is easier to deal with. You have to your binaries to be compiled with symbolic information and from read a bit may mean installing additional debugging for packages or even building binaries from source. Some of these problems are resolved recently by different way to build BPF binary source called Core Compile 1s run everywhere system based also on BPF type format so that we have a compact way to represent the symbolic information for the kernel for example to make programs independent and we can do it probably for application software as well. So the most important problem is the lack of real life experience with BPF trace and the main reason is that customers do not yet use Linux kernel file. So eventually we will be there. It's the future. It may be somewhat crazy to use application MariaDB with operating system profiling tools that are usually associated with system administrators or performance engineers or kernel hackers, but it really works. As in case of open source software like MariaDB or MySQL there is no good reason not to try to use these tools because you can study the source code and not just try things but be sure what to code in your probes to end up with useful reason. That's all. Thank you very much. I'm ready for your questions. If you have found something that looks like a bug in MariaDB please report it. Thank you again and bye.
MariaDB Server on Linux. I am a principal support for troubleshooting the ideas, the conclusions So one of the ideas that I like personally is to DPAs that are working with modern Linux kernels checking the content of the tool subdirectory at based on that and my previous dynamic tracing developers in case of performance problems to for application software as well. So the most