0

I attempted to add an index to a date field in a table (MyISAM) having about 4,000 rows, using PHPMyAdmin. PHPMyadmin froze, finally timed out. Now, even after rebooting the server, the MySQL (MariaDB) server is using 100% cpu, and is almost unresponsive. For example, a query to return 400 rows in a small table took 48 seconds, where usually it would be less than 1 sec.

It has been at least 15-20 minutes since I attempted this update.

Show ProcessList; shows several queries waiting on table locks, but does not show the UPDATE TABLE query which would have been adding the index.

Does it usually take some time after adding an index for the index to be built, and is MySQL usually very unresponsive during this time? How can I check the status, or stop the process?

mysqlcheck reports all tables "OK".

SOLUTION: I had added an index on a DateTime field in another MyISAM table with which I was joining this table (it had about 25k rows). For some reason, that index prevented the query from completing - not sure what was up. I deleted the index and everything was fine. Both tables checked out OK, so it wasn't corrupted. Not sure how anyone would ever trace this type of issue without backtracking the previous schema modifications one at a time. There were no pertinent errors messages to point to the issue.

Ryan Griggs
  • 885
  • 2
  • 12
  • 27
  • That is a strong reason for moving from MyISAM to InnoDB. More discussion: http://mysql.rjweb.org/doc.php/myisam2innodb – Rick James Oct 25 '19 at 00:10
  • Questions like this get a lot more traction in stackoverflow.com – Rick James Oct 25 '19 at 00:10
  • @RickJames I would like to do that, but I have some tables which need fulltext indexes. My server is Centos 7 and Virtualmin, with MariaDB 5.5.60 and it's not recommended to update MariaDB to v10 until the next Virtualmin release. – Ryan Griggs Oct 25 '19 at 00:55
  • @RickJames I never know which section to post in. I just guessed that this was more "admin-related" than programming related, so I went with ServerFault. – Ryan Griggs Oct 25 '19 at 00:56
  • Yeah, the lines between the forums is vague. My second choice would be where DBA questions should go: dba.stackexchange.com. My point is, database questions get a lot more traffic at those two sites, much more than at ServerFault. – Rick James Oct 26 '19 at 00:11
  • MariaDB 10.0 was released about 5 years ago! 10.4 is, or soon will be, released. – Rick James Oct 26 '19 at 00:15
  • Ah, here we go: ----- 2013-11-18 MariaDB 10.0.6 -- -- ----- Fulltext search is supported in InnoDB – Rick James Oct 26 '19 at 00:17
  • Additional information request. Post on pastebin.com and share the links. From SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report AND Optional very helpful information, if available includes - htop OR top OR mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide meaningful suggestions. – Wilson Hauck Nov 12 '19 at 23:12

1 Answers1

0

Back in 5.5 days, adding an index required rebuilding the table and all its indexes. That includes your FULLTEXT index. Rebuilding it is quite a CPU chore.

Newer versions have FULLTEXT for InnoDB.

Newer versions have ways to ADD INDEX without rebuilding everything else.

Etc.

Rick James
  • 2,058
  • 5
  • 11