5

I'd love to hear everyone's opinion on this issue. I currently use Innodb mysql v8 in RDS. Our db is moderate, with ~130 tables, most are small, with our largest table having ~30 million rows over 6 years. We've currently scaled our RDS instant vertically way larger than our database should need and we're still CPU bound and can't use replicas because the slave gets too far behind. I'm convinced it's because we have UUID version 1 as our primary keys for our tables and have to do lots of string comparisons for our application.

What I'd like to do is prove it by creating a subset of tables that use an auto-incrementing INT as the primary key and foreign keys but I'm struggling to see how this could work. If I just add an auto-incrementing column to the current tables, the queries that our ORM uses won't include it so it wouldn't be that much of a gain. What I am wondering is how I could use a transition table to do essentially swap out the values from UUIDs to INT. For instance, an ORM query now says something like:

select id_primary_key from table_old where id_foreign_key in (UUID1, UUID2, UUID3) with an output of :1d3sed4a-5812-a35c-0204-515f6at42b46

If I create a transition table that has auto_inc INT, and old UUID, what would be the next step so that when the ORM runs that query, it queries against INTs instead of UUIDs?

select id_primary_key from table_new where id_foreign_key in (1, 2, 3) output: 58743

Any thoughts?

UPDATE-- It looks like we're going to be looking into more of a global model in the future, where have multiple dbs and as such will keep the UUIDs. We'll try to go the sharding route as well as trying to store them as VARBINARY(16) and using UUIDtoBIN() w/ the swap flag set to true because we're using UUID v1. Thank you all for your answers and time! I learned a lot from testing it.

3
  • I think it's a three-factor issue. Issue 1 are indexes, whether missing, unused or duplicate. I went through and added missing indexes and removed unused indexes. Issue 2 are ORM slow-running queries. We've rewritten several of those. Issue three I thought was the schema design itself because we don't have a large database (~280gb) and our largest table now is about 30 million rows. We have enough RAM to cache our entire database into memory, but we are CPU bound all the time. With the size and needing character set conversions, I became convinced it's all the giant string comparisons.
    – Muab Nhoj
    Nov 17, 2022 at 16:51
  • 1
    Make sure the string comparisons are at least using compatible collations, otherwise they'll cause table-scans. Another optimization is to make both strings use binary collation, that way the string comparison is done in one memcmp() call internally, instead of comparing character-by-character using the collation's equivalency lookups. In my experience, binary collation can gain up to 30% performance improvement. But that's still only incremental. Optimizations that gain orders of magnitude improvement are worth more. Nov 17, 2022 at 17:19
  • Using BINARY or VARBINARY is as good in that respect as using a binary collation. Nov 17, 2022 at 17:20

3 Answers 3

7

Changing from UUID to integer is bound to be a lot of work to implement, and might not help. I recommend first to try to understand more specifically why you have performance problems before you do an invasive change.

For example:

  • Are you using ROW based replication and have large tables without a primary key constraint? The replica will do a table-scan for every update on that table, which will slow down replication throughput.

  • Is the rate of insert/update/delete simply too high for replication to keep up? Or is the replica provisioned as a less performant server than its source? Or do you need to split the database into "shards" such that each has a fraction of the write traffic?

  • Have you identified your slow queries and used EXPLAIN to analyze their optimization? How many are doing table-scans or index-scans instead of using indexes to improve searching or sorting? Do you have the right indexes to help those queries? Look for common mistakes like collation mismatch when comparing strings, or using LIKE or REGEXP, or comparing the results of expressions without creating an expression index.

In any case, you should develop some tests to prove that the solution will solve your problem before implementing it against your production system.

1
  • Bill, I totally agree and I've been looking into pretty much everything you mentioned above since I started here. The ORM spits out some of the ugliest queries you've ever seen so query tuning and proper indexing have been the first steps. Also I think we haven't had replicas that are the same build as the master so of course they would fall behind IMO, which I've shared.
    – Muab Nhoj
    Nov 17, 2022 at 16:40
