[google-cloud-sql-discuss] Re: Debugging "Out of memory (Needed XXXXXXX bytes)" error

Hey Ann Su,

Thanks for keeping us updated with your findings. The issue seems to rest entirely on max_heap_table_size and tmp_table_size, since these are used to limit the size that internal in-memory temporary tables - the kind needed to run queries - can grow to. But I'm a bit confused at this point - did you decrease these values, or increase them? It seems like you're saying you decreased them, which ought to cause the error more frequently by limiting even further the size which the system can allocate to run queries.

Cheers,

Nick
Cloud Platform Community Support

On Monday, February 20, 2017 at 6:27:26 AM UTC-5, Ann Su wrote:

UPDATE 2:

It seems that the error also disappears after reducing the session size of tmp_table_size and max_heap_table_size.

Still, I don't understand why can't the system allocate more than 2,5GB on 52GB machine.

cheers,

Ann

On Monday, February 20, 2017 at 11:19:20 AM UTC+1, Ann Su wrote:

UPDATE:

Actually, all the queries are related to use of DISTINCT and removing it from query makes the "out of memory error" vanish. However, DISTINCT is used for a reason, so we can remove it.

Ann

On Monday, February 20, 2017 at 10:48:58 AM UTC+1, Ann Su wrote:

Hi Nick,

there are several queries that are causing problems. I believe this could be related to some kind of sorting ("group by", "distinct" etc.) and small buffer sizes. The table is using myisam for performance reasons.

The instance has 52 GB of memory.

The full error is:

{

 textPayload: "2017-02-20T09:32:32.931234Z 3045181 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 2576982012 bytes)"  

 insertId: "s=6d6327081d4e4a1bab5830443a5d7d2f;i=3275fa;b=b2909fad69874f529830c69e12050af7;m=f0b6ccf14d;t=548f2f167843a;x=e9e749ac80b48145@a3"  

 resource: {

  type: "cloudsql_database"   {

   project_id: "project-12345"    

   database_id: "project-12345:master1"    

   region: "europe"    

  }

 }

 timestamp: "2017-02-20T09:32:32.932480Z"  

 severity: "ERROR"  

 logName: "projects/project-12345/logs/cloudsql.googleapis.com%2Fmysql.err"  

}

thanks,

Ann

On Wednesday, February 15, 2017 at 7:25:29 PM UTC+1, paynen wrote:

Hey Ann,

Is there a particular query which is being run when this occurs? My guess would be that the memory is being used up by a particular query and the demands it makes on internal temporary tables, etc. Also, could you provide a sample of the error messages, along with the size of the instance memory that was configured when each one occurred? Finally, in order to help debug this, you may want to start logging the exact query used whenever catching an error like this.

Cheers,

Nick
Cloud Platform Community Support

On Wednesday, February 15, 2017 at 12:12:15 PM UTC-5, Ann Su wrote:

Ever since we've migrated to Google Cloud SQL we are hitting an error every now and then:

/usr/sbin/mysqld: Out of memory (Needed 2576982012 bytes)

The number of bytes needed is always the same (~2.5G) and increasing RAM on an instance doesn't help.

Since there are no super privileges or direct access to configs and the scope of adjustable flags from UI/console is very limited we can't experiment with mysql configuration to find out what's causing the problem.

Do you have any ideas how we can debug this issue?

thanks,

Ann

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/bcac486d-c06e-4e60-8e59-7474237416c1%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.