Phabricator_file is huge - What's inside? How to shrink?

By comparison a probably little used phabricator mariadb table phabricator_file is 400 MB. Due to use of automysqlbackup this became the biggest file included in backups.

403M /var/lib/automysqlbackup/daily/phabricator_file/phabricator_file_2020-04-30_21h13m.Thursday.sql.gz

What’s actually inside it? I tried looking into it with sqlbrowser but it exited with an error.

Any way to look into that file?

phabircator file/query/all/ shows much less. Perhaps 130 files between 2 and 200 KB.

Any way to shrink that file? Tired mysqloptimize already but made no difference.

This may be useful to further investigate the reasons:

SELECT phid, name, storageEngine, byteSize FROM phabricator_file.file ORDER BY byteSize DESC LIMIT 50;

Note that you can quickly see a file if you put its Phabricator identifier in the search bar of your Phabricator instance.

1 Like

Happy birthday, and thank your for your reply!

I did

  • migrated from mysql storage backend to local disk (as per instructions).
  • set applications/view/PhabricatorFilesApplication/ to

PhabricatorMySQLFileStorageEngine No 0 B

(It’s just a “small” phabricator instance. We’re at T914 since 2015. Therefore I am not worried about scaling issues at all.)

But that does not seem to delete existing entries from mysql (actually mariadb).

Here’s an excerpt from the command you suggested:

MariaDB [(none)]> SELECT phid, name, storageEngine, byteSize FROM phabricator_file.file ORDER BY byteSize DESC LIMIT 50;
+--------------------------------+------------------------------------------------------------------------------+---------------+----------+
| phid                           | name                                                                         | storageEngine | byteSize |
+--------------------------------+------------------------------------------------------------------------------+---------------+----------+
| PHID-FILE-duktih6y2vrwnxbbrzdb | workcard-alphanumeric/lato-dark/O.png-#69536b-0,0,0,0.png                    | blob          |   193296 |
| PHID-FILE-hrxv6d3dze2mgrlyomch | workcard-profile                                                             | blob          |   193296 |
| PHID-FILE-t5vjvsvwxouyzgo26ye5 | workcard-workcard-alphanumeric/lato-white/J.png-#cdaede-255,255,255,0.7.png  | blob          |   193264 |
| PHID-FILE-jibd6tjir43rrpqfhone | workcard-alphanumeric/lato-white/J.png-#cdaede-255,255,255,0.7.png           | blob          |   193264 |
| PHID-FILE-rfukf2xs7m34hcu7rcey | workcard-profile                                                             | blob          |   193180 |
| PHID-FILE-rr6in4mpjye443kwvn4d | workcard-#769b5a-alphanumeric/lato-dark/D.png-255,255,255,0.7.png            | blob          |   193180 |
| PHID-FILE-ylp43l6n6g34jzynu7np | workcard-profile                                                             | blob          |   193173 |

Test wise I also increased LIMIT 50 to LIMIT 20000, copied to file, and counted.

12420 rows in set (0.019 sec)

I.e. more than 12000 such files. That adds up.

I could add a script to destroy all of these objects but these would just be re-generated by phabricator.

Can I disable these workcard-profile / preview related files or have them stored on local disk rather than mysql? Any other advice?

You can migrate files to another sotarge engine with the help of ./bin/files migrate.

This is also explained here: https://secure.phabricator.com/book/phabricator/article/configuring_file_storage/#migrating-files-between-engines

I did this already. Mentioned in my previous post.

Didn’t resolve this issue.

What was the output of ./bin/files migrate --engine local-disk --all?

