1

I am inserting approximately 120 million rows in table. Its really slow and giving error "The total number of locks exceeds the lock table size". Can anyone please guide me the quickest process to insert 120 million rows or to remove that error. Insertion table is ISAM.

Here is the query

INSERT INTO brokergenius.market_data  (
  currentPrice,
  SN_ID,
  quantity,
  seatNumbers,
  ZN_ID,
  listingAttributeList,
  listingAttributeCategoryList,
  deliveryTypeList,
  ticketClass,
  dirtyTicketInd,
  splitOption,
  ticketSplit,
  splitVector,
  sellerOwnInd,
  faceValue,
  serviceFee,
  deliveryFee,
  totalCost,
  score,
  seller_price,
  Event_id,
  RN_ID,
  SSN_ID,
  LI_ID ,
  TS_ID
)
SELECT DISTINCT
  currentPrice,
  sectionId,
  quantity,
  seatNumbers,
  zoneId,
  listingAttributeList,
  listingAttributeCategoryList,
  deliveryTypeList,
  ticketClass,
  dirtyTicketInd,
  splitOption,
  ticketSplit,
  splitVector,
  sellerOwnInd,
  faceValue,
  serviceFee,
  deliveryFee,
  totalCost,
  score,
  seller_price,
  event_id,
  0,
  0,
  0,
  0
FROM
    market_snapshot.marketsnapshot;

migrated from stackoverflow.com Dec 5 '15 at 10:12

This question came from our site for professional and enthusiast programmers.

  • If my answer below does not help, you might want to include your complete query; the one supplied has no FROM. – Uueerdo Dec 4 '15 at 23:07
  • Complete query is added :) The thing is that there are no columns which satisfy properly order by. Is there any solution except that ? – user3488299 Dec 4 '15 at 23:16
  • If there is no order by and it is a simple SELECT... FROM (i.e. no joins, subqueries, or bizarre conditions to muddle things), such as the one you have, and the source table is not being INSERTed during this process, the LIMIT portion of the answer below should be safe. – Uueerdo Dec 4 '15 at 23:19
  • What Engine are the source and target? There is no ISAM. – Rick James Dec 12 '15 at 1:29
  • How many rows? How many DISTINCT rows? Do you need DISTINCT on the entire row? Or would it suffice to be DISTINCT on the PRIMARY KEY? What is the PK of each table? – Rick James Dec 12 '15 at 1:32
2

Quickest way to get around such an error is to split it up into multiple queries.

Option 1:

Take the original query and execute it repeatedly changing LIMIT bounds.
... LIMIT 0, 10000000;
... LIMIT 10000000, 10000000;
... LIMIT 20000000, 10000000;
... LIMIT 30000000, 10000000;
and so on...

Without an ORDER BY, the limit is not 100.00000% guaranteed to be consistent; but as long as the query is not overly complicated this is usually safe.

Option 2: Take the original query and execute it repeatedly. Instead of a shifting window from varying LIMIT clauses, devise a set of WHERE conditions that will discretely partition the data. For example: If you know the source is roughly even divided up by several sectionID values, you could have WHEREs based on those values (or non-overlapping ranges of them.)

  • Is there any way to insert bulk load without any order by and where clause? My system has 256 GB RAM and 500GB SSD – user3488299 Dec 4 '15 at 23:17
  • The only "bulk" operations I know of would entail first dumping the select results to a file, and then reading it back into the target table from the file. However, I am not very familiar with the specifics on that method. – Uueerdo Dec 4 '15 at 23:20
  • Is Limit without order by is safe so that data should not be duplicated ? – user3488299 Dec 5 '15 at 8:33
  • Because of the DISTINCT, chunking gets more complicated. – Rick James Dec 12 '15 at 1:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy