5 ms·
How to monitor MySQL / MariaDB query progress
- wolf550e 7y agoThe 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).
- morgo 7y agoFor MySQL there are better ways to do this: https://mysql.wisborg.dk/2018/08/10/innodb-progress-information/ https://mysql.wisborg.dk/2018/08/10/innodb-progress-informat... The article is MariaDB specific.
- ping_pong 7y agoSorry 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.
- gregwebs 7y agoMariadb 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/ https://mariadb.com/kb/en/library/innodb-online-ddl-overview...
- ro_sharp 7y agoWouldn’t it be preferable if the DBMS did this for you?
- merb 7y agowould be cool if there was an dbms that does that, hm... maybe there is?
- zbentley 7y agoDoesn'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-operations.html https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-op...
- wolf550e 7y agohttps://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html https://www.percona.com/doc/percona-toolkit/LATEST/pt-online...
- mkonecny 7y agoAre 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 https://dev.mysql.com/doc/refman/5.6/en/mysql-nutshell.html
- verelo 7y agoNo, 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.
- durpleDrank 7y agoPrometheus is a good option as well. https://github.com/prometheus/mysqld_exporter https://github.com/prometheus/mysqld_exporter
- jamesbiv 7y agoI’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
- crazygringo 7y agoI would love to see this built-in as a column to SHOW PROCESSLIST, that would show % done.