And this is not the full transaction, can you send the full transaction and in the form of text?

|
madno 2021-08-17 08:03:18
MasterZiv 2021-08-17 08:01:00
Create indices, then you can use this instance as main db

Replication lag keeps increasing even with all the indexes. After removing them also same issue. But can you tell the what might be causing the locks of the rows

madno 2021-08-17 08:05:47
MasterZiv 2021-08-17 08:01:00
Create indices, then you can use this instance as main db

Now with all the indexes removed, with only PK (id , created) . I believe it is restructuring the whole clustered index when I insert a new record. But according to the doc. It should only lock where it will be inserted. But it locking all the rows of the previous partition

MasterZiv 2021-08-17 08:11:54
madno 2021-08-17 08:03:18
Replication lag keeps increasing even with all the indexes. After removing them also same issue. But can you tell the what might be causing the locks of the rows

Anything, one must be on site to find out…

How many pairtitions have you created, tens, thousands?

MasterZiv 2021-08-17 08:12:54
madno 2021-08-17 08:05:47
Now with all the indexes removed, with only PK (id , created) . I believe it is restructuring the whole clustered index when I insert a new record. But according to the doc. It should only lock where it will be inserted. But it locking all the rows of the previous partition

This happens always, should not be a big problem

MasterZiv 2021-08-17 08:13:55
madno 2021-08-17 08:05:47
Now with all the indexes removed, with only PK (id , created) . I believe it is restructuring the whole clustered index when I insert a new record. But according to the doc. It should only lock where it will be inserted. But it locking all the rows of the previous partition

What do you mean by previous partition?

iatharva 2021-08-17 08:57:44
Ishu Gupta 2021-08-15 16:05:20
It’s too costly

It’s free

madno 2021-08-17 08:58:48
MasterZiv 2021-08-17 08:13:55
What do you mean by previous partition?

I meant older partition where large number of records are present

MasterZiv 2021-08-17 09:00:26
madno 2021-08-17 08:58:48
I meant older partition where large number of records are present

Maybe smaller numbers?

Amit Kumar 2021-08-17 09:08:43
can someone send DBA material, doc for beginners (architecture and all )
MasterZiv 2021-08-17 09:21:19
madno 2021-08-17 08:05:47
Now with all the indexes removed, with only PK (id , created) . I believe it is restructuring the whole clustered index when I insert a new record. But according to the doc. It should only lock where it will be inserted. But it locking all the rows of the previous partition

How can you see this, that all rows on the other partition are locked?

MasterZiv 2021-08-17 09:22:30
Amit Kumar 2021-08-17 09:08:43
can someone send DBA material, doc for beginners (architecture and all )

Look on the mysql web site and Persona web site. It is all there

madno 2021-08-18 01:17:46
MasterZiv 2021-08-17 09:21:19
How can you see this, that all rows on the other partition are locked?

From innodb status. I haven’t found any solution to that till now

MasterZiv 2021-08-18 08:17:29
madno 2021-08-18 01:17:46
From innodb status. I haven’t found any solution to that till now

Can you share this?

madno 2021-08-18 08:28:08
MasterZiv 2021-08-18 08:17:29
Can you share this?

https://dba.stackexchange.com/questions/298085/large-number-of-row-locks-on-a-partitioned-slave-table

Large number of row locks on a partitioned slave tableDatabase Administrators Stack Exchange
I am testing mysql5.7.28 partition. Table in master is not partioned. The table in slave is partitioned. When I start the replication, slave lag keeps on increasing.
This is the table schema in mas…
madno 2021-08-18 08:29:41
Here is my SO questions. Details of lock from that
heap size 8298704, 651691 row lock(s), undo log entries 3
MasterZiv 2021-08-18 09:03:41
madno 2021-08-18 08:28:08
https://dba.stackexchange.com/questions/298085/large-number-of-row-locks-on-a-partitioned-slave-table

is this really your partition definition ?

