2

What are your strategies when a MySQL/MariaDB database server grows to have too much traffic for a single host to handle, i.e. scaling CPU/RAM is not an option anymore? Do you deploy ProxySQL to start splitting the traffic according to some rule to two different hosts? What would the rule be, and how would you split the data? Has anyone migrated to TiDB? In that case, what was the strategy to detect if the SQL your app uses is fully compatible with TiDB?

Otto
  • 444
  • 10

1 Answers1

2

My strategy is:

  1. If the problem is database size: Shrink the database. Compress any data that can be compressed (see e.g. this question), and delete data that is unnecessary or of sufficiently low value. Consider also the S3 engine for archiving MariaDB data (so read-only) for AWS S3 or API-compatible storage, if that is something you have access to. Also use a sensible backup solution - mysqldump is not an ideal solution for a big database. Consider mariadb-backup or for MySQL/Percona: xtrabackup. Or better yet: LVM snapshots or ZFS snapshots.
  2. If the problem is read traffic: Scale out via standard primary-replica replication utilising the binary log, or my personal favourite which is Galera cluster. In both cases I would put MaxScale v21.06 (GPL) in front as a database proxy with a read-write splitter. (Packages here.) I have played a little with ProxySQL but found it to be lacking compared to MaxScale. Although, on the other hand, ProxySQL has a clearer commitment to GPL.
  3. If the problem is write traffic: Sharding, probably via the MariaDB Spider engine.
  4. Last resort: Some other kind of database system altogether. This could be for only part of the data, or for all the data.
dbdemon
  • 6,964
  • 4
  • 21
  • 40