[FEATURE] Scheduler: Add task to optimize MySQL tables 40/43040/3
authorMorton Jonuschat <m.jonuschat@mojocode.de>
Fri, 4 Sep 2015 18:42:38 +0000 (20:42 +0200)
committerChristian Kuhn <lolli@schwarzbu.ch>
Wed, 9 Sep 2015 09:44:52 +0000 (11:44 +0200)
A scheduler task to run the ``OPTIMIZE TABLE`` command on selected
database tables has been added. The ``OPTIMIZE TABLE`` command
reorganizes the physical storage of table data and associated index
data to reduce storage space and improve I/O efficiency.

The scheduler task only shows database tables with storage engines
that support the ``OPTIMIZE TABLE`` command.

Resolves: #25341
Releases: master
Change-Id: I35b677487baaa505fd398749853b663251457c6b
Reviewed-on: http://review.typo3.org/43040
Reviewed-by: Georg Ringer <georg.ringer@gmail.com>
Tested-by: Georg Ringer <georg.ringer@gmail.com>
Reviewed-by: Christian Kuhn <lolli@schwarzbu.ch>
Tested-by: Christian Kuhn <lolli@schwarzbu.ch>
typo3/sysext/core/Documentation/Changelog/master/Feature-25341-SchedulerTaskToOptimizeDatabaseTables.rst [new file with mode: 0644]
typo3/sysext/scheduler/Classes/Task/OptimizeDatabaseTableAdditionalFieldProvider.php [new file with mode: 0644]
typo3/sysext/scheduler/Classes/Task/OptimizeDatabaseTableTask.php [new file with mode: 0644]
typo3/sysext/scheduler/Resources/Private/Language/locallang.xlf
typo3/sysext/scheduler/ext_localconf.php

