How do you make mysql insert faster?
I'm a graduate school student researching OLAP with Mondrian OLAP. So I want to insert data into InnoDB (MySQL 5.5) faster at the initial loading. In this environment, the only user is me, so I think can allow more loose settings for insertion speed. At the moment, I'm using the following techniques. Show
Are there any more techniques for faster insertion on InnoDB? And do I have to modify
asked Dec 6, 2012 at 7:13
SUGGESTION #1If your machine has multiple cores, you need to increase the following:
What are these?
SUGGESTION #2In order for separate data and indexes from the system tablespace (ibdata1), you need to do perform a complete restructuring of InnoDB. Sounds complicated, but is very straightforward. I wrote about this in the DBA StackExchange (Aug 29, 2012) and in StackOverflow (Oct 29, 2010). The basic steps are
Before you run
That way, there will be file handles dedicated to the each individual table. The default is 300. File handles have been known to get cached. There will be a slowdown if you set this very high and hit the ceiling quickly. This should not be the case if you are working a small number of tables.
answered Dec 6, 2012 at 16:06
RolandoMySQLDBARolandoMySQLDBA 175k31 gold badges303 silver badges496 bronze badges 2 There's an entire document dedicated to bulk loading data into InnoDB. The main points:
The third may or may not help you, so I suggest reading that link to see how you are initially loading the data. For instance, if you are breaking the loads into multiple inserts to run concurrently, it will definitely help you to set the value to 2. If you are doing one large multi-line insert, it won't do much (if anything) to help. Since you are turning of the binary log for this initial insert, you shouldn't care about the gaps in autoincrement numbers (if doing concurrent inserts). answered Dec 6, 2012 at 14:26
Derek DowneyDerek Downey 23k11 gold badges76 silver badges104 bronze badges 2 You can use the following methods to speed up inserts:
answered Dec 9, 2016 at 7:55
Plan A: "Batch" INSERTs -- multiple rows per INSERT statement. Suggest about 1000 rows per statement. autocommit=on, no explicit BEGIN...COMMIT Plan B: LOAD DATA If you insert too many rows at once, InnoDB must do more work to be able to rollback the insert if there is a crash. For this reason, I disagree with autocommit=off, which would put the entire set into a single transaction. LOAD DATA of the entire set of rows may would have the same problem, but it is quite fast. buffer_pool = 5G out of 6G is on the verge of being too big. If there is any swapping, performance will plummet. PARTITIONing would probably make it go slower. SHOW CREATE TABLE -- Secondary keys could be a serious handicap. Are you using InnoDB? or XtraDB? answered Dec 11, 2012 at 0:15
Rick JamesRick James 70.5k4 gold badges40 silver badges97 bronze badges 1 How can insert 1000 records at a time in MySQL?MySQL Insert Multiple Rows. First, specify the name of table that you want to insert after the INSERT INTO keywords.. Second, specify a comma-separated column list inside parentheses after the table name.. Third, specify a comma-separated list of row data in the VALUES clause. Each element of the list represents a row.. Which is faster insert or update MySQL?Insertion is inserting a new key and update is updating the value of an existing key. If that is the case (a very common case) , update would be faster than insertion because update involves an indexed lookup and changing an existing value without touching the index.
Which is more efficient to insert data into a table in MySQL?LOAD DATA (all forms) is more efficient than INSERT because it loads rows in bulk.
How can I make MySQL query run faster?Adjust the size and properties of the memory areas that MySQL uses for caching. With efficient use of the InnoDB buffer pool, MyISAM key cache, and the MySQL query cache, repeated queries run faster because the results are retrieved from memory the second and subsequent times.
|