Timeline for Bulk Delete for Large Table in MySQL
Current License: CC BY-SA 3.0
14 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| May 20, 2020 at 16:44 | comment | added | Muhammad Omer Aslam | thank you very much for your response @RolandoMySQLDBA | |
| May 20, 2020 at 16:37 | comment | added | RolandoMySQLDBA | @MuhammadOmerAslam The above approach would be perfect when you have scheduled downtime. Following the link in akuzminsky's answer (percona.com/doc/percona-toolkit/LATEST/pt-archiver.html). You can use pt-archiver to archive data or just delete data without archiving. | |
| May 20, 2020 at 14:40 | comment | added | Muhammad Omer Aslam | @RolandoMySQLDBA so you mean rather than following the above approach you have mentioned using temp table I should use the pt-archiver ? i havent used it before | |
| May 20, 2020 at 13:41 | comment | added | RolandoMySQLDBA |
@MuhammadOmerAslam in that case, the ibdata1 file (system tablespace) would just grow in the undo logs. This is especially the case when ibdata1 would start getting bigger in filesize (See my old post dba.stackexchange.com/questions/40730/…). You need to exercise more care by using pt-archiver as mentioned in akuzminsky's answer, It could be throttled to delete rows in chunks and then use pt-online-schema-change to run ALTER TABLE ENGINE=InnoDB to shrink the table.
|
|
| May 20, 2020 at 3:08 | comment | added | Muhammad Omer Aslam | would there be any difference if i use it inside a transaction block, keeping in mind the lock wait timeout,also would it have any cons if my actual table is real huge which is now renamed and has to be dropped | |
| Jul 18, 2017 at 14:46 | history | edited | RolandoMySQLDBA | CC BY-SA 3.0 |
added 1 character in body
|
| Apr 13, 2017 at 12:42 | history | edited | CommunityBot |
replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
|
|
| Nov 20, 2014 at 18:12 | vote | accept | Tianyi Cong | ||
| Nov 20, 2014 at 18:12 | comment | added | Tianyi Cong | I am applying this strategy on staging, does rename command should start with "RENAME TABLE"? | |
| Nov 20, 2014 at 18:11 | history | edited | RolandoMySQLDBA | CC BY-SA 3.0 |
added 6 characters in body
|
| Nov 20, 2014 at 15:42 | history | edited | RolandoMySQLDBA | CC BY-SA 3.0 |
added 177 characters in body
|
| Nov 19, 2014 at 22:43 | comment | added | Tianyi Cong | Thanks the reply Rolando! How does MySql internally handle drop table? First delete all the columns then remove table or something else? Will it take much shorter time than delete those cloumns? | |
| Nov 19, 2014 at 22:00 | history | edited | RolandoMySQLDBA | CC BY-SA 3.0 |
edited body
|
| Nov 19, 2014 at 21:51 | history | answered | RolandoMySQLDBA | CC BY-SA 3.0 |