Mediawiki wiki text size
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)