Percona Blog

False.

> But it does not seperate table data into one file per table

That's because if you didn't have it set when creating the database, it won't move data to the new fs layout when you set the setting on, without an OPTIMIZE. If you had it on from the beginning, table data is per file. I literally just did an ls on my /va/lib/mysql and there's a folder per database, in which there are 2 files per table (.frm and .ibd).

When innodb_file_per_table is on, and the database has been OPTIMIZEd, only the following is stored in ibdata1 [0]:

- data dictionary aka metadata of InnoDB tables

- change buffer

- doublewrite buffer

- undo logs

[0] https://www.percona.com/blog/2013/08/20/why-is-the-ibdata1-file-continuously-growing-in-mysql/

We provide multiple solutions for sharding, ranging from https://github.com/tarantool/twemproxy-docker twemproxy port to https://github.com/tarantool/shard.
Tarantool is close to a data grid in its architecture and features from the database world do not apply 1:1.

We have been working on a general-purpose resharding for over 3 years, but have yet to release it to the open source community: it's very hard to do it well.

But our customers get a sharding scheme that best suits their business needs, including fully automatic shard management and data re-balancing. I submitted a talk about the technology and know-how behind this to Percona Live 2017: https://www.percona.com/live/17/sessions/best-practices-application-level-sharding

Was about to say that we managed upwards of 4k servers worth of MySQL databases (as in 4k baremetal servers worth, not 4k small VMs each having a small MySQL) using "Orchestrator" at Booking.com ten years ago. I checked if it was still kicking before writing this and found that the GitHub repo was archived last year. The next Google hit I find is this article from three days ago:

https://www.percona.com/blog/orchestrator-for-managing-mysql-high-availability-using-raft/

Please do some additional research into the state of maintenance of that piece of tech before jumping on it. But it certainly did a lot of powerful things for us back in the day. The automatic promotion of followers was key to our deployment.

Unfortunately every index takes time to access; it's like dereferencing a pointer. You also usually want the hottest indexes to reside in RAM. Making more indexes compete for RAM pages may and will have implications for other queries, possibly more important.

I agree that often machine resources are plentiful, and human resources are scarce, so managing indexes automatically makes sense. It's even implemented, e.g. in Oracle as a complete management tool [1], or in Postgres as a discovery and recommendation tool [2].

[1]: https://oracle-base.com/articles/19c/automatic-indexing-19c

[2]: https://www.percona.com/blog/2019/07/22/automatic-index-recommendations-in-postgresql-using-pg_qualstats-and-hypopg/

This is a great resource.

The PG docs are generally quite good, but logical replication is one of those things where experience and this sort of resource is invaluable.

That said, I also feel like logical replication is still pretty hard to use. One example, not covered here, is that the status of slots is not replicated to any secondaries. There are solutions for this now (see https://www.percona.com/blog/how-patroni-addresses-the-problem-of-the-logical-replication-slot-failover-in-a-postgresql-cluster/) but generally the domain of dealing with logical changes is still one where I feel like MySQL binlog has a leg up (though still some sharp edges) in making it a bit easier to work with.

The flip side is that GUIDs take up a ton of space and are full of entropy, which makes them very inefficient to work with, are not cache-friendly, and not sortable. Certain database engines (e.g., InnoDB on MySQL) also limit the length of primary keys for performance reasons, which disqualifies GUIDs from being used. With clustering, short keys are better as they reduce IOPS required to fetch data.

See https://www.percona.com/blog/2006/10/03/long-primary-key-for-innodb-tables/ for an interesting discussion of the topic.

In general, it's unwise to make recommendations about what data types to use, especially for primary keys, without thorough research on their implications.