Estimating distribution of row access in mysql

Question

I am trying to get an idea of the size of the "hot data" part of a rather large table, and I was wondering if this could be done directly in mysql. I know that with the percona version of mysql, I can have access to figures like "number of rows accessed per table", but I would actually need those data on a per row basis (e.g. row with id 1 was read 200 times, row with id 2 was read 300 times, where id is the auto increment column)

Answer 1

Create another table and log the inserts, updates, and deletes using DML triggers.

1. So lets say you have a Table A that you want to track.
2. Create another table, say, tbl_row_stats.
3. Create DML triggers on Table A for inserts, updates, and deletes. I.e. whenever any of these three actions happen to Table A, insert a row in tbl_row_stats with the respective row id of Table A and the action that took place.
4. Later on you can do a select using a group by clause, to see the number of times that each action took place per row.

For tracking the selects, you will have to add that to your application code (or stored procedure). I.e. whenever a select is done from the application, a corresponding insert to tbl_row_stats has to be done.

Answer 2

If you want that kind of granular statistics per id, it sounds like you may want to try something a little convoluted to collect such info. Let's explore this scenario:

If you have a table with the following layout in the wp database:

CREATE TABLE `wp_posts` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_author` bigint(20) unsigned NOT NULL DEFAULT '0',
  `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content` longtext NOT NULL,
  `post_title` text NOT NULL,
  `post_excerpt` text NOT NULL,
  `post_status` varchar(20) NOT NULL DEFAULT 'publish',
  `comment_status` varchar(20) NOT NULL DEFAULT 'open',
  `ping_status` varchar(20) NOT NULL DEFAULT 'open',
  `post_password` varchar(20) NOT NULL DEFAULT '',
  `post_name` varchar(200) NOT NULL DEFAULT '',
  `to_ping` text NOT NULL,
  `pinged` text NOT NULL,
  `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content_filtered` text NOT NULL,
  `post_parent` bigint(20) unsigned NOT NULL DEFAULT '0',
  `guid` varchar(255) NOT NULL DEFAULT '',
  `menu_order` int(11) NOT NULL DEFAULT '0',
  `post_type` varchar(20) NOT NULL DEFAULT 'post',
  `post_mime_type` varchar(100) NOT NULL DEFAULT '',
  `comment_count` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `post_name` (`post_name`),
  KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
  KEY `post_parent` (`post_parent`),
  KEY `post_author` (`post_author`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

and you want to track access to this table, you must force the recording of the table's id using a series of operations combining the use of Triggers, BLACKHOLE tables, MySQL Replication, and "Socially Responsible" Coding.

TRIGGERS

Triggers follow the course of any INSERT, UPDATE, and DELETE. In this scenario, you will need three(3) types of Triggers: 1) After INSERT, 2) After UPDATE, 3) After DELETE.

Each time wp_posts has an INSERT, UPDATE or DELETE, record the ID in another table. What kind of table ???

BLACKHOLE TABLES

Let's create a table for recording IDs

CREATE TABLE wp_posts_idtracker
(
  `ID` bigint(20) unsigned NOT NULL,
  `DTSTAMP` datetime NOT NULL
) ENGINE=BLACKHOLE;

Now here are triggers to record the ID accesses:

CREATE TRIGGER wp_posts_insertafter AFTER INSERT ON wp_posts FOR EACH ROW
INSERT INTO wp_posts_idtracker VALUES (NEW.ID,NOW());

CREATE TRIGGER wp_posts_updateafter AFTER UPDATE ON wp_posts FOR EACH ROW
INSERT INTO wp_posts_idtracker VALUES (OLD.ID,NOW());

CREATE TRIGGER wp_posts_deleteafter AFTER DELETE ON wp_posts FOR EACH ROW
INSERT INTO wp_posts_idtracker VALUES (OLD.ID,NOW());

WAIT A MINUTE !!! The table wp_posts_idtracker is a BLACKHOLE table. It stores nothing. So where do the statistics get written ??? Make sure the MySQL Instance with wp_posts has binary logging enabled. Big deal, the stats get written to the binary logs. How can you read those stats ???

MySQL REPLICATION

Using commodity hardware, employee the use of a MySQL Replication Slave. Setup the slave to only accept one table: wp_posts_idtracker. Place this line in /etc/my.cnf in the slave:

[mysqld]
replicate-do-table=wp.wp_posts_idtracker

Since this BLACKHOLE table would be replicated over to the slave, how will it store data ??? Convert it to MyISAM on the slave. Also, index it by ID and DTSTAMP and by DTSTAMP:

use wp
ALTER TABLE wp_posts_idtracker ENGINE=MyISAM;
ALTER TABLE wp_posts_idtracker ADD INDEX (ID,DTSTAMP);
ALTER TABLE wp_posts_idtracker ADD INDEX (DTSTAMP);

Now the Master will simply record every access to a row in wp_posts into the binary logs. MySQL Replicaton takes responsibility to record that over to the Slave. As an alternative to using a separate server for recording this information, you may want to create a second instance of MySQL on port 3307 and have it act as slave of MySQL running on port 3306. You must make sure that the datadir of MySQL on port 3307 is on a separate data volume from that of MySQL on port 3306. Another variation would be to alter the storage engine for wp_posts_idtracker on the slave with the MEMORY storage engine to reduce disk I/O (Caution: If you go with the MEMORY storage engine fro the table wp_posts_idtracker, remember to make the indexes BTREE instead of the default HASH index because running range queries against a HASH-indexed table has horrible performance, even for a MEMORY table). Still another variation would be to place the binary logs in a RAM Disk for even faster replication or placing the relay logs in a RAM Disk as well, along with further reducing disk I/O.

Thus far, IDs involved in INSERTs, UPDATEs and DELETEs are stored safely in a Replication Slave. Are we forgetting any other types of access to wp_posts ??? Oh yes, SELECT statements. How do we record SELECTs ??? There are no triggers for SELECTs in any known RDBMS. How do we handle SELECTs ???

"SOCIALLY RESPONSIBLE" CODING

Since there are no special mechanisms for SELECTs, the developer must take personal responsibilitity to get the IDs that are encountered in SELECT queries and simply INSERT them into the table wp.wp_posts_idtracker. Suppose the ID you are requesting comes from a bulk gathering of IDs. Send them in bulk into wp.wp_posts_idtracker:

SELECT * FROM wp_posts WHERE ...
INSERT INTO wp.wp_posts_idtracker SELECT IDs,NOW() FROM wp_posts WHERE ...

Don't worry about the INSERT query causing Disk I/O accessing the IDs. Those IDs should be cached following the SELECT. If wp_posts is MyISAM, keys would be cached in the key cache (sized by key_buffer_size). If wp_posts is InnoDB, keys would be cached in the innodb buffer pool (sized by innodb_buffer_pool_size).

SUMMARY

Once you employ this rather unique infrastructure, you can simply connect to the slave and read your statistics.

---

This was just an example of how to create the type of stats recording you want for a table's ID. You may have other ideas in mind. Do not be afraid to try them. Always make sure you know the consequences of each milestone you are trying achieve when recording stats.

Give it a Try !!!

I TOLD YOU THIS WOULD BE CONVOLUTED !!!