Optimize Mysql InnoDB tables

Navigation
0

I have a MySQL database of almost 15 GB with many tables, some of them gigantic.

I wanted to do the optimization task but being INNODB is not possible.

From what I have been told this database has been running since 2000 and has never been done any maintenance work.

What is the best way you know to optimize this type of database?

    
asked by IcuScc 12.04.2018 в 08:21
source

1 answer

1

In MySQL 5.7 you can optimize InnoDB tables

  

OPTIMIZE TABLE works for InnoDB, MyISAM, and ARCHIVE tables. OPTIMIZE   TABLE is also supported for dynamic columns of in-memory NDB tables.   It does not work for fixed-width columns of in-memory tables, nor does   it work for Disk Data tables.

If you are running on a server with an old version, it would be advisable:

  • Remove a complete dump from the database
  • Raise a server with MySQL 5.7 and activate the option innodb_file_per_table (this helps a lot for future optimizations)
  • Load the dump
  • Point the application to the new DB

    • Removing the dump and loading it in the new DB will leave you the optimized tables, but for future optimizations you'll want to be running in 5.7 and have each table in its own file.

        
    answered by 12.04.2018 в 13:45
    How to decrypt data from an array codeigniter3 Error Xamarin Forms MediaPlugin