KR20100132752A - Distributed data processing system - Google Patents

Abstract

PURPOSE: A distribution processing system of query data for improving service performance through database distribution is provided to reduce database resource usage by distributing database traffic through a middleware system having relatively low use amount. CONSTITUTION: An SQL(Structured Query Language) mapper(224) maps a local database SQL through a real database SQL, generates the local database SQL which is replaced with the real database SQL, and manages the execution of the local database SQL. An SQL analyzer(225) inform a manager of the SQL which requires the long execution time, and analyzes the real database SQL by sentence. A data sync scheduler(226) performs data synchronization according to the synchronization cycle of the real database SQL.

Description

Query data distribution processing system for improving service performance through database distribution {DISTRIBUTED DATA PROCESSING SYSTEM}

The present invention relates to a query data distribution processing system for improving service performance through database distribution. In particular, the present invention relates to a query data distribution processing system for eliminating service bottlenecks through DB distribution, maintaining optimal system performance, and minimizing cost, time, and personnel for system maintenance.

In general, a database stores related data. The data stored in the database should be updated, inserted, and deleted with the latest data whenever necessary. For this purpose, a database is managed by a software system called a database management system (DBMS), which is a mediator of applications and data, which puts data used by multiple applications in a single space so that all applications can share data. Software that collects, maintains, and manages. In other words, DBMS is a comprehensive data management system that provides applications with consistent results by searching for and changing the data they want.

These DBMSs use Structured Query Language (SQL) to allow multiple users to search or manage data in the database. SQL is one of the query languages for databases, including data definition words (DDL) and data manipulation words (DML). This SQL is widely used because it is not limited to a specific database management system. It was initially used only on systems that were IBM's relational database, but is now widely used in other databases.

SQL is a representative language based on relational mapping. It is a language for mapping desired output relations from input relations. In 1973, structured queries asrelational expressions (SQUARE) were published in 1973. Mathematical expressions were difficult for beginners to use. Based on this, structured english as query language (SEQUEL) was created at SanJose Laboratories in IBM in 1974. It was further refined and SEQUEL 2 was released in 1976. The system, based on SEQUEL 2, called System R and Oracle, became known as SQL.

SQL has not only simple query capabilities, but also complete data definition and manipulation capabilities. It can be used interactively through various online terminals, or inserted into programs in host languages such as COBOL, PL / I, and C. SQL is device independent and does not make any reference to access paths. It performs operations on tables, which are sets of records rather than individual records. In addition, SQL is a syntax similar to English sentences, so even beginners can use it relatively easily.

FIG. 1 shows a conventional query data distribution processing system having a DBMS. The web service platform such as APACHE or WebLogic is used according to the requests of users 101 using various terminals with Internet access. Through 102, data stored in various database management systems of the database platform 103 are retrieved and provided to the users 101.

However, the increase in data traffic due to large user queries, increased connection volume, new services, and incorrect queries causes bottlenecks between the web service platform and the database platform.

In particular, in case of large portal sites that increase the instantaneous access rate of large-scale Internet portal sites due to the increase of Internet users (users), or have to process the response to a large number of user connections stably, WAS and The bottleneck in the DB section accounts for most of the disorders.

For example, in the case of the music portal, the large capacity of the DB system causes excessive system construction costs and maintenance costs, while the initial DB design concept collapses due to the rapidly changing service requirements of users. Pending phenomenon between WAS and DB occurs due to excessive use of Query.

Therefore, in the case of a page that induces an overload query, a page written in pure HTML is generated through a system or manually generated by HTML at a predetermined time interval, without passing through a DB. However, it only handled, but it requires modification of source code, so it is difficult to apply the workload according to requirement change.

As another example, in the case of CBS, there is almost no user demand, but excessive data traffic is generated when a special situation such as an emergency disaster occurs.

Therefore, in the past, the load of DB data queried in an emergency situation was transferred to the WAS, but it is difficult to apply the workload according to the requirement change due to the source code modification, and there is an unrealistic problem in processing a large amount of data due to the memory load. .

Lastly, in the case of a large-capacity mobile menu system, the displayed image and the image map information set therein are excessive due to the characteristics of the menu system. Even if only a single menu is displayed, excessive DB traffic may occur.

In the past, an excessive amount of image information was changed to XHTML in the menu setting process using an image generator. However, the image generator is excessively increased due to the excessive cost of introducing the image generator and the system construction, and the image generation process is not processed in real time. Induce load and process it as a batch job on a separate server.

