CODEWITHSUNDEEP
mysqlamazon-rds
aarelovich
aarelovich

Reputation: 5586

General error 1114 when querying a table only 4% of the storage capacity in an RDS Instance

I have the following problem when attempting to draw stats on a table located in an Amazon AWS instance.

The table is composed of a huge list of names, dates and state two letter codes among other things. The dates are represented by a 6 digit code where the first four digits are the year and the last two digits the month (yes, we don't care about the day).

I do a query to check the table size and it returns: 4097 MB which sounds about right.

But I also need to run the following query:

SELECT state_code,COUNT(date_code) as cnt,date_code
    FROM mytable 
    WHERE (date_code > 200801) 
    GROUP BY date_code,state_code
    ORDER BY  state_code ASC

Basically I want to know how many rows I have have for each date_code on each state.

My RDS instance is a db.m4.large running MySQL 8.0.23 with 1000 GiB of storage and a Maximum of 2000 GiB

I get this error: Failed getting the number of obits by year and state. Reason: Select failure: SQLSTATE[HY000]: General error: 1114 The table '/rdsdbdata/tmp/#sql7245_5bc3_1' is full.

Now, there is a question very much like this one here: How to solve MySQL “The table is full” error 1114 with Amazon RDS?

However the solution is to simply increase the storage size, but 4 GB is only 4 % of the storage size. And this table will be ever increasing.

How could I solve this issue? Is the only answer to increase table size

Just FYI, this will be a very seldom query. Worst case scenario something like once a day. I need for it to work, no really interested in making it fast. In case it matters.

Upvotes: 1

Views: 1133

Answers (1)

Rick James
Rick James

Reputation: 142560

SELECT  state_code,
        COUNT(*) as cnt,   -- assuming `date_code` is never NULL
        date_code
    FROM mytable 
    WHERE (date_code > 200801) 
    GROUP BY  state_code, date_code   -- note swap
    ORDER BY  state_code, date_code   -- matching GROUP BY

Add INDEX(date_code, state_code).

Notes:

  • COUNT(*) is the normal syntax for counting; COUNT(x) checks x for being not null before counting.
  • The ordering in GROUP BY is probably not important, but is in ORDER BY. Since you asked to order by state_code, I changed GROUP BY. Then, ...
  • When GROUP BY and ORDER BY are the same, there is no need for a second sort.
  • The new index is "covering", so it will scan the index in it rather than scanning the bulkier data BTree.

If the "older" data is never changing, you can get a much faster query by building and maintaining a "summary table". More discussion: http://mysql.rjweb.org/doc.php/summarytables

Upvotes: 1

Related Questions