2

I have a large database (millions of rows in hundreds of tables) that contains sensitive data (Think SSNs, account numbers, etc). I am tasked with safeguarding the data and have chosen to encrypt only sensitive data and not every field. This encryption is handled by the application, and not the database server, so all solutions require that the encryption process stays in the application.

Because there are existing JOINs in the application, I can't just encrypt every value separately because 2 identical values will yield different encrypted strings (and thus won't JOIN). I've chosen to key the data (a hash of the value, so the key will always be the same for each unique value) so that I can store the key in place of the original data and use it to look up the encrypted data when needed.

So I need to go through the database, row-by-row, and encrypt certain fields, storing the encryption in a separate table, and then storing the key in place of the original data.

My problem is that looping over the data is very slow. 10-15 minutes per 1000 values. I've optimized the task by grouping the values so that I only have to encrypt each unique value 1 time and then update all instances of that value with the key in 1 UPDATE.

Is there a different approach (keeping all the encryption in the Application layer) that can speed up my task of encrypting the sensitive fields?

2 Answers 2

1

You're paying the extra price of network IO and there's not a whole lot you can do if you need to remove the encryption (really the key storage/management) from the database. If your MySQL instance is on a Unix host, you can try connecting with a Unix socket file instead of TCP/IP. Just host the app on the same machine during the encryption process, finish the DB then move the app back to wherever it should be.

If your MySQL DB is running on Windows then you're out of luck. You could still put the app on the same machine or isolate them from the rest of the network so you minimize impact from network congestion or switch latency but those are likely to yield limited improvement unless your network was really, really busy or if you were using some old 100Mbps switch.

1

Performance is probably being hurt by all the COMMITs and single-row actions. The following should speed it up by order(s) of magnitude.

  • Do a batch of 100 rows at a time
  • A single multi-row INSERT for the the extra table.
  • Use BEGIN...COMMIT around each batch.

Since you need to pull the data out, encrypt it, and then put it back in, here are some more details.

Overview:

  1. Create an extra table t for working in.
  2. Do a batch
  3. TRUNCATE TABLE t
  4. If not finished, go back to step 2.
  5. DROP TABLE t

The batch:

Decide which rows to work on.  See my http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks
BEGIN;
SELECT ... FROM ... WHERE ... FOR UPDATE;  -- (100 rows)
Munch on them in the application
INSERT INTO t VALUES (...), (...) ... -- a single multi-row insert
UPDATE real_table JOIN t SET real_table.xxx = t.xxx;
etc.
COMMIT;

Note that each SQL is working on 100 rows at a time. "100" is big enough give you most of the speedup possible, yet small enough to not interfere noticeably with other operations.

Your Answer

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

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