A process for troubleshooting data accumulation problems in a database with outdated users

"Article Summary "

For software that uses a database to hold a large number of users, the cleanup mechanism for outdated data needs to be carefully designed. Improper design can result in incomplete data deletion or cleanup.

In this paper, the problem of outdated user data accumulation caused by improper parameter configuration of a data cleaning module is analyzed in detail, which provides a useful reference for the analysis and solution of related software problems.

First, the problem description

In a software system, in order to allow different types of users to enjoy the corresponding services, the introduction of a mailbox service level concept, that is, different service levels of users with different permissions. "A penny, a penny", for operators, high service level users charge high tariffs, to provide high-quality services.

In order to maintain the information of different users, a user data table is established in the database, which contains fields such as user numbers (such as mobile phone number), status (such as normal use, deactivation, arrears, etc.), service level (called Cos value), user attributes (A or Class B user), and so on.

In the recent past, the support staff of a commercial ringtones of the software system have fed back a problem: The database has accumulated outdated user data, and many of the data that should have been deleted remains.

Second, the cause of the initial analysis

In this software system, there is an outdated data cleansing module dedicated to cleaning up outdated user data. Since there are a large number of stale user data in the database that have not been deleted, there must be a problem with this module.

We let the on-site support staff return the database that was used and recovered the data in the development team's self-test database. We looked at the user data table and found that a large amount of outdated user data was stored in the database. So what are the characteristics of the data that should be deleted?

We observed some of the outdated user data and found that they had the following two features:

First, a COS value corresponds to a particular number of outdated user data, accounting for the amount of data not cleaned up more than 80%.

Second, most of the user attributes for stale user data that have not been deleted are Class B.

Third, problem positioning

Based on the above analysis, we refer to the code and database scripts to find the cause of the problem.

First, why is there a particular number of expired user data corresponding to a COS value? Is this kind of data very special and does the program not perform a delete operation on it?

We traced the program execution process in detail and found that in the database script, there was a parameter value to control whether to delete outdated user data and how many days the data would expire. The value of this parameter is defined in a COS parameter table. The Cos parameter table is defined as follows:

The name "Cosname" for the Cos parameter that controls the deletion of outdated user data is "Deloverduedata", and "Cosid" corresponds to the service level in the User data table, and "Cosvalue" represents the time threshold for deleting data.

We execute the SQL statement in the database "select cosid, cosvalue from tb_cosparamterwhere cosname = ' deloverduedata '", the result is as follows:

Cosid Cosvalue

1 0

2 30

3 30

4 40

5 50

As you can see, the user data with a service level of 1 corresponds to a time threshold of 0, which means that it is not deleted. And we previously looked at the database, it is true that the Cos value 1 corresponds to the expiration of the user data particularly much. It seems that this cos value is not matched, resulting in the service level corresponding to the outdated user data can not be deleted.

We execute the SQL statement "update tb_cosparamter set cosvalue=" where Cosname = ' deloverduedata ' and cosid = 1"will cosid to 1 When the corresponding Cosvalue value is modified to 30, after restarting the cleanup module, it is found that a large number of outdated data has been deleted, but there are still a small number of user attributes of B expired data still exist, what reason?

We continue to follow the database script and find that a parameter value is used to control which type of user data to delete, which is also defined in the Cos parameter table Tb_cosparamter. The Cos parameter's name "Cosname" is "Delusertype", "Cosid" corresponds to the service level in the User data table, "Cosvalue" is 1 to delete only Class A users, and 0 for a or b users to delete.

We execute the SQL statement in the database "select cosid, cosvalue from tb_cosparamterwhere cosname = ' Delusertype '", the result is as follows:

Cosid Cosvalue

1 1

2 1

3 1

4 1

5 1

As you can see, each service level corresponds to a COS value of 1, which means that only class A users are deleted. This is consistent with what we have observed. It appears that because this COS value is not well-equipped, the expired data for user attribute B cannot be deleted.

We execute the SQL statement "update tb_cosparamter set cosvalue= 0 where cosname = ' Delusertype '" will cosname to "Delusertype" The corresponding Cosvalue value is modified to 0, after restarting the cleanup module, it is found that the user attribute B's expired data has been deleted, the database no longer have redundant data.

Iv. Summary

During the troubleshooting of outdated user data accumulation, we first find out the cause of the problem by analyzing the problem data and then locate the problem by tracking the program flow.

Through this troubleshooting, we have summed up the following several experience:

First, encounter procedural problems, we do not panic, you can first look at the phenomenon of the problem on the surface and speculate on the cause of the problem, and then based on the previous analysis in-depth study of the process flow, to find the crux of the problem.

Second, for the important data in the database, people should have regular maintenance, and make corresponding records. This prevents certain parameter values from being inconsistent.

Third, be careful when modifying related field values in the database. Before modifying, try to back up the data in the important table, avoid destroying the database and can't recover.

Whether it's a bug in the program or a database problem, our goal is to try to get rid of them. In the process of solving the problem, we have to adopt a flexible approach, and follow the traces of the discovery. This way, no matter what kind of bug can be eliminated.

--------------------------

My public number: ZHOUZXI, please scan the following two-dimensional code:

A process for troubleshooting data accumulation problems in a database with outdated users