Suggestions and replies

With my suggestion, others started to give their feedback. There were few interesting and informative feedbacks as listed below.

Sanjeewa Malalgoda: I was looking into this subject some times back and found some points. AFAIU only number of transactions is not enough .found some interesting tools like dbtuna[1] and jetprofiler[2] I hope we can have a look at them and get some idea. I have tested jetprofiler and it gives

lot of information related the db usage.

[1]http://www.dbtuna.com/

[2]http://www.jetprofiler.com/

I went through them and got to this below conclusion which I made as my reply.

“It is always good to know a person who has worked in the same area. I went through those 2 tools and they mainly target the management and administrative aspects of the db server. It gives us nice graphical representations about existing data. This can be very useful when understanding the usage patterns of users. But this do not give us any new informations, this only presents the date found in the information_schema, logs. As you have used it you might know more about it, so correct me if I am wrong.”

Jet Profiler

1. Install Java 1.6 separately
2. Unzip jetprofiler_v2.0.5.zip to the desired folder (e.g. /usr/local/bin/jetprofiler or /home/USER/bin/jetprofiler).
3. Run ./jetprofiler

Need to find I/O rates, bandwidth used by each Database in the MySQL server.

So where I went yesterday seems to be misleading. I thought I have to measure the bandwidth used by the requests coming into the WSO2 Data Service Server. However what I have to do was, I have to measure the facets about queries happen in between web service end and the database end. First I thought If I calculate(simply sum) the length fields of the SOAP requests it would be enough. I got to know that they(WSO2) already measuring that. What I have to do is, when SOAP is read and DB queries are made I have to measure those connections.

MySQL log files

I thought there can be interesting information in MySQL log files so I searched on that. Search gave me this resource( http://dev.mysql.com/doc/refman/5.6/en/server-logs.html ), where it talks about different types of server logs like general log, error log, slow query log. After all I found them no interest to my problem.

Server Status Variables

My next stop was server status variables (http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html). This gives us lot of valuable information about the server and those are very related to measuring server usage. Nevertheless our main problem of finding the usage by a individual database is still no solved.

Limiting the number of connections, size, number of queries

Being exhausted with finding a way to measure size and I/O rates, I stared searching of a way to limit those parameters. Lucky me, I have found something, even it is not what I wanted. So I am thinking about a billing system where we will not measure but we will limit the usage of the user. User can select a plan that suits him. As a example he can select a plan giving 20BG of space + 1M queries per hour.

How to Limit Size: http://lrem.net/software/mysql-quota-daemon.xhtml

How to limit other parameters like no_of_connections, no_of_queries_per_hour, etc.:http://dev.mysql.com/doc/refman/5.5/en/grant.html