openark forge

Open source utilities
 

Querying for data

When directly querying for data, one must choose the right time resolution. Data can be viewed in raw format (sv_diff view), in per-sample, per-hour and per-day (sv_* views) metrics, or in modified, calculated format, (sv_report_* views).

For example, looking at sampled com_select values:

mysql> SELECT ts, com_select FROM sv_sample LIMIT 5;
+---------------------+------------+
| ts                  | com_select |
+---------------------+------------+
| 2009-10-22 20:55:01 |  853202052 |
| 2009-10-22 21:00:01 |  853248581 |
| 2009-10-22 21:05:01 |  853292775 |
| 2009-10-22 21:10:01 |  853330226 |
| 2009-10-22 21:15:01 |  853379080 |
+---------------------+------------+

Looking at the differences in com_select values:

mysql> SELECT ts, com_select_diff FROM sv_sample LIMIT 5;
+---------------------+-----------------+
| ts                  | com_select_diff |
+---------------------+-----------------+
| 2009-10-22 20:55:01 |           29300 |
| 2009-10-22 21:00:01 |           46529 |
| 2009-10-22 21:05:01 |           44194 |
| 2009-10-22 21:10:01 |           37451 |
| 2009-10-22 21:15:01 |           48854 |
+---------------------+-----------------+

Looking at average com_select per second on an hourly aggregation:

mysql> SELECT ts, com_select_psec FROM sv_hour LIMIT 5;
+---------------------+-----------------+
| ts                  | com_select_psec |
+---------------------+-----------------+
| 2009-10-22 20:00:00 |           97.67 |
| 2009-10-22 21:00:00 |          127.81 |
| 2009-10-22 22:00:00 |          132.62 |
| 2009-10-22 23:00:00 |          134.92 |
| 2009-10-23 00:00:00 |           87.99 |
+---------------------+-----------------+

Measuring the percentage of com_select queries out of all questions:

mysql> SELECT ts, ROUND(100*com_select_diff/questions_diff, 1) AS com_select_percent
       FROM sv_hour LIMIT 5;
+---------------------+--------------------+
| ts                  | com_select_percent |
+---------------------+--------------------+
| 2009-10-22 20:00:00 |               28.0 |
| 2009-10-22 21:00:00 |               28.3 |
| 2009-10-22 22:00:00 |               28.6 |
| 2009-10-22 23:00:00 |               29.7 |
| 2009-10-23 00:00:00 |               16.8 |
+---------------------+--------------------+

Comparing status variables and server variables

Some status values only make sense when compared with server variables. Since these are all stored together, it’s very easily done:

mysql> SELECT ts, ROUND(100 - 100*(key_blocks_unused * key_cache_block_size)/key_buffer_size, 1)
         AS key_buffer_used_percent
       FROM sv_hour LIMIT 5;
+---------------------+-------------------------+
| ts                  | key_buffer_used_percent |
+---------------------+-------------------------+
| 2009-10-22 20:00:00 |                    18.2 |
| 2009-10-22 21:00:00 |                    18.2 |
| 2009-10-22 22:00:00 |                    18.2 |
| 2009-10-22 23:00:00 |                    18.2 |
| 2009-10-23 00:00:00 |                    18.2 |
+---------------------+-------------------------+

The above identical values indicate little or no MyISAM activity. Indeed, the diagnosed database is InnoDB only, with just the system tables being MyISAM.

MySQL 5.0 and 5.1 have different tabel cache implementations. Still, look at how easy it is to check for the tabel cache use percent:

mysql> SELECT ts, ROUND(100*open_tables/(IFNULL(table_cache, 0) + IFNULL(table_open_cache, 0)), 1)
         AS table_cache_use_percent
       FROM sv_hour ORDER BY id DESC LIMIT 5;
+---------------------+-------------------------+
| ts                  | table_cache_use_percent |
+---------------------+-------------------------+
| 2009-11-09 11:00:00 |                    12.5 |
| 2009-11-09 10:00:00 |                    12.2 |
| 2009-11-09 09:00:00 |                    11.1 |
| 2009-11-09 08:00:00 |                    10.8 |
| 2009-11-09 07:00:00 |                    10.3 |
+---------------------+-------------------------+

We’ve now looked at last 5 sampled hours.

Pre-measured metrics

The above examples are so common and important that the sv_report_* views provide them built in:

mysql> SELECT com_select_percent FROM sv_report_hour LIMIT 5;
+--------------------+
| com_select_percent |
+--------------------+
|               28.0 |
|               28.3 |
|               28.6 |
|               29.7 |
|               16.8 |
+--------------------+

mysql> SELECT key_buffer_used_percent FROM sv_report_hour ORDER BY id DESC LIMIT 5;
+-------------------------+
| key_buffer_used_percent |
+-------------------------+
|                    18.2 |
|                    18.2 |
|                    18.2 |
|                    18.2 |
|                    18.2 |
+-------------------------+

mysql> SELECT table_cache_use_percent FROM sv_report_hour ORDER BY id DESC LIMIT 5;
+-------------------------+
| table_cache_use_percent |
+-------------------------+
|                    12.5 |
|                    12.2 |
|                    11.1 |
|                    10.8 |
|                    10.3 |
+-------------------------+

Selecting by conditions

And, of course, one can SELECT by range of dates, look for particular hour in the night, search for rows where some values were considerable higher than average, what have you!

mysql> SELECT ts, com_select_psec
       FROM sv_hour
       WHERE
         ts BETWEEN '2009-10-25' AND '2009-10-29'
         AND com_select_psec > (SELECT 2*AVG(com_select_psec) FROM sv_hour);
+---------------------+-----------------+
| ts                  | com_select_psec |
+---------------------+-----------------+
| 2009-10-26 00:00:00 |          212.83 |
| 2009-10-26 01:00:00 |          214.27 |
+---------------------+-----------------+
 
Powered by Wordpress and MySQL. Theme by openark.org