3

I'm with the other Answers -- it is a lot of work and may not buy you what you need. And it may be possible to get queries and replication sped up even without abandoning UUIDs.

As for conversion, I would do something like the following. [Caveat; this has not been debugged!]

  1. Add an AUTO_INCREMENT column on each table that will need it. At the same time, add INDEX(id) so that the id will continue to be updated while you proceed with other steps.
  2. If you have FOREIGN KEYS, you may have to at least turn them off.
  3. Build a big set of SQL scripts to switch from UUIDs to the new id. Wherever tables are currently JOINed on a UUID, you will need to switch to the id and, at the same time, get the ids set. This is a giant task, and you probably must take an outage for as long as necessary.
  4. To avoid changing all the code to reference the new ids, we will need to swap names around. This leads me to step 0:

Step 0: Change PRIMARY KEYs from UUIDs to "natural" PKs wherever possible. In my experience, this gets rid of 2/3 of the UUIDs/ids for PKs. (And in many cases, speeds up queries!) This may lead to less time taken in step 5 for dropping the UUIDs and changing INDEX(id) to PRIMARY KEY(id)

Sorry for giving you a half-baked Answer. Mostly I argue against getting rid of UUIDs. (Yeah, I also frequently argue against ever using UUIDs.)

If you do end up with INTs, you may as well use suitable-sized INTs. This will save a little more space, hence a little more speed.

If the ORM is getting in your way, maybe it is time to convert to writing your own SQL.

2

I create a transition table that has auto_inc INT, and old UUID, what would be the next step so that when the ORM runs that query, it queries against INTs instead of UUIDs?

Well, you'd want to join to that transition table which will be by the new INT field when you have it, but unfortunately by the UUID still when you don't, such as when you're trying to join a parent and child table together.

I think this approach may end being more work to maintain in the long run and may not help improve performance much if your issue really is due to using a UUID.

Instead, why not just:

  1. Add a new INT AUTO_INCREMENT primary key column with the appropriate starting value (and INT foreign key columns when applicable) to every table.
  2. Backfill that column using a window function, such as ROW_NUMBER(), based on whatever sorting logic you see fit.
  3. Join all the foreign key related child tables to their parents by their current UUID fields to relate them.
  4. After the above join, update the new INT foreign key fields with their related parent table's new INT primary key values.
  5. Drop the old UUID columns from every table.
  6. Rename the new columns to the old column names.

It'll surely be a good amount of work upfront but I think worth it compared to the alternative approach in the long run, and will ensure you're completely dependent on only the INT fields, ensuring utmost performance in that regard.

7
  • 1
    Nice! This makes a lot of sense. It's definitely going to be a ton of work but I think it would be worthwhile performance wise for sure. Thank you!
    – Muab Nhoj
    Nov 16, 2022 at 15:57
  • If we backfill, how will we handle new entries to all tables? At some point, we'd need to auto-increment the pks right?
    – Muab Nhoj
    Nov 16, 2022 at 17:18
  • 1
    @MuabNhoj Yes, I didn't go into explicit details, but you'd want to make the new INT columns have the IDENTITY specification. Whether you do that before hand, and do an identity insert, or after you're done backfilling, is up to you. I'm not sure which is easier. But yes moving forward you would have IDENTITY columns that you maintain the relationship of, as opposed to GUIDs.
    – J.D.
    Nov 16, 2022 at 17:29
  • 2
    @MuabNhoj Btw I know I answered your direct question, but I definitely agree with Bill also on really determining what your root performance problem is. It's rare for it to be the data type being used as the key that's the main performance bottleneck (though GUIDs can play a small factor). It's not impossible it's your issue, but definitely do your homework before going through the lengthy conversion process I outlined.
    – J.D.
    Nov 16, 2022 at 17:38
  • 3
    Yeah, it would be pretty frustrating to go through all the work to change the primary key type, but discover it doesn't solve the real performance problem. Nov 16, 2022 at 18:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.