InnoDB INSERT 性能的能力

嗨，我正在執行最新版本的 Percona Server。

伺服器版本：5.5.24-55 Percona 伺服器（GPL），26.0 版

我有這些特性的10 cpu 盒子。

processor       : 0
vendor_id       : AuthenticAMD
cpu family      : 16
model           : 9
model name      : AMD Opteron(tm) Processor 6128
stepping        : 1
microcode       : 0x10000d9
cpu MHz         : 800.000
cache size      : 512 KB

它有 SSD 和 64GB 的 RAM。Innodb 大約為 10GB，因此 innodb_buffer_pool_size 設置為 10GB。

我有一張如下表：

create table TODAY
( symbol_id       integer not null
, openp           decimal(10,4)
, high            decimal(10,4)
, low             decimal(10,4)
, last            decimal(10,4) not null
, volume          int
, last_updated      datetime        -- the time of the last quote update
, prev        decimal(10,4) null
, PRIMARY KEY ( symbol_id )
)

如果我從一個空表開始並插入 23,000 行，則大約需要 10 秒。如果我隨後進行更新，每行的每一列都被更新（當然 symbol_id 除外），則需要 11-12 秒。

這通常是我應該從 Innodb 獲得的寫入性能嗎？有什麼建議可以提高這種性能嗎？更新 23,000 行是一種極端情況，因為通常在交易日我需要每 5 秒更新大約 1000 行（因此，這是我正在處理的更現實的約束）。

我已更改的其他相關 mysql.cnf 設置：

innodb_buffer_pool_size = 10G
innodb_log_file_size    = 64M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

順便說一句，如果我使用 ENGINE=MEMORY 創建表而不是 Innodb，則插入大約需要 4 秒，更新需要 6 秒。

許多 TIA 如果有人可以幫助我弄清楚這種查詢的基準是什麼，或者幫助我改進時間。

大學教師

PS 完整的 Innodb 設置。

mysql> 顯示全域變數，如“innodb%”；
+-------------------------------------------+------------------------+
| 變數名 | 價值 |
+-------------------------------------------+------------------------+
| innodb_adaptive_flushing | 開 |
| innodb_adaptive_flushing_method | 估計 |
| innodb_adaptive_hash_index | 開 |
| innodb_adaptive_hash_index_partitions | 1 |
| innodb_additional_mem_pool_size | 8388608 |
| innodb_autoextend_increment | 8 |
| innodb_autoinc_lock_mode | 1 |
| innodb_blocking_buffer_pool_restore | 關閉 |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_restore_at_startup | 0 |
| innodb_buffer_pool_shm_checksum | 開 |
| innodb_buffer_pool_shm_key | 0 |
| innodb_buffer_pool_size | 10737418240 |
| innodb_change_buffering | 全部 |
| innodb_checkpoint_age_target | 0 |
| innodb_checksums | 開 |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_corrupt_table_action | 斷言 |
| innodb_data_file_path | ibdata1:10M:自動擴展 |
| innodb_data_home_dir | |
| innodb_dict_size_limit | 0 |
| innodb_doublewrite | 開 |
| innodb_doublewrite_file | |
| innodb_fake_changes | 關閉 |
| innodb_fast_checksum | 關閉 |
| innodb_fast_shutdown | 1 |
| innodb_file_format | 羚羊 |
| innodb_file_format_check | 開 |
| innodb_file_format_max | 羚羊 |
| innodb_file_per_table | 關閉 |
| innodb_flush_log_at_trx_commit | 2 |
| innodb_flush_method | O_DIRECT |
| innodb_flush_neighbor_pages | 面積 |
| innodb_force_load_corrupted | 關閉 |
| innodb_force_recovery | 0 |
| innodb_ibuf_accel_rate | 100 |
| innodb_ibuf_active_contract | 1 |
| innodb_ibuf_max_size | 5368692736 |
| innodb_import_table_from_xtrabackup | 0 |
| innodb_io_capacity | 200 |
| innodb_kill_idle_transaction | 0 |
| innodb_large_prefix | 關閉 |
| innodb_lazy_drop_table | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | 關閉 |
| innodb_log_block_size | 512 |
| innodb_log_buffer_size | 8388608 |
| innodb_log_file_size | 67108864 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 75 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_old_blocks_pct | 37 |
| innodb_old_blocks_time | 0 |
| innodb_open_files | 300 |
| innodb_page_size | 16384 |
| innodb_purge_batch_size | 20 |
| innodb_purge_threads | 1 |
| innodb_random_read_ahead | 關閉 |
| innodb_read_ahead | 線性 |
| innodb_read_ahead_threshold | 56 |
| innodb_read_io_threads | 4 |
| innodb_recovery_stats | 關閉 |
| innodb_recovery_update_relay_log | 關閉 |
| innodb_replication_delay | 0 |
| innodb_rollback_on_timeout | 關閉 |
| innodb_rollback_segments | 128 |
| innodb_show_locks_held | 10 |
| innodb_show_verbose_locks | 0 |
| innodb_spin_wait_delay | 6 |
| innodb_stats_auto_update | 1 |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | 開 |
| innodb_stats_sample_pages | 8 |
| innodb_stats_update_need_lock | 1 |
| innodb_strict_mode | 關閉 |
| innodb_support_xa | 開 |
| innodb_sync_spin_loops | 30 |
| innodb_table_locks | 開 |
| innodb_thread_concurrency | 0 |
| innodb_thread_concurrency_timer_based | 關閉 |
| innodb_thread_sleep_delay | 10000 |
| innodb_use_global_flush_log_at_trx_commit | 開 |
| innodb_use_native_aio | 開 |
| innodb_use_sys_malloc | 開 |
| innodb_use_sys_stats_table | 關閉 |
| innodb_version | 1.1.8-rel26.0 |
| innodb_write_io_threads | 4 |
+-------------------------------------------+------------------------+
一組 90 行（0.00 秒）

