Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 0 down vote accepted

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 !!!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.