debug MySQL and MariaDB.

hi,

for user of MySQL and MariaDB, what tools and way you all to debug performance down to query level. e.g. which part of the query slow everything down?

please share.

if move to RDS by AWS, still easy to debug ?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
 
theGhost_k8Connect With a Mentor Database ConsultantCommented:
Yes
0
 
theGhost_k8Database ConsultantCommented:
Hi marrowyung,

Hope you're doing good. You can always use pt-query-digest to analyze the slow queries and work further to optimize them. No tool will give you direct answers but hints and way of doing things. You may want to monitor the performance metrics. You can review amazon cloud watch and also review the slow queries on AWS RDS.

Thanks
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
"You can review amazon cloud watch and also review the slow queries on AWS RDS."

you mean by using pt-query-digest  ?

so in RDS for MariaDB, can only use Cloud watch ?

actually for Maria DB, what cloud is the most preferable ?

"No tool will give you direct answers but hints and way of doing things."

in MS SQL world we have !
0
Protect Your Employees from Wi-Fi Threats

As Wi-Fi growth and popularity continues to climb, not everyone understands the risks that come with connecting to public Wi-Fi or even offering Wi-Fi to employees, visitors and guests. Download the resource kit to make sure your safe wherever business takes you!

 
theGhost_k8Database ConsultantCommented:
You cannot take rule-of-thumb everywhere :) anywyas... Let's not generalize and talk to the actual problem. Then you might have "direct answer" to your case.

* On AWS too you can use pt-query-digest by downloading slow logs.
* On AWS you have Cloudwatch providing live performance metrics to review
* On AWS, you have cloudwatch basic metrics by default. You can configure additional monitoring as well. Like PMM.
* "most preferable " >> Again it depends on what expertise you have. I have worked more with AWS and less with GCP. I recently heard about Alibaba Cloud too. If you want an answer, go with AWS.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
"On AWS too you can use pt-query-digest by downloading slow logs."

so not directly to the cloud ! actually I am more interesting on debugging download query level, e.g. find out which part of query cause the problem.

any executive plan on how data flow for the query and what cost each process is ?

so download slow logs to the local PC and the pt-query-digest  that. any UI tools good for maria DB debugging ?

from here https://www.percona.com/doc/percona-toolkit/LATEST/pt-query-digest.html

it seems pt-query-digest output is very simple, any more advance guide on how this tools help us debugs easier? not just found out which query is slow but in deep on which part of query slow down the whole query.

". You can configure additional monitoring as well. Like PMM."

PMM need extra payment ? not directly offer by AWS ?

" I have worked more with AWS and less with GCP"

what is GCP ? RDS is better ? in terms of ?

" I recently heard about Alibaba Cloud too. If you want an answer, go with AWS."

someone use Alibaba too but some company don't afford the security risk !
0
 
theGhost_k8Database ConsultantCommented:
For that you can use mysql command explain to debug more into detail.

PMM is UI tool for query analytics and server perf monitoring both. PMM is free.

AWS = Amazon Web Services
GCP = Google cloud platform

RDS is better cause I have used it in production. Google does provide the similar service but I haven't used it in production yet.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
"AWS = Amazon Web Services
GCP = Google cloud platform"

I think they are the same !

any diff you found out between AWS and GCP on DB technology and why you prefer AWS ?
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
PMM is free and pt-query-digest free too I believe .?
0
 
theGhost_k8Database ConsultantCommented:
" any diff you found out between AWS and GCP on DB technology and why you prefer AWS ? "
>> as said, I don't prefer AWS, I know aws more than google.
0
 
Prabhin MPConnect With a Mentor Engineer-TechOPSCommented:
Hi,

Therefore, you need this in my.cnf

slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/log/mysql/slow_query.log
log_queries_not_using_indexes


Hope this will help you!!!
1
 
marrowyungSenior Technical architecture (Data)Author Commented:
theGhost_k8,

"as said, I don't prefer AWS, I know aws more than google.


But you said : "RDS is better cause I have used it in production.", right ?

RDS is part of AWS, so you prefer AWS, right?

Prabhin MP,

"slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/log/mysql/slow_query.log
log_queries_not_using_indexes"

tks.

but it just say to MySQL please log down slow query and queries not using index, next question is, how can I know which column in index? and what kind of index?

anyway to know which part  query slow down everything ? and suggest on that as well ?
0
 
theGhost_k8Database ConsultantCommented:
RDS is on of the many products of Amazon Web Services. Did you read the explain plan link I shared earlier?
1
 
