?
Solved

"Error Code : 1037 - Out of memory; restart server and try again" when using order by clause

Posted on 2006-11-19
1
Medium Priority
?
882 Views
Last Modified: 2007-12-19
I've just re-installed mysql and now certain queries cause my app to fail.

Specifically, the query "select distinct * from mytable order by name" causes the following error:

Error Code : 1037 Out of memory; restart server and try again (needed 65528 bytes)

If I remove the "order by" clause then it runs just fine so I'm guessing that the memory issue is a red herring? If not, then how do you determine what an acceptable memory value should be?
0
Comment
Question by:groovyjon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 35

Accepted Solution

by:
Raynard7 earned 500 total points
ID: 17975925
Hi,

each thread / connection has a memory limit assigned to it - this is the maximum amount of room for all temp tables and memory tables at any point in time.

If you have this too small then if you need to do a sort - it creates a temporary table and then sorts by it - then there is not enough room to create it in memory.

The size of memory depends on what system you are running, what queries that you are using and the number of simultaneous users.

If you have say 512 MB free normally and you only have 20 users or so then 6MB would do your job, if you had less and many many more users then this would start paging memory on your machine.

I would probably say that 1 MB should be sufficient for most normal purposes.

Additionally it is important to note the memory is not allocated until it is being used by the user - so even if you have three connections at 1MB memory they probably will not take 3MB but probably 1 until the others take more when runnign queries.
0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question