MySQL Memory Problems When Sorting Multiple Columns

[WayCoolJr]

I am running MySQL Server 5.0.37 on a Windows 2003 Server.

I have a table that I want sorted by three columns (column1, then column2, then column3).

I can sort one column just fine. But, if the recordset is large, sorting by two (or more) columns crashes the MySQL Server Service.

Through research it looks like I am having a memory problem as the following error message is written to the MySQL error log:

080325 14:27:10 ERROR C:\SWsoft\Plesk\Databases\MySQL\bin\mysqld-nt.exe: Out of memory; restart server and try again (needed 32776 bytes)

The my.ini file shows the following values:

sort_buffer_size=256K
sort_buffer=256K

Oddly enough, phpMyAdmin shows the following:

sort buffer size 32,776

I increased the sort_buffer_size and sort_buffer in my.ini to 512k, but this did not help.

I am also concerned that if I successfuly increase the buffer sizes too big, I will have problems if I have a large number of users running this query at the same time.

Any help on how I can resolve this would be greatly appreciated.

[Dan Grossman]

That's a really, really small buffer. This must be a small table for the result sets to always fit inside a 512k buffer.

Do you have so little RAM that you expect 512k per concurrent user to fill it up? With only 1GB of RAM that'd be well over a thousand queries running simultaneously... which would require thousands of concurrent users unless this query takes several full seconds to complete.

[WayCoolJr]

I have 512MB of RAM in my Windows server. I would not expect more than 100 queries running simultaneously and probablay about 100 to 200 records returned per query.

The table is currently quite small, but I am only in development/testing stage. I do expect the table to grow pretty big once it is released 100,000 records+ in about 3 months).

I am running this on a GoDaddy virtual dedicated server and MySQL was already installed and the buffers were 256k. What size would you recommend and is the my.ini the correct place to make this change?

Thanks for your help.

[HarryR]

It looks like you have a seriously small amount of memory available to you. How much memory does the whole system have?

The error message you've given indicates that MySQL has actually reached the physical limit, tried to allocate ~32kb for the sort buffer and been killed by the OS.

[WayCoolJr]

HarryR,

I believe we both posted at the same time, so I wanted to answer your question.

I have 512MB on the server.

I should also mention, that the MySQL Server Service does not crash until the recordset is too big. In other words, a query that only returns 5 records in the recordset won't cause a crash. But, a query that returns 100 records will crash. But, this only applies to queries where I try to sort by more than one column. I have no problems with any other queries.

[HarryR]

Can you give an estimate on the memory usage of other processes on the system?

It could be a combination of a spike in load taking up memory that MySQL would normally use, with the web side of things using up memory to display it or store the results...

Also, Dan has a very good point - with 256k or 512k buffers you should be able to handle a huge number of concurrent users.

I know will be a bit of a performance hit, but it might be an option to decrease the temporary table size so even small sorts go to disk?

[WayCoolJr]

My system has approx 300MB available right now with nothing hitting the server.

I was thinking that Dan was saying that the buffer sizes were really small. Do the buffer sizes mean that each query is given this buffer size, or all concurrent queries combined?

[Dan Grossman]

The sort_buffer is a per-thread buffer. One is created for each thread, it's not shared.

How long does your query take to resolve? If it's returning only 100-200 records and is indexed, probably a few milliseconds, right? In that case, you really expect to have enough users for 100 queries to be running in the same few milliseconds? That's what you claimed. If you really have the tens of thousands of simultaneous users on your site necessary to produce 100 simultaneous queries, then a VPS might not be beefy enough for you.

If you really don't have that kind of volume, try increasing your sort_buffer to 1-2MB, restarting MySQL, and see if the problem goes away.

[WayCoolJr]

Dan,

Thanks your help on this. I see what you're saying about 100 simultaneous queries within a few milliseconds - although that would be nice - long way from being there -

I will try your advice and test and will report back with the results shortly.

Thanks again.