*Background:*
I am using a multi-shard MySQL setup (multiple db hosts, each host holding
many databases, a.k.a. "shards"). My Python application is creating engines
to many of these shards. For performance reasons, the application utilizes
bakery and BakedQuery to avoid compiling SQL statements on every ORM call.
*Issue:*
I have 100+ BakedQuery and 100+ shards. It appears it needs a bakery
(LRUCache) of size = (N BakedQuery * M shards) to cache all of the queries
for all shards because the cache key for the compiled SQL (not the
BakedQuery) contains the dialect object. Please see _execute_clauseelement
in sqlalchemy.engine.base.
key = (
dialect,
elem,
tuple(sorted(keys)),
self.schema_for_object.hash_key,
len(distilled_params) > 1,
)
compiled_sql = self._execution_options["compiled_cache"].get(key)
if compiled_sql is None:
compiled_sql = elem.compile(
dialect=dialect,
column_keys=keys,
inline=len(distilled_params) > 1,
schema_translate_map=self.schema_for_object
if not self.schema_for_object.is_default
else None,
)
self._execution_options["compiled_cache"][key] = compiled_sql
When the cache capacity is small, it keeps evicting cache entries and
compiling queries, using a lot of CPU. Also if it takes N*M for cache
capacity, it is bad for memory.
*Question:*
1. Any good suggestions on fixing the performance and memory issue? e.g. by
sharing the cache key
2. To share the cache key, do we implement a __eq__ for the Dialect object
or make all the shards (engines) share the same Dialect object?
3. Is sharing a Dialect object dangerous? I see default_schema_name in the
Dialect object.
3. This not only affects bakedquery, but also compiled_cache of engines
(non-BakedQuery). Is there a good universal fix for the issue (like Q2,
sharing dialect)?
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/d1e7f3ce-4cd8-4d9d-b774-44175a4e523b%40googlegroups.com.
