Configuring MySQL for performance

If you use the tpfrtmc offline utility and a MySQL instance in high throughput environments, configure your MySQL instance to ensure that MySQL can run for long periods of time without being interrupted.

About this task

By default, MySQL uses the system malloc libraries to allocate and release memory. When the tpfrtmc offline utility is running with high throughput, MySQL retains memory over long periods of time, even though that memory is already freed. The result is that MySQL memory usage grows over time and can use all memory on the server and cause various components to fail.

You can configure MySQL to use a more performance sensitive malloc library to reduce the overall usage of memory over long periods of high throughput. If you use the Sample analytics pipeline, this configuration is already set.

• For MySQL version 8.0.32 or later, which uses Oracle Linux® based containers, complete the following steps:
  1. Enter the following command to download the jemalloc library from the Oracle Linux repository:

     wget https://yum.oracle.com/repo/OracleLinux/OL8/developer/EPEL/x86_64/getPackage/jemalloc-5.2.1-2.el8.x86_64.rpm

  2. Take one of the following actions to install the jemalloc library on your system:
     • Enter the following command:RUN rpm -i /home/tpfrtmc/jemalloc-5.2.1-2.el8.x86_64.rpm
     • Add the following lines to your Docker compose file:

       COPY jemalloc-5.2.1-2.el8.x86_64.rpm /home/tpfrtmc/
       RUN rpm -i /home/tpfrtmc/jemalloc-5.2.1-2.el8.x86_64.rpm

  3. Take one of the following actions:
     • Set up the following environment variable for MySQL on your system:

       LD_PRELOAD="/usr/lib64/libjemalloc.so.2"

     • Add the following line to your Docker compose file in the environment section:

       LD_PRELOAD: /usr/lib64/libjemalloc.so.2

  4. Start MySQL.
• For MySQL version 8.0.17 or earlier, which uses Debian Linux based containers, complete the following steps:
  1. Take one of the following actions to install the libjemalloc1 library on your system:
     • Enter the following command:

       apt-get install libjemalloc1 -y

     • Add the following line to your Docker compose file:

       RUN apt-get install libjemalloc1 -y

  2. Take one of the following actions:
     • Set up the following environment variable for MySQL on your system:

       LD_PRELOAD="/usr/lib/x86_64-linux-gnu/libjemalloc.so.1"

     • Add the following line to your Docker compose file in the environment section:

       LD_PRELOAD: /usr/lib/x86_64-linux-gnu/libjemalloc.so.1

  3. Start MySQL.