資料庫休眠

① 怎樣控制ms sqlserver資料庫發布訂閱執行時間

1、通過查詢日誌
（1）、Windows下開啟MySQL慢查詢
MySQL在Windows系統中的配置文件一般是是my.ini找到[mysqld]下面加上
代碼如下
log-slow-queries = F:/MySQL/log/mysqlslowquery。log
long_query_time = 2

（2）、Linux下啟用MySQL慢查詢
MySQL在Windows系統中的配置文件一般是是my.cnf找到[mysqld]下面加上
代碼如下
log-slow-queries=/data/mysqldata/slowquery。log
long_query_time=2
說明
log-slow-queries = F:/MySQL/log/mysqlslowquery。
為慢查詢日誌存放的位置，一般這個目錄要有MySQL的運行帳號的可寫許可權，一般都將這個目錄設置為MySQL的數據存放目錄；
long_query_time=2中的2表示查詢超過兩秒才記錄；

2.show processlist 命令

SHOW PROCESSLIST顯示哪些線程正在運行。您也可以使用mysqladmin processlist語句得到此信息。
各列的含義和用途：
ID列
一個標識，你要kill一個語句的時候很有用，用命令殺掉此查詢 /*/mysqladmin kill 進程號。
user列
顯示單前用戶，如果不是root，這個命令就只顯示你許可權范圍內的sql語句。
host列
顯示這個語句是從哪個ip的哪個埠上發出的。用於追蹤出問題語句的用戶。
db列
顯示這個進程目前連接的是哪個資料庫。
command列
顯示當前連接的執行的命令，一般就是休眠（sleep），查詢（query），連接（connect）。
time列
此這個狀態持續的時間，單位是秒。
state列
顯示使用當前連接的sql語句的狀態，很重要的列，後續會有所有的狀態的描述，請注意，state只是語句執行中的某一個狀態，一個 sql語句，以查詢為例，可能需要經過ing to tmp table，Sorting result，Sending data等狀態才可以完成
info列
顯示這個sql語句，因為長度有限，所以長的sql語句就顯示不全，但是一個判斷問題語句的重要依據。

這個命令中最關鍵的就是state列，mysql列出的狀態主要有以下幾種：
Checking table
正在檢查數據表（這是自動的）。
Closing tables
正在將表中修改的數據刷新到磁碟中，同時正在關閉已經用完的表。這是一個很快的操作，如果不是這樣的話，就應該確認磁碟空間是否已經滿了或者磁碟是否正處於重負中。
Connect Out
復制從伺服器正在連接主伺服器。

Copying to tmp table on disk
由於臨時結果集大於tmp_table_size，正在將臨時表從內存存儲轉為磁碟存儲以此節省內存。
Creating tmp table
正在創建臨時表以存放部分查詢結果。
deleting from main table
伺服器正在執行多表刪除中的第一部分，剛刪除第一個表。
deleting from reference tables
伺服器正在執行多表刪除中的第二部分，正在刪除其他表的記錄。

Flushing tables
正在執行FLUSH TABLES，等待其他線程關閉數據表。
Killed
發送了一個kill請求給某線程，那麼這個線程將會檢查kill標志位，同時會放棄下一個kill請求。MySQL會在每次的主循環中檢查kill標志位，不過有些情況下該線程可能會過一小段才能死掉。如果該線程程被其他線程鎖住了，那麼kill請求會在鎖釋放時馬上生效。
Locked
被其他查詢鎖住了。
Sending data
正在處理SELECT查詢的記錄，同時正在把結果發送給客戶端。

Sorting for group
正在為GROUP BY做排序。
Sorting for order
正在為ORDER BY做排序。
Opening tables
這個過程應該會很快，除非受到其他因素的干擾。例如，在執ALTER TABLE或LOCK TABLE語句行完以前，數據表無法被其他線程打開。正嘗試打開一個表。
Removing plicates
正在執行一個SELECT DISTINCT方式的查詢，但是MySQL無法在前一個階段優化掉那些重復的記錄。因此，MySQL需要再次去掉重復的記錄，然後再把結果發送給客戶端。

Reopen table
獲得了對一個表的鎖，但是必須在表結構修改之後才能獲得這個鎖。已經釋放鎖，關閉數據表，正嘗試重新打開數據表。
Repair by sorting
修復指令正在排序以創建索引。
Repair with keycache
修復指令正在利用索引緩存一個一個地創建新索引。它會比Repair by sorting慢些。
Searching rows for update
正在講符合條件的記錄找出來以備更新。它必須在UPDATE要修改相關的記錄之前就完成了。
Sleeping
正在等待客戶端發送新請求.

System lock
正在等待取得一個外部的系統鎖。如果當前沒有運行多個mysqld伺服器同時請求同一個表，那麼可以通過增加--skip-external-locking參數來禁止外部系統鎖。
Upgrading lock
INSERT DELAYED正在嘗試取得一個鎖表以插入新記錄。
Updating
正在搜索匹配的記錄，並且修改它們。

User Lock
正在等待GET_LOCK()。
Waiting for tables
該線程得到通知，數據表結構已經被修改了，需要重新打開數據表以取得新的結構。然後，為了能的重新打開數據表，必須等到所有其他線程關閉這個表。以下幾種情況下會產生這個通知：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE,或OPTIMIZE TABLE。
waiting for handler insert
INSERT DELAYED已經處理完了所有待處理的插入操作，正在等待新的請求。
大部分狀態對應很快的操作，只要有一個線程保持同一個狀態好幾秒鍾，那麼可能是有問題發生了，需要檢查一下。
還有其他的狀態沒在上面中列出來，不過它們大部分只是在查看伺服器是否有存在錯誤是才用得著。

② mysql鎖定了資料庫表只能寫，為什麼還可以讀

當你開始執行一個 ALTER ，而你遇到了可怕的「元數據鎖定等待」，我敢肯定你一定遇見過。我最近遇到了一個案例，其中被更改的表要執行一個很小范圍的更新（<100行）。ALTER 在負載測試期間一直等待了幾個小時。在停止負載測試後，ALTER 按預期在不到一秒的時間內就完成了。那麼這里發生了什麼？

檢查外鍵

每當有奇數次鎖定時，我的第一直覺就是檢查外鍵。當然這張表有一些外鍵引用了一個更繁忙的表。但是這種行為似乎仍然很奇怪。對表運行 ALTER 時，會針對子表請求一個 SHARED_UPGRADEABLE 元數據鎖。還有針對父級的 SHARED_READ_ONLY 元數據鎖。

我們來看看如何根據文檔獲取元數據鎖定[1]：

如果給定鎖定有多個伺服器，則首先滿足最高優先順序鎖定請求，並且與 max_write_lock_count系統變數有關。寫鎖定請求的優先順序高於讀取鎖定請求。

[1]：https://dev.mysql.com/doc/refman/en/metadata-locking.html

請務必注意鎖定順序是序列化的：語句逐個獲取元數據鎖，而不是同時獲取，並在此過程中執行死鎖檢測。

通常在考慮隊列時考慮先進先出。如果我發出以下三個語句（按此順序），它們將按以下順序完成：

1. INSERT INTO parent2. ALTER TABLE child3. INSERT INTO parent

但是當子 ALTER 語句請求對父進行讀取鎖定時，盡管排序，但兩個插入將在 ALTER 之前完成。以下是可以演示此示例的示例場景：

數據初始化：

• CREATE TABLE `parent` (

• `id` int(11) NOT NULL AUTO_INCREMENT,

• `val` varchar(10) DEFAULT NULL,

• PRIMARY KEY (`id`)

• ) ENGINE=InnoDB;

• CREATE TABLE `child` (

• `id` int(11) NOT NULL AUTO_INCREMENT,

• `parent_id` int(11) DEFAULT NULL,

• `val` varchar(10) DEFAULT NULL,

• PRIMARY KEY (`id`),

• KEY `idx_parent` (`parent_id`),

• CONSTRAINT `fk_parent` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION

• ) ENGINE=InnoDB;

• INSERT INTO `parent` VALUES (1, "one"), (2, "two"), (3, "three"), (4, "four");

Session 1:

• start transaction;update parent set val = "four-new" where id = 4;



Session 2:

• alter table child add index `idx_new` (val);



Session 3:

• start transaction;update parent set val = "three-new" where id = 3;



此時，會話 1 具有打開的事務，並且處於休眠狀態，並在父級上授予寫入元數據鎖定。 會話 2 具有在子級上授予的可升級（寫入）鎖定，並且正在等待父級的讀取鎖定。最後會話 3 具有針對父級的授權寫入鎖定：

• mysql> select * from performance_schema.metadata_locks;+-------------+-------------+-------------------+---------------+-------------+| OBJECT_TYPE | OBJECT_NAME | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS |+-------------+-------------+-------------------+---------------+-------------+| TABLE | child | SHARED_UPGRADABLE | TRANSACTION | GRANTED | <- ALTER (S2)| TABLE | parent | SHARED_WRITE | TRANSACTION | GRANTED | <- UPDATE (S1)| TABLE | parent | SHARED_WRITE | TRANSACTION | GRANTED | <- UPDATE (S3)| TABLE | parent | SHARED_READ_ONLY | STATEMENT | PENDING | <- ALTER (S2)+-------------+-------------+-------------------+---------------+-------------+



請注意，具有掛起鎖定狀態的唯一會話是會話 2（ALTER）。會話 1 和會話 3 （分別在 ALTER 之前和之後發布）都被授予了寫鎖。排序失敗的地方是在會話 1 上發生提交的時候。在考慮有序隊列時，人們會期望會話 2 獲得鎖定，事情就會繼續進行。但是，由於元數據鎖定系統的優先順序性質，會話 3 具有鎖定，會話 2 仍然等待。

如果另一個寫入會話進入並啟動新事務並獲取針對父表的寫鎖定，則即使會話 3 完成，ALTER 仍將被阻止。

只要我保持一個對父表打開元數據鎖定的活動事務，子表上的 ALTER 將永遠不會完成。更糟糕的是，由於子表上的寫鎖定成功（但是完整語句正在等待獲取父讀鎖定），所以針對子表的所有傳入讀取請求都將被阻止！

另外，請考慮一下您通常如何對無法完成的語句進行故障排除。您查看已經打開較長時間的事務（在進程列表和 InnoDB 狀態中）。但由於阻塞線程現在比 ALTER 線程更年輕，因此您將看到的最舊的事務/線程是 ALTER 。

這正是這種情況下發生的情況。在准備發布時，我們的客戶端正在運行 ALTER 語句並結合負載測試（一種非常好的做法！）以確保順利發布。問題是負載測試保持對父表打開一個活動的寫事務。這並不是說它只是一直在寫，而是有多個線程，一個總是活躍的。 這阻止了 ALTER 完成並阻止對相對靜態的子表的隨後的讀請求。

幸運的是，這個問題有一個解決方案（除了從設計模式中驅逐外鍵）。變數max_write_lock_count[2]可用於允許在寫入鎖定之後在讀取鎖定之前授予讀取鎖定連續寫鎖。默認情況下，此變數設置為 18446744073709551615，如果你對該表發出 10,000 次寫入/秒，那麼你的讀將被鎖定 5800 萬年……