Relational query, Planetscale throws: `ResourceExhausted desc = Out of sort memory`

SOLUTION: the problem was that I had a json column storing a massive value on each row.

code = ResourceExhausted desc = Out of sort memory, consider increasing server sort buffer size (errno 1038) (sqlstate HY001)

Possible reasons? - bloated table: 76 columns, half of type json() or text() – but only 228 rows – total db size only 1.61MB - drizzle relational query over-selecting? Drizzle relational query:

await db.query.properties.findFirst({
  columns: {},
  with: {
    neighborhood: true, // 2 columns
  },
})

await db.query.properties.findFirst({
  columns: {},
  with: {
    neighborhood: true, // 2 columns
  },
})

The logged sql statement selects all 78 columns. Shouldn't it only need need ~two columns for relation lookup?

select cast(`neighborhood` as json) from (select `properties`.`id`, ...**75 other properties columns** ),
...

select cast(`neighborhood` as json) from (select `properties`.`id`, ...**75 other properties columns** ),
...

2 Replies

mcgrealifeOP•2y ago

By adding a where key to my relational query, the query succeeds where: eq(properties.id, 1234), Here are my relations definitions

export const properties = mysqlTable('properties', {
  id: serial('id').primaryKey(),
  neighborhoodId: int('neighborhoodId'),
  // ... 76 other columns
})

export const neighborhoods = mysqlTable('neighborhoods', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 256 }),
})

export const neighborhoodRelations = relations(neighborhoods, ({ many }) => ({
  properties: many(properties),
}))

export const propertyRelations = relations(properties, ({ one }) => ({
  neighborhood: one(neighborhoods, {
    fields: [properties.neighborhoodId],
    references: [neighborhoods.id],
  }),
}))

export const properties = mysqlTable('properties', {
  id: serial('id').primaryKey(),
  neighborhoodId: int('neighborhoodId'),
  // ... 76 other columns
})

export const neighborhoods = mysqlTable('neighborhoods', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 256 }),
})

export const neighborhoodRelations = relations(neighborhoods, ({ many }) => ({
  properties: many(properties),
}))

export const propertyRelations = relations(properties, ({ one }) => ({
  neighborhood: one(neighborhoods, {
    fields: [properties.neighborhoodId],
    references: [neighborhoods.id],
  }),
}))

EDIT: https://stackoverflow.com/questions/29575835/error-1038-out-of-sort-memory-consider-increasing-sort-buffer-size Some mysql users report this error when using large json columns. So maybe it is related to by ~30 json columns. My json columns are only storing small array of strings. But maybe the drizzle query could be optimized to only select the columns necessary for the relation lookup query? (or maybe sql requires it) In my schema, if I comment out my other 76 properties columns (without db push), the query succeeds! (even as a findMany). Here is the drizzle logged raw sql:

select cast(neighborhood as json) from (select properties.id, properties.name, properties.neighborhoodId, if(count(properties_neighborhood.id) = 0, '[]', json_arrayagg(json_array(properties_neighborhood.id, properties_neighborhood.name))) as neighborhood from properties left join neighborhoods properties_neighborhood on properties.neighborhoodId = properties_neighborhood.id group by properties.id) properties

select cast(neighborhood as json) from (select properties.id, properties.name, properties.neighborhoodId, if(count(properties_neighborhood.id) = 0, '[]', json_arrayagg(json_array(properties_neighborhood.id, properties_neighborhood.name))) as neighborhood from properties left join neighborhoods properties_neighborhood on properties.neighborhoodId = properties_neighborhood.id group by properties.id) properties

But if I comment the columns back in, and execute the same raw sql query that only uses the required columns, it fails with the same error:

await db.execute(
sql`select cast(neighborhood as json) from (select properties.id, properties.name, properties.neighborhoodId, if(count(properties_neighborhood.id) = 0, '[]', json_arrayagg(json_array(properties_neighborhood.id, properties_neighborhood.name))) as neighborhood from properties left join neighborhoods properties_neighborhood on properties.neighborhoodId = properties_neighborhood.id group by properties.id) properties`
)

await db.execute(
sql`select cast(neighborhood as json) from (select properties.id, properties.name, properties.neighborhoodId, if(count(properties_neighborhood.id) = 0, '[]', json_arrayagg(json_array(properties_neighborhood.id, properties_neighborhood.name))) as neighborhood from properties left join neighborhoods properties_neighborhood on properties.neighborhoodId = properties_neighborhood.id group by properties.id) properties`
)

Because the drizzle query generated from the db.execute(sql``) query does contain the other 76 (many json) columns again So maybe the issue is a combination of: - drizzle query is over-selecting - my table has many json column types (even though the actual data stored in the json is much less than 1.6Mib total) for now, I will just do multiple select() queries (non relational), since they do not have memory issues EDIT: Wow, I had a rogue json column storing a massive value on each row. I deleted that and now it's working perfectly 🙌

Dan•2y ago

Yes, we are aware that the query selects more columns than it needs to, but that's (probably) related to a bug in the PlanetScale engine that doesn't allow selecting * plus additional fields in subqueries - it ignores the * part in that case and only returns the additional fields. So we had to work around that by selecting all the columns in subqueries. FWIW, we're gonna think about how to optimize the relational queries generation soon, so maybe something will change for this case as well.

Gaming

Programming

Relational query, Planetscale throws: `ResourceExhausted desc = Out of sort memory`