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

Improve this question

1 Answer 1

Reset to default
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).

Improve this answer
5
  • Could you clarify on "high enough" and "too high". This is on an amazon ec2 instance on an ebs volume.
    – pablo
    Aug 18, 2011 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, 2011 at 21:33
  • @pablo I suggest you to install cacti w/MySQL cacti templates from Percona (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, 2011 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, 2011 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, 2011 at 23:56

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .