The shell script uses lsof to read file offsets of files. This is neat. But it won't work for database tables in tablespaces (i.e. when not using file per table).
I’m not too sure if it covers table changes but MySQL comes with a slow log tool which tells you what queries are causing performance issues and if need be help optimize query requests itself, indexing even though is painstaking is perhaps the best way to fix the issue in the long run but to track down the slow queries in the first place and mitigate against them individually I’d use slow log.
Sorry but adding a new index on a table with 6 billion rows is stupid. If you did this in production, it would take that table offline for possibly days. And if it somehow failed, you would be SOL.
You create a copy of the table with an updated set of indexes and copy the data over into the new table. I think Percona even has a utility that will copy all the next data over via stored procedure that is getting written to the table.
Are you sure? This was the case in mysql 5.5 but 5.6 and up will not take the table offline anymore.
> Several ALTER TABLE operations can be performed without copying the table, without blocking inserts, updates, and deletes to the table, or both. These enhancements are known collectively as online DDL. See Section 14.13, “InnoDB and Online DDL” for details.
Doesn't adding an index only take out a brief metadata lock at the start of the operation, and then leave the table safe for queries while the rest of indexing completes?
The article is MariaDB specific.