JuiceFS is ideal for physical backups of MySQL, see our official documentation for details. Recently, a customer reported that the xtrabackup –prepare process for backup validation was very slow during testing. We did the analysis with the performance analysis tools provided by JuiceFS, quickly found performance bottlenecks, and reduced the time by 1/10 in an hour by constantly adjusting XtraBackup parameters and JuiceFS mount parameters. This article will share our performance analysis and optimization process records to provide reference for you to analyze and optimize I/O performance.

Data preparation

We used the SysBench tool to generate a single-table database of around 11GiB size, with the partition of the database table set to 10. To simulate a normal database read/write scenario, the SysBench accesses the database at a pressure of 50 requests per second, where the database writes data to data disks are in the range of 8 to 10MiB/s. Back up the database to JuiceFS with the following commands.

# xtrabackup --backup --target-dir=/jfs/base/
Copy the code

To ensure that the data in each data preparation operation is exactly the same, the snapshot function of JuiceFS is used to generate the snapshot/ JFS /base_snapshot/ based on the/JFS /base directory. Before each operation, the system deletes the data obtained in the previous data preparation operation and generates a new snapshot.

Use default parameters

# ./juicefs mount volume-demoz /jfs

#  time xtrabackup --prepare --apply-log-only --target-dir=/jfs/base_snapshot
Copy the code

The execution time is 62 seconds.

JuiceFS supports exporting oplog operation logs and visualizing oplog. Before executing xtrabackup –prepare we open a new terminal to connect to the server and type in the command line

# cat /jfs/.oplog > oplog.txt
Copy the code

After collecting Oplog logs, run xtrabackup –prepare. After the operation is complete, download oplog. TXT to the local PC and upload the oplog analysis page provided by JuiceFS to juicefs.com/oplog/.

Let’s visualize Oplog.

Here is an overview of the various elements in this diagram. Our oplog contains time stamps, thread ids, file system operation functions (read, write, fsync, flush, etc.), operation duration, etc. The number on the left represents the thread ID, the horizontal axis represents the time, and different types of operations are colored differently.

When we zoom in on the partial image, it becomes clear that different colors represent different types of operations.

Several threads that are not relevant to this operation are excluded. During data preparation, there are four threads for reading and five threads for writing, which overlap in time.

Increase XtraBackup’s memory buffer

Refer to the official XtraBackup documentation, data preparation is the process of using embedded InnoDB to perform crash recovery on backup datasets.

Use the –use-memory option to increase the size of the embedded InnoDB memory buffer. The default is 100MB, we increased it to 4GB.

# time xtrabackup --prepare --use-memory=4G --apply-log-only --target-dir=/jfs/base_snapshot
Copy the code

Execution time dropped to 33 seconds.

You can see that the reads and writes do not overlap, and the data is read to memory and then written to the file system.

Increases the number of XtraBackup read threads

By increasing the buffer, the time is cut in half, and the entire read process is still relatively time-consuming. We see that each reader thread is basically full, and we try to add more readers.

# time xtrabackup --prepare --use-memory=4G --innodb-file-io-threads=16 --innodb-read-io-threads=16 --apply-log-only --target-dir=/jfs/base_snapshot
Copy the code

Execution time dropped to 23 seconds.

The number of read threads has increased to 16 (4 by default) and the read operation has dropped to around 7 seconds.

JuiceFS enables asynchronous write

In the previous step we greatly optimized the read operation time, and now the time consumed by the write process is more obvious. Through the analysis of Oplog, it is found that fsync cannot be parallel in write operations, so increasing the number of write threads does not improve the write efficiency. We also verified this point by increasing the number of write threads in actual operation, which will not be described here. By analyzing the parameters (offset, write data size) of oplog write operations to the same file (same file descriptor), we found a large number of random write operations. We can enable the writeback option when mounting JuiceFS to write data to the local site first and then asynchronously to the object store.

