Neo
October 30, 2019, 12:42am
1
I had some code in place to update the MySQL man page DB 'on the fly' but this database has grown so big over time (now over 7 GB, 7.1 GB to be exact) that the update query was causing the man page rendering to slow down to a crawl.
So, I just disabled updating the table 'on the fly' so the pages will load fast again.
It was not the number of rows causing the problem but something else:
mysql> select count(*) from `neo_man_page_entry`;
+----------+
| count(*) |
+----------+
| 347938 |
+----------+
1 row in set (0.00 sec)
Which I think is related to indexing in that table and the update query.
So, I'll revisit and fix this at a later time.
For now, I man pages are loading fast again.
Neo
October 30, 2019, 12:46am
2
If anyone (Akshay when back from vacation?) can improve this, please suggest something:
<?php
function updateDB($os, $section, $query, $text, $raw = '')
{
global $vbulletin;
$dateline = time();
$string = substr($text, 0, 20);
$snippet = trim(preg_replace('/\s+/', ' ', $string));
$token = $os . '_' . $section . '_' . $query;
if (strlen($token) >= 1) {
$sql = 'INSERT INTO neo_man_page_entry
(os, section, query, text, formatted, token, strlen, dateline )
VALUES
("' .
htmlentities($os, ENT_QUOTES) . '","' .
htmlentities($section, ENT_QUOTES) . '","' .
htmlentities($query, ENT_QUOTES) . '","' .
htmlentities($text, ENT_QUOTES) . '","' .
htmlentities($raw, ENT_QUOTES) . '","' .
htmlentities($token, ENT_QUOTES) . '",' .
strlen($raw) . ',' .
$dateline . ')
ON DUPLICATE KEY UPDATE
dateline = ' . $dateline . ',hits = hits +1';
$status = $vbulletin->db->query_write($sql);
return $status;
} else {
$file = '/var/log/apache2/debug/neo_update_mandb_entry_error.log';
$uid = $vbulletin->userinfo['userid'];
$text = $raw = "unknown";
error_log(date(DATE_RFC822) . " UID " . $uid . ' token ' . $token . ' len ' . strlen($text) . ' snip ' . $snippet . "\n", 3, $file);
return -1;
}
}
and:
mysql> describe neo_man_page_entry;
+-----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+----------------+
| manid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| token | varchar(120) | NO | UNI | NULL | |
| dateline | int(11) unsigned | NO | | NULL | |
| os | varchar(16) | NO | MUL | NULL | |
| section | varchar(8) | NO | | NULL | |
| query | varchar(64) | NO | MUL | NULL | |
| text | mediumtext | NO | MUL | NULL | |
| formatted | mediumtext | NO | | NULL | |
| strlen | int(10) unsigned | NO | | 0 | |
| hits | int(10) unsigned | NO | | 1 | |
+-----------+------------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)