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 have a VBA program that separates and saves an excel file into 48 different .csv files, then loads those files into 48 separate tables. I use "load data local infile" to accomplish this, and it has worked very well, and very speedily, up until about 2 weeks ago.

Suddenly, every "rs.open..." line, where I actually send the query to MySQL, took about 4-5 seconds, as opposed to the ~1 second it used to take, which drastically increased the time it took to go through each full file.

I still haven't pinpointed the real problem, but it DID get me thinking: Would better computer hardware appreciably speed up the process? I've got 4gigs of ram and a 3.2 Ghz processor, so my computer isn't terrible, but it COULD be better.

Questions

  • Can the speed of MySQL queries be appreciably increased with better hardware, or is it somehow limited by MySQL itself?
share|improve this question
    
It sounds like a memory allocation problem to me, more than a hardware problem. Rolando's suggestion in his answer is a good one. –  user1240 Dec 29 '11 at 6:08

2 Answers 2

up vote 2 down vote accepted

I would like to recommend something with regard to LOAD DATA INFILE:

You need to increase bulk_insert_buffer_size (such as 256M) because a special tree structure is made to accommodate the loading of the data. Even with LOAD DATA INFILE, the tree structure is used when loading data into non-empty tables.

According to the MySQL Docs on bulk_insert_buffer_size:

MyISAM uses a special tree-like cache to make bulk inserts faster for INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA INFILE when adding data to nonempty tables. This variable limits the size of the cache tree in bytes per thread. Setting it to 0 disables this optimization. The default value is 8MB.

share|improve this answer

I seriously doubt you're being CPU bound. This can easily be confirmed or denied by watching top (or process manager? in windows). More ram and faster hard disks are likely the hardware facets you'd want to improve on a DB server.

share|improve this answer
    
No, yeah, I don't actually think that's my problem either; this is more just a "It got me thinking" kind of question. –  Quintis555 Dec 29 '11 at 20:04

Your Answer

 
discard

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

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