MySQL Config on Mac

MySQL

I have a MacBook pro with 2.53 GHz Intel Core 2 Duo and 4GB DDR3 ram.

I am running a huge database on my laptop with over 100 million records.

I want to know : Which of the .cnf files is suitable for MySQL on my MacBook?

Best Answer

I have never used MySQL on a Mac, but I have for Windows.

The Windows version of MySQL came with my-medium.ini, my-small.ini, my-large.ini, my-huge.ini.

If the Mac version of MySQL came with similar .cnf files, I would choose my-medium.cnf

Once you copy my-medium.cnf to my.cnf, you could tune it as follows:

If the majority of your data is MyISAM do this:

SELECT CONCAT(CEILING(SumNDXs/POWER(1024,2)),'M') KeyBufferSize
FROM
(
    SELECT SUM(index_length) SumNDXs
    FROM information_schema.tables
    WHERE table_schema NOT IN ('information_schema','mysql')
    AND engine = 'MyISAM'
) A;

This will give you the proper size for key_buffer_size given the amount of MyISAM data you have. Obviously, if this number exceeds 1024M (1GB,25% of system RAM), then set key_buffer_size to your liking with good judgment, as long as it's less than 1024M.

If the majority of your data in InnoDB do this

SELECT CONCAT(CEILING(InnoDBDataAndIndexes/POWER(1024,2)),'M') KeyBufferSize
FROM
(
    SELECT SUM(data_length+index_length) InnoDBDataAndIndexes FROM
    information_schema.tables WHERE engine = 'InnoDB'
) A;

This will give you the proper size for innodb_buffer_pool_size given the amount of InnoDB data and indexes you have. Obviously, if this number exceeds 3072M (3GB,75% of system RAM), then set innodb_buffer_pool_size to your liking with good judgment, as long as it's less than 3072M.

If you have a mixture of InnoDB and MyISAM data, then strike a balance between these numbers and leave at least 25% of system RAM free for MacOS.

Once you have innodb_buffer_pool_size, set innodb_log_file_size to 25% of innodb_buffer_pool_size.

Then do three 3 things:

  1. Shutdown mysql
  2. delete ib_logfile0 and ib_logfile1
  3. Startup mysql (mysqld process will recreate ib_logfile0 and ib_logfile1 to new size)

Give it a try, and let us know !!!

Related Question