MariaDB Observability
Zaitsev, Peter
A broken MariaDB means broken Application, so maintaining insights in MariaDB operational performance is critical. Thankfully MariaDB offers a lot in terms of observability to resolve problems quickly and get great insights into opportunities for optimization. In this talk, we will cover the most important observability improvements in MariaDB ranging from Performance Schema and Information Schema to enhanced error logging and optimizer trace. If you're a Developer or DBA passionate about Observability or just want to be empowered to resolve MariaDB problems quickly and efficiently you should attend this talk.
 Hello everyone. Let's talk about MariaDB observability. And I'll just get going as we have a lot of material to cover and not a lot of time. Now why observability is important? Well, if you look at the modern systems, we are really dealing with a lot of complexity. And often we have many non-easier repeatable problems with performance and otherwise. And that means we cannot just repeat the problem in the development system with a lot of debugging enabled. We really need the production observability so we understand what went wrong, why, and how we can prevent that. If you look at the observability which is achieved through data capture, I actually typically see two use cases for that. Here we have an ongoing data capture which you have as a normal monitoring and that should come with relatively little overhead. Or we have a debugging which is a temporary data capture when you can use something wrong and maybe for a given session or given instance you enable much more verbose login and IOS instrumentation which can get expensive, can get us the more details and solve maybe more problems when you cannot solve just some more monitoring. Now it is also important as a background for observability is what you cannot just look at MariaDB alone because the rating system issues, hardware issues would be often the root cause of your problems. You cannot also ignore application issues or various other background issues like in cloud virtualized environments maybe it is not the neighbors which is giving you trouble and not your application itself. Well the fact very brief intro in why observability is important let's talk about MariaDB. If you think about MariaDB, those six are the most important information data sources which exist in MariaDB of the modern versions. First one is show global status or show status. This is something which existed forever and it shows more than 500 status variables. Majority of them are counters though some of them are gauges or text and they can be session and global in a scope. They also can be both queried through the show status command as well as queried from information schema table. Here is an example querying them from information schema table and also looking at the questions which is number of queries right database protest in a global and a session scope. In a global scope you can see the number is very large because that instance has been running for a long time and processed a lot of queries though if you look at the session scope it's just about slightly more than 100 that's how many queries we're giving session I am running executed right not that many. Now though what even if you query session status as this example you can get some data which comes from the global variables right like in a DB row counters they are global they're not kind of not shown for session and global separately right but they will be shown if you query session though or in a global scope right so you have to just know that to avoid being being confused right because interpreting them as if there would be indeed session variables can lead you astray. If you would like to get the output from something similar to being start you can use this this comment which pretty much will print out one of the variables in this case question as with one second increment I found that as a very helpful tool if you really need to focus on one variable and zero out what is going on in in your production. Moving on to information schema information schema contains lots of tables as some of them are going to be schema related and others are performance statistics here is example of information about your state your schema like you can query tables table and see information about your tables and and views. If you look at example of a performance statistics in a D-Metrics table is a great information and in a D-Metrics is something which is a very similar to show status but is focused on in a DB tables and it has some additional information like you can see it can be used mean and max counters as information about when it was last reset and so on and so forth. It is worth to note what by default many of those in a DB variables are not enabled and if you want to look at them all you can use energy monitor enable all or there is also options to enable only only something. Another table which exists in this information schema table is energy in mutixes which is a very cool table which allows us to show what kind of mutixes are having a lot of OS weights and I think that can be very helpful for some of the advanced diagnostics when you have a mutics content. Next thing what you can see from information schema is this process list where you can think about that as an extended process list. As you can see in this case we don't only have information about running queries and with time with microsecond resolution but also information about the statement progress and applicable or how much memory it used and so on and so forth which is pretty cool. The next data source to mention is performance schema and performance schema is very some of the most advanced instrumentation it's available. It has more than 80 tables right so lots and lots of data right and the thing to note though it's disabled by default in MariaDB because it comes with some overhead and it is also very flexible in terms of how much instrumentation and so how much overhead you have and if you enable a lot of instrumentation the overhead may be quite high. If you want to enable performance schema in MariaDB you can add this setting to your MariaDB configuration file and restart. Unfortunately while a lot of performance schema configuration can be dynamic enabling it requires a restart. If you look at performance schema configuration it's kind of tricky if you will but the good thing is what defaults should work most well for most use cases. We can see what there are a number of SQL tables which can be used for configuration with SQL language and also there are common line settings which are similar to those which is important because those tables are ephemeral right when your instance starts the configuration will be lost. So what are configuration settings to give in mind? The actors allows us to configure which users should be instrumented and typically you keep it to instrument all the users. Consumers is what kind of summaries are going to be built and I highlighted here in red this transaction summaries which I'm new in MariaDB 10.5 that is one of their great work which has been done on performance schema in MariaDB 10.5. The second poor configuration is the instruments which basically responds to the instrumentation points events which are going to be counted and timed. In MariaDB there is almost a thousand instrumentation points as of MariaDB 10.5.8 and almost 300 of them are timed and instrumented so you can get a lot of details right by default and you can configure even more of them. Additionally you can configure the objects which tells you pretty much about the database objects such as tables, store procedures, triggers which you want to implement which are all configured through that setup for object stable. So let's look at example of performance schema. In this case we're looking at tables called event current statements current which shows us all the or shows us the current tables and in this case well this is the statement we are looking for we can see information about when it was started, when it's ended, right and you know a whole bunch of other stuff like how many rows it's sent and so on so which can be very helpful on information. Another example could be looking at a file summary. In this case it tells us information about their inadivid data file or actually all the files which share the type of being in a dv data file and we can see how many reads, writes, how many events in total, how many miscellaneous events happen to this file. If miscellaneous when it comes to IO typically being fc and c operation as well as times. Now if you notice in this case the times are kind of some bizarre long numbers and this is before because MariaDB by default, MariaDB performance schema uses a picosecond to measure the time and a picosecond is one thousandth of a nanosecond so that is a very, very exact diamond, right, very high resolution which performance schema uses but which you need to convert to anything which is meaningful. Okay as I mentioned in MariaDB there have been a lot of work done to improve performance schema. In MariaDB 10.5 performance schema roughly matches mySquare.5.SAM performance schema so things like memory instrumentation, metadata login, prepared statements, stored procedure, transactions and user variables have been have been added so that is pretty cool, right, that means some of the most usable features of performance schema are now available in MariaDB. Okay let's move on and look at some additional data sources which is very valuable for instrumentation. It's with the error log, general log and query log, obviously our logs. When you look at the error log you have a lot of flexibility out there, you can send it to the files or by default it goes to the standard systemd.general right from which you can query that. If you look at the slug query log which would log the queries, the execution time, in this regard it's actually much more useful than the query log. Over recent times I find query log is only usable for debugging purposes when MariaDB crashes before query execution is complete. In this case you would often find the query in the general query log before it's logged before execution but it would not be in let's say slug query log. For other purposes slug query log has a lot more valuable information because the query is logged after it executed so you will have execution timing as well as other wonderful data. You can configure slug query log to log only queries which are slow or you can configure that you can configure that to log over queries and especially in the systems where you don't have a lot of queries often in the development system, logging over queries and analyzing them with some tools really gives you the most detail. Though if you have tens of thousands of hundreds of thousands of queries a second, logging every single query becomes way too extensive. This is example of a slug query log with explain which is very cool and especially when it's configured for some login only long queries because in this case you can really get exact explains of the queries which have been slow. So if you look to mention a few words about explain in particular, if you're not familiar with explain please google. Explain is a very valuable tool with both database developers and DBAs need to know. It's really healthy to understand how query is executed. You really want to make sure what your queries are executed correctly using the right indexes and so on and so forth because if they don't you may be putting a lot more load on database when you should. Sometimes hundreds even thousand times more and queries which do not have been optimized well can really affect your database performance very poorly. Explain in MariaDB has multiple output formats you can use. You can also run explain for currently running query. For example you find the query running for some reason very slow already for 15 minutes. You can just run explain on it. Here is your standard explain which is outputs as a table but there are also some advanced explain features. Like I mentioned you can get explain output in JSON format. You can use show explain for connection. In this case you will get the connection ID and then there is also analyze which is a not called explain but it is also a way to understand query performance which would run the query itself but it also will provide you information about how that query was actually executed. Where explain uses estimates about query execution the analyze actually tells you what has happened. Here is example of the explain analyze and you can see in this case you can see actual number of rows, actual number of rows filter and so on and so forth. That is a pretty cool feature. Now this is all good but if you are looking for some more of visualization of this availability data from MariaDB I would encourage you to check out the corner monitoring management. This is the tool which we have developed at the corner that is completely open source solutions for number of open source databases and it is particularly focused on really helping developers and DBAs to find the queries which are running poorly and optimize their performance. With that little plug that's all I have for you and now I would be happy to answer your questions. Thank you.
 Hello everyone. Let's talk about MariaDB observability. And I'll just get going as we have a lot of  material to cover and not a lot of time. Now why observability is important? Well, if you look at  the modern systems, we are really dealing with a lot of complexity. And often we have many non-easier  repeatable problems with performance and otherwise. And that means we cannot just  repeat the problem in the development system with a lot of debugging enabled. We really need  the production observability so we understand what went wrong, why, and how we can prevent that.  If you look at the observability which is achieved through data capture, I actually typically see  two use cases for that. Here we have an ongoing data capture which you have as a normal monitoring and  that should come with relatively little overhead. Or we have a debugging which is a temporary data  capture when you can use something wrong and maybe for a given session or given instance you  enable much more verbose login and IOS instrumentation which can get expensive,  can get us the more details and solve maybe more problems when you cannot solve just some  more monitoring. Now it is also important as a background for observability is what you cannot just  look at MariaDB alone because the rating system issues, hardware issues would be often the root  cause of your problems. You cannot also ignore application issues or various other background  issues like in cloud virtualized environments maybe it is not the neighbors which is giving you  trouble and not your application itself. Well the fact very brief intro in why observability is  important let's talk about MariaDB. If you think about MariaDB, those six are the most  important information data sources which exist in MariaDB of the modern versions.  First one is show global status or show status. This is something which existed forever and it shows  more than 500 status variables. Majority of them are counters though some of them are gauges or  text and they can be session and global in a scope. They also can be both queried through  the show status command as well as queried from information schema table. Here is an example  querying them from information schema table and also looking at the questions which is number of  queries right database protest in a global and a session scope. In a global scope you can see  the number is very large because that instance has been running for a long time and processed a lot  of queries though if you look at the session scope it's just about slightly more than 100  that's how many queries we're giving session I am running executed right not that many. Now though  what even if you query session status as this example you can get some data which comes from  the global variables right like in a DB row counters they are global they're not kind of not  shown for session and global separately right but they will be shown if you query session though  or in a global scope right so you have to just know that to avoid being being confused right  because interpreting them as if there would be indeed session variables can lead you astray.  If you would like to get the output from something similar to being start you can use  this this comment which pretty much will print out one of the variables in this case question  as with one second increment I found that as a very helpful tool if you really need to focus on  one variable and zero out what is going on in in your production. Moving on to information schema  information schema contains lots of tables as some of them are going to be schema related  and others are performance statistics here is example of information about your state your  schema like you can query tables table and see information about your tables and and views.  If you look at example of a performance statistics in a D-Metrics table is a great information and  in a D-Metrics is something which is a very similar to show status but is focused on in a DB  tables and it has some additional information like you can see it can be used mean and max counters  as information about when it was last reset and so on and so forth. It is worth to note what  by default many of those in a DB variables are not enabled and if you want to look at them all  you can use energy monitor enable all or there is also options to enable only only something.  Another table which exists in this information schema table is energy in mutixes which is a very  cool table which allows us to show what kind of mutixes are having a lot of OS weights and I  think that can be very helpful for some of the advanced diagnostics when you have a mutics  content. Next thing what you can see from information schema is this process list where you  can think about that as an extended process list. As you can see in this case we don't only have  information about running queries and with time with microsecond resolution but also information  about the statement progress and applicable or how much memory it used and so on and so forth which is  pretty cool. The next data source to mention is performance schema and performance schema is  very some of the most advanced instrumentation it's available. It has more than 80 tables right so  lots and lots of data right and the thing to note though it's disabled by default in MariaDB  because it comes with some overhead and it is also very flexible in terms of how much instrumentation  and so how much overhead you have and if you enable a lot of instrumentation the overhead  may be quite high. If you want to enable performance schema in MariaDB you can add this setting to your  MariaDB configuration file and restart. Unfortunately while a lot of performance  schema configuration can be dynamic enabling it requires a restart. If you look at performance  schema configuration it's kind of tricky if you will but the good thing is what defaults should  work most well for most use cases. We can see what there are a number of SQL tables which can be used  for configuration with SQL language and also there are common line settings which are similar  to those which is important because those tables are ephemeral right when your instance starts  the configuration will be lost. So what are configuration settings to give in mind? The  actors allows us to configure which users should be instrumented and typically you keep it to  instrument all the users. Consumers is what kind of summaries are going to be built and I highlighted  here in red this transaction summaries which I'm new in MariaDB 10.5 that is one of their  great work which has been done on performance schema in MariaDB 10.5. The second poor configuration  is the instruments which basically responds to the instrumentation points events which are going to  be counted and timed. In MariaDB there is almost a thousand instrumentation points as of MariaDB  10.5.8 and almost 300 of them are timed and instrumented so you can get a lot of details  right by default and you can configure even more of them. Additionally you can configure the  objects which tells you pretty much about the database objects such as tables, store procedures,  triggers which you want to implement which are all configured through that setup for object stable.  So let's look at example of performance schema. In this case we're looking at tables called event  current statements current which shows us all the or shows us the current tables and in this case  well this is the statement we are looking for we can see information about when it was started,  when it's ended, right and you know a whole bunch of other stuff like how many rows it's sent  and so on so which can be very helpful on information. Another example could be looking at  a file summary. In this case it tells us information about their inadivid data file or actually all  the files which share the type of being in a dv data file and we can see how many reads, writes,  how many events in total, how many miscellaneous events happen to this file. If miscellaneous  when it comes to IO typically being fc and c operation as well as times. Now if you notice in  this case the times are kind of some bizarre long numbers and this is before because MariaDB  by default, MariaDB performance schema uses a picosecond to measure the time and a picosecond  is one thousandth of a nanosecond so that is a very, very exact  diamond, right, very high resolution which performance schema uses but which you need to  convert to anything which is meaningful. Okay as I mentioned in MariaDB there have been a lot of  work done to improve performance schema. In MariaDB 10.5 performance schema roughly matches  mySquare.5.SAM performance schema so things like memory instrumentation, metadata login,  prepared statements, stored procedure, transactions and user variables have been  have been added so that is pretty cool, right, that means some of the most usable features  of performance schema are now available in MariaDB. Okay let's move on and look at some  additional data sources which is very valuable for instrumentation. It's with the error log,  general log and query log, obviously our logs. When you look at the error log you have a lot of  flexibility out there, you can send it to the files or by default it goes to the standard  systemd.general right from which you can query that. If you look at the slug query log  which would log the queries, the execution time, in this regard it's actually much more  useful than the query log. Over recent times I find query log is only usable for debugging purposes  when MariaDB crashes before query execution is complete. In this case you would often find the  query in the general query log before it's logged before execution but it would not be in let's say  slug query log. For other purposes slug query log has a lot more valuable information because the  query is logged after it executed so you will have execution timing as well as other wonderful data.  You can configure slug query log to log only queries which are slow or you can configure that  you can configure that to log over queries and especially in the systems where you don't have  a lot of queries often in the development system, logging over queries and analyzing them with  some tools really gives you the most detail. Though if you have tens of thousands of hundreds of  thousands of queries a second, logging every single query becomes way too extensive. This is  example of a slug query log with explain which is very cool and especially when it's configured for  some login only long queries because in this case you can really get exact explains of the queries  which have been slow. So if you look to mention a few words about explain in particular, if you're  not familiar with explain please google. Explain is a very valuable tool with both database  developers and DBAs need to know. It's really healthy to understand how query is executed.  You really want to make sure what your queries are executed correctly using the right indexes  and so on and so forth because if they don't you may be putting a lot more load on database when  you should. Sometimes hundreds even thousand times more and queries which do not have been  optimized well can really affect your database performance very poorly. Explain in MariaDB  has multiple output formats you can use. You can also run explain for currently running query.  For example you find the query running for some reason very slow already for 15 minutes. You can  just run explain on it. Here is your standard explain which is outputs as a table but there are  also some advanced explain features. Like I mentioned you can get explain output in JSON  format. You can use show explain for connection. In this case you will get the connection ID and  then there is also analyze which is a not called explain but it is also a way to understand  query performance which would run the query itself but it also will provide you information  about how that query was actually executed. Where explain uses estimates about query execution  the analyze actually tells you what has happened. Here is example of the explain analyze and you  can see in this case you can see actual number of rows, actual number of rows filter and so on  and so forth. That is a pretty cool feature. Now this is all good but if you are looking for  some more of visualization of this availability data from MariaDB I would encourage you to check  out the corner monitoring management. This is the tool which we have developed at the corner  that is completely open source solutions for number of open source databases and it is particularly  focused on really helping developers and DBAs to find the queries which are running poorly and  optimize their performance. With that little plug that's all I have for you and now I would be happy  to answer your questions. Thank you.
