From d153db44ac16ff6befdd37ce1c6fa0fe12deba62 Mon Sep 17 00:00:00 2001
From: Michael <heluecht@pirati.ca>
Date: Tue, 4 Aug 2020 12:24:24 +0000
Subject: [PATCH] Periodically run an "optimize table" command for cache
tables
---
src/Module/Admin/Site.php | 9 ++--
src/Worker/Cron.php | 2 +-
src/Worker/CronJobs.php | 52 ++++++------------------
static/settings.config.php | 4 ++
view/templates/admin/site.tpl | 3 +-
view/theme/frio/templates/admin/site.tpl | 3 +-
6 files changed, 22 insertions(+), 51 deletions(-)
diff --git a/src/Module/Admin/Site.php b/src/Module/Admin/Site.php
index 50566b349f..7cc17fa036 100644
--- a/src/Module/Admin/Site.php
+++ b/src/Module/Admin/Site.php
@@ -173,8 +173,7 @@ class Site extends BaseAdmin
$maxloadavg = (!empty($_POST['maxloadavg']) ? intval(trim($_POST['maxloadavg'])) : 20);
$maxloadavg_frontend = (!empty($_POST['maxloadavg_frontend']) ? intval(trim($_POST['maxloadavg_frontend'])) : 50);
$min_memory = (!empty($_POST['min_memory']) ? intval(trim($_POST['min_memory'])) : 0);
- $optimize_max_tablesize = (!empty($_POST['optimize_max_tablesize']) ? intval(trim($_POST['optimize_max_tablesize'])) : 100);
- $optimize_fragmentation = (!empty($_POST['optimize_fragmentation']) ? intval(trim($_POST['optimize_fragmentation'])) : 30);
+ $optimize_tables = (!empty($_POST['optimize_tables']) ? intval(trim($_POST['optimize_tables'])) : false);
$contact_discovery = (!empty($_POST['contact_discovery']) ? intval(trim($_POST['contact_discovery'])) : Contact\Relation::DISCOVERY_NONE);
$synchronize_directory = (!empty($_POST['synchronize_directory']) ? intval(trim($_POST['synchronize_directory'])) : false);
$poco_requery_days = (!empty($_POST['poco_requery_days']) ? intval(trim($_POST['poco_requery_days'])) : 7);
@@ -301,8 +300,7 @@ class Site extends BaseAdmin
DI::config()->set('system', 'maxloadavg' , $maxloadavg);
DI::config()->set('system', 'maxloadavg_frontend' , $maxloadavg_frontend);
DI::config()->set('system', 'min_memory' , $min_memory);
- DI::config()->set('system', 'optimize_max_tablesize', $optimize_max_tablesize);
- DI::config()->set('system', 'optimize_fragmentation', $optimize_fragmentation);
+ DI::config()->set('system', 'optimize_tables' , $optimize_tables);
DI::config()->set('system', 'contact_discovery' , $contact_discovery);
DI::config()->set('system', 'synchronize_directory' , $synchronize_directory);
DI::config()->set('system', 'poco_requery_days' , $poco_requery_days);
@@ -657,8 +655,7 @@ class Site extends BaseAdmin
'$maxloadavg' => ['maxloadavg', DI::l10n()->t('Maximum Load Average'), DI::config()->get('system', 'maxloadavg', 20), DI::l10n()->t('Maximum system load before delivery and poll processes are deferred - default %d.', 20)],
'$maxloadavg_frontend' => ['maxloadavg_frontend', DI::l10n()->t('Maximum Load Average (Frontend)'), DI::config()->get('system', 'maxloadavg_frontend', 50), DI::l10n()->t('Maximum system load before the frontend quits service - default 50.')],
'$min_memory' => ['min_memory', DI::l10n()->t('Minimal Memory'), DI::config()->get('system', 'min_memory', 0), DI::l10n()->t('Minimal free memory in MB for the worker. Needs access to /proc/meminfo - default 0 (deactivated).')],
- '$optimize_max_tablesize' => ['optimize_max_tablesize', DI::l10n()->t('Maximum table size for optimization'), $optimize_max_tablesize, DI::l10n()->t('Maximum table size (in MB) for the automatic optimization. Enter -1 to disable it.')],
- '$optimize_fragmentation' => ['optimize_fragmentation', DI::l10n()->t('Minimum level of fragmentation'), DI::config()->get('system', 'optimize_fragmentation', 30), DI::l10n()->t('Minimum fragmenation level to start the automatic optimization - default value is 30%.')],
+ '$optimize_tables' => ['optimize_tables', DI::l10n()->t('Periodically optimize tables'), DI::config()->get('system', 'optimize_tables', false), DI::l10n()->t('Periodically optimize tables like the cache and the workerqueue')],
'$contact_discovery' => ['contact_discovery', DI::l10n()->t('Discover followers/followings from contacts'), DI::config()->get('system', 'contact_discovery'), DI::l10n()->t('If enabled, contacts are checked for their followers and following contacts.') . '<ul>' .
'<li>' . DI::l10n()->t('None - deactivated') . '</li>' .
diff --git a/src/Worker/Cron.php b/src/Worker/Cron.php
index 4a49103e09..37d4d4b807 100644
--- a/src/Worker/Cron.php
+++ b/src/Worker/Cron.php
@@ -109,7 +109,7 @@ class Cron
DBA::delete('workerqueue', ['`done` AND `executed` < UTC_TIMESTAMP() - INTERVAL 1 HOUR']);
// Optimizing this table only last seconds
- if (DI::config()->get('system', 'optimize_workerqueue', false)) {
+ if (DI::config()->get('system', 'optimize_tables')) {
DBA::e("OPTIMIZE TABLE `workerqueue`");
}
diff --git a/src/Worker/CronJobs.php b/src/Worker/CronJobs.php
index 5b5c01acab..385109ae20 100644
--- a/src/Worker/CronJobs.php
+++ b/src/Worker/CronJobs.php
@@ -193,46 +193,18 @@ class CronJobs
// Delete the cached "parse_url" entries that are older than three month
DBA::delete('parsed_url', ["`created` < NOW() - INTERVAL 3 MONTH"]);
- // Maximum table size in megabyte
- $max_tablesize = intval(DI::config()->get('system', 'optimize_max_tablesize')) * 1000000;
- if ($max_tablesize == 0) {
- $max_tablesize = 100 * 1000000; // Default are 100 MB
- }
- if ($max_tablesize > 0) {
- // Minimum fragmentation level in percent
- $fragmentation_level = intval(DI::config()->get('system', 'optimize_fragmentation')) / 100;
- if ($fragmentation_level == 0) {
- $fragmentation_level = 0.3; // Default value is 30%
- }
-
- // Optimize some tables that need to be optimized
- $r = q("SHOW TABLE STATUS");
- foreach ($r as $table) {
-
- // Don't optimize tables that are too large
- if ($table["Data_length"] > $max_tablesize) {
- continue;
- }
-
- // Don't optimize empty tables
- if ($table["Data_length"] == 0) {
- continue;
- }
-
- // Calculate fragmentation
- $fragmentation = $table["Data_free"] / ($table["Data_length"] + $table["Index_length"]);
-
- Logger::log("Table " . $table["Name"] . " - Fragmentation level: " . round($fragmentation * 100, 2), Logger::DEBUG);
-
- // Don't optimize tables that needn't to be optimized
- if ($fragmentation < $fragmentation_level) {
- continue;
- }
-
- // So optimize it
- Logger::log("Optimize Table " . $table["Name"], Logger::DEBUG);
- q("OPTIMIZE TABLE `%s`", DBA::escape($table["Name"]));
- }
+ if (DI::config()->get('system', 'optimize_tables')) {
+ Logger::info('Optimize start');
+ DBA::e("OPTIMIZE TABLE `auth_codes`");
+ DBA::e("OPTIMIZE TABLE `cache`");
+ DBA::e("OPTIMIZE TABLE `challenge`");
+ DBA::e("OPTIMIZE TABLE `locks`");
+ DBA::e("OPTIMIZE TABLE `oembed`");
+ DBA::e("OPTIMIZE TABLE `parsed_url`");
+ DBA::e("OPTIMIZE TABLE `profile_check`");
+ DBA::e("OPTIMIZE TABLE `session`");
+ DBA::e("OPTIMIZE TABLE `tokens`");
+ Logger::info('Optimize finished');
}
DI::config()->set('system', 'cache_last_cleared', time());
diff --git a/static/settings.config.php b/static/settings.config.php
index abfb1024e0..19f47d2b8a 100644
--- a/static/settings.config.php
+++ b/static/settings.config.php
@@ -146,6 +146,10 @@ return [
// in the user settings, this controls the maximum file
// size of the upload file.
'max_csv_file_size' => 30720,
+
+ // optimize_tables (Boolean)
+ // Periodically (once an hour) run an "optimize table" command for cache tables
+ 'optimize_tables' => false,
],
// Used in the admin settings to lock certain features
diff --git a/view/templates/admin/site.tpl b/view/templates/admin/site.tpl
index ab97d8218d..5c63970e9f 100644
--- a/view/templates/admin/site.tpl
+++ b/view/templates/admin/site.tpl
@@ -87,8 +87,6 @@
{{include file="field_input.tpl" field=$proxyuser}}
{{include file="field_input.tpl" field=$timeout}}
{{include file="field_input.tpl" field=$maxloadavg_frontend}}
- {{include file="field_input.tpl" field=$optimize_max_tablesize}}
- {{include file="field_input.tpl" field=$optimize_fragmentation}}
{{include file="field_input.tpl" field=$abandon_days}}
{{include file="field_input.tpl" field=$temppath}}
{{include file="field_checkbox.tpl" field=$suppress_tags}}
@@ -115,6 +113,7 @@
{{include file="field_input.tpl" field=$dbclean_expire_days}}
{{include file="field_input.tpl" field=$dbclean_unclaimed}}
{{include file="field_input.tpl" field=$dbclean_expire_conv}}
+ {{include file="field_checkbox.tpl" field=$optimize_tables}}
<div class="submit"><input type="submit" name="page_site" value="{{$submit}}"/></div>
<h2>{{$worker_title}}</h2>
diff --git a/view/theme/frio/templates/admin/site.tpl b/view/theme/frio/templates/admin/site.tpl
index c5a24ffd34..0136b1cd44 100644
--- a/view/theme/frio/templates/admin/site.tpl
+++ b/view/theme/frio/templates/admin/site.tpl
@@ -190,8 +190,6 @@
{{include file="field_input.tpl" field=$proxyuser}}
{{include file="field_input.tpl" field=$timeout}}
{{include file="field_input.tpl" field=$maxloadavg_frontend}}
- {{include file="field_input.tpl" field=$optimize_max_tablesize}}
- {{include file="field_input.tpl" field=$optimize_fragmentation}}
{{include file="field_input.tpl" field=$abandon_days}}
{{include file="field_input.tpl" field=$temppath}}
{{include file="field_checkbox.tpl" field=$suppress_tags}}
@@ -254,6 +252,7 @@
{{include file="field_input.tpl" field=$dbclean_expire_days}}
{{include file="field_input.tpl" field=$dbclean_unclaimed}}
{{include file="field_input.tpl" field=$dbclean_expire_conv}}
+ {{include file="field_checkbox.tpl" field=$optimize_tables}}
</div>
<div class="panel-footer">
<input type="submit" name="page_site" class="btn btn-primary" value="{{$submit}}"/>
--
2.39.5