Is it because of php execution time limit or something?

|
moh98ala 2020-06-02 14:17:34
your welcome
digindominic 2020-06-03 11:20:41
I am having an issue with my check out query.. it is taking like 22 seconds to execute and taking up 100% cpu on the server. What tweak can be done to fix the issue.

The server have 4 cores and 6gb memory

Master Yoda 2020-06-03 11:26:35
digindominic 2020-06-03 11:20:41
I am having an issue with my check out query.. it is taking like 22 seconds to execute and taking up 100% cpu on the server. What tweak can be done to fix the issue.

The server have 4 cores and 6gb memory

Check query plan with EXPLAIN. and profile the query

MasterZiv 2020-06-03 12:12:37
digindominic 2020-06-03 11:20:41
I am having an issue with my check out query.. it is taking like 22 seconds to execute and taking up 100% cpu on the server. What tweak can be done to fix the issue.

The server have 4 cores and 6gb memory

yes check query plan…

MasterZiv 2020-06-03 12:13:08
digindominic 2020-06-03 11:20:41
I am having an issue with my check out query.. it is taking like 22 seconds to execute and taking up 100% cpu on the server. What tweak can be done to fix the issue.

The server have 4 cores and 6gb memory

It’s strange you have 100% CPU load…

sixpeteunder 2020-06-03 13:17:44
Hello, I need help structuring an SQL database. Specifically this part:

I have a system where I have “parent” and “child” accounts. The idea is that parent accounts are the ones used for authentication, and each parent account may create one or more child accounts.

Parent accounts may be of two types: a “single” account allows only one child account, while a “multi” account allows more than one child.

MasterZiv 2020-06-03 13:22:25
sixpeteunder 2020-06-03 13:17:44
Hello, I need help structuring an SQL database. Specifically this part:

I have a system where I have “parent” and “child” accounts. The idea is that parent accounts are the ones used for authentication, and each parent account may create one or more child accounts.

Parent accounts may be of two types: a “single” account allows only one child account, while a “multi” account allows more than one child.

What do you mean by “structuring” ?

sixpeteunder 2020-06-03 13:23:13
This is the idea I have for my system, and I want to come up with a good table structure representing it.
MasterZiv 2020-06-03 13:24:04
sixpeteunder 2020-06-03 13:17:44
Hello, I need help structuring an SQL database. Specifically this part:

I have a system where I have “parent” and “child” accounts. The idea is that parent accounts are the ones used for authentication, and each parent account may create one or more child accounts.

Parent accounts may be of two types: a “single” account allows only one child account, while a “multi” account allows more than one child.

What child accounts are used for ?

sixpeteunder 2020-06-03 13:26:34
A “parent” creates “child” accounts, with specific details, and the system recommends items of interest to each child, basing on those details.

The parent account is only used for logging in.

MasterZiv 2020-06-03 13:27:44
sixpeteunder 2020-06-03 13:26:34
A “parent” creates “child” accounts, with specific details, and the system recommends items of interest to each child, basing on those details.

The parent account is only used for logging in.

Can children be used for logging in?

sixpeteunder 2020-06-03 13:28:10
No, they cannot.
MasterZiv 2020-06-03 13:28:33
sixpeteunder 2020-06-03 13:28:10
No, they cannot.

then they are not accounts at all.

MasterZiv 2020-06-03 13:29:10
sixpeteunder 2020-06-03 13:28:10
No, they cannot.

What do they have in common, parent account and child account?

sixpeteunder 2020-06-03 13:29:51
That makes sense.
What if I change it so that children accounts act like “single” accounts?
sixpeteunder 2020-06-03 13:31:44
MasterZiv 2020-06-03 13:29:10
What do they have in common, parent account and child account?

names and contact details. A parent account should also be able to manage all “children” accounts from a central place, but a parent account itself is not supposed to be used for recommending items.

MasterZiv 2020-06-03 13:32:56
sixpeteunder 2020-06-03 13:31:44
names and contact details. A parent account should also be able to manage all “children” accounts from a central place, but a parent account itself is not supposed to be used for recommending items.

ok then what you call child account is not an account,

sixpeteunder 2020-06-03 13:34:11
Please explain?
MasterZiv 2020-06-03 13:34:31
sixpeteunder 2020-06-03 13:31:44
names and contact details. A parent account should also be able to manage all “children” accounts from a central place, but a parent account itself is not supposed to be used for recommending items.

So i recommend creating a parent entity for all of them, like
“Person”, which will be used for storing names, contacts and all other common things for people.

MasterZiv 2020-06-03 13:36:31
MasterZiv 2020-06-03 13:34:31
So i recommend creating a parent entity for all of them, like
“Person”, which will be used for storing names, contacts and all other common things for people.

From the “Person” entity you can inherit those two entities “Account” (you called it parent account)
and “YYY” — you called it child account.

For YYY I can’t figure out a name just now.

MasterZiv 2020-06-03 13:37:44
Probably, “RecommendationObject”
MasterZiv 2020-06-03 13:37:55
AdviseTarget
sixpeteunder 2020-06-03 13:38:30
Yes, this looks like a good idea.
Then each Account can have many “RecommedationObjects”
MasterZiv 2020-06-03 13:39:28
Between “Account” and “AdviseTarget” you can organize simple ONE-to-many relationship (link) with some restriction on the number of “children”
MasterZiv 2020-06-03 13:39:41
sixpeteunder 2020-06-03 13:38:30
Yes, this looks like a good idea.
Then each Account can have many “RecommedationObjects”

