我有一个巨大的数据库(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)