Set up Orca to use SQL

You can configure Orca to use a MySQL compatible database in place of Redis for all of its persistence use cases. This provides more resiliency for your deployment.

Orca’s execution state is stored in Redis by default, but can be configured for SQL. In this topology, Redis is still required for the work queue. Using SQL for execution state will make your Spinnaker installation more durable.

This guide will go over MySQL setup, how to configure Orca, as well as how to perform a zero-downtime migration from Redis to SQL.

If you already have an Orca deployment, you should also refer to the Redis to SQL Migration Guide .

MySQL 5.7 Setup

Orca ships with MySQL drivers by default, but you can include your own JDBC drivers on the classpath if you need to connect to a different database.

Orca has been developed and tested targeting MySQL 5.7. As part of this, setting MySQL’s tx_isolation value to READ-COMMITTED is essential to successfully running Orca in SQL. While Orca will attempt to set this on connection sessions, it is better to have it set on the database itself.

The SQL integration is configured to support a migration user and a service user. The migration user will only be used to perform schema changes on the database, whereas the service user will be used for runtime traffic.

Before deploying Orca, the schema and database uses must first be manually setup:

  1. Set MySQL Server variable tx_isolation setting to READ-COMMITTED. Refer to MySQL Server System Variables .

From the MySQL Server command line run

  1. set tx_isolation = 'READ-COMMITTED';
  1. Setup the schema and database users
  1. CREATE SCHEMA `orca` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  3. GRANT 
  4.   SELECT, INSERT, UPDATE, DELETE, CREATE, EXECUTE, SHOW VIEW
  5. ON `orca`.* 
  6. TO 'orca_service'@'%'; -- IDENTIFIED BY "password" if using password based auth
  8. GRANT 
  9.   SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, LOCK TABLES, EXECUTE, SHOW VIEW 
  10. ON `orca`.* 
  11. TO 'orca_migrate'@'%'; -- IDENTIFIED BY "password" if using password based auth

When Orca starts up, it will perform database migrations to ensure its running the correct schema. It is safe to start multiple Orca services at the same time, even if migrations need to be run.

Configuring Orca for SQL

This configuration is your baseline for Orca to talk to SQL, in orca.yml.

  1. sql:
  2.   enabled: true
  3.   connectionPool:
  4.     jdbcUrl: jdbc:mysql://localhost:3306/orca
  5.     user: orca_service
  6.     password: hunter2
  7.     connectionTimeout: 5000
  8.     maxLifetime: 30000
  9.     # MariaDB-specific:
  10.     maxPoolSize: 50
  11.   migration:
  12.     jdbcUrl: jdbc:mysql://localhost:3306/orca
  13.     user: orca_migrate
  14.     password: hunter2
  16. # Ensure we're only using SQL for accessing execution state
  17. executionRepository:
  18.   sql:
  19.     enabled: true
  20.   redis:
  21.     enabled: false
  23. # Reporting on active execution metrics will be handled by SQL
  24. monitor:
  25.   activeExecutions:
  26.     redis: false
  28. # Use SQL for Orca's work queue
  29. # Settings from Netflix and may require adjustment for your environment
  30. # Only validated with AWS Aurora MySQL 5.7
  31. # Please PR if you have success with other databases
  32. keiko:
  33.   queue:
  34.     sql:
  35.       enabled: true
  36.     redis:
  37.       enabled: false
  39. queue:
  40.   zombieCheck:
  41.     enabled: true
  42.   pendingExecutionService:
  43.     sql:
  44.       enabled: true
  45.     redis:
  46.       enabled: false

Note that orca.yml overwrites the configuration generated by Halyard.

In case you have deployed Spinnaker using Halyard , you need to add the configuration above to orca-local.yml.

Read more about profiles and service-settings here .

MariaDB

The default MySQL Connector for Aurora MySQL 5.7 should be fine, but you may also setup Orca to use the MariaDB JDBC driver over MySQL Connector.

The MariaDB driver is Aurora clustering aware, which takes care of automatic master failover operations. Due to licensing issues, Orca cannot ship with the MariaDB driver.

An example of wiring up MariaDB into Orca can be found here: robzienert/orca-mariadb-extension .

Netflix’s Amazon Aurora Example

While vanilla MySQL provides more durability and performance over Redis, Netflix additionally uses Amazon Aurora MySQL 5.7. If you’d like to configure Orca to use Aurora as well, here is how Netflix has it set up.

IMPORTANT: This configuration is for multi-region Aurora replication. If you are only deploying Aurora into a single region, don’t enable any binlog settings.

Aurora Parameter Groups

  • binlog_cache_size: 32768
  • default_tmp_storage_engine: InnoDB
  • general_log: 0
  • innodb_adaptive_hash_index: 0
  • innodb_buffer_pool_size: {DBInstanceClassMemory*3/4}
  • key_buffer_size: 16777216
  • log_queries_not_using_indexes: 0
  • log_throttle_queries_not_using_indexes: 60
  • long_query_time: 0.5
  • max_allowed_packet: 25165824
  • max_binlog_size: 134217728
  • query_cache_size: {DBInstanceClassMemory/24}
  • query_cache_type: 1
  • read_buffer_size: 262144
  • slow_query_log: 1
  • sync_binlog: 1
  • tx_isolation: READ-COMMITTED

Aurora DB Cluster Parameter Group

  • binlog_checksum: NONE
  • binlog_error_action: IGNORE_ERROR
  • binlog_format: MIXED
  • character_set_client: utf8mb4
  • character_set_connection: utf8mb4
  • character_set_database: utf8mb4
  • character_set_filesystem: utf8mb4
  • character_set_results: utf8mb4
  • character_set_server: utf8mb4
  • collation_connection: utf8mb4_unicode_ci
  • collation_server: utf8mb4_unicode_ci
  • innodb_checksums: 0

Last modified February 24, 2022: Update orca-sql.md (#183) (df43255)