1 Like
./bin/files migrate --engine local-disk --all
F12299259: Already stored in engine "local-disk".
F12299258: Already stored in engine "local-disk".
F12299257: (11 KB) Migrating from "blob" to "local-disk"...
Done.
F12299256: Already stored in engine "local-disk".
F12299255: Already stored in engine "local-disk".
F12299254: Already stored in engine "local-disk".
F12299253: Already stored in engine "local-disk".
F12299252: Already stored in engine "local-disk".
F12299251: (183 KB) Migrating from "blob" to "local-disk"...
Done.
F12299250: Already stored in engine "local-disk".
F12299249: Already stored in engine "local-disk".
F12299248: Already stored in engine "local-disk".
F12299247: Already stored in engine "local-disk".
F12299246: Already stored in engine "local-disk".
F12299245: Already stored in engine "local-disk".
F12299244: Already stored in engine "local-disk".
F12299243: Already stored in engine "local-disk".
F12299242: (1 KB) Migrating from "blob" to "local-disk"...
Done.
F12299241: Already stored in engine "local-disk".
F12299240: (14 KB) Migrating from "blob" to "local-disk"...
Done.
F12299239: Already stored in engine "local-disk".
F12299238: (1 KB) Migrating from "blob" to "local-disk"...
Done.
F12299237: Already stored in engine "local-disk".
F12299236: Already stored in engine "local-disk".
F12299235: Already stored in engine "local-disk".
F12299234: Already stored in engine "local-disk".
F12299233: Already stored in engine "local-disk".
F12299232: Already stored in engine "local-disk".
F12299231: Already stored in engine "local-disk".
F12299230: Already stored in engine "local-disk".
F12299229: (1 KB) Migrating from "blob" to "local-disk"...
Done.
F12299228: (1 KB) Migrating from "blob" to "local-disk"...
Done.
F12299227: (10 KB) Migrating from "blob" to "local-disk"...
Done.
F12299226: (1 KB) Migrating from "blob" to "local-disk"...
Done.
F12299225: (11 KB) Migrating from "blob" to "local-disk"...
Done.
F12299224: (70 KB) Migrating from "blob" to "local-disk"...
Done.
F12299223: (7 KB) Migrating from "blob" to "local-disk"...
Done.
F12299222: (50 KB) Migrating from "blob" to "local-disk"...
Done.
F12299221: (14 KB) Migrating from "blob" to "local-disk"...
Done.
F12299220: (530 B) Migrating from "blob" to "local-disk"...
Done.
F12299219: (14 KB) Migrating from "blob" to "local-disk"...
Done.
F12299218: (13 KB) Migrating from "blob" to "local-disk"...
Done.
F12299217: (56 KB) Migrating from "blob" to "local-disk"...
Done.
F12299216: (38 KB) Migrating from "blob" to "local-disk"...
Done.
F12299214: (5 KB) Migrating from "blob" to "local-disk"...
Done.
F12299213: (2 KB) Migrating from "blob" to "local-disk"...
Done.
F12299212: (4 KB) Migrating from "blob" to "local-disk"...
Done.
F12299211: (1 KB) Migrating from "blob" to "local-disk"...
Done.
F12299209: (9 KB) Migrating from "blob" to "local-disk"...
Done.
F12299208: (1 KB) Migrating from "blob" to "local-disk"...
Done.
F12299207: (3 KB) Migrating from "blob" to "local-disk"...
Done.
F12299206: (1 KB) Migrating from "blob" to "local-disk"...
Done.
F12299205: (4 KB) Migrating from "blob" to "local-disk"...
Done.
F12299204: (2 KB) Migrating from "blob" to "local-disk"...
Done.
F12299203: (2 KB) Migrating from "blob" to "local-disk"...
Done.
F12299201: (2 KB) Migrating from "blob" to "local-disk"...
Done.
F12299199: (13 KB) Migrating from "blob" to "local-disk"...
Done.
F12299198: (539 B) Migrating from "blob" to "local-disk"...
Done.
F12299197: (175 KB) Migrating from "blob" to "local-disk"...
Done.
F12299196: (378 B) Migrating from "blob" to "local-disk"...
Done.
F12299194: (63 KB) Migrating from "blob" to "local-disk"...
Done.
F12299193: (11 KB) Migrating from "blob" to "local-disk"...
Done.
F12299192: (7 KB) Migrating from "blob" to "local-disk"...
Done.
F12299191: (1 KB) Migrating from "blob" to "local-disk"...
Done.
F12299190: (11 KB) Migrating from "blob" to "local-disk"...
Done.
F12299189: (4 KB) Migrating from "blob" to "local-disk"...
Done.
F12299188: (15 KB) Migrating from "blob" to "local-disk"...
Done.
F12299187: (1 KB) Migrating from "blob" to "local-disk"...
Done.
F12299186: (8 KB) Migrating from "blob" to "local-disk"...
Done.
F12299185: (1 KB) Migrating from "blob" to "local-disk"...
Done.
F12299184: (68 KB) Migrating from "blob" to "local-disk"...
Done.
F12299183: (186 KB) Migrating from "blob" to "local-disk"...
Done.
F12299182: (69 KB) Migrating from "blob" to "local-disk"...
Done.
F12299181: (1 KB) Migrating from "blob" to "local-disk"...
Done.
F12299180: (181 KB) Migrating from "blob" to "local-disk"...
Done.
F12299179: (1 KB) Migrating from "blob" to "local-disk"...
Done.
F12299178: (14 KB) Migrating from "blob" to "local-disk"...
Done.
F12299177: (4 KB) Migrating from "blob" to "local-disk"...
Done.
F12299176: (1 KB) Migrating from "blob" to "local-disk"...
Done.
F12299175: (15 KB) Migrating from "blob" to "local-disk"...
Done.
F12299174: (16 KB) Migrating from "blob" to "local-disk"...
Done.
F12299173: (15 KB) Migrating from "blob" to "local-disk"...
Done.
F12299172: (1 KB) Migrating from "blob" to "local-disk"...
Done.
F12299171: (5 KB) Migrating from "blob" to "local-disk"...
Done.
F12299169: (19 KB) Migrating from "blob" to "local-disk"...
Done.
F12299168: (1 KB) Migrating from "blob" to "local-disk"...
Done.
F12299167: (183 KB) Migrating from "blob" to "local-disk"...
Done.
F12299166: (540 B) Migrating from "blob" to "local-disk"...
Done.
F12299165: (47 KB) Migrating from "blob" to "local-disk"...
Done.
F12299164: (4 KB) Migrating from "blob" to "local-disk"...
Done.
F12299163: (8 KB) Migrating from "blob" to "local-disk"...
Done.
F12299162: (14 KB) Migrating from "blob" to "local-disk"...
Done.
F12299161: (156 KB) Migrating from "blob" to "local-disk"...
Done.
F12299160: (1 KB) Migrating from "blob" to "local-disk"...
Done.
F12299159: (187 KB) Migrating from "blob" to "local-disk"...
Done.
F12299158: (130 KB) Migrating from "blob" to "local-disk"...
Done.
F12299157: (12 KB) Migrating from "blob" to "local-disk"...
Done.
F12299156: (155 KB) Migrating from "blob" to "local-disk"...
Done.
F12299154: (185 KB) Migrating from "blob" to "local-disk"...
Done.
F12299153: (2 KB) Migrating from "blob" to "local-disk"...
Done.
Total Migrated Files: 75
Total Migrated Bytes: 2 MB