how to set bulk_insert_buffer_size in mysql?

Question

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.

score 4 · Accepted Answer · edited Jun 20 '20 at 09:12

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 GLOBALly changable.

bulk_insert_buffer_size

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)

what is the difference between session and global? Does that means this value will be set for only one session? — Aamir, Nov 30 '13 at 14:01
@Aamir0731 Hello, `SESSION` means set for current connection. if client A "SET SESSION xxx = 1", only makes it sense to client A. But `GLOBAL` means if someone "SET GLOBAL xxx = 1", it effects all connections. — Jason Heo, Nov 30 '13 at 14:02
done!! :) thank you ok but what if I want to change it permanently for my live server? i dont think so session gonna work there? correct — Aamir, Nov 30 '13 at 14:05
@Aamir0731 You're very welcome! I have updated my answer. (see bottom of it). — Jason Heo, Nov 30 '13 at 14:06
>add `set bulk_insert_buffer_size=256M' and restart. where i can set this? and sorry i did not understand your last point about dynamic variable= yes — Aamir, Nov 30 '13 at 14:13
@Aamir0731 probably this might help you. http://stackoverflow.com/questions/2482234/how-to-know-mysql-my-cnf-location — Jason Heo, Nov 30 '13 at 14:19

how to set bulk_insert_buffer_size in mysql?

1 Answers1

UPDATED