Home / server / Questions / 316887
Accepted
J. M. Becker
Asked: 2011-09-30 08:00:33 +0800 CST

MySQL: view read/write proportion, per specific table

  • 772

I'm optimizing the configuration of a MySQL server, running only Bromine. This is non specific to application, and was just noted for completeness. I am trying to determine if switching from MyISAM to InnoDB is a better option, on few write dominant tables. these tables take most of the writes for the server, but also the highest percentage of all queries. I don't want to make all the tables InnoDB, even though it would save me this time. I prefer MyISAM over all; InnoDB has its own strengths, which make sense for these write heavy specific tables.

I know how to look at the read/write statistics for the MySQL instance, and run all the recommended analyses scripts. I plainly would like to know, how do I look at the read/write stats on a specific table?

If someone, with relevant experience, has any recommended stats to look at. I would appreciate the input.

UPDATE: I posted about this issue on the Bromine forumns, And they let me know which tables are write heavy. This does not really answer the question, as I would like to know anyway.

Just for the sake of completeness, Here is my Table / Engine

Name                               Engine
QRTZ_BLOB_TRIGGERS                 MyISAM
QRTZ_CALENDARS                     MyISAM
QRTZ_CRON_TRIGGERS                 MyISAM
QRTZ_FIRED_TRIGGERS                MyISAM
QRTZ_JOB_DETAILS                   MyISAM
QRTZ_JOB_LISTENERS                 MyISAM
QRTZ_LOCKS                         MyISAM
QRTZ_PAUSED_TRIGGER_GRPS           MyISAM
QRTZ_SCHEDULER_STATE               MyISAM
QRTZ_SIMPLE_TRIGGERS               MyISAM
QRTZ_TRIGGERS                      MyISAM
QRTZ_TRIGGER_LISTENERS             MyISAM
activities                         InnoDB
browsers                           MyISAM
browsers_nodes                     MyISAM
combinations                       MyISAM
combinations_requirements          MyISAM
commands                           InnoDB
configs                            MyISAM
echelons                           InnoDB
groups                             MyISAM
jobs                               InnoDB
myacos                             MyISAM
myaros                             MyISAM
myaros_myacos                      MyISAM
nodes                              MyISAM
operatingsystems                   MyISAM
plugins                            MyISAM
projects                           MyISAM
projects_reports                   MyISAM
projects_users                     MyISAM
reports                            MyISAM
requirements                       MyISAM
requirements_testcases             MyISAM
seleniumservers                    InnoDB
sites                              MyISAM
suites                             InnoDB
testcases                          MyISAM
testcasesteps                      MyISAM
tests                              InnoDB
types                              MyISAM
users                              MyISAM
mysql optimization innodb myisam database-performance

2 Answers

  1. Best Answer

    I got statistics through cacti, with the mysqlstats plugin.

    If anyone does it through the command line, with a script of some sort, I'd appreciate the information.

    • 1

  2. You cannot look at the tables once and determine which ones are write heavy, and this is especially so if the database has been up and running for any considerable time. Due to changing usage patterns, a table that appears to have received a massive number of writes may no longer be receiving any.

    You need to take a snapshot of all the tables at time t, and then another snapshot at time t+1 (where the interval might be an hour, a day, or more likely a week, possibly a month). Once you have them you can measure the difference between the two and calculate the activity from that.

    You want to look at the ratio of table reads compared to table writes. A ratio of 1 means a table is never written to, only read. And a ratio of 0 means a table is only written to, never read. Most table will of course lie somewhere in between.

    SELECT t.table_name, s.rows_read/ (s.rows_read * 1.0 + s.rows_changed) as read_ratio, s.rows_read, s.rows_changed
FROM information_schema.tables t
LEFT JOIN information_schema.table_statistics s USING (table_schema, table_name)
ORDER BY 2, 3, 4, 1;

    The global variable userstat must be set to on to record activity in these statistics tables.

    Put the above query in a file, and then from the command line you can run:

    mysql -BNe < tablestats.sql
    • 1