Shulou(Shulou.com)06/01 Report--

On the role of MYSQL flush table

The level is limited and we still need to learn. If there are any mistakes, please correct them.

First give the official documentation:

? FLUSH TABLES

Closes all open tables, forces all tables in use to be closed, and flushes the query cache. FLUSH

TABLES also removes all query results from the query cache, like the RESET QUERY CACHE

Statement.

In MySQL 5.6, FLUSH TABLES is not permitted when there is an active LOCK TABLES... READ.

To flush and lock tables, use FLUSH TABLES tbl_name... WITH READ LOCK instead.

? FLUSH TABLES tbl_name [, tbl_name]...

With a list of one or more comma-separated table names, this statement is like FLUSH TABLES with

No names except that the server flushes only the named tables. No error occurs if a named table

Does not exist.

The description says that FLUSH TABLE closes the open table and flushes the query cache, if there is a LOCK TABLES. READ is not allowed if it exists.

You can use FLUSH TABLES tbl_name... if you need to flush and implement READ LOCK at the same time WITH READ LOCK

If you close an open table here, you must be confused about what an open table is.

What does it mean to close open tables, and do you need to synchronize dirty data to disk while closing open tables?

We consider using innodb_file_per_table to build INNODB tables. Each table has a corresponding data file idb and a format file frm.

But some data is still stored in shared tablespaces. If our thread wants to access the data of this table, we must first open these files and then pass the

System calls such as pread () / read () lseek () perform file location finding and reading. Then the system call where we open the file should be the open () system call.

Of course, it is necessary to have a LINUX system and programming foundation. First, each process contains PCB (PCB kernel state space, which is shared between processes).

PCB is the identification of the existence of a process, which is stored in the information related to the running of the process. In the kernel, it is actually a structure, which contains a lot of information such as me.

We are familiar with PID PPID (about 100 kinds of information) in which a file descriptor is saved in PCB, and it is easy for us to kernel code task_struct.

The information about the structure, that is, PCB, is found as follows:

/ * open file information * /

Struct files_struct * files

It corresponds to the file structure in the kernel. You can see that the process has a files_struct structure to save. We can simply think that the process saves the process.

There is a file descriptor that opens all files. The file descriptor counts backwards from 0, and each count represents a file that is opened. If a process wants to

To open a file, our LINUX uses the open () system call. The function successfully returns the open file descriptor. If it fails, the return value is set to-1.

At the same time, give perror to see the explanation of this function in LINUX.

Open () and creat () return the new file descriptor, or-1 if an error occurred (in which case, errno)

Is set appropriately).

Well, since MYSQL is a process with multiple threads, then certainly can not escape such LINUX arrangement, so since MYSQL wants to open the table, then the underlying

The call must be an OPEN function. Since the file is opened and closed, when programming in LINUX system or C language, we usually finish using the file.

Use CLOSE () or FCLOSE () to close the file so that the system releases the file information retained by the open file and prevents memory leaks.

So let's prove that we use strace to TRACE LINUX thread information in the LINUX process respectively.

I. about opening the table

1. First close and restart mysql to avoid interference with the file descriptions of other open tables.

[root@hadoop1 kernels] # service mysqldefault restart

Shutting down MySQL.... [OK]

Starting MySQL... [OK]

2. Use pstree to view all threads of mysql (first find the MYSQL process ID)

Mysqld (10735) ─┬─ {mysqld} (10745)

├─ {mysqld} (10746)

...

├─ {mysqld} (10773)

└─ {mysqld} (10774)

We open a new MYSQL thread

[root@hadoop1 kernels] # pstree-p 10735

Mysqld (10735) ─┬─ {mysqld} (10745)

├─ {mysqld} (10746)

...

├─ {mysqld} (10774)

└─ {mysqld} (10794)

So we find our newly opened session thread ID 10794.

3. STRACE thread 10794

Strace-T-p 10794

Then open a table in a new session, and what I open here is

Select count (*) from tstr

Process 10794 attached-interrupt to quit

.

Open (". / test/tstr.ibd", O_RDONLY) = 39

Getcwd ("/ mysql/data"..., 4096) = 12

Lstat ("/ mysql/data/test", {st_mode=S_IFDIR | 0700, st_size=4096,...}) = 0

Lseek (39, 0, SEEK_END) = 98304

Pread (39, "* z\ 322 _\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 1\ 313\ 332\ 7\ 342\ 0\ 0\ 0\ 0\ 0"..., 16384, 0) = 16384

Close (39) = 0

Open (". / test/tstr.ibd", O_RDWR) = 39

Fcntl (39, F_SETLK, {type=F_WRLCK, whence=SEEK_SET, start=0, len=0}) = 0

.

Let's analyze the key steps here.

Open (". / test/tstr.ibd", O_RDONLY) = 39 here opens the tstr.ibd read-only mode of the table, and returns the file descriptor 39

Lseek (39, 0, SEEK_END) = 98304 this is the size of the currently open file, which is the initial 96K, because I only have one piece of data

Pread (39, "* z\ 322 _\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 1\ 313\ 332\ 7\ 342\ 0\ 0\ 0\ 0\ 0"..., 16384, 0) = 16384

This line actually reads 16384 bytes of data.

Close (39) = 0 and then closes the file tstr.ibd

Open (". / test/tstr.ibd", O_RDWR) = 39 and then opens the tstr.ibd data file in read-write mode again, returning the file descriptor 39