diff --git a/typo3/sysext/core/Documentation/Changelog/master/Feature-25341-SchedulerTaskToOptimizeDatabaseTables.rst b/typo3/sysext/core/Documentation/Changelog/master/Feature-25341-SchedulerTaskToOptimizeDatabaseTables.rst
new file mode 100644 (file)
index 0000000..51e2e13
--- /dev/null
@@ -0,0 +1,32 @@
+============================================================
+Feature: #25341 - Scheduler task to optimize database tables
+============================================================
+
+Description
+===========
+
+A scheduler task to run the ``OPTIMIZE TABLE`` command on selected
+database tables has been added. The ``OPTIMIZE TABLE`` command
+reorganizes the physical storage of table data and associated index
+data to reduce storage space and improve I/O efficiency when
+accessing the table. The exact changes made to each table depend
+on the storage engine used by that table. For more information see
+the `MySQL manual`_.
+
+The scheduler task is meant for the MySQL database system and only
+shows tables matching the MySQL storage engines MyISAM, InnoDB and
+ARCHIVE. Using this task with DBAL and other DBMS is not supported
+as the commands used are MySQL specific.
+
+
+Impact
+======
+
+Optimizing tables is I/O intensive. On MySQL < 5.6.17 it also locks
+the tables for the whole time, which can severely impact the website
+while it is running. When considering whether or not to run optimize,
+consider the workload of transactions that your server will process
+as InnoDB tables do not suffer from fragmentation in the same way
+that MyISAM tables do.
+
+.. _MySQL manual: https://dev.mysql.com/doc/refman/5.6/en/optimize-table.html
diff --git a/typo3/sysext/scheduler/Classes/Task/OptimizeDatabaseTableAdditionalFieldProvider.php b/typo3/sysext/scheduler/Classes/Task/OptimizeDatabaseTableAdditionalFieldProvider.php
new file mode 100644 (file)
index 0000000..3b88231
--- /dev/null
@@ -0,0 +1,127 @@
+<?php
+namespace TYPO3\CMS\Scheduler\Task;
+
+/*
+ * This file is part of the TYPO3 CMS project.
+ *
+ * It is free software; you can redistribute it and/or modify it under
+ * the terms of the GNU General Public License, either version 2
+ * of the License, or any later version.
+ *
+ * For the full copyright and license information, please read the
+ * LICENSE.txt file that was distributed with this source code.
+ *
+ * The TYPO3 project - inspiring people to share!
+ */
+
+/**
+ * Additional BE fields for optimize database table task.
+ */
+class OptimizeDatabaseTableAdditionalFieldProvider implements \TYPO3\CMS\Scheduler\AdditionalFieldProviderInterface {
+
+       /**
+        * Add a multi select box with all available database tables.
+        *
+        * @param array $taskInfo Reference to the array containing the info used in the add/edit form
+        * @param AbstractTask|NULL $task When editing, reference to the current task. NULL when adding.
+        * @param \TYPO3\CMS\Scheduler\Controller\SchedulerModuleController $parentObject Reference to the calling object (Scheduler's BE module)
+        * @return array Array containing all the information pertaining to the additional fields
+        */
+       public function getAdditionalFields(array &$taskInfo, $task, \TYPO3\CMS\Scheduler\Controller\SchedulerModuleController $parentObject) {
+               // Initialize selected fields
+               if (empty($taskInfo['scheduler_optimizeDatabaseTables_selectedTables'])) {
+                       $taskInfo['scheduler_optimizeDatabaseTables_selectedTables'] = array();
+                       if ($parentObject->CMD === 'add') {
+                               // In case of new task, select no tables by default
+                               $taskInfo['scheduler_optimizeDatabaseTables_selectedTables'] = array();
+                       } elseif ($parentObject->CMD === 'edit') {
+                               // In case of editing the task, set to currently selected value
+                               $taskInfo['scheduler_optimizeDatabaseTables_selectedTables'] = $task->selectedTables;
+                       }
+               }
+               $fieldName = 'tx_scheduler[scheduler_optimizeDatabaseTables_selectedTables][]';
+               $fieldId = 'scheduler_optimizeDatabaseTables_selectedTables';
+               $fieldOptions = $this->getDatabaseTableOptions($taskInfo['scheduler_optimizeDatabaseTables_selectedTables']);
+               $fieldHtml = '<select class="form-control" name="' . $fieldName . '" id="' . $fieldId . '" class="from-control" size="10" multiple="multiple">' . $fieldOptions . '</select>';
+               $additionalFields[$fieldId] = array(
+                       'code' => $fieldHtml,
+                       'label' => 'LLL:EXT:scheduler/Resources/Private/Language/locallang.xlf:label.optimizeDatabaseTables.selectTables',
+                       'cshKey' => '_MOD_system_txschedulerM1',
+                       'cshLabel' => $fieldId
+               );
+               return $additionalFields;
+       }
+
+       /**
+        * Checks that all selected backends exist in available backend list
+        *
+        * @param array $submittedData Reference to the array containing the data submitted by the user
+        * @param \TYPO3\CMS\Scheduler\Controller\SchedulerModuleController $parentObject Reference to the calling object (Scheduler's BE module)
+        * @return bool TRUE if validation was ok (or selected class is not relevant), FALSE otherwise
+        */
+       public function validateAdditionalFields(array &$submittedData, \TYPO3\CMS\Scheduler\Controller\SchedulerModuleController $parentObject) {
+               $validData = TRUE;
+               $availableTables = array_keys($this->getDatabaseTables());
+               if (is_array($submittedData['scheduler_optimizeDatabaseTables_selectedTables'])) {
+                       $invalidTables = array_diff($submittedData['scheduler_optimizeDatabaseTables_selectedTables'], $availableTables);
+                       if (!empty($invalidTables)) {
+                               $parentObject->addMessage($GLOBALS['LANG']->sL('LLL:EXT:scheduler/Resources/Private/Language/locallang.xlf:msg.selectionOfNonExistingDatabaseTables'), \TYPO3\CMS\Core\Messaging\FlashMessage::ERROR);
+                               $validData = FALSE;
+                       }
+               } else {
+                       $parentObject->addMessage($GLOBALS['LANG']->sL('LLL:EXT:scheduler/Resources/Private/Language/locallang.xlf:msg.noDatabaseTablesSelected'), \TYPO3\CMS\Core\Messaging\FlashMessage::ERROR);
+                       $validData = FALSE;
+               }
+               return $validData;
+       }
+
+       /**
+        * Save selected backends in task object
+        *
+        * @param array $submittedData Contains data submitted by the user
+        * @param \TYPO3\CMS\Scheduler\Task\AbstractTask $task Reference to the current task object
+        * @return void
+        */
+       public function saveAdditionalFields(array $submittedData, \TYPO3\CMS\Scheduler\Task\AbstractTask $task) {
+               $task->selectedTables = $submittedData['scheduler_optimizeDatabaseTables_selectedTables'];
+       }
+
+       /**
+        * Build select options of available backends and set currently selected backends
+        *
+        * @param array $selectedTables Selected backends
+        * @return string HTML of selectbox options
+        */
+       protected function getDatabaseTableOptions(array $selectedTables) {
+               $options = array();
+               $availableTables = $this->getDatabaseTables();
+               foreach ($availableTables as $tableName => $tableInformation) {
+                       $selected = in_array($tableName, $selectedTables, TRUE) ? ' selected="selected"' : '';
+                       $options[] = '<option value="' . $tableName . '"' . $selected . '>' . $tableName . '</option>';
+               }
+               return implode('', $options);
+       }
+
+       /**
+        * Get all registered caching framework backends
+        *
+        * @return array Registered backends
+        */
+       protected function getDatabaseTables() {
+               $tables =  $this->getDatabaseConnection()->admin_get_tables();
+               $tables = array_filter(
+                       $tables,
+                       function($table) {
+                               return !empty($table['Engine']) && in_array($table['Engine'], array('MyISAM', 'InnoDB', 'ARCHIVE'));
+                       }
+               );
+               return $tables;
+       }
+
+       /**
+        * @return \TYPO3\CMS\Core\Database\DatabaseConnection
+        */
+       protected function getDatabaseConnection() {
+               return $GLOBALS['TYPO3_DB'];
+       }
+}
diff --git a/typo3/sysext/scheduler/Classes/Task/OptimizeDatabaseTableTask.php b/typo3/sysext/scheduler/Classes/Task/OptimizeDatabaseTableTask.php
new file mode 100644 (file)
index 0000000..185059a
--- /dev/null
@@ -0,0 +1,70 @@
+<?php
+namespace TYPO3\CMS\Scheduler\Task;
+
+/*
+ * This file is part of the TYPO3 CMS project.
+ *
+ * It is free software; you can redistribute it and/or modify it under
+ * the terms of the GNU General Public License, either version 2
+ * of the License, or any later version.
+ *
+ * For the full copyright and license information, please read the
+ * LICENSE.txt file that was distributed with this source code.
+ *
+ * The TYPO3 project - inspiring people to share!
+ */
+
+/**
+ * Perform OPTIMIZE TABLE SQL statements
+ *
+ * This task reorganizes the physical storage of table data and associated index data,
+ * to reduce storage space and improve I/O efficiency when accessing the table. The
+ * exact changes made to each table depend on the storage engine used by that table.
+ */
+class OptimizeDatabaseTableTask extends \TYPO3\CMS\Scheduler\Task\AbstractTask {
+
+       /**
+        * Database tables that should be cleaned up,
+        * set by additional field provider.
+        *
+        * @var array Selected tables to optimize
+        */
+       public $selectedTables = array();
+
+       /**
+        * Execute table optimization, called by scheduler.
+        *
+        * @return bool
+        */
+       public function execute() {
+               foreach ($this->selectedTables as $tableName) {
+                       $result = $this->getDatabaseConnection()->admin_query('OPTIMIZE TABLE ' . $tableName . ';');
+                       if ($result === FALSE) {
+                               throw new \RuntimeException(
+                                       \TYPO3\CMS\Scheduler\Task\TableGarbageCollectionTask::class . ' failed for: ' . $tableName . ': ' .
+                                       $this->getDatabaseConnection()->sql_error(),
+                                       1441390263
+                               );
+                       }
+               }
+
+               return TRUE;
+       }
+
+       /**
+        * Output the selected tables
+        *
+        * @return string
+        */
+       public function getAdditionalInformation() {
+               return implode(', ', $this->selectedTables);
+       }
+
+       /**
+        * @return \TYPO3\CMS\Core\Database\DatabaseConnection
+        */
+       protected function getDatabaseConnection() {
+               return $GLOBALS['TYPO3_DB'];
+       }
+
+}
index 41f4b16..722fc00 100644 (file)
                        <trans-unit id="label.tableGarbageCollection.numberOfDays">
                                <source>Delete entries older than given number of days</source>
                        </trans-unit>
