Application-layer monitoring of communication between one or more database clients and one or more database servers

• US 7,509,330 B2
• Filed: 09/03/2004
• Issued: 03/24/2009
• Est. Priority Date: 09/03/2004
• Status: Expired due to Fees

• Current Assignee: CF DB EZ LLC (SoftBank Group Corp.)
• Original Assignee: Crossroads Systems (Texas) Incorporated (Crossroads Systems, Inc.)
• Inventors: Ewing, David B., Martindale, Rick A.
• Primary Examiner(s): Chace, Christian P.
• Assistant Examiner(s): Syed, Farhan M
• Application Number:
  US10/934,887
• Publication Number:
  US 20060053164A1
• Time in Patent Office: 1,663 Days
• Field of Search: 707/100, 707/101, 707/102, 707/103.Y
• US Class Current: 1/1
• CPC Class Codes: G06F 11/3409 for performance assessment
• CPC Class Codes: G06F 11/3495 for systems
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409
• CPC Class Codes: G06F 2201/80 Database-specific techniques
• CPC Class Codes: G06F 2201/81 Threshold
• CPC Class Codes: G06F 2201/865 Monitoring of software
• CPC Class Codes: G06F 2201/885 Monitoring specific for caches
• CPC Class Codes: H04L 67/125 involving control of end-device applications over a network
• CPC Class Codes: Y10S 707/99931 Database or file accessing
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

One embodiment includes a system for application-layer monitoring of communication between one or more database clients and one or more database servers. The system includes one or more decoders residing at a decoding layer above a network layer. The decoders reside at a first network location between one or more database clients residing at one or more second network locations and one or more database servers residing at one or more third network locations. The decoders receive database messages communicated from the database clients and intended for the database servers and database messages communicated from the database servers and intended for the database clients, decode the database messages, and extract query-language statements from the database messages. The system also includes a monitoring application residing at an application layer above the decoding layer. The monitoring application resides at the first network location. The monitoring application receives query-language statements extracted at the decoders and records observations on the database messages based on the query-language statements extracted at the decoders.

Claims

1. A computer readable storage medium having stored thereon a system for application-layer monitoring of communication between one or more database clients and one or more database servers, the system comprising:

one or more decoders residing at a decoding layer above a network layer, the decoders residing on a database device at a first network location between one or more database clients residing at one or more second network locations distinct from the first network location and one or more database servers residing at one or more third network locations distinct from both the first network location and the one or more second network locations, the decoders on the database device being operable to;

receive database messages communicated over the network from any of the database clients residing at the one or more second network locations and intended for any of the database servers at the one or more third network locations and database messages communicated from the database servers and intended for the database clients;

decode the database messages, wherein decoding the database messages comprises decoding a protocol generated as an output of a database connectivity driver in response to an input by a database application residing at an application layer, wherein decoding the database messages comprises decoding database messages of database implementations different from each other and, wherein the database connectivity driver is utilized by the one or more database clients to communicate with the database server; and

extract query-language statements from the database messages, wherein the query-language statements are created by the database application at one or more of the database clients and provided as input to the database connectivity driver and the database connectivity driver generate the database message as an output based on the query-language statement; and

a monitoring application residing at an application layer above the decoding layer, the monitoring application residing on the database device at the first network location, the monitoring application being operable to receive query-language statements extracted at the decoders and record observations on the database messages based at least in part on the query-language statements extracted at the decoders.

2. The system of claim 1, further comprising a database message interface at the network layer and at the first network location, the database message interface operable, in a passive mode, to receive communication between the database clients and the database servers for communication to the decoders.

3. The system of claim 1, further comprising a database message interface at the network layer and at the first network location, the database message interface operable, in a proxy mode, to receive communication between the database clients and the database servers for communication to the decoders.

4. The system of claim 1, wherein observations on the database messages based at least in part on the query-language statements extracted at the decoders comprise one or more of the following:

subject database instances of the query-language statements;

vendors of database implementations of the subject database instances;

software and versions of the software in the database implementations;

network protocols and versions of the network protocols used to communicate the database messages;

protocol drivers at the database servers, versions of the protocol drivers, and application programming interfaces (APIs) at the database servers;

devices hosting the subject database instances of the query-language statements;

