1

When looking at the output of SHOW INNODB STATUS on mysql what are the most important values to be looking at, and what would be an indicator of poor performance?

Specifically under FILE I/O and BUFFER POOL AND MEMORY.

The DB server is:

High-CPU Medium Instance

1.7 GB of memory
5 EC2 Compute Units (2 virtual cores with 2.5 EC2 Compute Units each)
350 GB of instance storage
32-bit platform
I/O Performance: Moderate
API name: c1.medium

This is a follow up to this: Mysql InnoDB table size performance

Community
  • 1
pablo
  • 3,020
  • 1
  • 18
  • 23

1 Answers1

1

Probably the most important value is "Buffer pool hit rate", it should be high enough. Next, you should check number of writes/sec and fsyncs/sec, number of fsyncs/sec should not be too high (if this number is lower than 1 it's OK, but this depends of implementation of your filesystem and/or underlying hardware, if you have a HW RAID w/BBU instructed to buffer fsync requests a high number of fsyncs won't affect performance much).

Alex
  • 7,789
  • 4
  • 36
  • 51
  • Could you clarify on "high enough" and "too high". This is on an amazon ec2 instance on an ebs volume. – pablo Aug 18 '11 at 20:57
  • @pablo I checked on one of my live DBs, buffer pool hit rate was 1000/1000, which was effectively 100%. Of course, there is no guarantee it is 100% all the time, 95-98% is more typical. It depends on your workload what exact numbers are good for you here, but higher is better. 950/1000 should be enough in most cases. – Alex Aug 18 '11 at 21:33
  • @pablo I suggest you to install cacti w/MySQL cacti templates from Percona (http://code.google.com/p/mysql-cacti-templates/), these templates interpret lots of internal MySQL variables graphically. I always use cacti for performance measurement. – Alex Aug 18 '11 at 21:35
  • thanks my mysql solution is managed and monitored for me so I dont have much options for dropping in cacti at the moment. also, what about fsyncs/sec whats a "normal" value that isnt too high for this? – pablo Aug 18 '11 at 23:47
  • @pablo My live systems have about 0.5-0.7 fsyncs/sec, I guess this is because I've set `innodb_flush_log_at_trx_commit` to `2` so MySQL don't have to flush to disk on every data modification. – Alex Aug 18 '11 at 23:56