database table Design--table design optimization of backup records

##================================================================##

Requirements Scenario:

Because MySQL does not have a system table like SQL Server to hold backup records, a large-scale MySQL server needs to be centrally managed and viewed.

When a server has a performance problem or replication delay, it needs to determine whether it is caused by a data backup.

##================================================================##

First edition

According to the requirements, considering the backup information that needs to be logged, such as backup server information, backup start-up time, backup success, and so on, the first version of the table is designed:

Create TableFull_backup_log (IDbigintAuto_incrementPrimary Key, # # self-increment primary key, business-independent host_ipvarchar( -), # # Backup machine IP host_portint, # # Backup machine Port Backup_typevarchar( -), # # Backup type, mysqldump and Xtrabackup start_timedatetime, # # Backup start time End_timedatetime, # # Backup end time Is_successint, # # is the backup successful Backup_messagevarchar( the), # # Backup message check_timedatetime# #写入或更新记录的时间);

##================================================================##

Second Edition

The backup_message is made larger, mainly in the backup process to write some information in, but think about it, the table can not be very good to record every step of the backup process, put all the information into the Backup_message column is not conducive to viewing, and then add a detail table:

Create TableFull_backup_log_detail (IDbigintAuto_incrementPrimary Key, # # self-increment primary key, business-independent full_backup_log_idbigint, # #关联full_backup_log表主键 host_ipvarchar( -), # # Backup machine IP host_portint, # # Backup machine Port Backup_typevarchar( -), # # Backup type, mysqldump and Xtrabackup backup_messagevarchar( the), # # Backup message check_timedatetime# #写入或更新记录的时间);

Although the Full_backup_log table contains backup and backup type data, it can be obtained through the FULL_BACKUP_LOG_ID Association, but considering the Full_backup_log_detail table data log data, the write will not change. So by redundancy to reduce the association, only query Full_backup_log_detail can see the backup details of a server.

##================================================================##

Third edition

Usually the DBA cares about the last successful time of each database backup, and the is_success field in table Full_backup_log is used to identify the success of the backup, which can be obtained through the following SQL:

SelectT1.*  fromFull_backup_log asT1Inner Join (SelectHost_ip,host_port,Max(ID) asmax_id fromFull_backup_logwhereIs_success=1Group  byhost_ip,host_port) asT2 onT1.id=t2.max_id

If the Full_backup_log table data is large, such as storing thousands of instances of data for several years, the table of data millions of tens of thousands of time, the above query even if the appropriate index is not efficient execution.

Since the DBA does not care about the earlier data, it can be implemented through data close, but if you occasionally query earlier data, you need the current table and the history table to Union, the implementation of the program to determine whether the data is carried forward, and then add a table to hold the last successful backup record:

# # Full_backup_info The record used to hold the last successful backup of the backup machineCreate TableFull_backup_info (IDbigintAuto_incrementPrimary Key, # # self-increment primary key, business-independent host_ipvarchar( -), # # Backup machine IP host_portint, # # Backup machine Port Backup_typevarchar( -), # # Backup type, mysqldump and Xtrabackup start_timedatetime, # # Backup start time End_timedatetime, # # Backup end timeBackup_messagevarchar( the), # # Backup message check_timedatetime# #写入或更新记录的时间);

The same data is easy to reduce the table association, although the last successful backup record certainly corresponds to the backup record in the Full_backup_log table, but because the saved data is all redundant, there is no need to add fields and tables in table Full_backup_info Full_backup_ Log to associate

##================================================================##

Fourth edition

When the backup process uses excessive CPU and IO resources to cause performance problems and alarms, the DBA needs to first determine if the alarm server is in the process of being backed up and needs to see which servers are being backed up:

Method 1: Use the start_time and end_time of the Full_backup_log table to get the server that is currently being backed up, you need to index the end_time, and if end_time defaults to null, where end_time is null Or End_time >now, performance can easily be affected by or, consider setting a default value for end_time such as 2199-01-01 What, change the query to where End_time >now

Method 2: The is_success column extension in the Full_backup_log table identifies the backup state, and if 1 indicates success 0 means failure-1 means backup is being backed up, the query condition is where is_success=-1, the is_success column needs to be indexed, but is _success column selectivity is too low, and MySQL does not support filtering indexes, so it is easy to generate an inefficient execution plan.

Workaround:

Create a new table that is dedicated to the server records you are backing up so that you can only query the table to get a list of all the servers that are being backed up and delete the table records immediately after the backup succeeds.

# # full_backup_in_process is used to store the server information being backed upCreate Tablefull_backup_in_process (IDbigintAuto_incrementPrimary Key, # # self-increment primary key, business-independent host_ipvarchar( -), # # Backup machine IP host_portint, # # Backup machine Port Backup_typevarchar( -), # # Backup type, mysqldump and Xtrabackup start_timedatetime, # # Backup start time Check_timedatetime# #写入或更新记录的时间);

##================================================================##

Summarize:

Some of the research and development colleagues in the design, with the changes in demand constantly modify the table, by adding fields on the original table to solve the new requirements, resulting in too many tables, the same table to deal with different requirements, or through the implementation of complex SQL, forcing DBAs to optimize SQL or create a bunch of inefficient indexes, and reputation for "business requirements." But many requirements can actually be curve-handled, often optimizing business requirements and optimizing implementations to ultimately address performance issues.

Once a research and development colleague to help it optimize SQL, found that its business needs are tens of millions of of data to sort the page and then take the top, a few 10 seconds can not return the results, it is recommended to remove the sorting, is told that some of the data needs to be prioritized, and this part of the need to prioritize processing of very few, The final solution is to split the priority data out of a separate program for processing, and the normal processing of other general data-not-sorted queries, perfect solution.

##================================================================##

database table Design--table design optimization of backup records