Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

--- it-wiki:mysql:faq [2023/03/24 07:10] – [Tips to reduce the size of MySQL Database] marko
+++ it-wiki:mysql:faq [2023/03/24 07:18] (aktuell) – [Tips to reduce the size of MySQL Database] marko
@@ Zeile 42: / Zeile 42: @@
 ===== Tips to reduce the size of MySQL Database =====
+==== Backup, first but not least ====
+Best practices suggest to backup your database before take any dangerous action. MySQL and MariaDB include the mysqldump utility to simplify the process to create a backup of a database or system of databases.
+<code bash>
+# Backup Single DB
+mysqldump -u [username] -p [databaseName] > [filename]-$(date +%F).sql
+# Backup Entire DBMS
+mysqldump --all-databases --single-transaction --quick --lock-tables=false > full-backup-$(date +%F).sql -u root -p
+</code>
+If you are using MariaDB, you have the option of  full or incremental backup via ''mariabackup'' utility.
+<code bash>
+$ mariabackup --backup --target-dir=/var/mariadb/backup/ --user=myuser --password=mypassword
+</code>
 ==== List MySQL Table and Index Size ====
 Use the following queries to monitor and evaluation table and index size.
@@ Zeile 133: / Zeile 146: @@
 To enable statistics dynamically execute the following command :
+<code bash>
+SET GLOBAL userstat=1;
+</code>
+Now, every query to the database updates the statistic tables. The ''TABLE_STATISTICS'' and ''INDEX_STATISTICS'' are the most interesting tables. The first table shows the number of rows reads from the table and the number of rows changed in the table. The second table shows statistics on index usage.
+<code bash>
+SELECT * FROM INFORMATION_SCHEMA.TABLE_STATISTICS;
++--------------+------------------------+-----------+--------------+------------------------+
+| TABLE_SCHEMA | TABLE_NAME             | ROWS_READ | ROWS_CHANGED | ROWS_CHANGED_X_INDEXES |
++--------------+------------------------+-----------+--------------+------------------------+
+| hope         | eth_table1             |      1004 |            0 |                      0 |
+| hope         | eth_table2             |  14343683 |            0 |                      0 |
+| hope         | eth_table3             |      1002 |            0 |                      0 |
++--------------+------------------------+-----------+--------------+------------------------+
+</code>
+<code bash>
+SELECT * FROM INDEX_STATISTICS;
++--------------+-------------------+------------+-----------+
+| TABLE_SCHEMA | TABLE_NAME        | INDEX_NAME | ROWS_READ |
++--------------+-------------------+------------+-----------+
+| hope         | eth_table1        | PRIMARY    |         2 |
+| hope         | eth_table2        | PRIMARY    |         4 |
++--------------+-------------------+------------+-----------+
+</code>
+With the help of the new tables, you can find all unused indexes in a single query.
+<code bash>
+SELECT DISTINCT s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME
+FROM information_schema.statistics `s` LEFT JOIN information_schema.index_statistics INDXS
+ON (s.TABLE_SCHEMA = INDXS.TABLE_SCHEMA AND
+   s.TABLE_NAME=INDXS.TABLE_NAME AND
+   s.INDEX_NAME=INDXS.INDEX_NAME)
+WHERE INDXS.TABLE_SCHEMA IS NULL;
+</code>
+Finally, to delete index run this command in ''mysql'' client :
+<code bash>
+DROP INDEX index_name ON table_name;
+</code>
+Do not forget to set ''userstat=0'' when statistics are not required anymore.
+==== Shrink and Optimize MySQL ====
+In general MySQL InnoDB does not release disk space after deleting data rows from the table. It keeps the space to reuse it later.
+''OPTIMIZE TABLE'' reorganizes the physical storage of table data and associated index data, to reduce storage space and improve I/O efficiency when accessing the table. The exact changes made to each table depending on the storage engine used by that table.
+Optimization is available only when ''innodb_file_per_table'' is enabled.  Check your configuration like this :
+<code bash>
+MariaDB [(none)]> show variables like "innodb_file_per_table";
++-----------------------+-------+
+| Variable_name         | Value |
++-----------------------+-------+
+| innodb_file_per_table | ON    |
++-----------------------+-------+
+</code>
+You can use ''OPTIMIZE TABLE'' to reclaim the unused space and to defragment the data file.
+<code bash>
+OPTIMIZE table MyTable;
++-------------------+----------+----------+----------+
+| Table             | Op       | Msg_type | Msg_text |
++-------------------+----------+----------+----------+
+| testDB.MyTable    | optimize | status   | OK       |
++-------------------+----------+----------+----------+
+</code>
+When innodb_file_per_table is OFF, then all data is going to be stored in ibdata files. If you drop some tables and delete some data, then there are two ways to reclaim that unused disk:
+  * Completely dump the database, then drop it and finally reload it
+  * Change the storage engine and revert it back to your previous configuration. This will definitively recreate the table and indexes from the start and unused disk space will be reclaimed.
+<code bash>
+ALTER TABLE my_table ENGINE = MyISAM;
+ALTER TABLE my_table ENGINE = InnoDB;
+</code>
+Whenever you run OPTIMIZE or ALTER TABLE , MySQL will create a new data file until the operation is finished. Do not forget to have enough available space for the operations to finish successfully!
+If you want to optimize a 10 GB Table, ensure that there are more than 10GB of free disk space.