Skip to content
:tech
← Articles

AI-translated. Read the original in Korean

Buzzvil NewsEngineering

Optimizing MySQL for Honeyscreen’s Points Migration

Optimizing MySQL for Honeyscreen’s Points Migration

In a previous post, we introduced Honeyscreen’s new DynamoDB-based points system. Its biggest change was combining the separate points-history and user-balance tables into a single, consistent schema. We also consolidated tables previously separated by point type. When first designing the schema, I had debated whether to use separate tables for each type or one table with a point-type column. Each approach has advantages and disadvantages.

Multiple Tables

  • Columns and indexes can be tailored to each point type.
  • Type-specific queries run quickly without additional indexes.
  • Retrieving a user’s complete chronological points history requires querying every table, hurting performance.
  • Management becomes harder as the number of point types grows.

One Table

  • All types share one schema, so adding a column required by one type means changing the entire table.
  • Each point type has a different foreign key—for example, an ad ID for ad rewards or an order ID for points spent on purchases. You must decide whether to put these in one column or several.
  • New point types do not require new tables.
  • The schema can eliminate the need for a separate balance table.

I initially chose multiple tables. Over time, though, I began to think one table would have been better. The decisive problem was that we had nearly ten points tables, making a user’s history slow to retrieve and difficult to manage. By then, experience had also taught us the requirements of each point type, so we could design a general-purpose table. We decided to consolidate the history and, while doing so, migrate it to DynamoDB.

In the single-table system, every points entry also records the balance at that moment, much like a bank statement. This makes it possible to see a user’s balance at any point in time. Previously, only the final balance lived in a separate table; reconstructing an earlier balance required summing all preceding entries. The new structure solves that naturally. New entries had running balances, but old entries did not, so we needed to generate them. With billions of historical records, that was no small task. Our migration followed these steps.

  1. Create a migration instance from a snapshot of the live AWS RDS database.
  2. Create the consolidated points table.
  3. Copy data from the type-specific tables into it.
  4. Starting with each user’s first entry, update the running-balance column chronologically.
  5. Back up the consolidated table to S3.

Updating each user’s historical balances required sorting their entries by time. The consolidated table therefore needed a composite index on user ID and accrual time. Creating the index before copying the data would slow inserts, so we optimized the process by copying first without the index, then building it afterward.

Next came updating the running balances, which was not straightforward in MySQL. Fortunately, the approach in this answer combined an ordered UPDATE with variables. We ordered by user ID and accrual time, stored the running total in a point_sum variable, and used it to update the next row. The linked example treated the entire table as one history, whereas Honeyscreen needed a separate history for each user. We therefore added a last_user_id variable and reset point_sum to zero whenever the current row’s user_id changed. The query looked roughly like this.

SET @last_point_sum := 0;
SET @last_user_id := -1;
UPDATE
  points
SET
  point_sum = IF(@last_user_id = user_id, @last_point_sum := @last_point_sum + point_amount, @last_point_sum := 0),
  @last_user_id := user_id
WHERE
  user_id BETWEEN 0 AND 999
ORDER BY
  user_id, created_at;

It worked as expected on a small dataset, but updating billions of points records was far too slow. It looked likely to take weeks, so I stopped and looked for optimizations. First, I changed the following MySQL parameters.

innodb_flush_log_at_trx_commit: 0 innodb_support_xa: 0 sync_binlog: 0 tx_isolation: READ-UNCOMMITTED

innodb_flush_log_at_trx_commit protects committed transactions against loss during failures. Setting it to 0 improves performance by avoiding a disk sync of the InnoDB log on every commit. Setting sync_binlog to 0 leaves binary-log flushing to the OS, also improving performance. Because the binary log is needed only for replication and certain recovery operations, disabling it altogether seemed better. On RDS, this means setting backup-retention-period to 0. innodb_support_xa reportedly ensures consistency between the InnoDB and binary logs; that was unnecessary here, so I set it to 0 too, though it likely made little difference with binary logging already disabled. skip-innodb_doublewrite could offer another small gain, but it is not recommended for production and cannot be changed on RDS, so I could not use it. For a disposable migration workload like this, it may be useful where available.

MySQL’s default transaction isolation level is REPEATABLE READ, which uses gap locks. Switching to READ COMMITTED or READ-UNCOMMITTED avoids them. I chose READ-UNCOMMITTED because it appeared to have the least overhead.

Next, I used EXPLAIN to inspect the UPDATE execution plan. It used the user-ID/accrual-time composite index as intended, but the Extra column said “Using where; Using filesort,” indicating a separate sort. A Stack Overflow answer said UPDATE uses indexes only in its WHERE clause, so our composite index was not helping with ordering. Locks during execution were another source of overhead. I used the following command to inspect transaction locks.

SELECT * FROM information_schema.INNODB_TRX

Running that query while the UPDATE was in progress showed trx_rows_locked continually increasing. I also tried the following query to check lock usage.

SHOW ENGINE INNODB STATUS;

Because the query’s state keeps changing, I checked twice during execution. Extracting the relevant portions showed row locks in use, as follows.

---TRANSACTION 29583745568, ACTIVE 5 sec fetching rows mysql tables in use 1, locked 1 45467 lock struct(s), heap size 4863528, 5273956 row lock(s) MySQL thread id 424965, OS thread handle 0x2b64bcb9d700, query id 1579693 xxx.xxx.xxx.xxx user System lock
---TRANSACTION 29583745568, ACTIVE 18 sec starting index read mysql tables in use 1, locked 1 110753 lock struct(s), heap size 11744808, 8905501 row lock(s), undo log entries 447763 MySQL thread id 424965, OS thread handle 0x2b64bcb9d700, query id 1579693 xxx.xxx.xxx.xxx user updating

