1

I have a percona mysql (5.7) server with 20K QPS (lots of inserts/updates/deletes).

My question is: why issuing a simple, but long, select query (to any table) with trx isolation=READ-UNCOMMITTED makes history list length (undo logs) to grow? (After query has executed the history list is purged right away).

Here's code example:

SET TRANSACTION ISOLATION LEVEL READ-UNCOMMITTED;
SELECT tt.id, tt.name, SLEEP(1) AS delay FROM table tt; # 100 rows, takes 100 to execute

In my understanding, that SELECT query does not need a read view and it does not care about data consistency. It just reads dirty, possibly uncommitted, data. But, during it's execution, history list length starts to grow fast (due to high QPS, other transactions write a lot), it turns mysql unable to purge - but why? My transaction is in READ-UNCOMMITTED mode, it wont ever need old versions of rows, right? (correct me here if I'm wrong).

I just cannot catch mysql's logic here.. and need a help.

P.S. I checked "information_schema.INNODB_TRX". it shows trx isolation=READ-UNCOMMITTED, trx_read_only=1, trx_autocommit_non_blocking=1. I checked other long running transactions, none. I repeated this on 4 different replicas and master - same behaviour. I tried without "SLEEP(1)" on bigger tables - same behaviour. I repeated this on a replica, without any connections but mine and replica's treads only (so no one could affect the test).

UPD 1 (for comment):

I have done a test. Create a table, insert 4 rows.

CREATE TABLE my_table (
  id INT(11) NOT NULL AUTO_INCREMENT
, text VARCHAR(255) DEFAULT NULL
, PRIMARY KEY (id)
) ENGINE = INNODB;

INSERT INTO my_table(id, text) VALUES (1, 'hi'), (3, 'hi 2'), (5, 'hi 3'), (7, 'hi 5');

start dirty read, session 2 will be inserting rows after that query has started

# session 1, now 2021-12-22 21:20:00
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
    SELECT tt.id, tt.text, SLEEP(4) AS delay FROM my_table tt;

start inserting rows here AFTER session 1 has started

# session 2, now 2021-12-22 21:20:22
    INSERT INTO my_table(text) VALUES('hi 7');
    INSERT INTO my_table(text) VALUES('hi 11'); ....

and so on, we can insert rows forever and query in session 1 never finishes

Max32Nov
  • 53
  • 6

2 Answers2

3

According to mysqlperformanceblog.com's Reasons for run-away main Innodb Tablespace, here are the reasons you are probably experiencing these things:

Running Transaction which does a lot of changes – If transaction modifies a lot of rows it has to use a lot of space in undo segment and there is no way around it. Be careful in particular with update or delete transactions which go over a lot of rows. In many cases doing such processes in chunks, updating/deleting may be thousands of rows per transaction may be better if your application can handle it. Note ALTER TABLE will not require excessive amount of undo space even for very large tables as it internally commits every 10000 rows.

Running Very Long Transaction If you’re running very long transaction, be it even SELECT, Innodb will be unable to purge records for changes which are done after this transaction has started, in default REPEATABLE-READ isolation mode. This means very long transactions are very bad causing a lot of garbage to be accommodated in the database. It is not limited to undo slots. When we’re speaking about Long Transactions the time is a bad measure. Having transaction in read only database open for weeks does no harm, however if database has very high update rate, say 10K+ rows are modified every second even 5 minute transaction may be considered long as it will be enough to accumulate about 3 million of row changes.

Purge Thread Falling Behind This is the most dangerous reason. It is possible for database updates happen faster than purge thread can purge records when they are no more needed which means undo space can just grow until it consumes all free space (or specified max size for ibdata1 file). “Good” thing is performance typically starts to suffer terribly well before that and it gets noticed. There are to things you can do about this problem first you can use innodb_max_purge_lag to make a threads doing modifications slow down if purge thread can’t keep up. This however does not work in all cases. If you’re running XtraDB you can also use innodb_use_purge_thread to use dedicated purge thread, which works a bit faster as it does not need to compete with other activities of main theread. You can also use more than one purge thread by setting this variable to higher values though this functionality is a bit experimental.

I have mentioned this post many years ago ...

POSSIBLE SUGGESTIONS

  • Make sure you kill all Sleeping Connections. They may still be holding Invisible Table Locks. There are situations where InnoDB will retain table locks after a rollback without knowing who made the locks (See my May 13, 2012 post Cannot update certain rows in innodb tables). History length should dissipate if such locks are present and subsequently removed.
  • Perhaps experiment with innodb_max_purge_lag on the replica to see if history length drops.
RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
3

should the read view of my query with tx_isolation=read uncommitted prevent mysql from purging undo logs? In my case I see an instant jump up of history list as the query starts and instant drop as query finishes.

Yes, that's correct behavior. Purging old row versions can't proceed past the row versions required for a given running query.

A single query holds a snapshot, regardless of your tx_isolation. Meaning, the query sees only rows that existed at the moment the query started. If new rows are created during the query execution, that query still cannot see the new rows. It's as if each query gets its own REPEATABLE-READ isolation that lasts until the end of the query execution.

This means row versions required for that query's view of the database cannot be purged, at least until the query is finished. Using READ-UNCOMMITTED does not change this.

When you use READ-COMMITTED or READ-UNCOMMITTED, row versions can be purged after the query finishes, but the transaction has not finished.

When you use REPEATABLE-READ, row versions can't be purged until after the transaction is finished.

There is no legitimate use of READ-UNCOMMITTED as far as I know. It doesn't reduce locking requirements, it doesn't reduce snapshot requirements, it only causes situations where your transaction can view anomalous data (e.g. partially-executed changed in other transactions).

I recommend do not use READ-UNCOMMITTED, ever.

Bill Karwin
  • 16,963
  • 3
  • 31
  • 45