Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 1 down vote accepted

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 ?
share|improve this answer
    
long story short, I administer the server, it wasn't responding and showed the process list before rebooting mysql. TIL that those were seconds and not ms! OMG! This query was done manually from one user (I doubt that such beasts would exist in an application). So, I'm thinking adding an index would solve this (adding an index on file_name_fra too)? I'll have to inquire about which text fields are being sorted on to add indexes. –  TekiusFanatikus Nov 30 '12 at 16:55

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.

share|improve this answer
1  
The explain was done on the select statement (not the update). I have a PK on file_id which is also specified in the update statement. The files table is MyISAM with one primary key (file_id) and no indexes... dead simple table. –  TekiusFanatikus Nov 30 '12 at 16:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.