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

I posted a related question on askubuntu.com, but this may be a better place and way to ask it.

I'm working on a way to speed up a db import. Currently the import is taking a week, give or take a few days, which is unacceptable, and I know it can happen faster (dumping the data from a production server takes under an hour).

I set up a MySQL database as a symlink on a ramdisk I set up using ramfs, which is like a cacheless version of tmpfs. Despite the performance difference between the a hard disk and the RAM, I'm still only seeing between 40-190 QPS.

The table is innodb.

How can I speed up the import?

I seem to be getting a lot of down votes on this question, so perhaps it would be helpful to clarify my intentions:

  1. This is not a production server
  2. I have a system of flushing the ramdisk to disk, and went through the effort to set it up so that I could do it at will
  3. My goal is to be able to import over 8 million records in a reasonable amount of time. Something less than a week would be nice, which is the performance I was getting with the default install of MySQL.
share|improve this question

closed as off topic by Mircea Vutcovici, Michael Hampton, John Gardeniers, mdpc, Magellan Nov 29 '12 at 17:22

Questions on Server Fault are expected to relate to server, networking, or related infrastructure administration within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

    
Why are you setting up a database on a ramfs (you realize you'll lose all the data when the machine reboots, right)? Why do you expect that a database engine (big, complex piece of software) will operate at blistering "DDR3" speeds (you realize for small databases disk access is rarely the bottleneck due to OS and DB server caching, right)? –  voretaq7 Nov 29 '12 at 17:12
    
You may want to go over to dba.stackexchange.com and visit their chat to see if somone there can give you some advice. –  Iain Nov 29 '12 at 17:25

1 Answer 1

up vote 2 down vote accepted

this is not normally how you setup a database. MySQL does it's own caching to memory. The ideal would be to setup MySQL with loads of memory so it can cache the indexes of the tables to memory, this will speed up look ups and increase the QPS.

BUT, if you have SQL thats not optimised and using indexes correctly you won't be able to increase QPS, not even with faster drives as in your case the faster drives are ram fs.

A couple of things to review from yourside,

  1. How are my tables structured?
  2. NO!!! -> SELECT * FROM ... <- this kills performance
  3. Do I have the correct indexes
  4. Are my busy tables on separate disk volumes
  5. BINLOGS on separate disk volume, INNODB on separate disk volume
  6. VARCHARS = BAD DESIGN

Lastly it's difficult to optimise a database that's not properly designed for the task at hand.

share|improve this answer
    
ultimately I increased the innodb_buffer_pool_size, set innodb_flush_log_at_trx_commit=2, and moved the ibdata1, ib_logfile0, and ib_logfile1 over onto the ramdisk. The import that was taking a week was cut down to less that 20 minutes. –  jobu1324 Nov 29 '12 at 18:40

Not the answer you're looking for? Browse other questions tagged or ask your own question.