The present invention for solving the conventional problems as described above is a system (Web Application Server (Web Application Server) or Web Server (Web Server) or a separate application server that accepts a relatively low user demand and connects to the database) Application Server), which is referred to as 'middleware', to distribute data traffic and increase the utilization of DB resources.

In addition, an object of the present invention is to arrange the file DB in the middleware system to improve the data processing speed without expanding or additional development of new hardware, thereby improving the stability of the data service to the user.

Query data distribution processing system for improving service performance through the DB distribution according to the present invention for achieving the above object, the user who can communicate with the Internet using a medium such as WEB / WAP phone / smart phone / PDA and the like; Middleware system 200 having WEB / WAP service server 210 including subscriber, WEB / WAP server 1, WEB / WAP server 2, WAS 1, WAS2, etc., and databases such as ORACLE, SYBASE, MSSQL, MYSQL, etc. In the query data distribution processing system for improving service performance through a database distribution including a database platform 300 having a management system 310, the middleware system analyzes a set SQL syntax and assigns each distribution condition value to a parameter. Setter creates a table to set, creates a table to be stored in a local file for SQL, and automatically generates a local file query statement. The.

The setter is configured to manage the overall solution operation such as the start and stop of the setter 220, manage the DB connection between the Real Database and the local database, and handle the overall logging of the Setterite solution. The service manager loads the file, and the meta data corresponding to the SQL of the real database is inquired, and the metadata of the real database SQL is generated to generate the local database SQL to store the result of the real database SQL. As a component that creates a database and a table in a local database by analyzing, a local file caching unit in charge of processing to be performed in the local database, real database data is inquired, and the corresponding SQL query processing is performed when inquiring data from the real database. Relay to the database, When the data query is completed, the remote / local data synchronizer relaying the corresponding SQL query processing to the local database, wraps and manages two connections of the real database and the local database, and maps the local database SQL through the real database SQL. It generates a local database SQL to replace the real database SQL, manages the execution of the local database SQL, SQL mapping unit, collects the real database SQL, and takes the time-consuming SQL to the administrator. Includes a SQL analyzer that parses SQL by each syntax, and a data synchronization scheduler that analyzes the synchronization cycle of the set real database SQL, and then runs the data synchronizer to synchronize data between the real database and the local database. That is to be characterized.

The setterlight includes a service manager including a configuration manager, a database connection manager, a log manager, and a service manager, and a database meta data fetcher. A local file caching unit consisting of a local database generator, a local database query generator, and a local database executor, a database data manager, a local database Remote / local data synchronizer consisting of a local database data manager, a local database SQL monotor, and a database SQL monitor, database connection wrapper, and DM query translator. (DB query translat or), SQL DB consisting of local DB query generator and local DB executor, database SQL gathering and database SQL analyzer And a data synchronization scheduler 226 configured as a SQL analyzer, a scheduler analyzer, and a scheduler executer.

In addition, the query data distribution processing system for improving service performance through the database distribution according to the present invention, the database distributed by retrieving the data of the plurality of database management system through the middleware system according to the request of a plurality of users to provide to the data requestor Claims [1] A query data distribution processing system for improving service performance through the following steps, comprising: a first step of requesting SQL setting; A second step of parsing an SQL statement by a setter in the middleware system; Checking whether there is a parameter in the SQL statement; A fourth step of setting each dispersion condition value to a parameter corresponding to the condition clause; And a fifth step of generating a table to be stored in a local file with respect to the SQL and automatically generating a local file query syntax.

Therefore, according to the present invention, the present invention can reduce DB resource usage by distributing DB traffic to a relatively low usage middleware system, and improve processing speed by using a local file DB of a web server system. There is an effect to improve the stability and reliability of the overall service.

In addition, the present invention has the effect of improving the data processing speed of each database management system and the web server by distributing the load (load) of the web server and database management system without the addition of new hardware and additional development. That is, the present invention can reduce the CPU usage of the database management system by 50%.

In addition, the present invention has the effect of maximizing the system performance of the middleware system and the DB server by lowering the usage of the DB server and increasing the usage of the middleware system to a certain number, increasing the utilization rate to 60 to 100% or more in the system configuration. .

