Nov
20
2014
--

Microsoft Promises Managed, Web-Based App Stores For The Enterprise With Windows 10

ereer Microsoft previously announced that Windows 10, its next operating system, will contain a single app store that will work across device classes. Don’t make the Lord of the Rings joke. The company also previously promised that organizations will be able to curate their own app experience for their users. Today, it announced that a “Web-based” store will be an option for… Read More

Nov
20
2014
--

Sys Schema for MySQL 5.6 and MySQL 5.7

Performance Schema (P_S) has been available since MySQL 5.5, more than 4 years ago. It is still difficult to see production servers with P_S enabled, even with MySQL 5.6 where the feature is enabled by default. There have been several complaints like the overhead, that the fix is a work in progress, and the ease of use. 52 tables to query and 31 configuration variables is enough to scare people.

There is a solution for the second problem, the usability. It’s name is “ title="sys schema" rel="nofollow" href="https://github.com/MarkLeith/mysql-sys/" rel="nofollow">sys schema“. It is a collection of views, functions and procedures to make P_S more user friendly.

Installation

If you are a MySQL Workbench user the installation is pretty easy because sys schema is already included. You just need to install it. Click on “Performance – Performance Reports” and there you will find the “Install Helper” button that will install sys schema.

href="http://www.percona.com/blog/wp-content/uploads/2014/11/workbench_sys.jpg"> class="aligncenter size-full wp-image-26915" src="http://www.percona.com/blog/wp-content/uploads/2014/11/workbench_sys.jpg" alt="sys schema mysql workbench" width="600" height="451" />

If you don’t use MySQL Workbench you need to download sys_56.sql or sys_57.sql (depends if you use 5.6 or 5.7) from the github repository. Then, just import the sql file as usual:

mysql -u root -p < ./sys_56.sql

Usage

After the import, you will have a new “sys” schema with some very descriptive table names. Let’s see an example. Do you want to know what tables are using most of our InnoDB buffer memory? Easy:

mysql> select * from sys.innodb_buffer_stats_by_table;
+---------------+--------------------+-----------+-----------+-------+--------------+-----------+-------------+
| object_schema | object_name        | allocated | data      | pages | pages_hashed | pages_old | rows_cached |
+---------------+--------------------+-----------+-----------+-------+--------------+-----------+-------------+
| test          | t                  | 63.61 MiB | 58.06 MiB |  4071 |         4071 |      4071 |     2101222 |
| InnoDB System | SYS_FOREIGN        | 32.00 KiB | 0 bytes   |     2 |            2 |         2 |           0 |
| InnoDB System | SYS_TABLES         | 32.00 KiB | 1.11 KiB  |     2 |            2 |         2 |          10 |
| InnoDB System | SYS_COLUMNS        | 16.00 KiB | 4.68 KiB  |     1 |            1 |         1 |          71 |
| InnoDB System | SYS_DATAFILES      | 16.00 KiB | 324 bytes |     1 |            1 |         1 |           6 |
| InnoDB System | SYS_FIELDS         | 16.00 KiB | 722 bytes |     1 |            1 |         1 |          17 |
| InnoDB System | SYS_INDEXES        | 16.00 KiB | 836 bytes |     1 |            1 |         1 |          12 |
| InnoDB System | SYS_TABLESPACES    | 16.00 KiB | 318 bytes |     1 |            1 |         1 |           6 |
| mysql         | innodb_index_stats | 16.00 KiB | 274 bytes |     1 |            1 |         1 |           3 |
| mysql         | innodb_table_stats | 16.00 KiB | 53 bytes  |     1 |            1 |         1 |           1 |
+---------------+--------------------+-----------+-----------+-------+--------------+-----------+-------------+

Pretty easy and useful, right? You can also get what is the database using more memory in the buffer pool querying innodb_buffer_stats_by_schema.

For each table there is another similar ones that it’s name starts with x$. For example, you have user_summary_by_file_io and x$user_summary_by_file_io. The difference is that the first table has a human readable output values while the second one has the real values. Let’s see an example:

mysql> select * from sys.user_summary_by_file_io;
+------------+-------+------------+
| user       | ios   | io_latency |
+------------+-------+------------+
| root       | 19514 | 2.87 s     |
| background |  5916 | 1.91 s     |
+------------+-------+------------+
2 rows in set (0.00 sec)
mysql> select * from sys.x$user_summary_by_file_io;
+------------+-------+---------------+
| user       | ios   | io_latency    |
+------------+-------+---------------+
| root       | 19514 | 2871847094292 |
| background |  5916 | 1905079715132 |
+------------+-------+---------------+

For humans, at least for me, it is easier to read seconds rather than picoseconds src="http://www.percona.com/blog/wp-includes/images/smilies/icon_smile.gif" alt=":)" class="wp-smiley" />

There are multiple tables with very descriptive names.

– io_by_thread_by_latency /> – schema_unused_indexes /> – statements_with_temp_tables /> – statements_with_errors_or_warnings /> – user_summary_by_statement_type /> – waits_by_host_by_latency /> …

There are lot more, and they are explained with examples in project’s title="sys readme" rel="nofollow" href="https://github.com/MarkLeith/mysql-sys/blob/master/README.md" rel="nofollow">README file.

Configuration

On the MySQL side nothing special is needed. Just enable performance_schema:

performance_schema="on"

sys schema also provides some procedures to enable/disable some features. For example:

– ps_setup_enable_background_threads /> – ps_setup_enable_consumers /> – ps_setup_enable_instrument /> and so on…

