Improving performance for large volume of simultaneous reads/writes on Percona 8

Question

A general description of the environment and the use case:

Technical:

• 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;

Application:

1. 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).
2. The input data consists of exactly two fields, field1, field2, which are also part of the output. There is a relation of one-to-many between field1 and field2.
3. The query for reading data from the input table is of the form SELECT * FROM input WHERE field1 = ? LIMIT 99999 and could plausibly return between 1 and 99,999 rows per each value of field1.
4. 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(?, ..., ?);
5. 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.
6. 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 REPLACE INTO with 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 CHARSET from utf8 to 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!

Answer 1

~1900 always-on connections

What the heck are they doing? What is the value of SHOW GLOBAL STATUS LIKE 'Threads_running';? Check that number several times; if it gets above, say, 20, you could be having contention issues. Normally lots of "idle" threads is not a problem.

UNIQUE KEY `f12` (`field1`,`field2`),
KEY `field1` (`field1`)  -- This is redundant and can be dropped.

How is ROW_FORMAT=COMPRESSED treating you? (I'm not a fan of it.)

lasts several days

Would summary tables help out?

... input ...
PRIMARY KEY (`id`),
UNIQUE KEY `f12` (`field1`,`field2`),

If you don't need id, toss it and promote the UNIQUE to PRIMARY.

LIMIT 99999

Will you be using OFFSET? If so, there is a performance issue we should discuss.

(DELETE FROM input WHERE field1 = ? AND field2 IN(?, ..., ?);

I don't know where the inflection point is, but 99999 items in an IN list will probably choke the processing. In other situations, I have found the optimal chunking to be somewhere between 100 and 1000, not 99999. More discussion: http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks

REPLACE INTO with INSERT ... ON DUPLICATE KEY UPDATE

Yes. IODKU does the Insert or Update is a single "step". REPLACE is two significant steps: DELETE, then INSERT. Note that that means the AUTO_INCREMENT` value will change even if the other columns are unchanged.

deadlocks

Would you like to share one? SHOW ENGINE=InnoDB STATUS;

2048

Since you are talking about ~1900 connections, be aware that a connection is likely to need multiple entries in the table_open_cache, etc. That is, the rest of the things set to 2048 may be set too low.

join_buffer_size = 128M max_heap_table_size = 8G tmp_table_size = 4G

Many settings are "per connection". This one, for example could hugely blow up if certain queries happened 'simultaneously' in many of your 1900 connections. Lower it.

key_buffer_size = 1G

MySQL 8 does not handle MyISAM; lower this value to 10M.

If you would like more analysis of your settings and/or of the slowlog, see http://mysql.rjweb.org/doc.php/mysql_analysis

Answer 2

Your my.cnf should NOT have stray section name

[innodb]

remove it, please.

First easily identified problem, then after stop/start, 24 hours of instance uptime provide requested data for workload analysis.
Thanks

Answer 3

Rate Per Second = RPS

Suggestions to improve performance for your my.cnf for your 8.0.26-16 Percona instance

innodb_lru_scan_depth=100  # from 256 to minimize CPU cycles used every second
thread_pool_size=6  # from 8 to better allow sharing of 8 available cpu's
read_rnd_buffer_size=96K  # from 2M to reduce handler_read_rnd_next RPS of 6,545
net_buffer_length=96K  # from 16K to reduce packet count required when busy
innodb_buffer_pool_size=16G  # from 8G to reduce innodb_data_reads RPS of 6,168

Many more opportunities exist.

Observations, It appears general_log_file and slow_query_log_file are not available - meaning you have no recorded critical files - OR the SHOW GLOBAL VARIABLES; results were not completely posted for analysis. ROW_FORMAT=COMPRESSED should be reconsidered. Every row READ requires decompression, every second when used for any table.