# mysqlcheck -o innodb_test -u root -p innodb_test.b_admin_notify If you check the database size before and after the optimization, you will see that the total size has reduced: # mysqlcheck -o -all-databases -u root -p Or run the optimization of all databases on the server: To optimize all tables in a database, run this command in your server console: You can also run defragmentation using mysqlcheck in your server console: # mysqlcheck -o innodb_test b_workflow_file -u root -p innodb_test.b_workflow_fileĪnd b_workflow_file is the name of the table # OPTIMIZE TABLE b_disk_deleted_log_v2, b_disk_object_path, b_crm_timeline_bind Īfter successful defragmentation, you will see an output like this: +-+-+-+Īs you can see, data_free_mb equals to 0 now and the table size has reduced significantly (3 – 4 times). To optimize these tables, run the following command in the mysql console: rw-r- 1 mysql mysql 981M Oct 17 11:54 b_disk_object_path.MYD rw-r- 1 mysql mysql 828M Oct 17 13:23 b_crm_timeline_bind.MYD # ls -lh /var/lib/mysql/innodb_test/ | grep b_ -rw-r- 1 mysql mysql 402M Oct 17 12:12 b_disk_deleted_log_v2.MYD Check how much space they occupy on the disk: | TABLE_NAME | data_length_mb | data_free_mb | Thus, you will display all tables with at least 50 MB of unused space: +-+-+-+ Select table_name, round(data_length/1024/1024) as data_length_mb, round(data_free/1024/1024) as data_free_mb from information_schema.tables where round(data_free/1024/1024) > 50 order by data_free_mb Open the MySQL console, select a database and run this query: Make sure if there are any tables in the database that require defragmentation. To optimize tables and databases, it is recommended to defragment them. Optimizing Tables and Database in MySQL and MariaDB # du -sh modx_session.OLD 25M modx_session.OLD # ls -la modx_session.OLD -rw-r- 1 mysql mysql 25550000 Dec 17 15:20 modx_session.OLD When you add it, a table is backed up before compression and marked with OLD label: # du -sh modx_session.MYD 18M modx_session.MYD Remember to run myisamchk -rq on compressed tables # myisampack -b /var/lib/mysql/test/modx_session Compressing /var/lib/mysql/test/modx_session.MYD: (4933 records) Select the database you need in your mysql console: Prior to doing anything, I recommend to backup all databases.
MYSQL OPTIMIZER TURN OFF FREE
I have an innodb_test database containing tables that can potentially be compressed and thus I can free some disk space. You can compress tables with text/BLOB data and save quite a lot of disk space.
Back up all databases on your server (except mysql and performance_schema).If your server is configured and you have some productive databases with InnoDB tables, do the following:
0 kommentar(er)
