Details
-
Task
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
None
Description
It can be dangerous to run "set read_only" on a production server because it can block in close_cached_tables. More details about the pain this caused previously are at:
http://mysqlha.blogspot.com/2008/07/what-exactly-does-flush-tables-with.html
Per the code in set_var.cc:
/*
|
Perform a 'FLUSH TABLES WITH READ LOCK'.
|
This is a 3 step process:
|
- [1] lock_global_read_lock()
|
- [2] close_cached_tables()
|
- [3] make_global_read_lock_block_commit()
|
[1] prevents new connections from obtaining tables locked for write.
|
[2] waits until all existing connections close their tables.
|
[3] prevents transactions from being committed.
|
*/
|
Can there be a variant that doesn't do #2? My workload doesn't use MyISAM and I don't know if #2 is done because of MyISAM. Calling close_cached_tables seems like a heavy way to force LOCK TABLEs to be unlocked. Any long running queries will cause #2 to block.
Attachments
Issue Links
- relates to
-
-
- Open
-
Activity
By Mark:
We are not using this for backup. This doesn't make it safe to copy InnoDB/XtraDB tables as the background IO threads can still do writes (flush dirty pages, merge insert buffer pages, purge delete rows.
We want a fast version of this to support other admin activities. In this case we don't care if this doesn't make it safe to backup MyISAM. Is it possible to provide that option and is it interesting for MariaDB to have such an option?
By Monty:
This is how innodb/xtradb works just now. However, I assume it should not be hard to fix XtraDB to flush things completely for tables that are closed and flushed and not in use by any transaction.
(This is what Aria does today).
What is possible relatively easy is to add the above FAST keyword to FLUSH TABLES. For MyISAM tables it would do as I describe above, for other table type it would basicly be a nop and would thus solve your problem while being useful for others.
Assuming this won't block waiting for running queries to finish, yes let's proceed with a worklog.
Suggestion of how solve the issue that [2] (closed_cahched_tables())
is slow:
I would prefer that setting 'readonly' would mean that
myisam/aria tables are synced to disk and not changed anymore (so that
you can do a backup of them if you wanted).
The syncing is not critical for transactional tables (like InnoDB).
I suggest that we change close_cached_tables(), for the case of
FLUSH TABLES WITH READ LOCK, so that we only wait for non-transactional
tables (like MyISAM and Aria) to be flushed and not used.
This can be done by adding a handler specific flag to the above
handler types that would signal that we need to wait until the table
is flushed and closed. These tables we handle like now in
close_cached_tables() while we ignore waiting for other table types.
If you are only using InnoDB tables, there would not be any waits anymore
for [2]. For MyISAM and Aria tables things would be as before.
Estimate amount of work (with testing): 8-16 hours (probably closer to 8).
This includes a patch for MySQL 5.1 and we would add this to MariaDB 5.5
Will "refresh_version" still be incremented in this case? And if yes, then doesn't that create a significant performance impact by forcing all InnoDB table instances to be reopened?
We can skip incrementing refresh_version for the case of
set readonly=1
The reason we increment refresh_version is to ensure that we will close any old instances of the table and read the possible new table definition (that may have changed during FLUSH TABLES).
This is not needed when setting readonly.
Patch committed
http://lists.askmonty.org/pipermail/commits/2012-May/003284.html
New patch committed:
http://lists.askmonty.org/pipermail/commits/2012-May/003324.html
By Monty:
The reason for 2 is to ensure that that all table info is written to
disk so that if you do a snapshot or copy of tables, you will get
things in a consistent state.
This is mostly for MyISAM and non transactional tables, but it will
also speed up things for InnoDB tables and allow you to copy xtradb
tables from one server to another (if you are using table spaces)
without having to take down the server.