Connection Statistics !!!

MySQL & MariaDB Other MySQL® Questions
1

Hello Scott, gmouse,

I have a very small requirement, Please advice me on how can i achieve the same.

As i have stated below, i need to know connection statistics between my web server to my DB server Per day,
and i need the same stats either to be stored on the server or mailed to me on daily basis.

10.222.1.201 – Web Server

10.222.1.218 – Percona MySQL server

Is there any such script that i can use to get my connection stats?

Please suggest.

Thank you

2

What kind of stats do you need? You can see connections and aborted connections through the standard “show global status” output:

Aborted_connects


Connections

If you flush the statuses to start with (FLUSH STATUS), you can then get the numbers to start, and then pull them every 24 hours or whatever and flush the statuses after each time so that you get the amount since you last checked. Keep in mind that doing this would flush all statuses though, so make sure you or nobody else is trying to keep track of on-going status variables.

As for how to implement it, you could do a simple cron task that would query the database for those status variables and have cron email the results to you.
3

Hello Scott,

All i need is to know,

a ) How many connections does the web server make in an hour and in a day

b ) What time of the day had the maximum connections

c ) The above report to be mailed to me on daily basis.

Is this doable ?

Thank you

4

Why don’t you use continuous monitoring solution, like Ganglia or Cacti for example. Instead of crafting some scripts that would send you reports, you can have much better insight in what’s going on in the system, not only in terms of connections but with a lot more details. And by having graphs you would see how the load distributes over time with good resolution.
You can start with this simple script for Ganglia: [url]http://vuksan.com/linux/ganglia/#mySQL_server_stats[/url]
Or if you want much more detailed graphing system for your MySQL server, I would recommend Cacti with MySQL templates from Percona:
[url]http://www.percona.com/doc/percona-monitoring-plugins/[/url]

5

Hello przemek,

To use either of them i will have to setup another server, and that is not viable for us at the moment :frowning:

Hence, If i could just get those variables in small file by email, would suffice for us.

Thank you for your suggestions.

6

When you start mixing in questions like what time of day it happened, you are getting more towards a real monitoring solution like przemek mentioned. Doing that in a script would be quite a bit more involved than what you are likely looking for.

However if you still want to do it by hand, you could create a small table to hold the data (i.e. just an ID column, the date/time, and the number of connections), and have the cron task add a record to the table each hour or whatever interval you want, and then either query the data yourself from the table or have another cron task query the table at the end of the day and send you the results.