2023-11-26
in
15 min read
I’ve been on the fediverse since creating my first Diaspora account in 2018. I’ve maintained my own presence here since establishing my own Friendica server in late-2021/early-2022. While it definitely has its ups and downs by far one of the biggest things to watch out for is the ever increasing size of the local storage for these servers. It’s not just about content generated by the local users but also content comming from across the fediverse that those users interact with. This post tries to capture some of the commands I use to get a picture of what is going on on my server.
Notes Before Starting
In the below commands you will see that we often compare against the “baseurl” of the account user. We use that to determine if the account is a local account or not. For example, the Friendica server I administer is at https://friendica.myportal.social
. Let’s say a user has an account “joesmith”. Their address in the fediverse would be @joesmith@friendica.myportal.social and in the database the “baseurl” for the user would be https://friendica.myportal.social. So to run this on my server the <server_url> would be set to that.
It is assumed that you are running this on the local server with the MariaDB or MySQL console. You will need to have it configured to use the database that your Friendica data is stored in. The database name is usually something like friendica or friendicadb. Assuming that the database is called friendicadb before issuing any of the commands be sure to execute:
USE friendicadb;
Database Size Statistics
Calculating the Biggest Tables
SELECT
TABLE_NAME AS `Table`,
ROUND((DATA_LENGTH + INDEX_LENGTH)) AS `Size (Byte)`,
TABLE_ROWS
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_SCHEMA = 'friendicadb'
ORDER BY
(DATA_LENGTH + INDEX_LENGTH) DESC;
Example output:
+---------------------------+-------------+------------+
| Table | Size (Byte) | TABLE_ROWS |
+---------------------------+-------------+------------+
| post-user | 12662358016 | 19885161 |
| post-content | 8898871296 | 4134969 |
| item-uri | 3889594368 | 10925400 |
| post-media | 3333767168 | 3601907 |
| post-tag | 3146186752 | 28725235 |
| apcontact | 2601091072 | 1571433 |
| conversation | 2453291008 | 438598 |
| post-thread-user | 2437021696 | 4442163 |
| contact | 1792950272 | 559589 |
| post | 1713258496 | 7732236 |
| post-history | 1172701184 | 161448 |
| storage | 824721408 | 3294 |
| parsed_url | 489897984 | 225221 |
| post-thread | 437108736 | 2119477 |
| contact-relation | 271826944 | 3336249 |
| inbox-status | 264781824 | 909806 |
| endpoint | 157237248 | 710296 |
| diaspora-interaction | 149569536 | 112559 |
| tag | 133857280 | 555536 |
| gserver | 63619072 | 211170 |
| account-user | 59850752 | 646496 |
| diaspora-contact | 42041344 | 16939 |
| post-link | 33701888 | 63019 |
| notification | 23789568 | 79938 |
| notify | 17973248 | 14423 |
| post-user-notification | 9961472 | 123256 |
| workerqueue | 9027584 | 20841 |
| fcontact | 8503296 | 5371 |
| cache | 7553024 | 1406 |
| photo | 3555328 | 3801 |
| post-delivery-data | 2637824 | 31159 |
| mail | 2277376 | 1340 |
| post-question-option | 2146304 | 14319 |
| application | 2113536 | 4550 |
| session | 1966080 | 4956 |
| user-contact | 1916928 | 7691 |
| application-token | 1687552 | 4380 |
| user | 1654784 | 59 |
| gserver-tag | 1359872 | 12876 |
| post-collection | 1097728 | 984 |
| post-activity | 1064960 | 315 |
| group_member | 425984 | 4573 |
| post-question | 294912 | 4007 |
| event | 278528 | 297 |
| worker-ipc | 253976 | 1 |
| account-suggestion | 229376 | 2719 |
| arrived-activity | 225696 | 119 |
| fetched-activity | 225696 | 24 |
| post-category | 180224 | 1506 |
| pconfig | 180224 | 715 |
| inbox-entry | 81920 | 0 |
| conv | 81920 | 193 |
| profile | 81920 | 86 |
| report | 65536 | 7 |
| notify-threads | 65536 | 238 |
| fetch-entry | 65536 | 0 |
| intro | 65536 | 83 |
| profile_field | 65536 | 18 |
| delivery-queue | 65536 | 0 |
| search | 49152 | 138 |
| group | 49152 | 177 |
| hook | 49152 | 40 |
| addon | 49152 | 14 |
| post-delivery | 49152 | 0 |
| push_subscriber | 49152 | 1 |
| subscription | 49152 | 7 |
| manage | 49152 | 0 |
| fsuggest | 49152 | 0 |
| delayed-post | 49152 | 0 |
| application-marker | 32768 | 14 |
| inbox-entry-receiver | 32768 | 0 |
| locks | 32768 | 0 |
| 2fa_app_specific_password | 32768 | 0 |
| report-post | 32768 | 19 |
| host | 32768 | 0 |
| openwebauth-token | 32768 | 0 |
| 2fa_trusted_browser | 32768 | 0 |
| verb | 32768 | 10 |
| config | 32768 | 150 |
| oembed | 32768 | 0 |
| mailacct | 32768 | 0 |
| attach | 32768 | 0 |
| register | 32768 | 0 |
| permissionset | 32768 | 103 |
| userd | 32768 | 0 |
| process | 32768 | 11 |
| key-value | 16384 | 23 |
| 2fa_recovery_codes | 16384 | 12 |
| network-item-view | NULL | NULL |
| collection-view | NULL | NULL |
| category-view | NULL | NULL |
| account-view | NULL | NULL |
| workerqueue-view | NULL | NULL |
| pending-view | NULL | NULL |
| post-user-view | NULL | NULL |
| media-view | NULL | NULL |
| tag-search-view | NULL | NULL |
| post-thread-view | NULL | NULL |
| account-user-view | NULL | NULL |
| application-view | NULL | NULL |
| post-thread-user-view | NULL | NULL |
| post-view | NULL | NULL |
| tag-view | NULL | NULL |
| diaspora-contact-view | NULL | NULL |
| network-thread-view | NULL | NULL |
| profile_field-view | NULL | NULL |
| owner-view | NULL | NULL |
+---------------------------+-------------+------------+
Figuring out Storage Table Composition
This is helpful for determining whether you have an excess amount of profile/contact photo caching going on. This is a setting that is configurable on the administrator panel to reduce size. The greedy caching of contact photos is meant to make the UI have better performance but it also can grow tremendously fast.
SELECT SUM(octet_length(data)) 'Total Size in Bytes' FROM storage;
SELECT
SUM(octet_length(storage.data)) 'Contact Photos Size in Bytes'
FROM storage
INNER JOIN photo ON photo.`backend-ref` = storage.id
WHERE album = "Contact Photos";
SELECT
SUM(octet_length(storage.data)) 'Profile Photos Size in Bytes'
FROM storage
INNER JOIN photo ON photo.`backend-ref` = storage.id
WHERE album = "Profile photos";
SELECT
SUM(octet_length(storage.data)) 'Wall Photos Size in Bytes'
FROM storage
INNER JOIN photo ON photo.`backend-ref` = storage.id
WHERE album = "Wall photos";
Example output:
+---------------------+
| Total Size in Bytes |
+---------------------+
| 870356897 |
+---------------------+
+------------------------------+
| Contact Photos Size in Bytes |
+------------------------------+
| 5476433 |
+------------------------------+
1 row in set (0.028 sec)
+------------------------------+
| Profile Photos Size in Bytes |
+------------------------------+
| 19893734 |
+------------------------------+
1 row in set (0.101 sec)
+---------------------------+
| Wall Photos Size in Bytes |
+---------------------------+
| 603816088 |
+---------------------------+
Table Disk Retention Statistics
MySQL/MariaDB doesn’t free up disk space right away just because a table has deleted a bunch of rows. It keeps hold of the blocks for performance reasons so that they can be reused again later, since they are already allocated. If you see a table where that is being done excessively you can run the OPTIMIZE command to potentially free up some space. To determine the free storage run the command:
SELECT
table_name, data_length, data_free
FROM
information_schema.tables
WHERE
table_schema='friendicadb'
ORDER BY
data_free desc;
Example output:
+---------------------------+-------------+-----------+
| table_name | data_length | data_free |
+---------------------------+-------------+-----------+
| post-media | 2641428480 | 35651584 |
| post-content | 8195653632 | 8388608 |
| contact | 1140834304 | 8388608 |
| item-uri | 1851768832 | 7340032 |
| post-link | 23642112 | 7340032 |
| account-user | 27672576 | 7340032 |
| post-collection | 1064960 | 7340032 |
| post-activity | 1064960 | 6291456 |
| parsed_url | 444579840 | 6291456 |
| post | 720355328 | 6291456 |
| post-user | 2901393408 | 6291456 |
| tag | 45678592 | 5242880 |
| post-history | 1172701184 | 5242880 |
| diaspora-interaction | 149569536 | 5242880 |
| inbox-status | 117424128 | 5242880 |
| gserver | 37273600 | 5242880 |
| storage | 824721408 | 4194304 |
| diaspora-contact | 35176448 | 4194304 |
| application | 1589248 | 4194304 |
| mail | 1589248 | 4194304 |
| post-tag | 1445969920 | 4194304 |
| photo | 2637824 | 4194304 |
| post-delivery-data | 2637824 | 4194304 |
| session | 1572864 | 4194304 |
| notification | 5783552 | 4194304 |
| user | 1589248 | 4194304 |
| post-thread-user | 636420096 | 4194304 |
| application-token | 1589248 | 4194304 |
| post-user-notification | 6520832 | 4194304 |
| notify | 13123584 | 4194304 |
| post-question-option | 2146304 | 4194304 |
| gserver-tag | 1097728 | 4194304 |
| user-contact | 1523712 | 4194304 |
| workerqueue | 3686400 | 4194304 |
| endpoint | 93323264 | 4194304 |
| post-thread | 181059584 | 4194304 |
| contact-relation | 188481536 | 4194304 |
| cache | 7389184 | 4194304 |
| apcontact | 1496236032 | 3145728 |
| conversation | 2341470208 | 2097152 |
| fcontact | 7880704 | 2097152 |
| arrived-activity | 127008 | 67643 |
| fetched-activity | 127008 | 36363 |
| worker-ipc | 126992 | 6 |
| post-category | 81920 | 0 |
| event | 229376 | 0 |
| addon | 16384 | 0 |
| group | 16384 | 0 |
| group_member | 196608 | 0 |
| process | 16384 | 0 |
| permissionset | 16384 | 0 |
| 2fa_recovery_codes | 16384 | 0 |
| post-question | 196608 | 0 |
| verb | 16384 | 0 |
| intro | 16384 | 0 |
| inbox-entry | 16384 | 0 |
| 2fa_app_specific_password | 16384 | 0 |
| notify-threads | 16384 | 0 |
| post-delivery | 16384 | 0 |
| key-value | 16384 | 0 |
| hook | 16384 | 0 |
| delivery-queue | 16384 | 0 |
| delayed-post | 16384 | 0 |
| oembed | 16384 | 0 |
| mailacct | 16384 | 0 |
| manage | 16384 | 0 |
| openwebauth-token | 16384 | 0 |
| 2fa_trusted_browser | 16384 | 0 |
| push_subscriber | 16384 | 0 |
| report | 16384 | 0 |
| conv | 65536 | 0 |
| profile | 49152 | 0 |
| config | 16384 | 0 |
| register | 16384 | 0 |
| pconfig | 114688 | 0 |
| account-suggestion | 163840 | 0 |
| inbox-entry-receiver | 16384 | 0 |
| locks | 16384 | 0 |
| search | 16384 | 0 |
| application-marker | 16384 | 0 |
| userd | 16384 | 0 |
| fsuggest | 16384 | 0 |
| attach | 16384 | 0 |
| profile_field | 16384 | 0 |
| subscription | 16384 | 0 |
| fetch-entry | 16384 | 0 |
| host | 16384 | 0 |
| report-post | 16384 | 0 |
| account-view | NULL | NULL |
| account-user-view | NULL | NULL |
| post-thread-user-view | NULL | NULL |
| pending-view | NULL | NULL |
| tag-search-view | NULL | NULL |
| workerqueue-view | NULL | NULL |
| tag-view | NULL | NULL |
| application-view | NULL | NULL |
| media-view | NULL | NULL |
| collection-view | NULL | NULL |
| diaspora-contact-view | NULL | NULL |
| post-view | NULL | NULL |
| post-user-view | NULL | NULL |
| post-thread-view | NULL | NULL |
| network-item-view | NULL | NULL |
| category-view | NULL | NULL |
| owner-view | NULL | NULL |
| network-thread-view | NULL | NULL |
| profile_field-view | NULL | NULL |
+---------------------------+-------------+-----------+
Now if there was a table you wanted to free excessive space from you could run an optimize command. For example, if I wanted to optimize the top table, post-media I’d execute:
OPTIMIZE TABLE `post-media`;
Note optimizing tables lock the table which will make your Friendica instance non-responsive if any query requires that table. The optimization of larger tables can take several minutes or longer. Depending on your server configuration the process can require over twice as much disk space as the original table size. Optimizing the table does not guarantee any freed up space.
Binary Log Window Size
MySQL and MariaDB maintain logs for a given period of time to allow rollback/restore operations. They can get quite large. To minimize the size you can reduce how long the log window size is. In modern MariaDB configurations this is done by setting the expire_logs_days system variable or max_binlog_size system variable. For more information see The MariaDB Documentation
on this topic.
Post, Comment, and Activity Statistics
Get Total Post, Comment, and Activity Accounts from Local Users
SELECT
`post`.gravity, `post`.`deleted`, count(*)
FROM `post`
JOIN `account-view` ON `post`.`author-id`=`account-view`.id
WHERE
`account-view`.baseurl = '<server_url>'
GROUP BY
post.gravity, post.deleted
;
Example output:
+---------+---------+----------+
| gravity | deleted | count(*) |
+---------+---------+----------+
| 0 | 0 | 13087 |
| 0 | 1 | 68 |
| 3 | 0 | 56024 |
| 3 | 1 | 25084 |
| 6 | 0 | 10388 |
| 6 | 1 | 4176 |
+---------+---------+----------+
The “gravity” field determines what kind of content the count is for. The valid values are:
- 0 => Posts
- 3 => Activities (likes, dislikes)
- 6 => Comments
- 9 => Unknown type
The “deleted” column shows if it is for deleted (1) or not-deleted (0) versions of the respective entry.
So interpreting the above table this servire has:
- 13,087 posts from local users
- 68 deleted posts from local users
- 56,024 activities from local users
- 25,084 deleted activities from local users
- 10,388 comments from local users
- 4,176 deleted comments from local users
Get Total Post, Comment, and Activity From Outside Accounts Your Server Is Federating
This command is nearly identical to the above command except we want the baseurl to not match our server’s URL.
SELECT
`post`.gravity, `post`.`deleted`, count(*)
FROM `post`
JOIN `account-view` ON `post`.`author-id`=`account-view`.id
WHERE
`account-view`.baseurl != '<server_url>'
GROUP BY
post.gravity, post.deleted
;
Getting Most Active Local Posters/Commenters
SELECT
`account-view`.addr, post.deleted, post.gravity, count(*) as pc
FROM `post`
JOIN `post-content` ON `post`.`uri-id`=`post-content`.`uri-id`
JOIN `account-view` ON `post`.`author-id`=`account-view`.id
WHERE
`account-view`.baseurl = '<server_url>'
GROUP BY
`account-view`.addr, post.deleted, post.gravity
ORDER BY
pc DESC
LIMIT 100
;
Example output:
+--------------------------------------------------+---------+---------+----+
| addr | deleted | gravity | pc |
+--------------------------------------------------+---------+---------+----+
| testuser3@friendicadevtest1.myportal.social | 0 | 0 | 14 |
| testuser3@friendicadevtest1.myportal.social | 0 | 6 | 11 |
| testuser1@friendicadevtest1.myportal.social | 0 | 6 | 8 |
| testuser1@friendicadevtest1.myportal.social | 1 | 0 | 5 |
| testuser1@friendicadevtest1.myportal.social | 0 | 0 | 5 |
| trolltestuser1@friendicadevtest1.myportal.social | 0 | 6 | 4 |
| testuser3@friendicadevtest1.myportal.social | 1 | 6 | 4 |
| testuser2@friendicadevtest1.myportal.social | 1 | 0 | 3 |
| admin@friendicadevtest1.myportal.social | 0 | 0 | 1 |
| testuser1@friendicadevtest1.myportal.social | 1 | 6 | 1 |
| testuser2@friendicadevtest1.myportal.social | 0 | 0 | 1 |
+--------------------------------------------------+---------+---------+----+
Like above, “gravity” column says what kind of item is being counted with the values being:
- 0 => Posts
- 3 => Activities (likes, dislikes)
- 6 => Comments
- 9 => Unknown type
The “deleted” column is on whether the count is for deleted or not-deleted items. So interpreting the above testuser3 has the most of both posts (14) and comments (11). testuser3 also has 4 deleted comments. Although ordering is by counts one may want to push these into a spreadsheet to look at the data in different ways. Ordering by the address parametmer if you are doing the LIMIT option could give you results which don’t show the greatest counts because it will then be ordering the calculation by alphabetical order of the address fields. For example changing ORDER BY to addr and LIMIT to 3 would give:
+---------------------------------------------+---------+---------+----+
| addr | deleted | gravity | pc |
+---------------------------------------------+---------+---------+----+
| admin@friendicadevtest1.myportal.social | 0 | 0 | 1 |
| testuser1@friendicadevtest1.myportal.social | 0 | 0 | 5 |
| testuser1@friendicadevtest1.myportal.social | 0 | 6 | 8 |
+---------------------------------------------+---------+---------+----+
Getting Most Active Outside Posters/Commenters Your Server Is Federating
This command is nearly identical to the above command except we want the baseurl to not match our server’s URL.
SELECT
`account-view`.addr, post.deleted, post.gravity, count(*) as pc
FROM `post`
JOIN `post-content` ON `post`.`uri-id`=`post-content`.`uri-id`
JOIN `account-view` ON `post`.`author-id`=`account-view`.id
WHERE
`account-view`.baseurl != '<server_url>'
GROUP BY
`account-view`.addr, post.deleted, post.gravity
ORDER BY
pc DESC
LIMIT 100
;
Getting Most Active Local Interactors
This command lists the most active people in terms of “activities” (likes/dislikes/etc):
SELECT
`account-view`.addr, `post`.deleted, count(*) as pc
FROM `post`
JOIN `account-view` ON `post`.`author-id`=`account-view`.id
WHERE
gravity=3 AND `account-view`.baseurl = '<server_url>'
GROUP BY
`account-view`.addr
ORDER BY
pc DESC
LIMIT 100
;
Example Output:
+--------------------------------------------------+---------+----+
| addr | deleted | pc |
+--------------------------------------------------+---------+----+
| testuser3@friendicadevtest1.myportal.social | 0 | 12 |
| testuser1@friendicadevtest1.myportal.social | 0 | 5 |
| testuser2@friendicadevtest1.myportal.social | 1 | 4 |
| trolltestuser1@friendicadevtest1.myportal.social | 0 | 1 |
+--------------------------------------------------+---------+----+
The count just lists all activities together for each user but breaks out deleted versus not-deleted ones.
Getting Most Active Outside Interactors Your Server Is Federating
This command is nearly identical to the above command except we want the baseurl to not match our server’s URL.
SELECT
`account-view`.addr, `post`.deleted, count(*) as pc
FROM `post`
JOIN `account-view` ON `post`.`author-id`=`account-view`.id
WHERE
gravity=3 AND `account-view`.baseurl != '<server_url>'
GROUP BY
`account-view`.addr
ORDER BY
pc DESC
LIMIT 100
;