MySQL Deployment

Ratchet on MySQL 8+.

Prerequisites

• MySQL 8.0 or later
• InnoDB storage engine (required for row-level locking)
• utf8mb4 character set with utf8mb4_unicode_ci collation
• Default REPEATABLE READ or READ COMMITTED transaction isolation

Schema Setup

Apply DDL

mysql -u ratchet -p ratchet < stores/ratchet-store-mysql/src/main/resources/ddl/mysql-schema.sql

Or copy into your migration tool:

cp mysql-schema.sql src/main/resources/db/migration/V1__ratchet_schema.sql
flyway migrate

Verify Installation

SHOW TABLES LIKE 'scheduler_%';
SHOW TABLES LIKE 'ratchet_%';

You should see:

• ratchet_schema_version
• scheduler_batch
• scheduler_batch_metrics
• scheduler_business_key_reservation
• scheduler_job
• scheduler_job_archive
• scheduler_job_execution
• scheduler_job_log
• scheduler_job_queue
• scheduler_job_tag
• scheduler_lock
• scheduler_node
• scheduler_recurring_job
• scheduler_recurring_job_archive
• scheduler_resource_limit
• scheduler_resource_permit
• scheduler_workflow_condition

Configuration

DataSource

Configure your data source for MySQL:

<!-- persistence.xml -->
<persistence-unit name="your-application-pu" transaction-type="JTA">
  <jta-data-source>java:/RatchetDS</jta-data-source>
  <mapping-file>META-INF/orm-mysql.xml</mapping-file>
  <class>run.ratchet.store.entity.JobEntity</class>
  <class>run.ratchet.store.entity.JobExecutionEntity</class>
  <class>run.ratchet.store.entity.ResourceLimitEntity</class>
  <class>run.ratchet.store.entity.BatchMetricsEntity</class>
  <class>run.ratchet.store.entity.WorkflowConditionEntity</class>
  <class>run.ratchet.store.entity.ArchivedJobEntity</class>
  <class>run.ratchet.store.entity.NodeEntity</class>
  <class>run.ratchet.store.entity.JobLogEntity</class>
  <class>run.ratchet.store.entity.ResourcePermitEntity</class>
  <class>run.ratchet.store.entity.BatchEntity</class>
  <exclude-unlisted-classes>true</exclude-unlisted-classes>
  <properties>
    <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect" />
  </properties>
</persistence-unit>

The mapping-file line is required for non-Hibernate providers (EclipseLink, OpenJPA, etc.). MySQL stores UUIDv7 IDs as BINARY(16), and the mapping file applies the store-local UuidByteArrayConverter so those providers bind UUID fields as 16 bytes. Hibernate maps UUID to BINARY(16) natively on MySQL, and Hibernate 6+ rejects an AttributeConverter on an @Id attribute; omit the <mapping-file> line entirely when using Hibernate. PostgreSQL does not use this mapping file.

The MySQL store does not require a fixed persistence-unit name. By default it uses the deployment's unnamed @PersistenceContext. If your application has multiple persistence units, provide a CDI alternative for RatchetEntityManagerProvider:

@ApplicationScoped
@Alternative
@Priority(Interceptor.Priority.APPLICATION)
public class RatchetPuProvider implements RatchetEntityManagerProvider {
  @PersistenceContext(unitName = "your-application-pu")
  EntityManager em;

  @Override
  public EntityManager getEntityManager() {
    return em;
  }
}

Or via WildFly CLI:

/subsystem=datasources/data-source=RatchetDS:add( \
    jndi-name=java:/RatchetDS, \
    driver-name=mysql, \
    connection-url=jdbc:mysql://localhost:3306/ratchet, \
    user-name=ratchet, \
    password=secret, \
    min-pool-size=5, \
    max-pool-size=20, \
    valid-connection-checker-class-name=org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker)

MySQL isolation level

The MySQL store supports the server default, REPEATABLE READ, and READ COMMITTED. No datasource isolation override is required. Existing READ COMMITTED configurations remain supported.

Claims discover candidates without locking a range, then lock specific primary keys and recheck eligibility. Resource-permit acquisition reads current permit state while holding the resource lock, even if the caller already has an older transaction snapshot. The startup check accepts both isolation levels; it still rejects unsupported levels such as SERIALIZABLE by default.

Verify a live application connection with SELECT @@SESSION.transaction_isolation;.

MySQL-Specific Settings

