0

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.
Community
  • 1
jobu1324
  • 485
  • 4
  • 9
  • 17
  • 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. – user9517 Nov 29 '12 at 17:25

1 Answers1

2

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.

Danie
  • 1,360
  • 10
  • 12
  • 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