hostnames, Internet Protocol (LP) addresses, Media Access Control (MAC) addresses, and network ports of the database servers;

operating systems (OSs), versions of the OSs, and attributes of the OSs of devices hosting the subject database instances;

protocol drivers at the clients, versions of the protocol drivers, and APIs at the clients;

devices hosting the clients;

application frameworks at the clients;

hostnames, IP addresses, MAC addresses, and network ports of the clients 12;

OSs, versions of the OSs, and attributes of the OSs of the clients 12;

a number of queries communicated from each of the clients to each of one or more database instances; and

a rate at which queries communicated from each of the clients to each of one or more database instances.

5. The system of claim 1, wherein observations on the database messages based at least in part on the query-language statements extracted at the decoders comprise full text of the query-language statements.

6. The system of claim 1, wherein observations on the database messages based at least in part on the query-language statements extracted at the decoders comprise timestamps in the query-language statements.

7. The system of claim 1, wherein observations on the database messages based at least in part on the query-language statements extracted at the decoders comprise times to complete responses to queries from clients.

8. The system of claim 7, wherein an observation comprising a time to complete a response to a query from a client indicates a first portion of the time to complete the response attributable to the client and a second portion of the time to complete the response attributable to a database server.

9. The system of claim 7, wherein observations on the database messages based at least in part on the query-language statements extracted at the decoders comprise one or more average times to complete responses to queries from clients.

10. The system of claim 1, wherein observations on the database messages based at least in part on the query-language statements extracted at the decoders comprise statistics on each of one or more subsets of the query-language statements, the subsets being defined according to user input.

11. The system of claim 1, wherein the monitoring application is further operable to provide user access to recorded observations based at least in part on the query-language statements extracted at the decoders using a graphical user interface (GUI) comprising at least one or more of:

a statistical display of recorded observations;

a historical display of recorded observations; and

a tabular display of recorded observations.

12. The system of claim 1, wherein a single nonblocking control thread drives the decoders and the monitoring application according to message events in an event queue.

13. The system of claim 12, wherein, to drive the decoders, the nonblocking control thread communicates function calls to the decoders comprising buffers pointing to frames of database messages in memory at the first network location without communicating the frames of the database messages to the decoders or assembling the database messages and communicating the database messages to the decoders.

14. The system of claim 1, wherein the decoders comprise at least:

a first decoder operable to decode Distributed Relational Database Architecture (DRDA) database messages;

a second decoder operable to decode Tabular Data Stream (TDS) database messages; and

a third decoder operable to decode OracleNet database messages.

15. The system of claim 1, further comprising a database message interface residing at the network layer and at the first network location, the database message interface being operable to:

receive frames of database messages from the clients and the database servers and write the frames to memory at the first network location for processing at the decoders; and

communicate frames of database messages from memory at the first network location to the clients and the database servers without assembling the frames.

16. The system of claim 1, further comprising a normalization module residing at a query-language layer above the decoding layer, the normalization module being operable to receive query-language statements extracted at the decoders and, before the caching application receives the query-language statements, normalize the query-language statements for processing at the monitoring application.

17. The system of claim 16, wherein normalizing a query-language statement comprises one or more of:

capitalizing all letters in the query-language statement;

reordering terms in the query-language statement according to a predetermined order; and

modifying one or more clauses in the query-language statement according to a predetermined clause equivalency.

18. The system of claim 1, further comprising a parser residing at a query-language layer above the decoding layer, the parser being operable to receive query-language statements extracted at the decoders and, before the caching application receives the query-language statements, parse the query-language statements for processing at the caching application.

19. The system of claim 1, wherein the network layer comprises a Transport Control Protocol over Internet Protocol (TCP/IP) interface or an Interprocess Communication (IPC) interface.

20. The system of claim 1, wherein at least some of the query-language statements are Structured Query Language (SQL) statements.

21. The system of claim 1, wherein the monitoring application is further operable to communicate observations on the database messages to a computer system at a fourth network location for reporting to one or more users.

22. The system of claim 1, wherein the monitoring application is further operable to communicate observations on the database messages to a computer system at a fourth network location operable to initiate particular processes in response to particular observations.

23. The system of claim 22, wherein the computer system is operable to delete particular records in a web cache in response to observations indicating communication of particular database messages comprising particular write messages.

