A general description of the environment and the use case:
- We're running a distributed application with a single server and ~25 clients; each client is running ~75 threads.
- At one connection per thread, this results in up to ~1900 always-on connections from all client threads to the one server that continuously read, write, and delete data.
- The server machine that's hosting the database has 32G RAM and 8 cores, running Linux.
Tables involved (obfuscated but otherwise unmodified, to make this question as generic and as useful as possible, while also retaining the relevant information):
CREATE TABLE `input` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `user` varchar(191) COLLATE utf8mb4_0900_bin DEFAULT NULL, `domain` varchar(191) COLLATE utf8mb4_0900_bin DEFAULT NULL, `mail_hash` varchar(191) COLLATE utf8mb4_0900_bin DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `f12` (`field1`,`field2`), KEY `field1` (`field1`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
CREATE TABLE `output` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `field1` varchar(191) COLLATE utf8mb4_0900_bin DEFAULT NULL, `field2` varchar(191) COLLATE utf8mb4_0900_bin DEFAULT NULL, [...various other output fields], `dt` datetime DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `f12` (`field1`,`field2`), ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;
- The main process for a typical job lasts several days. It involves reading batches of up to 99,999 records from an input table that contains between ~100M and 200M rows, processing them, then writing the output to a table that is now at ~500M rows (holding data from older jobs).
- The input data consists of exactly two fields,
field2, which are also part of the output. There is a relation of one-to-many between
- The query for reading data from the input table is of the form
SELECT * FROM input WHERE field1 = ? LIMIT 99999and could plausibly return between 1 and 99,999 rows per each value of
- Processing each record takes between a few milliseconds and a few seconds, and after it is processed it is removed from the input table in batch (
DELETE FROM input WHERE field1 = ? AND field2 IN(?, ..., ?);
- Prior to processing the data, the process will check the output table to see if the same data hasn't already been processed; the query for this is
SELECT ... FROM output WHERE field1 = ? AND field2 IN(?, ..., ?);with up to 1,000 elements in the
IN()construct at once.
- Writes to the output table use
REPLACE INTO output [all fields], and may or may not overwrite existing data (we are also looking at changing the
INSERT ... ON DUPLICATE KEY UPDATE, as we are not modifying the primary key).
We recently upgraded from Percona 5.7 to Percona 8.0, and we noticed a significant drop in performance, as well deadlocks, which we had not experienced before. The slow query log says some data may take up to 40 seconds to be read from the
input table. The
output table got locked when performing a version of the query described at point 5 above (
SELECT ... FROM output WHERE field1 = ? AND field2 IN(?, ..., ?) AND dt > ?; with ~15K of elements inside
IN(). To fix this, we had to chunk it to multiple requests limited to 1,000 each and remove the
dt > ? (we're now filtering on date in the application), because this would take minutes.
The only other change apart from the upgrade we did was to change the default table
utf8mb4 (we read that performance issues were eliminated in version 8; using it 5.7 would grind the processing to a halt).
Here's the configuration we ended up with after tweaking
my.cnf for the past few hours to at least get the application to run without locking up:
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid secure_file_priv = "" bind-address = 0.0.0.0 loose-local-infile = 1 local-infile = 1 skip-name-resolve disable-log-bin max_allowed_packet = 128M max_connections = 2048 transaction-isolation = READ-UNCOMMITTED open_files_limit = 2048 table_open_cache = 2048 innodb_open_files = 2048 thread_cache_size = 2048 join_buffer_size = 128M sort_buffer_size = 16M read_rnd_buffer_size = 2M max_prepared_stmt_count = 20000 thread_handling = pool-of-threads max_heap_table_size = 8G tmp_table_size = 4G key_buffer_size = 1G read_buffer_size = 1M [InnoDB] innodb_file_per_table = 1 innodb_log_file_size = 2G innodb_log_buffer_size = 512M innodb_undo_tablespaces = 2 innodb_locks_unsafe_for_binlog = 1 innodb_buffer_pool_size = 8G innodb_buffer_pool_instances = 8 innodb_use_native_aio = 1 innodb_io_capacity = 250 innodb_io_capacity_max = 500 innodb_purge_threads = 1 innodb_adaptive_hash_index = 0 innodb_page_cleaners = 4 innodb_lru_scan_depth = 256
I'd greatly appreciate any hints as to what could make the same application that worked in Percona 5.7 suddenly grind to a halt in Percona 8.0. Should we go back to
utf8? Also, if anything looks out of place in the configuration, do not hesitate to point it out. Thank you!