We also have the same procedures with “disable”. After you have made the changes you can save them calling ps_setup_save() and reload it later on if you want calling ps_setup_reload_saved(). If you want to reset the configuration to default values just call ps_setup_reset_to_default().

For example, we can check that some consumers are disabled. It is possible to enable them and save the configuration:

mysql> CALL sys.ps_setup_show_disabled_consumers();
+--------------------------------+
| disabled_consumers             |
+--------------------------------+
| events_stages_current          |
| events_stages_history          |
| events_stages_history_long     |
| events_statements_history      |
| events_statements_history_long |
| events_waits_current           |
| events_waits_history           |
| events_waits_history_long      |
+--------------------------------+
mysql> CALL sys.ps_setup_enable_consumers('events');
+---------------------+
| summary             |
+---------------------+
| Enabled 8 consumers |
+---------------------+
mysql> CALL sys.ps_setup_show_disabled_consumers();
Empty set (0.00 sec)
mysql> CALL sys.ps_setup_save(TRUE);

Conclusion

Performance Schema is very useful when we want to know what is happening inside the database. It is getting more features with each new GA and will probably be the single point of information in near future. Now thanks to sys schema it is also easy to use.

The post rel="nofollow" href="http://www.percona.com/blog/2014/11/20/sys-schema-mysql-5-6-5-7/">Sys Schema for MySQL 5.6 and MySQL 5.7 appeared first on rel="nofollow" href="http://www.percona.com/blog">MySQL Performance Blog.

Nov
20
2014
--

Deep Identity Raises $470,000 To Help Companies Manage Access On Their Networks

network Deep Identity, a Singapore-based startup that helps companies manage access to their internal networks and services, has raised US$470,000 (SG$589,000) to expand its services into the cloud and open a presence in Europe. Read More

Nov
19
2014
--

Searching For Truths In Big, Enormous, Massive Data

Big data concept picture with words bid data above a monitor over a map of the world. We have so much data, mountains and mountains of data. In fact, there’s so much, the name ‘Big Data’ doesn’t even do it justice. Maybe we should call it humongous data or at least enormous data. Yet for all this data, it seems we are lost in those piles of data, no closer to the answers that are supposed to solve our most fundamental business and social problems.… Read More

Nov
19
2014
--

How a set of queries can be killed in MySQL using Percona Toolkit’s pt-kill

You might have encountered situations where you had to kill some specific select queries that were running for long periods and choking the database. This post will go into more detail with an example of report query offloading.

Report query (select) offloading to a slave server is a common practice to reduce the workload of the master DB server. The long running selects will be executed in the slave for report generation. I have observed in many cases where the slave used to get delayed or the slave DB encounters a slowdown due to some heavy long-running orphaned selects from some wrong reports.

There are two main ways to kill queries in MySQL: 1. use custom scripts that match on a regular expression, or 2. use a tool written and supported by Percona that is designed to kill queries based on matching conditions. Below is one script that will help you to kill those queries. The script will take the process list from MySQL and filter the long-running select query considering the “User”, “Time” and “State” from the list. However I suggest that you use the  title="pt-kill" href="http://www.percona.com/doc/percona-toolkit/2.1/pt-kill.html" >pt-kill tool from  title="percona toolkit" href="http://www.percona.com/doc/percona-toolkit/2.2/" >Percona Toolkit which provides a more reliable choice and options based on your requirements.

Process list:

+-----+------+-----------+------+---------+------+------------------------------+-----------------------------+
| Id  | User | Host      | db   | Command | Time | State                        | Info                        |
+-----+------+-----------+------+---------+------+------------------------------+-----------------------------+
| 103 | root | localhost | test | Query   |    0 | init                         | show processlist            |
| 104 | root | localhost | test | Sleep   |  383 |                              | NULL                        |
| 106 | root | localhost | test | Query   |  377 | Waiting for table level lock | SELECT * FROM t FOR UPDATE  |
| 107 | root | localhost | test | Query   |  364 | Waiting for table level lock | insert into t value(5)      |
| 108 | rpt  | localhost | test | Query   |  345 | Waiting for table level lock | SELECT c1 FROM t FOR UPDATE |
+-----+------+-----------+------+---------+------+------------------------------+-----------------------------+

1. Shell script example:

List all queries from rpt user having query time greater than 1 minute:
[root@test3 ~]# for id in $(mysql -t -e "SHOW FULL PROCESSLIST" | /bin/awk -F "|" '($6 ~ /Query/) && (60< $7) &&
 ($3 ~ /rpt/) { print $2}'); do echo "KILL QUERY $id;"; done
KILL QUERY 108;
Kill all queries from rpt user having query time greater than 1 minute:
[root@test3 ~]# for id in $(mysql -t -e "SHOW FULL PROCESSLIST" | /bin/awk -F "|" '($6 ~ /Query/) && (60< $7) &&
 ($3 ~ /rpt/)  { print $2}'); do mysql  -e "KILL QUERY $id;"; done

2. title="pt-kill" href="http://www.percona.com/doc/percona-toolkit/2.1/pt-kill.html">Pt-kill example:

List all queries from rpt user having query time greater than 1 minute (–print):

[root@test3 ~]# pt-kill --busy-time 60 --match-command Query --user root --print --match-user rpt
--socket /tmp/mysql.sock
# 2014-11-12T03:51:01 KILL 108 (Query 485 sec) SELECT c1 FROM t FOR UPDATE
# 2014-11-12T03:51:31 KILL 108 (Query 515 sec) SELECT c1 FROM t FOR UPDATE
# 2014-11-12T03:52:01 KILL 108 (Query 545 sec) SELECT c1 FROM t FOR UPDATE

