如何在mysql中设置bulk_insert_buffer_size？
如何在mysql中设置bulk_insert_buffer_size？
提问于 2013-11-30 21:50:30
我想为insert设置bulk_insert_buffer_size变量。我也在其他页面上搜索过，上面都是关于my.cnf的信息。但是我的系统里没有my.cnf。我检查了my.ini，那里没有这样的变量。
我也试着通过命令行-
SET GLOBAL bulk_insert_buffer_size= 268435456;它显示了-
1 queries executed, 1 success, 0 errors, 0 warnings
Query: SET GLOBAL bulk_insert_buffer_size =1024*1024*256
0 row(s) affected
Execution Time : 0 sec
Transfer Time : 0.001 sec
Total Time : 0.002 sec但在跑步时-
SHOW VARIABLES LIKE '%bulk%';我得到了同样的(旧的)-
Variable_name Value
bulk_insert_buffer_size 8388608 如果我做错了什么，请告诉我。我使用的是MySQL5.5版本。谢谢。
回答 1
Stack Overflow用户
回答已采纳
发布于 2013-11-30 21:57:23
令人困惑。它不会在全局中更改，但会在会话中更改。我也使用64位的MySQL 5.5、CentOS。无论如何，如果您想永久设置此值，请添加‘set bulk_insert_buffer_size=256M’并重新启动。
mysql> show variables like '%bulk%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| bulk_insert_buffer_size | 268435456 | <= initial value
+-------------------------+-----------+
1 row in set (0.00 sec)
mysql> set global bulk_insert_buffer_size = 1024 * 1024 * 1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%bulk%';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| bulk_insert_buffer_size | 268435456 | <= not changed with GLOBAL
+-------------------------+-----------+
1 row in set (0.00 sec)
mysql> set session bulk_insert_buffer_size = 1024 * 1024 * 1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%bulk%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| bulk_insert_buffer_size | 1048576 | <= changed with SESSION
+-------------------------+---------+
1 row in set (0.00 sec)MySQL manual说bulk_insert_buffer_size可以在MySQL运行时修改(Dynamic Variable = yes)。SESSION和GLOBAL都是可变的。
已更新
如果通过GLOBAL更改bulk_insert_buffer_size，似乎不会影响会话变量(仍然令人困惑)。要查看，请使用"SHOW GLOBAL VARIABLE"，"SHOW VARIABLE“表示"SHOW SESSION VARIABLE”
mysql> show session variables like '%bulk%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| bulk_insert_buffer_size | 2097152 |
+-------------------------+---------+
1 row in set (0.00 sec)
mysql> show global variables like '%bulk%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| bulk_insert_buffer_size | 1048576 |
+-------------------------+---------+
1 row in set (0.00 sec)
mysql> show variables like '%bulk%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| bulk_insert_buffer_size | 2097152 |
+-------------------------+---------+
1 row in set (0.00 sec)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/20301010
复制相关文章
相似问题
腾讯云开发者