24. A method for application-layer monitoring of communication between one or more database clients and one or more database servers, the method comprising:

at a decoding layer above a network layer on a database device at a first network location between one or more database clients residing at one or more second network locations distinct from the first network location and one or more database servers residing at one or more third network locations distinct from both the first network location and the one or more second network locations;

using one or more decoders on the database device for receiving database messages communicated over the network from any of the database clients residing at the one or more second network locations and intended for the database servers at the one or more third network locations and database messages communicated from the database servers and intended for the database clients;

decoding the database messages, wherein decoding the database messages comprises decoding a protocol generated as an output of a database connectivity driver in response to an input by a database application residing at an application layer, wherein decoding the database messages comprises decoding database messages of database implementations different from each other and, wherein the database connectivity driver is utilized by the one or more database clients to communicate with the database serve; and

extracting query-language statements from the database messages, wherein the query-language statements are created by the database application at one or more of the database clients and provided as input to the database connectivity driver and the database connectivity driver generate the database message as an output based on the query-language statement; and

at an application layer above the decoding layer at the first network location, using a monitoring application on the database device for receiving query-language statements extracted at the decoders and recording observations on the database messages based at least in part on the query-language statements extracted at the decoding layer.

25. The method of claim 24, further comprising, at the network layer at the first network location, in a passive mode, receiving communication between the database clients and the database servers for communication to the decoding layer.

26. The method of claim 24, further comprising, at the network layer at the first network location, in a proxy mode, receiving communication between the database clients and the database servers for communication to the decoding layer.

27. The method of claim 24, wherein observations on the database messages based at least in part on the query-language statements extracted at the decoding layer comprise one or more of the following:

subject database instances of the query-language statements;

vendors of database implementations of the subject database instances;

software and versions of the software in the database implementations;

network protocols and versions of the network protocols used to communicate the database messages;

protocol drivers at the database servers, versions of the protocol drivers, and application programming interfaces (APIs) at the database servers;

devices hosting the subject database instances of the query-language statements;

hostnames, Internet Protocol (IP) addresses, Media Access Control (MAC) addresses, and network ports of the database servers;

operating systems (OSs), versions of the OSs, and attributes of the OSs of devices hosting the subject database instances;

protocol drivers at the clients, versions of the protocol drivers, and APIs at the clients;

devices hosting the clients;

application frameworks at the clients;

hostnames, IP addresses, MAC addresses, and network ports of the clients 12;

OSs, versions of the OSs, and attributes of the OSs of the clients 12;

a number of queries communicated from each of the clients to each of one or more database instances; and

a rate at which queries communicated from each of the clients to each of one or more database instances.

28. The method of claim 24, wherein observations on the database messages based at least in part on the query-language statements extracted at the decoding layer comprise full text of the query-language statements.

29. The method of claim 24, wherein observations on the database messages based at least in part on the query-language statements extracted at the decoding layer comprise timestamps in the query-language statements.

30. The method of claim 24, wherein observations on the database messages based at least in part on the query-language statements extracted at the decoding layer comprise times to complete responses to queries from clients.

31. The method of claim 30, wherein an observation comprising a time to complete a response to a query from a client indicates a first portion of the time to complete the response attributable to the client and a second portion of the time to complete the response attributable to a database server.

32. The method of claim 30, wherein observations on the database messages based at least in part on the query-language statements extracted at the decoding layer comprise one or more average times to complete responses to queries from clients.

33. The method of claim 24, wherein observations on the database messages based at least in part on the query-language statements extracted at the decoding layer comprise statistics on each of one or more subsets of the querylanguage statements, the subsets being defined according to user input.

34. The method of claim 24, further comprising, at the application layer, providing user access to recorded observations based at least in part on the querylanguage statements extracted at the decoding layer using a graphical user interface (GUI) comprising at least one or more of:

a statistical display of recorded observations;

a historical display of recorded observations; and

a tabular display of recorded observations.

35. The method of claim 24, wherein a single nonblocking control thread drives the decoding layer and the application layer according to message events in an event queue.

36. The method of claim 35, wherein, to drive the decoding layer, the nonblocking control thread communicates function calls to the decoding layer comprising buffers pointing to frames of database messages in memory at the first network location without communicating the frames of the database messages to the decoding layer or assembling the database messages and communicating the database messages to the decoding layer.

