I happen to get to work with some decent size data. I don’t know that I would say Big Data because that is a buzz word and I know there are people dealing with MUCH more data than I get to.
However, on the main application I work on, we do have over 2TB of data. With this much data comes a price to pay when you want to make a schema change. Locking tables being the biggest price, as sometimes this will render an application unusable.
There are ways around this sometimes. Percona has some great tools to make altering tables work without locking. It involves a lot of really cool magic, including creating a new table, creating triggers on the old table to populate the new table, and then copying all of the data. Once done, remove the old table and rename the new table. It’s fascinating when you think about the simplicity of it.
However, I still often go old school with a simple ALTER TABLE command. In the past, when I wanted to see the progress that has been made, I simply looked at the files on disk and compared the table being updated’s file size and the temporary table. As the temporary table grew close to the same size, I knew it was done.
Today I learned there was a SQL query you can run. I then modified what was given to me to also include the number left (I don’t know what the number means), and the percentage completed. That final number is the one I am finding most fascinating right now.
I am documenting this here for my future self as I will one day ask what that query was and go digging trying to find it.
SELECT
EVENT_NAME,
WORK_COMPLETED,
WORK_ESTIMATED,
WORK_ESTIMATED-WORK_COMPLETED AS REMAINING,
WORK_COMPLETED/WORK_ESTIMATED*100 as PercentDone
FROM
performance_schema.events_stages_current