Как ускорить UPDATE MySQL?

Question

[vasiaduman]

У меня есть следующая проблема.
В скрипте есть

"""
SELECT id FROM 
    table
WHERE
    time_login < %s
ORDER BY time_last_login ASC
LIMIT 1 FOR UPDATE
""" % time_threshold

time_login = unixtime метка
time_threshold = int(time.time()) - (5 * 60)

Сразу же после этого запроса есть

time_last_login = int(time.time())
        update_time_query = "UPDATE table SET time_last_login = %s WHERE id = %s"
        cursor.execute(update_time_query, (time_last_login, id))
        connection.commit()

Делаю для того, чтобы отпустить строку от FOR UPDATE.
Теперь проблема, с каждым запросом растет время выполнения скрипт.
Начинается с 0.34 сек и доход до 41 секунды.
Запросов в секунду 200, строк в таблице 33000
Специально для этого был взят выделенный сервер (Intel(R) Core(TM) i7-8700 CPU @ 3.20GHz, 64 ОЗУ, 1 ТБ SSD)
Но запросы все так же медленно выполняются.

mysql конфиг

[mysqld]
disable-log-bin
innodb_buffer_pool_size = 48G
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
max_heap_table_size = 256M
sql-mode =
tmp_table_size = 256M
transaction-isolation = READ-COMMITTED
default-authentication-plugin = mysql_native_password
mysqlx = OFF
performance-schema = ON
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

# New Settings
max_connections = 2000000
thread_cache_size = 2000
table_open_cache = 20000
table_open_cache_instances = 16
open_files_limit = 100000
max_allowed_packet = 256M
innodb_lock_wait_timeout = 600
innodb_spin_wait_delay = 96

# Recommendations
skip-name-resolve = ON
sort_buffer_size = 16M
read_rnd_buffer_size = 16M
join_buffer_size = 16M
performance-schema = ON
innodb_redo_log_capacity = 12G
innodb_buffer_pool_instances = 50

# Additional settings
innodb_io_capacity = 4000
innodb_log_file_size = 8G
innodb_flush_neighbors = 0
innodb_thread_concurrency = 0
innodb_read_io_threads = 16
innodb_write_io_threads = 16
# Дополнительные параметры для улучшения производительности
innodb_flush_log_at_timeout = 1
innodb_max_dirty_pages_pct = 90
innodb_max_dirty_pages_pct_lwm = 10
innodb_lru_scan_depth = 2048
innodb_io_capacity_max = 10000

# Дополнительные настройки
innodb_adaptive_hash_index = ON
innodb_change_buffering = all
innodb_log_buffer_size = 512M
table_definition_cache = 2000

Подскажите что могу сделать?

Answer 1

[Петр]

Блокировка "for update" работает только внутри транзакции.
И не работает с autocommit.
Делайте явный START TRANSACTION

Answer 2

[AUser0]

Индекс на id можете сделать.

А ещё можете помедитировать над запросом

EXPLAIN UPDATE table SET time_last_login='%s' WHERE id='%s'

, цифры подставьте сами.

Comments

[vasiaduman]

mysql> EXPLAIN UPDATE table SET time_last_login ='1720756038' WHERE id=9834;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | UPDATE | table | NULL | range | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

[nnn Xion]

а какие индексы вообще в таблице есть?

[nnn Xion]

Возможно стоит проганять по чаще `ANALYZE TABLE` и `OPTIMIZE TABLE` но вроде мускул и так норм справляется с этим. и optimize у него не такой как в постгре. но думаю попробовать стоит

Answer 3

[mayton2019]

Проверте что построен индекс по полю time_last_login

Comments

[vasiaduman]

а ничего страшного что часто меняется?
раз в пол часа?

[mayton2019]

vasiaduman, ничего страшного. Индекс строится 1 раз и синхронизируется на каждую транзакцию.
Зато у тебя сортировка нормально отработает.

Answer 4

[Пума Тайланд]

Если у вас 200 запросов на апдейт в секунду и всего 33к записей, перейдите на редис или любой аналог многопоточный если нужен перформанс в сотовые секунды

Answer 5

[otlejal]

Как минимум использовать асинхронный драйвер mysql. Как максимум перейти на постгрес и увеличить кэш. Разработчикам тапалок привет)