0

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
12
  • 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, 2013 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, 2013 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, 2013 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. Jun 1, 2013 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. Jun 1, 2013 at 19:29

2 Answers 2

1

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.

2
  • 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? May 31, 2013 at 23:27
  • That 1161 is MB. Maybe not 2G. Use 1536M (That's 1.5G) Jun 1, 2013 at 1:00
1

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.

1
  • ^ 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. May 31, 2013 at 23:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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