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?

Gerald Schneider
  • 23,274
  • 8
  • 57
  • 89
Ahmad Hamad
  • 11
  • 1

3 Answers3

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.

gnur
  • 149
  • 7
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.

Kondybas
  • 6,964
  • 2
  • 20
  • 24
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.

asktyagi
  • 2,860
  • 2
  • 8
  • 25