Ratchet does not expose MySQL-only tuning flags. Use the shared scheduler settings instead:

• RatchetOptions.polling().batchSize()
• RatchetOptions.polling().minDelayMs()
• RatchetOptions.polling().maxDelayMs()
• RatchetOptions.execution().maxConcurrency("SINGLE", ...)
• RatchetOptions.maintenance().jobRetentionDays()

Schema Design

Primary Tables

Table	Purpose
scheduler_job	Cold job metadata, payload, and terminal state
scheduler_job_execution	Per-attempt execution history with timing and errors
scheduler_job_archive	Archived completed/failed/canceled jobs
scheduler_batch	Batch progress tracking

MySQL-Specific Features

The MySQL schema uses several MySQL-specific features:

• ENUM types for status, job_type, backoff_policy, and level columns
• BINARY(16) UUIDv7 identifiers for every primary and foreign key that refers to a job, batch, execution, log, archive, resource permit, or workflow row
• JSON columns for payload, params, job_result, and mdc
• GENERATED ALWAYS AS ... STORED columns to extract target_class and method_name from payload JSON
• A reservation table for active business-key uniqueness without keeping terminal rows hot
• Optional operator views in ddl/views/vw_jobs.sql that expose binary UUIDs as hyphenated strings via BIN_TO_UUID(col) without MySQL's UUIDv1 swap flag

Key Indexes

The schema includes optimized indexes for the polling query:

-- Executable claim path on scheduler_job_queue
INDEX idx_claim_executable (status, job_type, scheduled_time, priority, job_id)

-- Orphan recovery
INDEX idx_queue_orphan (status, picked_at, picked_by)

-- recurring-master scheduling
INDEX idx_rec_claim (is_paused, next_fire)

Performance Tuning

InnoDB Buffer Pool

Size the InnoDB buffer pool to 70-80% of available RAM:

[mysqld]
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 8

Redo Log Size

Larger redo logs improve write throughput:

[mysqld]
innodb_log_file_size = 1G

Max Connections

Ensure MySQL allows enough connections for your connection pool:

[mysqld]
max_connections = 200

Transaction Isolation

Upgrade older Ratchet builds that reject REPEATABLE READ before removing existing isolation overrides; disabling their startup check does not add support.

No server-wide isolation change is needed. Both REPEATABLE READ and READ COMMITTED are supported. Choose the isolation level for the application's transaction semantics; compare throughput with the same workload before choosing either for performance.

Check the effective value from an application connection:

SELECT @@SESSION.transaction_isolation;

Monitoring

Monitor Job Queue Depth

-- PENDING is live state on scheduler_job_queue (the row is deleted at terminal).
SELECT COUNT(*) AS pending_jobs
FROM scheduler_job_queue
WHERE status = 'PENDING';

Failed Job Trends

SELECT DATE(archived_at) AS date, COUNT(*) AS failures
FROM scheduler_job_archive
WHERE final_status = 'FAILED'
  AND archived_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY DATE(archived_at)
ORDER BY date DESC;

Job Execution Performance

SELECT
  AVG(duration_ms) AS avg_duration_ms,
  MAX(duration_ms) AS max_duration_ms
FROM scheduler_job_execution
WHERE started_at >= DATE_SUB(NOW(), INTERVAL 1 DAY)
  AND status = 'SUCCEEDED';

Active Nodes

SELECT node_id, heartbeat_ts, started_at
FROM scheduler_node
WHERE heartbeat_ts > DATE_SUB(NOW(), INTERVAL 30 SECOND)
ORDER BY started_at;

Maintenance

Cleanup Old Jobs

Ratchet automatically archives completed jobs based on the RATCHET_JOB_RETENTION_DAYS setting. To manually clean up:

DELETE FROM scheduler_job_archive
WHERE archived_at < DATE_SUB(NOW(), INTERVAL 90 DAY);

Rebuild Indexes

Periodically optimize tables for better query performance:

OPTIMIZE TABLE scheduler_job;
OPTIMIZE TABLE scheduler_job_execution;
OPTIMIZE TABLE scheduler_job_archive;

Backup & Recovery

Backup Strategy

Use MySQL's native backup tools:

mysqldump -u root -p ratchet > ratchet-backup.sql

Or with Percona XtraBackup for online backups:

xtrabackup --backup --target-dir=/backup

Recovery

mysql -u root -p ratchet < ratchet-backup.sql

See Also

• PostgreSQL Deployment
• Database Setup
• Runtime setup