@y0z @c-bata Could you review this PR?

I assigned @porink0424 as an additional reviewer, considering his recent contributions to RDBStorage. I'll proceed with reviewing the changes after @porink0424 has approved this PR.

I executed a micro-benchmark and confirmed that this PR makes study.optimize() significantly faster especially for the study with lots of trials.

Benchmark Script

import optuna
import time
import numpy as np

from concurrent.futures import ThreadPoolExecutor

n_trials = 10000
# Disable calling storage.get_best_trial for logging
optuna.logging.set_verbosity(optuna.logging.WARNING)


def objective(trial: optuna.Trial) -> float:
    s = 0.0
    for i in range(18):
        trial.set_user_attr(f"attr{i}", "attr value")
    for i in range(8):
        s += trial.suggest_float(f"x{i}", -10, 10) ** 2
    return s


storage = optuna.storages.RDBStorage("mysql+pymysql://optuna:password@127.0.0.1:3306/optuna")
start = time.time()
study = optuna.create_study(storage=storage, sampler=optuna.samplers.RandomSampler())
study.optimize(objective, n_trials, n_jobs=10)
print(f"study.optimize elapsed: {time.time() - start}")

start = time.time()
with ThreadPoolExecutor(max_workers=10) as pool:
    for i in range(20):
        pool.submit(storage.get_all_trials, study._study_id)
print(f"storage.get_all_trials: {time.time() - start}")

#!/bin/sh

docker pull mysql:8.0
docker stop optuna-mysql
sleep 3

set -e

docker run -d --rm -p 3306:3306 -v ./mysql-conf:/etc/mysql/conf.d \
    -e MYSQL_USER=optuna -e MYSQL_DATABASE=optuna -e MYSQL_PASSWORD=password -e MYSQL_ALLOW_EMPTY_PASSWORD=yes \
    --name optuna-mysql \
    mysql:8.0

echo "Wait ready for mysql"
sleep 20

python3 benchmark_for_pr5704.py

Benchmark Results

study.optimize (10 threads)

storage.get_all_trials x 20 (10 threads)

@porink0424 @c-bata Thanks for the review comments. I applied your suggestions. Please take a look.

Do you think we should update the unfinished_trial_ids and last_finished_trial_id in _CachedStorage.set_trial_state_values?

I don't have a strong opinion, but I think we don't have to update them in set_trial_state_values. Currently, it is updated only when the _read_trials_from_remote_storage is called (and the new trial is created). There are several reasons.

(1) The existing finished_trial_ids is only updated when the current condition.
(2) If we use a sampler which do not depend on the history of optimization, i.e., do not call get_all_trials, then it is unnecessary to update the unfinished_trial_ids and last_finished_trial_id in the optimization. The first call of get_all_trials for some analysis of the optimization would be slow, but don't have a significant impact, and it is actually speed up with a lot of trials by current changes.
(3) If we use a sampler which depends on the history of optimization, then the unfinished_trial_ids and last_finished_trial_id are updated when each sampling. Therefore, there is no significant impacts.

@c-bata @not522 @y0z Thanks for the comment. I updated codes and replied your suggestions. PTAL.