There's still a big element of mystery here: why did MySQL sustain 80MB/sec of write I/O without daemons running? There "should" have been no queries against the fngrams table, and a large table "should" not require huge I/O volumes if it isn't being used. I don't think it's totally implausible that this was some kind of general thrash/swap/memory management issue in MySQL (the innodb_buffer_pool_size on these hosts if 4GB, and mysql was using all of it) but I would expect "paging-like" activities to cause a large read volume, not a large write volume.

Regardless, the fngrams table looks a lot like it's in the middle of things here.

The easiest way I can come up with to reduce the size of the table is to remove common ngrams from the index. For example, almost every (English-language) document matches the ngrams th, the, and he . These ngrams aren't actually useful: they have no real power to reduce the size of the result set, and if the user searches for "the" we're probably better off just doing LIKE on whatever result set we can use other indexes to reduce to, since almost everything is going to hit anyway.

On this install, for the maniphest_task_ngrams table, these are the most common ngrams:

mysql> SELECT ngram, COUNT(*) N from maniphest_task_fngrams GROUP BY ngram ORDER BY N desc limit 100;
+-------+-------+
| ngram | N     |
+-------+-------+
|  th   | 10736 |
| the   | 10161 |
| ng    | 10087 |
| at    | 10055 |
| on    | 10052 |
| ed    | 10038 |
| is    | 10028 |
| to    |  9996 |
|  to   |  9984 |
| ing   |  9946 |
...

We have 13,000 tasks, so all of these ngrams are present in more than 50% of the documents and pretty much useless as an index.

If we throw out ngrams which are present in more than X% of documents, we reduce the size of the ngrams table by this much:

This isn't tremendously reassuring at first blush: the long tail is pretty thick. Throwing out ngrams present in more than 50% of documents is almost certainly very safe, but I'm less sure about throwing out ngrams present in more than 5% of documents.

That said, it may not be bad: the tail on distinct ngrams is also very thick. The table has 88,378 distinct ngrams and throwing out all the ngrams present in more than 5% of documents discards only 2,164 of them, or about 2.5%.

In fact, discarding ngrams present in more than 1% of documents only discards 6% of ngrams, and saves 88% of the table size.

Generally, if we can get away with this or something similar, I think this is the best approach. If we can't trick MySQL into scaling at the ~1M object level we probably need to deploy an external index which has more cleverness than "do a lot of joins", and that ramps up complexity explosively.

Another way we can cheat is to split the fngrams table into a "latin" table and a nonlatin table. Basically:

• If an ngram has byte length 3, it goes in the latin table.
• If an ngram has byte length >3 (i.e., contains unicode) it goes in the nonlatin table.

The table currently uses utf8mb4, so we're presumably wasting 9 bytes per ngrams, on 99% of ngrams for English documents. There may be a similar effect in the index, although I'm not sure.

However, the local table has 4,809,955 rows and a total size of ~1GB (430MB data + 570MB indexes), which suggests each row requires ~200 bytes of storage, and that shaving 9 bytes off might not do much. This would be easy to test, though.

It's also possible that using, e.g., ROW_FORMAT=compressed might help (more likely with the ffield table than the fngrams table?).

We could also remove the documentID key, which is used only for indexing, and GC old indexes instead. I suspect this won't help us, but the impact of this is also easy to test.

If none of this works we can ultimately use a dedicated external index instead, but we're in a far stronger position if Ferret can scale through reasonable workloads, and then scale into unreasonable ones by being given a beefier database with more RAM.

Here are some possible structural changes we can make to the table, using the Maniphest ngrams table on this install as an example dataset (this is roughly 50x smaller than the target dataset):

ID Column: We don't need an explicit ID column on this table, but my understanding is that InnoDB creates an implicit one internally if you don't assign one yourself, so you might as well assign one so you can use it. This seems born out by the table not dropping in size after removing the ID column.

None of these seem terribly promising. Only the initial OPTIMIZE had any real impact on the reported data size. Dropping the documentID key looks like it has a real effect, but it's maybe ~15% of the total table size and makes updates far more involved.

I'm going to try dropping common ngrams instead and see how well that works.

I looked at how the last 1,000 queries on this instance would be affected by different "common" thresholds. Note that these charts are all sort of garbage (X axis is nonlinear) because I couldn't figure out how basic spreadsheet software works.

