Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

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

share|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
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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