Mysql – Does innodb_data_reads include reads from the buffer pool

It is ironic I answered a question earlier about InnoDB vs MEMORY storage engines.

There is something very weird about the MEMORY Storage Engine you must consider.

MEMORY tables perform full table locks each time there is an INSERT, UPDATE, and DELETE.

MEMORY tables still trigger a little disk I/O because the .frm file of the MEMORY table is a disk file that must be referenced with each query as far the table's existence goes and query parsing thereafter.

The default index type for a MEMORY table is the HASH index not BTREE. If you forget to declare USING BTREE, all range searches become table scans. HASH indexes are poor candidates for indexes to fulfill range queries. The query you have in the question body would quickly be victimized by this.

Even if you create indexing in the MEMORY table with the USING BTREE clause, BTREE indexes in RAM grow at a pace of O(log n) so expect disk I/O again for checking the index definition in the .frm file plus O(log n) running time on page access.

Another crazy thing to think about when using the MEMORY storage engine is this : If you try to join a MEMORY table and an InnoDB table, the resulting lock behavior defaults to the worst one, which in this case is full table locking.

CAVEAT

Others have answered question like this back in March 2011

• David Splllett's answer to What are reasons *not* to use the MEMORY storage engine in MySQL?
• Morgan Tocker's answer to What are reasons *not* to use the MEMORY storage engine in MySQL?

Here is one on why an all memory Database is good or bad : Is it feasible to have MySQL in-memory storage engine utilize 512 GB of RAM?

Monitor everything that you can as frequently as you can. I highly recommend Graphite w/ statsd as a central location to collect all your metrics. It provides a very simple plaintext protocol that makes it trivial to log nearly any metric data and a UI that makes it incredibly easy to compare one metric against another. On my systems, I collect a ton of information and most of it has proved invaluable at some point or another. Here are a few of them:

I wrote a daemon called mysampler that send the output of SHOW GLOBAL STATUS to graphite (or csv, if you want) at regular intervals. We log this at 5s intervals, but there are times that I wish we had it set to 1s intervals. You start to see some very interesting patterns at that level of granularity. It's aware of what stats are counters and which are absolute values (Questions is a counter, Threads_running is an absolute value) and will output the deltas for the counters.

ab-tblstats2g runs from cron every night and sends table size statistics to graphite so we can track table growth. I plan to extend it to include the maximum primary key value and the number of rows (from table statistics) in the near future. It also works with MSSQL Server.

mysql_logger logs the output of SHOW FULL PROCESSLIST to syslog every X interval of time. It makes it trivial to find out exactly what was running concurrently when something goes weird (table locks, long running queries, etc). We dump that data into Splunk for easy searching, but I still sometimes just use grep in the syslog logs.

pt-stalk from the Percona Toolkit is great for "what just happened?" scenarios. It watches server status variables to exceed a certain value (Threads_connected > 25 by default, but Threads_running is usually a more valuable metric, in my experience) and when triggered, collects a bunch of data about MySQL and the system which can be reviewed with pt-sift or by just reviewing the generated files. It will even generate tcpdumps, gdb, oprofile, and strace traces.

That's basically what we monitor, which differs from alerting. For alerting, I suggest you alert on a very small number of metrics. You can cover 90% of cases by simply choosing a workload-representative query and setting a threshold on how long it takes to return. If it exceeds that threshold, alert...there is a problem. Otherwise, you're ok. No need to check "is the process running," or anything like that. Other things to look for are entries in the MySQL error log, approaching too many connections, and the how well replication is functioning (slave lag, slave running, tables in sync). Hit ratios are completely useless for alerting purposes - all that matters is that queries are returning within some period of time.

For further reading, the white paper Preventing MySQL Emergencies by the Percona folks is a good read that goes into great detail on what to monitor and alert on. Percona has also released a set of Nagios Plugins (which should work with Zabbix, I believe) that you can use.