how to set bulk_insert_buffer_size in mysql?
global-variables, myisam, mysql, variables
Solution
confusing. it is not changed in GLOBAL, but changed in SESSION. I also use MySQL 5.5, CentOS, 64Bit. anyway if you want set this value permanently, add `set bulk_insert_buffer_size=256M' and restart.
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 says `bulk_insert_buffer_size` can be modified while MySQL running (`Dynamic Variable` = yes). SESSION and `GLOBAL`ly changable.
UPDATED
IF `bulk_insert_buffer_size` is changed via GLOBAL, It seems that not affect SESSION variable (still confusing). To see, use "SHOW GLOBAL VARIABLE", "SHOW VARIABLES" implies "SHOW SESSION VARIABLES"
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)
Problem
I want to set bulk_insert_buffer_size variable for insert. I searched from other pages too, all they are saying about my.cnf. But there is no my.cnf in my system. I checked my.ini and there is no such variable there. I also tried through command line - ``` SET GLOBAL bulk_insert_buffer_size= 268435456; ``` It shows - ``` 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 ``` but on running - ``` SHOW VARIABLES LIKE '%bulk%'; ``` I am getting the same(old one) - ``` Variable_name Value bulk_insert_buffer_size 8388608 ``` Please let me know if I am doing something wrong. I am using Mysql 5.5 version. Thank you.