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

Hi Nick,

yes, I DECREASED the values. Initially, I had max_heap_table_size and tmp_table_size set to maximum possible value. 

There is an old bug filed related to "out of memory" and "count(distinct)" on mysql site:

https://bugs.mysql.com/bug.php?id=27023

Someone in the thread mentioned that it helped him to reduce max_heap_table_size and tmp_table_size (at least that's how I interpreted his answer) to avoid allocating more memory that system can handle.

That would lead me to two conclusions

1) there could be a bug causing misallocation of memory (too much of it) to perform count(distinct)

2) there could be a configuration issue on your servers that disallows allocating more memory than 2.5G 

which were to some extent confirmed by the fact, that decreasing the limits actually helped me.

cheers,

Lukasz

On Thu, Feb 23, 2017 at 12:21 AM, 'paynen' via Google Cloud SQL discuss <google-cloud-sql-discuss@googlegroups.com> wrote:

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 a topic in the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-cloud-sql-discuss/-JHM_F405jM/unsubscribe.
To unsubscribe from this group and all its topics, 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.

--
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/CALDjGb98W2Zqxcpdg%2BAEL7_MuoO2yx1M8ygk0vwwyn_D81eq%3DA%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.