Mediawiki wiki text size

From richmondmakerlabs.uk
Revision as of 21:27, 21 September 2020 by Beardedfool (talk | contribs)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

Problem: Database larger than expected, or is it

Assuming that I'm right and the mediawiki text table only has text in it, it seems large for what we have in it

<root:/var/lib/mysql/wiki# ls -alS | head -2
total 775M
-rw-rw---- 1 mysql mysql 664M Sep 21 21:10 wiki_text.ibd
Database changed
MariaDB [wiki]> SELECT
    ->      table_schema as `Database`,
    ->      table_name AS `Table`,
    ->      round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
    -> FROM information_schema.TABLES
    -> ORDER BY (data_length + index_length) DESC;
+--------------------+----------------------------------------------------+------------+
| Database           | Table                                              | Size in MB |
+--------------------+----------------------------------------------------+------------+
| wiki               | wiki_text                                          |     659.55 |
| wiki               | wiki_revision                                      |       7.78 |

This seems... large

MariaDB [wiki]> select COUNT(*) from wiki_text;
+----------+
| COUNT(*) |
+----------+
|    15530 |
+----------+
1 row in set (0.06 sec)

Page Statistics

Page statistics
Content pages	461
Pages
(All pages in the wiki, including talk pages, redirects, etc.)	697
Uploaded files	512
Edit statistics
Page edits since Richmond Maker Labs was set up	15,577
Average edits per page	22.35

sqldump

is a fair bit smaller but still large

mysqldump wiki > wiki.sql
-rw-r--r-- 1 root root 417M Sep 21 22:04 wiki.sql

Old data

It does seem to have to data in the table that has been deleted

select COUNT(*) from wiki_text where old_text like '%Areca%';.
 +----------+
| COUNT(*) |
+----------+
|       13 |
+----------+
1 row in set (3.06 sec)

decribe wiki_text

MariaDB [wiki]> describe wiki_text;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| old_id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| old_text  | mediumblob       | NO   |     | NULL    |                |
| old_flags | tinyblob         | NO   |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)


Maintenance tools tried

deleteOldRevisions.php

root:/var/lib/mediawiki/maintenance# php deleteOldRevisions.php
Delete old revisions

Searching for active revisions...done.
Searching for inactive revisions...done.
3552 old revisions found.

purgeOldText.php

root:/var/lib/mediawiki/maintenance# php purgeOldText.php
Searching for active text records via contents table...done.
done.
Searching for inactive text records...done.
0 inactive items found.

deleteArchivedRevisions.php

This should be another table anyway but just in case someone comes across it as a solution

root:/var/lib/mediawiki/maintenance# php deleteArchivedRevisions.php
Found 0 revisions to delete.

Shrinking Maridb

Not convinced this is actually doing anything. It went up at one point after deleting old revisions

MariaDB [wiki]> OPTIMIZE TABLE wiki_text;
+----------------+----------+----------+-------------------------------------------------------------------+
| Table          | Op       | Msg_type | Msg_text                                                          |
+----------------+----------+----------+-------------------------------------------------------------------+
| wiki.wiki_text | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| wiki.wiki_text | optimize | status   | OK                                                                |
+----------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (18.67 sec)

Software Versions

https://wiki.richmondmakerlabs.uk/index.php/Special:Version