Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

How to insert the data into MYSQL DB in background mode ?

The data contains huge volume about 20 million rows

share|improve this question
2  
Just what is this supposed to mean? In background of what? for what purpose? in which context/environment? etc. –  Alien Life Form Feb 1 '12 at 10:56

1 Answer 1

up vote 2 down vote accepted

I assume that by "background mode" you mean that you want lower priority for your INSERT statements to rule out concurrency and performance issues while feeding the database.

In this case, consider using the INSERT DELAYED statement for row insertion. From the MySQL docs:

The DELAYED option for the INSERT statement is a MySQL extension to standard SQL that is very useful if you have clients that cannot or need not wait for the INSERT to complete. This is a common situation when you use MySQL for logging and you also periodically run SELECT and UPDATE statements that take a long time to complete.

When a client uses INSERT DELAYED, it gets an okay from the server at once, and the row is queued to be inserted when the table is not in use by any other thread.

Another major benefit of using INSERT DELAYED is that inserts from many clients are bundled together and written in one block. This is much faster than performing many separate inserts.

When inserting a large amount of data, consider temporarily disabling the binary log to reduce the strain on your I/O subsystem.

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.