FIG. 2 is a block diagram showing an example of a query data distribution processing system for improving service performance through database distribution according to the present invention. FIG. 3 is an internal block diagram of satellites disclosed in FIG. FIG. 5 is a flowchart illustrating an operation state of a query data distribution processing system including a setter, and FIG. 5 is a graph showing a change in system resources according to a change in TPS.

Figure 2 shows an example of a query data distribution processing system for improving service performance through database distribution according to the present invention, the mobile subscriber 120 such as WEB subscriber 110, WAP phone user, smart phone user, PDA user, etc. Subscriber (100) capable of internet communication, including the like; WEB / WAP service server 210 including WEB / WAP server 1, WEB / WAP server 2, WAS 1, WAS2, etc., and analyzing each SQL syntax to set each distributed condition value in a parameter, and local file for SQL A middleware system 200 having a setter 220 for generating a table to be stored in and automatically generating a local file inquiry syntax; And a database platform 300 having a database management system 310 such as ORACLE, SYBASE, MSSQL, MYSQL, and the like.

FIG. 3 is an internal configuration diagram of the satellite virtual machine based on the java virtual machine disclosed in FIG. 2. The setter 220 having the administrator tools is a service manager 221. (service manager) and local file caching part 222 (local file caching part), remote / local data synchronizer 223 (remote-local data synchronizer), SQL mapping part 224 (SQL mapping part), SQL analyzer (225) (SQL analyzer), the data synchronization scheduler 226 (data synchronize scheduler) and the like (administrator tools).

Here, the service manager 221 includes a configuration manager, a database connection manager, a log manager, a service manager, and the like, and starts and terminates the setter 220. It manages overall solution operation and DB connection of Real Database and Local Database. In addition, the service manager 221 handles the overall logging of the Setrite 220 solution and loads the configuration file.

The local file caching unit 222 may include a database meta data fetcher, a local database generator, a local database query generator, a local database executor, and the like. It searches the meta data corresponding to the SQL of the real database and creates a local database SQL to store the result of the real database SQL. In addition, the local file caching unit 222 analyzes the meta data of the real database SQL and creates a database and a table in the local database. The local file caching unit 222 is responsible for processing to be performed in the local database.

The remote / local data synchronizer 223 includes a database data manager, a local database data manager, a local database SQL monotor, and a database SQL monitor. It searches the real database data, relays the SQL query processing to the real database when searching the data from the real database, and relays the SQL query processing to the local database when the data query is completed in the database.

The SQL mapping unit 224 is composed of a database connection wrapper, a DB query translator, a local DB query generator, a local DB executor, and the like. It wraps and manages two connections, real database and local database, and maps local database SQL through real database SQL. In addition, the SQL mapping unit 224 generates a local database SQL to replace the real database SQL, and manages the execution of the local database SQL.

The SQL analyzer 225 is composed of a database SQL gathering, a database SQL analyzer, and the like, collecting real database SQL, and notifying the administrator of SQL that takes a long time to execute. Analyze database SQL by each statement.

The data synchronization scheduler 226 is composed of a scheduler analyzer, a scheduler executer, and the like, and analyzes a synchronization cycle of a set real database SQL, and executes a data synchronizer when a corresponding synchronization cycle is reached. Synchronize the data in the database.

The administrator tool supports local database and real database management system resource utilization monitoring and real-time database transaction analysis. The administrator tool also provides the ability to configure real database SQL and supports various reporting features.

4 is a view illustrating an operation of an example query data distribution processing system including a setter light. In accordance with a request of a plurality of subscribers, a middleware system retrieves data from a plurality of database management systems and provides a subscriber with a database distribution. A query data distribution processing system for improving service performance, comprising: a first step of requesting an SQL setting, a second step of parsing an SQL syntax by a setter in the middleware system, and checking whether a parameter exists in the SQL statement A third step, a fourth step of setting each distributed condition value in a parameter corresponding to a conditional clause, and a fifth step of generating a table to be stored in a local file for the SQL and automatically generating a local file query syntax.

5 is a graph showing a change in system resources according to TPS changes, and the CPU usage of the database management system is reduced by about 50%, so that the loads of the web service server and the database management system are distributed to each database management system and the web service. The processing speed of the server is improved, the usage of the WAS and DB server are maximized by lowering the usage of the DB server and increasing the usage of the WAS to a certain number.

