Mysql InnoDB table size performance

Question

I have a table that is closely approaching 2 million records. This table stores a history of transactions. This is on a high traffic website but also the table is not accessed regularly. We currently have no slow queries due to this table, but am looking as to when I should expect to need to migrate data from this table to a data archival method.

The server is an amazon ec2 high cpu medium box.

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


         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 1677878
 Avg_row_length: 71
    Data_length: 120209408
Max_data_length: 0
   Index_length: 246497280
      Data_free: 0
 Auto_increment: 1914179
    Create_time: 2011-08-07 20:15:29
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: InnoDB free: 7168 kB

Few questions:

• At which point might I start seeing performance degrade due to the amount of records in this table?

• What settings should I be checking on the mysql server, via SHOW INNODB STATUS and on the table itself(ie, row format) to ensure I am maximizing performance as I need it?

• What metrics should I be gathering to calculate performance over time?

Answer 1

So many questions!! To your first point, you will probably start to notice performance problems if you start to cause full table scans or joins that create temp tables. You can track this by looking at the EXPLAIN output for your queries. Here is some info on EXPLAIN:

http://weevilgenius.net/2010/09/mysql-explain-reference/

For the settings, ideally it is great if you can fit your entire database in memory. Here are the variables that are probably most helpful to tune:

innodb_buffer_pool_size=8192M
innodb_additional_mem_pool_size=512M
innodb_log_buffer_size=8M
innodb_flush_method = O_DIRECT
key_buffer_size=4096M
read_buffer_size=1M
read_rnd_buffer_size=10M
sort_buffer_size=1M
join_buffer_size=1M
tmp_table_size=32M

You'll want to make most of these as big as reasonable (you will want to research some of these as some are per connection and some are global). You mentioned SHOW INNODB STATUS which is a great place to start, try looking at these fields:

--------
FILE I/O
--------
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
28889 OS file reads, 52982178 OS file writes, 35063424 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 2.67 writes/s, 2.67 fsyncs/s

These should show you if you are bound by IO somewhere. Then check:

----------------------
BUFFER POOL AND MEMORY
----------------------

Free buffers       342668

to make sure you have free buffers.

Regarding your last question of what to monitor, more is better. I use OpenNMS to monitor our MySQL server performance. Here are the things we currently trend/alert on drastic changes:

EventCount
Uptime
Bytes_received
Bytes_sent
Com_delete
Com_delete_multi
Com_insert
Com_insert_select
Com_select
Com_stmt_execute
Com_update
Com_update_multi
Created_tmp_disk_tables
Created_tmp_tables
key_buffer_size
key_cache_block_size
Key_blocks_unused
Key_read_requests
Key_reads
Key_write_requests
Key_writes
Open_files
Open_tables
table_open_cache
Questions
Slow_queries
Connections
Threads_created
Threads_cached
Threads_connected
Threads_running