37. The method of claim 24, wherein decoding the database messages comprises decoding Distributed Relational Database Architecture (DRDA) database messages, Tabular Data Stream (TDS) database messages, and OracleNet database messages.

38. The method of claim 24, further comprising, at the network layer at the first network location:

receiving frames of database messages from the clients and the database servers and writing the frames to memory at the first network location for processing at the decoding layer; and

communicating frames of database messages from memory at the first network location to the clients and the database sewers without assembling the frames.

39. The method of claim 24, further comprising, at a querylanguage layer above the decoding layer, receiving query-language statements extracted at the decoding layer and, before the application layer receives the query-language statements, normalizing the query-language statements for processing at the application layer.

40. The method of claim 39, wherein normalizing a query-language statement comprises one or more of:

capitalizing all letters in the query-language statement;

reordering terms in the query-language statement according to a predetermined order; and

modifying one or more clauses in the query-language statement according to a predetermined clause equivalency.

41. The method of claim 24, further comprising, at a query-language layer above the decoding layer, receiving query-language statements extracted at the decoding layer and, before the application layer receives the query-language statements, parsing the query-language statements for processing at the application layer.

42. The method of claim 24, wherein the network layer comprises a Transport Control Protocol over Internet Protocol (TCP/IP) interface or an Interprocess Communication (IPC) interface.

43. The method of claim 24, wherein at least some of the query-language statements are Structured Query Language (SQL) statements.

44. The method of claim 24, further comprising, at the application layer, communicating observations on the database messages to a computer system at a fourth network location for reporting to one or more users.

45. The method of claim 24, further comprising, at the application layer, communicating observations on the database messages to a computer system at a fourth network location operable to initiate particular processes in response to particular observations.

46. The method of claim 45, wherein the computer system is operable to delete particular records in a web cache in response to observations indicating communication of particular database messages comprising particular write messages.

47. A computer readable storage medium having stored thereon computer-executable instructions for application-layer monitoring of communication between one or more database clients and one or more database sewers, the computer executable instructions configured to:

on a database device at a first network location between one or more database clients residing at one or more second network locations distinct from the first network location and one or more database servers residing at one or more third network locations distinct from both the first network location and the one or more second network locations using one or more decoders on the database device to;

receive database messages communicated over the network from any of the database clients residing at the one or more second network locations and intended for the database servers at the one or more third network locations and database messages communicated from the database servers and intended for the database clients;

decoding the database messages, wherein decoding the database messages comprises decoding a protocol generated as an output of a database connectivity driver in response to an input by a database application residing at an application layer, wherein decoding the database messages comprises decoding database messages of database implementations different from each other and, wherein the database connectivity driver is utilized by the one or more database clients to communicate with the database serve; and

extracting query-language statements from the database messages, wherein the query-language statements are created by the database application at one or more of the database clients and provided as input to the database connectivity driver and the database connectivity driver generate the database message as an output based on the query-language statement; and

at an application layer above the decoding layer at the first network location, using a monitoring application on the database device for receiving query-language statements extracted at the decoders and recording observations on the database messages based at least in part on the query-language statements extracted at the decoding layer.

48. The computer-executable instructions of claim 47, further operable, at the network layer at the first network location, in a passive mode, to receive communication between the database clients and the database servers for communication to the decoding layer.

49. The computer-executable instructions of claim 47, further operable, at the network layer at the first network location, in a proxy mode, to receive communication between the database clients and the database servers for communication to the decoding layer.

50. The computer-executable instructions of claim 47, wherein observations on the database messages based at least in part on the query-language statements extracted at the decoding layer comprise one or more of the following:

subject database instances of the query-language statements;

vendors of database implementations of the subject database instances;

software and versions of the software in the database implementations;

network protocols and versions of the network protocols used to communicate the database messages;

protocol drivers at the database servers, versions of the protocol drivers, and application programming interfaces (APIs) at the database servers;

devices hosting the subject database instances of the query-language statements;

hostnames, Internet Protocol (IP) addresses, Media Access Control (MAC) addresses, and network ports of the database servers;

operating systems (OSs), versions of the OSs, and attributes of the OSs of devices hosting the subject database instances;

