How to monitor MySQL / MariaDB query progress(spinellis.gr)
spinellis.gr
How to monitor MySQL / MariaDB query progress
https://www.spinellis.gr/blog/20191103/
15 comments
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.
https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html
https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html
Prometheus is a good option as well.
https://github.com/prometheus/mysqld_exporter
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.
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.
https://dev.mysql.com/doc/refman/5.6/en/mysql-nutshell.html
> 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.
https://dev.mysql.com/doc/refman/5.6/en/mysql-nutshell.html
No, but it would take so much CPU and OI, it might as well. I doubt the application would be in great shape while this operation took place.
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?
https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-op...
https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-op...
Mariadb does this already, maybe even automatically depending on your settings, but I haven't used it myself. https://mariadb.com/kb/en/library/innodb-online-ddl-overview...
I would love to see this built-in as a column to SHOW PROCESSLIST, that would show % done.
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).
The article is MariaDB specific.