Download the PHP package simbiat/optimize-tables without Composer
On this page you can find all versions of the php package simbiat/optimize-tables. It is possible to download/install these versions without Composer. Possible dependencies are resolved automatically.
FAQ
Example:
In general, it is recommended to use always a project to download your libraries. In an application normally there is more than one library needed.
- Some hosting areas are not accessible by a terminal or SSH. Then it is not possible to use Composer.
- To use Composer is sometimes complicated. Especially for beginners.
- Composer needs much resources. Sometimes they are not available on a simple webspace.
- If you are using private repositories you don't need to share your credentials. You can set up everything on our site and then you provide a simple download link to your team member.
- Simplify your Composer build process. Use our own command line tool to download the vendor folder as binary. This makes your build process faster and you don't need to expose your credentials for private repositories.
Informations about the package optimize-tables
Database Tables Optimizer
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:
The output will look like this:
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:
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:
This will also create a tables.json file with some more statistics, that will look like this:
There is also possibility to get statistics from the last run by:
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:
If you want to work with regular booleans, you can send one extra 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.
| Function | Parameters | Description |
|---|---|---|
setThreshold |
float $threshold |
Set a threshold for fragmentation of table data. If the current value is more or equal to this value - table will be suggested for OPTIMIZE. |
setSuggest |
string $action, bool $flag |
Flag allowing to suggest a command if flag is set to `true`. `$action` stands for appropriate command: `ANALYZE`, `CHECK`, `COMPRESS`, `OPTIMIZE`, `REPAIR` and `HISTOGRAM` (special case of `ANALYZE`). |
setJsonPath |
string $jsonpath |
Path to save statistics, which are necessary for all consecutive runs. By default file `tables.json` will be written to system's temporary folder. |
setDefragParam |
string $param, float $value |
MariaDB 10.1.1 implemented `innodb_defragment` flag with a set of settings for it. All of them can be set through this function, if non-default are required. Their names can be sent without the `innodb_defragment` prefix. |
setMaintenance |
string $table, string $setting_column, string $setting_name, string $value_column |
Library can take quite some time to run and some commands may lock tables (fully or not), so it's advisable to prevent applications from communicating with them. Usually this is handled by having some kind of system parameter identifying an ongoing maintenance. `$table` stands for table name, `$setting_column` - name of the column where to search for `$setting_name`, that is name of the maintenance flag. `$value_column` is name of the column, which we will be updating. |
setDays |
string $action, int $days |
Set number of days to wait since previous run of an `$action` (same as in `setSuggest`). Unless the designated amount of time has passed the action will not be suggested for the table. |
setExclusions |
string $action, string $table, $column = NULL |
Exclude table(s) from processing for particular `$action`. In case of `histogram` you can also send list of columns to exclude from preparing histograms if we are on MySQL 8.0+. In order to avoid providing excessive columns to histograms' exclusion list, adding only 1 table at a time is allowed. |
