We use mysql for creating inverted index files for our small scientific search engine.
We have a lot of XML files, indexate them to one big (50mln) mysql table.
Then we create tables and files using group by similar rows
insert ignore into table (field1, fieldCount) select field,count(*) from table_temp group by field order by field)
So sometimes this query doesn’t work with no errors:
Using command top and iostat I see, that mysql is working, but without using processor (memory and HDD) resources.
MySQL is in state sbwait (waiting for data!!??)
Mysql command show processlist shows, that NULL query is executing.
Mysql logs are ok - no problems.
Parent process (php script, that executed query) is working, also without using resources.
Does everybody know, where is the problem?