Exactly

sixpeteunder 2020-06-03 13:40:48
MasterZiv 2020-06-03 13:39:28
Between “Account” and “AdviseTarget” you can organize simple ONE-to-many relationship (link) with some restriction on the number of “children”

Does the restriction need to be done in the code or can it be done in the database?

MasterZiv 2020-06-03 13:41:14
sixpeteunder 2020-06-03 13:40:48
Does the restriction need to be done in the code or can it be done in the database?

in the code only

digindominic 2020-06-03 13:42:32
MasterZiv 2020-06-03 12:13:08
It’s strange you have 100% CPU load…

I can see the the same query is repeated many times in the slow query log

digindominic 2020-06-03 13:42:41
Why is that happening?
MasterZiv 2020-06-03 13:43:00
digindominic 2020-06-03 13:42:41
Why is that happening?

Because your client sends it. more and more and more

sixpeteunder 2020-06-03 13:43:06
MasterZiv 2020-06-03 13:41:14
in the code only

Okay, thank you.

digindominic 2020-06-03 13:46:05
MasterZiv 2020-06-03 13:43:00
Because your client sends it. more and more and more

Is it because of php execution time limit or something?

I have only checkout button once and this is happening😔

MasterZiv 2020-06-03 13:47:28
digindominic 2020-06-03 13:46:05
Is it because of php execution time limit or something?

I have only checkout button once and this is happening😔

If your clients send this query and the query is slow, each execution gets logged into the slow query log.
This is it, there is no other cause.

MasterZiv 2020-06-03 13:48:57
digindominic 2020-06-03 13:46:05
Is it because of php execution time limit or something?

I have only checkout button once and this is happening😔

If you want to ask why your clients send this query over and over again, then this is not a question of DB programming, not for this chat.

digindominic 2020-06-03 17:07:49
MasterZiv 2020-06-03 13:48:57
If you want to ask why your clients send this query over and over again, then this is not a question of DB programming, not for this chat.

I got it fixed using this blog: https://opensource.com/article/17/5/speed-your-mysql-queries-300-times

Thanks for your time

How to speed up your MySQL queries 300 timesOpensource
MySQL has a built-in slow query log. Learn how to speed it up 300 times.
2020-06-04 20:35:12
mysql transactions go through many wait_events like wait/io/table/sql/handler, wait/io/file/innodb/innodb_log_fileany many other. My question is there any link or book or any other resource where its meaning/details can be found?
I have searched the dev.mysql.com but did not found any thing relevant.
MasterZiv 2020-06-04 23:00:57
2020-06-04 20:35:12
mysql transactions go through many wait_events like wait/io/table/sql/handler, wait/io/file/innodb/innodb_log_fileany many other. My question is there any link or book or any other resource where its meaning/details can be found?
I have searched the dev.mysql.com but did not found any thing relevant.

You must be base on Oracle DB… Your knowledge …

But actually this “wait” scheme is not strictly necessary to understand and manage MySQL DB performance.

MasterZiv 2020-06-04 23:03:03
2020-06-04 20:35:12
mysql transactions go through many wait_events like wait/io/table/sql/handler, wait/io/file/innodb/innodb_log_fileany many other. My question is there any link or book or any other resource where its meaning/details can be found?
I have searched the dev.mysql.com but did not found any thing relevant.

MySQL AFAIR don’t have such a wonderful performance evaluation model as Oracle has, but you still can successfully control the database.

2020-06-05 06:57:26
Agreed on evaluation model.
Buy knowing waits, it gets easier to understand where the performance issue lies.
2020-06-05 06:58:17
You can say it’s quickest way to get to the troubling part of DB server
2020-06-05 09:10:12
for example:-wait/io/table/sql/handler
there is a wait on IO, but WHY that wait is.
MasterZiv 2020-06-05 09:54:11
2020-06-05 09:10:12
for example:-wait/io/table/sql/handler
there is a wait on IO, but WHY that wait is.

MySQL has no wait model for performance evaluation. Anyway

2020-06-05 09:59:56
select EVENT_NAME from performance_schema.events_waits_current;
2020-06-05 10:00:25
Probably above query can explain what I am looking for.
2020-06-05 10:04:36
wait/io/file/innodb/innodb_data_file
wait/io/socket/sql/client_connection
wait/io/table/sql/handler
wait/synch/cond/sql/COND_compress_gtid_table
wait/synch/cond/sql/COND_queue_state
wait/synch/cond/sql/COND_server_started
wait/synch/mutex/innodb/buf_dblwr_mutex
wait/synch/mutex/innodb/buf_pool_mutex
wait/synch/mutex/innodb/fil_system_mutex
wait/synch/mutex/innodb/lock_wait_mutex
wait/synch/mutex/innodb/log_sys_mutex

i get this output result of above query and I want help to know if there is any place to know above result-set definations.

MasterZiv 2020-06-05 10:32:58
@acromegale , can you help with this?
MasterZiv 2020-06-05 10:34:39
I personally don’t use this…
acromegale 2020-06-05 10:56:23
I haven’t seen a good books about ps, only articles or presentations without deep diving into details
piterden 2020-06-05 11:01:21
acromegale 2020-06-05 10:56:23
I haven’t seen a good books about ps, only articles or presentations without deep diving into details

Is source code readable?

acromegale 2020-06-05 11:02:58
Sure, if you know c/c++ 🙂
|