How to estimate network data transfer volume for a mysql database

If you want to directly measure the network traffic in and out of MySQL in your application, there are two status variables you can monitor:

• Bytes_received : The number of bytes received from all clients
• Bytes_sent : The number of bytes sent to all clients

GLOBAL LEVEL

You could fetch these for all sessions (past and present) with this query

SELECT * FROM information_schema.global_status
WHERE variable_name IN ('Bytes_received','Bytes_sent');

This will give you the number of each for the lifetime of the mysql instance.

SESSION LEVEL

You could fetch these for each session with

SELECT * FROM information_schema.session_status
WHERE variable_name IN ('Bytes_received','Bytes_sent');

This will give you the number of each for the lifetime of the DB session.

You could use GUIs to graph them over time. Since you mentioned Java and Tomcat, you may wish to create your own presentation. You would have to keep the previous values, get the current values, subtract them, and then plot or display averages as you wish.

Give it a Try !!!

CAVEAT

I discussed this in the DBA StackExchange a long time ago (MySQL : Does 'bytes_sent' and 'bytes_received' include mysqldump data?) Anything that has a thread will be counted in these two variables, including internals like that for MySQL Replication (via the IO Thread and SQL thread).

In light of this, make sure you do not have unnecessary DB Connections doing reads and that you do not monitor MySQL during a mysqldump or a reload of a mysqldump. Even monitoring software (such as Nagios, Zabbix, MONYog, EM7, etc) will generate Bytes_received traffic each time it runs SHOW GLOBAL VARIABLES;

SUGGESTION

Since everything connected to mysqld will affect the global Bytes_received and Bytes_sent status values, you may want to have your application collect the session-level Bytes_received and Bytes_sent just before running mysqli_close(). Then, you can generate a report of those numbers. You could then subtract those totals from the global counts to see how much housekeeping data is being requested.