how to calculate database transaction count per second and database growth

Question

Hello ,

I need to learn about calculating database load of a project . Lets assume that the events below are firing when an insert is completed.

1. Insert a new record to table1
2. Get the uniqueid from inserted and insert the uniqueid to another table , with some other parameters
3. table1 is a cumulative dataware , so select count(1) from table1 where anotherColumn = xxyyx command is fired
4. After count result received , update another table with this count

I need to calculate how many transactions are opened and what is the cpu load of database when 100 or 1000 times this scenario is fired at the same time.

Thanks...

Answer 1

By your question I understand that you wish to obtain a statistics in regards of the number of transactions per seconds (TPS). This is a standard statistic data about the capacity of your computer infrastructure and your database servers. If I understand correctly, this mathematic formula could perhaps help.

TPS = Number of transactions / Sum(Each transaction time in seconds)

This will allow you to know how much transactions per seconds peeked your server in the time elapsed (DateTime interval period)

On the other hand, if you wish to calculte the very server load considering the number of users and then simulate it, you might want to look for specialized loadTest tools.

Let me know if I answer well your question, or let me know what I miss within your question.

EDIT 1 This link might help: Open source performance testing tools

EDIT 2 Another well known tool: QaLoad de Microfocus
And for JAVA Applets:Apache JMeter
But you have to make sure that these tools supports all of the features such Web Services, SSL, Kerberos, SOAP, XML, etc. if you require it.

Answer 2

I would highly suggest you get a monitoring tool like Spotlight and simply watch what happens as you send x number of queries through the system.

Taking a Look and See What's Actually Going On approach is going to be definitive whereas any calculation you do could possibily skip some important pieces.

Answer 3

You can monitor the transactions using performance manager. Have a look here at the transaction counters that you can monitor.

You can also use performance manager to monitor the CPU load at the same time.

In terms of how to perform the load testing, you either need to knock up a test harness yourself to load the db server in the appropriate way, or probably best bet is to try a third party tool specifically designed to aid load testing. There are a number of these tools about.

Answer 4

Thanks for the fast replies ; Actually , I am about to manage a big project and I have to say some statistical data about database growth , transaction count per second etc to find the needs of hardware. It is guessed that min 555 or max 2000 insert requests must be done per second to sql server and some kind of scenaria will be fired as in my first post. After modelling the system I can calculate some statistics but I havent needed to do sth like that before and need some guideness...

Thanks...