I've lightly edited some of the actual query text to preserve anonymity.

100% Threshold (Current Behavior)

Example queries which can't use ngram constraints: ~v0, -robust.

50% Threshold

Example queries which can't use ngram constraints: same as above.

25% Threshold

Example queries which can't use ngram constraints: phabricator https, party, forking.

10% Threshold

Example queries which can't use ngram constraints: handles, projects, notes, sticky, storage, extension, epriestley, component, chinese, allocation, activating, billing, install application extension, specify reviewer, logs large, revision created, branches.

5% Threshold

Example queries which can't use ngram constraints: active directory, active credentials, scrolling boxes, setcommentaction, uncommitted changes, revision dependency, phacility private, opencats, force online, changed visibility, table borders, message query.

2% Threshold

Example queries which can't use ngram constraints: upgrade guidance, orchestration, overheated, mercurial merge, materialized members, employee directory, differential ignore whitespace, dangerous change protection.

1% Threshold

Example queries which can't use ngram constraints: cache status, getroutes, edge table, milestone, minifier.

The thing I'm mostly looking at is how many queries are pushed down to 0 ngrams, i.e. they can't use the ngram index.

The 25% threshold seems generally safe, and impacts only a small number of searches which aren't really meaningful searches anyway, like party.

The 10% threshold is a little dicier. It starts to impact "searches" which have some common word. These probably also aren't meaningful searches: even a human intelligence could not return whatever the user who searched for meant by querying for notes or component or branches.

The 5% threshold isn't much different from the 10% threshold, and includes some more "probably not very good searches for common terms" sorts of queries.

The 2% threshold is a bit worse than the 5% threshold, but continues including more legitimate-ish queries.

The 1% threshold isn't much worse than the 2% threshold, and more or less continues the trend of catching some actual queries.

Even at the 1% threshold, only 12% of the queries miss the ngram index.

Overall, this seems promising -- not ideal, but not disastrous. We don't seem to have a super long tail of ngrams which occur in only a tiny number of documents, and we don't seem to be catching many "good" queries even with a very aggressive threshold.

That said, this install is something of a magnet for nonsense queries and may not be particularly representative of realworld use.

I'm inclined to start with a relatively conservative threshold of 0.15 -- which the data above predicts should affect almost nothing, but reduce the table size by 50% -- and go from there. We can easily decrease the threshold later, but can not increase it without rebuilding indexes.

I'm also going to increase the db tier innodb_buffer_pool_size from 4GB to 6GB. The hosts have 7.5GB of RAM and 8GB of swap, and are pretty much sitting there with a little under 3GB of RAM mostly unused and no other load.

In my general reading about this I think there are also a couple other things we may be able to tweak.

I've pushed the MySQL configuration tweaks here, and manually built the common ngrams table with a 0.15 threshold.

Another thing we can do here which will generally reduce the operational load is to be more selective about how we dump/export tables from MySQL. Broadly, we have three general classes of tables today:

• Normal data tables, which store the authoritative copy of data, like maniphest_task.
• Readthrough cache tables, which will be automatically/transparently rebuilt if the data is lost, like cache_general.
• Index tables, which won't be automatically/transparently rebuilt, but can be rebuilt with bin/storage index (or maybe some other similar command, although no other examples spring to mind).

For normal data tables, we obviously need to dump the data.

For cache tables, we never need to dump the data. Skipping the data in these tables, some of which are large, and just dumping the schema will make dumps faster and backups smaller.

For index tables, we should probably keep dumping the data for normal users, but skipping this data in the production cluster makes a significant amount of sense: we get smaller/faster backups, and the overhead of doing an index rebuild in the event of a restore isn't large.

I think the patch for this is also fairly simple, so I'm going to see if I can get it working.

For those of us who don't mind having to wait through a large reindex after restoring from a backup, is there a way to ask the backup process to skip dumping the index tables? (Based on the above I presume they'll still be included)

(Some background to this: a full database dump of our instance at KDE is now 165GB uncompressed - probably due to us having 3.54 million commits in our repositories)

Yes, use bin/storage dump --no-indexes. See here for more details:

https://secure.phabricator.com/book/phabricator/article/configuring_backups/#skipping-indexes

The "common ngrams" changes above (which will also reduce the size of the table, probably by 50% or more) are likely to become automatic soon too, but I want to make sure they work in production first.