5

I have a lot of RAM (1 gb) on my server, and I have a table (100 mb) that I would like to increase the speed of. Is it possible to keep the entire table in memory (while keeping it MYISAM)? Would this make things faster (I already have the proper indexes).

Thanks

Improve this question
1
  • i question this approach if you get 20 visitors at once then your a you've already reached and passed your system resources, what would you do if you get 100 visitors? Commented Dec 3, 2010 at 7:10

3 Answers 3

Reset to default
5

A better suggestion will be increase query cache size, let mysql do the internal optimization

more details - http://dev.mysql.com/doc/refman/5.5/en/query-cache.html

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Query cache will not work though, when the application uses prepared statements. Just a little thing to remember.
@ajreal: what type of query cache size would you recommend for a 1 gb MySQL server with a main table that is 100 mb? there are also a few other tables that are used once-in-a-while. also, quick question: does the query cache for a table's queries get invalidated after an insert to that table?
@eric - the query cache purged if there is write operation, this served almost all your purpose ... read that article to monitor query cache hit rate
The query cache in MySQL is a very simple implementation. It is a simple string match of queries it has received before. Prepared statments are indeed better - for performance, but mostly for security! They effectively prevent code injection when you have user provided filter values. The basic idea is that instead of you concatenating a query string with all parameters already put in, you use question marks in the WHERE clause and later fill in the values. That counteracts the exact string match of the query cache. The MySQL manual has more info on prepared statements.
Before 5.1.17, the query cache is not used for prepared statements , so to enjoy both benefits, upgrade does the trick
|
5

If you have lots of memory in the machine and you use it for MySQL primarily, the OS should take care of that. With MyISAM the idea is to let the filesystem keep the pages in its cache that are mostly needed. With lots of RAM, chances increase that your table will not hit the disk very much anyway.

This is IMHO even better than trying to make the table of type MEMORY, because then you would have to worry about writes - those should usually be persistent anyway.

So in the end, unless the machine is under much concurrent memory pressure from other apps than the MySQL server - in which case 1GB would not seem to much for a server nowadays - you will probably not have to do anything to achieve your goal :)

Improve this answer

Comments

4

You can change the table engine to MEMORY and it will store the full table in memory.

Improve this answer

2 Comments

If database server power tripped ... data will be?
All the contents of a table with engine = memory will be lost. They are only useful for store (in fact, very quickly) temporary values. To set this values: ALTER TABLE t1 ENGINE = memory;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.