2.6.1.3.5. 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.

Usage 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 tables is only grows and never decreases. This is due to the fact that when records are deleted from the table, the space under them remains reserved for new rows. Therefore, deleting data from a table does not reduce the space that the table occupies on disk.

Ways to reduce the size:

• Table optimization Database. After the operation is completed, click the button to update information about the size of the database.
• Recreating the database:
  1. Export database.
  2. Save yourself name and password Database.
  3. Delete database.
  4. Create database again with the same name and password as the remote database.
  5. Import dump back.

Some CMSs log sessions to the database by default, which increases the load on the database, and can also cause the database to grow in size due to problems with deleting sessions or a very large site audience. It is strongly recommended to use 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.

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 later affect both performance and the size of the database. If the site has such functionality, you should carefully consider its use and completely abandon it for large projects. It is better to store the cache in the form of files or using additional, more productive technologies, for example Memcache or Redis.