Sqlalchemy's compiled cache of SQL statements is "a dictionary-like object", the size of which is configurable. But how can I see how many entries are actually being used by an app that has been running for some time?
The documentation says
To estimate the optimal size of the cache, assuming enough memory is present on the target host, the size of the cache should be based on the number of unique SQL strings that may be rendered for the target engine in use. The most expedient way to see this is to use SQL echoing
While echoing is useful for other information, I don't see any clear way to use it for determining how many items are actually in the cache. It lists statements & whether they are cache hits or misses, cache key generation time, etc, but it doesn't seem straightforward to figure out how many entries are in the cache from this.
I'd like to do something like len(compiled_cache) with the goal of getting a rough idea about just how many unique (cacheable) queries are actually being used by an app.
The default cache inherits from collections.abc.MutableMapping, so in principle you can call len() on it or display it as if it were a normal dictionary:
>>> engine = sa.create_engine('mysql:///test', echo=True, future=True)
>>> engine._compiled_cache
{}
>>> conn = engine.connect()
2022-04-10 08:04:37,511 INFO sqlalchemy.engine.Engine SELECT DATABASE()
2022-04-10 08:04:37,511 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-04-10 08:04:37,512 INFO sqlalchemy.engine.Engine SELECT @@sql_mode
2022-04-10 08:04:37,512 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-04-10 08:04:37,513 INFO sqlalchemy.engine.Engine SELECT @@lower_case_table_names
2022-04-10 08:04:37,513 INFO sqlalchemy.engine.Engine [raw sql] ()
>>> res = conn.execute(sa.text('select * from username'))
2022-04-10 08:04:48,566 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-04-10 08:04:48,566 INFO sqlalchemy.engine.Engine select * from username
2022-04-10 08:04:48,566 INFO sqlalchemy.engine.Engine [generated in 0.00054s] ()
>>> len(engine._compiled_cache)
1
>>> # Cache hit doesn't affect size
>>> res = conn.execute(sa.text('select * from username'))
2022-04-10 08:05:20,693 INFO sqlalchemy.engine.Engine select * from username
2022-04-10 08:05:20,693 INFO sqlalchemy.engine.Engine [cached since 32.13s ago] ()
>>> len(engine._compiled_cache)
1
>>> res = conn.execute(sa.text('select id from username'))
2022-04-10 08:05:47,376 INFO sqlalchemy.engine.Engine select id from username
2022-04-10 08:05:47,376 INFO sqlalchemy.engine.Engine [generated in 0.00041s] ()
>>> len(engine._compiled_cache)
2
>>> # We can display the cache if we want to
>>> engine._compiled_cache
{
(<sqlalchemy.dialects.mysql.mysqldb.MySQLDialect_mysqldb object at 0x7f0042c90c10>, ('0', <class 'sqlalchemy.sql.elements.TextClause'>, 'text', 'select * from username'), (), False, False): [
(
<sqlalchemy.dialects.mysql.mysqldb.MySQLDialect_mysqldb object at 0x7f0042c90c10>,
('0', <class 'sqlalchemy.sql.elements.TextClause'>, 'text', 'select * from username'),
(),
False,
False
),
<sqlalchemy.dialects.mysql.mysqldb.MySQLCompiler_mysqldb object at 0x7f0042c93a30>,
2
],
(<sqlalchemy.dialects.mysql.mysqldb.MySQLDialect_mysqldb object at 0x7f0042c90c10>, ('0', <class 'sqlalchemy.sql.elements.TextClause'>, 'text', 'select id from username'), (), False, False): [
(
<sqlalchemy.dialects.mysql.mysqldb.MySQLDialect_mysqldb object at 0x7f0042c90c10>,
('0', <class 'sqlalchemy.sql.elements.TextClause'>, 'text', 'select id from username'),
(),
False,
False
),
<sqlalchemy.dialects.mysql.mysqldb.MySQLCompiler_mysqldb object at 0x7f0042e849d0>,
3
]
}
>>>
In practice, len may only be indicative if multiple concurrent connections are generating new entries, and will level off once the cache is full.