simbiat / optimize-tables
Library optimize tables in database.
Requires
- php: ^8.3
- ext-mbstring: *
- simbiat/database: ^2
- dev-master
- 2.1.11+20240616
- 2.1.10+20240510
- 2.1.9+20240502
- 2.1.8+20240408
- 2.1.7+20221010
- 2.1.6+20211228
- 2.1.5+20211227
- 2.1.4+20210601
- 2.1.3+20210525
- 2.1.2+20210429
- 2.1.1+20210428
- 2.1.0+20210424
- 2.0.3+20210415
- 2.0.2+20210314
- 2.0.1+20210311
- 2.0.0+20210310
- 1.0.3+20201130
- 1.0.2+20200905
- 1.0.1+20200406
- 1.0.0+20200405
This package is auto-updated.
Last update: 2024-12-13 09:11:30 UTC
README
This library is developed to allow bulk optimization of tables in MySQL (MariaDB and, potentially, other forks). While MS SQL has its Maintenance Plan Wizard, there is nothing like that for MySQL.
Benefits
One may think that simply getting list of tables and running OPTIMIZE against them is enough, but in reality, it's not that simple:
- Not all tables support OPTIMIZE.
- There are some special parameters that may improve OPTIMIZE results in some cases.
- Some tables may benefit from CHECK and REPAIR commands as well. In fact, it's useful to periodically run CHECK to avoid potential corruption of a table.
- ANALYZE is quite useful a command as well, allowing to update MySQL statistics, that may improve some of SELECT.
- MariaDB 10.4+ and MySQL 8.0+ also support histograms, that may improve SELECT in cases when a column does not have indexes for some reason.
- No matter how useful these commands are, there are cases when you do not need to run them, especially on large tables, since they may take quite some time to complete. The simplest case: there were no or very little changes since last time the OPTIMIZE was run.
This library aims to cover all these points in as smart a manner, as was possible at the moment of writing. For details refer Usage section of this readme or comments in the code.
Requirements
- SimbiatDB
- MySQL or MariaDB
Usage
To use the library you need to establish connection with your database through SimbiatDB library and then call this:
(new \Simbiat\optimizeTables)->analyze('schema');
The output will look like this:
array ( 'bic__bik_swif' => array ( 'TABLE_NAME' => 'bic__bik_swif', 'ENGINE' => 'InnoDB', 'ROW_FORMAT' => 'Compressed', 'TABLE_ROWS' => '387', 'DATA_LENGTH' => '24576', 'INDEX_LENGTH' => '8192', 'DATA_FREE' => '0', 'TOTAL_LENGTH' => '32768', 'FRAGMENTATION' => '0.0000', 'FULLTEXT' => '0', 'TO_COMPRESS' => false, 'TO_ANALYZE' => true, 'TO_CHECK' => true, 'TO_HISTOGRAM' => true, 'TO_OPTIMIZE' => false, 'TO_REPAIR' => false, 'COMMANDS' => array ( 0 => 'SET @@SESSION.old_alter_table=false;', 1 => 'SET @@SESSION.alter_algorithm=\'INPLACE\';', 2 => 'SET @@GLOBAL.innodb_defragment=true;', 3 => 'CHECK TABLE `simbiatr_simbiat`.`bic__bik_swif` FOR UPGRADE EXTENDED;', 4 => 'ANALYZE TABLE `simbiatr_simbiat`.`bic__bik_swif`;', 5 => 'ANALYZE TABLE `simbiatr_simbiat`.`bic__bik_swif` PERSISTENT FOR ALL;', 6 => 'SET @@SESSION.old_alter_table=DEFAULT;', 7 => 'SET @@SESSION.alter_algorithm=DEFAULT;', 8 => 'SET @@GLOBAL.innodb_defragment=DEFAULT;', 9 => 'SET @@GLOBAL.innodb_optimize_fulltext_only=DEFAULT;', ), ), )
schema means the name of the database/schema you want to analyze. This command will show you the list (as an array) of tables with some statistics and list of optimization commands they can/should be run for each table.
After this you have an option to run them manually if you like some control. Alternatively, if you want to use the library in a cron or other scheduler you can run this:
(new \Simbiat\optimizeTables)->optimize('schema');
This will analyze the tables and then run the suggested commands. The result of the function (by default) will be an array of log entries, where array keys are UNIX micro timestamps:
array ( 1586091446410816 => 'Getting list of tables...', 1586091449185271 => 'Attempting to enable maintenance mode using \'UPDATE `simbiatr_simbiat`.`settings` SET value = true WHERE `settings`.`setting` = \'maintenance\';\'...', 1586091449185567 => 'Maintenance mode enabled.', 1586091449185579 => 'Updating settings for optimization...', 1586091449185584 => 'Attempting to update setting \'SET @@SESSION.old_alter_table=false;\'...', 1586091449185704 => 'Successfully updated setting.', 1586091449185716 => 'Attempting to update setting \'SET @@SESSION.alter_algorithm=\'INPLACE\';\'...', 1586091449185839 => 'Successfully updated setting.', 1586091449185850 => 'Attempting to update setting \'SET @@GLOBAL.innodb_defragment=true;\'...', 1586091449186018 => 'Successfully updated setting.', 1586091451043050 => 'Checking `bic__bik_swif`...', 1586091451043385 => 'Successfully checked `bic__bik_swif`.', 1586091451043407 => 'Analyzing `bic__bik_swif`...', 1586091451046106 => 'Successfully analyzed `bic__bik_swif`.', 1586091451046131 => 'Creating histograms for `bic__bik_swif`...', 1586091451060711 => 'Successfully created histograms for `bic__bik_swif`.', 1586091762231919 => 'Reverting settings after optimization...', 1586091762231934 => 'Attempting to update setting \'SET @@SESSION.old_alter_table=DEFAULT;\'...', 1586091762232198 => 'Successfully updated setting.', 1586091762232225 => 'Attempting to update setting \'SET @@SESSION.alter_algorithm=DEFAULT;\'...', 1586091762232435 => 'Successfully updated setting.', 1586091762232462 => 'Attempting to update setting \'SET @@GLOBAL.innodb_defragment=DEFAULT;\'...', 1586091762232675 => 'Successfully updated setting.', 1586091762232702 => 'Attempting to update setting \'SET @@GLOBAL.innodb_optimize_fulltext_only=DEFAULT;\'...', 1586091762232911 => 'Successfully updated setting.', 1586091762232956 => 'Attempting to disable maintenance mode using \'UPDATE `simbiatr_simbiat`.`settings` SET value = true WHERE `settings`.`setting` = \'maintenance\';\'...', 1586091762237817 => 'Maintenance mode disabled.', )
This will also create a tables.json file with some more statistics, that will look like this:
{
"logs": {
"1586090907695229": "Getting list of tables...",
"1586090910393167": "No tables to process were returned. Skipping..."
},
"before": {
"bic__bik_swif": {
"TABLE_NAME": "bic__bik_swif",
"ENGINE": "InnoDB",
"ROW_FORMAT": "Compressed",
"TABLE_ROWS": "387",
"DATA_LENGTH": "24576",
"INDEX_LENGTH": "8192",
"DATA_FREE": "0",
"TOTAL_LENGTH": "32768",
"FRAGMENTATION": "0.0000",
"FULLTEXT": "0",
"TO_COMPRESS": false,
"TO_ANALYZE": true,
"TO_CHECK": true,
"TO_HISTOGRAM": true,
"TO_OPTIMIZE": false,
"TO_REPAIR": false,
"CHECK_DATE": 1586078961,
"CHECK_TIME": 224,
"ANALYZE_DATE": 1586078961,
"ANALYZE_TIME": 2087,
"HISTOGRAM_DATE": 1586078961,
"HISTOGRAM_TIME": 13604
}
},
"after": {
"bic__bik_swif": {
"TABLE_NAME": "bic__bik_swif",
"ENGINE": "InnoDB",
"ROW_FORMAT": "Compressed",
"TABLE_ROWS": "387",
"DATA_LENGTH": "24576",
"INDEX_LENGTH": "8192",
"DATA_FREE": "0",
"TOTAL_LENGTH": "32768",
"FRAGMENTATION": "0.0000",
"FULLTEXT": "0",
"TO_COMPRESS": false,
"TO_ANALYZE": true,
"TO_CHECK": true,
"TO_HISTOGRAM": true,
"TO_OPTIMIZE": false,
"TO_REPAIR": false,
"CHECK_DATE": 1586078961,
"ANALYZE_DATE": 1586078961,
"HISTOGRAM_DATE": 1586078961
}
}
}
There is also possibility to get statistics from the last run by:
(new \Simbiat\optimizeTables)->showStats();
This will show all tables, that were either compressed or had changes in their size statistics.
In case you want these statistics to be returned instead of logs, you run this:
(new \Simbiat\optimizeTables)->optimize('schema', true);
If you want to work with regular booleans, you can send one extra true:
(new \Simbiat\optimizeTables)->optimize('schema', true, true);
This is it - easy to use. There are also some settings, that will allow you more control on what is done by optimize().
Settings
All settings can be chained together. To check current setting, you can replace set with get in the function name and remove parameter, which actually sets the value.