Ticket #584 (new enhancement)

Opened 5 years ago

Speedup constraint queries

Reported by: jackdied@… Owned by: andrew
Priority: major Milestone:
Component: migrations Version: unknown
Keywords: Cc:


MySQL has _very_ slow reads from the information_schema table. This fork adds a caching layer to generic.DatabaseOperations?. On my production setup with 44 migrations it cuts the number of info_schema queries by 70% and the ones it does do are cheaper.

=How It Works=

All constraint queries now go through the lookup_constraint() method. If the cache is empty or invalid _fill_constraint_cache() gets called. The cache itself is just a dictionary of cache[db_name][table_name][column_name] with the column value being a set() of constraints for that column. The cache gets invalidated at the table_name level. If the table changes the value of cache[db_name][table_name] is set to INVALID.

When a lookup happens if the cache is empty it gets filled. If the cache for that table is INVALID it gets filled. If the cache has been populated and isn't INVALID either the column constraints are returned - or in the usual case - we get a KeyError? for the column and return an empty list (most keys don't have constraints).

The generic Ops class fills the cache per-table. The MySQL Ops class fills the cache for the whole database at once. That difference is hidden in the _is_valid_cache() method.

As a result of the refactoring the mysql module lost net 20 lines of code and the generic module gained net 60.

Note: See TracTickets for help on using tickets.