To reduce lock overhead, I created another table with the same schema and inserted the calculated running-balance results into it instead of updating the existing table. The MySQL reference manual explains that a plain SELECT does not use locks, whereas UPDATE takes exclusive next-key locks on every scanned record. The latter seemed inefficient here. I also understood inserts into an auto-increment-primary-key table to be highly efficient in MySQL. The revised query follows.

SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;
SET @new_point_sum := 0;
SET @last_user_id := -1;
INSERT INTO
  points_final
  (
    SELECT
      user_id, point_amount, date_at, point_type, coalesce(@new_point_sum := IF(@last_user_id = user_id, @new_point_sum + point_amount, 0)), 
      coalesce(@last_user_id := user_id)
    FROM
      points
    WHERE
      user_id BETWEEN 0 AND 999
    ORDER BY
      user_id, date_at
  );
COMMIT;
SET autocommit=1;
SET unique_checks=1;
SET foreign_key_checks=1;

I used COALESCE to avoid MySQL’s undefined variable-evaluation order. The reference manual states, “However, the order of evaluation for expressions involving user variables is undefined.” In the final query above, the expression updating @last_user_id could run before the one updating @new_point_sum. I found a way to enforce evaluation order in this article, using COALESCE. It feels a little uncomfortable, but appears to work.

I also set autocommit to 0 to run the query inside a transaction. This avoids committing every insert and instead commits once at the end, making the operation more efficient. Transactions may sound inherently slower, but for bulk inserts they can be beneficial. I set unique_checks and foreign_key_checks to 0 to avoid unnecessary constraint checks, although that may not matter much in this case. “SELECT * FROM information_schema.INNODB_TRX” showed trx_rows_locked fixed at zero, confirming no locks were being taken. “SHOW ENGINE INNODB STATUS” likewise showed no row locks.

---TRANSACTION 29583746127, ACTIVE 2 sec fetching rows mysql tables in use 2, locked 1 2 lock struct(s), heap size 360, 0 row lock(s), undo log entries 203998 MySQL thread id 11, OS thread handle 0x2aea60329700, query id 390 xxx.xxx.xxx.xxx user Sending data
---TRANSACTION 29583746127, ACTIVE 15 sec inserting mysql tables in use 2, locked 1 2 lock struct(s), heap size 360, 0 row lock(s), undo log entries 1294454 MySQL thread id 11, OS thread handle 0x2aea60329700, query id 390 xxx.xxx.xxx.xxx user Sending data

Switching to INSERT improved performance by more than two to three times over UPDATE, but it still was not enough. While the migration ran, CPU usage was low, and neither disk IOPS nor throughput was fully utilized. The bottleneck was unclear. In plain language, the query did this:

  • Retrieve user x’s entire history in chronological order.
  • Use the user_id/date_at index to identify the required records, then fetch the actual data from data blocks.
  • Iterate through the records, updating @new_point_sum.
  • Insert the updated history into the new points table.
  • Repeat for every user x.

The most suspicious step was retrieving history through the user_id/date_at index. The index finds the relevant records quickly, but every column other than user_id and date_at still has to be read from the data blocks. Those blocks are scattered across the disk, potentially requiring as many disk accesses as there are history records. That is bound to be slow. On an HDD, the head spends time seeking around the disk. Even an SSD, despite better random access, still has a problem: it reads whole blocks (512K), so fetching one row can waste bandwidth on the rest of the block. If the dataset fits in memory, those blocks can be cached and reused. If it is larger than memory, repeated cache eviction makes the work inefficient.

To address this, I adopted a covering index as described in this blog post, adding every table column to the existing user_id/date_at index. Retrieving a user’s history no longer required accessing data blocks, and performance improved dramatically. A query that would have taken weeks finished within a single day.

Only one task remained: moving the migrated table to S3. I tried AWS Data Pipeline, but it failed. This discussion explains that tables larger than 4 GB cannot be transferred this way. It also supports only commas as delimiters, so data containing commas is problematic. Moving MySQL data to S3 seemed common enough that I had expected better support, making these restrictions disappointing.

Next I tried mysqldump. It can back up data as INSERT statements, but CSV output requires access to the database server’s disk, which RDS does not provide. XtraBackup had the same disk-access requirement. I therefore followed an approach using the mysql command-line utility directly.

The remaining step was uploading the backup to S3. Saving a large backup to disk only to upload it again felt inefficient, so I wrote a shell script that streams it directly. It opens a pipe, starts an aws s3 command in the background with that pipe as input, then sends MySQL’s output into it. Multiprocess programming in a shell script turned out to be rather fun.

trap "rm -f pipe" EXIT

rm -f pipe
mkfifo pipe

gzip <pipe | aws s3 cp - s3://path-to-file &

# Open pipe for writing.
exec 3>pipe

id=0
chunk=10000
while [ $id -le $last_pk ]
do
  to=$(( $id + $chunk - 1 ))
  mysql -N -B -uusername db_name -ppassword  -h mysql_address -e "SELECT * FROM points_final WHERE id between $id AND $to" >&3

  id=$(( $id + $chunk ))
done

# Close pipe.
exec 3>&-

echo "All done!!"
exit 0

Conclusion

I still have much to learn about MySQL, but the migration’s trial and error taught me a lot. Here are the main lessons.

  • For large updates, creating a new table and using SELECT/INSERT can sometimes be more efficient.
  • Make good use of covering indexes, especially on intermediate migration tables that will be discarded after one use.
  • Use EXPLAIN to inspect query execution plans.
  • If EXPLAIN does not reveal the problem, try SHOW ENGINE INNODB STATUS.
  • Long live shell scripts!

Thank you.

Reference

All articles