maria-developers team mailing list archive

[Michael Widenius <monty@xxxxxxxxxxxx>]

Hi!

>>>>> "MARK" == MARK CALLAGHAN <mdcallag@xxxxxxxxx> writes:

MARK> It can be dangerous for us to run "set read_only" on a production server
MARK> because it can block in close_cached_tables. More details about the pain
MARK> this caused at a previous job are at:

MARK> http://mysqlha.blogspot.com/2008/07/what-exactly-does-flush-tables-with.html


MARK> Per the code in set_var.cc:


MARK>     /*

MARK>     Perform a 'FLUSH TABLES WITH READ LOCK'.

MARK>     This is a 3 step process:

MARK>     - [1] lock_global_read_lock()

MARK>     - [2] close_cached_tables()

MARK>     - [3] make_global_read_lock_block_commit()

MARK>     [1] prevents new connections from obtaining tables locked for write.

MARK>     [2] waits until all existing connections close their tables.

MARK>     [3] prevents transactions from being committed.

MARK>   */


MARK> Can there be a variant that doesn't do #2? My workload doesn't use MyISAM
MARK> and I don't know if #2 is done because of MyISAM. Calling
MARK> close_cached_tables seems like a heavy way to force LOCK TABLEs to be
MARK> unlocked. Any long running queries will cause #2 to block.

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.

It's possible to do a 'FLUSH TABLES FAST WITH READ LOCK' version that
would only flush the header of MyISAM tables, which would probably
help you, as long as you don't plan to copy any tables to any other
server.

Regards,
Monty