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?


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 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
    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 !!!

| improve this answer | |
  • thanx for your replay. Mac configs are as the same as windows'. I executed the InnoDB query you wrote here but I got an error : "ERROR 1582 (42000): Incorrect parameter count in the call to native function 'CEILING'". – AliBZ Feb 17 '11 at 9:03
  • @AliBZ Thanks you, I changed the queries. There should have been an extra right parenthesis after POWER(1024,2) in both queries. – RolandoMySQLDBA Feb 17 '11 at 18:13
  • @RolandoMySQLDBA thanx man, I ran the query and I got 16349M ! How is it !? :D – AliBZ Feb 17 '11 at 20:54
  • @AliBZ All it means is that you have almost 16G of InnoDB (data and Indexes combined). This being the case, I recommend you use your judgement. With 4GB total RAM, I suggest setting innodb_buffer_pool_size=1G and innodb_log_file_size=256M for now and see how mysqld behaves. – RolandoMySQLDBA Feb 17 '11 at 21:07
  • @RolandoMySQLDBA Thanx for your replay. I checked activity monitor which is mac's task manager and mysql takes up to 2.5 GB of my RAM. I usually don't do other tasks when I am using mysql, so it is ok that mysql takes this much of my ram. But I wanna know which of .cnf configuration is suitable for my system. – AliBZ Feb 17 '11 at 21:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.