Help with a mysql error, that i can't solve

[menntarra_34]

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 

[kickstart]

Hi

 

Can you post the queries that are failing and the table layouts.

 

All the best

 

Keith

[menntarra_34]

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...

[kickstart]

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

[menntarra_34]

it is for sitemap creation, a script that runs this query and then creates sitemaps...

What do you mean by "Have you tried explain?"

[kickstart]

Hi

 

In Mysql you can run a query with Explain to reveal the indexes used, etc

 

All the best

 

Keith

[menntarra_34]

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

[kickstart]

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

[menntarra_34]

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

[kickstart]

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

[menntarra_34]

That didn't work either... as i said it must be stopped because of some limitation, don't you think?

[Mchl]

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?

[menntarra_34]

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...

[mikosiko]

...... 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

[menntarra_34]

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? :-\

[menntarra_34]

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.

[menntarra_34]

Okay, problem solved, i increase tmpDSK space, and now there are no problems. Thanks for everybody, for taking time helping me