Timeline for "Create Unique Index" on big table taking too long time
Current License: CC BY-SA 4.0
10 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jul 7, 2018 at 1:32 | comment | added | Wilson Hauck | @Narnia_Optimus Additional information request, please. Post on pastebin.com or here. A) complete (not edited) my.cnf-ini Text results of: B) SHOW GLOBAL STATUS; C) SHOW GLOBAL VARIABLES; D) complete MySQLTuner report E) SHOW FULL PROCESSLIST; Optional very helpful information, if available includes - htop, top & mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -x when system is busy for an idea of IOPS by device, df -h for a linux/unix free space list by device, for server tuning analysis. | |
| Jul 5, 2018 at 15:00 | comment | added | Narnia_Optimus | Both conditions were met. I further checked SHOW PROCESSLIST from the console and it shows the same result :( | |
| Jul 5, 2018 at 14:51 | comment | added | Rick James |
SHOW PROCESSLIST needs to be done via root while the CREATE INDEX is being done.
|
|
| Jul 5, 2018 at 14:35 | comment | added | Narnia_Optimus | I have added 'show create table commits' and 'show PROCESSLISTS' results. For the latter, I see neither "Repairing by key_buffer" nor "Repairing by sort". | |
| Jul 5, 2018 at 13:26 | comment | added | Rick James | @Narnia_Optimus - and I added some words about "polyonomial", though I think it is close to proportional to the table size. Yes, there are variations, but I can't explain them without seeing the schema. | |
| Jul 5, 2018 at 13:24 | history | edited | Rick James | CC BY-SA 4.0 |
polynomial
|
| Jul 5, 2018 at 13:10 | comment | added | Rick James |
@Narnia_Optimus - MyISAM's .MYI file holds some dynamic, non-data, non-schema, information about the table, so it is at least 1024 bytes (1 "block"). In addition, that file holds any indexes (PRIMARY or secondary). (Innodb works differently.) The 36.7G indicates that there are some index(es); but it does not say how many indexes. SHOW CREATE TABLE commits is reliable at listing all the indexes (but not the space taken).
|
|
| Jul 5, 2018 at 9:29 | comment | added | Narnia_Optimus | Impressed with your suggestions! Thanks a lot. I will check 'PROCESSLIST'. Figure 2 in the question shows 36.7 GiB for index length for the 'commits' table. I recognized even for some other table, there are values before executing 'CREATE INDEX' command. Do you know the reason for this? How can I be sure that indexing is done on the 'commits' table? | |
| Jul 5, 2018 at 2:23 | history | edited | Rick James | CC BY-SA 4.0 |
recalc
|
| Jul 5, 2018 at 2:13 | history | answered | Rick James | CC BY-SA 4.0 |