# Use Percona's tools for large GLPI databases ## Who is Percona? [Percona](https://www.percona.com) is a leading provider of unbiased open source database solutions, they offer a wide range of products (complete from database server/clustering solution to monitoring or administration tools). ## Which tool to use and why? For changes (modification of columns or data migration) **in large GLPI databases**, based on MySQL / MariaDB, you can use the tool **"pt-online-schema-change"**, which is included in the **"percona-toolkit"** developed by Percona, for example: * Converting all data from **MyISAM** engine to **InnoDB** engine * Migrate all **DATETIME** collumn to **TIMESTAMP** format (for timezone support) * Adding / removing / recovering an Index on **glpi\_logs** table By saying "large database", we mean those whose **glpi\_logs** table exceeds 5GB of data, but of course it's possible to use these tools on all databases (regardless of their size). **pt-online-schema-change** is more efficient and more secure than the command line tools (**bin/console**) provided in GLPI core release (useful for less important databases). ## Official documentation * Installing **percona-toolkit**: * * OR * Using **pt-online-schema-change**: * ## Risks As indicated in the official documentation: Percona Toolkit is mature, proven in the real world, and well tested, but all database tools can pose a risk to the system and the database server (like any others sysadmin/DBA tools). {% hint style="danger" %} **Danger** **Before using this tool, please:** * Read the tool’s documentation * Review the tool’s known "BUGS" * Test the tool on a non-production server * Backup your production server and verify the backups {% endhint %} **TECLIB cannot be held responsible for improper usage conducting to loss of data.** ## Warning about disk usage The main principle of **pt-online-schema-change** is to create a temporary table that corresponds to the ALTER you want to do, then copy the data from the old table to the new one, and delete the old table if everything is ok. Therefore, pay attention to the disk space usage: it will temporarily double (or triple), then return to normal (or decrease in case of "OPTIMIZE"). {% hint style="success" %} **Disk size** If your **glpi\_logs** table (before intervention) is 25GB, we recommend having a **free** disk space of at least 50/75GB to perform the operation.Warning about execution time {% endhint %} Although thanks to this tool, the data migrations are much faster (than with the bin/console tool of GLPI), we always advise (like any good self-respecting Linux system administrator) to execute all your commands in a virtual terminal or terminal multiplexer (like: tmux or screen). If for any reason you lose SSH connection/session, being in a virtual terminal the execution of your command will continue without you! :sunglasses: {% hint style="success" %} **Example with** [**tmux**](https://fr.wikipedia.org/wiki/Tmux) * On Ubuntu 20.04 LTS: `apt-get install tmux` * Create a new tmux session: `tmux new -s innodb_migration` * Run your commands ... * Detach without closing the session: `CTRL+b` then `d` key * Connect to the existing session: `tmux attach -t innodb_migration` * Quit a session: `exit`Migrate data from MyISAM to InnoDB {% endhint %} ## Migrate data from MyISAM to InnoDB GLPI command: * **pt-online-schema-change** command: * `--alter "ENGINE=InnoDB"` Here is the complete cli usage, you can test with `--dry-run` instead of `--execute`: ```bash $ pt-online-schema-change \ --alter "ENGINE=InnoDB" \ --ask-pass \ --execute \ D=glpi,t=glpi_logs,u=root,h=localhost ``` You can change the variables: * `u` as user database * `h` as host database * `D` as database name * `t` as glpi table to alter ## Use timestamp data type GLPI command: * **pt-online-schema-change** command: * `--alter "MODIFY COLUMN date_mod TIMESTAMP NULL DEFAULT NULL"` Here is the complete cli usage, you can test with `--dry-run` instead of `--execute`: ```bash $ pt-online-schema-change \ --alter "MODIFY COLUMN date_mod TIMESTAMP NULL DEFAULT NULL" \ --ask-pass \ --execute \ D=glpi,t=glpi_logs,u=root,h=localhost ``` You can change the variables: * `u` as user database * `h` as host database * `D` as database name * `t` as glpi table to alter ## Repair corrupted INDEXES If by accident or mistake, the INDEXES of one of your tables are corrupted (you discovered it thanks to the MySQL **CHECK** command), we recommend to re-run the `--alter "ENGINE=InnoDB"` command, your table **INDEXs** will be rebuilt while keeping your data safe. ## Recover disk space after deleting data After having done a lot of cleaning in your **glpi\_logs** table (deleting millions of lines for example) you realize that the disk space used has not changed on the filesystem side. Without going into the details of how the InnoDB storage engine works, tell yourself that this is normal you need to "OPTIMIZE" your table. To do this, we recommend once again to re-run the `--alter "ENGINE=InnoDB"` command, your table **INDEXs** will be rebuilt while keeping your data safe and recover the used disk space. ## Run for all tables Here is a script to alter all tables for specific database. Be sure to adapt the **ALTER\_COMMAND** command with the right action to perform (see previous chapters). (you can test with `--dry-run` instead of `--execute`) ```bash #!/bin/bash DBNAME=glpi DBUSER=root DBPWD=password DBHOST=localhost ALTER_COMMAND="MODIFY COLUMN date_mod TIMESTAMP NULL DEFAULT NULL" for TABLENAME in $(mysql -h$DBHOST -u$DBUSER -p$DBPWD --batch --column-names=false -e "show tables" $DBNAME); do pt-online-schema-change \ --alter $ALTER_COMMAND \ --execute \ D=$DBNAME,t=$TABLENAME,u=$DBUSER,p=$DBPWD,h=$DBHOST done ``` --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://help.glpi-project.org/tutorials/procedures/linux_percona_toolkit.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.