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) Code (markup): The my.ini file shows the following values: sort_buffer_size=256K sort_buffer=256K Code (markup): Oddly enough, phpMyAdmin shows the following: sort buffer size 32,776 Code (markup): 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 successfully increase the buffer sizes too big, I will have problems with my server memory (512MB) 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.
I am using InnoDB. Currently, as I am in dev/testing stage, the table only has about 300 rows. But once released, I expect that to grow to 100,000+. 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. This only applies to queries where I try to sort by more than one column - all other queries run fine.
Are you using "SELECT * ..." and/or "WHERE `column` LIKE '%find_me%'" in your select statement? These typically use up a lot of memory. You probably need to adjust your global buffer parameters; key_buffer_size, innodb_buffer_pool_size, innodb_additional_memory_pool_size, innodb_log_buffer_size, query_cache_size, until you find values that work. Based on your available memory it may be hard to impossible to get the server to work once you get into the 100'000+ rows, but you should be fine for now if you tune your statements and your mysql operating parameters. Also, if you don't need innoDB you're probably better off using myisam for such a low amount of memory. Take a look at the my-medium.cnf example file that comes with mysql. It is probably about the right specs for your hardware.
well. did you check if your memory hardware has enough free space for this? and how much size per row? 1MB per row?