[MDEV-7901] re-implement analyze table for low impact Created: 2015-04-03  Updated: 2021-01-29  Resolved: 2016-08-03

Status: Closed
Project: MariaDB Server
Component/s: Admin statements
Fix Version/s: 10.2.2

Type: Task Priority: Major
Reporter: Daniel Black Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 7
Labels: verified

Issue Links:
Blocks
is blocked by MDEV-24733 Doc bug: ANALYZE TABLE Closed
Relates
relates to MDEV-10494 re-implement analyze table for even l... Open
relates to MDEV-10555 Server crashes in mysql_admin_table u... Closed
relates to MDEV-7196 ANALYZE TABLE should do complete tabl... Open
relates to MDEV-7363 bulk engine-independent index stats m... Closed
relates to MDEV-7829 Could not execute Update_rows_v1 even... Closed
Sprint: 10.1.6-1, 10.1.6-2, 10.1.7-1, 10.2.0-5, 10.2.0-6, 5.5.51 & 10.2.2

 Description   

MDEV-7829 and MDEV-7363 where attempts to work around the implementation of analyze table because holding a read lock only on the entire table while gathers statistics. This can be up to an hour or so on larger tables as witnessed in production databases. This level of total read only on a table isn't acceptable.

MDEV-7196 suggested by jplindst indicated that perhaps a full scan is more appropriate to give better statistics. Obviously this isn't going to help the locking scenario at all.

So perhaps, instead of locking the entire table, the analyze table takes a full table scan in incremental blocks. These block sizes should try to scale to something that keeps the lock time less that a goal time, a new variable max_analyze_table_lock_time (locks can be dropped in analyze table gets there too soon). Analyze table being a long running operation should drop to read committed (or uncommitted?) mode to keep the undo logs from growing because of the intensive read operations.

 Comments   
Comment by Sergei Petrunia [ 2015-04-04 ]

Removing analyze-stmt label . That label is for ANALYZE statement feature (https://mariadb.com/kb/en/mariadb/analyze-statement ), which has nothing to do with "ANALYZE table" statements.s

Comment by Sergei Golubchik [ 2015-04-04 ]

I don't see why ANALYZE TABLE that collects engine-independent statistics needs to take table locks at all. It could do a lock-less consistent read on InnoDB, just like a normal SELECT * does.

Comment by Michael Widenius [ 2015-07-07 ]

Agree with Sergei Golubchick; For InnoDB and XtraDB using lock-less select and even running in not-repeatable mode should allow analyze to run without any notable performance degredation.

When it comes to MyISAM and Aria, there is two possibilties:

  • Run in ALLOW_READ_ALLOW_WRITE mode; It doesn't matter if we miss a few rows while doing a scan. It should not be hard to extend MyISAM and Aria to do this.
  • Run things in blocks, 10000 rows at a time; If there is another table waiting for the lock, unlock, relock and continue from the original position.
Comment by VAROQUI Stephane [ 2015-07-30 ]

Would be nice to introduce ISET Independent Storage Engine Throttling API. We can upgrade EITS system tables via such plugin, but can also get an auto schedule backup by streaming chunk and Binlog Row Events between chunks. Some plugin can be use for maintenance like deleting rows based on on expiration date and column name

Comment by Daniel Black [ 2015-08-31 ]

Was any progress made? Can this be pushed to a public branch so continuation work can be done?

Comment by Oleksandr Byelkin [ 2015-08-31 ]

We found it too big changes to squeeze it just before RC release.

Comment by Daniel Black [ 2015-08-31 ]

We found it too big changes to squeeze it just before RC release.

Is that due to the MyISAM/Aria complexity monty mentioned? If so can that part be put as a independent deferred request? After all, in a very large proportion of the time, innodb tables are the only ones that impact operations.

Comment by Oleksandr Byelkin [ 2015-08-31 ]

Mostly because very probable delay of stable version due to fixing errors in this part.

Comment by Oleksandr Byelkin [ 2016-08-01 ]

done with innodb relaxed locks

Comment by Oleksandr Byelkin [ 2016-08-01 ]

revision-id: fc42991720838e165ad448b6707602cded92faa4 (mariadb-10.2.1-9-gfc42991)
parent(s): 08683a726773f8cdf16a4a3dfb3920e5f7842481
committer: Oleksandr Byelkin
timestamp: 2016-08-01 19:25:45 +0200
message:

MDEV-7901: re-implement analyze table for low impact

Table before collecting engine independent statistics now is reopened in read mode,
InnoDB allow write operations in this case.

—

Comment by Daniel Black [ 2016-08-01 ]

Thank you so much sanja

Comment by Oleksandr Byelkin [ 2016-08-03 ]

revision-id: 810e5ae2eb8d7bdc1dc5549865dc9177360b8e62 (mariadb-10.2.1-9-g810e5ae)
parent(s): 08683a726773f8cdf16a4a3dfb3920e5f7842481
committer: Oleksandr Byelkin
timestamp: 2016-08-03 17:26:55 +0200
message:

MDEV-7901: re-implement analyze table for low impact

Table before collecting engine independent statistics now is reopened in read mode,
InnoDB allow write operations in this case.

Comment by Sergei Golubchik [ 2016-08-03 ]

ok to push

Generated at Thu Feb 08 07:23:08 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.