protocol drivers at the clients, versions of the protocol drivers, and APIs at the clients;

devices hosting the clients;

application frameworks at the clients;

hostnames, IP addresses, MAC addresses, and network ports of the clients 12;

OSs, versions of the OSs, and attributes of the OSs of the clients 12;

a number of queries communicated from each of the clients to each of one or more database instances; and

a rate at which queries communicated from each of the clients to each of one or more database instances.

51. The computer-executable instructions of claim 47, wherein observations on the database messages based at least in part on the query-language statements extracted at the decoding layer comprise full text of the query-language statements.

52. The computer-executable instructions of claim 47, wherein observations on the database messages based at least in part on the query-language statements extracted at the decoding layer comprise timestamps in the query-language statements.

53. The computer-executable instructions of claim 47, wherein observations on the database messages based at least in part on the query-language statements extracted at the decoding layer comprise times to complete responses to queries from clients.

54. The computer-executable instructions of claim 53, wherein an observation comprising a time to complete a response to a query from a client indicates a first portion of the time to complete the response attributable to the client and a second portion of the time to complete the response attributable to a database sewer.

55. The computer-executable instructions of claim 53, wherein observations on the database messages based at least in part on the query-language statements extracted at the decoding layer comprise one or more average times to complete responses to queries from clients.

56. The computer-executable instructions of claim 47, wherein observations on the database messages based at least in part on the query-language statements extracted at the decoding layer comprise statistics on each of one or more subsets of the query-language statements, the subsets being defined according to user input.

57. The computer-executable instructions of claim 47, further operable, at the application layer, to provide user access to recorded observations based at least in part on the querylanguage statements extracted at the decoding layer using a graphical user interface (GUI) comprising at least one or more of:

a statistical display of recorded observations;

a historical display of recorded observations; and

a tabular display of recorded observations.

58. The computer-executable instructions of claim 47, wherein a single nonblocking control thread drives the decoding layer and the application layer according to message events in an event queue.

59. The computer-executable instructions of claim 58, wherein, to drive the decoding layer, the nonblocking control thread communicates function calls to the decoding layer comprising buffers pointing to frames of database messages in memory at the first network location without communicating the frames of the database messages to the decoding layer or assembling the database messages and communicating the database messages to the decoding layer.

60. The computer-executable instructions of claim 47, operable, at the decoding layer, to decode Distributed Relational Database Architecture (DRDA) database messages, Tabular Data Stream (TDS) database messages, and OracleNet database messages.

61. The computer-executable instructions of claim 47, further operable, at the network layer at the first network location, to:

receive frames of database messages from the clients and the database servers and write the frames to memory at the first network location for processing at the decoding layer; and

communicate frames of database messages from memory at the first network location to the clients and the database servers without assembling the frames.

62. The computer-executable instructions of claim 47, further operable, at a query-language layer above the decoding layer, to receive query-language statements extracted at the decoding layer and, before the application layer receives the query-language statements, normalize the query-language statements for processing at the application layer.

63. The computer-executable instructions of claim 62, wherein normalizing a query-language statement comprises one or more of:

capitalizing all letters in the query-language statement;

reordering terms in the query-language statement according to a predetermined order; and

modifying one or more clauses in the query-language statement according to a predetermined clause equivalency.

64. The computer-executable instructions of claim 47, further operable, at a query-language layer above the decoding layer, to receive query-language statements extracted at the decoding layer and, before the application layer receives the query-language statements, parse the query-language statements for processing at the application layer.

65. The computer-executable instructions of claim 47, wherein the network layer comprises a Transport Control Protocol over Internet Protocol (TCP/IP) interface or an Interprocess Communication (IPC) interface.

66. The computer-executable instructions of claim 47, wherein at least some of the query-language statements are Structured Query Language (SQL) statements.

67. The computer-executable instructions of claim 47, further operable, at the application layer, to communicate observations on the database messages to a computer system at a fourth network location for reporting to one or more users.

68. The computer-executable instructions of claim 47, further operable, at the application layer, to communicate observations on the database messages to a computer system at a fourth network location operable to initiate particular processes in response to particular observations.

69. The computer-executable instructions of claim 68, wherein the computer system is operable to delete particular records in a web cache in response to observations indicating communication of particular database messages comprising particular write messages.