2

I am running an alter table statement over a table with 350 million rows. How can I know that the statement is actually doing something and is not hung? Equally I'd hate to disturb a statement potentially moments before it finishes. Especially after it has run for hours.

I need to be able to script this in either Bash or PERL as I the host is a Redhat Linux box. The statement in this case is:

ALTER TABLE table_name AUTO_INCREMENT = 1;
s.k
  • 519
  • 4
  • 7
  • 23
  • I don't think that statement should take much time, it doesn't have to do anything to the table contents. – Barmar May 18 '13 at 04:08
  • That is what I thought too. However other postings that I found on courtesy of aunt Google mention that this is to be expected for large tables. – s.k May 18 '13 at 04:10
  • I don't know of a way to monitor the internal activity of the mysql server. The best I can suggest is making a copy of the table on a test server and performing the ALTER there, to ensure that it works. Then do it on the production server. – Barmar May 18 '13 at 04:13
  • Interestingly I do this on an off line server dedicated for this type of thing. Then my scripts copy the tables to the production server under a different name and then swap them into use using rename. It works quite well for protecting the production system from such activity. – s.k May 18 '13 at 04:17
  • Just out of curiosity, how are you setting the auto increment to 1, and then avoiding duplicate primary keys? – Bryan May 18 '13 at 04:18
  • 1
    In the table only ~360 million rows are used. Due to design they are in a sliding window and are always sequential. So when we get to the key MAX of ~2 billion I was hoping to just reuse the earlier ones in sequence. Actually I am beginning to believe that what I am attempting is not possible as it mentions [here](http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html) that 'InnoDB resets the next auto_increment value to the highest value in the table + 1 after a server restart.' and I am wondering if MyISAM does the same. – s.k May 18 '13 at 04:32
  • I'd expect the database statement call not to return until it finished. Is that wrong? – bart May 18 '13 at 04:44
  • [This](http://viralpatel.net/blogs/reseting-mysql-autoincrement-column/) article basically says that what I am trying to do is not possible. It indicates that the auto_increment value will always be one more than the maximum value in the auto_increment column. So I have to re-index all of my rows or change the size of the index. Changing the size of the index is obviously only a temporary measure as eventually it too has a limit; albeit much higher. The only real possibility for me then is to re-index. – s.k May 18 '13 at 05:06

3 Answers3

6

What you're actually looking for is this. SHOW PROCESSLIST. I will also add that updating auto increments does indeed take an extraordinary amount of time when working with large data sets (as you've discovered)

http://dev.mysql.com/doc/refman/5.1/en/show-processlist.html

If you open up a new connection to the DB and run that command, it will show you what stage (not how long is left) the query being run is at. Either copying to temp table, finalizing, etc

Bryan
  • 6,682
  • 2
  • 17
  • 21
  • 3
    +1, but it's `SHOW PROCESSLIST` (no space between `PROCESS` and `LIST`). – Ed Gibbs May 18 '13 at 04:18
  • Excellent thank you. That was just what I was looking for. One thing further if I may, can this tell me if the process has got into a bad state (not that I have ever seen such a thing happen) as I would like to know how to detect and code for exceptions. – s.k May 18 '13 at 04:20
  • Not necessarily a bad state per say, the worst thing I've personally experienced when performing an alter, is waiting for a table meta lock. This query will in fact point out a metalock, and you should kill the query immediately if it runs into that lock. http://dev.mysql.com/doc/refman/5.5/en/metadata-locking.html If you don't kill it, the alter freezes, and all consecutive updates / inserts / selects freeze as well, lots of fun in a production environment haha... – Bryan May 18 '13 at 04:22
  • 1+ for this nice post @Bryan. – JDGuide May 18 '13 at 04:30
1

As others have mentioned, SHOW PROCESSLIST is your friend. Sometimes. When having multiple long queries you might want to add FULL to that statement: SHOW FULL PROCESSLIST. With FULL the result will contain the complete query in the Info column.

I wonder why your query is slow in the first place. MySQL should have a good idea about where to look for the last record in that table. If it's auto_increment it's indexed as well, and it should be super easy for MySQL to do.

PROCESSLIST often gives a good hint on where MySQL is in it's work, and especially if a query/command is blocked for some reason.

mogul
  • 4,441
  • 1
  • 18
  • 22
0

Just to give to you another option ... You can try developer tool SQLyog .

it shows query profile with variations in variables along with progress of statement

alwaysLearn
  • 6,882
  • 7
  • 39
  • 67
  • Technically yes I could monitor the process using that tool, or for that matter the standard MySQL Workbench product. However I need to script this so a GUI based tool isn't exactly what I was looking for. But thank you for pointing it out, I will take a look at it with the idea that it might be valuable to us for other purposes. – s.k May 18 '13 at 04:44