2.6.1.3.5. Large database size

Databases can grow over time and become quite large, which in the future may lead to violations of service provision rules or filling up disk space. There are many reasons for such behavior, ranging from the chosen type of storage to the constantly recorded data in the DB. This article presents several recommendations for reducing the space occupied by databases.

The use of the storage type InnoDB for database tables has both advantages and disadvantages. In the context of disk space, the main disadvantage of InnoDB is that the size of the tables only grows and never decreases. This is because when records are deleted from a table, the space they occupied remains reserved for new rows. Therefore, deleting data from a table does not lead to a reduction in the space the table occupies on the disk.

Methods for reducing size:

• Optimization of database tables. Automatic updating of database size information occurs every few hours; to speed up the process, press the button to update the database size information.
• Recreating the database:
  1. Export the database.
  2. Save the database name and password.
  3. Delete the database.
  4. Create the database again with the same name and password as the deleted database.
  5. Import the dump back.

Some CMS by default record sessions in the database, which increases the load on the database and can also lead to an increase in the database size due to issues with session deletion or a very large site audience. We strongly recommend using files, Memcache(d), or Redis for session storage. If it is still necessary to store sessions specifically in the database, it is important to check for outdated sessions, as in some situations, failures occur and records in the database only accumulate, causing a significant increase in occupied space.

There are a number of plugins and systems that create a cache of pages or site elements and place it in the database, which can subsequently affect both performance and the size of the database. If such functionality is present on the site, it is advisable to use it with caution, and for large projects, it is better to avoid it altogether. It is preferable to store the cache as files or by using additional, more efficient technologies, such as Memcache or Redis.