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'm trying to optimize an important cronjob for my program, and what it basically does is generate a long list of user ids with their score points. I have a table called user_ranks and it will bulk insert all of that data into that table. Normally that went fine, but it is taking a longer and longer time, which now is giving huge problems to my website. The bulk insert query takes about 5 seconds, which is too much. It is blocking other queries to that table. (I'm using row level locking but it locks the whole table)

Now what my idea to optimize this is, is to do the bulk inserting in another table, say user_ranks_new and then after the insert is done, rename the user_ranks table to user_ranks_old and user_ranks_new to user_ranks and afterwards dropping the old table.

This is obviously a lot faster than blocking the important table (user_ranks) for about 5 seconds. This cronjob runs every 10 minutes, so I want it optimized a lot.

Are there any drawbacks to creating lots of tables, renaming them, and dropping them? Does the ibdata files gets a lot bigger, or any other?

p.s. I'm aware of settings like shown on this page (http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-bulk-data-loading.html), but they didn't really help significantly.

share|improve this question
    
I don't know if MySQL has this or not, but if you don't have an atomic "exchange"-type operation to do the switch you're dead - queries will randomly fail rather than taking a bit more time. (I'm not sure there are RDBMSs that provide an atomic and non-blocking "exchange"-type thing. Sounds entirely non-trivial wrt global database consistency - but I could be wrong there.) –  Mat Aug 2 '13 at 11:35
    
QUESTIONS : 1) What is the output for SELECT data_length, index_length, (data_length+index_length) tblsize FROM information_schema.tables WHERE engine='InnoDB';? 2) How much RAM on the DB Server? 3) What is SELECT version();? 4) Do you have innodb_file_per_table enabled? 5) Any MyISAM in this mix? 6) Is this a bar metal serve or a VM? 7) How many rows are you loading into user_ranks? 8) How many CPU/Cores ? –  RolandoMySQLDBA Aug 2 '13 at 16:21
    
1) hmm, a lot of rows are there, do you want to see them all? 2) 10GB RAM 3) version: 5.5.25a 4) Yes, enabled 5) Yes, but not many tables.. about 3% 6) Virtual machine, it's a VPS. 7) about 300.000 8) 6 processors –  Martin Aug 2 '13 at 21:49
    
Mat, Thanks for your reply too, by the way, I have no clue if that exists and it's an important point. And forgot to mention @RolandoMySQLDBA –  Martin Aug 3 '13 at 20:17

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.