I have a MySQL server 5.0.75 Ubuntu, on an m1.small instance running on Amazon's EC2 as part of an application. During peak usage the server load will rise very high, while the memory usage stays low and the application server is no longer responsive since it's waiting for query results. The application server has only 5-8 apache processes running (mod_perl processes).

The data directory uses only 140MB of data so the MyIsam tables aren't very big.

The queries are pretty complicated with some big joins being performed, and the application makes a lot of queries.

mysqltuner reports everything OK except "Maximum possible memory usage: 1.7G (99% of installed RAM)" but I'm nowhere close to using that.

My question is, where should I be looking to fix this? Is this something that can be tuned away, or do I just need a larger instance/server? Googling indicates either or also upgrading MySQL server.

Any pointers in the right direction would be greatly appreciated, thanks!

EDIT: I just discovered this in my slow queries log:

# Time: 101116 11:17:00
# User@Host: user[pass] @  [host]
# Query_time: 4063  Lock_time: 1035  Rows_sent: 0  Rows_examined: 19960174
SELECT * FROM contacts WHERE contacts.contact_id IN (SELECT external_id FROM contact_relations WHERE external_table = 'contacts' AND contact_id IN (SELECT contact_id FROM contacts WHERE (company_name like '%%butan%%%' OR country like '%%butan%%%' OR city like '%%butan%%%' OR email1 like '%%butan%%%') AND (company_name is not null and company_name != '')));

Which actually brings up a different but related question:

If I have a contact table containing: John Smith,The Fun Factory,555-1212,[email protected]

What's the best way to search for that record using "factory" as a search key? Fulltext rarely seems to find items in the middle of a word, for example "actor" should bring up "Factory"