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)
migrated from serverfault.com Jun 10 '11 at 13:00
This question came from our site for system and network administrators.
Create another table and log the inserts, updates, and deletes using DML triggers.
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.
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:
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 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 ???
Let's create a table for recording IDs
Now here are triggers to record the ID accesses:
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 ???
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:
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:
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:
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).
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 !!!