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 server right now receiving more raw data files in 1 hour then I can upsert (insert -> merge) in an hour.

I have a table with 100M (rounded up) rows. Table is currently MyISAM. The table has 1000 columns mostly boolean and a few varchar.

Currently the fastest way i've found to get the information into my DB until now was:

Process raw data into CSV files. Load Data In File to rawData Table. Insert rawData table into Table1. (on dupe key do my function) Truncate rawData Repeat. Worked fine until im merging 6M+ Rows into 100M rows and expecting it to take under an hour.

I got 16G of ram so I set my Key_Buffer_Pool to 6G. I have my query cache pool to 16M I have my query cache limit to 10M I would just replace the information however it has to be an Upsert, Update the fields that are true if exists and insert if it does not.

Things im looking into atm; - Possibly switching server table to InnoDB? |-> Not sure about the performance, as the insert into an empty table is fine, its the merge that's slow.

Maybe allowing more table cache? Or even Query Cache? mysql sql mysqli innodb myisam

Merge Code:

b.3_InMarket = (b.3_InMarket OR r.3_InMarket),

To compare my 2 bool columns.

Update

  • Ok I set Raid0
  • Changed my query to Lock Write on tables when inserting
  • When importing csv im disabling keys then re-enabling them before upsert.
  • Changed concurrent_insert to 2
share|improve this question
1  
Now, not a mysql specialsit here, but in my world, 16g is small - more tiny - for a database server and you are seriously not talking about the most important part - the disc subsystem. This leads me to the usual conclusion of you not really focusing on the disc IO - and that is THE MOST IMPORTANT PART. How many discs do you have? How busy are they? –  TomTom May 31 '13 at 22:55
6  
"Its a Raid Drive". My car has a wheel. WHAT Raid, What discs? "DISK IO is fast" - How many hundred megabytes per second at full random IO? A fast IO system of 6tb would consist of around 85 discs (roughly - Raid 10 of 147gb SAS drives, 15k RPM). You have that? –  TomTom May 31 '13 at 23:14
3  
And I woudl say it is "DEAD SLOW" regarding database performance. You would know how fast it is if it woudl be a database server, because you would have spent days considering alterantive disc layouts. I would say you likely have a Raid 5 or something of sloooooooow and laaaaaarge and cheeeeeaaaap discs. Simple like that. No SSD, no High Perforamnce SAS discs - you would definitely know that. Result: Welcome in database land where large slow discs are worth - nothing. –  TomTom Jun 1 '13 at 5:57
2  
@Jake have you thought about normalizing it, I mean making more rows instead of those columns? Maybe most of the columns are false normally or true, so you can store only rows for the less probable cases. Then a fast insert ignore and a InnoDB are possible, where the inserts possibly don't lock the reads. It also depends on how many non-boolean line-identifying cols you have. –  flaschenpost Jun 1 '13 at 19:24
4  
@TomTom Jake may not be the perfect database admin and Big Data Guru, but he has a problem with a given dataset and a given server. I thought the art of Database Administration is to get a reasonable performance out of a given situation. Jake did not just run into this exclusive club, but was redirected to this forum from the "ordinary people" out there on StackOverflow. –  flaschenpost Jun 1 '13 at 19:29

2 Answers 2

up vote 1 down vote accepted

If your table has a table with more than 1000 columns, it cannot be converted to InnoDB. In that case, run this query

SELECT CEILING(SUM(index_length)/POWER(1024,2)) num
FROM information_schema.tables WHERE engine='MyISAM';

This will give you the correct size for key_buffer_size in MB.

Since you are doing an UPSERT, you should set concurrent_insert to 2 to make INSERTs go faster. You may want to consider changing the table's row format to Fixed. I wrote about why to do both in StackOverflow. In essence, if you make the table's row format Fixed, all table rows are the same size. Thus, INSERTs and UPDATEs would operate on the exact same length of data. Management of row access is far more reasonable.

Since MyISAM only caches indexes (in the key buffer), all data must be read from disk. anything you can do to getting better RAID performance (as asked by @TomTom) would help your cause as well.

share|improve this answer
    
Thanks bro, performing your changes now! –  Jake Steele May 31 '13 at 23:22
    
It says 1161 so setting this around 2G would be more then enough? Its currently set to 5G, or just over 25% of my total ram. You recommend lowering it? –  Jake Steele May 31 '13 at 23:27
    
That 1161 is MB. Maybe not 2G. Use 1536M (That's 1.5G) –  RolandoMySQLDBA Jun 1 '13 at 1:00
    
Did my answer help ??? –  RolandoMySQLDBA Jun 3 '13 at 15:12
    
If my answer was really helpful, please mark it as accepted by clicking the checkmark. Please upvote my answer as well. –  RolandoMySQLDBA Jun 3 '13 at 21:54

In StackOverflow I suggested using a bitset to join many bool-columns together - what do the experts here say? The use case seems to be rarely unsetting a value, just setting it if for that row at any time comes a "true" in an import.

share|improve this answer
    
^ I would also like to know peoples opinion. ON DUPLICATE KEY b.3_InMarket = (b.3_InMarket OR r.3_InMarket), Is how I am currently merging the Boolean values. –  Jake Steele May 31 '13 at 23:45

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.