mysql简单优化

查看数据量

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