marrowyungSenior Technical architecture (Data)Author Commented:
yes, I believe you are talking about :

For that you can use mysql command explain to debug more into detail.

Open in new window


I am also watching resource on OMEE tools and MOnyog as well, all explain plan can at most shows us if there is an index need. it don't show how slow query is and which part has problem.

any suggestion ? dbforge studio ?
0
 
theGhost_k8Database ConsultantCommented:
"which part has problem" >> Can you show me some example of what are you expecting when you say that?
Explain plan provides information from optimizer about statement exec plan. Now as you have execution plan you can understand what query is doing and what problem it has.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
""which part has problem" >> Can you show me some example of what are you expecting when you say that?"

like if the query has > 200 lines of code, developers usually say they STILL don't know what is going on after double checking, then some very experience DBA will have to find it for them on WHICH PART of the query is the slowest and tell them why don't you try to fix this part !

this is very important.

"Now as you have execution plan you can understand what query is doing and what problem it has."

for me, this usually hard to understand by their text format and usually just mean add an index. query tuning is much more than adding an index. a lot of situation will be even slower by adding index.

any complex example on how that explain plan to help ?
0
 
theGhost_k8Database ConsultantCommented:
" like if the query has > 200 lines of code, developers usually say they STILL don't know what is going on after double checking, then some very experience DBA will have to find it for them on WHICH PART of the query is the slowest and tell them why don't you try to fix this part !"

That's what I meant in very first comment "No tool will give you direct answers but hints and way of doing things".. An experienced DBA will be able to figure-out what's wrong. It is not only in index but also in logic of the query. I'd google to find samples but just to have an idea you may refer to this.

" for me, this usually hard to understand by their text format and usually just mean add an index. query tuning is much more than adding an index. a lot of situation will be even slower by adding index."
>> If you're aware about what explain is conveying, you'd be able to decide how to fix the query. I do not think any tool will point out wrong joins (doing full instead of outer), wrong comparisons (comparing text with int), wrong string comparison (eg. using % on LHS of the string)....
MySQL has Explain which serves better- using it for like 10+ years. If you're looking for any tool, I do not know of it. Please share here if you find.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
"not think any tool will point out wrong joins (doing full instead of outer), wrong comparisons (comparing text with int), wrong string comparison (eg. using % on LHS of the string)....
MySQL has Explain which serves better- using it for like 10+ years. If you're looking for any tool, I do not know of it. Please share here if you find."

that one is for MS SQL, sentryone , they has anit pattern detection too.

free tools for MS SQL but very pro is whoisactive, but also for MS SQL.

"An experienced DBA will be able to figure-out what's wrong.

but I agree this. both tools I just said also need DBA's work but much easier.
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
I am now thing is MariaDB do not have ETL tools ? any OLAP service for MariaDB? any data science tools like R server and Python for MariaDB?
0
 
theGhost_k8Database ConsultantCommented:
I just googled what you asked and this is what I found and also this. These are generic questions, database does support what you asked for with drivers... I think you can use them. R server is microsoft's product (I googled), I donot know any specific development we have for MySQL / MariaDB. You have Python drivers...
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
"R server is microsoft's product (I googled),"

Microsoft Bought that, not belongs to them before. They add that later on.

I want to be friend with you as you are more on MySQL and mariaDB. I come from MS SQL background. MS SQL has a lot of deep into detail debug tools from famous guy,

MS SSIS ETL has a weakness on scaling out but MS just fix it based on SQL 2016/2017. big improvement.

I have MySQL cluster question I think you can help to answer for that.

" These are generic questions, database does support what you asked for with drivers... I think you can use them."

you mean MysQL/MariaDB only support that with drivers? any URL show what drivers MySQL/mariaDB have .

I see this : https://www.python.org/download/other/

pyhon do not offically support pyhon! but another called https://www.activestate.com/activepython.

"I just googled what you asked and this is what I found and also this."

that one means it is not built in for MariaDB, is a third party. but it is glad it has sth like this.,but it has open source version and advanced pay version.
0
 
theGhost_k8Database ConsultantCommented:
Hi Marrow,

Your questions and discussions are often broad and it always flows with the question and comments.
I was suggesting that you can always use the drivers to connect the database, for eg: https://dev.mysql.com/downloads/connector/python/
I have not used any of the ETL or other fancy tools you mentioned :)
about friend: hello friend :)
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
hahaa. as you are MysQL guy ! and I am a mix ! but not as strong as you in MySQL
1
 
marrowyungSenior Technical architecture (Data)Author Commented:
tks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.