Mariadb
innodb_buffer_pool_size 設置是否影響更新查詢？
我有一個巨大的數據庫（16GB），我希望在現有列的基礎上添加一個新列。
現有列如下所示：-
MariaDB [demo]> SELECT my_mon FROM mytable; +----------+ | my_mon | +----------+ | Jan 2020 | | Feb 2020 | | Mar 2020 | | Apr 2020 | | May 2020 | | Jun 2020 | | Jul 2020 | | Aug 2020 | | Sep 2020 | | Oct 2020 | | Nov 2020 | | Dec 2020 | | Jan 2021 | | Feb 2021 | | Mar 2021 | | Apr 2021 | | May 2021 | | Jun 2021 | | Jul 2021 | | Aug 2021 | | Sep 2021 | | Oct 2021 | | Nov 2021 | | Dec 2021 | +----------+ 24 rows in set (0.015 sec) MariaDB [demo]>我希望通過執行以下操作創建一個新列：
MariaDB [demo]> alter table mytable add column my_wave char(8); MariaDB [demo]> update mytable set my_wave = concat("W",ceiling(month(str_to_date(substr(my_mon,1,3),"%b"))/4)," ",substr(my_mon,5,8));這個新列如下所示：
MariaDB [demo]> select * from mytable; +----+----------+---------+ | id | my_mon | my_wave | +----+----------+---------+ | 1 | Jan 2020 | W1 2020 | | 2 | Feb 2020 | W1 2020 | | 3 | Mar 2020 | W1 2020 | | 4 | Apr 2020 | W1 2020 | | 5 | May 2020 | W2 2020 | | 6 | Jun 2020 | W2 2020 | | 7 | Jul 2020 | W2 2020 | | 8 | Aug 2020 | W2 2020 | | 9 | Sep 2020 | W3 2020 | | 10 | Oct 2020 | W3 2020 | | 11 | Nov 2020 | W3 2020 | | 12 | Dec 2020 | W3 2020 | | 13 | Jan 2021 | W1 2021 | | 14 | Feb 2021 | W1 2021 | | 15 | Mar 2021 | W1 2021 | | 16 | Apr 2021 | W1 2021 | | 17 | May 2021 | W2 2021 | | 18 | Jun 2021 | W2 2021 | | 19 | Jul 2021 | W2 2021 | | 20 | Aug 2021 | W2 2021 | | 21 | Sep 2021 | W3 2021 | | 22 | Oct 2021 | W3 2021 | | 23 | Nov 2021 | W3 2021 | | 24 | Dec 2021 | W3 2021 | +----+----------+---------+ 24 rows in set (0.000 sec) MariaDB [demo]>我的查詢是：增加 innodb_buffer_pool_size 會使這個和類似的查詢執行得更快嗎？
我總共有 8GB 的 RAM。
我目前的（我認為的預設設置）是：
| innodb_buffer_pool_size | 134217728 |
這是128MB。
我想澄清我的疑問。
innodb_buffer_pool_size 是一個CACHE。一個很大的值將確保多次讀取，特別是對相同數據的重複請求將很快。
就我而言，我正在進行更新（這就是為什麼我在這個問題的開頭描述了我的更新），這意味著我需要讀取一列並寫入另一列。
innodb_buffer_pool_size 是否與更新相關？
更新表是按“塊”行“塊”發生的，還是將表載入到記憶體中然後更新它？
也許，我想看看更新的程式碼/算法。
有人可以澄清此設置是否以及為什麼會影響此更新查詢？
注意：我正在嘗試修改 innodb_buffer_pool_size 以查看它是否會影響 Ergest Basha 建議的更新時間。
我原來的伺服器是LIVE DEVELOPMENT伺服器，所以我想在我的筆記型電腦上執行這個實驗。我創建了一個 3GB 的數據集。我這台電腦上的innodb_buffer_pool_size也是一樣的。我還沒有修改它。
MariaDB [demo]> show variables like "%innodb_buffer_pool_size%"; +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ | innodb_buffer_pool_size | 134217728 | +-------------------------+-----------+ 1 row in set (0.002 sec) MariaDB [demo]> LOAD DATA INFILE './mydata.csv' INTO TABLE mytable FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS; Query OK, 132000000 rows affected (56 min 11.379 sec) Records: 132000000 Deleted: 0 Skipped: 0 Warnings: 0 MariaDB [demo]> ALTER TABLE mytable ADD COLUMN my_wave CHAR(7); Query OK, 0 rows affected (1.650 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [demo]> UPDATE mytable SET my_wave=concat("W",ceiling(month(str_to_date(substr(my_mon,1,3),"%b"))/4)," ",substr(my_mon,5,8)); ERROR 2013 (HY000): Lost connection to MySQL server during query MariaDB [demo]> UPDATE mytable SET my_wave=concat("W",ceiling(month(str_to_date(substr(my_mon,1,3),"%b"))/4)," ",substr(my_mon,5,8)); ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 36 Current database: demo ERROR 2013 (HY000): Lost connection to MySQL server during query $ systemctl status mysql ● mariadb.service - MariaDB 10.3.31 database server Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled) Active: active (running) since Sun 2022-02-06 14:56:15 IST; 12s ago Docs: man:mysqld(8) https://mariadb.com/kb/en/library/systemd/ Process: 28797 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS) Process: 28798 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS) Process: 28800 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ] Process: 28970 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS) Process: 28972 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS) Main PID: 28848 (mysqld) Status: "To roll back: 1 transactions, 11705889 rows" Tasks: 32 (limit: 4915) Memory: 440.3M CGroup: /system.slice/mariadb.service └─28848 /usr/sbin/mysqld重新啟動伺服器，但查詢仍然沒有通過。
MariaDB [demo]> UPDATE mytable SET my_wave=concat("W",ceiling(month(str_to_date(substr(my_mon,1,3),"%b"))/4)," ",substr(my_mon,5,8)); ERROR 2013 (HY000): Lost connection to MySQL server during query MariaDB [demo]>你好，我們又見面了，
當我設置 innodb_buffer_pool_size=5G
並嘗試執行 UPDATE 創建一個新列，它凍結了我的筆記型電腦。
我重新啟動了電腦，現在我的 mysql 拒絕啟動。
$ sudo mysql ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
可能。
128M 是該設置的一個舊的、太小的預設值。更改為大約 70% 的可用記憶體，這可能是：
innodb_buffer_pool_size = 5G因為它是一個“記憶體”，所以較大的值將減少 I/O，這是對大表的任何大查詢的重要組成部分。
UPDATEing一個 16GB 的表會很慢，但有另一個原因。系統將保留所有行的舊副本，以防您崩潰。（這樣它可以將數據恢復到一致的狀態。）也就是說
UPDATE，無論記憶體大小如何，都會花費很長時間。所有表上的所有操作都使用 buffer_pool。
UPDATE不會自己分塊。你可以做分塊。請參閱http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks。（它是在DELETE上下文中編寫的，但更改UPDATE應該很容易。）要考慮的另一種方法：使用舊列和新列建構一個新表，例如
CREATE TABLE newtable ( id ..., my_mon ..., my_wave ..., PRIMARY KEY(id) ) ENGINE=InnoDB SELECT id, my_mon, concat(...) AS my_wave;