查看数据量
mysql> SELECT ENGINE,concat(round(SUM((DATA_LENGTH+INDEX_LENGTH)/1024/1024),2),'MB') as total_size,COUNT(ENGINE) as table_counts FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN('information_schema', 'performance_schema', 'mysql') AND ENGINE IS NOT NULL GROUP BY ENGINE ORDER BY ENGINE ASC;
+--------+------------+--------------+
| ENGINE | total_size | table_counts |
+--------+------------+--------------+
| InnoDB | 766.88MB | 286 |
| MyISAM | 1475.47MB | 666 |
+--------+------------+--------------+
2 rows in set (0.03 sec)
#vim /etc/my.cnf
数据量不多的情况下innodb_buffer_pool_size值大于数据量的值,数据量多的情况下,innodb_buffer_pool_size 的值大于热点数据的值,innodb_log_file_size的值为innodb_buffer_pool_size的一半儿或者更小
innodb_flush_method = O_DIRECT
查看热点数据
mysql> show engine innodb status \G;
Modified db pages为热点数据页数,每个页为16k