managing a MySQL db into memory all the time

Question

Not a sysadmin so... We have a database that is approx 4GB in size on a MySQL 5.0.22 instance. The table is InnoDB / UTF-8. There is basically a single query that runs against it against an indexed column. The hardware has 16GB of memory. Ideally, I'd like to manage so that both the data file and index file should always be in memory (with writes being flushed to disk). There is very little activity on this server.

Assuming that it has sufficient memory (which I think it does), should this be the expected behavior without swapping to disk?

How could I confirm that this behavior is taking place?

thanks in advance

Answer 1

if you dont have any other memory-hungry apps running there should be no problem fitting your 'working set' in memory. completely. i've got couple sql servers doing exactly that. they provide excellent read performance and have not caused any trouble.

things to do:

1. make sure mysql is told to allocate enough buffer pools for innodb - in my.cnf put:

   innodb_buffer_pool_size=8GB

2. provided you dont have any other memory hungry apps [ apache / any appservs, custom stuff ] - minimize available swap [ there are different contradicting 'schools' about this subject. i would put 1GB swap + monitor and make sure it's not used. ] ; [ you can read some opinions about swap here ]

3. make sure mysql is 'warmed up' just after startup - run query that forces mysql to read every row / column in your table[s]. simple:

   select md5(col1), md5(col2) from table

   should do the trick. repeat modifications of this query [ eg length(col1) ] to make sure data indeed is read from memory. for more scientific checks you ran run iostat side-by-side and make sure disks are not touched. to be even more scientific - you might want to look at stats of innodb [ to ensure there is no I/O when executing new version of queries one after another ] by executing

   SHOW ENGINE INNODB STATUS\G

   more about interpreting the output here and here.