# ./juicefs mount --writeback volume-demoz /jfs
# time xtrabackup --prepare --use-memory=4G --innodb-file-io-threads=16 --innodb-read-io-threads=16 --apply-log-only --target-dir=/jfs/base_snapshot
Copy the code

The time dropped to 11.8 seconds.

The write process has dropped to about 1.5 seconds.

We saw that read threads are still very intensive. We tried to keep increasing the number of read threads. The maximum number of read threads in InnoDB is 64.

# time xtrabackup --prepare --use-memory=4G --innodb-file-io-threads=64 --innodb-read-io-threads=64 --apply-log-only --target-dir=/jfs/base_snapshot
Copy the code

The execution time is 11.2 seconds, which is basically the same as before.

We can see that the read operation of the reader thread is relatively sparse. It should be because the data read by the thread is dependent on each other, so it cannot be fully parallelized. The time of the read process cannot be compressed by increasing the number of threads.

Increase the disk cache for JuiceFS

In the previous step, the efficiency of the read process by increasing the number of read threads has reached its peak, and the read process time can only be reduced by reducing the latency of reading data.

JuiceFS read operations provide prefetch and cache acceleration capabilities, and we will try to reduce read latency by increasing JuiceFS ‘local cache.

Change the local cache of JuiceFS from high performance cloud disk to SSD cloud disk, and change the cache size from 1G to 10GB.

# ./juicefs mount --writeback volume-demoz --cache-size=10000 --cache-dir=/data/jfsCache /jfs

# time xtrabackup --prepare --use-memory=4G --innodb-file-io-threads=64 --innodb-read-io-threads=64 --apply-log-only --target-dir=/jfs/base_snapshot
Copy the code

Execution time dropped to 6.9 seconds.

The read operation time is further reduced by improving cache performance and increasing cache space.

To sum up, we analyzed Oplog and continuously searched for points that could be optimized, and reduced the whole data preparation process from 62 seconds to 6.9 seconds step by step. The effect can be shown more intuitively in the figure below.

Increase the amount of database data

The above operations are aimed at 11G, a relatively small data set, and a good result is obtained by constantly adjusting parameters for optimization. For comparison, we generate a single-table database with partition 10 of about 115G in the same way. The backup operation is performed with SysBench continuing at 50 requests per second.

# time xtrabackup --prepare --use-memory=4G --innodb-file-io-threads=64 --innodb-read-io-threads=64 --apply-log-only --target-dir=/jfs/base_snapshot
Copy the code

The process takes 74 seconds.

As we can see, reading and writing are still separate.

When the amount of data increases by a factor of 10, the preparation time increases by a factor of 10. This is because the backup (Xtrabackup –backup) process takes 10 times longer and produces 10 times as many Xtrabackup_logfiles as SysBench does with the same pressure on the database. Data preparation is to merge all data updates in xtrabackup_logfile into the data file. It can be seen that even if the data size is increased by 10 times, the time for updating a single log is basically unchanged. This can also be verified from the figure above. As the data size increases, the preparation process is still divided into two distinct processes: read data and write data, indicating that the set buffer size of 4GB is still sufficient, and the whole process can still be done in memory and updated to the file system.

conclusion

We used SysBench, a relatively simple tool, to construct the initial data and continuously update the database to simulate the database running during data backup. Oplog of JuiceFS is used to observe the read and write characteristics of XtraBackup accessing backup data during data preparation. Parameters of XtraBackup and JuiceFS are adjusted to continuously optimize the efficiency of data preparation.

In a real production scenario, where the situation is much more complex than in our SysBench simulations, our linear relationship above is not necessarily strictly true, but the idea that we can quickly identify points of optimization by analyzing Oplog and then continually tune the cache and concurrency of XtraBackup and JuiceFS is a common one.

The whole parameter setting process takes about an hour. The Oplog analysis tool plays a very important role in this process, helping us quickly locate system performance bottlenecks, so as to adjust parameters for optimization. We also hope that the Oplog analysis function can help you quickly locate and analyze performance problems.

If you have any help, please pay attention to our project Juicedata/JuiceFS! (0 ᴗ 0 ✿)