MySQL: view read/write proportion, per specific table

database-performanceinnodbmyisamMySQLoptimization

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

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.

Related Solutions

Mysql – How do MyISAM and InnoDB Utilize HD Space

MySQL use of DiskSpace is quite predictable.

The information_schema can quickly give away how much space is used by both storage engines. However, it is far better to configure InnoDB with innodb_file_per_table. That way, you can micromanage the diskspace of individual InnoDB tables. If you do not have innodb_file_per_table, the ibdata1 will grow and NEVER, EVER SHRINK.

I wrote nice articles about Cleaning Up InnoDB once and for all.

As for MyISAM, you need to periodically run one of the following:

OPTIMIZE TABLE myisam-tablename;
ALTER TABLE myisam-tablename ENGINE=MyISAM; ANALYZE TABLE myisam-tablename;

These will compress MyISAM so that there are not unused data and index pages in the MyISAM table components (.MYD and .MYI)

You can manually monitor the disk space usage with this query:

SELECT IFNULL(B.engine,'Total') "Storage Engine", CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,SUM(data_length+index_length) TSize FROM information_schema.tables WHERE table_schema NOT IN ('mysql','information_schema','performance_schema') AND engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,(SELECT 3 pw) A ORDER BY TSize;

This will tell how much space is occupied by data and index for engine storage engine. Notice at the end of the query the clause: (SELECT 3 pw). Change the number to generate the report in different units

(SELECT 0 pw) for Bytes
(SELECT 1 pw) for KiloBytes
(SELECT 2 pw) for MegaBytes
(SELECT 3 pw) for GigaBytes
(SELECT 4 pw) for TeraBytes
(SELECT 5 pw) for PetaBytes (Write me if you every get numbers this high)

UPDATE 2012-05-26 22:29 EDT

When it comes to InnoDB, if you use innodb_file_per_table, you may find that there is a difference between the file size of the .ibd file and the sum of data_length + index_length.

For an InnoDB table mydb.mytable, here is the comparison you should make:

Get the size of file by running ls -l /var/lib/mysql/mydb/mytable.ibd | awk '{print %5}'
Get the size of the table from the information schemna's point of view: SELECT data_length+index_length FROM information_schema.tables WHERE table_schema='mydb' AND table_name='mytable';
If the filesize > (data_length+index_length) * 1.1, then you should drag the table like this: ALTER TABLE mydb.mytable ENGINE=InnoDB;

This will make a temp table, copy only real data pages and index pages into the temp table, delete the original, and rename the temp table back to mydb.mytable. Instant table compression with one command. Please plan all table compressions during off hours.

Mysql – Join performance on MyISAM and InnoDB tables

Converting tables from MyISAM to InnoDB will really benefit concurrent writes and also atomic lockings.

The mix of InnoDB and MyISAM tables should in no case affect the join performance unless those joins try to lock the full MyISAM table (highly unlikely), doing an EXPLAIN will always help diagnose the queries.

About the second question, since they're backup tables and almost never used these will definitely take advantage of being MyISAM to be as write performant as possible.

Remember these parameters InnoDB : Awesome at concurrent writing, granular locking but not so good at serial writing and also uses more memory MyISAM : Very light, awesome at serial writing, not so good at concurrent writing, smaller memory footprint

Best Answer

Related Solutions

Mysql – How do MyISAM and InnoDB Utilize HD Space

UPDATE 2012-05-26 22:29 EDT

Mysql – Join performance on MyISAM and InnoDB tables

Related Topic