Large mysql insert timeout

Question

I have a large table (200+m records) and I am running the following query:

INSERT INTO queue_noindex (DomainID,ProcessFlag) 
  SELECT DomainID,ProcessFlag 
  FROM central_store 
  WHERE BatchInstall = 2 LIMIT 6000000,250000000

I've run it a number of times now and each time I get the following when I run:

Show table status = Aprox. 52m rows

I know there is at least 100m records in central_store that are applicable and the query is still running ... and at 14167 seconds. When it finished earlier there were no records actually in the queue_noindex table, so it clearly didn't finish and genuinely insert the records it said it had!

Are there logs or something I could be looking at as to why this query (which is quite simple and there is an index on BatchInstall) keeps failing?

How many records are being added typically each time the query is being run? Are any other scripts accessing either of the tables involved whilst the inserts are taking place (and what type are them queries that are accessing the tables at the same time)? — SpacePhoenix
– SpacePhoenix, Commented Jan 14, 2018 at 4:10
It is the only query accessing either of the tables at any time. It says 50m or so records but they don't actually get entered in the table. — UKUser35
– UKUser35, Commented Jan 14, 2018 at 8:04

UKUser35 · Accepted Answer · 2018-01-15 21:02:34Z

2

So the answer it turns out was a clue from the error log found in var/logs/mysql:

66 [ERROR] Got error 147 when reading table './iprf_v2/central_store'

On further investigation I found that this was due to too many locks and also a solution was to change the SET GLOBAL innodb_buffer_pool_size = 1073741824.

So I ran the query again (but it was the only query running) and also changed the pool size and it correctly transferred the records over.

answered Jan 15, 2018 at 21:02

UKUser35

1632 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

w. Patrick Gale Over a year ago

You saved me @UKUser35. I set the buffer pool size as you did and was able to get my insert-select query to complete. Before I set the buffer pool size my query would time out constantly no matter how much time I let it run. Now my query finishes in about a minute as opposed to it simply timing out after many minutes.

Collectives™ on Stack Overflow

Large mysql insert timeout

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related