Help Center
Home Hosting MySQL & MariaDB Databases

Optimizing MySQL/MariaDB Databases to Prevent Exceeding the Capacity Limit

In our current packages, you have 2 gigabyte of storage space per database available. If this limit is exceeded, write access to this database will be blocked until the limit is undercut again. The following instructions show you how to optimize your database and thus prevent it from being locked.

You can determine the used storage space of a database by looking at the value shown in your IONOS account or by using phpMyAdmin.

Please note that phpMyAdmin displays an incorrect value for the memory usage when using InnoDB tables. For this reason, the value displayed in your IONOS account under Hosting > Contract selection > Databases > Manage is being used as the point of reference.

A possible reason for a heavily overcommitted memory in your database can be frequent changes to tables. As a result, more and more space is occupied by the database itself, although not many changes to the actual storage space occur. You can reorganize and optimize storage space used by the database.

Note
  • It is not possible to increase the storage space for the database to more than 2 gigabyte.
  • If you have already exceeded the maximum storage space of your database, cleanup is only possible via IONOS customer service. An exception applies to innodb tables where an Alter Table command can be used, see step 8 below.

Freeing Up Storage Space

Guided Steps

  • Log in to your IONOS account.

  • Click the Hosting tile and select the appropriate hosting contract.
  • Click Manage in the Databases tile. An overview of your databases will be displayed.
  • Click the Open link in the row where your database is displayed. The administration program phpMyAdmin will open in a new window.
  • Click on the database name in the tree view. The details view will display all the tables in this database.
  • Select SQL in the menu bar.
  • Type the following command in the SQL window:

    SELECT concat('ALTER TABLE `', table_schema, '`.`', table_name, '` FORCE;')
    FROM information_schema.tables
    WHERE data_free / (data_length + index_length + data_free) > 0.6
    ORDER BY data_free DESC;

    SQL commands will be generated which will allow you to free memory. Execute the Optimize commands in your SQL window. dbname corresponds to your actual database name.

    +---------------------------------------------------------------------+
    | concat('ALTER TABLE `', table_schema, '`.`', table_name, '` FORCE;')|
    +---------------------------------------------------------------------+
    | ALTER TABLE `dbname`.`attemptoptions` FORCE;                        |
    | ALTER TABLE `dbname`.`attemptsimple_login_log` FORCE;               |
    | ALTER TABLE `dbname`.`attemptposts`FORCE;                           |
    | ALTER TABLE `dbname`.`attemptpostmeta` FORCE;                       |
    +---------------------------------------------------------------------+
    4 rows in set (0.01 sec)

    Provided the database is not locked, you can execute these SQL commands regardless of the actual memory usage.

Note

The disk space you cleaned up will be available in the database after a time delay. Usually you can use your database again the following day.

Automating Optimization

You can automate optimization by using Cron Jobs. For more information about Cron Jobs, please see these Help Center articles.

Content