“ PARTITION BY RANGE (TO_DAYS(created))
(PARTITION y2021w32 VALUES LESS THAN (738382) ENGINE = InnoDB,
PARTITION y2021w33 VALUES LESS THAN (738389) ENGINE = InnoDB,
PARTITION y2021w34 VALUES LESS THAN (738396) ENGINE = InnoDB,
PARTITION y2021w35 VALUES LESS THAN (738403) ENGINE = InnoDB,
PARTITION y2021w36 VALUES LESS THAN (738410) ENGINE = InnoDB,
PARTITION y2021w37 VALUES LESS THAN (738417) ENGINE = InnoDB,
PARTITION y2021w38 VALUES LESS THAN (738424) ENGINE = InnoDB)
“`

madno 2021-08-18 09:05:48
MasterZiv 2021-08-18 09:03:41
is this really your partition definition ?

“ PARTITION BY RANGE (TO_DAYS(created))
(PARTITION y2021w32 VALUES LESS THAN (738382) ENGINE = InnoDB,
PARTITION y2021w33 VALUES LESS THAN (738389) ENGINE = InnoDB,
PARTITION y2021w34 VALUES LESS THAN (738396) ENGINE = InnoDB,
PARTITION y2021w35 VALUES LESS THAN (738403) ENGINE = InnoDB,
PARTITION y2021w36 VALUES LESS THAN (738410) ENGINE = InnoDB,
PARTITION y2021w37 VALUES LESS THAN (738417) ENGINE = InnoDB,
PARTITION y2021w38 VALUES LESS THAN (738424) ENGINE = InnoDB)
“`

Partition y2021 values less than (TO_DAYS(‘2021-08-15’)),
….

Partition definition are like this

MasterZiv 2021-08-18 09:07:37
madno 2021-08-18 09:05:48
Partition y2021 values less than (TO_DAYS(‘2021-08-15’)),
….

Partition definition are like this

So this must be by years and weeks of year , right?

madno 2021-08-18 09:12:13
Weekly partition I wanted to do
madno 2021-08-18 09:13:04
So in TO_DAYS() , I have given exact date of the start of the week.
madno 2021-08-18 09:14:59
To_days() function changed the date to a number what you see in show create table.
MasterZiv 2021-08-18 09:19:37
madno 2021-08-18 09:14:59
To_days() function changed the date to a number what you see in show create table.

Ok I’ll take a look at this but in general inserts don’t lock anything so you’re information on SO should be incorrect.

MasterZiv 2021-08-18 09:20:17
madno 2021-08-18 09:14:59
To_days() function changed the date to a number what you see in show create table.

His do you know this was an insert?

madno 2021-08-18 09:21:07
MasterZiv 2021-08-18 09:20:17
His do you know this was an insert?

Checked the master binlog position where slave is blocked

madno 2021-08-18 09:21:28
Basically Exec_master_log_pos
MasterZiv 2021-08-18 09:21:51
madno 2021-08-18 09:21:07
Checked the master binlog position where slave is blocked

Can you show the query?

madno 2021-08-18 09:23:47
mysql_en-12146.jpg

MasterZiv 2021-08-18 09:30:36
madno 2021-08-18 09:23:47

Bit this is not the table you shown on so.

MasterZiv 2021-08-18 09:31:52
madno 2021-08-18 09:23:47

And this is not the full transaction, can you send the full transaction and in the form of text?

MasterZiv 2021-08-18 09:38:50
madno 2021-08-18 09:21:07
Checked the master binlog position where slave is blocked

You can read about Inno locks in INSERT statement here
https://dev.mysql.com/doc/refman/5.6/en/innodb-locks-set.html

I think that what you have shown so far doesn’t mean your INSERT really locks something else then the rows being inserted.

madno 2021-08-18 09:40:05
MasterZiv 2021-08-18 09:30:36
Bit this is not the table you shown on so.

I have changed the tabke name in SO

madno 2021-08-18 09:40:42
MasterZiv 2021-08-18 09:31:52
And this is not the full transaction, can you send the full transaction and in the form of text?

I’ll check this again

Ishu Gupta 2021-08-19 05:15:25
Hi when I fired a select query on a table it shows table is marked as crashed and should be repaired table storage engine is archive and repair table command also not worked
Ishu Gupta 2021-08-19 05:15:37
Can anyone support here
madno 2021-08-19 05:38:10
MasterZiv 2021-08-18 09:38:50
You can read about Inno locks in INSERT statement here
https://dev.mysql.com/doc/refman/5.6/en/innodb-locks-set.html

I think that what you have shown so far doesn’t mean your INSERT really locks something else then the rows being inserted.

Hi found out the culprit, in that whole transaction , following queries are executed
1. Begin
2. Insert into wallet table(eg- id 123)
3. Insert into table2
4. Update wallet where pk=123
5. Commit

So I assume update is locking the rows. Is it locking because it has to check the uniqueness of that col before update?

|