julien-duponchelle / julien-duponchelle/python-mysql-replication
Performance issues with highly sharded tables
- Dominant language
- Python
- Stars
- 2.4k
- Forks
- 690
- PR merge metrics
- No merged PRs in 30d
Description
Hello,
I'm currently streaming binary logs from a very specific MySQL architecture which consists on lots of table shards (10k tables with identical columns schema).
The `BinLogStreamReader` queries the MySQL server, stores in its `self.table_map` the schema of every table, and resets it on every binary log rotation. In my case that means 10k queries on a regular basis.
I found it was 4X faster to receive events when avoiding doing those queries.
As for my specific use case, I am using only identical table schemas, I forked the library to apply some ugly hack for my needs. I can now create an instance of `BinLogStreamReader` with a specific parameter to avoid querying for table schemas more than once:
`binlogstream.py`
```
class BinLogStreamReader(object):
def __init__(self, [...] unique_fixed_schema=False)
[...]
```
```
if binlog_event.event_type == ROTATE_EVENT and not self.__unique_fixed_schema:
self.table_map = {}
[...]
```
`event.py`
```
def _read_table_id(self):
[...]
# If the below option is set to True, we consider that all tables
# will always have the same schema for ever
if self._unique_fixed_schema:
return 0
```
I would love to have your feedback on this use case.
I'm not sure if this is something we could consider adding (obviously in a better and smarter way) in the original repository, but please tell me if you think this is something interesting to discuss about.
Thanks!
Contributor guide
Research direction
Start by reviewing BinLogStreamReader in binlogstream.py and _read_table_id in event.py, focusing on table_map resets during ROTATE_EVENT and the proposed fixed-schema behavior. Compare the reported 10k-table workload with the current schema-query flow, then clarify the supported configuration and its safety constraints. Done should include an agreed design that avoids unnecessary schema queries without breaking normal table-schema handling.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- mysql, python
- Domain
- databases, performance
- Issue type
- Feature
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 25/100