1

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!

11
  • 1
    Additional information request, please. Aany SSD or NVME devices on MySQL Host server? Post on pastebin.com and share the links. From your SSH login root, Text results of: A) SELECT COUNT(*) FROM information_schema.tables; B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) STATUS; not SHOW STATUS, just STATUS; AND very helpful information includes - htop OR top for active apps, ulimit -a for limits, iostat -xm 5 3 for IOPS and core/cpu count, for server workload tuning analysis to provide suggestions. Dec 3, 2021 at 18:05
  • 1
    @WilsonHauck here is the information you requested. Unfortunately this only captures the last day of regular activity, as it crashed after several days of uninterrupted work. After the job was completed, I also performed some backups and cleanup tasks. But perhaps it contains enough information for some useful hints: pastebin.com/raw/TudTYFcB
    – Andrei
    Dec 10, 2021 at 12:05
  • Any chance you could post TEXT results of OS Command Prompt htop? Thanks for posting other info. analysis in process. Dec 10, 2021 at 14:54
  • Do you have any SSD or NVME data storage on your PERC 8.0.26 server? Dec 10, 2021 at 15:33
  • 1
    Consider getting your SWAP space to 20% of Mem - 6B from 1/2G available today. Seeing 2 PID's for auditd seems suspicious. Was MySQL not running? Usually we see evidence of an active mysqld with CPU and Mem% used. Please comment on Suggestions influence on overall busy after 24 hours of uptime. Dec 13, 2021 at 13:26

3 Answers 3

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

2
  • Thanks a lot, Rick, I have adjusted the specific my.cnf settings as you suggested, and grouped the "per connection" settings together. After tweaking the configuration some more, I no longer get deadlocks. I'll see about getting rid of the redundant id fields and the redundant index. No, the LIMIT does not use an OFFSET. Never had any problems with ROW_FORMAT=COMPRESSED, and it's crucial for keeping storage costs down. Replaced all REPLACE INTO with ON DUPLICATE KEY UPDATE. Also following your "Analyze MySQL Performance" guide to keep an eye on slow queries.
    – Andrei
    Dec 3, 2021 at 21:06
  • We actually don't delete 99,999 items at once. We discovered this the hard way a while back, and now we're chunking all lists in multiple batches of up to 1,000 items at the software level, and do the DELETE ... IN() on that.
    – Andrei
    Dec 3, 2021 at 21:09
1

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

2
  • Thank you, Wilson. It seems that removing [InnoDB], which I thought was merely aesthetic, actually solved the performance regressions, at least in our limited test run. What happened, I speculate, was that consolidating the settings file and moving all innodb_ settings under [InnoDB] (which was grandfathered in the file from time immemorial), apparently made Percona completely ignore all of them and use defaults instead! I will attempt to run the application at full capacity for longer and will come back with the data you requested.
    – Andrei
    Dec 3, 2021 at 21:16
  • 1
    @Andrei We are happy you are making progress. Look forward to analyzing your data after running at capacity for 24 hours or more. Dec 4, 2021 at 15:19
1

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. See profile for contact info and get in touch.

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.

1
  • We are using ROW_FORMAT=COMPRESSED to reduce storage space requirements (at the expense of CPU load). Thank you very much for the suggestions @WilsonHauck, and for the generous offer for further collaboration.
    – Andrei
    Dec 13, 2021 at 9:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.