Kill all queries from rpt user having query time greater than 1 minute (–kill-query):

[root@test3 ~]# pt-kill --busy-time 60 --match-command Query --user root --print --kill-query --match-user rpt
--socket /tmp/mysql.sock
# 2014-11-12T03:53:26 KILL QUERY 108 (Query 630 sec) SELECT c1 FROM t FOR UPDATE

Process list:

+-----+------+-----------+------+---------+------+------------------------------+----------------------------+
| Id  | User | Host      | db   | Command | Time | State                        | Info                       |
+-----+------+-----------+------+---------+------+------------------------------+----------------------------+
| 103 | root | localhost | test | Query   |    0 | init                         | show processlist           |
| 104 | root | localhost | test | Sleep   |  843 |                              | NULL                       |
| 106 | root | localhost | test | Query   |  837 | Waiting for table level lock | SELECT * FROM t FOR UPDATE |
| 107 | root | localhost | test | Query   |  824 | Waiting for table level lock | insert into t value(5)     |
| 108 | rpt  | localhost | test | Sleep   |  805 |                              | NULL                       |
| 111 | root | localhost | NULL | Sleep   |   25 |                              | NULL                       |
+-----+------+-----------+------+---------+------+------------------------------+----------------------------+

 Note :  href="http://www.percona.com/doc/percona-toolkit/2.1/pt-kill.html#cmdoption-pt-kill--kill-query">–kill-query option makes class="Apple-converted-space">  href="http://www.percona.com/doc/percona-toolkit/2.1/pt-kill.html"> class="program">pt-kill class="Apple-converted-space"> kill matching queries. This requires MySQL 5.0 or newer. Unlike class="Apple-converted-space">  class="reference internal" href="http://www.percona.com/doc/percona-toolkit/2.2/pt-kill.html#cmdoption-pt-kill--kill">–kill class="Apple-converted-space"> which kills the connection for matching queries, this option only kills the query, not its connection.

title="pt-kill" href="http://www.percona.com/doc/percona-toolkit/2.1/pt-kill.html">pt-kill captures queries from SHOW PROCESSLIST, filters them, and then either kills or prints them. This is also known as a “slow query sniper” in some circles. The idea is to watch for queries that might be consuming too many resources, and kill them. For details please read the tool’s ( title="pt-kill" href="http://www.percona.com/doc/percona-toolkit/2.1/pt-kill.html">pt-kill) documentation.

The post rel="nofollow" href="http://www.percona.com/blog/2014/11/19/how-a-set-of-queries-can-be-killed-in-mysql-using-pt-kill/">How a set of queries can be killed in MySQL using Percona Toolkit’s pt-kill appeared first on rel="nofollow" href="http://www.percona.com/blog">MySQL Performance Blog.

Nov
19
2014
--

Managed By Q, The Platform For Office Management, Closes $1.65M Seed Round

b There are tons of products and services out there that make an office more efficient, from specialized industry software to something as simple as an Amazon Prime membership. But there’s no service that comprehensively handles the maintenance and everyday care of the actual space. That’s where Managed By Q comes in. The startup just raised $1.65 million in seed funding from… Read More

Nov
19
2014
--

CipherCloud Scores $50M As Cloud Security Comes To Forefront

Word "cloud" on stacks of money. CipherCloud landed a bushel of cash today as it announced $50M in Series B funding to continue building its cloud security business. The round was led by Transamerica Ventures along with Delta Partners and existing investors Andreessen Horowitz and T-Ventures, the venture capital arm of Deutsche Telekom also pitching in. When I asked CipherCloud executives why they thought they were… Read More

Nov
18
2014
--

OrderWithMe Raises Another $28M To Help Small Businesses Procure Like Big Ones

bike shop OrderWithMe, a startup based out of Las Vegas that has built a platform for small businesses to come together to effectively act like big ones to source, order and pay for supplies and components – has raised another $28 million in funding. The company says the money will be used to build out its operations to new verticals — it has “multiple industry partners lined… Read More

Nov
18
2014
--

Avoiding MySQL ALTER table downtime

MySQL table alterations can interrupt production traffic causing bad customer experience or in worst cases, loss of revenue. Not all DBAs, developers, syadmins know MySQL well enough to avoid this pitfall. DBAs usually encounter these kinds of production interruptions when working with upgrade scripts that touch both application and database or if an inexperienced admin/dev engineer perform the schema change without knowing how MySQL operates internally.

Truths /> * Direct MySQL ALTER table locks for duration of change (pre-5.6) /> * Online DDL in MySQL 5.6 is not always online and may incurr locks /> * Even with href="http://www.percona.com/software/percona-toolkit" >Percona Toolkit‘s pt-online-schema-change there are several workloads that can experience blocking

Here on the href="http://www.percona.com/products/percona-managed-services" >Percona MySQL Managed Services team we encourage our clients to work with us when planning and performing schema migrations. We aim to ensure that we are using the best method available in their given circumstance. Our intentions to avoid blocking when performing DDL on large tables ensures that business can continue as usual whilst we strive to improve response time or add application functionality. The bottom line is that a business relying on access to its data cannot afford to be down during core trading hours.

Many of the installations we manage are still below MySQL 5.6, which requires us to seek workarounds to minimize the amount of disruption a migration can cause. This may entail slave promotion or changing the schema with an ‘online schema change’ tool. MySQL version 5.6 looks to address this issue by reducing the number of scenarios where a table is rebuilt and locked but it doesn’t yet cover all eventualities, for example when changing the data type of a column a full table rebuild is necessary. The topic of 5.6 Online Schema Change was discussed in great detail last year in the post, “ href="http://www.percona.com/blog/2013/07/05/schema-changes-whats-new-in-mysql-5-6/" >Schema changes – what’s new in MySQL 5.6?” by Przemys?aw Malkowski

