danielmarschall
commented
6 months ago Backup plugin
Greatest bottleneck is log , since I have 137.000 entries, and so it does 2x 137.000 entries (fetch user and objects). Instead I should do a JOIN , and then group manually with PHP , something like
select
log.id, lobj.object, lusr.username
FROM `oidplus_log` log
LEFT JOIN `oidplus_log_user` lusr on lusr.log_id = log.id
LEFT JOIN `oidplus_log_object` lobj on lobj.log_id = log.id
WHERE log.id > 136951 and log.id < 137000WIP:
===================================================================
--- OIDplusPageAdminDatabaseBackup.class.php (Revision 1446)
+++ OIDplusPageAdminDatabaseBackup.class.php (Arbeitskopie)
@@ -335,6 +335,49 @@
// Backup logs (Tables log, log_object, log_user)
$log = [];
if ($export_log) {
+
+ $log_tmp = array();
+
+ $res = OIDplus::db()->query("SELECT log.id as _log_id, log.unix_ts, log.addr, log.event, ".
+ " lobj.object, lobj.severity as obj_severity, ".
+ " lusr.username, lusr.severity as usr_severity ".
+ "FROM ###log log ".
+ "LEFT JOIN ###log_user lusr on lusr.log_id = log.id ".
+ "LEFT JOIN ###log_object lobj on lobj.log_id = log.id ");
+ while ($row = $res->fetch_array()) {
+ $id = $row['_log_id'];
+ if (!isset($log_tmp[$id])) {
+ $num_rows["log"]++;
+ $log_tmp[$id] = [
+ "unix_ts" => $row["unix_ts"],
+ "addr" => $row["addr"],
+ "event" => $row["event"],
+ "objects" => [],
+ "users" => []
+ ];
+ }
+ if (($row['object'] ?? '') != '') {
+ $num_rows["log_object"]++;
+ $log_tmp[$id]['objects'][] = [
+ "object" => $row['object'],
+ "severity" => $row['obj_severity'] ?? 0
+ ];
+ }
+ if (($row['username'] ?? '') != '') {
+ $num_rows["log_user"]++;
+ $log_tmp[$id]['users'][] = [
+ "username" => $row['username'],
+ "severity" => $row['usr_severity'] ?? 0
+ ];
+ }
+ }
+
+ foreach ($log_tmp as $log_id => $data) {
+ $log[] = $data;
+ }
+ unset($log_tmp);
+
+ /*
$res = OIDplus::db()->query("select * from ###log order by id");
$rows = [];
while ($row = $res->fetch_array()) {
@@ -372,6 +415,7 @@
"users" => $log_users
];
}
+ */
}
// Backup public/private keyBut this does not work for me, because this query loads forever until I get timeout after 5 minutes!!!
SELECT log.id as _log_id, log.unix_ts, log.addr, log.event, lobj.object, lobj.severity as obj_severity, lusr.username, lusr.severity as usr_severity
FROM oidplus_log log
LEFT JOIN oidplus_log_user lusr on lusr.log_id = log.id
LEFT JOIN oidplus_log_object lobj on lobj.log_id = log.id Note that this query has the disadvantage that event might contain a lot of data and this amount of data is multiplied by the two joins. The same thing would happen (and even worse) if we do a single select on the objects, asn1id and iri table. Then we'd multiply the description field which might be very large.
Same thing to OIDs , where you need to fetch an ASN1ID and IRI for each OID! It's just not so extreme for myself, because I have more log entries (173k) as OIDs (64k)
... maybe log entries should be pruned? (maybe even prune by severity?) maybe save as TXT before pruning...
... if backup fails, will restore then also fail?
wehowski
Taken from the current TODO file:
See also https://github.com/frdl/oidplus-plugin-alternate-id-tracking/issues/17 for the Alt-ID plugins.
In addition, it turns out that the backup plugin cannot be used. It was slow all the time, but now it is so slow that it throws a HTTP 500....