3 ms·
Just a month ago we migrated many of the columns from integers to UUIDs (encoded as binary(16)) in several critical tables which are pretty large (Percona serve
by kgeist 5y ago
Just a month ago we migrated many of the columns from integers to UUIDs (encoded as binary(16)) in several critical tables which are pretty large (Percona server, too), and so far I haven't heard about any serious performance degradation after the release.
- jcelerier 5y agowhat's pretty large for you though ? There are fields where 100k entries is a pretty large dataset and others where "large" starts at petabyte
- kgeist 5y agoI didn't mention that our DB setup uses sharding, and every tenant has their own DB shard (there are tens of thousands of shards). I just checked that one of the largest tenants has 2.2 mln rows in one of the affected tables, which is usually joined with 2-4 more related tables using UUIDs (another such table is 1.1 mln rows, for example), and they're on the hot code path because it's the core of the system. Maybe with sharding the difference is negligible? During code review I raised the concern that inserts and joins can become much slower after we migrate it to UUIDs, but so far my fears haven't materialized. Usually with these tables we have performance problems on the application side, not in the DB, such as ORM fetching data in a very inefficient way, or using too much RAM. Maybe it'll bite us in the long term as the tenants' shards grow in size, who knows.
- jandrewrogers 5y agoA few million rows is tiny for a database, so it shouldn't be an issue in most cases. That much data will trivially fit into cache (these days, possibly even CPU cache). It probably won't become noticeable until your tables are much larger than cache memory.