In the above-described embodiment of the present invention, the setterlight is installed in the web service platform among the middleware systems as an exemplary embodiment, but the present invention is not limited thereto, and a new business service, a medium / large capacity service, and a plurality of low-capacity Setterlight can be installed in any system that induces an overload of the database management system, such as services such as service, communicator service, blog & SNS service, and digital content service.

Although the present invention has been described in detail with reference to the accompanying drawings, the present invention is not limited to such a specific structure. Those skilled in the art will be able to variously modify or change the embodiments of the present invention without departing from the spirit and scope of the present invention as set forth in the claims below.

1 is a block diagram showing a conventional query data processing system.

2 is a block diagram showing a query data distribution processing system for improving service performance through DB distribution according to the present invention.

FIG. 3 is a diagram illustrating an internal configuration of the satellite described in FIG. 2.

4 is a flow chart showing an operating state of an example query data distribution processing system including a satellite.

5 is a graph showing changes in system resources according to TPS change.

-Explanation of symbols for the main parts of the drawings

100: subscriber 110: WEB subscriber

120: Mobile Subscriber 200: Middleware System

210: WEB / WAP Service Server 220: Setter Lite

221: service manager 222: local file caching unit

223: remote-local data synchronizer 224: SQL mapping unit

225: SQL Analyzer 226: Data Synchronization Scheduler

300: database management system

Claims (4)

WEB / WAP service server including users and subscribers capable of internet communication using a medium such as WEB / WAP phone / smart phone / PDA, WEB / WAP server 1, WEB / WAP server 2, WAS 1, WAS2, etc. Query data distribution processing for service performance improvement through database distribution including a middleware system 200 having a) and a database platform 300 having a database management system 310 such as ORACLE, SYBASE, MSSQL, MYSQL, etc. In the system, The middleware system 200 is equipped with a setter 220 that analyzes the set SQL syntax, sets each distribution condition value in a parameter, generates a table to be stored in a local file for SQL, and automatically generates a local file query syntax. Query data distribution processing system for improving service performance through the database distribution, characterized in that the). The method of claim 1, The setter 220 manages the overall solution operation such as the start and end of the setter 220 and manages the DB connection between the real database and the local database, and the overall logging of the setter 220 solution ( Service Manager 221 which loads configuration file that handles logging, and Meta Data corresponding to SQL of real database are inquired and local database to store execution result of real database SQL. Real Database that generates SQL Analyzes the metadata of SQL and creates the database and tables in the local database. The local file caching unit 222 in charge of processing to be performed in the local database and the real database data are inquired. When retrieving data from the real database, it relays the SQL query processing to the real database. When the data query is completed in the database, the remote / local data synchronizer 223 relays the corresponding SQL query processing to the local database, and the two connections between the real database and the local database are wrapped and managed through real database SQL. Maps local database SQL, creates local database SQL to replace real database SQL, manages execution of local database SQL, and SQL mapping unit 224, and collects real database SQL and takes time to execute SQL. SQL analyzer 225 which analyzes the real database SQL by each syntax, and analyzes the synchronization cycle of the set real database SQL, and executes the data synchronizer when the synchronization cycle is reached. To keep the data in sync Emitter query distributed data processing system for improved service performance through a distributed database comprising the synchronization scheduler (226). The method according to claim 1 or 2, The setter 220 includes a service manager 221 including a configuration manager, a database connection manager, a log manager, and a service manager, and database metadata. A local file caching unit 222 comprising a database meta data fetcher, a local database generator, a local database query generator, and a local database executor; Remote / local data synchronizer 223 consisting of a database data manager, a local database data manager, a local database SQL monotor, and a database SQL monitor. , Database connection wrapper, DM query translation SQL mapping unit 224 consisting of a DB query translator, a local DB query generator, and a local DB executor, database SQL gathering, and database SQL analyzer Service performance through database distribution, comprising a SQL analyzer 225 configured as a database SQL analyzer and a data synchronization scheduler 226 configured as a scheduler analyzer and a scheduler executer. Query data distribution processing system for improvement. In the query data distribution processing system for improving the service performance through the database distribution that retrieves the data of a plurality of database management system through the middleware system according to the request of a plurality of subscribers to provide to the subscriber, A first step of requesting SQL setting; A second step of parsing an SQL statement by a setter embedded in the middleware system; Checking whether there is a parameter in the SQL statement; A fourth step of setting each dispersion condition value to a parameter corresponding to the condition clause; And And a fifth step of generating a table to be stored in a local file with respect to the SQL, and automatically generating a local file query statement.

Images