1

I do run several mysql servers for several clients, there is a common problem I used to see. When I run some big query against big databases, for example:

I have a MyISAM table with several fulltext indexes, and around 8.7 million records, if I search a non-indexed column the mysql service becomes unresponsive until the result comes out (this could take 30 minutes).

How to avoid rendering the mysql service unresponsive? For example, in any OS you can set the process priority to guarantee that every process gets some resources to run, How do I do that in mysql?

Improve this question
2
  • 3
    I'm no database expert, but letting users or software search through 8.7 million rows without using indexes sounds kind of insane...
    – pauska
    Apr 21, 2015 at 7:14
  • Please posts a sample query you are using
    – RolandoMySQLDBA
    Apr 21, 2015 at 20:23

3 Answers 3

Reset to default
2

There can be several solutions to your problem. You could try tweaking settings (allowing mysql to use more ram, making sure it doesn't swap, adding indexes) or altering the hardware (more ram, faster disks).

Another option is to set up a mysql slave to run the heavy read queries on, that way you can still update the master with read+write queries while the slave calculates the result you need.

Improve this answer
1

You SHOULD have indices for every column involved into the search. When you search in the column that has no index, search if performed by "filesort" that is not very efficient for FTS.

Improve this answer
1

You should not run heavy read queries on master specially in production. Please go for replication or MySQL cluster setup and route your queries to non write nodes.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .