3

I have this simple query:

INSERT IGNORE INTO beststat (bestid,period,rawView) VALUES ( 4510724 , 201205 , 1 )

On the table:

CREATE TABLE `beststat` ( `bestid` int(11) unsigned NOT NULL, `period` mediumint(8) unsigned NOT NULL, `view` mediumint(8) unsigned NOT NULL DEFAULT '0', `rawView` mediumint(8) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`bestid`,`period`), ) ENGINE=InnoDB AUTO_INCREMENT=2020577 DEFAULT CHARSET=utf8

And it takes 1 sec to completes.

Side Note: actually it doesn't take always 1sec. Sometime it's done even in 0.05 sec. But often it takes 1 sec

This table (beststat) currently has ~500'000 records and its size is: 40MB. I have 4GB RAM and innodb buffer pool size = 104,857,600, with: Mysql: 5.1.49-3

This is the only InnoDB table in my database (others are MyISAM)

ANALYZE TABLE beststat shows: OK

Maybe there is something wrong with InnoDB settings?

Improve this question
4
  • Please try REPLACE INTO beststat (bestid,period,rawView) VALUES (x,y,z) for collidong values of x,y and INSERT INTO beststat (bestid,period,rawView) VALUES (x,y,z) for non-colliding values of x,y- obviously makeing sure you don't destroy anything valueable - and post result for comparison Commented May 12, 2012 at 10:16
  • How did you measure the speed of the insert? Commented May 12, 2012 at 10:38
  • Are you sure there aren't any other processes/threads taking up a lot of CPU time ? Commented May 12, 2012 at 14:02
  • @SoboLAN: I run a lot more query and only query like this on this table innodb take so much Commented May 12, 2012 at 14:10

2 Answers 2

Reset to default
2

I ran some simulations about 3 years ago as part of some evaluation project for a customer. They had a requirement to be able to search a table where data is constantly being added, and they wanted to be up to date up to a minute.

InnoDB has shown much better results in the beginning, but has quickly deteriorated (much before 1mil records), until I have removed all indexes (including primary). At that point InnoDB has become superior to MyISAM when executing inserts/updates. (I have much worse HW then you, executing tests only on my laptop.)

Conclusion: Insert will always suffer if you have indexes, and especially unique.

I would suggest following optimization:

  1. Remove all indexes from your beststat table and use it as a simple dump.
  2. If you really need these unique indexes, consider some programmable solution (like remembering the max bestid at all time, and insisting that the new record is above that number - and immediately increasing this number. (But do you really need so many unique fields - and they all sound to me just like indexes.)
  3. Have a background thread move new records from InnoDB to another table (which can be MyISAM) where they would be indexed.
  4. Consider dropping indexes temporarily and then after bulk update re-indexing the table, possibly switching two tables so that querying is never interrupted.

These are theoretical solutions, I admit, but is the best I can say given your question.

Oh, and if your table is planned to grow to many millions, consider a NoSQL solution.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Strnage I run a couple of databases on Innodb with over 500 000 000 records in and don't have issues. Dropping the primary key is good for write speed but bad for read speeds. NoSQL alternatives might be suitable but then it might not be suited as well.
Removing the unique key is not a very good advice. You'll spend more time fixing duplicates later than you save during insertion. Millions of rows should not be a problem with any modern RDBMS on modern hardware.
OK, I agree that my answer is not generic enough, but it worked for me (billion records, million daily inserts, no duplication problem, bulk processing done hourly). I guess for exact solution more info of the problem is needed.
I don't think I can do anything above :(
1

So you have two unique indexes on the table. You primary key is a autonumber. Since this is not really part of the data as you add it to the data it is what you call a artificial primary key. Now you have a unique index on bestid and period. If bestid and period are supposed to be unique that would be a good candidate for the primary key.

Innodb stores the table either as a tree or a heap. If you don't define a primary key on a innodb table it is a heap if you define a primary key it is defined as a tree on disk. So in your case the tree is stored on disk based on the autonumber key. So when you create the second index it actually creates a second tree on disk with the bestid and period values in the index. The index does not contain the other columns in the table only bestid, period and you primary key value.

Ok so now you insert the data first thing myself does is to ensure the unique index is always unique. Thus it read the index to see if you are trying to insert a duplicate value. This is where the slow down comes into play. It first has to ensure uniqueness then if it passes the test write data. Then it also has to insert the bestid, period and primary key value into the unique index. So total operation would be 1 read index for value 1 insert row into table 1 insert bestid and period into index. A total of three operations. If you removed the autonumber and used only the unique index as the primary key it would read table if unique insert into table. In this case you would have the following number of operations 1 read table to check values 1 insert into tables. This is two operations vs three. So you do 33% less work by removing the redundant autonumber.

I hope this is clear as I am typing from my Android and autocorrect keeps on changing innodb to inborn. Wish I was at a computer.

Improve this answer

3 Comments

I can try that! I will remove that useless id field. But still it shouldn't take so much.
It would make a difference less work to do but don't expect it to be lightyears faster. There could be other problems like disk drive contention which can slow you down. At least this way you would be more efficient.
removed. Now the table size is half than before! now it's 32MB. and that's alone a good result. Let's hope now queries are faster

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.