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.

Answer 1

When you ran

SET GLOBAL bulk_insert_buffer_size =1024*1024*256;

what you did was set the insert buffer to 256M for incoming DB Connections going forward.

To set it for your current DB session, run this in that session:

SET bulk_insert_buffer_size =1024*1024*256;

Then, you can run

SHOW VARIABLES LIKE '%bulk%';

and see a different value.

In a post made back on April 18, 2013 (see Steps 2 and 3), I suggested setting the bulk_insert_buffer_size to 256M within a current session.

If you want to keep that value when you restart mysql, just add this to my.cnf

[mysqld]
bulk_insert_buffer_size = 256M

Give it a Try !!!