Mysql
處於“更新”狀態時經常查詢緩慢
我有兩張我經常更新的表，一張是直接的
UPDATE ..，另一張INSERT .. ON DUPLICATE KEY UPDATE ...是通常這些查詢是即時的，但有時需要 0.1s 到 1+ 秒，然後再即時幾秒鐘。另外需要注意的是，我有兩台 MySQL 伺服器（在同一個專用網路中）。所有選擇查詢（除了一兩個）都在從屬伺服器上執行，這裡看到的插入當然是在主伺服器上。當數據庫處於大負載下時，減速最為明顯。最後，我正在使用 xtradb（看看它是否有幫助），但是在普通的 MySQL 5.5 InnoDB 上也發生了同樣的行為。
-- CustomData +-----------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------+------+-----+---------+-------+ | Server | int(11) | NO | PRI | NULL | | | Plugin | int(11) | NO | PRI | NULL | | | ColumnID | int(11) | NO | PRI | NULL | | | DataPoint | int(11) | NO | | NULL | | | Updated | int(11) | NO | MUL | NULL | | +-----------+---------+------+-----+---------+-------+前任。幾個查詢：
mysql> insert into CustomData (Server, Plugin, ColumnID, DataPoint, Updated) VALUES ( 52707, 1, 1, 0, 1327093596) on duplicate key update DataPoint = 0 , Updated = 1327093596 ; Query OK, 0 rows affected (0.00 sec) mysql> insert into CustomData (Server, Plugin, ColumnID, DataPoint, Updated) VALUES ( 52707, 1, 1, 0, 1327093596) on duplicate key update DataPoint = 0 , Updated = 1327093596 ; Query OK, 0 rows affected (0.12 sec) mysql> insert into CustomData (Server, Plugin, ColumnID, DataPoint, Updated) VALUES ( 52707, 1, 1, 0, 1327093596) on duplicate key update DataPoint = 0 , Updated = 1327093596 ; Query OK, 0 rows affected (0.00 sec) mysql> insert into CustomData (Server, Plugin, ColumnID, DataPoint, Updated) VALUES ( 52707, 1, 1, 0, 1327093596) on duplicate key update DataPoint = 0 , Updated = 1327093596 ; Query OK, 0 rows affected (0.00 sec)解釋-
mysql> explain select * from CustomData where Server = 52707 and Plugin = 1 and ColumnID = 1 ; +----+-------------+------------+-------+---------------+---------+---------+-------------------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+-------+---------------+---------+---------+-------------------+------+-------+ | 1 | SIMPLE | CustomData | const | PRIMARY,Cron | PRIMARY | 12 | const,const,const | 1 | | +----+-------------+------------+-------+---------------+---------+---------+-------------------+------+-------+Profiler（用於 0.12 秒查詢）
mysql> show profile for query 56 ; +----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.000062 | | checking permissions | 0.000005 | | Opening tables | 0.000016 | | System lock | 0.000007 | | init | 0.000011 | | update | 0.129531 | | end | 0.000005 | | query end | 0.000012 | | closing tables | 0.000008 | | freeing items | 0.000017 | | logging slow query | 0.000001 | | cleaning up | 0.000036 | +----------------------+----------+所有其他有問題的查詢都非常相似。我沒有註意到任何長時間執行的查詢
show processlist;顯示引擎 INNODB 狀態（需要 10 個代表發布 >2 個連結，所以）- pastebin .com/raw.php?i=tdtZeTCJ
我想指出的最後一件事是，在給定的範例中，任何工作人員只
INSERT .. ON DUPLICATE KEY在唯一的行上執行，而不是同一行（因此每個查詢都針對不同的行）提前致謝！
編輯：
我所有的數據都在 3G 左右，有索引。緩衝池大小最初是 2G，但也嘗試過 4G。
我的.cnf-
[mysqld] datadir = /data/mysql socket = /var/run/mysqld/mysqld.sock user = mysql bind-address = 10.10.1.7 log-slow-queries = mysql-slow.log long_query_time = 5 log-queries-not-using-indexes # Replication server-id = 1 # master log-bin = /var/log/mysql/mysql-bin.log expire-logs-days = 10 binlog-do-db = metrics max_binlog_size = 104857600 binlog_format = MIXED # slave # replicate-do-db = metrics # read_only = 1 # /Replication skip-external-locking sysdate-is-now performance_schema log-error=/var/log/mysql.log default-storage-engine = InnoDB innodb_file_per_table = 1 innodb_file_format = barracuda innodb_buffer_pool_size = 4G innodb_additional_mem_pool_size = 10M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 0 innodb_lock_wait_timeout = 50 innodb_fast_shutdown = 1 innodb_flush_method = O_DIRECT transaction_isolation = READ-COMMITTED max_connections = 4092 connect_timeout = 86400 wait_timeout = 86400 interactive_timeout = 86400 max_allowed_packet = 64M open_files_limit = 2048 table_cache = 2048 net_buffer_length = 8K query_cache_type = 1 query_cache_size = 16M thread_cache = 100 thread_stack = 512K tmpdir = /dev/shm tmp_table_size = 64M key_buffer_size = 64M sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid編輯2
創新型-
我讀到了間隙鎖定 - 是否有可能是間隙鎖定我
Plugin的ColumnID列，忽略了 PK 是 3 cols 的事實以及我正在使用 READ-COMMITTED 所以它不應該是這樣的事實嗎？從 innotop 中可以看出，有些是非常接近的，所以如果是這種情況，那麼會有很多鎖在周圍mysql> show indexes from CustomData; +------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | CustomData | 0 | PRIMARY | 1 | Server | A | 1073724 | NULL | NULL | | BTREE | | | | CustomData | 0 | PRIMARY | 2 | Plugin | A | 1879017 | NULL | NULL | | BTREE | | | | CustomData | 0 | PRIMARY | 3 | ColumnID | A | 7516069 | NULL | NULL | | BTREE | | | | CustomData | 1 | Cron | 1 | Plugin | A | 20 | NULL | NULL | | BTREE | | | | CustomData | 1 | Cron | 2 | ColumnID | A | 20 | NULL | NULL | | BTREE | | | | CustomData | 1 | Cron | 3 | Updated | A | 7516069 | NULL | NULL | | BTREE | | | | CustomData | 1 | Updated | 1 | Updated | A | 2505356 | NULL | NULL | | BTREE | | | +------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+cron 鍵每 30 分鐘才使用一次，現在甚至沒有啟用（以防它被啟動）
您需要在 InnoDB 設置中進行一些調整
調整 #1：更大的 InnoDB 重做日誌
由於II沒有看到innodb_log_file_size，我假設你有5M的預設值。由於您的 innodb_buffer_pool_size = 是 4G，因此您需要 1G 重做日誌。
調整 #2：讓 InnoDB 使用所有 CPU
開箱即用，InnoDB 並不使用所有 CPU。我很久以前寫了一篇關於 InnoDB LEFT UNCONFIGURED 如何在舊版本中更快地工作的文章。我還為 InnoDB 寫了關於多核參與的文章：
說了這麼多，下面是要做的調整
cp /etc/my.cnf /etc/my.cnf_old將此設置添加到 /etc/my.cnf
[mysqld] innodb_log_file_size = 1G innodb_io_capacity = 20000 innodb_read_io_threads = 5000 innodb_write_io_threads = 5000接下來，執行這些步驟
service mysql stop mv /var/log/mysql/ib_logfile0 /var/log/mysql/ib_logfile0_old mv /var/log/mysql/ib_logfile1 /var/log/mysql/ib_logfile1_old service mysql start現在，所有核心都支持 InnoDB，並且有更多的空間進行事務隔離
試一試 ！！！
多少記憶體？innodb_buffer_pool_size 應該是可用RAM 的 70% 左右。
你多久做一次選擇？插入？
請注意，INSERT 也在從站上執行。你在用 SBR 嗎？還是 RBR？
請使用顯示創建表；DESCRIBE 描述性較差！
看起來您在可能更改的欄位上有索引。這可能是問題的一部分。你可以不用一些索引嗎？
特別是，讓 cron 鍵花費更長的時間 – do only (pluginID, columnID)
更改更新後，必須從一個位置刪除索引中的“行”，然後在另一個位置重新插入。