Fun with Performance Schema

I'm using a very small MariaDB instance as a datastore for my YouLess energy monitor, my own mail server (postfix, roundcube). It's a virtual machine from a commercial VPS provider.

All data fits in memory and the overhead of running with performance_schema on is not an issue.

While I was reading a blog post about performance_schema by Mark Leith I wanted to see what P_S could tell me about my own server.

This is the output from the first query:

mysql> select * from file_summary_by_event_name order by count_read desc,count_write desc limit 10;
+--------------------------------------+------------+-------------+--------------------------+---------------------------+
| EVENT_NAME                           | COUNT_READ | COUNT_WRITE | SUM_NUMBER_OF_BYTES_READ | SUM_NUMBER_OF_BYTES_WRITE |
+--------------------------------------+------------+-------------+--------------------------+---------------------------+
| wait/io/file/sql/FRM                 |      25387 |         548 |                  8795589 |                    117014 |
| wait/io/file/myisam/dfile            |      20102 |         760 |                 35053140 |                     32720 |
| wait/io/file/aria/MAI                |      17392 |      208795 |                  7163684 |                   7756451 |
| wait/io/file/aria/MAD                |       8696 |           0 |                 71237632 |                         0 |
| wait/io/file/sql/binlog              |       6624 |       41964 |                 54217518 |                  58900518 |
| wait/io/file/myisam/kfile            |       2666 |         196 |                   619930 |                      6817 |
| wait/io/file/sql/file_parser         |       2435 |           8 |                  4360082 |                     14128 |
| wait/io/file/innodb/innodb_data_file |       1054 |      243380 |                 17063936 |                7016056832 |
| wait/io/file/innodb/innodb_log_file  |          6 |      220950 |                    69632 |                 799742976 |
| wait/io/file/innodb/innodb_temp_file |          6 |           6 |                  6291456 |                   6291456 |
+--------------------------------------+------------+-------------+--------------------------+---------------------------+
10 rows in set (0.00 sec)

I'm using InnoDB for most (if not any) table. The data fits in memory so it shouldn't need to read a lot from disk.

The second result is for wait/io/file/myisam/dfile. I know that the tables in the 'mysql' database use MyISAM. By why is is so high in the list? Did I accidentally create some MyISAM tables?

The second query:

> select * from file_summary_by_instance order by count_read desc,count_write desc limit 5;
+----------------------------------------------------------------------+---------------------------+------------+-------------+--------------------------+---------------------------+
| FILE_NAME                                                            | EVENT_NAME                | COUNT_READ | COUNT_WRITE | SUM_NUMBER_OF_BYTES_READ | SUM_NUMBER_OF_BYTES_WRITE |
+----------------------------------------------------------------------+---------------------------+------------+-------------+--------------------------+---------------------------+
| /opt/mariadb-5.5.XX-linux-x86_64/data/mysql/proc.MYD                 | wait/io/file/myisam/dfile |      19411 |           4 |                  3207486 |                      2244 |
| /opt/mariadb-5.5.XX-linux-x86_64/data/serverX-bin.000022             | wait/io/file/sql/binlog   |       6618 |       41739 |                 54184750 |                  58809577 |
| /opt/mariadb-5.5.XX-linux-x86_64/data/XXXXXXX/table1.frm             | wait/io/file/sql/FRM      |        630 |          19 |                    81268 |                      4971 |
| /opt/mariadb-5.5.XX-linux-x86_64/data/XXXXXXX/view1.frm              | wait/io/file/sql/FRM      |        618 |           0 |                   375532 |                         0 |
| /opt/mariadb-5.5.XX-linux-x86_64/data/XXXXXXX/view2.frm              | wait/io/file/sql/FRM      |        617 |           0 |                   433493 |                         0 |
+----------------------------------------------------------------------+---------------------------+------------+-------------+--------------------------+---------------------------+
5 rows in set (0.00 sec)

So the mysql.proc table is the one that's the issue. I do use some stored procedures for reporting so that's easy to explain.

As we all know Linux takes care of this by eating RAM.

So performance_schema can easily give detailed information about your server. I wouldn't have guessed that stored procedures were in the top 10 read I/O.

This shows some opportunity for improving MySQL on lightly loaded and idle servers. Is that really needed? Yes, it could make MySQL more enery efficient by not waking sleeping disks and leaving the CPU(s) in a sleep state. Drizzle has shown us that it's possible to get rid of the FRM's. If I have some time I might do some tests with Intel's PowerTOP.

I did also run the queries for write I/O, but that goes to InnoDB datafiles, logfiles, binlogs, etc. and is all how you expect them to be.