With new functionality arriving in MySQL 5.7, we look forward to non-blocking DDL operations such as; OPTIMIZE TABLE and RENAME INDEX. ( rel="nofollow" href="http://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html" rel="nofollow">More info)

The best advice for MySQL 5.6 users is to review rel="nofollow" href="http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html" rel="nofollow">the matrix to familiarize with situations where it might be best to look outside of MySQL to perform schema changes, the good news is that we’re on the right path to solving this natively.

Truth be told, a blocking alter is usually going to go unnoticed on a 30MB table and we tend to use a direct alter in this situation, but on a 30GB or 300GB table we have some planning to do. If there is a period of time where activity is low and the this is permissive of locking the table then sometimes it is better execute within this window. Frequently though we are reactive to new SQL statements or a new performance issue and an emergency index is required to reduce load on the master in order to improve the response time.

To pt-osc or not to pt-osc?

As mentioned, pt-online-schema-change is a fixture in our workflow. It’s usually the right way to go but we still have occasions where pt-online-schema-change cannot be used, for example; when a table already uses triggers. It’s an important to remind ourselves of the the steps that pt-online-schema-change traverses to complete it’s job. Lets look at the source code to identify these;

[moore@localhost]$ egrep 'Step' pt-online-schema-change
# Step 1: Create the new table.
# Step 2: Alter the new, empty table. This should be very quick,
# Step 3: Create the triggers to capture changes on the original table and <--(metadata lock)
# Step 4: Copy rows.
# Step 5: Rename tables: orig -> old, new -> orig <--(metadata lock)
# Step 6: Update foreign key constraints if there are child tables.
# Step 7: Drop the old table.

I pick out steps 3 and 5 from above to highlight a source of a source of potential downtime due to locks, but step 6 is also an area for concern since foreign keys can have nested actions and should be considered when planning these actions to avoid related tables from being rebuilt with a direct alter implicitly. There are several ways to approach a table with referential integrity constraints and they are detailed within the pt-osc documentation a good preparation step is to review the structure of your table including the constraints and how the ripples of the change can affect the tables around it.

Recently we were alerted to an incident after a client with a highly concurrent and highly transactional workload ran a standard pt-online-schema-change script over a large table. This appeared normal to them and a few hours later our pager man was notified that this client was experiencing max_connections limit reached. So what was going on? When pt-online-schema-change reached step 5 it tried to acquire a metadata lock to rename the the original and the shadow table, however this wasn’t immediately granted due to open transactions and thus threads began to queue behind the RENAME command. The actual effect this had on the client’s application was downtime. No new connections could be made and all existing threads were waiting behind the RENAME command.

Metadata locks /> Introduced in 5.5.3 at server level. When a transaction starts it will acquire a metadata lock (independent of storage engine) on all tables it uses and then releases them when it’s finished it’s work. This ensures that nothing can alter the table definition whilst a transaction is open.

With some foresight and planning we can avoid these situations with non-default pt-osc options, namely –nodrop-new-table and –no-swap-tables. This combination leaves both the shadow table and the triggers inplace so that we can instigate an atomic RENAME when load permits.

EDIT: as of percona-toolkit version 2.2 we have a new variable –tries which in conjunction with –set-vars has been deployed to cover this scenario where various pt-osc operations could block waiting for a metadata lock. The default behaviour of pt-osc (–set-vars) is to set the following session variables when it connects to the server;

wait_timeout=10000 /> innodb_lock_wait_timeout=1 /> lock_wait_timeout=60

when using –tries we can granularly identify the operation, try count and the wait interval between tries. This combination will ensure that pt-osc will kill it’s own waiting session in good time to avoid the thread pileup and provide us with a loop to attempt to acquire our metadata lock for triggers|rename|fk management;

–tries swap_tables:5:0.5,drop_triggers:5:0.5

The documentation is here href="http://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html#cmdoption-pt-online-schema-change--tries">http://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html#cmdoption-pt-online-schema-change–tries

This illustrates that even with a tool like pt-online-schema-change it is important to understand the caveats presented with the solution you think is most adequate. To help decide the direction to take use the flow chart to ensure you’re taking into account some of the caveats of the MySQL schema change. Be sure to read up on the recommended outcome though as there are uncharted areas such as disk space, IO load that are not featured on the diagram.

id="attachment_26954" style="width: 310px" class="wp-caption aligncenter"> href="http://www.percona.com/blog/wp-content/uploads/2014/11/DDLFlow1.png"> class="wp-image-26954 size-medium" src="http://www.percona.com/blog/wp-content/uploads/2014/11/DDLFlow1-300x296.png" alt="DDL Decision chart" width="300" height="296" /> class="wp-caption-text">Choosing the right DDL option

Ensure you know what effect ALTER TABLE will have on your platform and pick the right method to suit your uptime. Sometimes that means delaying the change until a period of lighter use or utilising a tool that will avoid holding a table locked for the duration of the operation. A direct ALTER is sometimes the answer like when you have triggers installed on a table.

– In most cases pt-osc is exactly what we need /> – In many cases pt-osc is needed but the way in which it’s used needs tweaking /> – In few cases pt-osc isn’t the right tool/method and we need to consider native blocking ALTER or using failovers to juggle the change into place on all hosts in the replica cluster.

