Optimising MySQL Database to Prevent Exceeding Capacity Limit

In our current packages, you have 2 gigabyte of storage space per MySQL 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 optimise your database and thus prevent it from being locked.

You can find out about the storage space used for a MySQL database by looking at the value shown in your IONOS Customer Account or by using phpMyAdmin. Please note: phpMyAdmin displays an incorrect value for the memory usage when using innodb tables. For this reason, the value displayed in the IONOS Customer 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 MySQL 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 reorganise and optimise 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, clean-up 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.

Free memory space

To optimise a database in your IONOS Customer Account:

  • If you have not yet done so, please log in to your IONOS Customer Account.

  • Click the Hosting tile and select the appropriate hosting contract.
  • Click Manage in the Databases tile. An overview of your created MySQL databases is being 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('optimize table `', table_schema, '`.`', TABLE_NAME, '`;')
    FROM information_schema.tables
    WHERE table_schema = database()
      AND 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('optimize table `', table_schema, '`.`', table_name, '`;') |
    +-------------------------------------------------------------------+
    | optimize table `dbname`.`attemptoptions`;                         |
    | optimize table `dbname`.`attemptsimple_login_log`;                |
    | optimize table `dbname`.`attemptposts`;                           |
    | optimize table `dbname`.`attemptpostmeta`;                        |
    +-------------------------------------------------------------------+
    4 rows in set (0.01 sec)
    Please note: This optimisation can only be performed as long as your database is not locked.
  • Optional: For innodb tables that already exceed the maximum allowed storage space, you can alternatively execute the following SQL command:
    SELECT concat("ALTER TABLE `", table_schema, "`.`", TABLE_NAME, "` ENGINE=InnoDB;")
    FROM information_schema.tables
    WHERE table_schema = database()
      AND ENGINE = 'InnoDB'
      AND data_free / (data_length + index_length + data_free) > 0.6
    ORDER BY data_free DESC;
    SQL commands will be generated which allow you to free memory. Execute the Alter Table commands in your SQL window. dbname corresponds to your actual database name.
    +------------------------------------------------------------------------------+
    | concat('ALTER TABLE `', table_schema, '`.`', table_name, '` ENGINE INNODB;') |
    +------------------------------------------------------------------------------+
    | ALTER TABLE `dbname`.`attemptoptions` ENGINE INNODB;                         |
    | ALTER TABLE `dbname`.`attemptsimple_login_log` ENGINE INNODB;                |
    | ALTER TABLE `dbname`.`attemptposts` ENGINE INNODB;                           |
    | ALTER TABLE `dbname`.`attemptpostmeta` ENGINE INNODB;                        |
    +------------------------------------------------------------------------------+
    4 rows in set (0.60 sec)
Note

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

Automate Optimisation

You can automate optimisation by using Cron Jobs. For more information about Cron Jobs, see the corresponding Help Centre articles.