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.

When I establish a connection to DB, I have to write about 30,000 records with an INSERT statement. I've read in a book that I should do it !

Questions

  • Do I need to do SQL_NO_CACHE ?
  • Is it gonna increase performance and insert rate?
share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

@randy is correct in his assertion because I learned from Baron Schwartz awhile ago that caching buffers for queries (query cache) and caching for data and indexes (MyISAM Key Buffer [indexes only], InnoDB Buffer Pool [data and indexes)]) are orthogonal. In other words, one has nothing to do with, and has no bearing on, the other. @randy gets +1 for this.

If you are doing bulk INSERTS, I would like to make two suggestions:

SUGGESTION #1 : Use extended INSERTs

If you have 30,000 INSERT to make, please do not do 30,000 INSERTs. Try to bunch up 500-1000 rows at a time. This is what mysqldump does when creating a dump file so that when a table is being reloaded, it has hundreds or thousands of rows being inserted.

So, instead of

INSERT INTO tblname VALUES (...);
INSERT INTO tblname VALUES (...);
.
.
.
INSERT INTO tblname VALUES (...);

Try to group them like this

INSERT INTO tblname VALUES (...),(...),(...),(...),(...);
INSERT INTO tblname VALUES (...),(...),(...),(...),(...);
.
.
.
INSERT INTO tblname VALUES (...),(...),(...),(...),(...);

The more you group together, the better it will be for the next suggestion...

SUGGESTION #2 : Use a bigger bulk insert buffer

Most people forget to set the bulk insert buffer. The default is 8M. You can really improve performance for extended INSERTs by increasing it to something like

[mysqld]
bulk_insert_buffer_size=512M

Give it a Try !!!

share|improve this answer
    
+1 for the first paragraph, and marked as an answer for your very helpful suggestions. I appreciate it. –  john.locke Apr 17 '12 at 3:07
    
Does myISAM or InnoDB matter for setting this option? –  john.locke Apr 17 '12 at 4:20
    
Is setting UNIQUE_CHECKS=0 and foreign_key_checks=0 help on increasing performance? what is its drawbacks? (All of my DB tables are InnoDB) –  john.locke Apr 17 '12 at 4:54
    
And finally one part of my project is to bulk insert, if I set bulk_insert_buffer_size in mysql.cnf does it affect other normal inserts(1-10 QPS)? I use transaction –  john.locke Apr 17 '12 at 5:10
add comment

No.

SQL_NO_CACHE is only relevant for SELECT statements. It is not an option for INSERT or LOAD DATA statements. "The SQL_CACHE and SQL_NO_CACHE options affect caching of query results in the query cache"

Moreover, the query cache itself has to be enabled, then is invalidated when the underlying dataset has changed.

If you're performing a series of SELECT statements at the same time as the bulk load, then you will save a few microseconds by using SQL_NO_CACHE, but it is only an option as part of the SELECT.

share|improve this answer
add comment

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.