If you want to learn more about avoiding avoidable downtime please tune into my webinar Wednesday, November 19 at 10 a.m. PST. It’s titled “Tips from the Trenches: A Guide to Preventing Downtime for the Over-Extended DBA.” href="http://www.percona.com/resources/mysql-webinars/tips-trenches-guide-preventing-downtime-over-extended-dba" >Register now! (If you miss it, don’t worry: Catch the recording and download the slides from that same href="http://www.percona.com/resources/mysql-webinars/tips-trenches-guide-preventing-downtime-over-extended-dba" >registration page.)

The post rel="nofollow" href="http://www.percona.com/blog/2014/11/18/avoiding-mysql-alter-table-downtime/">Avoiding MySQL ALTER table downtime appeared first on rel="nofollow" href="http://www.percona.com/blog">MySQL Performance Blog.

Nov
18
2014
--

MySQL’s INNODB_METRICS table: How much is the overhead?

Starting with MySQL 5.6 there is an rel="nofollow" href="http://dev.mysql.com/doc/refman/5.6/en/innodb-metrics-table.html" rel="nofollow">INNODB_METRICS table available in INFORMATION_SCHEMA which contains some additional information than provided in the SHOW GLOBAL STATUS output – yet might be more lightweight than PERFORMANCE_SCHEMA.

Too bad INNODB_METRICS was designed during the Oracle-Sun split under MySQL leadership and so it covers only InnoDB counters. I think this would be a great replacement to all counters that are currently provided though SHOW STATUS – it captures more information such as providing MIN/MAX counts for variables as well as providing the type of the counter (whenever it is current or commutative) as well as human readable comment – describing what such counter means.

The examples of data you can get only from the INNODB_METRICS table includes information about InnoDB Page Splits and merging (which can cause quite an impact to the database performance).

href="http://www.percona.com/blog/wp-content/uploads/2014/11/img_54692c0556d11.png" > class="alignnone wp-image-26924" src="http://www.percona.com/blog/wp-content/uploads/2014/11/img_54692c0556d11.png" alt="" width="629" height="279" />

As well as details of InnoDB purging performance, adaptive hash index activity, details about InnoDB flushing or how index condition pushdown (ICP) is working for you.

The InnoDB Metrics come disabled by default as of MySQL 5.6 and it provides very elaborate configuration commands – you can enable/disable individual counters or counters for specific subsystems you’re interested in. I would expect most users though would need only basic configuration:

set global innodb_monitor_disable = all;
set global innodb_monitor_enable = all;

…which enables and disables all InnoDB Metrics appropriately. Of course if you just rather keep changes permanently you would want to keep it as a setting in the MySQL Configuration file. Small side note – some of the InnoDB metrics correspond to SHOW STATUS counters and those are permanently enabled.

As those metrics are disabled by default I was wondering if they really do have so huge a overhead that we can’t keep them enabled. In my tests I’ve measured less than 1% overhead, really the variance between runs of benchmark was larger than caused by having metrics enabled. It might be on very large systems with heavy workloads that the overhead might be higher – if you observe any significant overhead from using INNODB_METRICS please comment on this post so we know.

Finally let me post a selection of metrics that have been actively running a simple sysbench test, in total there are 214 metrics as of the current MySQL 5.6 release.