我執行了 numactl –hardware ，這是我得到的輸出。我的管理員的評論如下（作為解釋）。

root@prog:/data/mysql# numactl --hardware
可用：4 個節點 (0-3)
節點 0 CPU：0 1 2 3
節點 0 大小：32766 MB
節點 0 可用：21480 MB
節點 1 CPU：4 5 6 7
節點 1 大小：32768 MB
節點 1 免費：25285 MB
節點 2 CPU：12 13 14 15
節點 2 大小：32768 MB
節點 2 免費：20376 MB
節點 3 CPU：8 9 10 11
節點 3 大小：32768 MB
節點 3 免費：24898 MB
節點距離：
節點 0 1 2 3
0: 10 16 16 16
1: 16 10 16 16
2: 16 16 10 16
3: 16 16 16 10

您需要在以下方面調整 InnoDB 設置：

• 讓 InnoDB 訪問你所有的核心
• 將 innodb_buffer_pool_size增加到12G
• 將 innodb_buffer_pool_instances增加到2（首先執行numactl --hardware以確定物理 CPU 的數量。它報告的每個 CPU 數量，使用該數字。我最近在Jeremy Cole 的部落格中了解到這一點）
• 將日誌文件大小 ( innodb_log_file_size ) 增加到 2047M
• 支持單個 InnoDB 表的單獨表空間文件（啟用innodb_file_per_table）
• 支持高性能或高耐用性（ACID 合規性）
• 高性能：innodb_flush_log_at_trx_commit設置為 0 或 2
• 高耐久性：innodb_flush_log_at_trx_commit設置為 1（預設）
• 將innodb_log_buffer_size的大小與每秒的事務數一起增加（可能是 32M）
• 您目前的 innodb_flush_log_at_trx_commit 設置很好
• 您目前的 innodb_flush_method 設置很好
• 將 innodb_read_io_threads增加到64
• 將 innodb_write_io_threads增加到64
• 將 innodb_io_capacity增加到10000

以下是我過去關於調整 InnoDB 儲存引擎的文章的連結：

• CPU 性能是否與數據庫伺服器相關？（2012 年 4 月 26 日）
• 為什麼 InnoDB 將所有數據庫儲存在一個文件中？（2012 年 3 月 25 日）
• 多核和 MySQL 性能（2011 年 9 月 20 日）
• 可以讓 MySQL 使用多個核心嗎？（2011 年 9 月 12 日）
• 關於單執行緒與多執行緒數據庫的性能（2011 年 5 月 26 日）
• 如何安全地更改 MySQL innodb 變數“innodb_log_file_size”？（2011 年 2 月 16 日）
• 您如何針對繁重的 InnoDB 工作負載調整 MySQL？（2011 年 2 月 12 日）
• Howto：清理 mysql InnoDB 儲存引擎？（2010 年 10 月 29 日）

引用自：https://dba.stackexchange.com/questions/20372