MySQL not responding, Writing to net status

Question

I just had to reboot MySQL to regain access to some of my website. It had under 300MB of RAM used. Another product was working fine though, accessing it's own tables which are InnoDB.

Here's the show processlist before I rebooted it:

3629079 user    ip:4815 web Query   2324    Writing to net  SELECT * FROM web.files order by file_name_eng desc
3632228 user    ip:4817 null    Sleep   2323        null
3632453 user    ip:4818 null    Sleep   2323        null
3649490 user2   127.0.0.1:28050 null    Query   1967    Locked  UPDATE web.files SET date_publish_test = NOW(), uuser_id = 2 WHERE file_id = 20454

That is a simple select, here's the explain of it (35K records in total, type MyISAM):

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  files   ALL null    null    null    null    30593   Using filesort

Version: 5.0.54a-enterprise-gpl-nt on Windows 2003.

Am I going at this properly?

Answer 1

Your problem is quite straightforward.

• You mentioned that the table is using the MyISAM storage engine.
• The processlist shows
  • Process ID 3629079 is running SELECT * FROM web.files order by file_name_eng desc
  • SELECT has been running 2324 seconds (39 min 44 sec)
  • Process ID 3649490 is running UPDATE query
  • SELECT has blocking the UPDATE for 1967 sec (32 min 47 sec)

NOTE : Any INSERT, UPDATE, DELETE on a MyISAM table requires a full table lock each time.

NOTE : If a series of 50 UPDATEs is issued on one table, each UPDATE requires a full table lock.

SUGGESTION #1

If web.files has no FULLTEXT indexes, you should convert web.files to InnoDB with

ALTER TABLE web.files ENGINE=InnoDB;

I suggested this because InnoDB will allow UPDATEs, DELETEs, and INSERTs to harmoniously coexist with SELECTs via MVCC and Transaction Isolation.

If you are not the Project Manager/Lead Developer, please consult with your Project Manager/Lead Developer before doing this.

SUGGESTION #2

SELECT * FROM web.files order by file_name_eng desc

From the EXPLAIN plan, I can see that the query

1. Query must do a full table scan because there is no WHERE clause
2. There is no index on file_name_eng column, so there is no assistance in order the table (a filesort would happen, but happen fast)

Therefore, you should run

ALTER TABLE web.files ADD INDEX (file_name_eng);

Rhetorial Questions

• Do you need SELECT * ?
• Could you list only the columns you really need ?

Answer 2

It looks like the database was trying to select 30593 rows, it was taking a long time. Since no keys are identified in the 'explain', add an index to file_name_eng since it is in the order clause.

As RolandoMySQLDBA suggested, carefully evaluate if you really need a select * there. Add LIMIT clauses and paging of results if you are showing this to a user somewhere (and not just dumping all records to a file)

Also be aware of MyISAM table locks. Long running selects will block updates as RolandoMySQLDBA said. I don't think you have to convert to InnoDB as a first step though. Large MyISAM tables work OK if you are careful to use indexes and don't do "SELECT *" queries without a LIMIT, or a WHERE clause at least.