When planning to port my application (Java+Tomcat+MySql) to a cloud hosting, I've found that one important factor to consider is the volume of data transferred to/from the database, as it is limited by cloud providers. I haven’t found a convenient way to estimate this volume based on usage statistics (e.g. number of queries and average row size), neither how to measure current utilization.
Is there any statistics build into MySql I could use or it is better to monitor this from the network perspective, monitoring the data transferred to/from the port used by MySql's.
Thanks in advance
Best Answer
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:
GLOBAL LEVEL
You could fetch these for all sessions (past and present) with this query
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
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 runsSHOW GLOBAL VARIABLES;
SUGGESTION
Since everything connected to mysqld will affect the global
Bytes_received
andBytes_sent
status values, you may want to have your application collect the session-levelBytes_received
andBytes_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.