7

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

5

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

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.

  • Thanks @RolandoMySQLDB. It looks like having replication will make this stats pretty much useless in my case. – pablochacin Aug 13 '13 at 9:48
  • That is true for global status. You may want to think about having all user sessions collect its individual byte traffic into a common table and tally them on demand. – RolandoMySQLDBA Aug 13 '13 at 12:35
  • I am going to add my previous comment to my answer. – RolandoMySQLDBA Aug 13 '13 at 12:48
0

The easiest way would be to fire up the MySQL Workbench. Though it can take some small effort to properly set up the server administration features, it presents graphs for network utilization and is an all-around excellent tool for managing MySQL instances. A web-based solution to MySQL administration is phpMyAdmin, which also provides MySQL statistics. However, be advised that phpMyAdmin has had several major security flaws in the past (allowing total takeover of a system, for example), so I strongly recommend that you use the MySQL Workbench instead. (Tangentially: if you really want or need to use phpMyAdmin, make sure you have an authentication scheme in front of phpMyAdmin, so for malevolents to even see your phpMyAdmin login they must get through your first login layer. My servers, for instance, require Basic authentication over HTTPS and check the provided username and password against PAM before allowing access to even the phpMyAdmin login page.)

If you're also interested in general statistics collection with lots of pretty graphs, Munin is a good solution, and it also includes a collection of MySQL monitoring plugins, though they must first be configured and there are some Perl dependencies to be installed to make the MySQL monitoring plugins work (Cache::Cache and DBD::MySQL, according to the comments left in the plugin scripts).

Finally, remember that being billed for MySQL traffic is only an issue if your application and database reside on different servers; localhost traffic not metered (or billed, therefore).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.