Fcntl (39, F_SETLK, {type=F_WRLCK, whence=SEEK_SET, start=0, len=0}) = 0

This line sets the property of the tstr.ibd file, and this function is used to change the file property to a property that needs to be written to the lock, and the starting byte is 0. 0.

If we find that LINUX once again opened the file as a write lock after closing it. Let's now see if this file descriptor is included in our thread

4 、

[root@hadoop1 fd] # pwd

/ proc/10794/fd

[root@hadoop1 fd] # ls-lrt | grep tstr.ibd

Lrwx- 1 root root 64 Dec 5 06:36 39-> / mysql/data/test/tstr.ibd

2. What has FLUSH TABLE done?

1. In some cases, FLUSH TABLE will be blocked, such as LOCK TABLES. READ

Secondly, I also get files during table operations, such as during DML (not things), and during table reconstruction, SELECT reads data very slowly.

All operations that do not allow the process to close the file descriptor.

For example, the operation alter table add key (DDL) requires saving the indexing system to a tablespace data file.

For example, the operation delete from (DML) requires that the data be deleted from the tablespace data file.

For example, when SELECT is slow.

It waits for the system call futex (0xbb3f35c, FUTEX_WAIT_PRIVATE, 1, {31535999, 999934000})

2. What FLUSH actually does is:

[root@hadoop1 fd] # strace-T-p 15101

Process 15101 attached-interrupt to quit

....

Close (24) = 0

Close (25) = 0

Close (28) = 0

Close (29) = 0

Close (26) = 0

Close (27) = 0

Close (30) = 0

Close (31) = 0

Close (32) = 0

Close (33) = 0

Close (22) = 0

Close (23) = 0

Close (20) = 0

Close (21) = 0

Close (34) = 0

Close (35) = 0

Close (38) = 0

Lseek (37, 0, SEEK_SET) = 0

Write (37, "\ 376\ 1\ 354:\ 1\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0\ 0, 35) = 35

Fsync (37) = 0

Close (37) = 0

Close (39) = 0

...

Process 15101 detached

Let's observe that all of these operations close the file descriptor, but I find that the files closed here are all MYISAM and CSV files, as follows:

Lrwx- 1 root root 64 Dec 5 08:27 4-> / mysql/data/test/ibdata1

Lrwx- 1 root root 64 Dec 5 08:27 39-> / mysql/data/mysql/general_log.CSV

Lr-x- 1 root root 64 Dec 5 08:27 38-> / mysql/data/mysql/general_log.CSV

Lrwx- 1 root root 64 Dec 5 08:27 37-> / mysql/data/mysql/general_log.CSM

Lrwx- 1 root root 64 Dec 5 08:27 36-> socket: [42669]

Lrwx- 1 root root 64 Dec 5 08:27 35-> / mysql/data/mysql/event.MYD

Lrwx- 1 root root 64 Dec 5 08:27 34-> / mysql/data/mysql/event.MYI

Lrwx- 1 root root 64 Dec 5 08:27 33-> / mysql/data/mysql/servers.MYD

Lrwx- 1 root root 64 Dec 5 08:27 32-> / mysql/data/mysql/servers.MYI

Lrwx- 1 root root 64 Dec 5 08:27 31-> / mysql/data/mysql/procs_priv.MYD

Lrwx- 1 root root 64 Dec 5 08:27 30-> / mysql/data/mysql/procs_priv.MYI

Lrwx- 1 root root 64 Dec 5 08:27 3-> / mysql/data/binlog.index

Lrwx- 1 root root 64 Dec 5 08:27 29-> / mysql/data/mysql/columns_priv.MYD

Lrwx- 1 root root 64 Dec 5 08:27 28-> / mysql/data/mysql/columns_priv.MYI

Lrwx- 1 root root 64 Dec 5 08:27 27-> / mysql/data/mysql/tables_priv.MYD

Lrwx- 1 root root 64 Dec 5 08:27 26-> / mysql/data/mysql/tables_priv.MYI

Lrwx- 1 root root 64 Dec 5 08:27 25-> / mysql/data/mysql/proxies_priv.MYD

Lrwx- 1 root root 64 Dec 5 08:27 24-> / mysql/data/mysql/proxies_priv.MYI

Lrwx- 1 root root 64 Dec 5 08:27 23-> / mysql/data/mysql/db.MYD

Lrwx- 1 root root 64 Dec 5 08:27 22-> / mysql/data/mysql/db.MYI

Lrwx- 1 root root 64 Dec 5 08:27 21-> / mysql/data/mysql/user.MYD

Lrwx- 1 root root 64 Dec 5 08:27 20-> / mysql/data/mysql/user.MYI

At the same time, it will

Synchronized data is written to the kernel buffer while FSYNC this file and then closed.

However, for INNODB data files, no file closure was found.

Then we can draw two conclusions depending on it.

1. Flush table closes the file descriptors of MYISAM and CSV (unknown to other storage engines), writes dirty data to the file, and closes the file descriptor

Close the file.

2. Flush table does not actually close the file descriptor in INNODB, nor does it write dirty data, but FLUSH TABLE does

Innodb file operation caused by blocking, blocking waiting for the futex system call. So flush tbale may not be of much use to INNODB.

The level is limited and we still need to learn. If there are any mistakes, please correct them.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.