4

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?

Improve this question

1 Answer 1

Reset to default
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
Improve this answer
1
  • this is great info thanks am still looking through it Commented Aug 18, 2011 at 5:36

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.