diff options
Diffstat (limited to 'doc/build/faq/performance.rst')
| -rw-r--r-- | doc/build/faq/performance.rst | 160 |
1 files changed, 160 insertions, 0 deletions
diff --git a/doc/build/faq/performance.rst b/doc/build/faq/performance.rst index 6e1440721..781d6c79d 100644 --- a/doc/build/faq/performance.rst +++ b/doc/build/faq/performance.rst @@ -8,6 +8,166 @@ Performance :class: faq :backlinks: none +.. _faq_new_caching: + +Why is my application slow after upgrading to 1.4 and/or 2.x? +-------------------------------------------------------------- + +SQLAlchemy as of version 1.4 includes a +:ref:`SQL compilation caching facility <sql_caching>` which will allow +Core and ORM SQL constructs to cache their stringified form, along with other +structural information used to fetch results from the statement, allowing the +relatively expensive string compilation process to be skipped when another +structurally equivalent construct is next used. This system +relies upon functionality that is implemented for all SQL constructs, including +objects such as :class:`_schema.Column`, +:func:`_sql.select`, and :class:`_types.TypeEngine` objects, to produce a +**cache key** which fully represents their state to the degree that it affects +the SQL compilation process. + +The caching system allows SQLAlchemy 1.4 and above to be more performant than +SQLAlchemy 1.3 with regards to the time spent converting SQL constructs into +strings repeatedly. However, this only works if caching is enabled for the +dialect and SQL constructs in use; if not, string compilation is usually +similar to that of SQLAlchemy 1.3, with a slight decrease in speed in some +cases. + +There is one case however where if SQLAlchemy's new caching system has been +disabled (for reasons below), performance for the ORM may be in fact +significantly poorer than that of 1.3 or other prior releases which is due to +the lack of caching within ORM lazy loaders and object refresh queries, which +in the 1.3 and earlier releases used the now-legacy ``BakedQuery`` system. If +an application is seeing significant (30% or higher) degradations in +performance (measured in time for operations to complete) when switching to +1.4, this is the likely cause of the issue, with steps to mitigate below. + +.. seealso:: + + :ref:`sql_caching` - overview of the caching system + + :ref:`caching_caveats` - additional information regarding the warnings + generated for elements that don't enable caching. + +Step one - turn on SQL logging and confirm whether or not caching is working +^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ + +Here, we want to use the technique described at +:ref:`engine logging <sql_caching_logging>`, looking for statements with the +``[no key]`` indicator or even ``[dialect does not support caching]``. +The indicators we would see for SQL statements that are successfully participating +in the caching system would be indicating ``[generated in Xs]`` when +statements are invoked for the first time and then +``[cached since Xs ago]`` for the vast majority of statements subsequent. +If ``[no key]`` is prevalent in particular for SELECT statements, or +if caching is disabled entirely due to ``[dialect does not support caching]``, +this can be the cause of significant performance degradation. + +.. seealso:: + + :ref:`sql_caching_logging` + + +Step two - identify what constructs are blocking caching from being enabled +^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ + +Assuming statements are not being cached, there should be warnings emitted +early in the application's log (SQLAlchemy 1.4.28 and above only) indicating +dialects, :class:`.TypeEngine` objects, and SQL constructs that are not +participating in caching. + +For user defined datatypes such as those which extend :class:`_types.TypeDecorator` +and :class:`_types.UserDefinedType`, the warnings will look like:: + + sqlalchemy.ext.SAWarning: MyType will not produce a cache key because the + ``cache_ok`` attribute is not set to True. This can have significant + performance implications including some performance degradations in + comparison to prior SQLAlchemy versions. Set this attribute to True if this + type object's state is safe to use in a cache key, or False to disable this + warning. + +For custom and third party SQL elements, such as those constructed using +the techniques described at :ref:`sqlalchemy.ext.compiler_toplevel`, these +warnings will look like:: + + sqlalchemy.exc.SAWarning: Class MyClass will not make use of SQL + compilation caching as it does not set the 'inherit_cache' attribute to + ``True``. This can have significant performance implications including some + performance degradations in comparison to prior SQLAlchemy versions. Set + this attribute to True if this object can make use of the cache key + generated by the superclass. Alternatively, this attribute may be set to + False which will disable this warning. + +For custom and third party dialects which make use of the :class:`.Dialect` +class hierarchy, the warnings will look like:: + + sqlalchemy.exc.SAWarning: Dialect database:driver will not make use of SQL + compilation caching as it does not set the 'supports_statement_cache' + attribute to ``True``. This can have significant performance implications + including some performance degradations in comparison to prior SQLAlchemy + versions. Dialect maintainers should seek to set this attribute to True + after appropriate development and testing for SQLAlchemy 1.4 caching + support. Alternatively, this attribute may be set to False which will + disable this warning. + + +Step three - enable caching for the given objects and/or seek alternatives +^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ + +Steps to mitigate the lack of caching include: + +* Review and set :attr:`.ExternalType.cache_ok` to ``True`` for all custom types + which extend from :class:`_types.TypeDecorator`, + :class:`_types.UserDefinedType`, as well as subclasses of these such as + :class:`_types.PickleType`. Set this **only** if the custom type does not + include any additional state attributes which affect how it renders SQL:: + + class MyCustomType(TypeDecorator): + cache_ok = True + impl = String + + If the types in use are from a third-party library, consult with the + maintainers of that library so that it may be adjusted and released. + + .. seealso:: + + :attr:`.ExternalType.cache_ok` - background on requirements to enable + caching for custom datatypes. + +* Make sure third party dialects set :attr:`.Dialect.supports_statement_cache` + to ``True``. What this indicates is that the maintainers of a third party + dialect have made sure their dialect works with SQLAlchemy 1.4 or greater, + and that their dialect doesn't include any compilation features which may get + in the way of caching. As there are some common compilation patterns which + can in fact interfere with caching, it's important that dialect maintainers + check and test this carefully, adjusting for any of the legacy patterns + which won't work with caching. + + .. seealso:: + + :ref:`engine_thirdparty_caching` - background and examples for third-party + dialects to participate in SQL statement caching. + +* Custom SQL classes, including all DQL / DML constructs one might create + using the :ref:`sqlalchemy.ext.compiler_toplevel`, as well as ad-hoc + subclasses of objects such as :class:`_schema.Column` or + :class:`_schema.Table`. The :attr:`.HasCacheKey.inherit_cache` attribute + may be set to ``True`` for trivial subclasses, which do not contain any + subclass-specific state information which affects the SQL compilation. + + .. seealso:: + + :ref:`compilerext_caching` - guidelines for applying the + :attr:`.HasCacheKey.inherit_cache` attribute. + + +.. seealso:: + + :ref:`sql_caching` - caching system overview + + :ref:`caching_caveats` - background on warnings emitted when caching + is not enabled for specific constructs and/or dialects. + + .. _faq_how_to_profile: How can I profile a SQLAlchemy powered application? |