During performance testing, monitoring your database is an important measure to potentially uncover any bottlenecks. Feel free to refer to our article on A High-Level Insight into Performance Monitoring for more information on server monitoring. The below article explores the most common methods to monitor the types of databases that are normally hosted on servers.
- Oracle DB
- Microsoft SQL Server
- IBM DB2
- MySQL
- PostgreSQL
Oracle DB
Oracle database server information can be captured from the Oracle V$ tables, in particular Session statistics, V$SESSTAT and system statistics, V$SYSSTAT. Note, to monitor these, your user account will need ‘read’ permission on those tables. The following views are also available in Oracle.
For further information regarding the above, refer to the following web page.
A good performance testing tool will normally have a built-in Oracle DB monitor which will access these tables and will run specific queries at regular intervals. Since these are essentially SQL queries you can easily build up a shell script or write a small program to retrieve that data if your chosen performance test tool cannot do this. The typical counters that you can collect are:
There are also guidelines and tips for the use of STATSPACK and TKPROF which can assist with monitoring Oracle DB performance. For more information follow the link Oracle DB performance.
Microsoft SQL Server
SQL Server uses Perfmon to expose its performance data. If you connect to a machine with SQL Server on it using Perfmon, you will see that a category named “SQL Server” has appeared. From then on it is the same as monitoring the Windows OS metrics refer to our article Monitoring Operating Systems. LoadRunner’s SQL Server monitor is actually just a front-end to Perfmon.
Typical counters to collect are:
IBM DB2 UDB (Universal Database)
Several performance testing tools have built-in monitors for IBM DB2 UDB. They all rely on the same principle as for Oracle DB or MS SQL Server. The database itself keeps the performance metrics in certain tables, which can be queried.
Oracle’s Enterprise Manager also has a DB2 add-in which allows for monitoring. In all cases, a log file is produced and will have to be manually correlated and imported. When running on Windows, the DBA has the option of enabling the DB2 Perfmon Provider. This is simply a DLL (DB2PERF.DLL) which creates an interface between DB2 and Perfmon. You can then simply connect to it like you would to any other Perfmon source. A good reference can be found if you follow the link here. When monitoring DB2, a DBA will normally provide you with performance data rather than you having to retrieve it yourself.
MySQL
MySQL is the most popular open-source database out there. The most accessible monitoring method with MySQL is to use the mysqladmin command-line utility. The syntax would be “mysqladmin extended ”
The typical counters to monitor are:
To use the above you will need to write a shell script to retrieve the values and log them to a file.
PostgreSQL
PostgreSQL, also known as Postgres, is a another free and open-source relational database management system. It was originally named POSTGRES, referring to its origins as a successor to the Ingres database which was developed at the University of California, Berkeley. In 1996, the project was renamed to PostgreSQL to reflect its support for SQL. After a review in 2007, the development team decided to keep the name PostgreSQL and the alias Postgres.
Several tools are available for monitoring PostgreSQL database activity and analysing performance. Refer to the following site for more information here. Note, if you’ve identified a poorly-performing query, further investigation might be needed by using PostgreSQL’s EXPLAIN command.
In summary, there are numerous methods to monitor databases while performance testing.
To find out how SQA Consulting can assist you with your performance monitoring needs contact us.