mariadb_observability_0_179.png mariadb_observability_180_443.png mariadb_observability_444_1319.png mariadb_observability_1320_1367.png mariadb_observability_1368_2507.png mariadb_observability_2508_3347.png mariadb_observability_3348_3539.png mariadb_observability_3540_3887.png mariadb_observability_3888_4643.png mariadb_observability_4644_5555.png mariadb_observability_5556_6683.png mariadb_observability_6684_7559.png mariadb_observability_7560_7895.png mariadb_observability_7896_8351.png mariadb_observability_8352_9119.png mariadb_observability_9120_9623.png mariadb_observability_9624_10367.png mariadb_observability_10368_11183.png mariadb_observability_11184_12215.png mariadb_observability_12216_12647.png mariadb_observability_12648_13691.png mariadb_observability_13692_14111.png mariadb_observability_14112_14735.png mariadb_observability_14736_15623.png mariadb_observability_15624_16163.png mariadb_observability_16164_17087.png mariadb_observability_17088_18995.png mariadb_observability_18996_19979.png mariadb_observability_19980_20903.png mariadb_observability_20904_23063.png mariadb_observability_23064_23615.png mariadb_observability_23616_25331.png mariadb_observability_25332_25583.png mariadb_observability_25584_26639.png mariadb_observability_26640_26783.png mariadb_observability_26784_26939.png mariadb_observability_26940_27311.png mariadb_observability_27312_28007.png mariadb_observability_28008_28367.png mariadb_observability_28368_28583.png mariadb_observability_28584_28984.png
Hello everyone. Let's talk about MariaDB observability. And I'll just get going as we have a lot of material to cover and not a lot of time. Now why observability is important? Well, if you look at the modern systems, we are really dealing with a lot of complexity. And often we have many non-easier repeatable problems with performance and otherwise. And that means we cannot just repeat the problem in the development system with a lot of debugging enabled. We really need the production observability so we understand what went wrong, why, and how we can prevent that. If you look at the observability which is achieved through data capture, I actually typically see two use cases for that. Here we have an ongoing data capture which you have as a normal monitoring and that should come with relatively little overhead. Or we have a debugging which is a temporary data capture when you can use something wrong and maybe for a given session or given instance you enable much more verbose login and IOS instrumentation which can get expensive, can get us the more details and solve maybe more problems when you cannot solve just some more monitoring. Now it is also important as a background for observability is what you cannot just look at MariaDB alone because the rating system issues, hardware issues would be often the root cause of your problems. You cannot also ignore application issues or various other background issues like in cloud virtualized environments maybe it is not the neighbors which is giving you trouble and not your application itself. Well the fact very brief intro in why observability is important let's talk about MariaDB. If you think about MariaDB, those six are the most important information data sources which exist in MariaDB of the modern versions. First one is show global status or show status. This is something which existed forever and it shows more than 500 status variables. Majority of them are counters though some of them are gauges or text and they can be session and global in a scope. They also can be both queried through the show status command as well as queried from information schema table. Here is an example querying them from information schema table and also looking at the questions which is number of queries right database protest in a global and a session scope. In a global scope you can see the number is very large because that instance has been running for a long time and processed a lot of queries though if you look at the session scope it's just about slightly more than 100 that's how many queries we're giving session I am running executed right not that many. Now though what even if you query session status as this example you can get some data which comes from the global variables right like in a DB row counters they are global they're not kind of not shown for session and global separately right but they will be shown if you query session though or in a global scope right so you have to just know that to avoid being being confused right because interpreting them as if there would be indeed session variables can lead you astray. If you would like to get the output from something similar to being start you can use this this comment which pretty much will print out one of the variables in this case question as with one second increment I found that as a very helpful tool if you really need to focus on one variable and zero out what is going on in in your production. Moving on to information schema information schema contains lots of tables as some of them are going to be schema related and others are performance statistics here is example of information about your state your schema like you can query tables table and see information about your tables and and views. If you look at example of a performance statistics in a D-Metrics table is a great information and in a D-Metrics is something which is a very similar to show status but is focused on in a DB tables and it has some additional information like you can see it can be used mean and max counters as information about when it was last reset and so on and so forth. It is worth to note what by default many of those in a DB variables are not enabled and if you want to look at them all you can use energy monitor enable all or there is also options to enable only only something. Another table which exists in this information schema table is energy in mutixes which is a very cool table which allows us to show what kind of mutixes are having a lot of OS weights and I think that can be very helpful for some of the advanced diagnostics when you have a mutics content. Next thing what you can see from information schema is this process list where you can think about that as an extended process list. As you can see in this case we don't only have information about running queries and with time with microsecond resolution but also information about the statement progress and applicable or how much memory it used and so on and so forth which is pretty cool. The next data source to mention is performance schema and performance schema is very some of the most advanced instrumentation it's available. It has more than 80 tables right so lots and lots of data right and the thing to note though it's disabled by default in MariaDB because it comes with some overhead and it is also very flexible in terms of how much instrumentation and so how much overhead you have and if you enable a lot of instrumentation the overhead may be quite high. If you want to enable performance schema in MariaDB you can add this setting to your MariaDB configuration file and restart. Unfortunately while a lot of performance schema configuration can be dynamic enabling it requires a restart. If you look at performance schema configuration it's kind of tricky if you will but the good thing is what defaults should work most well for most use cases. We can see what there are a number of SQL tables which can be used for configuration with SQL language and also there are common line settings which are similar to those which is important because those tables are ephemeral right when your instance starts the configuration will be lost. So what are configuration settings to give in mind? The actors allows us to configure which users should be instrumented and typically you keep it to instrument all the users. Consumers is what kind of summaries are going to be built and I highlighted here in red this transaction summaries which I'm new in MariaDB 10.5 that is one of their great work which has been done on performance schema in MariaDB 10.5. The second poor configuration is the instruments which basically responds to the instrumentation points events which are going to be counted and timed. In MariaDB there is almost a thousand instrumentation points as of MariaDB 10.5.8 and almost 300 of them are timed and instrumented so you can get a lot of details right by default and you can configure even more of them. Additionally you can configure the objects which tells you pretty much about the database objects such as tables, store procedures, triggers which you want to implement which are all configured through that setup for object stable. So let's look at example of performance schema. In this case we're looking at tables called event current statements current which shows us all the or shows us the current tables and in this case well this is the statement we are looking for we can see information about when it was started, when it's ended, right and you know a whole bunch of other stuff like how many rows it's sent and so on so which can be very helpful on information. Another example could be looking at a file summary. In this case it tells us information about their inadivid data file or actually all the files which share the type of being in a dv data file and we can see how many reads, writes, how many events in total, how many miscellaneous events happen to this file. If miscellaneous when it comes to IO typically being fc and c operation as well as times. Now if you notice in this case the times are kind of some bizarre long numbers and this is before because MariaDB by default, MariaDB performance schema uses a picosecond to measure the time and a picosecond is one thousandth of a nanosecond so that is a very, very exact diamond, right, very high resolution which performance schema uses but which you need to convert to anything which is meaningful. Okay as I mentioned in MariaDB there have been a lot of work done to improve performance schema. In MariaDB 10.5 performance schema roughly matches mySquare.5.SAM performance schema so things like memory instrumentation, metadata login, prepared statements, stored procedure, transactions and user variables have been have been added so that is pretty cool, right, that means some of the most usable features of performance schema are now available in MariaDB. Okay let's move on and look at some additional data sources which is very valuable for instrumentation. It's with the error log, general log and query log, obviously our logs. When you look at the error log you have a lot of flexibility out there, you can send it to the files or by default it goes to the standard systemd.general right from which you can query that. If you look at the slug query log which would log the queries, the execution time, in this regard it's actually much more useful than the query log. Over recent times I find query log is only usable for debugging purposes when MariaDB crashes before query execution is complete. In this case you would often find the query in the general query log before it's logged before execution but it would not be in let's say slug query log. For other purposes slug query log has a lot more valuable information because the query is logged after it executed so you will have execution timing as well as other wonderful data. You can configure slug query log to log only queries which are slow or you can configure that you can configure that to log over queries and especially in the systems where you don't have a lot of queries often in the development system, logging over queries and analyzing them with some tools really gives you the most detail. Though if you have tens of thousands of hundreds of thousands of queries a second, logging every single query becomes way too extensive. This is example of a slug query log with explain which is very cool and especially when it's configured for some login only long queries because in this case you can really get exact explains of the queries which have been slow. So if you look to mention a few words about explain in particular, if you're not familiar with explain please google. Explain is a very valuable tool with both database developers and DBAs need to know. It's really healthy to understand how query is executed. You really want to make sure what your queries are executed correctly using the right indexes and so on and so forth because if they don't you may be putting a lot more load on database when you should. Sometimes hundreds even thousand times more and queries which do not have been optimized well can really affect your database performance very poorly. Explain in MariaDB has multiple output formats you can use. You can also run explain for currently running query. For example you find the query running for some reason very slow already for 15 minutes. You can just run explain on it. Here is your standard explain which is outputs as a table but there are also some advanced explain features. Like I mentioned you can get explain output in JSON format. You can use show explain for connection. In this case you will get the connection ID and then there is also analyze which is a not called explain but it is also a way to understand query performance which would run the query itself but it also will provide you information about how that query was actually executed. Where explain uses estimates about query execution the analyze actually tells you what has happened. Here is example of the explain analyze and you can see in this case you can see actual number of rows, actual number of rows filter and so on and so forth. That is a pretty cool feature. Now this is all good but if you are looking for some more of visualization of this availability data from MariaDB I would encourage you to check out the corner monitoring management. This is the tool which we have developed at the corner that is completely open source solutions for number of open source databases and it is particularly focused on really helping developers and DBAs to find the queries which are running poorly and optimize their performance. With that little plug that's all I have for you and now I would be happy to answer your questions. Thank you.
and otherwise. And that means we cannot just observability is important let's talk about are the most important information data sources which exist in MariaDB of the modern versions. overhead and it is also very flexible in terms of If you want to enable performance schema in configure which users should be instrumented and instrumented so you can get a lot of details can use show explain for connection. In this case