Overview

When I tried to extract data from MySQL with Embulk and it was slow, I researched various related topics. For now, I'll just list the reference links.

Query Processing Flow

(Oracle) SQL Processing

(SQL Server) Processing a SELECT Statement

<Unofficial> MySQL Logical Architecture

<Unofficial> How MySQL Works

Understanding SQL Structure by Reading Execution Plans

Prepared Statements

Key Points About Prepared Statements That Server-Side Engineers Should Know

(MySQL) Prepared Statements

(Postgres) PREPARE

(Wiki) Prepared statement

Prepared Statements and Result Sets

(Postgres) Issuing Queries and Processing Results

Fetch size

Understanding "Cursors"

Fetching and Inserting 1 Million Records via JDBC

A Story About Getting Stuck with JDBC setFetchSize()

MySQL Command-Line Client

If you encounter problems due to insufficient memory for large result sets, use the --quick option. This forces mysql to retrieve results from the server one row at a time instead of retrieving the entire result set and buffering it in memory before displaying it.

embulk-input-jdbc

• fetch_rows : number of rows to fetch one time (integer, default: 10000)
  • If this value is set to > 1:
    • It uses a server-side prepared statement and fetches rows by chunks.
  • If this value is set to 1:
    • It uses a client-side built statement and fetches rows one by one.
    • Internally, useCursorFetch=false is used and java.sql.Statement.setFetchSize is set to Integer.MIN_VALUE.
  • If this value is set to -1:
    • It uses a client-side built statement and fetches all rows at once. This may cause OutOfMemoryError.
    • Internally, useCursorFetch=false is used and java.sql.Statement.setFetchSize is not set.

Performance Improvement

https://www.manageengine.jp/products/Applications_Manager/solution_mysql-speed-improvement.html

https://airbyte.com/data-engineering-resources/optimizing-mysql-queries

Pagination

SQL for Pagination Processing

Pagination in MySQL

Pagination vs Cursor

Offset vs Cursor-Based Pagination: Which is the Right Choice for Your Project?

Offset pagination vs Cursor pagination

ResultSet and JVM Memory

Does jdbc dataset store all rows in jvm memory

ResultSet behavior with MySQL database, does it store all rows in memory?

JVM Heap Area

How Java Heap Memory Management Works

Java Heap and Garbage Collection

JVM Heap and Containers

JVM Heap Size and Tuning in the Container Era

In containers, a Linux kernel feature called CGroup can be used to limit the memory available to processes inside the container. However, when you get the memory size from inside a container, you see the memory size of the container host. Some people may have experienced running the free command inside a container and seeing the host's memory size displayed. To solve this problem, an option to get the heap size from CGroup instead of memory size was added in Java 9.

UseContainerSupport is an option added in Java 10 (JDK-8146115). It has also been backported to Java 8u191 and similar versions. UseContainerSupport not only retrieves the memory limit from CGroup, but also has the following features:

• Uses the CPU limit values from CGroup as well
• Can also retrieve memory usage on CGroup

The UseContainerSupport option is enabled by default. Therefore, even without specifying anything, 1/4 of the memory available to the container will be allocated as heap size.

CGroup

Linux Kernel Container Features [2] - What is cgroup? (Part 1)