menntarra_34 Posted September 19, 2010 Share Posted September 19, 2010 Hello, my problem is the following: I have a php script that uses two mysql queries, but the query can never finish cause it is stopped by some kind of limitation. My Mysql error_log only shows this message: /usr/sbin/mysqld: Sort aborted The queries are SELECTs and they try to sort about 500.000 line of data based on numbers. The table size is about 35MB I know that this could be some kind of limitation, but i have no clue what to change. Here i post my my.cnf: [client] port = 3306 socket = /var/lib/mysql/mysql.sock [mysqld_safe] socket = /var/lib/mysql/mysql.sock nice = 0 [mysqld] user = mysql socket = /var/lib/mysql/mysql.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language = /usr/share/mysql/english skip-external-locking key_buffer = 16M key_buffer_size = 864M sort_buffer_size = 1M read_buffer_size = 3M read_rnd_buffer_size = 1M max_allowed_packet = 16M thread_stack = 128K table_cache = 3264 open_files_limit = 3200 thread_cache_size = 320 max_heap_table_size = 1024M tmp_table_size = 512M query_cache_limit = 5M query_cache_size = 512M query_cache_type = 1 query_cache_min_res_unit= 1K low_priority_updates = 1 join_buffer_size = 4M max_connections = 1000 long_query_time = 10 log-slow-queries = /var/log/slowq.log interactive_timeout = 60 wait_timeout = 60 skip-bdb [mysqldump] quick quote-names max_allowed_packet = 16M [mysql] #no-auto-rehash # faster start of mysql but no tab completition [isamchk] key_buffer = 16M Quote Link to comment https://forums.phpfreaks.com/topic/213822-help-with-a-mysql-error-that-i-cant-solve/ Share on other sites More sharing options...
kickstart Posted September 19, 2010 Share Posted September 19, 2010 Hi Can you post the queries that are failing and the table layouts. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/213822-help-with-a-mysql-error-that-i-cant-solve/#findComment-1112940 Share on other sites More sharing options...
menntarra_34 Posted September 19, 2010 Author Share Posted September 19, 2010 query="SELECT keyword FROM search_log WHERE active = 1 ORDER BY hits DESC" IT has to check about 540.000 rows, so something might be with some sort of limitations.. i tried to set sort_buffer_size to 90MB, but it didn't help... Quote Link to comment https://forums.phpfreaks.com/topic/213822-help-with-a-mysql-error-that-i-cant-solve/#findComment-1112953 Share on other sites More sharing options...
kickstart Posted September 19, 2010 Share Posted September 19, 2010 Hi Nothing nasty there and I wouldn't expect half a million records to cause a problem (although not sure what you are going to do with half a million returned rows). I take it active is indexed. Have you tried an explain? All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/213822-help-with-a-mysql-error-that-i-cant-solve/#findComment-1112960 Share on other sites More sharing options...
menntarra_34 Posted September 19, 2010 Author Share Posted September 19, 2010 it is for sitemap creation, a script that runs this query and then creates sitemaps... What do you mean by "Have you tried explain?" Quote Link to comment https://forums.phpfreaks.com/topic/213822-help-with-a-mysql-error-that-i-cant-solve/#findComment-1112963 Share on other sites More sharing options...
kickstart Posted September 19, 2010 Share Posted September 19, 2010 Hi In Mysql you can run a query with Explain to reveal the indexes used, etc All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/213822-help-with-a-mysql-error-that-i-cant-solve/#findComment-1112969 Share on other sites More sharing options...
menntarra_34 Posted September 19, 2010 Author Share Posted September 19, 2010 Here is the EXPLAIN of that query: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE search_log ALL NULL NULL NULL NULL 587350 Using where; Using filesort Quote Link to comment https://forums.phpfreaks.com/topic/213822-help-with-a-mysql-error-that-i-cant-solve/#findComment-1112975 Share on other sites More sharing options...
kickstart Posted September 19, 2010 Share Posted September 19, 2010 Hi Looks like you do not have an index on either active (the column you are searching on) or hits (the column you are sorting on). An an index on for each of these and give that a try, All the best KEith Quote Link to comment https://forums.phpfreaks.com/topic/213822-help-with-a-mysql-error-that-i-cant-solve/#findComment-1112981 Share on other sites More sharing options...
menntarra_34 Posted September 19, 2010 Author Share Posted September 19, 2010 i added indexes, but the error still exists. Here is the EXPLAIN: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE search_log ref active active 4 const 498492 Using where; Using filesort Quote Link to comment https://forums.phpfreaks.com/topic/213822-help-with-a-mysql-error-that-i-cant-solve/#findComment-1112985 Share on other sites More sharing options...
kickstart Posted September 20, 2010 Share Posted September 20, 2010 Hi Try a further index on both columns (ie, composite index on search_log and hits). That is the only thing that I can now think to try. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/213822-help-with-a-mysql-error-that-i-cant-solve/#findComment-1113140 Share on other sites More sharing options...
menntarra_34 Posted September 20, 2010 Author Share Posted September 20, 2010 That didn't work either... as i said it must be stopped because of some limitation, don't you think? Quote Link to comment https://forums.phpfreaks.com/topic/213822-help-with-a-mysql-error-that-i-cant-solve/#findComment-1113446 Share on other sites More sharing options...
Mchl Posted September 20, 2010 Share Posted September 20, 2010 Here was a proof I can't read [edit] It seems that this can happen, if MySQL runs out of space on hdd to store temporary data. Check if there are no filesystem quotes that could be limitng MySQL's disk use. Which MySQL version is that? What is search_log table's storage engine? Quote Link to comment https://forums.phpfreaks.com/topic/213822-help-with-a-mysql-error-that-i-cant-solve/#findComment-1113447 Share on other sites More sharing options...
menntarra_34 Posted September 20, 2010 Author Share Posted September 20, 2010 well, if i just do the query in phpmyadmin, so not with the .php script, the same error occurs, the query can't be executed, and mysql stops to function. So it has nothing to do with the filesystem, as all this is done in memory. And the problem is not with the table size, cause it is growing just fine, there isn't any limit, only with the query... Quote Link to comment https://forums.phpfreaks.com/topic/213822-help-with-a-mysql-error-that-i-cant-solve/#findComment-1113456 Share on other sites More sharing options...
mikosiko Posted September 20, 2010 Share Posted September 20, 2010 ...... So it has nothing to do with the filesystem, as all this is done in memory. that is not necessarily true according to Mysql manual in reference to sorting using the filesort algorithm: For each row, store a pair of values in a buffer (the sort key and the row pointer). The size of the buffer is the value of the sort_buffer_size system variable. When the buffer gets full, run a qsort (quicksort) on it and store the result in a temporary file. Save a pointer to the sorted block. (If all pairs fit into the sort buffer, no temporary file is created.) your sort_buffer_size is only 1 MB, therefore is possible than the sort is creating temporary chunk files... hence the answer from MCHL is perfectly valid... look for space limitations in your temporary file system Quote Link to comment https://forums.phpfreaks.com/topic/213822-help-with-a-mysql-error-that-i-cant-solve/#findComment-1113466 Share on other sites More sharing options...
menntarra_34 Posted September 20, 2010 Author Share Posted September 20, 2010 well i checked my /tmp dir with df -h command and it sais: /usr/tmpDSK 480M 15M 465M /tmp so 15M is used and 465M available, which means, that a sort of a 30M table shouldn't be a problem, or i might be wrong... What else to look for? :-\ Quote Link to comment https://forums.phpfreaks.com/topic/213822-help-with-a-mysql-error-that-i-cant-solve/#findComment-1113480 Share on other sites More sharing options...
menntarra_34 Posted September 20, 2010 Author Share Posted September 20, 2010 Well... you are right. i have to increase tmpDSK, i check the command while executing the query and it seems to increase and then it reaches the limit... I just don't understand WHY can't the mysql error log explain more than "Sort aborted" message... it is so stupid... Thanks for the help, i'll report back when i increased the tmpDSK size, if it worked or not. Quote Link to comment https://forums.phpfreaks.com/topic/213822-help-with-a-mysql-error-that-i-cant-solve/#findComment-1113485 Share on other sites More sharing options...
menntarra_34 Posted September 20, 2010 Author Share Posted September 20, 2010 Okay, problem solved, i increase tmpDSK space, and now there are no problems. Thanks for everybody, for taking time helping me Quote Link to comment https://forums.phpfreaks.com/topic/213822-help-with-a-mysql-error-that-i-cant-solve/#findComment-1113489 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.