I have to export 6 thousand data to a table in html, but it takes a long time, how can I improve?

|
TabahiHere 2020-12-08 10:31:07
Okay I need to dig it up. How do I test on database if it is creating deadblocks. (Any tool)
MasterZiv 2020-12-08 18:51:15
piterden 2020-12-08 09:24:26
Didn’t you think about to change your profession?

Денис, ну что это…?

piterden 2020-12-08 18:54:10
Не, ну у него кластер, а он даже не знает какую инфу дать чтобы приблизиться к решению
TabahiHere 2020-12-08 18:56:45
MasterZiv 2020-12-08 18:51:15
Денис, ну что это…?

Это был замечательный прием.

MasterZiv 2020-12-08 18:58:32
TabahiHere 2020-12-08 09:18:31
Hi everyone, I have mariadb Galera cluster with mariadb 10.5 and galera 4. It is load balanced with haproxy kept first node as master and remaining two backup. Still I am having deadlocks. Please help.

Dreadlocks are normal situation, they are always with you unless you have single user database.
We will not help you in this respect.

If you starve from very frequent dreadlocks, it is always a very specific substation for every particular DB, and, again, we will not help you. It is you yourself who only can solve and fix the situation. One from aside should investigate the situation something like for 3-4 days to come to any solution, if it is at all possible to solve.

Read the logs, all info is there, and do it yourself.
If this is impossible you should hire some specialist for someone like a week of work.

MasterZiv 2020-12-08 18:59:29
TabahiHere 2020-12-08 09:30:44
May be, cause I’m learning. No-one seems to have all knowledge since birth as you have.

If it is a student task just ignore dreadlocks

MasterZiv 2020-12-08 19:00:16
TabahiHere 2020-12-08 09:35:01
This is how I have configured. I have read on few blogs that if you allow writing on multiple nodes simultaneously you may get deadblocks. Still having those.

So what else I need to check?

Or you can just disallow writing to all nodes

MasterZiv 2020-12-08 19:00:50
TabahiHere 2020-12-08 10:31:07
Okay I need to dig it up. How do I test on database if it is creating deadblocks. (Any tool)

The MySQL log.

TabahiHere 2020-12-08 19:01:11
MasterZiv 2020-12-08 19:00:50
The MySQL log.

yes.

Master Yoda 2020-12-09 18:18:04
TabahiHere 2020-12-08 09:18:31
Hi everyone, I have mariadb Galera cluster with mariadb 10.5 and galera 4. It is load balanced with haproxy kept first node as master and remaining two backup. Still I am having deadlocks. Please help.

Maybe you should run ‘show engine innodb status’ and see which queries are stuck in deadlock? Or if you are using transactions, try to make them efficient and split in smaller transactions? Don’t use “for update” where it is not needed. Deadlock doesn’t mean actually deadlock most of the time. It could mean “innodb_lock_wait_timeout” exceeded. If your business logic allows increase it?

Master Yoda 2020-12-09 18:18:42
TabahiHere 2020-12-08 09:18:31
Hi everyone, I have mariadb Galera cluster with mariadb 10.5 and galera 4. It is load balanced with haproxy kept first node as master and remaining two backup. Still I am having deadlocks. Please help.

Well having a cluster and load balancer doesn’t mean you would be free of deadlock

iHeadNlghtmare 2020-12-10 10:16:46
I have to export 6 thousand data to a table in html, but it takes a long time, how can I improve?
MasterZiv 2020-12-10 10:33:29
iHeadNlghtmare 2020-12-10 10:16:46
I have to export 6 thousand data to a table in html, but it takes a long time, how can I improve?

What should this “6 thousand data” mean ?

Measure what takes time most, the reading data from the DB or formatting it into HTML
or maybe some other step. Then improve the slowest step.

iHeadNlghtmare 2020-12-10 10:33:58
6k rows
iHeadNlghtmare 2020-12-10 10:34:12
But takes 40 seconds
MasterZiv 2020-12-10 10:34:42
iHeadNlghtmare 2020-12-10 10:33:58
6k rows

It is very little. Should not take long.

MasterZiv 2020-12-10 10:36:03
iHeadNlghtmare 2020-12-10 10:33:58
6k rows

So explain details or else no one will be able to help you

iHeadNlghtmare 2020-12-10 10:36:03
Probably the php is slow to read that array and write to table
iHeadNlghtmare 2020-12-10 10:36:04
Idk
MasterZiv 2020-12-10 10:36:24
iHeadNlghtmare 2020-12-10 10:36:03
Probably the php is slow to read that array and write to table

Measure it.

MasterZiv 2020-12-10 15:04:00
Hello. How can I select all rows that has 1 matching row in the other joinned table eg. getting products that were attached to only one category
MasterZiv 2020-12-10 15:05:50
Generally it is achievable with JOIN statement
MasterZiv 2020-12-10 15:05:50
You can share DDL of your tables
MasterZiv 2020-12-10 15:07:18
MasterZiv 2020-12-10 15:04:00
Hello. How can I select all rows that has 1 matching row in the other joinned table eg. getting products that were attached to only one category

in general

select …
from thetable
where ( < corellated subquery > ) …

Murali 2020-12-11 11:22:58
Hi friends, MySQL service automatically stopped in windows environment. Also it may not started after start the service Can any one advise how to fix this issue. Please it’s urgent
MasterZiv 2020-12-11 14:28:29
Murali 2020-12-11 11:22:58
Hi friends, MySQL service automatically stopped in windows environment. Also it may not started after start the service Can any one advise how to fix this issue. Please it’s urgent

Look into the logs and see what’s there. Then fix the cause of the problem

MasterZiv 2020-12-13 14:32:42
Чат для английского языка
ColonelMustang 2020-12-14 13:02:54
I was wondering if MySQL router can be used with different clusters? Let’s say if I have cluster 1 and cluster 2 with different database tables, how would I use the MySQL router?
ColonelMustang 2020-12-14 13:03:07
This is because my application connects to a lot of DBs which might not be on the same server
ColonelMustang 2020-12-14 13:04:29
Unfortunately no documentation/tutorial does answers this
2020-12-14 19:30:28
someone here works with flask and sqlite?
MasterZiv 2020-12-14 19:46:09
2020-12-14 19:30:28
someone here works with flask and sqlite?

the chat topic is MySQL!

MasterZiv 2020-12-14 19:48:33
ColonelMustang 2020-12-14 13:02:54
I was wondering if MySQL router can be used with different clusters? Let’s say if I have cluster 1 and cluster 2 with different database tables, how would I use the MySQL router?

Router is a universal thing as far as I understand, so it usage depends entirely on what configuration you put it in.
If this is OK for your application, then it is OK. If not — it is not OK.

ColonelMustang 2020-12-14 19:51:18
MasterZiv 2020-12-14 19:48:33
Router is a universal thing as far as I understand, so it usage depends entirely on what configuration you put it in.
If this is OK for your application, then it is OK. If not — it is not OK.

I found you can set up multiple routers

ElleAx 2020-12-19 17:47:29
I am doing myseql rn
|