Re: File full error in simple select statement
Posted by: Derek McKinnon
Date: April 29, 2021 10:13PM

That last SQL was actually erroring with an inability to connect to MySQL.

I've restarted the computer now. The code was altered to


CREATE TABLE tmp
SELECT w.weekend, d2.code AS family, d2.amount
FROM weekendings w
JOIN dr02 d2 ON (w.weekend >= d2.date AND d2.t1 <> "g")
OR (w.weekend >= d2.weekend AND d2.t1 = "g")
WHERE d2.amount IS NOT NULL;
SELECT weekend, family, SUM(amount) AS owing
FROM tmp
GROUP BY weekend, family
ORDER BY weekend, family


The tmp table was produced at 23 million rows.

The select statement again errored with a "full" message.


RAM. 32GB.

Yes, it is innodb.

I increased innodb_buffer_pool_size to 24GB.

I haven't touched the three innodb properties you mentioned. Here are their values.

@@global.innodb_thread_concurrency @@global.innodb_read_io_threads @@global.innodb_write_io_threads
33 4 4

I restarted the service.

This time it ran successfully! YEAHHHHH!!!!! (sorry, back to being professional now).

It appears to me the only difference was setting the innodb_buffer_pool_size to 3/4 of available RAM.

Is that what you think?

Options: ReplyQuote


Subject
Written By
Posted
Re: File full error in simple select statement
April 29, 2021 10:13PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.