Adjusting MySQL Data Storage Settings for WebReports

Trouble Viewing Images? Right-click on any image and select "Open in new tab" to view a larger version. You can also zoom in using Ctrl + Mouse Wheel for easier readability.

Article Goal

Learn how to increase MySQL’s data storage buffer sizes to support WebReports queuing, exporting, and downloading. This ensures larger reports can run without packet or memory errors.

Why Adjust MySQL Data Storage?

By default, MySQL’s memory allocation may be too small for large reports, especially when exporting via WebReports. Increasing these buffer settings helps:

• Prevent large packet errors

• Improve download stability

• Support bigger report queues

Tip: These settings are especially important if users frequently export large reports or queue multiple jobs in WebReports. Increasing the buffer helps avoid runtime errors during heavy report generation.

Where Do You Adjust These Settings?

These settings are configured using the MySQL Command Line Client. You'll need access to the root password that was created during your initial MySQL setup.

Steps to Adjust MySQL Data Storage

Step 1: Open the MySQL Command Line

• Launch MySQL Command Line Client from your Start Menu or shortcut

Step 2: Log in with Root Password

• Enter the root password created during the MySQL installation process

• Press Enter to log in

Tip: To paste a copied password into the client, click the title bar, then right-click inside the window

Step 3: Increase Buffer Sizes

Once logged in, run the following commands to increase the buffer sizes:

 

SET GLOBAL net_buffer_length = 1000000; SET GLOBAL max_allowed_packet = 1000000000;

 

 

Tip: If you still encounter memory errors, try increasing the values further by adding more zeroes (e.g., 2000000000). However, be mindful of your system’s physical memory limits.

Optional: Improve Rendering with InnoDB Buffer Pool

If Finished Reports or Visualize dashboards render slowly, consider adjusting the InnoDB Buffer Pool Size, which manages memory for frequently accessed data.

Reference Docs:

• How MySQL Uses Memory

• Configuring InnoDB Buffer Pool Size

Tip: The recommended innodb_buffer_pool_size is 50% of your total system RAM. Use caution—setting it too high can prevent MySQL from starting. Adjust in your my.cnf or my.ini file and restart MySQL to apply.

Article Summary

Adjusting MySQL’s net_buffer_length and max_allowed_packet settings is crucial for handling large exports and complex report downloads in WebReports. These settings can be increased using the MySQL Command Line to support heavier workloads and prevent runtime errors. For additional performance tuning, adjusting the InnoDB Buffer Pool Size can help optimize rendering in Visualize and Finished Reports.

Use Case: A user runs a large report that fails mid-download. After increasing the max_allowed_packet, the report completes successfully without errors.