Mysql

Mysql InnoDB 表大小性能

  • August 16, 2011

我有一張接近 200 萬條記錄的表。此表儲存事務的歷史記錄。這是在一個高流量的網站上,但也沒有定期訪問該表。由於此表,我們目前沒有緩慢的查詢,但我正在尋找何時需要將數據從該表遷移到數據存檔方法。

伺服器是亞馬遜 ec2 高 cpu 中型機箱。

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

幾個問題:

  • 由於該表中的記錄數量,我可能會在什麼時候開始看到性能下降?
  • 我應該通過 SHOW INNODB STATUS 和表本身(即行格式)檢查 mysql 伺服器上的哪些設置,以確保我在需要時最大限度地提高性能?
  • 我應該收集哪些指標來計算一段時間內的性能?

這麼多的問題!!首先,如果您開始進行全表掃描或創建臨時表的連接,您可能會開始注意到性能問題。您可以通過查看查詢的 EXPLAIN 輸出來跟踪這一點。以下是關於 EXPLAIN 的一些資訊:

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

對於設置,理想情況下,如果您可以將整個數據庫放入記憶體中,那就太好了。以下是可能對調整最有幫助的變數:

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

您需要將其中的大部分設置為盡可能大(您需要研究其中的一些,因為有些是每個連接,有些是全域的)。您提到了 SHOW INNODB STATUS 這是一個很好的起點,請嘗試查看以下欄位:

--------
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

如果您在某處受 IO 約束,這些應該會告訴您。然後檢查:

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

Free buffers       342668

以確保您有可用的緩衝區。

關於你最後一個監控什麼的問題,越多越好。我使用 OpenNMS 來監控我們的 MySQL 伺服器性能。以下是我們目前對劇烈變化的趨勢/警報:

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

引用自:https://serverfault.com/questions/300553