Hello, everyone, could you hear me well?
How do you feel today, Sunday, the fourth day?
Did you wake up with energy?
Or you are like...
We have energy, right?
Okay, that's good.
I'm Ida Pugja from Peru.
I traveled to Europe for the first time for this year.
It's the first time also doing a talk in English.
So if I make some mistake, I hope you can understand this.
And I am a technology evangelist at Percona.
I started six months ago.
It's a really great company focusing on databases.
And if you want to follow me on Twitter or want to connect,
I will be really happy by LinkedIn.
So I used to publish things about open source containers,
Kubernetes.
About me, I am from Peru.
I am a Google woman tech maker.
I was nominated as a docker captain the last year.
And I am a container and database enthusiast.
So this talk is about monitoring your database with open source tools.
We are going to focus in PMM.
How many of you did you hear about PMM before?
Percona monitoring and management.
Okay, so it's something new for this room.
And this is a perspective of a beginner view.
So this is not something advanced.
I will see this monitoring tool as a perspective,
because I am learning about databases in the company
where I am working, Percona.
So we are going to evaluate why it's important the value of monitoring databases.
Also, we will see PMM, Percona monitoring and management,
and how we can effectively see the dashboards and graphs
that we can get to monitor and manage our databases.
All this time I was working in a database company.
I asked myself, and I realized the importance is to monitor databases
because we can just have one database running for us.
We can have several of them.
We can have it in cloud, in infra.
So it's very important, and it's why we should ask
why we should care about databases,
why we should care about monitoring these databases.
And we can ask most questions like,
is my database performing well?
So as we start to work with databases,
there are several queries that we make.
So this queries maybe is not executing in the time that we expected.
It could be taking more time,
and it's going to have bottleneck in the time that we are executing.
So we should care about this
and know that there are metrics that we should detect
the problem for the performance of our databases.
Another question that could ask ourselves is,
are databases available and accepting connections?
We can have several databases,
and many connections could be made,
but if we don't put a limit in these connections,
it could just crash.
But if we put a limit also,
we should be aware when we are achieving
or we are reaching that limit to increase
or maybe to stop the connection.
But if we pass the limit,
so this is going to be a problem in our databases, right?
If this is an e-commerce company,
it can happen because the user is going to wait just two seconds
or maybe one second to go to the next page faster.
But if no, if we did this in three seconds,
five seconds that the user is going to go to another page
and we lost as a business.
Is my sister estable?
We also monitor the infrastructure where our database is running,
not just the databases,
because it is running over an infrastructure.
We are using CPUs, memory, this,
and we are not able to provision these resources on time.
We will be in problem for our databases.
I am having avoidable time,
so if our hardware is not enough,
so our application could crash
and we can have hardware failures
or network outage.
We should be aware of these metrics to avoid these problems.
So we also can have human errors or these crashes.
So there are metrics that we can see to identify these before.
But we can just see what are the problems
when we are having those problems.
We can also prevent these problems,
asking these questions.
I am minimising performance issues that can impact my business.
I am able to identify these issues before they happen
because there is a way to prevent.
As a previous example, if we are reaching the limit,
we can see it before we are reaching it.
So we can prevent provisioning more resources,
maybe checking the query that we already saw
that is taking too much time executing.
So there are ways we can prevent these problems.
Nowadays, there are challenges that we have
when we think to monitor databases.
For example, the data volume grow.
We don't talk about gigabytes now.
We are talking about terabytes or maybe more
because the database has a lot.
It's a challenge to monitor these days.
The complexity of the model databases.
Right now, it's SQL databases, not SQL databases.
The database has different models.
It could run in different cloud providers.
It could have different models.
So the complexity just grow.
Now it's different than before.
The downtime and data loss.
So it's one race that we should try to monitor to prevent it.
Lack of visibility.
If we don't have these things ready to be, to check it,
if we have to do another thing, like maybe create scripts,
Linux script or bash script to check it
and to get this metrics,
we don't have that data on time.
We don't have that metrics on time.
But if we have that visibility,
it's going to be easy for us to detect these problems
to monitor our databases.
Even better, if we have everything in one single dashboard
where we can visualize it.
I learned fatigue.
We can monitor many things.
But with this, we have different databases.
They can have MySQL, they can have other kind of databases.
So it depends on the business, what we want to monitor,
what metrics we are going to get to monitor.
But if we don't know in all these things that we have for databases,
we are going to get a lot of things that maybe we don't need
and create alerts for maybe things that we really don't need.
We are not focusing in that business exactly.
We should try to focus to monitor, we should try to focus in the business.
What's the metrics that are important for us, for our business?
Integration with other tools.
So with that time, we can do continuous integration,
continuous delivery things.
So it increases also the complexity to monitor our databases
because this is not in a single place.
This go over a process that is also devolved.
These things are being automated.
Now that we know why we should care about monitor databases,
we will talk about one of the solutions that could help us to monitor.
This is Percona monitoring and management, which is PMM.
This is an open source tools and free tools,
also based in other open source tools like MySQL.
This led us to monitor databases like MySQL, MariaDB,
PostgreSQL, AmongoDB, but not just that.
As I said before, this also led us to monitor the infrastructure
where our database is running.
It's important to know about that.
And it also helped us to performance our databases
to simplify the management of these databases
and we can exchange the security.
Percona monitoring and management is built on top of other open source tools
like Grafana.
I know many of you use Grafana, right?
Who use Grafana?
Okay, a lot.
And Victoria metrics also to storage this data,
that metrics we collect for a long time.
We are using clickhose to create these reports in real time
with all these metrics that we collect in the time.
We are using PostgreSQL to storage all the metadata
and all these metrics for databases,
all the important data that we have in PMM.
And everything that we visualize is saved in this database.
And we use Docker to install PMM.
We can containerize the installation of PMM, the client and the server,
and use it in different platforms.
We also use Kubernetes operators for scaling our databases.
There are three levels of deep when we talk about PMM interface.
This is the big one, which is a dashboard that we all know,
but we can go deeper and see the graphs,
that is a graphical representation of the metrics in long period of time.
And we have the metrics also, which is a countable number
that represents some value, some important value
of our infrastructure of our database.
Okay, as I tell you before, what we want to monitor
is going to depend of our business.
We are not going to monitor the same metrics as another business.
It depends a lot on that.
And we also should aware of the alerts that we create
that should be focused on what we do as a business
and create this alertness and notifications
that could be notified when we need it.
So it could be integrated with Slack,
with many other tools that we have in the hand
to know when the problem is happening exactly.
Some important metrics, some of that that we can check with PMM,
is query performance, high CPU, high memory usage,
and this high disk part of the infrastructure,
the amount of user connections.
We can know when the data grows
and other kind of metrics that we can have.
Could somebody tell me what other kind of metrics we can check with PMM?
With some monitoring tool?
A part of the infrastructure or...
Okay, we'll check.
If we see the long query response times,
as I say, some queries could take some time,
PMM has a very good dashboard, which is this, is Khan,
query analytics dashboard,
where we can see for a specific...
We can see all the queries for our databases.
In this case, I'm seeing all the 10 top queries
that is running in our databases,
but also we have an option to check it here
if we want to just check for MySQL, for Postgres, or MongoDB,
and we will see the amount of queries per second
for example that we are running and how time is taking.
So if we open this dashboard when we are working in databases,
the first thing that we are going to see is,
okay, this query is taking too much time.
In this case, no, but we have a query that is taking too much time,
or it's running a lot of queries per second.
We can see the first one,
and we can start troubleshooting from that point.
The high CPU utilization is part of the infrastructure.
Also, it's important to know how is this going.
For example, this dashboard in PMA,
in Percona Monitoring and Management,
we can see for a specific note,
we can check a note because we can have different notes running
in our infrastructure,
and in this case, for example,
we have 25% of our disk that is using.
This may be not a good example because I checked last 12 hours,
but we can check our disk usage during six months or more,
and then it's when we can see and take decisions.
Let's see if this is like six months.
We are using just 25% of our disk.
It could be a problem because we have a lot of infrastructure
that we are not using and we are wasting money
because of this space that we are not using in six months.
We can reduce our CPU and save money.
High memory usage is this dashboard where we can see the amount
of memory that I have for my databases
and also can see what is using for Kakache,
what has been using, what is going to be ready to be free,
and this is good because we can also see
when we are reaching the limit of the memory
and we can take actions to provisioning another disk.
We can say that this is very easy when we are working in cloud
because just we click in a button and say,
hey, provision another disk or increment the memory,
but if we are working in infra, maybe in a private cloud,
this is hard because we have to prepare the logistics
to get another disk, another memory is going to take time,
and have this kind of visualization helps.
The amount of input or output that we make in our disk,
you also can check it in this graph.
We can see that your latency here,
in this case, is stable,
but we can have peaks to see where we are detecting these problems.
User connections, as I say,
it helps to monitor the number of active database connections
and size it appropriately,
and also put limits in our connections for our databases.
We have, in this case, MySQL connections.
We can see that you are for other databases too,
but in this case, it's also stable.
We are going to have peaks when we can see
that we are working with a lot of transactions on our databases,
and we can take actions with that.
The maximum of connections allowed is 151.
We are in 150.
If this is going to be 151, this is going to alert us.
You have to check this.
The data grow also.
We can see a dashboard where we can...
Where you can see where our data is a lot
when we are inserting a lot of data in our databases,
or we are just removing things.
In this case, it's going to show when my databases start,
and it's not like too good to see it here,
but if I have time, yes, I still have time.
I still have time, right?
To show something, to show the dashboard.
I have time.
If you want to try it right now,
we can check this PMM demo per con graph.
You can enter.
Right now, we are going to check the dashboard,
but what we learned now, it was some aspects
that let me think what we should keep away
and monitoring our databases,
and also how to explore PMM,
which is an open source tool, is available there.
You have to double down and start to check it and explore it,
and it's easy to visualize things,
so we are going to check now PMM.
You can also enter to this link, so it's free to experiment.
Let's see if this is going to work.
Yes, this is the dashboard that we have.
We have several nodes that we can choose here.
A lot of them, so it depends on the database.
We have nodes of MongoDB, MySQL, Postgres,
and MySQL proxy also.
We can check the details for a specific time.
12 hours is not enough, maybe, for some things,
but maybe six months, three months,
we have a lot of things here.
So we also can check things about the system operator.
I don't have access right now to see that,
but we can also register alertings for this.
In this case, we have three databases
that are being monitored for Postgres.
One of that is the database for PMM itself.
We have nine databases in Mongo and 15 databases in MySQL.
This is a good thing for PMM,
because you can see everything in one single dashboard,
and we can go deeper for each node
or for each database as you want.
Yeah, this is all.
Thank you so much if you have some questions.
So thanks a lot for the interesting talk.
Does anyone have questions?
Hi, hello, good morning.
The query monitoring dashboard
do have some advice on how to perform these queries,
the bad queries, the slow queries.
Some advice on how to perform the bad queries,
the slow queries, how to rewrite them.
If you go deeper into that query,
you will open another dashboard
where you are going to be able to see suggestions.
You are doing something bad here,
and you can fix it with that.
Hi, thank you for the talk, first of all.
One question regarding the PMM query analytics.
Is it possible to filter by connection and not by database?
Say it again, please.
I want all queries from one connection instead of...
Yeah, is it possible?
Yeah, you can have just one connection.
Okay, then we have to talk.
Yeah, okay, thank you.