mysql> select name,count,round(avg_count,3) avg_cnt ,type,comment from innodb_metrics where count>0 or max_count>0;
+------------------------------------------+---------------+--------------+----------------+-------------------------------------------------------------------------------------------------------------------+
| name                                     | count         | avg_cnt      | type           | comment                                                                                                           |
+------------------------------------------+---------------+--------------+----------------+-------------------------------------------------------------------------------------------------------------------+
| metadata_table_handles_opened            |             1 |        0.000 | counter        | Number of table handles opened                                                                                    |
| metadata_table_reference_count           |            -6 |       -0.000 | counter        | Table reference counter                                                                                           |
| metadata_mem_pool_size                   |       8388608 |         NULL | value          | Size of a memory pool InnoDB uses to store data dictionary and internal data structures in bytes                  |
| lock_deadlocks                           |           521 |        0.002 | counter        | Number of deadlocks                                                                                               |
| lock_timeouts                            |          6857 |        0.027 | counter        | Number of lock timeouts                                                                                           |
| lock_rec_lock_waits                      |       1837441 |       16.150 | counter        | Number of times enqueued into record lock wait queue                                                              |
| lock_rec_lock_requests                   |      65270862 |      573.694 | counter        | Number of record locks requested                                                                                  |
| lock_rec_lock_created                    |      36872834 |      324.091 | counter        | Number of record locks created                                                                                    |
| lock_rec_lock_removed                    |      36872830 |      324.091 | counter        | Number of record locks removed from the lock queue                                                                |
| lock_rec_locks                           |             4 |        0.000 | counter        | Current number of record locks on tables                                                                          |
| lock_table_lock_created                  |      12239169 |      107.575 | counter        | Number of table locks created                                                                                     |
| lock_table_lock_removed                  |      12239165 |      107.575 | counter        | Number of table locks removed from the lock queue                                                                 |
| lock_table_locks                         |             4 |        0.000 | counter        | Current number of table locks on tables                                                                           |
| lock_row_lock_current_waits              |            55 |        0.000 | status_counter | Number of row locks currently being waited for (innodb_row_lock_current_waits)                                    |
| lock_row_lock_time                       |    1936523774 |     7548.123 | status_counter | Time spent in acquiring row locks, in milliseconds (innodb_row_lock_time)                                         |
| lock_row_lock_time_max                   |         51969 |         NULL | value          | The maximum time to acquire a row lock, in milliseconds (innodb_row_lock_time_max)                                |
| lock_row_lock_waits                      |       1901237 |        7.411 | status_counter | Number of times a row lock had to be waited for (innodb_row_lock_waits)                                           |
| lock_row_lock_time_avg                   |          1018 |         NULL | value          | The average time to acquire a row lock, in milliseconds (innodb_row_lock_time_avg)                                |
| buffer_pool_size                         |    6442450944 |         NULL | value          | Server buffer pool size (all buffer pools) in bytes                                                               |
| buffer_pool_reads                        |     370556494 |     1444.344 | status_counter | Number of reads directly from disk (innodb_buffer_pool_reads)                                                     |
| buffer_pool_read_requests                |   48016336694 |   187156.603 | status_counter | Number of logical read requests (innodb_buffer_pool_read_requests)                                                |
| buffer_pool_write_requests               |    4273025649 |    16655.268 | status_counter | Number of write requests (innodb_buffer_pool_write_requests)                                                      |
| buffer_pool_read_ahead                   |       6461109 |       25.184 | status_counter | Number of pages read as read ahead (innodb_buffer_pool_read_ahead)                                                |
| buffer_pool_pages_total                  |        393208 |         NULL | value          | Total buffer pool size in pages (innodb_buffer_pool_pages_total)                                                  |
| buffer_pool_pages_misc                   |            13 |         NULL | value          | Buffer pages for misc use such as row locks or the adaptive hash index (innodb_buffer_pool_pages_misc)            |
| buffer_pool_pages_data                   |        387844 |         NULL | value          | Buffer pages containing data (innodb_buffer_pool_pages_data)                                                      |
| buffer_pool_bytes_data                   |    6354436096 |         NULL | value          | Buffer bytes containing data (innodb_buffer_pool_bytes_data)                                                      |
| buffer_pool_pages_dirty                  |          2646 |         NULL | value          | Buffer pages currently dirty (innodb_buffer_pool_pages_dirty)                                                     |
| buffer_pool_bytes_dirty                  |      43352064 |         NULL | value          | Buffer bytes currently dirty (innodb_buffer_pool_bytes_dirty)                                                     |
| buffer_pool_pages_free                   |          5351 |         NULL | value          | Buffer pages currently free (innodb_buffer_pool_pages_free)                                                       |
| buffer_pages_created                     |      15712031 |       61.242 | status_counter | Number of pages created (innodb_pages_created)                                                                    |
| buffer_pages_written                     |      88472873 |      344.847 | status_counter | Number of pages written (innodb_pages_written)                                                                    |
| buffer_pages_read                        |     389195724 |     1516.995 | status_counter | Number of pages read (innodb_pages_read)                                                                          |
| buffer_data_reads                        | 6370189115392 | 24829527.611 | status_counter | Amount of data read in bytes (innodb_data_reads)                                                                  |
| buffer_data_written                      | 3238687916032 | 12623658.353 | status_counter | Amount of data written in bytes (innodb_data_written)                                                             |
| buffer_flush_batch_scanned               |        303528 |        0.000 | set_owner      | Total pages scanned as part of flush batch                                                                        |
| buffer_flush_batch_num_scan              |       1066763 |        9.376 | set_member     | Number of times buffer flush list flush is called                                                                 |
| buffer_flush_batch_scanned_per_call      |             0 |        0.000 | set_member     | Pages scanned per flush batch scan                                                                                |
| buffer_flush_batch_rescan                |            89 |        0.001 | counter        | Number of times rescan of flush list forced                                                                       |
| buffer_flush_batch_total_pages           |        634250 |        3.000 | set_owner      | Total pages flushed as part of flush batch                                                                        |
| buffer_flush_batches                     |        197859 |        1.739 | set_member     | Number of flush batches                                                                                           |
| buffer_flush_batch_pages                 |             1 |        0.000 | set_member     | Pages queued as a flush batch                                                                                     |
| buffer_flush_neighbor_total_pages        |       6051959 |        0.000 | set_owner      | Total neighbors flushed as part of neighbor flush                                                                 |
| buffer_flush_neighbor                    |      34246946 |      301.011 | set_member     | Number of times neighbors flushing is invoked                                                                     |
| buffer_flush_neighbor_pages              |             0 |        0.000 | set_member     | Pages queued as a neighbor batch                                                                                  |
| buffer_flush_n_to_flush_requested        |             0 |        0.000 | counter        | Number of pages requested for flushing.                                                                           |
| buffer_flush_avg_page_rate               |             0 |        0.000 | counter        | Average number of pages at which flushing is happening                                                            |
| buffer_flush_lsn_avg_rate                |         16570 |        0.146 | counter        | Average redo generation rate                                                                                      |
| buffer_flush_pct_for_lsn                 |             0 |        0.000 | counter        | Percent of IO capacity used to avoid reusable redo space limit                                                    |
| buffer_flush_adaptive_total_pages        |        631237 |       11.000 | set_owner      | Total pages flushed as part of adaptive flushing                                                                  |
| buffer_flush_adaptive                    |         56061 |        0.493 | set_member     | Number of adaptive batches                                                                                        |
| buffer_flush_adaptive_pages              |             1 |        0.000 | set_member     | Pages queued as an adaptive batch                                                                                 |
| buffer_flush_background_total_pages      |      39668544 |      420.000 | set_owner      | Total pages flushed as part of background batches                                                                 |
| buffer_flush_background                  |         94284 |        0.829 | set_member     | Number of background batches                                                                                      |
| buffer_flush_background_pages            |            26 |        0.000 | set_member     | Pages queued as a background batch                                                                                |
| buffer_LRU_batch_scanned                 |    5664563354 |     3153.000 | set_owner      | Total pages scanned as part of LRU batch                                                                          |
| buffer_LRU_batch_num_scan                |       1796523 |       15.790 | set_member     | Number of times LRU batch is called                                                                               |
| buffer_LRU_batch_scanned_per_call        |          2748 |        0.024 | set_member     | Pages scanned per LRU batch call                                                                                  |
| buffer_LRU_batch_total_pages             |      39665531 |      420.000 | set_owner      | Total pages flushed as part of LRU batches                                                                        |
| buffer_LRU_batches                       |         94281 |        0.829 | set_member     | Number of LRU batches                                                                                             |
| buffer_LRU_batch_pages                   |            26 |        0.000 | set_member     | Pages queued as an LRU batch                                                                                      |
| buffer_LRU_get_free_search               |     378687232 |     3328.446 | counter        | Number of searches performed for a clean page                                                                     |
| os_data_reads                            |     389095626 |     1516.605 | status_counter | Number of reads initiated (innodb_data_reads)                                                                     |
| os_data_writes                           |      93493818 |      364.417 | status_counter | Number of writes initiated (innodb_data_writes)                                                                   |
| os_data_fsyncs                           |       7023487 |       27.376 | status_counter | Number of fsync() calls (innodb_data_fsyncs)                                                                      |
| os_log_bytes_written                     |  339601504256 |  1323688.320 | status_counter | Bytes of log written (innodb_os_log_written)                                                                      |
| os_log_fsyncs                            |       2181429 |        8.503 | status_counter | Number of fsync log writes (innodb_os_log_fsyncs)                                                                 |
| os_log_pending_fsyncs                    |             0 |        0.000 | status_counter | Number of pending fsync write (innodb_os_log_pending_fsyncs)                                                      |
| os_log_pending_writes                    |             0 |        0.000 | status_counter | Number of pending log file writes (innodb_os_log_pending_writes)                                                  |
| trx_rw_commits                           |      12239165 |      107.575 | counter        | Number of read-write transactions committed                                                                       |
| trx_commits_insert_update                |      12223766 |      107.440 | counter        | Number of transactions committed with inserts and updates                                                         |
| trx_rollbacks                            |          2073 |        0.018 | counter        | Number of transactions rolled back                                                                                |
| trx_rollbacks_savepoint                  |          6860 |        0.060 | counter        | Number of transactions rolled back to savepoint                                                                   |
| trx_active_transactions                  |        -10769 |       -0.095 | counter        | Number of active transactions                                                                                     |
| trx_rseg_history_len                     |       9469305 |         NULL | value          | Length of the TRX_RSEG_HISTORY list                                                                               |
| trx_undo_slots_used                      |            81 |        0.001 | counter        | Number of undo slots used                                                                                         |
| trx_undo_slots_cached                    |        -16487 |       -0.145 | counter        | Number of undo slots cached                                                                                       |
| trx_rseg_current_size                    |        498385 |         NULL | value          | Current rollback segment size in pages                                                                            |
| purge_del_mark_records                   |       2900462 |       25.493 | counter        | Number of delete-marked rows purged                                                                               |
| purge_upd_exist_or_extern_records        |       2900334 |       25.492 | counter        | Number of purges on updates of existing records and  updates on delete marked record with externally stored field |
| purge_invoked                            |      15036193 |      132.160 | counter        | Number of times purge was invoked                                                                                 |
| purge_undo_log_pages                     |       2901506 |       25.503 | counter        | Number of undo log pages handled by the purge                                                                     |
| log_checkpoints                          |          7880 |        0.069 | counter        | Number of checkpoints                                                                                             |
| log_lsn_last_flush                       |  338495604612 |         NULL | value          | LSN of Last flush                                                                                                 |
| log_lsn_last_checkpoint                  |  337905680629 |         NULL | value          | LSN at last checkpoint                                                                                            |
| log_lsn_current                          |  338495612626 |         NULL | value          | Current LSN value                                                                                                 |
| log_lsn_checkpoint_age                   |     589931997 |     5185.167 | counter        | Current LSN value minus LSN at last checkpoint                                                                    |
| log_lsn_buf_pool_oldest                  |  337905680629 |         NULL | value          | The oldest modified block LSN in the buffer pool                                                                  |
| log_max_modified_age_async               |    6087493925 |         NULL | value          | Maximum LSN difference; when exceeded, start asynchronous preflush                                                |
| log_max_modified_age_sync                |    6522314920 |         NULL | value          | Maximum LSN difference; when exceeded, start synchronous preflush                                                 |
| log_pending_checkpoint_writes            |             0 |        0.000 | counter        | Pending checkpoints                                                                                               |
| log_num_log_io                           |       1618018 |       14.221 | counter        | Number of log I/Os                                                                                                |
| log_write_requests                       |     733043164 |     2857.233 | status_counter | Number of log write requests (innodb_log_write_requests)                                                          |
| log_writes                               |       2160056 |        8.419 | status_counter | Number of log writes (innodb_log_writes)                                                                          |
| index_page_splits                        |        725862 |        6.380 | counter        | Number of index page splits                                                                                       |
| index_page_merge_attempts                |       6318656 |       55.537 | counter        | Number of index page merge attempts                                                                               |
| index_page_merge_successful              |        600659 |        5.279 | counter        | Number of successful index page merges                                                                            |
| index_page_reorg_attempts                |        324359 |        2.851 | counter        | Number of index page reorganization attempts                                                                      |
| index_page_reorg_successful              |        324359 |        2.851 | counter        | Number of successful index page reorganizations                                                                   |
| index_page_discards                      |           850 |        0.007 | counter        | Number of index pages discarded                                                                                   |
| adaptive_hash_searches                   |     996683138 |     3884.841 | status_counter | Number of successful searches using Adaptive Hash Index                                                           |
| adaptive_hash_pages_added                |       6787699 |       59.660 | counter        | Number of index pages on which the Adaptive Hash Index is built                                                   |
| adaptive_hash_pages_removed              |       6165530 |       54.192 | counter        | Number of index pages whose corresponding Adaptive Hash Index entries were removed                                |
| adaptive_hash_rows_added                 |     971809814 |     8541.656 | counter        | Number of Adaptive Hash Index rows added                                                                          |
| adaptive_hash_rows_removed               |     450086501 |     3956.005 | counter        | Number of Adaptive Hash Index rows removed                                                                        |
| adaptive_hash_rows_deleted_no_hash_entry |            59 |        0.001 | counter        | Number of rows deleted that did not have corresponding Adaptive Hash Index entries                                |
| file_num_open_files                      |             4 |         NULL | value          | Number of files currently open (innodb_num_open_files)                                                            |
| ibuf_merges_insert                       |     281620042 |     1097.690 | status_counter | Number of inserted records merged by change buffering                                                             |
| ibuf_merges_delete_mark                  |      21569667 |       84.074 | status_counter | Number of deleted records merged by change buffering                                                              |
| ibuf_merges_delete                       |       3332320 |       12.989 | status_counter | Number of purge records merged by change buffering                                                                |
| ibuf_merges                              |      13938295 |       54.328 | status_counter | Number of change buffer merges                                                                                    |
| ibuf_size                                |         17478 |        0.068 | status_counter | Change buffer size in pages                                                                                       |
| innodb_master_thread_sleeps              |         82021 |        0.721 | counter        | Number of times (seconds) master thread sleeps                                                                    |
| innodb_activity_count                    |     167679087 |      653.574 | status_counter | Current server activity count                                                                                     |
| innodb_master_active_loops               |         82021 |        0.721 | counter        | Number of times master thread performs its tasks when server is active                                            |
| innodb_background_drop_table_usec        |         58451 |        0.514 | counter        | Time (in microseconds) spent to process drop table list                                                           |
| innodb_ibuf_merge_usec                   |    2815277852 |    24744.692 | counter        | Time (in microseconds) spent to process change buffer merge                                                       |
| innodb_log_flush_usec                    |    2843869697 |    24995.998 | counter        | Time (in microseconds) spent to flush log records                                                                 |
| innodb_dict_lru_usec                     |   25556359671 |   224625.875 | counter        | Time (in microseconds) spent to process DICT LRU list                                                             |
| innodb_checkpoint_usec                   |     417383675 |     3668.565 | counter        | Time (in microseconds) spent by master thread to do checkpoint                                                    |
| innodb_dblwr_writes                      |       1991948 |        7.764 | status_counter | Number of doublewrite operations that have been performed (innodb_dblwr_writes)                                   |
| innodb_dblwr_pages_written               |      88472873 |      344.847 | status_counter | Number of pages that have been written for doublewrite operations (innodb_dblwr_pages_written)                    |
| innodb_page_size                         |         16384 |         NULL | value          | InnoDB page size in bytes (innodb_page_size)                                                                      |
| innodb_rwlock_s_spin_waits               |      48060144 |      187.327 | status_counter | Number of rwlock spin waits due to shared latch request                                                           |
| innodb_rwlock_x_spin_waits               |       5197488 |       20.259 | status_counter | Number of rwlock spin waits due to exclusive latch request                                                        |
| innodb_rwlock_s_spin_rounds              |    1476389094 |     5754.624 | status_counter | Number of rwlock spin loop rounds due to shared latch request                                                     |
| innodb_rwlock_x_spin_rounds              |     434074879 |     1691.924 | status_counter | Number of rwlock spin loop rounds due to exclusive latch request                                                  |
| innodb_rwlock_s_os_waits                 |      42616148 |      166.108 | status_counter | Number of OS waits due to shared latch request                                                                    |
| innodb_rwlock_x_os_waits                 |      11139632 |       43.420 | status_counter | Number of OS waits due to exclusive latch request                                                                 |
| dml_reads                                |    5382057652 |    20978.019 | status_counter | Number of rows read                                                                                               |
| dml_inserts                              |    1012583832 |     3946.818 | status_counter | Number of rows inserted                                                                                           |
| dml_deletes                              |      12583838 |       49.049 | status_counter | Number of rows deleted                                                                                            |
| dml_updates                              |      25177697 |       98.137 | status_counter | Number of rows updated                                                                                            |
+------------------------------------------+---------------+--------------+----------------+-------------------------------------------------------------------------------------------------------------------+
133 rows in set (0.00 sec)

To learn more about the INNODB_METRICS table check out the rel="nofollow" href="http://dev.mysql.com/doc/refman/5.6/en/innodb-metrics-table.html" rel="nofollow">MySQL Manual as well as a rel="nofollow" href="https://blogs.oracle.com/mysqlinnodb/entry/get_started_with_innodb_metrics" rel="nofollow">Getting Started blog post by Oracle.

The post rel="nofollow" href="http://www.percona.com/blog/2014/11/18/mysqls-innodb_metrics-table-how-much-is-the-overhead/">MySQL’s INNODB_METRICS table: How much is the overhead? appeared first on rel="nofollow" href="http://www.percona.com/blog">MySQL Performance Blog.

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com