Large database size

Databases can grow over time and become quite large, which in the future may lead to a violation of the rules for providing services or filling up disk space. There are many reasons for this behavior, ranging from the chosen type of storage, ending with the constantly written data in the database. This article provides some guidelines for reducing the footprint of your databases.

The most common reasons for database growth are:

  • The storage type is InnoDB... InnoDB, unlike MyISAM, supports transactions, foreign keys, and other useful features. The downside to InnoDB is that tables only grow in size and never shrink. This is due to the fact that when you delete records from the table, the space under them remains reserved for new rows. Therefore, deleting data does not reduce the disk space that the table occupies. To reduce the size of tables, you can use the optimization function — it not only reduces the size of the database, but in some cases also speeds up the work with the optimized data:
    1. Run optimizing tables Database.
    2. After completing the operation, press the button to update the information about the space occupied by the database.
  • Features of the site or CMS:
    • Storing sessions in a database... Some CMS by default, sessions are written to the database, which increases the load on the database, and can also provoke a huge increase in the size of the database due to problems with deleting sessions or a very large audience of the site. We strongly recommend using files for storing sessions, Memcache(d) or Redis... If, nevertheless, it is necessary to store sessions in the database, you should check for outdated sessions, since in some situations a failure occurs and records in the database only accumulate, which causes a large increase in the occupied space.
    • Writing cached data to the database... There are a number of plugins and systems that create a cache of pages or site elements and put it into a database, which in the future can affect both performance and the size of the database. If the site has such functionality, you should carefully consider its use and for large projects completely abandon it. It is better to store the cache as files or using additional, more productive technologies, for example Memcache or Redis.