+                       <trans-unit id="label.optimizeDatabaseTables.selectTables">
+                               <source>Database tables</source>
+                       </trans-unit>
                        <trans-unit id="label.type">
                                <source>Type</source>
                        </trans-unit>
                        <trans-unit id="msg.userNotCreated">
                                <source>The creation of the "_cli_scheduler" user failed.</source>
                        </trans-unit>
+                       <trans-unit id="msg.selectionOfNonExistingDatabaseTables">
+                               <source>Some of the selected database tables do not exist. Please select only valid tables.</source>
+                       </trans-unit>
+                       <trans-unit id="msg.noDatabaseTablesSelected">
+                               <source>Please select at least one database table.</source>
+                       </trans-unit>
                        <trans-unit id="none">
                                <source>None</source>
                        </trans-unit>
                        <trans-unit id="recyclerGarbageCollection.description">
                                <source>This task empties all "_recycler_" folders below fileadmin. This helps free some space in the file system.</source>
                        </trans-unit>
+                       <trans-unit id="optimizeDatabaseTable.name">
+                               <source>Optimize MySQL database tables</source>
+                       </trans-unit>
+                       <trans-unit id="optimizeDatabaseTable.description">
+                               <source>This task executes "OPTIMIZE TABLE" statements on the selected database tables. This helps to reduce storage space and improve I/O efficiency. Warning! Tables will be locked during the optimization process.</source>
+                       </trans-unit>
                </body>
        </file>
 </xliff>
index 965eda9..0ea19d7 100644 (file)
@@ -99,3 +99,12 @@ if (!is_array($GLOBALS['TYPO3_CONF_VARS']['SC_OPTIONS']['scheduler']['tasks'][\T
                'expirePeriod' => 30
        );
 }
+
+// Add task for optimizing database tables
+$GLOBALS['TYPO3_CONF_VARS']['SC_OPTIONS']['scheduler']['tasks'][\TYPO3\CMS\Scheduler\Task\OptimizeDatabaseTableTask::class] = array(
+       'extension' => 'scheduler',
+       'title' => 'LLL:EXT:scheduler/Resources/Private/Language/locallang.xlf:optimizeDatabaseTable.name',
+       'description' => 'LLL:EXT:scheduler/Resources/Private/Language/locallang.xlf:optimizeDatabaseTable.description',
+       'additionalFields' => \TYPO3\CMS\Scheduler\Task\OptimizeDatabaseTableAdditionalFieldProvider::class
+
+);